Export DataTable to Excel with Open XML in c#

This code sample will illustrate how we can create Excel file from a data table in c#.
You will need to add DocumentFormat.OpenXml and WindowsBase dll references to make it work.

private static void WriteExcelFile(string outputPath, DataTable table)
{
 using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileToGenerate, SpreadsheetDocumentType.Workbook))
 {
  WorkbookPart workbookPart = document.AddWorkbookPart();
  workbookPart.Workbook = new Workbook();

  WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
  var sheetData = new SheetData();
  worksheetPart.Worksheet = new Worksheet(sheetData);

  Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
  Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };

  sheets.Append(sheet);

  Row headerRow = new Row();

  List<String> columns = new List<string>();
  foreach (System.Data.DataColumn column in table.Columns)
  {
   columns.Add(column.ColumnName);

   Cell cell = new Cell();
   cell.DataType = CellValues.String;
   cell.CellValue = new CellValue(column.ColumnName);
   headerRow.AppendChild(cell);
  }

  sheetData.AppendChild(headerRow);

  foreach (DataRow dsrow in table.Rows)
  {
   Row newRow = new Row();
   foreach (String col in columns)
   {
    Cell cell = new Cell();
    cell.DataType = CellValues.String;
    cell.CellValue = new CellValue(dsrow[col].ToString());
    newRow.AppendChild(cell);
   }

   sheetData.AppendChild(newRow);
  }

  workbookPart.Workbook.Save();
 }
}

Read More