C# Export DataTable values to Excel:
public static void InsertWorksheet(DataTable table, string docName)
{
try
{
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart and Workbook objects.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart < WorksheetPart > ();
// Create Worksheet and SheetData objects.
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add a Sheets object.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());
// Append the new worksheet named “Report” and associate it
// with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = “Report”
};
sheets.Append(sheet);
// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild < SheetData > ();
//////////////////////////////////
// Add a row to the cell table.
Row row;
row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
List < String > columns = new List < string > ();
//Adding Header row
foreach(System.Data.DataColumn column in table.Columns)
{
Cell refCell = null;
Cell newCell = new Cell();
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(column.ColumnName);
columns.Add(column.ColumnName);
}
UInt32 iIndex = 1;
//Adding data row
foreach(System.Data.DataRow dsrow in table.Rows)
{
Row rowdata;
rowdata = new Row()
{
RowIndex = iIndex + 1
};
sheetData.Append(rowdata);
foreach(String col in columns)
{
Cell refCell = null;
Cell newCell = new Cell();
rowdata.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(dsrow[col].ToString());
}
iIndex++;
}
// Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{}
}