jayme27
Posts: 3
|
Posted: 12/05/2006, 5:52 AM |
|
ENJOY:
Approved Solution for outputting HTML or ASP content to Multiple Excel Worksheets within a Workbook.
*No requirement for OWC, Excel application or MS Office to be installed on the web server. This approach uses straight XML to accomplish this feat.
1. Button onclick passes "u" parameters to URL where the receiving page does the parsing. Code of this receiving page is below.
2. Parses the "u" parameter and creates individual tab worksheets (1 for each "u" value) using the report page template: CMUnitUpdateAll.asp which is a CodeCharge Studio web page.
For any unfortunate souls that always wanted to know how to preform a more complex ASP --> Excel output:
=======Start of Processing Page===============================
<%@ Language=VBScript %>
<!-- #INCLUDE FILE=".\Common.asp"-->
<!-- #INCLUDE FILE=".\Cache.asp" -->
<!-- #INCLUDE FILE=".\Template.asp" -->
<%
Response.Buffer = true
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "Attachment; filename = CMUnitUpdateRptAll.xls"
'===================================================================
'declare variables
Dim adoConn
Dim sConn
Dim DBConnection1
Dim SQL
Dim recordset
'declare SQL statement that will query the db
'SQL = "SELECT OrgID,UnitName FROM Organization Where OrgID="&uinURL&" ORDER BY UnitName"
'define the connection string using include file
Set DBConnection1 = new clsDBConnection1
DBConnection1.Open
sConn = DBConnection1.ConnectionString
DBConnection1.Close
'create an ADO connection and recordset
Set adoConn = CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
'Open the connection to the database
adoConn.ConnectionString = sConn
adoConn.Open
'Open recordset object and execute the SQL statement
' recordset.Open SQL,adoConn
%>
<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<meta name="Excel Workbook Frameset">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta content="Excel.Sheet" name="ProgId">
<meta content="Microsoft Excel 11" name="Generator">
<style>
<!--table
@page
{mso-header-data:"&CMultiplication Table\000ADate\: &D\000APage &P";
mso-page-orientation:landscape;}
br
{mso-data-placement:same-cell;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<%
Dim i, orgcount, OrgSet
'orgcount = 3
'For i=1 to orgcount 'Generate a Sheet for each Organization
If Len(Request.QueryString("u"))>0 Then
dim Firstids
Firstids=split(Request.QueryString("u"),",")
For i=0 to UBound(Firstids)
'Open recordset object and execute the SQL statement
SQL = "SELECT OrgID,UnitName FROM Organization Where OrgID="&CStr(Firstids(i))&" ORDER BY UnitName"
set OrgSet = adoConn.Execute(SQL) 'Open Recordset
while not OrgSet.EOF 'Create and Name each worksheet
%>
<x:ExcelWorksheet>
<x:Name><%=OrgSet("UnitName")%></x:Name>
<x:WorksheetSource HRef="http://localhost/ConsequenceMgt/CMUnitUpdateAll.asp?OrgID=<%=CStr(Firstids(i))%>"/>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<%
OrgSet.MoveNext
wend
Next
End If
%>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</HEAD>
<BODY>
<%
'close recordset then db connection
OrgSet.Close
Set OrgSet = Nothing
adoConn.Close
Set adoConn = Nothing
%>
</BODY>
</HTML>
=====END of the Processing page====================
Enjoy,
Jayme
_________________
Jayme |
|
|
markito
Posts: 24
|
Posted: 12/05/2006, 8:42 AM |
|
What about a php version of this...???
|
|
|
jayme27
Posts: 3
|
Posted: 12/05/2006, 9:46 AM |
|
Shouldn't be too difficult since most of the page is standard HTML and SQL with very little ASP (vbscript) code.
Don't know difference between PHP and ASP, but sounds like you might? Sorry I can be no further help at this time. I'm strictly an ASP programmer only. Suppose I need to broaden my knowledge of PHP too.
Jayme
_________________
Jayme |
|
|
|