Generating Excel Reports from ASP.NET applications

Introduction
I see this question a lot in forums. So you made your all singing and dancing application and have beautifully styled your DataGrid, GridView or Repeater, finely tuned your report query in the database for lightening responses, there you sit marvelling your own creativity and programming prowess. That’s when you get the call from the client…

They want the data in an exportable format!!! Deafening silence, welcome to the real world, most mere mortals would rather use their favourite spreadsheet software than your blood sweat and tears creation. We developers shouldn’t take this as anything but logical choice. These programs are popular for a reason and have been around long enough that most users have already reached the tip of their learning curve. I would argue that your ability to be successful as a solution provider lies in providing solutions that fit neatly into existing ones and moreover lever and in a sense mash up tried and tested solutions and software. My question is: why re-invent the wheel?

Different Approaches
There are plenty of ways to export reports (CSV, Flat file and XML to name a few), but whether you like it or not most people use Excel or open source offerings like Open Office. Excel will open various file formats and you can trick excel by saving CSV files or even html tables with a .xls extension. If you have access to an ms office license, you can access the office com interop objects and generate spreadsheets that way. Lastly and perhaps the neatest of the solutions is to buy one of the out of the box solutions which provide standalone assemblies for generating spreadsheets like ASPOSE.

Million Dollar Question
which one of these should I use?

Million Dollar Answer
Any of them will achieve roughly the same outcome so it’s up to you.

My Choice
I have supplied solutions which export files based on the first method above, i.e. convert my data to XML or CSV content then export a file which has a .xls extension and set the Response Content Type header to excel:


Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=Management Report.xls");


The problem with this is that you are essentially cheating and while Excel will likely open the file, the user may get a prompt about the content when trying to open the file (something like the content not matching the extension). Also, if they were to use another program to import that file, then they would need to save it again from excel to ensure that the proper formatting is applied. I really see this as a quick and dirty approach which has its uses in some scenarios but does not really cut it for professional level solutions.

Using com interop in web applications is not my weapon of choice either and you really need to understand the concept of using unmanaged resources before you even attempt this method.

This leaves using a third party solution or writing your own (good luck to you). Third party solutions tend to be expensive, so unless your pockets are deep or your budget is large, which in the current recession is unlikely as companies are feeling the pinch, you are stuck.

The Secret
If you have ever downloaded the express versions of Visual Studio you will be asked to register the product, this entitles you to access to some pretty useful benefits in the benefit portal. One of these is SpreadSheetGear 2006, which essentially allows royalty free generation of spreadsheets from web applications (and windows apps if that is your cup of tea). It is as simple as adding a reference to the SpreadSheetGear assembly to your application, adding a using reference in your page or control then you have just to write some intuitive code to export to a file:


DataTable dt = /*get your data table here*/
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets["Sheet1"];
IRange cells = worksheet.Range;cells.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders);

// Auto size all worksheet columns which contain data
worksheet.UsedRange.Columns.AutoFit();

// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007.
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" yourfilename.xls");
workbook.SaveToStream(Response.OutputStream, FileFormat.XLS97);
Response.End();


The above is a very simple example, you can produce heavily styled reports using the same assembly by selecting ranges and applying styles accordingly. Obviously there is a learning curve, but it is a clean elegant solution which generates properly formatted Excel Files that could have otherwise cost big bucks.

kick it on DotNetKicks.com