AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1&pId=-1
Dynamic Excel Reports with ASP
page
by Steven Smith
Feedback
Average Rating: 
Views (Total / Last 10 Days): 309890/ 322

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:

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

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)

For example, a donut shop wants to track its donuts. It has a report written which dumps the following data:

flavor qty_baked qty_eaten qty_sold price
Boston Crème 24 2 10 0.5
Jelly 24 1 12 0.5
Strawberry Crème 36 1 15 0.5
Chocolate Crème Stick 24 2 6 0.75
Maple Crème Stick 12 1 6 0.75
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>
10    <TR>
11    <%
12   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
13   ' % Loop through Fields Names and print out the Field Names
14   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
15    j = 2 'row counter
16    For i = 0 to RS.Fields.Count - 1
17    %>

18    <TD><B><% = RS(i).Name %></B></TD>
19    <% Next %>
20    <TD><B>On Hand (calculated)</B></TD>
21    <TD><B>Gross (calculated)</B></TD>
22    </TR>
23    <%
24   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
25   ' % Loop through rows, displaying each field
26   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
27    Do While Not RS.EOF
28    %>

29    <TR>
30    <% For i = 0 to RS.Fields.Count - 1
31    %>

32    <TD VALIGN=TOP><% = RS(i) %></TD>
33    <% Next %>
34    <TD>=b<%=j%>-c<%=j%>-d<%=j%></TD>
35    <TD>=d<%=j%>*e<%=j%></TD>
36    </TR>
37    <%
38    RS.MoveNext
39    j = j + 1
40    Loop
41   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
42   ' % Make sure to close the Result Set and the Connection object
43   ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
44    RS.Close
45    %>

46    <TR BGCOLOR=RED>
47    <TD>Totals</TD>
48    <TD>=SUM(B2:B6)</TD>
49    <TD>=SUM(C2:C6)</TD>
50    <TD>=SUM(D2:D6)</TD>
51    <TD>n/a</TD>
52    <TD>=SUM(F2:F6)</TD>
53    <TD>=SUM(G2:G6)</TD>
54    </TABLE>

You can test this code by clicking here. As you can see, this not only includes all of the information from the original HTML table-based dump, but it also includes some simple Excel functions, and they would easily be able to create charts and/or graphs from this spreadsheet, or manipulate the data into a local database.

Note: Microsoft has acknowledged a BUG in IE (3.02, 4.0, 4.01, 4.01sp1) which causes it to misinterpret Excel output, particularly when generated by ASP, ISAPI, or CGI. You can read more about it at http://support.microsoft.com/support/kb/articles/q185/9/78.asp. To summarize: When Internet Explorer connects to a Web server resource that dynamically generates Word, Excel, or other Active Documents, Internet Explorer may send two GET requests for the resource. The second GET usually does not have session state information, temporary cookies, or authentication information that may have already been specified for the client. This bug can affect any local server (EXE) Active Document application hosted inside the Internet Explorer frame window. It occurs most frequently with ISAPI, ASP, or CGI applications that adjust the HTTP "Content Type" header to identify the installed application.

Update: 16 July 2003

I wrote this article several years ago, and since then it has grown to be extremely popular. I receive one or two emails per week asking questions like "How can I have Excel open several sheets?" or "How can I control the format of the sheet?" or "How can I add charting?". The thing to remember about this particular technique is that it is 100% dependent on the Excel application on the user's machine. It is a feature of Excel, not ASP, that allows it to look at an HTML table and interpret it as an Excel worksheet. Unfortunately, there are significant limits to what Excel can do in this regard. However, if you need to go beyond these limits, there are things you can do to help Excel, although they do require more work and resources than the one line of code this technique requires. The best product on the market, bar none, to do anything non-trivial with Excel over the web is ExcelWriter, from SoftArtisans. I'll be writing a review of the product as a separate article some time in the future, but I encourage you to visit their website and look through their samples to see if they solve your problem if this technique does not meet your needs.


Reference Links:


Product Spotlight
Product Spotlight 

©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-12-13 3:15:08 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search