Published: Wednesday, May 15, 2002
Creating Multisheet Excel Spreadsheets with Office Web Components (OWC) for XP
By J. Christian Reed
Creating Excel Spreadsheets through an ASP Page
By Scott Mitchell
This article discusses how to create a multisheet Excel spreadsheet using Office Web Components (OWC)
for Office XP. Before reading this article you may wish to know of the various ways one can
have an ASP page create an Excel application, so I thought it might be worthwhile to list some of
the more popular techniques.
One technique is to simply set the Response.ContentType property to
application/vnd.ms-excel and sending simple HTML that Excel can interpret. This technique
is rather limiting in that the generated Excel spreadsheet is bound to be very simple (that is you
cannot use formatting or insert formulas, etc.). Read
Dynamic Excel Reports with ASP
for more information on this technique. This option is nice, though, because it only requires that
the client have Excel installed on their machine, not the server.
Another approach, in which only the client must have Excel installed, is to use SoftArtisan's
ExcelWriter component. This
component generates an Excel spreadsheet that can have the full spreadsheet formatting and function
support without requiring Excel be installed on the Web server. However this component is not
free, costing either $199 or $299 for the standard a professional versions (as of May 2002).
If Excel is installed on the Web server you have another two (free) options. One is to use syntax like
CreateObject("Excel.Application"), which uses Office Automation to create an Excel spreadsheet
that you can then manipulate through your ASP page. While this approach gives you full access to the
many features of Excel, it is slow, especially if you expect any sort of audience attempting
to simultaneously create spreadsheets. (For more information on this technique check out
this code example.
A better approach is to use Office Web Components (OWC), which is highlighted at
Creating Excel Spreadsheets with Office Web Components (OWC).
For Excel 2000 I was able to create worksheets with many of Excel's advanced features, but the
documentation is anything but ample. While I've not read it, I've heard a good starting place is
Programming Microsoft
Office 2000 Web Components. Furthermore, I've not tried the OWC for Office XP, but they appear
to support many more features, as this article shows.
Now, without further delay, here is the article, showing how to create multisheet spreadsheets using
OWC for Office XP. As is the case with OWC, for this article's examples to work both the client and
server will need to have Office XP installed on it...
Introduction
When I started my last project I thought it would be a cake walk because every requirement seemed
so commonplace and everyday. Basically I needed to allow my users (through a Web page) to select
parameters (from combo boxes) that drove a select statement which in term returned its output on
a formatted multi-sheeted Excel spreadsheet. I had been there and done that in VB and so I assumed
that porting this knowledge to ASP would be no big deal. Well it wasn't as straight forward as I
had hoped.
I learned quickly from personal trial and error that using the usual Excel ActiveX component of
CreateObject("Excel.Application") was not going to work server-side and definitely not
client-side (my reports were too big). It was unstable and very slow. After reading on what
Microsoft had to say about this in an article titled
Microsoft
Office Development with Visual Studio,
I was convinced that I needed something else. I looked at the 4Guys article
Creating Excel Spreadsheets with Office Web Components (OWC) and
I thought I had a solution until I discovered that OWC only allowed for one worksheet in an Excel
workbook!
So I went back to CreateObject("Excel.Application") but was not pleased with its creepingly
slow speed.
Office Web Components for Office XP to the Rescue
After reading Microsoft's many discouragements and disclaimers regarding the use of
server-side automation of office applications I happened to discover that the Office Web Components
of Microsoft Office XP (OWC10) allow you to create server-side spreadsheet objects with multiple sheets!
Read more about Office XP Web Components at
http://www.microsoft.com/Office/developer/platform/webcomp.asp.
So after locating an example and playing around with it a little, I was able to complete my project.
Instead of boring you with a heavy example taken form that code I made up this little ASP OWC10 example.
Because you are compiling an Excel Workbook here you will want to buffer your server output until
it has completed the spreadsheet. To get Excel to open up this XML spreadsheet in the client's
browser we switch the ContentType to the Excel MIME type.
<%@ Language=VBScript %>
<%
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
|
Declare your variables set up your Excel Workbook OWC object. Most documentation and examples refers
to this object as a Spreadsheet or Worksheet. I stay away from this name
because it gets me confused about the difference between this object and the sheets objects
that I am going to use in my code. Notice that we use OWC10 here (Office XP Web Components);
you will have to have Office XP installed on your Web server for this code to work.
Dim objWorkbook, c, objSheet1, objSheet2, intCol, intRow
Set objWorkbook = CreateObject("OWC10.Spreadsheet")
|
Set up your Worksheet objects that are in your Workbook. I rename the
sheets here and I also delete the third sheet just to show that it can be done. The method
.add works here too, but your objWorkbook will start with the default three
Worksheets already in it so there is no need to add any if you need three or less. The
other nice thing about OWC is that it let's you use the Excel constants. When I played around with
Excel.Application in ASP I didn't have that luxury.
Set objSheet1 = objWorkbook.Worksheets(1)
objSheet1.Name = "One"
Set objSheet2 = objWorkbook.Worksheets(2)
objSheet2.Name = "Two"
objWorkbook.Worksheets(3).Delete
Set c = objWorkbook.Constants
|
Now I am going to use a Sub to write to my two Excel Worksheets. Nothing too fancy here:
Sub WriteData(pintDivisor, pstrColor)
With objWorkbook.ActiveSheet
For intRow = 1 To 100
For intCol = 1 To 10
.Cells(intRow, intCol).Value = (intRow - intCol) / pintDivisor
If .Cells(intRow, intCol).Value Mod 3 = 0 Then
.Cells(intRow, intCol).Interior.Color = pstrColor
End If
Next
.Cells(intRow, 11).Value = "= I" & CStr(intRow) & "+J" & CStr(intRow)
If intRow Mod 2 = 0 Then .Cells(intRow, 11).Interior.Color = "LightGray"
Next
.Columns("A:D").AutoFilter
End With
End Sub
|
We can then activate our Worksheets and write some data to the worksheets by calling the
WriteData Sub, like so:
objSheet1.Activate
Call WriteData(3, "Red")
objSheet2.Activate
Call WriteData(7, "Blue")
|
Finally we need to have the user's browser load Office XP and display the Excel worksheet. We do this
by simply dumping out the XML data. (Recall that at the top of our ASP page we set the
Response.ContentType property to the Excel MIME type, so the browser knows to launch Excel.)
objWorkbook.DisplayToolbar = False
objWorkbook.AutoFit = True
objSheet1.Activate
'Write the XML data to the client
Response.Write objWorkbook.XMLData
Response.End
'Clean up your variables!
Set objWorkbook = Nothing
Set c = Nothing
Set objSheet1 = Nothing
Set objSheet2 = Nothing
Set objSheet3 = Nothing
|
The client will have to have Excel XP installed on their computer or they will just see an Excel
Spreadsheet full of XML output that is completely unusable. If they have Excel XP installed they will
be prompted once the spreadsheet is created to open or save it. If they open it or save it they can
later use Excel to save it as an Excel Workbook format (.xls) and they will have all of
the formatting that you originally showed them.
Also for displaying straight from the server to client upon completion of the Excel Workbook the
following properties are available: HTMLData, CSVData, and
XMLData. XMLData is the only one that saves successfully with all formatting straight
to Excel Workbook file format. The HTMLData isn't bad but it is just HTML and so
AutoFilter and other Excel specific things won't work.
Conclusion
This worked great for me. This is the best thing I have seen from Office XP as of yet. I never could
figure out how to get the FreezePanes property to work (please email me if you figure
it out), but everything else seemed to work just as posted.
If you are serious about using Office Web Components for Office XP I highly recommend that you download
and install the Office XP Web
Component Toolpack. It comes with an invaluable Office XP Web Components Object Model Reference
and some great examples that you won't find elsewhere.
Good luck and Happy Programming!
By J. Christian Reed
Attachments
Download the complete source code (in text format)
