Creating Multisheet Excel Spreadsheets with Office Web Components (OWC) for XPBy 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...
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
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
So I went back to
CreateObject("Excel.Application") but was not pleased with its creepingly
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.
Declare your variables set up your Excel Workbook OWC object. Most documentation and examples refers
to this object as a
Worksheet. I stay away from this name
because it gets me confused about the difference between this object and the
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.
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.
Now I am going to use a Sub to write to my two Excel Worksheets. Nothing too fancy here:
We can then activate our
Worksheets and write some data to the worksheets by calling the
WriteData Sub, like so:
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.)
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:
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.
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!