Generating an Excel file in ASP.NET [closed]

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

Just avoid COM Interop via Microsoft.Office.Interop namespace. It is so damn slow and unreliable and unscalable. Not applicable for masochists.

Answer 2

man, in .net i guess you could have a component that could do that, but in classic asp I have already done it creating an html table and changing the mime tipe of the page to vnd/msexcel. I guess that if you use a gridview and change the mime type maybe it should work, because the gridview is an html table.

Answer 3

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?

Answer 4

I recommend free opensource excel generation libruary which is based on OpenXML

It helped me several months ago.

Answer 5

Based on the answers given, and consultation with coworkers, it appears that the best solution is to generate either an XML file or HTML tables and push it down as an attachment. The one change recommended by my co-workers is that the data (i.e. the HTML tables) can be written directly to the Response object, thus eliminating the need to write out a file, which can be troublesome due to permissions problems, I/O contention, and ensuring that scheduled purging occurs.

Here's a snippet of the code... I haven't checked this yet, and I haven't supplied all the called code, but I think it represents the idea well.

    Dim uiTable As HtmlTable = GetUiTable(groupedSumData)

    Response.Clear()

    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("Content-Disposition", String.Format("inline; filename=OSSummery{0:ddmmssf}.xls", DateTime.Now))

    Dim writer As New System.IO.StringWriter()
    Dim htmlWriter As New HtmlTextWriter(writer)
    uiTable.RenderControl(htmlWriter)
    Response.Write(writer.ToString)

    Response.End()

Answer 6

I personally prefer the XML method. I'll return the data from the database in a Dataset, save it to XMl, then I create an xslt file that contains a transformation rule that will format a proper document, and a simple XML transform will finish the job up. The best part of about this you can format cells, do conditional formatting, setup headers and footers, and even set print ranges.