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:
- Download EPPlus.dll. f
- Add reference to EPPlus.dll in your project
- 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)
- {
-
- ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dtSrc.TableName);
-
- objWorksheet.Cells["A1"].LoadFromDataTable(dtSrc, true);
- objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10));
- objWorksheet.Cells.AutoFitColumns();
-
- 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);
- }
- }
-
-
- if (File.Exists(p_strPath))
- File.Delete(p_strPath);
-
-
- FileStream objFileStrm = File.Create(p_strPath);
- objFileStrm.Close();
-
-
- File.WriteAllBytes(p_strPath, objExcelPackage.GetAsByteArray());
- }
- }
- }
- 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.