ARTICLE

Web application Export to Excel Advanced using open source library EPPlus

Posted by Amit Choudhary Articles | WebForms Controls April 27, 2011
Here you will see Web application Export to Excel Advanced using open source library EPPlus.
Reader Level:


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.

Web1.gif

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

COMMENT USING