Enhancing the ExcelGen Class (for Creating Excel Spreadsheets), Part 2By Scott Mitchell
In Part 1 we listed the new enhancements to
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.
SaveWorksheet, expected a single parameter,
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.
Now we can pick a random file name by using the
GetTempName method of the
FileSystemObject like so:
Note that, since
strTmpDir is a private member variable, the above code
would need to appear in a method of the
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
CleanUpSpreadsheets() method loops through the
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.)
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:
Now, each time that we use the class to generate a spreadsheet the
method will fire, deleting any out-of-date spreadsheets from the Web server's filesystem.
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!) :-)