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.)

- continued -

Introduction
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 Viewer installed).

In this article we will discuss the following enhancements made to the ExcelGen class:

A facility to stream the dynamically created spreadsheet to the user (in the first incarnation of the ExcelGen class 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 ExcelGen class 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)

Licensing Issues

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.

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!)

Fancier Output
In the first incarnation of the ExcelGen class the GenerateWorksheet 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 ExcelGen, GenerateWorksheet has been renamed to GenerateWorksheetFromRecordset and accepts two parameters: a Recordset and a Boolean value, bolAutofitColumns. If 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 GenerateWorksheetFromRecordset, let's 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.

Read Part 2

Class ExcelGen ... Sub GenerateWorksheetFromRecordset(objRS, bolAutofitColumns) 'Populates the Excel worksheet based on a Recordset's contents 'Start by displaying the titles If objRS.EOF then Exit Sub Dim objField, iCol, iRow, iMaxColVal iCol = iColOffset iRow = iRowOffset For Each objField in objRS.Fields objSpreadsheet.Cells(iRow, iCol).Value = objField.Name 'Make the column bold and underlined objSpreadsheet.Cells(iRow, iCol).Font.Bold = True objSpreadsheet.Cells(iRow, iCol).Font.Underline = True iCol = iCol + 1 Next 'objField 'Save the total number of columns we have for later use iMaxColVal = iCol - 1 'Display all of the data Do While Not objRS.EOF iRow = iRow + 1 iCol = iColOffset For Each objField in objRS.Fields If IsNull(objField.Value) then objSpreadsheet.Cells(iRow, iCol).Value = "" Else objSpreadsheet.Cells(iRow, iCol).Value = objField.Value End If iCol = iCol + 1 Next 'objField objRS.MoveNext Loop If bolAutofitColumns then 'autofit columns 'Loop from our offset to the max column position For iCol = iColOffset to iMaxColVal objSpreadsheet.Columns(iCol).AutoFitColumns Next End If End Sub ... End Class