Creating Excel Spreadsheets with Office Web Components (OWC), Part 2

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.