Click here to Skip to main content
13,198,068 members (94,336 online)
Click here to Skip to main content
Add your own
alternative version


26 bookmarked
Posted 20 Jan 2007

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.


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.


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


  • January 20, 2007: Version 1.0


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


About the Author

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

You may also be interested in...


Comments and Discussions

QuestionHow to arrange the columns? Pin
ldyc2-Nov-07 9:01
memberldyc2-Nov-07 9:01 
AnswerRe: How to arrange the columns? Pin
Maths2-Nov-07 20:50
memberMaths2-Nov-07 20:50 
GeneralRe: How to arrange the columns? Pin
ldyc3-Nov-07 2:50
memberldyc3-Nov-07 2:50 
GeneralRe: How to arrange the columns? Pin
ldyc5-Nov-07 11:36
memberldyc5-Nov-07 11:36 
GeneralRe: How to arrange the columns? Pin
hjenliu29-Nov-07 12:43
memberhjenliu29-Nov-07 12:43 
GeneralRe: How to arrange the columns? Pin
ldyc5-Dec-07 4:13
memberldyc5-Dec-07 4:13 
Question2007 compatible? Pin
ldyc1-Nov-07 10:36
memberldyc1-Nov-07 10:36 
AnswerRe: 2007 compatible? Pin
Maths1-Nov-07 19:36
memberMaths1-Nov-07 19:36 
GeneralRe: 2007 compatible? Pin
ldyc3-Nov-07 2:51
memberldyc3-Nov-07 2:51 
GeneralRe: 2007 compatible? Pin
ChebroluT29-Aug-10 22:37
memberChebroluT29-Aug-10 22:37 
GeneralNice article Pin
purplepangolin31-May-07 2:59
memberpurplepangolin31-May-07 2:59 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171020.1 | Last Updated 31 May 2007
Article Copyright 2007 by Maths
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid