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
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Wednesday, March 07, 2001

Enhancing the ExcelGen Class (for Creating Excel Spreadsheets), Part 3

By Scott Mitchell


  • Read Part 1
  • Read Part 2

  • In Part 2 we examined how to use the FileSystemObject to generate random file names for each spreadsheet. We also looked at how to have the out-of-date spreadsheets deleted every so often to ensure that our Web server's filesystem doesn't become overbloated with old spreadsheet files. In this part we'll (finally) look at how to stream the content of the spreadsheet to the user.

    - continued -

    '

    Ideally we would like to have the user visit a single ASP page that would do a couple of things:

    • Generate a dynamic Excel spreadsheet based on some database query
    • Stream this spreadsheet directly to the client's browser

    If the user has Excel or Excel Viewer installed he will see the spreadsheet in her browser. We can accomplish something very close to this. The StreamWorksheet() method accomplishes this, sort of. The method does two things: first it saves the spreadsheet to the Web server's filesystem using the random file name technique discussed in Part 2 of this article; next, it uses a Response.Redirect to send the user to this newly created Excel spreadsheet. This has the effect of seemingly streaming the Excel spreadsheet to the user's browser. The code for the StreamWorksheet() method follows below:

    Class ExcelGen
      ...
    
      Sub StreamWorksheet()
        'Save the worksheet in a temporary file
        Dim strFileName, objFSO
    
        Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
        strFileName = strTmpDir & objFSO.GetBaseName(objFSO.GetTempName) & ".xls"
        Set objFSO = Nothing
    
        if SaveWorksheet(strFileName) then
          Response.Redirect strFileName
        end if
      End Sub
    
      ...
    End Class
    

    Pretty simple, eh? At the end of this article you can grab the complete code for the ExcelGen class. This code should be placed in an include file for easy access. The code below demonstrates a quick example of generating a dynamic Excel spreadsheet based on a database query and streaming the spreadsheet to the visitor. (Note that it assumes that the ExcelGen class has been placed in an include file named /include/ExcelGen.class.asp.)

    <!--#include virtual="/include/ExcelGen.class.asp"-->
    <%
      'Grab the Recordset we wish to display in a spreadsheet
      Dim objRS, strSQL
      strSQL = "SELECT 'Title ID'=title_id, Title, " & _
               "'Book Type'=type, Price, Advance " & _
               "FROM titles"
      
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open strSQL, strSQL, "DSN=MyDSN;UID=sa;PWD="
    
    
      'Create an instance of the ExcelGen class
      Dim objExcel
      Set objExcel = New ExcelGen
    
      'Set the Row/Column offsets
      objExcel.RowOffset = 3
      objExcel.ColumnOffset = 1
    
      'Dump the recordset contents to the spreadsheet and 
      'AutoFit the columns
      objExcel.GenerateWorksheetFromRecordset objRS, True
    
      'Stream the spreadsheet to the user
      objExcel.StreamWorksheet
    
      'Clean up!
      Set objExcel = Nothing
    
      objRS.Close
      Set objRS = Nothing
    %>
    

    Well, that wraps up this article! Be sure to check out the related articles and support code links below. Happy Programming!

  • By Scott Mitchell


    Attachments:

  • Download the current ExcelGen class in text format
  • Download the first ExcelGen class in text format
  • Read Creating Excel Spreadsheets with Office Web Components (OWC)
  • Visit the technical docs



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