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