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 DataTable TransformRangeToDataTable(ExcelRange range, bool firstRowIsHeader = false, Model.ColumnNameType columnType = Model.ColumnNameType.CellValue) { DataTable dt = new DataTable(); int rowStart = range.Start.Row; // form columns for the data table for (int columnNumber = range.Start.Column; columnNumber <= range.End.Column; ++columnNumber) { string excelColumnAddressName = ExcelUtility.GetExcelColumnFromNumber(columnNumber); string cellValueAsString = ExcelReadUtility.ReadCellAsString(range.Worksheet, rowStart, columnNumber); if (firstRowIsHeader == true && columnType == Model.ColumnNameType.CellValue && !string.IsNullOrEmpty(cellValueAsString) ) { DataColumn column = new DataColumn(cellValueAsString, typeof(object)); dt.Columns.Add(column); } else { // use excel column address name DataColumn column = new DataColumn(excelColumnAddressName, typeof(object)); dt.Columns.Add(column); } } if (firstRowIsHeader == true) { // take row 1 as a header ++rowStart; // which means data will start on the next row } // loop through rows for (int rowNum = rowStart; rowNum <= range.End.Row; rowNum++) { DataRow row = dt.NewRow(); int dataTableColumnIndex = 0;// !!!! REMEMBER !!!! - The DataTable column indexes start at 0, the spreadsheet column index could be anything. Understand that they are different... // loop through columns for (int columnNum = range.Start.Column; columnNum <= range.End.Column; columnNum++) { object cellValue = ExcelReadUtility.ReadCell(range.Worksheet, rowNum, columnNum); row[dataTableColumnIndex] = cellValue; ++dataTableColumnIndex; }// end of looping through columns dt.Rows.Add(row); }// end of looping through rows return(dt); }
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(); } }