The ExcelWriter is a server-side component that builds a complete, native Excel spreadsheet using a data source or an Active Server Pages (ASP) page.

The process of using the ExcelWriter is simple. The user simply clicks the button to display the data in a Web form. Then to build a completely functional Excel spreadsheet, click the Spreadsheet button. Next, save the file as an Excel document. Once done, it can be used just like any other Excel spreadsheet. The spreadsheet can be fully edited and all formatting and formulas are present.

In one, single pass, using any ADO recordset, ExcelWriter can fill a spreadsheet. All that is needed is the beginning column and row and using the recordset object, the spreadsheet is easily filled.

There is no specific software required on the client to download and save an Excel spreadsheet. To open and use an Excel spreadsheet you must have one of the following products installed on the client.

To actually generate an instance of an Excel spreadsheet, an instance of the ExcelApplication object must be created. This is accomplished by the following syntax:

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

This object represents an Excel workbook. It has five properties and methods-Worksheets, Save, Version, CreateStyle and CreateFont. Each one of these represents portion of the Excel spreadsheet functionality. The ExcelWriter object uses these methods and properties assist in customizing spreadsheets.

Once the spreadsheet has been instantiated, the specific worksheet number is assigned-in the following case this is worksheet one:

Set ws = xlw.worksheets(1)

If we have an instance of sending the spreadsheet stream directly to the client-instead of saving it to the server hard drive)-by default a dialog box opens in the browser. In order to keep this from occurring and making things as smooth for the end user as possible, we can make a simple change to the IIS server. First, the xls extension needs to be associated with the asp.dll upon configuration of the virtual directory. This is done via the Internet Services Manger. Second, the ExcelWriter scripts should be renamed with the .xls extension.

Note: If this association of xls extensions with asp.dll, be sure not to save normal Excel spreadsheets in that virtual directory. If this is done, ASP will attempt to parse these spreadsheets as ASP scripts and they will fail.

Building formulas is simple as well. Simply take known Excel formulas and utilize their functionality. Most of the frequently used functions of Excel are fully supported in ExcelWriter. A simple rule of thumb is to always use the equal sign (=) just as it is when using Excel. Here are a few examples of simple formulas:

ws.Cells("E15").Value = "Your Total:"
ws.Cells("E16").Formula = "=sum(e2:e14)"

ws.Cells("F11").Value = "Column Average:"
ws.Cells("F12").Formula = "=average(f2:f10)"

ws.Cells("E18").Value = "Your Total x Column Average:"
ws.Cells("E19").Formula = "=E16*f12"

Creating spreadsheets with charts is straightforward. The developer simply needs to lay out the process and code it using a few rules. Let's take a simple example of adding charting features to a simple spreadsheet. First the chart must be defined: Dim chartType

The developer can then define the style, size, title, etc. of the graph. The following code defines a default chart (clustered column):

Set xlChart = WKS.Charts.Add(chartType,,11,1,25,5)
xlChart.ShowLegend = True
xlChart.Title = "Three-Month Projection of Sales"
xlChart.CategoryAxis.Title = "Months"
xlChart.ValueAxis.Title = "Sales"
xlChart.SeriesCollection.Add "B3:E7"
xlChart.SeriesCollection(1).Name = "Business Unit 1"
xlChart.SeriesCollection(2).Name = "Business Unit 2"
xlChart.SeriesCollection(3).Name = "Business Unit 3"
xlChart.SeriesCollection(4).Name = "Business Unit 4"
xlChart.SeriesCollection(5).Name = "Business Unit 5"

Notice the flexibility given the developer in constructing the graph: size, number of columns, title definition, etc. can be set as fixed or determined dynamically by data in the chart.

Security while using ExcelWriter is straightforward. If you are streaming the spreadsheets directly to the browser, security is not an issue. Since ExcelWriter does not write any interim files to the server-only to memory, there are no security issues to deal with.

Things change if you are generating spreadsheets and writing them to the server hard drive. ASP provides three distinct levels of authentication:

	Anonymous
	Basic
	NT Challenge/Response

Once a user accesses a page, that page's security is checked. If no specific security measures are in place, the anonymous level of security is used. If any NTFS permissions are in place, IIS and the browser negotiate if the end can be authenticated.

When using Basic or NT Challenge/Response, the IIS server performs the NT login for the user. This is commonly referred to as impersonation. When using ExcelWriter and saving them to the server hard drive, impersonation is used. If the end user is attempting to save a spreadsheet to the server hard drive, this will be accomplished using the security context provided via IIS and ASP.

If the end user has a non-authenticated account, ExcelWriter can only write to server locations available to the default IIS-or IUSER_MachineName-account. If the end user has an authenticated account, ExcelWriter is only able to write files to locations available to the user's domain permissions.

	Disk: In this case the spreadsheet is written directly to the hard disk of the server.
	OpenInExcel: For this option, the spreadsheet is sent to a new instance of the browser in a new window.
	OpenInPlace: In this instance, the spreadsheet is sent to the browser and is opened in the existing browser instance.
		In-place activation forces Microsoft Internet Explorer's menus and toolbars to change to Excel. The spreadsheet will replace the contents
	BinaryStream: For this instance, the spreadsheet is returned as a sequence of bytes in memory.

Each of these results are produced with the Save method. This permits the developer to save any file in a variety of methods.

ExcelWriter makes importing data from databases simple. There are many helpful features included. Some include, formatting of cells before or after the import, adding formulas before or after import and simple setting of maximum limit of rows and columns.

The process is simple. First an ADO Recordset is created utilizing a database query.

Set oDBConn = Server.CreateObject("ADODB.Connection")
oDBConn.Open "DBQ=" & filePath & ;Driver=(Microsoft Access Driver (*.mdb)
Set oDBRS = oDBConn.Execute ("Select * from customers")

Next an instance of ExcelWriter must be created.

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set cells = xlw.Worksheets(1).Cells

Once this is completed, the first row (FirstRow) and first column (FirstCol) must be set. For this example the first cell is (4,3) which is cell C4.

FirstRow = 4
FirstCol = 3

The CopyFromRecordset method is utilized to import the data. This method brings back the worksheet and automatically populates the headers.

The parameters, in order, are: (Recordset, True/False to show headers, First Row, First Column, Maximum number of rows, Maximum number of columns).