How to generate an Excel Reoprt using ASP

[kurienfenn]

i have to generate an excel report using ASP.the datasfor the report is from a database.please help me.thanks in advance.

[bluebeard96]

For a real easy solution, you could simplycreate a new text file,run your query, and write the data one record at a time into your text file (with tabs between the fields). When you finish with your records, close the text file and save it as an XLS. Any tab delimited text file will open just fine in excel if you simply change the file extension.


Did you simply need a data spreadsheet, or are you looking to add formatting, column widths, etc?





Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
Mike@KiwanisPQ.org
(760) 419-7429

[m1234]

Try something like this (replacing directory names, etc. with your own:

<!--Writefile out as excel csv file -->

<%
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.CreateTextFile("D:\inetpub\wwwroot\yourdir\yourotherdir\cgi-bin\roster.csv")%><%
Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open "DSN=yourdata_yourtable; UID=; PWD=" %><% SQLStmt = "SELECT * FROM roster order by last,first" %><% Set RS = Connection.Execute(SQLStmt)
%><%
On Error Resume Next
rs.MoveFirst
mystring="last,first,badge,phf,class,spouse,sphf,address,cit y,bphon,hphon,faxto,email,bday,sbday,anniv,status, picture,showpicture"
oTextFile.Write mystring &amp; chr(13) &amp; chr(10) %>
<!--note the above line writes the excel spreadsheet headings - then we write the data records out below-->
<% do while Not rs.eof
%><% mystring = rs("last") &amp; "," &amp; rs("first") &amp; "," &amp; rs("badge") &amp; "," &amp; rs("phf") &amp; "," &amp; rs("class") &amp; "," &amp; rs("spouse") &amp; "," &amp; rs("sphf") &amp; "," &amp; rs("address") &amp; "," &amp; rs("city") &amp; "," &amp; rs("bphon") &amp; "," &amp; rs("hphon") &amp; "," &amp; rs("faxno") &amp; "," &amp; rs("email") &amp; "," &amp; rs("bday") &amp; "," &amp; rs("sbday") &amp; "," &amp; rs("anniv") &amp; "," &amp; rs("status") &amp; "," &amp; rs("picture") &amp; "," &amp; rs("showpicture") %><% oTextFile.Write mystring &amp; chr(13) &amp; chr(10) %><%
rs.MoveNext
loop%><% oTextFile.Close
set oTextFile = nothing
set oFS = nothing
%><% rs.close
set rs=nothing
Connection.Close
set Connection=nothing %>

Post Edited (m1234) : 11/8/2004 9:43:55 PM GMT