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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
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 3

By Scott Mitchell


  • Read Part 1
  • Read Part 2

  • In Part 2 we looked at the private member variables of our class as well as our Class_Initialize() and Class_Terminate() event handlers and our Property Let statements. In this final part we'll examine the two methods of the ExcelGen class and look at how to use this class through an ASP page!

    - continued -

    '

    Creating the Methods for the ExcelGen Class
    Only two methods are needed for our class. The first one, GenerateWorksheet, accepts a single parameter: a populated Recordset object. This method then loops through the Recordset, transferring its contents to objSpreadsheet's Cells. Note that both the data from the Recordset and the names of the columns in the Recordset are outputted to the Excel spreadsheet.

    Class ExcelGen
        ...
    
        Sub GenerateWorksheet(objRS)
          'Populates the Excel worksheet based on a Recordset's
          'contents.  Check to make sure we have data to show
          If objRS.EOF then Exit Sub
    
          Dim objField, iCol, iRow
          
          'Set the iCol/iRow vars to the proper offsets
          iCol = iColOffset
          iRow = iRowOffset
    
          'Display the names of the columns in the Recordset
          For Each objField in objRS.Fields
            objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
            iCol = iCol + 1
          Next 'objField
    
          'Display all of the data
          Do While Not objRS.EOF
            iRow = iRow + 1
            iCol = iColOffset
    
            For Each objField in objRS.Fields
              'If the column contains a null value, insert blank string
              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
        End Sub    
    
        ...
    End Class
    

    Our last method, SaveWorksheet, accepts a single parameter, strFileName, which specifies the location to save the spreadsheet. This method returns a Boolean value: True if the file is saved successfully, False otherwise. Recall that exporting the Spreadsheet object to a physical Excel file can fail if the IUSR_machinename account has inadequate permissions.

    Class ExcelGen
      ...
    
      Function SaveWorksheet(strFileName)
        'Save the worksheet to a specified filename
        On Error Resume Next
        Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)
    
        'Return True if everthing went OK, False otherwise
        SaveWorksheet = (Err.Number = 0)
      End Function
    End Class
    

    Using the ExcelGen Class from an ASP Page
    Now that we've looked at the contents of our class, let's examine how to use it through an ASP page to create a spreadsheet containing the contents of a Recordset! It is highly recommended that you place the ExcelGen class in an include file and then use a server-side include on those ASP pages that need to utilize the class's functionality. (To learn more about server-side includes be sure to read: The Low-Down on #include.) For this example we'll assume that the ExcelGen class has been placed in the file /scripts/ExcelGen.class.asp.

    To use this class, then, we'll use a server-side include to import the contents of /scripts/ExcelGen.class.asp. Next, we'll create an instance of the class using the New keyword. Once we've created and populated a Recordset, we can call the .SaveWorksheet method to dump the Recordset's contents into an Excel spreadsheet. Finally, we need to save the contents of the spreadsheet using the .SaveWorksheet method.

    <!--#include virtual="/scripts/ExcelGen.class.asp"-->
    <%
      Dim objRS
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open "SELECT * FROM titles", "DSN=FooBar"
    
      Dim objExcel
      Set objExcel = New ExcelGen
    
      objExcel.RowOffset = 4
      objExcel.ColumnOffset = 1
    
      objExcel.GenerateWorksheet(objRS)
      If objExcel.SaveWorksheet(Server.MapPath("foo.xls")) then
        Response.Write "Worksheet saved.  " & _
                       "<a href=""foo.xls"">Download</a>"
      Else
        Response.Write "Error in saving worksheet!"
      End If
    
      Set objExcel = Nothing
    
      objRS.Close
      Set objRS = Nothing
    %>
    

    If the spreadsheet is saved successfully the user is presented with a hyperlink to download the Excel file.

    Conclusion / Caveats
    One annoying thing with the ExcelGen class is that a user must go through a two-phase step to view the contents of a Recordset through an Excel file. First, he must visit an ASP page that creates the Recordset; next, he must click on the link to the Excel file. This is a pain and something I plan on fixing in the next article on this topic (which will serve, basically, as an enhancement to the ExcelGen class).

    One major concern that should also be quickly apparent is that in the above example the Excel spreadsheet is always saved to the same file. Urg. This is bad since multiple users will be trying to access the same file and, most likely, they would be running different kinds of database queries producing varrying output. One approach is to create a unique file for every user visiting the page... but then how to we clean up old spreadsheets? We'll examine this topic in more detail in a future article...

    In the mean time, play with the code here, create your own spreadsheets, and poke around the Microsoft documentation. Happy Programming!

  • By Scott Mitchell


    Attachments:

  • Download the ExcelGen class (in text format)
  • Visit the technical docs
  • Read Enhancing the ExcelGen Class (for Creating Excel Spreadsheets)



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