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(); } }