How to export data to excel using mvc without using third party control.

Question

I want to export data to excel both in .xls and .xlsx format in asp.net MVC without using any third party control.

I tried using following code but it doesn't support .xlsx format.

Response.AddHeader("Content-Disposition", "attachment; 
filename=test.xlsx");
Response.ContentType = "application/ms-excel";
Response.Write(sw.ToString());
Response.End();

Pls give me solution.

Answer 1

You should use the Open XML SDK 2.0 to achieve that task since it is the supported Microsoft way of creating Office documents.

The other hacky way is to create a partial view and export the html to Excel. This would give a readonly view to the data since its not in the proper excel format, but works when you don't want to spend the time to write the Open XML.

Also the correct MIMEConentType for .xlsx is "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" and for .xls it is "application/vnd.ms-excel"

Answer 2

Besides Open XML SDK 2.0 described by @amurra, you can try OpenExcel. It is excellent library and is quite fast.

A quick and dirty option is to put all your data in a table and write that table only to response stream with the headers you are trying already, like:

Response.AddHeader("Content-Disposition", "attachment; filename=test.xlsx"); 
Response.ContentType = "application/ms-excel"; 
 //NOW WRITE TABLE
Response.Write("<table><tr><td>SrNo</td></tr><tr><td>1</td></tr></table>"); 
Response.End();

I've not used this hack but have seen it used in some application. When opening it Excel gives user a warning that the data is not excel format, but opens it successfully.

Answer 3

For your lines maybe this work, but I'm not sure about what are you trying to do.

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AppendHeader("Content-Disposition", "attachment; filename=test.xlsx")
Response.Write(sw.ToString())
Response.End()