Wednesday, August 19, 2009

Export to Excel

I like inventing something on an as-needed basis, yes. How often does that happen though?! Then I thought how cool it would be if I just innovate or derive from existing technology?

To cut it short, here's the problem:
Lets say you have a scenario where you want to export something right from the database (and from the data layer) to excel and the excel workbook should be downloadable through any of your web interface components. I am not talking about just having a tabular data being exported to Excel but, a workbook with multiple worksheets (which has data from different tables in a dataset), styles, headers, formulas, expressions, content formatting, and much more. Its just a major pain to do all this. Its definitely possible but sometimes its just not worth the time. A simple example would be creating an invoice or orders (where the order information comes from the DL). Plus working with COM objects is not fun as they are not thread safe.

Here's a solution that cost you a bit of cash:
There are applications that would do the export to excel but they cost a few huyndred bucks. Plus they serve only one type. For instance there is a software that lets you create only invoices. You dont get control over "ALL" what you wanted to do here.

Here's mine:
What I tried and had worked pretty good for me was TEMLPATES. I had a template folder attached to my web app and had pre-formatted excel files there. That is, all they need is the data from the database. They will have the formulas, formatting, styles and so on already. So if you lets say drop in 2 rows of numbers from the database to one of these files and want the total in a different cell, the precalculated formula for that cell will automatically drop the total for you. So, you open the appropriate file from the templates folder using FileStream and Workbook Objects. The workbook object is a part of the Microsoft.Interop.Excel namespace. MSDN has a few good resources on how to open an Excel file. So, once you have your template, you can just drop the values from a dataset or datareader to the appropriate cells and then save the file in a different location with a different name for the user to download from. I usually attach a GUID to the filename or an id from the database that uniquely identifies the file. Now, you have a neat excel sheet with values from DB and formatting from a template.Of course, the user doesn't know this and doesn't care. All he needs to know is that he gets a kickass downloadable XLS file from a web page.

Last but not least, you should close the Excel applocation object (as it is thread unsafe) and dispose of any COM resources that you used.

Easy as it sounds, it is easily few hours of work if you are familiar with COM objects and FileStreams. If your computer crashes in the process, do not email me. If it doesn't and you have problems, I'd be glad to help.