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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Wednesday, February 28, 2001

Creating Excel Spreadsheets with Office Web Components (OWC)

By Scott Mitchell


An Upgrade to the Class...
This article presents a class (ExcelGen) that generates an Excel spreadsheet based on the results of a Recordset. An enhanced version of this class is available at: Enhancing the ExcelGen Class (for Creating Excel Spreadsheets). However, make sure you've read this article completely before moving onto the next!

- continued -

'

Introduction
One of the great things about running an ASP Web site where visitors regularly contribute articles is that, in reading/editing those article submissions, I end up learning a lot of new things! Last week Bret Hern submitted a beautiful article on using Charting with Office Web Components. After poking around Microsoft's site some, I found rather terse, technical documentation on these nifty components and soon discovered that these components can also be used to create Excel spreadsheets via ASP code! These spreadsheets can then be saved as an Excel file for the user to download.

In this article we will look at using the Office Web Components (OWC) to create an Excel spreadsheet via ASP code based on the results from a database query! All of this complexity is encapsulated in a (rather basic) class. In the upcoming weeks I plan to expound on this class. Currently it just dumps the contents of a Recordset into a spreadsheet, but in future weeks I'd like to show how to add nifty formatting, apply formulas, pivot tables, and all that other jazzy stuff.

Licensing Issues
Microsoft has some pretty strict licensing issues on using Office Web Components in the Internet-world (as well as on an intranet). Before you begin using Office Web Components on your Web site be sure to read Microsoft's Licensing Agreement for OWCs.

Getting Started
To get started using Office Web Components you must have (at minimum) the Office Web Components section of Office 2000 installed on the Web server. (If you are wanting to create Excel spreadsheets and graphs without requiring Excel's presence on the Web server be sure to check out SoftArtisan's ExcelWriter component.) (For more on OWC requirements and installation information check out: Requirements for Office Web Components!)

Creating a Spreadsheet
In this article we will look only at the basics of creating a spreadsheet, setting various cell values, and saving the spreadsheet to disk. In future articles we will look at prettying up the display and working with some of the more advanced features...

Since the spreadsheet aspect of the Office Web Components is a simple COM object, you can create an instance of the spreadsheet component through your ASP page just as you would create an instance of any other COM component:

'Create an instance of the Spreadsheet component from OWC
Dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

Simple enough. Once you have a Spreadsheet object to work with you can set the values of the spreadsheet's cells using the Cells property of the Spreadsheet object like so:

objSpreadsheet.Cells(Row, Column).Value = SomeValue

Finally, to save the Spreadsheet as an Excel file you must use the Export method of the Worksheet object. (The ActiveSheet property of the Spreadsheet object returns a valid Worksheet object instance.) The Export method expects two parameters: a full physical file name and an SheetExportActionEnum constant. The file name parameter specifies the specific location to save the Excel spreadsheet; the export action indicates if the file should be saved to disk or piped directly to Excel. Since we are running all of this code on the server-side, if we try to pipe the spreadsheet contents directly to Excel, we will be trying to open Excel on the Web server - not what we want to do. In fact, this setting is only useful if you are using the Spreadsheet object as an ActiveX control, since then it will be executing on the client's machine as opposed to on the Web server. Therefore, when using the Export method in server-side script, always specify a value of 0 for the export action, which indicates to the Export method to simply save the spreadsheet to disk and to not try to pipe the contents straight to Excel.

objSpreadsheet.ActiveSheet.Export("C:\Inetpub\wwwroot\FooBar.xls", 0)

Keep in mind that the IUSR_machinename account must have Write permissions on the directory that you wish to write the Excel file to. If the IUSR_machinename account has inadequate permissions you will receive an error when trying to use the Export method... (Check out this FAQ for more information...)

Now that we've covered the basics of creating / filling / saving an Excel spreadsheet through ASP, we're ready to look at a nifty Excel spreadsheet generation class I wrote that will help this process. In Part 2 we'll examine this class in detail!

  • Read Part 2!

  • 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