Published: Wednesday, February 28, 2001
Creating Excel Spreadsheets with Office Web Components (OWC)
By Scott Mitchell
| An Upgrade to the Class... |
|---|
This article presents a class (ExcelGen) that generates an Excel spreadsheet
based on the results of a Recordset. An enhanced version of this class is available at:
Enhancing the ExcelGen Class (for Creating Excel Spreadsheets).
However, make sure you've read this article completely before moving onto the next!
|
Introduction
One of the great things about running an ASP Web site where visitors regularly contribute
articles is that, in reading/editing those article submissions, I end up learning a lot of
new things! Last week Bret Hern submitted a beautiful article on using
Charting with Office Web Components. After poking
around Microsoft's site some, I found rather terse, technical documentation on these
nifty components and soon discovered that these components can also be used to create
Excel spreadsheets via ASP code! These spreadsheets can then be saved as an Excel file for
the user to download.
In this article we will look at using the Office Web Components (OWC) to create an Excel
spreadsheet via ASP code based on the results from a database query! All of this complexity
is encapsulated in a (rather basic) class. In the upcoming weeks I plan to expound on this
class. Currently it just dumps the contents of a Recordset into a spreadsheet, but in
future weeks I'd like to show how to add nifty formatting, apply formulas, pivot tables,
and all that other jazzy stuff.
Getting Started
To get started using Office Web Components you must have (at minimum) the Office Web Components
section of Office 2000 installed on the Web server. (If you are wanting to create Excel
spreadsheets and graphs without requiring Excel's presence on the Web server be sure
to check out SoftArtisan's ExcelWriter
component.) (For more on OWC requirements and installation information check out:
Requirements for Office Web Components!)
Creating a Spreadsheet
In this article we will look only at the basics of creating a spreadsheet, setting various
cell values, and saving the spreadsheet to disk. In future articles we will look at prettying
up the display and working with some of the more advanced features...
Since the spreadsheet aspect of the Office Web Components is a simple COM object, you can
create an instance of the spreadsheet component through your ASP page just as you would create
an instance of any other COM component:
'Create an instance of the Spreadsheet component from OWC
Dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")
|
Simple enough. Once you have a Spreadsheet object to work with you can set the values of
the spreadsheet's cells using the Cells property of the Spreadsheet
object like so:
objSpreadsheet.Cells(Row, Column).Value = SomeValue
|
Finally, to save the Spreadsheet as an Excel file you must use the
Export method of the Worksheet object. (The ActiveSheet
property of the Spreadsheet object returns a valid Worksheet object
instance.) The Export method expects two parameters: a full physical file name
and an SheetExportActionEnum constant. The file name parameter specifies
the specific location to save the Excel spreadsheet; the export action indicates if the file
should be saved to disk or piped directly to Excel. Since we are running all of this code on
the server-side, if we try to pipe the spreadsheet contents directly to Excel, we will be
trying to open Excel on the Web server - not what we want to do. In fact, this setting is
only useful if you are using the Spreadsheet object as an ActiveX control, since
then it will be executing on the client's machine as opposed to on the Web server. Therefore,
when using the Export method in server-side script, always specify a value
of 0 for the export action, which indicates to the Export method to
simply save the spreadsheet to disk and to not try to pipe the contents straight to Excel.
objSpreadsheet.ActiveSheet.Export("C:\Inetpub\wwwroot\FooBar.xls", 0)
|
Keep in mind that the IUSR_machinename account must have Write permissions on
the directory that you wish to write the Excel file to. If the IUSR_machinename account
has inadequate permissions you will receive an error when trying to use the Export
method... (Check out this FAQ
for more information...)
Now that we've covered the basics of creating / filling / saving an Excel spreadsheet through
ASP, we're ready to look at a nifty Excel spreadsheet generation class I wrote that will help
this process. In Part 2 we'll examine this class in
detail!
Read Part 2!
