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

The best method i've seen for excel reports is to write out the data in XML with a XML extension and stream it to clients with the correct content type. (application/xls)

This works for any report which requires basic formating, and allows you to compare against existing spreadsheets by using text comparison tools.

Answer 2

You can output the data as html table cells, stick a .xls or .xlsx extension on it, and Excel will open it as if it were a native document. You can even do some limited formatting and formula calculations this way, so it's much more powerful than CSV. Also, outputting an html table ought to be pretty easy to do from a web platform like ASP.Net ;)

If you need multiple worksheets or named worksheets within your Excel Workbook, you can do something similar via an XML schema called SpreadSheetML. This is not the new format that shipped with Office 2007, but something completely different that works as far back as Excel 2000. The easiest way to explain how it works is with an example:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?> 
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>Your_name_here</Author>
      <LastAuthor>Your_name_here</LastAuthor>
      <Created>20080625</Created>
      <Company>ABC Inc</Company>
      <Version>10.2625</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<Styles>
      <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Bottom" />
            <Borders />
            <Font />
            <Interior />
            <NumberFormat />
            <Protection />
      </Style>
</Styles>

<Worksheet ss:Name="Sample Sheet 1">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table1">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
      <Cell><Data ss:Type="Number">1</Data></Cell>
      <Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">3</Data></Cell>
      <Cell><Data ss:Type="Number">4</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">5</Data></Cell>
      <Cell><Data ss:Type="Number">6</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">7</Data></Cell>
      <Cell><Data ss:Type="Number">8</Data></Cell>
</Row>
</Table>
</Worksheet>

<Worksheet ss:Name="Sample Sheet 2">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table2">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
      <Cell><Data ss:Type="String">A</Data></Cell>
      <Cell><Data ss:Type="String">B</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">C</Data></Cell>
      <Cell><Data ss:Type="String">D</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">E</Data></Cell>
      <Cell><Data ss:Type="String">F</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">G</Data></Cell>
      <Cell><Data ss:Type="String">H</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook> 

Answer 3

The only bulletproof way of avoiding the "It looks like these numbers are stored as text" green triangles is to use the Open XML format. It is worth using it, just to avoid the inevitable green triangles.

Answer 4

Assuming this is for an intranet, where you can set permissions and mandate IE, you can generate the workbook client side with JScript/VBScript driving Excel. This gives you native Excel formatting, without the hassle of trying to automate Excel on the server.

I'm not sure I'd really recommend this approach anymore except in fairly niche scenarios, but it was fairly common during the classic ASP heydays.

Answer 5

If you have to use Excel instead of a CSV file you will need to use OLE automation on an Excel instance one the server. The easiest way to do this is to have a template file and programatically fill it in with the data. You save it to another file.

Tips:

• Don't do it interactively. Have the user kick off the process and then post a page with the link to the file. This mitigates potential performance issues while the spreadsheet is generated.
• Use the template as I described before. It makes it easier to modify it.
• Make sure that Excel is set to not pop up dialogs. On a web server this will hang the whole excel instance.
• Keep the Excel instance on a separate server, preferably behind a firewall, so it is not exposed as a potential security hole.
• Keep an eye on resource usage. Generating a spreadhseet over the OLE automation interface (the PIA's are just shims over this) is a fairly heavyweight process. If you need to scale this to high data volumes you may need to be somewhat clever with your architecture.

Some of the 'use mime-types to trick excel into opening HTML table' approaches would work if you don't mind the format of the file being a bit basic. These approaches also fob the CPU heavy work off onto the client. If you want fine-graned control over the format of the spreadsheet you will probably have to use Excel itself to generate the file as described above.

Answer 6

I've done this a couple of times and each time the easiest way was to simply return a CSV (Comma Separated Value) file. Excel imports it perfectly, and it's relatively fast to do.