Creating Your First Spreadsheet

Example: Creating Your First Spreadsheet

First.asp creates a simple spreadsheet, assigns cell values, and opens the new spreadsheet in the browser.

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

To create a spreadsheet, the first step is to create an instance of the ExcelWriter object:

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

Next, 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.

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

When the spreadsheet is complete, you can save it to the server's hard disk, return the file in memory, or open the file in either the browser or Microsoft Excel. In this example, ExcelWriter opens the spreadsheet in the browser:

	xlw.Save "first.xls", saOpenInPlace

The spreadsheet has been created in memory and is now sent to the user's browser. The user may choose to save the spreadsheet. If it is saved, the default name will be first.xls.

Top

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}" -->

Top

Wrapping Up

Finally, the ExcelWriter object is explicitly destroyed. This is not strictly necessary but is considered good programming practice. The ASP Response is also ended. This prevents extraneous additional characters from being sent along with the spreadsheet.

	Set xlw = nothing
	Response.end

Top

Section Summary

1. The ASP page should only contain server-side script 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 ExcelWriter 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.

Top

Copyright © 2003, SoftArtisans, Inc.