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