Enhancing the ExcelGen Class (for Creating Excel Spreadsheets)By Scott Mitchell
|Before you Begin Reading...|
|This article is a sequel to a previous article: Creating Excel Spreadsheets with Office Web Components (OWC). The previous article examines how to use Office Web Components to create an Excel spreadsheet on the fly through an ASP page. This article aims at building upon the previous one, so if you've not yet read Creating Excel Spreadsheets with Office Web Components (OWC), please do before beginning this article. (A related article on creating graphs on the fly using the Office Web Components can be found here: Charting with Office Web Components.)|
In this article we will extend the
ExcelGen class that was created in a previous
article here on 4Guys: Creating Excel
Spreadsheets with Office Web Components (OWC). This class utilizes Microsoft's
Office Web Components which ship with Office 2000. Since we are going to be creating
Excel documents on the Web server and streaming them to the user, both the client
and the Web server will need to have Office 2000 installed (although I guess, in theory, the
client would only need to have the free Excel
In this article we will discuss the following enhancements made to the
- A facility to stream the dynamically created spreadsheet to the user (in the first
incarnation of the
ExcelGenclass an ASP page saved the dynamically created spreadsheet to the Web server and provided a hyperlink for the user to click to download the file. Now the user can have the document streamed directly to his or her browser!)
- Fancier output when displaying the contents of a Recordset (the name of each column for
the Recordset is made both bold and underlined)
- A means to create spreadsheets with random filenames so that multiple people can
generating different spreadsheets simultaneously (in the first
incarnation of the
ExcelGenclass the developer had to explicitly specify the filename to save the Excel spreadsheet as)
- A means to clean up out-of-date spreadsheets still residing on the Web server's filesystem (after a user is streamed his or her spreadsheet, a hard copy of the spreadsheet still remains on the Web server's filesystem. This enhancement periodically (and automatically) removes out-of-date spreadsheets)
|Microsoft has some pretty strict licensing issues on using Office Web Components in the Internet-world (as well as on an intranet). Before you begin using Office Web Components on your Web site be sure to read Microsoft's Licensing Agreement for OWCs.|
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!)
In the first incarnation of the
ExcelGen class the
method accepted a single parameter: a Recordset whose contents we wanted to dump to a spreadsheet.
The contents of the Recordset were simply placed on the spreadsheet without any nice formatting.
In this new incarnation of
GenerateWorksheet has been
GenerateWorksheetFromRecordset and accepts two parameters: a Recordset and
a Boolean value,
bolAutofitColumns is True,
the width of all of the columns in the spreadsheet are adjusted to automatically fit the longest
input. Also, the title of each column is both bolded and underlined.
The new and improved
GenerateWorksheetFromRecordset method can be seen at the bottom of this page.
Now that we've examined the enhancements for
turn our attention to a much more pressing issue: how to stream the content of the Excel spreadsheet
to the user! We'll examine this in Part 2.