private static (CellType, DateOrDoubleOrString) ParseCellContent(ICell c) { var res = new DateOrDoubleOrString(); if (c.CellType == NPOI.SS.UserModel.CellType.Numeric) { if (DateUtil.IsCellDateFormatted(c)) { var date = DateUtil.GetJavaDate(c.NumericCellValue); // var dateFormat = c.CellStyle.GetDataFormatString (); res.date = date; } else { res.value = c.NumericCellValue; } } else if (c.CellType == NPOI.SS.UserModel.CellType.String) { // System.Console.WriteLine($"{c.RowIndex} - {c.ColumnIndex}"); res.str = c.StringCellValue; } return(c.CellType, res); }
public static IEnumerable <Dictionary <string, (CellType, DateOrDoubleOrString, bool)> > GetColumnsOfEachRow(string filepath, string sheetname, List <string> columnName) { using (var fs = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet(sheetname); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); var rowCount = sheet.LastRowNum; var firstRow = sheet.GetRow(0); var columnCount = firstRow.LastCellNum; var headings = new List <string> (); var columnIndexesToCollectData = new List <int> (); for (int i = 0; i <= rowCount; i++) { var row = sheet.GetRow(i); if (i == 0) // Headings { headings = ExtractHeadings(row, columnCount); foreach (var colName in columnName) { var ci = headings.IndexOf(colName.ToLower()); columnIndexesToCollectData.Add(ci); if (ci == -1) { throw new Exception($"There is no Column with Name '{colName}'"); } } } else if (!IsEmpty(row)) { var res = new Dictionary <string, (CellType, DateOrDoubleOrString, bool)> (); var addedColumns = new List <int>(); for (IEnumerator cit = row.GetEnumerator(); cit.MoveNext();) { ICell c = (ICell)cit.Current; if (columnIndexesToCollectData.Contains(c.ColumnIndex)) { var(cellType, content) = ExcelReader.ParseCellContent(c); if (cellType == NPOI.SS.UserModel.CellType.Numeric) { addedColumns.Add(c.ColumnIndex); if (DateUtil.IsCellDateFormatted(c)) { res.Add(headings[c.ColumnIndex], (cellType, content, true)); } else { res.Add(headings[c.ColumnIndex], (cellType, content, false)); } } else if (cellType == NPOI.SS.UserModel.CellType.String) { addedColumns.Add(c.ColumnIndex); res.Add(headings[c.ColumnIndex], (cellType, content, false)); } else { // If any other type of Cell appears // TODO } } } foreach (var missedItem in columnIndexesToCollectData.Except(addedColumns)) { var d = new DateOrDoubleOrString(); d.str = ""; res.Add(headings[missedItem], (NPOI.SS.UserModel.CellType.String, d, false)); } yield return(res); } } } }