HOME · SOFTWARE · WEB DEVELOPMENT · ARTICLES · ABOUT US · CONTACT US

Generate Excel files

By: AdvanCode [2004-12-01]
Read: 39816 times

This article has been chosen Tutorial of the week at ASP Experts.com (Apr 29, 2002)

Generating Excel files from web pages or other applications is a major subject in many articles. I'd like to present here some solutions, part of them, already presented and well known. Some of them require MS Excel to be installed on the server, and other not.

Let's enumerate a few solutions:

using Automation on the server-side
using VBA on the client-side
external (commercial) components
using HTML (ASP)
using XML
using PERL
using ADO
writing binary files

A solution that require Excel to be installed is made by using Automation. Even from ASP pages it could be created external COM servers (as Excel) and use it thru automation. Some versions of IIS may require an extra setting. You can do this by writing the following code in a VBS file and execute it (for instance iis.vbs):

Set ow=GetObject("IIS://LocalHost/W3svc")

ow.Put "AspAllowOutOfProcComponents",True
ow.SetInfo

Set ow=Nothing
MsgBox "Done"

Then, you can create Excel objects thru automation. First, create some include files which contain the opening (OpenConnection.asp) and closing (CloseConnection.asp) of a connection and a file having all Excel constants called "excel.inc" (these values are declared in Excel type-library, which isn't available in ASP), as bellow:

<%
xlAutomatic = -4105

xlCenter=-4108
xlLeft = -4131
xlRight = -4152
xlBottom = -4107

xlNone = -4142

xlDiagonalDown = 5
xlDiagonalUp = 6
xlEdgeLeft = 7
xlEdgeTop = 8
xlEdgeBottom = 9
xlEdgeRight = 10
xlInsideVertical = 11
xlInsideHorizontal = 12

xlThin = 2
xlThick = 4
%>

Then, try this code (excel1.asp):

<!--#include file = "../Include/OpenConnection.asp"-->
<!--#include file = "../Include/EXCEL.INC"-->
<HTML>
<%
Dim rsAsp
Set rsAsp = Server.CreateObject("ADODB.Recordset")

Dim ex
Set ex=Server.CreateObject("Excel.Application")

Dim fs,f
Set fs=Server.CreateObject("Scripting.FileSystemObject")
f = fs.GetBaseName(fs.GetTempName()) & ".xls"

ex.Workbooks.Add

ex.ActiveSheet.Range("E1").Value = "Something"

ex.ActiveSheet.Range("A1:C1").Select
ex.Selection.Merge
ex.Selection.Value = "Title"
ex.Selection.Interior.ColorIndex=15
ex.Selection.Font.Bold=True
ex.Selection.Font.Size=12
ex.Selection.Font.ColorIndex=2

ex.ActiveSheet.Range("A2:C2").RowHeight = 24
ex.ActiveSheet.Range("A2:C2").HorizontalAlignment = xlCenter
ex.ActiveSheet.Range("A2:C2").VerticalAlignment = xlCenter
ex.ActiveSheet.Range("A2:C2").WrapText = True

ex.ActiveSheet.Columns("A:A").ColumnWidth = 10

rsAsp.Open "SELECT * FROM Table1", conn
Dim i
i=3

While not rsAsp.EOF
  ex.ActiveSheet.Cells(i,1).Value = rsAsp.Fields(0)
  ex.ActiveSheet.Cells(i,2).Value = rsAsp.Fields(1)
  rsAsp.MoveNext 
Wend

ex.ActiveWorkbook.SaveAs Server.MapPath(".") & "\" & f
ex.Quit

Set rsAsp = Nothing
Set ex = Nothing
Set fs = Nothing
%>
<body onload="document.location.href='<% =f %>'">
</HTML>
<!--#include file = "../include/CloseConnection.asp"-->

I used FileSystemObject to create temporary names for Excel files.

An advantage of this method is that Excel could be completely automated thru its objects.

But there are many drawbacks. First of all, read this page regarding the problems which can occur when Excel is installed on the server (needed for automation). Then, it must devise a solution to delete all those xls files created on the server. Also, is slow, because Excel is running in a separate process. And every time there is an error the page hangs up, because an message box will be issued on the server.

Let's see another solution, having Excel installed on the client computer (browser). However, on client side, Excel is needed, to view excel files, so we can use it to generate the file.

Here, will be facing other problems: how to pass parameters from ASP page to Excel files? And how can we safely access the datasource from the client computer?

Second problem could be solved using "MSDFMAP.Handler" or other solutions.

For first answer there is a solution for Internet Explorer, passing them by clipboard. Let's see the page which open the XLS file (report.asp):

<% Response.Expires =-2000 
On Error Resume Next
server_name="http://localhost"
%>
<HTML>
<HEAD>
<script language=JavaScript>
function start() {
	window.clipboardData.setData('Text','<% 
	select case Request.QueryString("xls")  'name of xls file
	case "EXCEL1"
		Response.Write Request.QueryString("xls") & "|" & _
		server_name & "|" & _
		Request.QueryString("Param1") & "|" & _
		Session("UserName") & "|" & _
		Session("Pass")

	End Select
		%>');
	location.href='<%= Request.QueryString("xls") %>.xls';
}
</script>
</HEAD>
<BODY onload="start()">
</BODY>
</HTML>

Then, in Excel1.xls we could query all parameters passed from ASP (separated by "|") and built the Excel doc against those parameters. In this example I use information from a database. In Excel1.xls you have to add at References "MS Forms 2.0" (to access the clipboard) and ADO or RDS.

Sub Auto_Open()
On Error Resume Next

Application.ScreenUpdating = False

'get the params from clipboard
Dim s As String
Set MyData = New DataObject
MyData.GetFromClipboard
If MyData.GetFormat(1) Then
    s = MyData.GetText()
    MyData.SetText " ", 1
    MyData.PutInClipboard
End If

If Left$(s, 6) = "EXCEL1" Then  'security check
	'get params from s variable

	'open the database and build the xls doc

End If

Application.ScreenUpdating = True

End Sub 'Auto_Open

As you can see, the xls file could be writen by code. When the file is re-open it won't be re-build because the parameters aren't in the clipboard (it's a good chance that Left$(s, 6) <> "EXCEL1").

The Excel doc could be build on the client-side in this way but still remains many problems. Every time a doc is open a security warning is issued because the doc is containing macros (Auto_Open()). Also, it could be security problems when accessing database remotely and ADO or RDS must be installed on client-computers.

A more reliable solution is by using external (commercial) components as ExcelWriter from SoftArtisans or ExcelSpeedGen. One word: it costs!

Also, there are some open-source projects for the same reason: to generate Excel docs. See www.sourceforge.net/projects/poi or jakarta.apache.org/poi/hssf/index.html (the same).

Of course, you've seen many articles(aCat,aTitle,aAuthor,aDate,aContent,aViewed) on the net to generate Excel documents using HTML, as samples from Microsoft:

Basicaly, any HTML file built like the one below is interpreted as Excel file, just to change the extension to *.xls (for instance excel2.xls):

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="application/vnd.ms-excel">
</HEAD>
<BODY>
<TABLE cellspacing=1 cellpadding=1 border=1>
	<TR>
		<TD align=center colspan=4>
	    <FONT size=4 color=blue>Price Quote</FONT></TD>
	</TR>
	<TR>
		<TD><B>Product</B></TD>
		<TD><B>Price</B></TD>
		<TD><B>Quantity</B></TD>
		<TD><B>Total</B></TD>
	</TR>
	<TR>
		<TD>Widget A</TD>
		<TD>36</TD>
		<TD>2</TD>
		<TD>=B3*C3</TD>
	</TR>
	<TR>
		<TD>Widget B</TD>
		<TD>45</TD>
		<TD>3</TD>
		<TD>=B4*C4</TD>
	</TR>
	<TR>
		<TD></TD>
		<TD></TD>
		<TD><B>Total</B></TD>
		<TD>=Sum(D3:D4)</TD>
	</TR>

 </TABLE>
</BODY>
</HTML>

The key is HTTP-EQUIV="Content-Type" CONTENT="application/vnd.ms-excel" in <HEAD> part. This will be corectly interpreted if Excel is installed on client computer.

One major drawback is that it could be only one worksheet per document (not multiple-sheets). This solution works with both Excel 97, 2000 and 2002.

Starting with Excel 2000 (and Excel 2002) documents could be saved and loaded as XML files. Using this method you can build directly xls docs from xml files, having multiple sheets.

A good start is msdn.microsoft.com/library/en-us/dnexcl2k2/html/odc_xmlss.asp?frame=true where you can find XML spreadsheet tag hierarchy explained (or msdn.microsoft.com/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp?frame=true). Also you can see msdn.microsoft.com/library/en-us/dnexcl2k2/html/odc_xlflatnr.asp?frame=true and some practical solutions as the following:

The problem is that all these are working with Excel 2000 or 2002, and isn't working with previous versions of Excel (mainly Excel 97). If the user doesn't have these newer versions of Excel he can't view the docs.

To write XLS files directly, without MS Excel installed it could be another solution, too. For instance, it could be used Perl. There is an article describe this method here. Or you can see this solution using Perl. Another resources on the same theme could be found here (LAOLA). I haven't tried them and I haven't any clue how reliable they are.

And now, it's time to see a solution that could meet your needs, using ADO. You can read more about this solution at:

But I'll change it to use directly from a web browser (both IE & Netscape). The components used are ADO and FileSystemObject. The version of ADO must be at least 2.5 to use ADODB.Stream.

First of all, we create a folder with all Excel documents used as templates (here called "templates" in working folder). And we create a temporary folder in working folder, too (in this example called "tmp"). It could be used Windows temporary folder (usually c:\windows\temp), but to keep track of all created documents we used our temporary folder. In our example, the template for XLS doc is templates\products.xls.

Also, we have an Access database as the source of informations to be wrote in XLS documents (excel_db1.mdb)

Then try the code as bellow in products.asp:

<%
Response.Expires = -2000
Response.Buffer = TRUE

'--- Create temporary file
Dim path
path=Server.MapPath(".")

Dim fs, file
Set fs=Server.CreateObject("Scripting.FileSystemObject")
file = path & "\tmp\" & fs.GetTempName
fs.CopyFile path & "\templates\products.xls", file

'--- Modify Excel template
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & file & _
               ";Extended Properties=""Excel 8.0;HDR=NO;"""

Dim oProdConn, oProdRS
Set oProdConn = Server.CreateObject("ADODB.Connection")
Set oProdRS =  Server.CreateObject("ADODB.Recordset")
oProdConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
                path & "\excel_db1.mdb"
                    
oProdRS.Open "SELECT * FROM Products", oProdConn
                   
Dim oRS 
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open "Select * from Items", oConn, 1, 3 
         '1=adOpenKeyset, 3=adLockOptimistic
    
Do Until oProdRS.EOF
    oRS.AddNew
    For i = 0 To 2
        oRS.Fields(i).Value = oProdRS.Fields(i).Value
    Next
    oRS.Update
    oProdRS.MoveNext
Loop
    
oProdRS.Close
Set oProdRS = Nothing
oProdConn.Close
Set oProdConn = Nothing
    
oConn.Close
Set oConn = Nothing

'--- Build the stream to client
Response.ContentType = "application/vnd.ms-excel"

Dim vntStream

Set oMyObject = Server.CreateObject("ADODB.Stream")
oMyObject.Type = 1 'adTypeBinary
oMyObject.Open
oMyObject.LoadFromFile file 
vntStream = oMyObject.Read

Response.BinaryWrite(vntStream)

'--- Delete tmp file & close all
oMyObject.Close
fs.DeleteFile file

Set oMyObject = Nothing
Set fs = Nothing

Response.End
%>

The code is almost self-explanatory. Into the line:
oRS.Open "Select * from Items"
the table "Items" isn't a real table, but a named area in Excel file (Insert | Name | Define...), and then made hidden (line 2 isn't visible in XLS doc).

It's important to notice that MS Excel doesn't have to be installed on the server and all temporary files are deleted.

The user will get an Excel file to download, both in IE and NN.

You can download all this sample here (11 kb).

Last solution presented here, is to write directly Excel binary files (BIFF). The problem is that the format of Excel files aren't documented. All you coud find are some examples from Microsoft as:

Also, you can download MS Excel 97 SDK where is a useful program called BIFFVIEW.EXE, to see the internal structure of a binary Excel file. But more interesting could be this PDF document which contains part of Excel file format.

You can see an example of using this method, at work, in VB here. It could be opened with newer versions of Excel (backward compatibility is working).

A big drawback is that this format is used for Excel 2.1 and is just 1 sheet per doc. You can't create multi-sheets documents.

Other solution is to try Microsoft Office Web Components. You can read more here (an example).

Last thing I want to mention is MS Office Developer page from MSDN, where it could be interestin things regarding Excel programming.

Also, it could be another solutions for this problem. It's up to you to apply one of these solutions or any other you consider will suit you needs.

« Go back

»TheBabelTower.org

Advertise Now - Pay Later, only for guaranteed results. More ...