Classic ASP: Generate Excel spreadsheets on the fly

Contributed by katy8439 October 11, 2006  
Tagged: 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 on the fly. You can export information from arrays, database tables or forms and present them as an Excel file.

Of course, the end user will have 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

You can view the table layout and information here

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 the standard ASP Page Header:
<%@ Language=VBScript %>

with:
<%Response.ContentType = "application/vnd.ms-excel"%>

And the page will be outputted as Excel, using standard VBScript, neat eh?! It’s great for reporting, time tracking and all sorts of other business type activities!

Click here to view the code in action

You can download the sample database here

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
' http://www.katywhitton.com

' 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/vnd.ms-excel"

' 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")%>

So as you can see we can export the data quite easily into Excel from
Access on the Fly and format it using standard HTML!