Exporting a Generic collection based data into Spreadsheet using Serialization

• Download source files - 40.5 Kb

Introduction

It is very interesting to note the dependency of spreadsheets in any business whatsoever. My opinion is that the Spreadsheet application such as Excel is among the best applications that the IT industry has brought out in the last decade. It takes away millions of man hours in data consolidation and extrapolation, leaving the business users to concentrate upon facts and figures.

Working with LOB applications and that too in a Finance division, I have come across several Windows and web applications that export the data presented in the user interface to spreadsheets. Looking into how these export features are implemented in several LOB applications, I have come across a common pattern where the developers use interop services to make use of Microsoft Office Excel application thus generating the spreadsheets and presenting it. This method had some disadvantages or pitfalls:

1. You need to install Microsoft Office Excel application on the server. Additional Licenses had to be bought.
2. The code itself was mostly written in a complex way, with probable maintenance issues.
3. Working with interop service libraries consumed more memory on server with each request. This possessed scalability issues.
4. Good response time was mostly compromised. This diminished better user experience.

When a similar requirement presented itself recently, it gave me an opportunity to look into other alternatives of implementing this feature.

Background

I prefer working with custom business entities. These entities are wrapped into a collection using Generics (IList<T>). In most cases, these generic based entity collections get bound to user controls such as grids in the user interface. The data presented in the grids had to be in most cases, exported into Excel spreadsheets. To achieve this, I wanted to write a simple reusable API that can be used in both Web and Windows application, and which can be extended as required. Also, I didn't want to go by the traditional way of using interop services. This is when I started exploring the Excel spreadsheet's object model.

As most of you know, you can save an Excel spreadsheet in an XML format. The Excel application creates an XML based file, which can be further analyzed to understand the object model. Once the XML file was saved, I used an XSD tool to infer the schema. Schema is presented in the figure. This schema led me to understand the basic model of XML based Excel spreadsheet. Now it was only a matter of creating an object model adhering to this schema which can be transformed into an XML that the Excel application recognizes.

Using the code

In the attached (see zipped source code) file, I have two projects. The project named Sphinx (no affiliation to the name) is a .NET Library project which contains all classes to build a basic Excel spreadsheet. It also contains a class called XMLSpreadsheet that exposes a method called GenerateSpreadsheet<T>(IList<T> collection) which returns an XML string. This XML string can then be saved and opened in Microsoft Office Excel 2003 application (not for older versions). A sample test implementation is available as another project in the same solution.

The object model presented in the Library represents a very basic version of the Excel schema which can be used to generate Excel spreadsheets. The object model can be further extended to achieve the additional requirement that you may have specific to your project. With some initial testing, I have found this to be extremely fast in comparison to the traditional interop model. So, download the source, extract the solution, open in VS 2005 and hit F5.

Points of Interest

The following points might be of interest to you:

1. XSD tool which comes along with Visual Studio .NET IDE is an excellent tool to infer schema from an XML file as well as to generate classes in C# & VB.NET
2. With correct serialization attributes, the object model can be serialized into the desired XML format
3. Refer Serializer class in Sphinx.Utility namespace to see how you can omit the basic XSD & XSI namespace inclusions in the XML while getting serialized
4. Watchout for custom namespaces that Excel spreadsheet adheres to in the method AddCustomNameSpace() in Serializer class in Sphinx.Utility

History

• January 20, 2007: Version 1.0