Thursday, April 12, 2007

Create Excel files on the fly with ASP.net

Numerous articles have been written about this topic, but since all projects are different I will summarize my experience based on a recent project. This is not intended to be a tutorial as there is already a wealth of tutorials and instructions for how to accomplish each of the following methods.
Selection of the method can be difficult because you need to balance the versions of Office the users have available with the importance of specific Excel related formatting and features with performance.
Office XP/2003 users only XML/XSLT :
Using XSLT to create Excel spreadsheets (Office XP/2003 only) is a great simple method that provides a high level of formatting ability such as adding Conditional formatting, styling, etc very easily. Be cautioned that when creating spreasheets using XSLT with more than a couple hundred rows, performance degrades quickly and it can take upwards of several minutes to create the file. Sure the performance can be improved by optimizing the XSLT template, but rendering a couple thousand rows of data will still be frustrating to most users.
Office 97-Office 2007 Office XML:
You can use the new native Office XML format of the Office 2007 products to create the file, but since it is still a relatively new technology, there is not as much documentation available to make quick work of the project. Additionally your user's running Office versions earlier than 2007 will need to be able to download and install the Office 2007 compatibility kit.
Office 97-Office 2007 HTML:
You can create HTML files and output to the stream with a response.ContentType of application/vnd.ms-excel and Excel will open the HTML into Excel. This method provides a good balance of speed, and the ability to format the cells and even add formulas to the cells. This method can be a little confusing to the users when they change some data in the spreadsheet and try to save the file. This is even more advantageous if what you want to export is in the form of a datagrid that you can override the rendering method to output the Datagrid HTML directly to the response stream instead of manually building the HTML table.
Office 95-Office 2007 csv or tsv:
This is the most compatible method, but provides the least flexibility with formatting or other Excel specific functionality.