Web applications are great to present information to your users. However, more often than
not, users need to be able to generate reports that can be saved on the local computer and
manipulated at a later point.
For a recently completed project we needed to export data from a web application and generate
Excel documents with multiple worksheets that contained a number of sections and subsections,
calculated totals, subtotals and summary information at the top of the page. The calculated
totals and the summary information needed to be data-driven based on the records displayed on
each Excel worksheet so that any changes made to the data a later point are properly reflected
in the totals, subtotals, and the summary section without having to manually recalculate the
values.
This article is the first of two article that outlines the use of Xml to dynamically generate
an Excel document and send it to the user over the Internet. This first part focuses on the
class framework that implements the functionality to output Xml documents. The second part
describes how we can use that framework to create sophisticated Excel documents.
We have quite a few options when it comes to exporting data from a web application to Excel
and the most appropriate solution depends on the complexity of the document to be created and
the environment in which the report is generated. We looked at a number of those solutions
which we describe in the following sections:
One solution we briefly considered is to export the data as a delimited list of values that
can be downloaded by the user and saved as an Excel file. Delimited files are easy to generate
and widely understood which means that they can be read by a variety of programs, including
Excel. The drawback is that the delimited value list does not support any formatting and the
creation of Excel formulas is cumbersome. But the biggest disadvantage of the tab-delimited
solution is that it does not support the creation multiple worksheets, one of the requirements
we had to satisfy.
Many solutions described on the web outline how we can use the DataGrid web control to export
data from an ASP.NET application to Excel (see KB Article 317719
). This approach allows us to easily control the
formatting of the exported data. The problem with letting the DataGrid web server control manage
the data export is that there is no easy way to group data, add formulas, display summary
information, and create multiple worksheets without resorting to Office Automation (see below).
The .NET language makes it easy to programmatically control Excel from ASP.NET and program
against the exposed Excel object model. This shifts the burden of creating an Excel file to
the Excel application itself. While this is certainly one option, given Excel is installed
on the server, it is not recommended to use any Office application automation from an
"unattended, non-interactive user account" and doing so can be "risky and unstable" (see
KB
Article 288367).
In our case we did not have access to Excel on the server and most likely would not have used
Excel Automation even if Excel was installed in light of the Microsoft recommendations.
What if we let the client control the Excel object by using client side script to access the
Excel object model. This would take care of running Office Automation from an "unattended and
non-interactive user account" issue and Excel would not have to be installed on the server.
The concern we had with this approach is that it requires changing the security settings for
the web browser because Excel is marked as not being safe for scripting. We generally try to
avoid solutions that require clients to adjust any settings, especially security settings.
Starting with Microsoft Excel XP, one of the possible file formats to save an existing Excel
document is as an Xml Spreadsheet (*.xml). On the other hand, we can use Excel to
open and display Xml documents. An Xml spreadsheet is a simple text document which means it
can easily be sent over the Internet, making this solution an ideal candidate for exporting
data to Excel from a web-based application and the solution we eventually implemented.
In order to determine the Xml markup to be generated we created a mockup of a sample Excel
doocument, saved the file as an Xml Spreadsheet document, and examined the markup to be
generated. This initial Xml markup along with the Microsoft Office 2003 Edition Xml Schema
References documentation gave us the starting point to implement a class framework that
will allow us to easily generate Xml spreadsheets that can be viewed and manipulated via
Excel (you download the Office 2003 Xml Reference Schemas information from the
Microsoft Download Center.