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