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.
Thursday, April 12, 2007
Subscribe to:
Post Comments (Atom)
@deanwillson on twitter
connect on LinkedIn
Posts
3 comments:
Jaffe, thank you for the kind comment.
Hi, Thank you for your blog, is is a really useful article and a good starting place to look at this problem.
Have you also considered using the com interop libs. (http://msdn.microsoft.com/en-us/library/aa163987(office.10).aspx#odc_oxppias_topic9)
If you have, do you know the pros/cons when using them compared to the other methods?
sham,
Yes, I did consider using the com interop. I should have mentioned that in the posting. For several reasons I decided that might not be ideal for the environment I was deploying to. The server where my application was hosted was not under my control and the organization with the control had very thorough and strict change control procedures. I did not want to deal with the extra complication of getting the assemblies installed and verified to be working as expected. I wanted to minimize external dependencies where possible. Additionally, while it is a solution that is used by many, some of the documentation I read indicated it may not be a very scalable solution. Since the com functionality was really designed for a workstation implementation, it was not optimized for server based solutions that could have tens or hundreds of users accessing at once.
Thank you for commenting on the post. Your input is most appreciated.
Post a Comment