Published: Wednesday, February 28, 2001
Creating Excel Spreadsheets with Office Web Components (OWC), Part 2
By Scott Mitchell
Read Part 1
In Part 1 we looked at some very basic code for
creating, populating, and saving an Excel spreadsheet all from an ASP page! In this part
we'll look at the implementation of a class that will allow you to dump the results of a
Recordset object to a spreadsheet!
Creating the Class
I chose to encapsulate the complexity of creating/populating/saving a spreadsheet into a
VBScript class. That means that you will need VBScript version 5.0 or higher installed on
your Web server. To find out what version of VBScript you are currently using, check out:
Determining the Server-Side Scripting Language and
Version. Also, for more information on the ins and outs of classes be sure to read
Mark Lidstone's excellent article: Using Classes within
VBScript.
Our class contains three private properties: objSpreadsheet, iColOffset,
and iRowOffset. In the Class_Initialize() event handler,
an instance of the Spreadsheet COM component is instantiated and referenced by
objSpreadsheet; iColOffset and iRowOffset, which
specify the how many columns over and rows down we should start inserting the database results,
are initialized to values of 2.
Class ExcelGen
Private objSpreadsheet
Private iColOffset
Private iRowOffset
Sub Class_Initialize()
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")
iRowOffset = 2
iColOffset = 2
End Sub
Sub Class_Terminate()
Set objSpreadsheet = Nothing 'Clean up
End Sub
...
End Class
|
Next, two Property Let constructs are defined to allow users of this class to
programmatically set the row and column offsets. These Property Let statements
ensure that the offsets attempted to be set are greater than zero.
Class ExcelGen
...
Public Property Let ColumnOffset(iColOff)
If iColOff > 0 then
iColOffset = iColOff
Else
iColOffset = 2
End If
End Property
Public Property Let RowOffset(iRowOff)
If iRowOff > 0 then
iRowOffset = iRowOff
Else
iRowOffset = 2
End If
End Property
...
End Class
|
Our ExcelGen class contains only two methods: one to insert the contents of a
Recordset into the spreadsheet and another to save the spreadsheet to an Excel file on the
Web server's filesystem. We'll examine both of these methods, as well as how to use this
class through an ASP page, in Part 3.
Read Part 3!
