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

In Part 1 we listed the new enhancements to the ExcelGen class and looked at the updated GenerateWorksheetFromRecordset method. In this part we'll look at how to stream Excel spreadsheets to the user!

Streaming Excel Spreadsheets to the Client
In our previous incarnation of the ExcelGen class, there was only one method of the class that could be used for delivering a dynamically-created Excel spreadsheet to a user. This method, SaveWorksheet, expected a single parameter, strFileName, and saved the Excel spreadsheet to the specified location on the Web server's filesystem. For the user to access this spreadsheet I suggested that you then display a hyperlink to the spreadsheet for the user to click on.

This had some problems. First of all, it required the developer to specify a filename for the spreadsheet. If you choose to use the same filename for all of the different spreadsheets you created, one user may visit the ASP page to generate one spreadsheet and have it generated, but before she can click on the hyperlink another user may visit a page that generates a different spreadsheet (but saves it with the same filename). Our first user, then, when finally clicking on the spreadsheet hyperlink, will see a different spreadsheet than the one that was intended to be created for her! Oh no!

If you opted to pick a random filename to save each Excel spreadsheet then, you will have alleviated the problems associated with multiple people potentially creating different spreadsheets simultaneously. This is accomplished in the ExcelGen class by first defining a private variable strTmpDir that contains the directory name you wish to save the spreadsheets in. This private variable is then set to a hard-coded value in the Class_Initialize() event handler.

Class ExcelGen ... Private strTmpDir Sub Class_Initialize() '... some code omitted for brevity ... 'Initialize strTmpDir strTmpDir = "C:\Inetpub\wwwroot\spreadsheets\" End Sub ... End Class

Now we can pick a random file name by using the GetTempName method of the FileSystemObject like so:

Dim tmpFileName, objFSO Set objFSO = Server.CreateObject("Scripting.FileSystemObject") tmpFileName = strTmpDir & objFSO.GetBaseName(objFSO.GetTempName) & ".xls"

Note that, since strTmpDir is a private member variable, the above code would need to appear in a method of the ExcelGen class...

At the completion of the above code, tmpFileName would equal something like: C:\Inetpub\wwwroot\spreadsheets\rad02922.xls. Great, now we have a random file name to use when saving our Excel spreadsheets! (To learn more about generating random file names check out the FAQ: How can I create a file with a random file name?) However, there is still a small problem... as more and more users visit your site and generate spreadsheets, piles of spreadsheets will be created on your Web server's filesystem! Eek. We need to think of a clever way to remove old spreadsheets...

First of all, what defines an "old" spreadsheet, one that can be deleted? For this class I am going to assume a spreadsheet that has been sitting on the Web server's filesystem for more than ten minutes is "out-of-date" and can be deleted. I shamelessly borrowed the code from Bret Hern's Charting with Office Web Components article. The CleanUpSpreadsheets() method loops through the strTmpDir folder, removing any files that were created more than 10 minutes ago. (Note that this method will remove any file older than ten minutes! If you are planning on saving these temporary spreadsheets in a directory where other files may be saved as well you should check to ensure that the file has the .xls extension before deleting it.)

Class ExcelGen ... Private Sub CleanUpSpreadsheets() Dim objFS Dim objFolder Dim objFile set objFS = Server.CreateObject("Scripting.FileSystemObject") set objFolder = objFS.GetFolder(strTmpDir) 'Loop through each file in the strTmpDir folder for each objFile in objFolder.Files 'Delete Spreadsheets older than 10 minutes If DateDiff("n", objFile.DateLastModified, now) > 10 then objFS.DeleteFile strTmpDir & objFile.Name, True end if next set objFolder = nothing set objFS = nothing End Sub ... End Class

Note that the CleanUpSpreadsheets() is a Private method. This means that only methods within the ExcelGen class can call this method! Now, you may be wondering why I made that method Private. Well, the only time I want the method to be called is when the instance of the class is terminated (that is when the developer who is using our class sets it to Nothing or the class instance goes out of scope). Therefore, in the Class_Terminate() event handler we have:

Class ExcelGen ... Sub Class_Terminate() Set objSpreadsheet = Nothing 'Clean up 'Remove out of date spreadsheets CleanUpSpreadsheets End Sub ... End Class

Now, each time that we use the class to generate a spreadsheet the CleanUpSpreadsheets() method will fire, deleting any out-of-date spreadsheets from the Web server's filesystem. Neat!

You may be wondering why I am mentioning all of this when I told you that I was going to be talking about how to stream the Excel spreadsheet to the client. Well, I'm right about to get to that. The method for streaming the spreadsheet uses the concepts described above, so I thought it was important to tackle those first. Mosey on over to Part 3 to learn about streaming the Excel spreadsheet to the user (I swear we'll get to it there!) :-)