TopMenu

Friday, 1 July 2011

Sometimes it is necessary to provide the functionality to download a Tabular report into Excel. When you are using a simple GridView and want to export it to the Excel. Most of the time the code used for exporting to Excel is like: string attachment = "attachment; filename=ExcelReport.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";

But this code is not foolproof for writing to an Excel file on stream since this will give you the following message when you open the excel file.

Microsoft has provided the solution to this problem, suggesting to edit the GroupSecurity setting. After updating the registery setting you'll not receive this error. See the details here

But this is not possible when you are serving a public website and have utility to export to Excel and this message may annoy your user.

So I found a solution for this problem of how you can use an open source library to create an Excel without using the Microsoft InterOp library to create the Excel file.

Go and Download the library file fromhere. Now add the assembly reference to you web application or any project you want.

Below is the sample code to create a very simple Excel file from a datatable using this library and writing it to the response stream in your ASP.Net web application.using OfficeOpenXml;using OfficeOpenXml.Style;

//Write it back to the client Response.BinaryWrite(fileBytes);
Response.End();
}
}

Now you'll have a fresh and clean proper Excel format file ready to download. It won't prompt any warning or error while opening.

Note: If your data table has a column of datetime type in the source datatable then you may not see the proper values in the created excel sheet. So here's the workaround. You have to explicity set the format of that column to a date type:ExcelColumn OrderDate = ws.Column(<order no. of Date Column for e.g. 3>);

OrderDate.Style.Numberformat.Format = "DD/MM/YYYY";
This is just a simple example to create an Excel using the EPPlus Excel library and it provides much more powerful Excel operations than just creating a simple Excel. The above example was to demonstrate the simple creation of an Excel file. Explore more of the power of the EPPlus library; see the documentation here.

Important : Create a proper Response header before writing the created Excel to the stream to prevent any warning.

1 comment:

Thanks for this sample and this works great in IE7, Firefox and even Safari. On IE9 - it doesn't prompt for the File/SaveAs dialog box and it just freezes.Why would IE9 not look at Content Disposition of attachment and prompt the user to open/save via the standard dialogue box?