Quick Start > Create a Spreadsheet with ExcelApplication

Create a Spreadsheet with ExcelApplication

To create generate an Excel workbook from code - without using an ExcelWriter template - use ExcelApplication, the main class for pure code-based workbook generation. An ExcelApplication object represents an Excel workbook, and is also an engine used to open, create, and write (save or stream to a browser) the workbook.

To generate a workbook with ExcelApplication:

1. Create an instance of ExcelApplication.

   Set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")

2. Create a worksheet:

   Set ws = xlw.Worksheets(1)

   A spreadsheet may contain multiple worksheets. The first worksheet in a spreadsheet is WorkSheets(1). If a spreadsheet contains more than one worksheet, the second would be WorkSheets(2), the third WorkSheets(3), and so on. This example creates only one worksheet.

3. Within the new worksheet, create cells and fill in values:

   ws.Cells("A1").Value = "Name"
   ws.Cells("B1").Value = "Count"
   ws.Cells("C1").Value = "Dollar"
   
   ws.Cells("A2").Value = "Fred Smith"
   ws.Cells("B2").Value = 10
   ws.Cells("C2").Value = 37.5

4. When the spreadsheet is complete, you can save it to the server's hard disk, return it in memory, return it as an ExcelWriter template, stream it to the browser. In this example, ExcelWriter streams the spreadsheet to the browser:

   xlw.Save "first.xls", saOpenInPlace

5. Explicitly destroy the ExcelApplication object:

   Set xlw = nothing
   Response.end

   Destroying the ExcelApplication object is not strictly necessary but is considered good programming practice. The HTTP response is also ended. This prevents extraneous additional characters from being sent along with the spreadsheet.

The spreadsheet was in memory and is now sent to the user's browser. The user may choose to save the spreadsheet or open it. If it is saved, the default name will be first.xls. If the user opens the file, it will open in the browser (because the second parameter passed to Save is saOpenInPlace).

Note that the code does not contain HTML. This is because the generated spreadsheet is written (to disk, memory, or the client) in a single HTTP response. If the response includes HTML, the spreadsheet will be corrupted. The HTTP response should contain only the creation of the spreadsheet. Do not include HTML or Response.Write lines.

Avoiding Errors by Using Typelibs

In ASP, Typelibs provide quick and convenient access to constants associated with a particular object. The script in this example includes ExcelWriter's TypeLib because the script uses the constant saOpenInPlace. Avoid errors by always including the TypeLib in your ExcelWriter scripts. The UUID attribute specifies ExcelWriter's unique identifier:

<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->

Section Summary

1. The ExcelWriter script should only contain the server-side code that generates the spreadsheet. Any HTML or Response.Write commands will corrupt the spreadsheet.
   
   
2. The first step is to create an instance of the ExcelApplication object.
   
   
3. A spreadsheet contains multiple worksheets, which in turn contain multiple cells.
   
   
4. The spreadsheet can be streamed to the browser in one step. It does not have to be stored on the Web server's hard disk.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.