When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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!

    - continued -

    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
            iColOffset = 2
          End If
        End Property
        Public Property Let RowOffset(iRowOff)
          If iRowOff > 0 then
            iRowOffset = iRowOff
            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!

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article