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);
}
/// <summary>
/// This is mostly just for testing. You should be streaming the data in, I've just had problems with that in the past
/// </summary>
/// <param name="excelFileData"></param>
/// <param name="worksheetPosition"></param>
/// <param name="firstRowIsHeader"></param>
/// <param name="columnType"></param>
/// <returns></returns>
public static DataTable TransformWorksheetToDataTable(byte[] excelFileData,
int worksheetPosition = 0,
bool firstRowIsHeader = false,
Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
using (var ms = new System.IO.MemoryStream(excelFileData))
{
return(TransformWorksheetToDataTable(ms, worksheetPosition, firstRowIsHeader, columnType));
}
}
public static DataTable TransformWorksheetToDataTable(string excelFilePath,
string worksheetName,
bool firstRowIsHeader = false,
Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
using (var stream = CreateReadOnlyFileStream(excelFilePath))
{
DataTable table = TransformWorksheetToDataTable(stream, worksheetName, firstRowIsHeader, columnType);
return(table);
}
}
public static DataTable TransformWorksheetToDataTable(System.IO.Stream excelSpreadsheetStream,
int worksheetPosition = 0,
bool firstRowIsHeader = false,
Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
lib.global.setup();
using (OfficeOpenXml.ExcelPackage pkg = new OfficeOpenXml.ExcelPackage(excelSpreadsheetStream))
{
DataTable table = TransformWorksheetToDataTable(pkg, worksheetPosition, firstRowIsHeader, columnType);
return(table);
}
}
public static DataTable TransformWorksheetToDataTable(ExcelWorksheet worksheet, bool firstRowIsHeader = false, Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
if (worksheet.Dimension == null)
{
return(new DataTable()); // worksheet was empty
}
var targetRange = worksheet.Cells[worksheet.Dimension.Address];
DataTable table = TransformRangeToDataTable(targetRange, firstRowIsHeader, columnType);
return(table);
}
public static DataTable TransformWorksheetToDataTable(ExcelPackage package, int worksheetPosition = 0, bool firstRowIsHeader = false, Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
var worksheet = package.Workbook.Worksheets[worksheetPosition];
DataTable table = TransformWorksheetToDataTable(worksheet, firstRowIsHeader, columnType);
return(table);
}
public static DataTable TransformWorksheetToDataTable(ExcelPackage package, string worksheetName, bool firstRowIsHeader = false, Model.ColumnNameType columnType = Model.ColumnNameType.CellValue)
{
var worksheet = package.Workbook.Worksheets[worksheetName];
if (worksheet == null)
{
return(null);
}
DataTable table = TransformWorksheetToDataTable(worksheet, firstRowIsHeader, columnType);
return(table);
}
