To read the article online, visit http://www.4GuysFromRolla.com/webtech/030701-1.shtml

Enhancing the ExcelGen Class (for Creating Excel Spreadsheets)

By Scott Mitchell


Before you Begin Reading...
This article is a sequel to a previous article: Creating Excel Spreadsheets with Office Web Components (OWC). The previous article examines how to use Office Web Components to create an Excel spreadsheet on the fly through an ASP page. This article aims at building upon the previous one, so if you've not yet read Creating Excel Spreadsheets with Office Web Components (OWC), please do before beginning this article. (A related article on creating graphs on the fly using the Office Web Components can be found here: Charting with Office Web Components.)

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)

Licensing Issues
Microsoft has some pretty strict licensing issues on using Office Web Components in the Internet-world (as well as on an intranet). Before you begin using Office Web Components on your Web site be sure to read Microsoft's Licensing Agreement for OWCs.

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
    


  • Article Information
    Article Title: 4GuysFromRolla.com - Enhancing the ExcelGen Class (for Creating Excel Spreadsheets)
    Article Author: Scott Mitchell
    Published Date: Wednesday, March 07, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/030701-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers