Generating an Excel file in ASP.NET

Question

I am about to add a section to an ASP.NET app (VB.NET codebehind) that will allow a user to get data returned to them as an Excel file, which I will generate based on database data. While there are several ways of doing this, each has its own drawbacks. How would you return the data? I'm looking for something that's as clean and straightforward as possible.

Answer 1

CSV

Pros:

• Simple

Cons:

• It may not work in other locales or in different Excel configurations (i.e. List separator)
• Can't apply formatting, formulas, etc

HTML

Pros:

• Still pretty Simple
• Supports simple formating and formulas

Cons:

• You have to name the file as xls and Excel may warn you about opening a non native Excel file
• One worksheet per workbook

OpenXML (Office 2007 .XLSX)

Pros:

• Native Excel format
• Supports all Excel features
• Do not require an install copy of Excel
• Can generate Pivot tables
• Can be generated using open source project EPPlus

Cons:

• Limited compatibility outside Excel 2007 (shouldn't be a problem nowadays)
• Complicated unless you're using a third party component

SpreadSheetML (open format XML)

Pros:

• Simple compared to native Excel formats
• Supports most Excel features: formating, styles, formulas, multiple sheets per workbook
• Excel does not need to be installed to use it
• No third party libraries needed - just write out your xml
• Documents can be opened by Excel XP/2003/2007

Cons:

• Lack of good documentation
• Not supported in older versions of Excel (pre-2000)
• Write-only, in that once you open it and make changes from Excel it's converted to native Excel.

XLS (generated by third party component)

Pros:

• Generate native Excel file with all the formating, formulas, etc.

Cons:

• Cost money
• Add dependencies

COM Interop

Pros:

• Uses native Microsoft libraries
• Read support for native documents

Cons:

• Very slow
• Dependency/version matching issues
• Concurrency/data integrity issues for web use when reading
• Very slow
• Scaling issues for web use (different from concurrency): need to create many instances of heavy Excel app on the server
• Requires Windows
• Did I mention that it's slow?