Classic ASP: Generate Excel Spreadsheets on the Fly

Posted October 11, 2006 by katy8439 in Web application programming

The following script allows you to generate an Excel spreadsheet from data stored in an access database.

This code allows you to generate an Excel Spreadsheet using ASP quickly and easily. You can export information from arrays, database tables, or forms and present them as an Excel file.

Of course, the end user will need to have Excel on their machine to view it.

We have a database containing a table called TBLCompany with the following information in it:

* ID
* CompanyName
* CompanyAddress1
* CompanyAddress2
* CompanyAddress3
* CompanyAddress4
* CompanyPostCode
* CompanyAddress3
* CompanyTel
* CompanyFax
* CompanyEmail
* CompanyWebsite

If we wanted to take the information contained in this Access Database and display it as an Excel file, all you have to do is replace this standard ASP Page Header:
<%@ Language=VBScript %>

Replace the header above with the following:
<%Response.ContentType = "application/"%>

The page will be outputted as Excel, using standard VBScript. This is great for reporting, time tracking, and numerous other business related activities!

Sample Code

<% ' This Code is Copyright Katy Whitton ' You are free to use this code on any site ' But Please Keep This Copyright Statement In ' Place ' For more ASP and JavaScripts, please visit ' ' Set Up our Database connection via DSNLess Connection ' You May be able to use Server.MapPath for you connection ' Or alternatively connect via ODBC, Please contact your ' Hosting provider for more information ' Dimension our Variables for the Dabase Connection dim objConn, strCon, objRS, strSQL Set objConn = Server.CreateObject("ADODB.Connection") 'Change the Line below to point to the location of the database strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\tutorials\source\sampleDB.mdb" objConn.connectionstring = strCon objConn.Open Set objRS = Server.CreateObject("ADODB.Recordset") ON ERROR RESUME NEXT ' Set the SQL Statement to get the information from the database strSQL="SELECT * FROM TBLCompany" ' Open the Database objRS.Open strSQL, objConn ' Open up the page as an Excel File Response.ContentType = "application/" ' Set up the Table we wish to Populate %>

<% ' Cycle through the database and populate the table DO WHILE NOT objRS.EOF%>

<% objRS.MoveNext Loop%>

ID CompanyName CompanyAddress1 CompanyAddress2 CompanyAddress3 CompanyAddress4 CompanyPostCode CompanyTel CompanyFax CompanyEmail CompanyWebsite
<%=objRS("ID")%> <%=objRS("CompanyName")%> <%=objRS("CompanyAddress1")%> <%=objRS("CompanyAddress2")%> <%=objRS("CompanyAddress3")%> <%=objRS("CompanyAddress4")%> <%=objRS("CompanyPostCode")%> <%=objRS("CompanyTel")%> <%=objRS("CompanyFax")%> <%=objRS("CompanyEmail")%> <%=objRS("CompanyWebsite")%>

As you can see, we can export the data quite easily into Excel from
Access on the Fly and format it using standard HTML.

The Conversation

Follow the reactions below and share your own thoughts.