Web application Export to Excel Advanced using open source library EPPlus

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 !!!