CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 ASP to Multi-Worksheet Excel book using only XML

Print topic Send  topic

Author Message
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 8-)

_________________
Jayme
View profile  Send private message
markito

Posts: 24
Posted: 12/05/2006, 8:42 AM

What about a php version of this...???
View profile  Send private message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.