Export To Excel Using EPPlus

This blog describes how you can use EPPLus to export data into excel from a webpage.

EPPlus is a very helpful open-source 3rd party dll for exporting data to excel. To implement the same in you project, follow the below steps:

1. Download EPPlus.dll. f
2. Add reference to EPPlus.dll in your project
3. Use the following Class:
   

   using OfficeOpenXml;
   using OfficeOpenXml.Drawing;
   using OfficeOpenXml.Style;
   public class clsGeneral
   {
      public void GenerateExcel2007(string p_strPath, DataSet p_dsSrc)
      {
         using (ExcelPackage objExcelPackage = new ExcelPackage())
         {
            foreach (DataTable dtSrc in p_dsSrc.Tables)
            {
               //Create the worksheet
               ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dtSrc.TableName);
               //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
               objWorksheet.Cells["A1"].LoadFromDataTable(dtSrc, true);
               objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10));
               objWorksheet.Cells.AutoFitColumns();
               //Format the header
               using (ExcelRange objRange = objWorksheet.Cells["A1:XFD1"])
               {
                  objRange.Style.Font.Bold = true;
                  objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                  objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                  objRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
                  objRange.Style.Fill.BackgroundColor.SetColor(Color.FromA#eaeaea);
               }
            }
   
            //Write it back to the client
            if (File.Exists(p_strPath))
               File.Delete(p_strPath);
   
            //Create excel file on physical disk
            FileStream objFileStrm = File.Create(p_strPath);
            objFileStrm.Close();
   
            //Write content to excel file
               File.WriteAllBytes(p_strPath, objExcelPackage.GetAsByteArray());
         }
      }
   }

4. Call the function from any page of your application with appropriate details.

That's it. Simple and clean. For advance implementation of EPPlus, you can refer here.