Serve Excel and Word Documents Safely

Serve Word and Excel Documents Safely

by Ken Cox

November 1, 2004

Excel and Word components make good reporting solutions for desktop users, but using them on the server side can be unreliable, especially as you scale up beyond a simple mail merge. The hazards are well known to anyone whose unattended, data-driven server application has stopped cold because a COM-based Word component threw up an errant modal dialog box.

OfficeWriter 2.5 Enterprise Edition bundles SoftArtisans' ExcelWriter and WordWriter products (see Figure 1). The package is designed to deliver huge volumes of custom documents ("100,000 reports per hour," according to SoftArtisans) over Internet Information Services (IIS)—all without requiring Office on the server. However, it does require programming. In a simple scenario, you build a spreadsheet or chart in Excel and insert syntax such as "%%=$RecipientName" into the cells to serve as data markers. You then point the spreadsheet object to an ADO.NET data set using the SetDataSource method, bind the data, and push out a custom spreadsheet.

Figure 1: Make Updateable Spreadsheets
ExcelWriter, part of the OfficeWriter package, lets you deliver customized, data-driven reports, pivot tables, and charts as Excel files without requiring Microsoft Office on the server. The Enterprise version includes components that let users update the server's data from a downloaded spreadsheet.

Databinding in WordWriter is easier because you populate Word's existing mail-merge fields. Be aware that OfficeWriter's documentation uses the term "template" for a regular Excel spreadsheet or Word document that has OfficeWriter formatting. It's not referring to Microsoft's DOT or XLM files.

Your server-side code can create rich documents on the fly with OfficeWriter, including pivot tables in spreadsheets and embedded images in Word documents. Companies moving to SQL Reporting Services will want to investigate OfficeWriter's tools for creating and delivering reports with Microsoft Office.

Data in OfficeWriter doesn't have to be one-way or read-only. ExcelWriter features "HotCells" in worksheets. You mark cells as editable and insert a macro button so users can post their changes back to a remote data source. The data source's URL can be delivered dynamically as an embedded field in the spreadsheet.

ExcelWriter and WordWriter run fine in .NET, but you don't get Xcopy deployment or administrator-free installation. The ExcelApplication object, called when generating a spreadsheet from scratch, uses COM through .NET Interop. For managed code apps, stick to the ExcelTemplate object even though it limits you to inserting data into existing Excel files.

OfficeWriter works as advertised and includes usable, commented samples and adequate documentation. Contact SoftArtisans for pricing on the Enterprise version I tested here. If you choose the Standard edition at $2,795 per server, watch out for missing high-end features and restrictions on how much you can output in a day.

SoftArtisans OfficeWriter 2.5 Enterprise Edition

Web: www.softartisans.com
Phone: 877-763-8278
Price: Contact SoftArtisans for Enterprise Edition pricing; $2,795 for Standard Edition; $799 for Limited Edition
Quick Facts: Reads and serves database-driven Excel spreadsheets and Word documents from a Web application; doesn't require Microsoft Office components.
Pros: Creates scalable reporting applications; generates Office-compatible files; Enterprise version allows data updates from Excel.
Cons: Not entirely managed code; no Xcopy deployment; expensive.

About the Author

Ken Cox is a programming writer and .NET developer in Toronto creating e-commerce Web applications, XML Web services, SharePoint sites, and technical documentation for software. Ken is a Microsoft MVP for ASP.NET. Reach him at kjopc@hotmail.com.