Dynamic Excel Reports with ASP

Sometimes it's useful to present data to users in Excel format, so they can easily manipulate the data themselves. In this article, a very simple technique for accomplishing this is demonstrated.

Technique

There are many situations in which you may wish to convert table data into an Excel spreadsheet format for the user. There are several methods available for doing this; I will describe in this article one of the simplest ones. It basically tricks the user's browser into thinking the HTML it is downloading is actually an Excel document, and then Excel does the rest of the work by parsing the HTML into a worksheet. Because of the way this works, although this technique is free and easy, it is also very limited in how it can be used. Also, this method only works if the client has Excel 97 or later installed.

If you need to generate Excel documents on the web server in any kind of scalable, robust, or customized fashion, the best tool available is OfficeWriter (formerly ExcelWriter) from SoftArtisans. AspAlliance author Andrew Mooney has written a fairly detailed review of an older version (v4) of ExcelWriter.

In order to create an Excel report dynamically, you must simply create a .asp file with the header of:

1 <% 2 Response.ContentType = "application/vnd.ms-excel"
3 %>

This informs the browser that the code to follow is Excel formatted, and Netscape or IE will prompt the user to Save or Open the file. When they Open the file, Excel is launched and the report is viewed by Excel. In order for Excel to understand your data, you need only to create an HTML table, which Excel 97 will then convert into its own format. NOTE: This must be the first line of code on the page! (Actually, it just has to be before any other header or HTML info is output to the browser, but put it at the top and it won't cause you problems)

Code

They also want to be able to manipulate this data using Excel, and perform some calculations on it. They created an Excel sheet using this code:

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

set conntemp=server.createobject("adodb.connection")
cnpath="DBQ=" & server.mappath("/stevesmith/data/timesheet.mdb")
conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath
set RS=conntemp.execute("select * from donut")
%>
<TABLE BORDER=1>
<TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
For i = 0 to RS.Fields.Count - 1
%>
<TD><B><% = RS(i).Name %></B></TD>
<% Next %>
<TD><B>On Hand (calculated)</B></TD>
<TD><B>Gross (calculated)</B></TD>
</TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not RS.EOF
%>
<TR>
<% For i = 0 to RS.Fields.Count - 1
%>
<TD VALIGN=TOP><% = RS(i) %></TD>
<% Next %>
<TD>=b<%=j%>-c<%=j%>-d<%=j%></TD>
<TD>=d<%=j%>*e<%=j</TD>
</TR>
<%
RS.MoveNext
j = j + 1
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close
%>
<TR BGCOLOR=RED>
<TD>Totals</TD>
<TD>=SUM(B2:B6)</TD>
<TD>=SUM(C2:C6)</TD>
<TD>=SUM(D2:D6)</TD>
<TD>n/a</TD>
<TD>=SUM(F2:F6)</TD>
<TD>=SUM(G2:G6)</TD>
</TR>
</TABLE>