In this article we will discuss and demonstrate how to create Microsoft Excel worksheets from Active Server pages. Though there are many ways that this can be done, we have chosen a method for the example that conserves server load and puts the majority work on the client.
Three Techniques
The other methods however are worth mentioning, since there are many ways to come to the same ends. The ends in this case is having real time data served from the web server and available in Excel upon the clients machine. The three methods are as follows: 1) Creating the VBA component on the web server and either streaming it down to the client or linking to the finished component from a separate page. 2) Making Excel call the web server for the data using HTTP and inserting it into the right areas of the Excel WorkSheet. 3) Creating an HTML stream that Excel can interrupt and translate into a Excel WorkSheet. The third technique is the one that I am going to discuss in this article, but first let take a brief look at the other options.
The
First Technique
The first technique is to create a VBA component, in this case an Excel WorkSheet, on the web server and either streaming it down to the browser or linking to it from another page. This technique at first glance seems the most appealing. Since you can create COM object on an ASP page by calling Server.CreateObject with the name of the object that you want to create. Then with multiple calls to method and properties of the object you could fill in the data of the WorkSheet. However, at second glance, this is not the best technique. To start with it would cause considerable server load if the server was receiving multiple requests for these objects. Another problem arises when opening the object. Each object must have a unique file name, objects with shared file names give all by the first instantiation read only permission. This means that for every request to the web server you must generate a unique name for that object. Since you are generating all these files you must also clean up the files, or they will start to consume disk space. The clean up mechanism will have to coordinate with the request, so files are not deleted when they are being used. You can see how the problems start to mount. Another issues is the size of the Excel files that you will be streaming, Excel files for small WorkSheets are usually big in comparison to an HTML page or a graphic. The large size cause network strain and slow response time for sites with minimal bandwidth. The final problem is, VBA objects are not multi-threaded and were never intended to be used as a server. Imagine this, you are a VBA object and you happily live on a machine where your owner sometime opens you up to view a document. This is what your designers intended and this is how you where built. You are very proud that you take up a minimum amount of memory and can run on a 486 machine without problems. Suddenly one day your owner throws you on a server and starts to make you open several requests a second. Even with addition memory and a fast processor you where never tested with multiple open instances and you fail miserable at performing your job. Out of the three techniques this is the most interest, but the least workable.
The Second Technique
The second technique is to have Excel call the web server and use the data to fill in WorkSheet. This technique is actually fairly easy to do in Excel 97. Microsoft has added additional functionality to Excel 97 called embedded HTML. There is also a wizard to give you a hand. The typical user experience would be to open an Excel document, push a button that is embedded in a cell, the web site is called and the data appears in the Excel WorkSheet. Through this method is very useable in my opinion it is not as powerful as the third method.
The Third Technique
The third technique is to create an HTML stream that Excel can interrupt and translate into an Excel WorkSheet. We will spend the remainder of the article describing how this works and how to use it. The only major flaw in this technique is that we can only fill in one Excel WorkSheet at a time, multiple Excel Worksheet in a WorkBook will not work. Before we get started there are a couple of things we need to discuss about Microsoft Internet Explorer and Excel 97.
Internet Explorer 3.0
Internet Explorer 3.0 is capable of displaying an Microsoft Excel Worksheet just as it would be displayed in the Excel application, if Excel is installed. The usual way of displaying the worksheet in the IE browser is to enter the URL of an excel workbook with the extension xls. IE then uses the extension to figure out the mime type and the application the mime type and the extension are associated with. IE needs to determine the mime type from the extension since IIS and other web servers pass the data stream back as a application/octect-stream. IE changes the mime type to application/vnd.ms-excel. If the IIS server returned application/vnd.ms-excel as the mime type, or Content-Type as defined in HTTP, IE would try to interrupt the data stream as an Excel WorkSheet. By interrupt I mean launch Microsoft Excel and display the data within the IE using Excel. So, any application sending back a Content-Type of application/vnd.ms-excel will cause IE to display the data as an Excel WorkSheet.
Excel 97
In theory we know how to make Excel come up in the IE Browser, now all we have to do is pass Excel the correct data stream. This might be the trickiest part and also the easiest with Excel 97. The example that we are about to present will not work with earlier versions of Excel, the examples will only work with Excel 97. Excel 97 contains a special piece of code that will generate an Excel WorkSheet from an HTML table. What this means is that we can pass an HTML table back as the data stream and Excel will interpret the data stream as a WorkSheet. Much easier then generating a data stream that looks like an Excel WorkSheet.
Summary
With Excel 97 and Internet Explorer 3.0 we can create an Excel spreadsheet from an HTML table by changing the response Content-Type to application/vnd.ms-excel and sending back an HTML table as the data stream. Why would we want to do this?
Real
Time Data, Anytime
If you create your HTML tables using Active Server pages to dynamically fill in the data, you can produce reports using ADO that reflect the current data in the database. As an example, analysis of page hits. Most Internet companies have page hit reports that are generated and viewable through HTML browsers. By changing the mime type on these reports you can display them as Excel WorkSheets that can later be graphed or manipulated.
Reduced Server Load If you have a dynamically generated web page
that reports page hits, it probably has a total at the bottom. The
lazy way to generate a total is to execute another SQL call after
the original call to display all the hits. Something like:
SELECT
SUM(Page_Hits) FROM
PageTable WHERE
DATEDIFF(day,Page_Date,GetDate()) < 1
I call this the lazy way since it makes another call to the database (SQL Server in this case) and causes extra the SQL Sever load. Especially since the aggregate does a table lock, and the GetDate() and DATEDIFF are expensive.
A second technique
is to add the page hits up from the original call that displays all
the hits. You can easily do this in an Active Server Page with a
little code like this:
<%
Total=0
Do While Not RS.eof
%>
<TR>
&l%TD>
<%=RS("Page_Hits)%>
</TD>
</TR>
<%
Total = Total + RS("Page_Hits)
RS.MoveNext
Loop
%>
<TR>
<TD>
<B><%=Total%></B>
</TD>
</TR>
This technique however has some problems when it comes to more complicated aggregates then SUM. For instance, what
if you were trying to calculate the mean, median, and average. With
more complicated functions, there is room for development
bugs.
There is a third
technique when creating Excel WorkSheets. This technique involves
embedded Excel formulas within the Excel WorkSheets. These formulas
can do calculations like sum and average. This technique has the
same advantage as the second technique, namely it doesn't need to go
back to the SQL Server for additional queries. Plus, it removes the
formula development from the Active Server page
developer.
Finally the big advantage is that it removes the complicated formulas from being run on the server and instead, runs them on the client. Since the formulas are not run until the browser loads the data stream in Excel, the server need not do the work of running the formulas as seen in the second technique.
Excel 97
One of the great advantages of displaying your data in Excel is that it is Excel. This means that you get all the advantages of working in Excel, like adding charts, and complicated algorithms in the Excel WorkSheet. Plus, others who are knowledgeable in Excel, such as
Sales and Marketing organizations, can save the Excel WorkSheet as an Excel document and manipulate the numbers or graphs to reflect their needs.
Changing the Mime
Type To change the
mime type of the response, add the following to the top of your
Active Server page.
An Excel Table with Embedded
Formulas
The layout of the Excel WorkSheet will resemble the HTML table that you pass down. The most common mistake is to have HTML, HEAD, TITLE, and BODY tags in your Active Server page. You do not need these tags in your Active Server
page. In fact, the WorkSheet will not open correctly with these
tags. Here is an example of an Active Server page that will open as
an Excel WorkSheet.
Notice the Excel formula in Cell A3. You can use any supported functions just like SUM is being used here.
Summary
The example above is pretty simple, however it gives you a good idea of what is going on. From here you should be able to use ADO to construct a table based on a results set from your database. Once the table is constructed and passed back to Excel it will appear as an Excel WorkSheet.
In' tra net
- n. 1) a computer network connecting an affiliated set of
clients using standard internet protocols, esp. TCP/IP and HTTP.
2) an IP-based network of nodes behind a firewall, or behind several
firewalls connected by secure, possibly virtual, networks.
IDM Unwired
IDM on the Road
NOW AN OFFICIAL AVANTGO CHANNEL!
To easily add IDM to your PDA click here
Events
[
Home | Discussion Forum | How Do I... |
Lotus Notes Intranets |
Microsoft SharePoint |
Products |
Shopping
]
