Exporting dataset to Excel file with multiple sheets in ASP.NET

Question

In C# ASP.NET 3.5 web application, I need to export multiple datatables (or a dataset) to an Excel 2007 file with multiple sheets, and then provide the user with 'Open/Save' dialog box, WITHOUT saving the Excel file on the web server.

I have used Excel Interop before. I have been reading that it's not efficient and is not the best approach to achieve this and there are more ways to do it, 2 of them being: 1) Converting data in datatables to an XML string that Excel understands 2) Using OPEN XML SDK 2.0.

It looks like OPEN XML SDK 2.0 is better, please let me know. Are there any other ways to do it? I don't want to use any third-party tools.

If I use OPEN XML SDK, it creates an excel file, right? I don't want to save it on the (Windows 2003) server hard drive (I don't want to use Server.MapPath, these Excel files are dynamically created, and they are not required on the server, once client gets them). I directly want to prompt the user to open/save it. I know how to do it when the 'XML string' approach is used.

Please help. Thank you.

Solution 1

You can easily stream the xml response to the user as an XML Excel file.

Any Page:

<a href="report.aspx" target="_blank"> Open excel Report</a>

Report.aspx:

 Response.Clear();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition"
                     , "attachment;filename=" & _fileName & ".xml");
 Response.Write("<?xml version=""1.0""?>");
 Response.Write(excelXML);

Solution 2

Is Excel 2007 support absolutely required?

We have used NPOI with great success, and it supports all the features we want (multiple worksheets, formatting, formulas). It is also pretty stable.

The files it produces are in Excel 2003 format though, so they are binary, not OOXML.

This question has been asked before, see here for a better discussion.