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";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
customerGridView.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
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 from here. 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;
private void ExportExcel(DataTable dt)
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SearchReport");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(dt, true);
//prepare the range for the column headers
string cellRange = "A1:" + Convert.ToChar('A' + dt.Columns.Count - 1) + 1;
//Format the header for columns
using (ExcelRange rng = ws.Cells[cellRange])
{
rng.Style.WrapText = false;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.Gray);
rng.Style.Font.Color.SetColor(Color.White);
}
//prepare the range for the rows
string rowsCellRange = "A2:" + Convert.ToChar('A' + dt.Columns.Count - 1) + dt.Rows.Count * dt.Columns.Count;
//Format the rows
using (ExcelRange rng = ws.Cells[rowsCellRange])
{
rng.Style.WrapText = false;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
//Read the Excel file in a byte array
Byte[] fileBytes = pck.GetAsByteArray();
//Clear the response
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Cookies.Clear();
//Add the header & other information
Response.Cache.SetCacheability(HttpCacheability.Private);
Response.CacheControl = "private";
Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
Response.AppendHeader("Pragma", "cache");
Response.AppendHeader("Expires", "60");
Response.AppendHeader("Content-Disposition",
"attachment; " +
"filename=\"ExcelReport.xlsx\"; " +
"size=" + fileBytes.Length.ToString() + "; " +
"creation-date=" + DateTime.Now.ToString("R") + "; " +
"modification-date=" + DateTime.Now.ToString("R") + "; " +
"read-date=" + DateTime.Now.ToString("R"));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//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.
Have a great experience Excel exporting !!!