Click here to Skip to main content
Click here to Skip to main content
Go to top

Exporting a Generic collection based data into Spreadsheet using Serialization

, 30 May 2007
Rate this:
Please Sign up or sign in to vote.
An article on exporting a Generic based collection to a spreadsheet using Excel object model serialization.

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.

Excel Object Model

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Maths
Architect
United Arab Emirates United Arab Emirates
Mathew enjoys spending his paid time with providing solutions in Microsoft & TIBCO technologies.

Comments and Discussions

 
QuestionHow to arrange the columns? Pinmemberldyc2-Nov-07 9:01 
AnswerRe: How to arrange the columns? PinmemberMaths2-Nov-07 20:50 
GeneralRe: How to arrange the columns? Pinmemberldyc3-Nov-07 2:50 
GeneralRe: How to arrange the columns? Pinmemberldyc5-Nov-07 11:36 
GeneralRe: How to arrange the columns? Pinmemberhjenliu29-Nov-07 12:43 
GeneralRe: How to arrange the columns? Pinmemberldyc5-Dec-07 4:13 
Question2007 compatible? Pinmemberldyc1-Nov-07 10:36 
AnswerRe: 2007 compatible? PinmemberMaths1-Nov-07 19:36 
GeneralRe: 2007 compatible? Pinmemberldyc3-Nov-07 2:51 
GeneralRe: 2007 compatible? PinmemberChebroluT29-Aug-10 22:37 
GeneralNice article Pinmemberpurplepangolin31-May-07 2:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 31 May 2007
Article Copyright 2007 by Maths
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid