public static byte[] SaveDataTableToExcelTable(byte[] excelData, DataTable table, bool printHeader = false, string worksheetName = "data")
{
lib.global.setup();
using (var ms = new System.IO.MemoryStream(excelData))
{
using (ExcelPackage pkg = new ExcelPackage(ms))
{
// if the worksheet exists delete it
ExcelUtility.DeleteWorksheet(pkg, worksheetName);
ExcelWorksheet ws = ExcelUtility.GetOrAddWorksehet(pkg, worksheetName);
ws.Cells["A1"].LoadFromDataTable(table, printHeader);
if (table.Rows.Count < 1)
{
throw new Exception("Cannot write empty DataTable to excel");
}
//var tableRange = ws.Cells[1, 1, table.Rows.Count + 1, table.Columns.Count];
ws.Tables.Add(ws.Dimension, $"table{Guid.NewGuid().ToString("N")}");
// auto fit all the columns
ws.Cells[ws.Dimension.Address].AutoFitColumns();
using (var outMS = new System.IO.MemoryStream())
{
pkg.SaveAs(outMS);
return(outMS.ToArray());
}
}
}
}
public static void SaveDataTableToExcel(System.IO.Stream destinationStream, DataTable table, bool printHeader = false, string worksheetName = "data")
{
lib.global.setup();
using (ExcelPackage pkg = new ExcelPackage(destinationStream))
{
ExcelWorksheet ws = ExcelUtility.GetOrAddWorksehet(pkg, worksheetName);
ws.Cells["A1"].LoadFromDataTable(table, printHeader);
pkg.Save();
}
}
