CODEWITHSUNDEEP
c#asp.netvisual-studio-2008excel-2007openxml-sdk
engg
engg

Reputation: 541

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

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.

Upvotes: 3

Views: 10329

Answers (3)

Rumit Parakhiya
Rumit Parakhiya

Reputation: 2714

I too had come across similar requirement to export dataset into excel. I used this open source library. It's based on Open XML standards and doesn't use Office Interop. It met my requirement. But, my requirement was basic. So, check if it fulfills your requirement.

Upvotes: 0

Glennular
Glennular

Reputation: 18215

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);

Upvotes: 1

Leon Breedt
Leon Breedt

Reputation: 1216

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.

Upvotes: 1

Related Questions