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

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.

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!