CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Introduction
The adoption of XML as the native file format for Excel 2007 has opened up a whole host of new possibilities. Now, for the first time, you can use standard tools to create native Excel files on the server. No longer is there a need to pollute your beautiful server room with a headless PC running Excel and an old VB6 app that uses OLE Automation to create reports. Such things can be consigned to the trash heap of IT history.
However, the Office Open XML standard is so new there are precious few good code samples. This article aims to correct the situation by providing a flexible set of open source class libraries that you can use to create Excel spreadsheets on the server.
Why Create Excel Spreadsheets on the Server?
Excel has long been recognized as the de facto standard when it comes to presenting management reports. The unique combination of great calculation engine, excellent charting facilities, and the possibility to perform “what if” analysis make it the “must have” business intelligence tool.
So, when I came to replace my aging management reporting infrastructure, I set one key requirement: the new system must be web-based and provide a “download in Excel” option. For mt business intelligence project, I built the data warehouse using SQL Server 2005 populated from PeopleSoft and Novient implementations using SQL Server 2005 Integration Services (SSIS). The OLAP cube was built on SQL Server 2005 Analysis Services (SSAS). SQL Server 2005 Reporting Services (SSRS) provides the web-based access to management reports and the all important “download in Excel” option. So, why do you need to create Excel on the server?
The problem with SQL Server 2005 Reporting Services is that it the Excel spreadsheets it generates are “dumb.” They contain no formula—just the raw data. So, the recipient cannot perform a “what if” analysis by changing a few values and expecting the spreadsheet to recalculate.
I considered a number of ways to overcome this issue, but by far the most attractive is to create the Excel spreadsheet on the server, straight from the OLAP cube data. So, I created a web-part for SharePoint Server 2007 so the user could enter their criteria and view the report online via Excel Services. Of course, users can download the file for off-line viewing in Excel 2007 or even Excel 2003 file format. This SharePoint web-part and its associated web service that does the file format conversion will be the topic of another article.
The Open Source ExcelPackage Assembly
The ExcelPackage assembly is a set of classes and wrappers around the .NET 3.0 System.IO.Packaging API and the new SpreadsheetML file format. It extracts away the complexity of dealing with the individual XML components that make up the new Excel 2007 file format. The classes are published as an assembly called ExcelPackage that you can install in the GAC and use as the basis of your own applications. In the sprit of open source projects, if you want to help extend the functionality offered by the ExcelPackage assembly, join the team over at the ExcelPackage Open XML project.
