SoftArtisans Excel Writer: Visual Studio Product Review

First Looks:
Deliver Spreadsheets Over the Web

by Ken Cox

October 2001 Issue

Note: The product reviewed in this article is an old version of ExcelWriter.

ExcelWriter 2.0 is a server-side component for Internet Information Server (IIS) that generates Microsoft Excel spreadsheets from a data source. Using Active Server Pages (ASP), you can produce text, graphs, and charts that correspond to the BIFF 8 format used by Excel 97, 2000, and 2002. This capability lets you take advantage of Excel's rich formatting and formulas while providing the latest statistics from your database. The budget-conscious should take note that you don't even need to buy Excel to run or view reports—the component works without requiring Excel on the Web server, and Microsoft offers a free Excel reader.

Generate Excel Spreadsheets -
Without Excel Enlarge image.

During the easy installation, watch for a privacy issue. The license says the product sends your registration data to SoftArtisans automatically. Specifically, "HRESULT of registration, Username, Computername, Domainname, Time Zone, OS Version, Locale Id, Browser Version, Source IP Address and CPU count."

To give ExcelWriter a spin, I used ASP to read data from an Access database and push the results out to Excel as a sorted table. Thanks to the example files, producing a simple spreadsheet application takes only a few minutes. Use the familiar ActiveX Data Objects (ADO) to connect to the database and execute a SQL query. After assigning values to the cells, you define the style of the columns using ExcelWriter's CreateStyle method. For example, you can format a date using DateStyle.Number = 17, or you can set the width of a text column using cells.ColumnWidth(i) = 20.

If you're generating Excel spreadsheets, you probably also want to display charts. ExcelWriter makes that easy, too. In many ways, you are repeating the programmatic steps you would take if you were working on the spreadsheet within Excel. For example, to show a 3-D bar chart, you generate the data, put it into cells, select ranges, and set the format for the type of chart. Anyone who is comfortable in VBA will grasp the objects in ExcelWriter easily. One disappointment is its lack of support for pivot tables. That addition would give it a big competitive boost against reporting products from Crystal Decisions.

In my examples, I generated spreadsheets on the fly. For static reports, schedule a Windows Scripting Host script to write a single XLS file to the file system instead of streaming new spreadsheets to each user.

ExcelWriter is fun to use, works well, and provides good documentation and example code. For the forward-thinking purchaser, SoftArtisans' Web site shows the product working already in ASP .NET. A server license starts at $799 per CPU for the light version, but you'll have to contact the company on pricing out the full version of the software because it is "negotiated on a per purchase basis." You can obtain a free evaluation download from http://support.softartisans.com/eval.aspx.

About the Author

Ken Cox is a technical writer and Web applications developer in Toronto. A former broadcast journalist, Ken is also a Microsoft MVP. Reach Ken at kjopc@hotmail.com.