Export To Excel Using EPPlus

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:
    1. using OfficeOpenXml;    
    2. using OfficeOpenXml.Drawing;    
    3. using OfficeOpenXml.Style;    
    4. public class clsGeneral    
    5. {    
    6.    public void GenerateExcel2007(string p_strPath, DataSet p_dsSrc)    
    7.    {    
    8.       using (ExcelPackage objExcelPackage = new ExcelPackage())    
    9.       {    
    10.          foreach (DataTable dtSrc in p_dsSrc.Tables)    
    11.          {    
    12.             //Create the worksheet    
    13.             ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dtSrc.TableName);    
    14.             //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1    
    15.             objWorksheet.Cells["A1"].LoadFromDataTable(dtSrc, true);    
    16.             objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10));    
    17.             objWorksheet.Cells.AutoFitColumns();    
    18.             //Format the header    
    19.             using (ExcelRange objRange = objWorksheet.Cells["A1:XFD1"])    
    20.             {    
    21.                objRange.Style.Font.Bold = true;    
    22.                objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;    
    23.                objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;    
    24.                objRange.Style.Fill.PatternType = ExcelFillStyle.Solid;    
    25.                objRange.Style.Fill.BackgroundColor.SetColor(Color.FromA#eaeaea);    
    26.             }    
    27.          }    
    28.     
    29.          //Write it back to the client    
    30.          if (File.Exists(p_strPath))    
    31.             File.Delete(p_strPath);    
    32.     
    33.          //Create excel file on physical disk    
    34.          FileStream objFileStrm = File.Create(p_strPath);    
    35.          objFileStrm.Close();    
    36.     
    37.          //Write content to excel file    
    38.             File.WriteAllBytes(p_strPath, objExcelPackage.GetAsByteArray());    
    39.       }    
    40.    }    
    41. }   
  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.
Ebook Download
View all
Learn
View all