Published: Wednesday, March 07, 2001
Enhancing the ExcelGen Class (for Creating Excel Spreadsheets)
By Scott Mitchell
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)
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
|
