When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
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 2

By Scott Mitchell

  • Read Part 1

  • 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!

    - continued -


    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
         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
      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!) :-)

  • Read Part 3!

  • Software Developer / Programmer - Distributed Systems (NYC)
    Next Step Systems
    US-NY-New York

    Justtechjobs.com Post A Job | Post A Resume

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