Export Data from MS-SQL to Excel in asp.net 3.5

I was working on a CMS project for a client and needed to export data from a ms-sql table using asp.net to excel.  I thought this would be easy; good news it is, but the search for how to do it was a real pain. After a few hours surfing and finding a number of articles that didn't quite do it, I thought I would put everything in one place.

First off, if you don't care about formatting the cells in the new excel document, then you will be done in about 30 seconds flat.  If you want to format the cells, you will need to spend a bit more time and take a look at the excel xml schemas and explanations on the msdn site.

The solution can easily be adapted to export asp.net controls such as the datagrid.  I have used the HttpContext class to stream the excel document rather than a build, store and offer up method to keep things simple in this example.

C# ASP.NET 3.5 Full Source Code click here

The code works as follows.

1. Grab the data into a .NET data reader
2. Build XML document based on MS-Excel XML schemas to hold Excel workbook and style sheets
3. Inject Table object into XML document to map data to Excel sheet object 
4. Stream document using the .NET  HttpContext class