SoftArtisans ExcelWriter

A Different Way of Building Excel Spreadsheets

By Tony Mayfield
32X Tech Corporation

So here is the problem: In your corporation, you need to be able to pull information from your databases into Microsoft Excel. Your executives need to be able to run "what-if" scenarios and run analyses on various business segments. The trouble is you also need a 24/7 solution that is reliable and easy to maintain. You want to be able to run all of this from a Web browser so you are not forced to maintain client software for all of your end users. What are your options?

The first thought may be to use the latest Microsoft Office Suite, Office XP or an option you may not have thought of - ExcelWriter from SoftArtisans.

Microsoft Office XP uses XML and has some nice features, but running from the Web, it has limitations. Although Office XP is a completely robust product as a client package, there are some limitations when used as a purely Web-based application. Cell formatting is limited to basic width and text changes.

"ExcelWriter helps utilize scarce resources as well as the end users' knowledge of Excel functionality." Tony Mayfield 32X Tech Corporation

The main reason is that XP uses XML exclusively to build Web spreadsheets. ExcelWriter also provides two pieces of functionality not available in Excel; 1) Single-step importing of data from a database and 2) Support for an almost limitless number of worksheets, columns and rows. This is an acceptable method for producing Web pages, but lacks some of the functionality that is found in ExcelWriter.

ExcelWriter permits significant options in formatting and a robust feature-set. This gives end users the feel and functionality of a native Excel sheet and even provides your users with full charting capabilities.

ExcelWriter is extremely fast and designed to build vast numbers of spreadsheets per day. Many use this to build thousands of spreadsheets per day.

ExcelWriter creates full-function Excel from databases or any ADO recordset. ExcelWriter makes efficient use of limited resources and most of your users' familiarity with Excel. ExcelWriter deserves your attention.

Basics

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.

What You Need to Run ExcelWriter

On the Server:

On Client Machines:

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.

• Microsoft Excel 95, 97, 2000 or Excel XP
• The Microsoft Excel Viewer (this is free and can only be used to view and print spreadsheets, not modify them).
• Other products that support Excel format.

Installing ExcelWriter

This is a simple process. Once the installation package is loaded to your machine, the installation of ExcelWriter is quick and easy. The easiest way is to simply double-click ExcelWriter.exe, and follow the InstallShield Wizard instructions. Automatic installation will unpack ExcelWriter files to selected location or the default location of C:\Program Files\SoftArtisans\ExcelWriter (note, you may change this location during the install process). You also have a choice whether to have a complete install or custom install. It is recommended that you choose Complete install unless you have previous experience in setting up the product. It will also register the saexcel.dll, and finally this will create a virtual directory called ExcelWriter. This directory will be populated with documentation and samples.

How It Works

Let's take a look at some of the functionality in more detail.

The ExcelApplication Object

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)

Making Sure the BackOffice Is Ready

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.

Formulas

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"

Spreadsheets with Charts

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

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.

Writing Your Results

ExcelWriter is capable of writing spreadsheets in a number of different ways:

• 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 of browser's HTML window by the Excel spreadsheet.
• 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.

Importing From A Database

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.

NumRows = cells.CopyFromRecordset(oDBRS, True, FirstRow, FirstCol).

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

Summary

ExcelWriter is an excellent method to permit end users' access to spreadsheets via the Web. For viewing and printing spreadsheets end users do not require anything more than a browser. ExcelWriter allows developers to quickly build powerful business applications that generate native Excel without installing Microsoft Excel on the server. ExcelWriter creates full-functional, Excel spreadsheets form databases or ADO-compliant recordsets. ExcelWriter is program that helps utilize scarce resources as well as the end users' knowledge of Excel functionality. ExcelWriter is designed to work reliably in high-demand environments.

About The Author

Tony Mayfield works for 32X Tech, a technology training and courseware development company based in Florida. © 2003, All Rights Reserved.