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. }
    29. //Write it back to the client
    30. if (File.Exists(p_strPath))
    31. File.Delete(p_strPath);
    33. //Create excel file on physical disk
    34. FileStream objFileStrm = File.Create(p_strPath);
    35. objFileStrm.Close();
    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.