Creating Excel Spreadsheets with Office Web Components (OWC), Part 3By Scott Mitchell
In Part 2 we looked at the private member variables of
our class as well as our
event handlers and our
Property Let statements. In this final part we'll examine
the two methods of the
ExcelGen class and look at how to use this class through
an ASP page!
Creating the Methods for the
Only two methods are needed for our class. The first one,
accepts a single parameter: a populated Recordset object. This method then loops through
the Recordset, transferring its contents to
Note that both the data from the Recordset and the names of the columns in the Recordset
are outputted to the Excel spreadsheet.
Our last method,
SaveWorksheet, accepts a single parameter,
which specifies the location to save the spreadsheet. This method returns a Boolean value:
True if the file is saved successfully, False otherwise. Recall that exporting the
object to a physical Excel file can fail if the
has inadequate permissions.
ExcelGen Class from an ASP Page
Now that we've looked at the contents of our class, let's examine how to use it through an ASP page to create a spreadsheet containing the contents of a Recordset! It is highly recommended that you place the
ExcelGen class in an include file and then use a server-side
include on those ASP pages that need to utilize the class's functionality. (To learn more about
server-side includes be sure to read: The Low-Down on
For this example we'll assume that the
ExcelGen class has been placed in the file
To use this class, then, we'll use a server-side include to import the contents of
/scripts/ExcelGen.class.asp. Next, we'll create an instance of the class using
New keyword. Once we've created and populated a Recordset, we can call the
.SaveWorksheet method to dump the Recordset's contents into an Excel spreadsheet.
Finally, we need to save the contents of the spreadsheet using the
If the spreadsheet is saved successfully the user is presented with a hyperlink to download the Excel file.
Conclusion / Caveats
One annoying thing with the
ExcelGen class is that a user must go through a
two-phase step to view the contents of a Recordset through an Excel file. First, he must
visit an ASP page that creates the Recordset; next, he must click on the link to the
Excel file. This is a pain and something I plan on fixing in the next article on this topic
(which will serve, basically, as an enhancement to the
One major concern that should also be quickly apparent is that in the above example the Excel spreadsheet is always saved to the same file. Urg. This is bad since multiple users will be trying to access the same file and, most likely, they would be running different kinds of database queries producing varrying output. One approach is to create a unique file for every user visiting the page... but then how to we clean up old spreadsheets? We'll examine this topic in more detail in a future article...
In the mean time, play with the code here, create your own spreadsheets, and poke around the Microsoft documentation. Happy Programming!
ExcelGenclass (in text format)