Here is an example that takes information from an access database and puts
it into an excel spreadsheet onto the server.
After reading the contents of the db instead of writing output to the
browser html text is written to the server with the .xls file extension. All
observations (states) will be a unique row within the excel that is created
with its corresponding info displayed as well. A link to the new document is
created (in the browser) for easy navigation to the new document. The
current time (server time) is also displayed to show that the excel document
was created dynamically.
<%
' Name of the access db being queried
accessdb="state_info"
' Connection string to the access db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")
' Query the states table from the state_info db
sql = "select state,statename,capital,year,order from states "
' Execute the sql
rs.Open sql, cn
' Move to the first record
rs.MoveFirst
' Name for the ouput document
file_being_created= "states.xls"
' create a file system object
set fso = createobject("scripting.filesystemobject")
' create the text file - true will overwrite any previous files
' Writes the db output to a .xls file in the same directory
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)
' All non repetitive html on top goes here
act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr>")
act.WriteLine("<th nowrap>State</th>")
act.WriteLine("<th nowrap>Abbreviaton</th>")
act.WriteLine("<th nowrap>Capital</th>")
act.WriteLine("<th nowrap>Year Entered</th>")
act.WriteLine("<th nowrap>Entrance Number</th>")
act.WriteLine("</tr>")
' For net loop to create seven word documents from the record set
' change this to "do while not rs.eof" to output all the records
' and the corresponding next should be changed to loop also
for documents= 1 to 7
Act.WriteLine("<tr>")
act.WriteLine("<td align=""right"">" & rs("statename") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("state") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("capital") & "</td>")
act.WriteLine("<td align=""right"">"& rs("year") & "</td>")
act.WriteLine("<td align=""right"">"& rs("order") & "</td>")
act.WriteLine("</tr>")
' move to the next record
rs.movenext
' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponding for statement above should be changed also
next
' All non repetitive html on top goes here
act.WriteLine("</table></body></html>")
' close the object (excel)
act.close
' Writes a link to the newly created excel in the browser
response.write "<a href='states.xls'>States</a> (.xls) has been created on "
& now() & "<br>"
%>
----- Original Message -----
From: "Dave Lyons" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, February 12, 2003 9:33 PM
Subject: wot: making an asp page convert to an excel download
> i just asked me how to convert an ASP page to an excel download ((the
dynamic data from an access db at least)
> sounds pretty easy in cf but i need the answer in asp
>
> thanks
>
> dave
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
