public IEnumerable <SheetModel <T> > ReadExcelDocument <T>(Stream fileStream, bool headerInFirstRow = true) where T : class, new()
{
if (fileStream == null)
{
return(null);
}
var results = new List <SheetModel <T> >();
var efm = ReadExcelDocument(fileStream, headerInFirstRow);
if (efm == null)
{
return(null);
}
foreach (var efmSheet in efm.Sheets)
{
var sheet = new SheetModel <T>()
{
SheetId = efmSheet.SheetId,
SheetName = efmSheet.SheetName
};
foreach (var efmRow in efmSheet.Rows)
{
var row = new T();
foreach (var property in row.GetType().GetProperties().Where(t => t.CanWrite && !t.GetGetMethod().IsVirtual))
{
var match = efmRow.Cells.FirstOrDefault(t => t.ColumnName.Equals(property.Name, StringComparison.InvariantCultureIgnoreCase));
if (match != null)
{
property.SetValue(row, match.CellValue);
}
}
sheet.Rows.Add(row);
}
results.Add(sheet);
}
return(results);
}
public ExcelFileModel ReadExcelDocument(Stream fileStream, bool headerInFirstRow = true)
{
if (fileStream == null)
{
return(null);
}
var efm = new ExcelFileModel();
using (var document = SpreadsheetDocument.Open(stream: fileStream, isEditable: false))
{
var wBookPart = document.WorkbookPart;
var sheets = wBookPart.Workbook.GetFirstChild <Sheets>();
foreach (Sheet sheet in sheets)
{
Worksheet wSheet = ((WorksheetPart)wBookPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetData = (SheetData)wSheet.GetFirstChild <SheetData>();
var sheetModel = new SheetModel()
{
SheetId = Convert.ToInt32(sheet.Id),
SheetName = sheet.Name
};
int rowIdx = 0;
int cellIdx = 1;
var columnNames = new List <(int idx, string name)>();
var rowModels = new List <RowModel>();
foreach (Row row in sheetData)
{
RowModel rowModel = null;
if (headerInFirstRow && rowIdx == 0)
{
foreach (Cell cell in row)
{
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
int id;
if (int.TryParse(cell.InnerText, out id))
{
var item = wBookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
columnNames.Add((idx: cellIdx, name: item?.Text?.Text ?? item?.InnerText ?? item?.InnerXml));
}
}
}
else
{
columnNames.Add((idx: cellIdx, name: cell.InnerText ?? cellIdx.ToString()));
}
cellIdx += 1;
}
}
else
{
rowModel = new RowModel()
{
RowId = rowIdx
};
foreach (Cell cell in row)
{
if (rowIdx == 0)
{
columnNames.Add((idx: cellIdx, name: cellIdx.ToString()));
}
var res = new CellModel()
{
RowId = rowIdx,
ColumnName = columnNames.FirstOrDefault(t => t.idx == cellIdx).name,
CellId = cellIdx
};
if (cell.CellFormula?.InnerText == null)
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
if (int.TryParse(cell.InnerText, out int id))
{
var item = wBookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
res.CellValue = item?.Text?.Text ?? item?.InnerText ?? item?.InnerXml;
}
break;
case CellValues.Number:
if (decimal.TryParse(cell.InnerText, out decimal numberVal))
{
res.CellValue = cell.InnerText;
res.CellValueNumber = numberVal;
}
break;
case CellValues.Date:
if (DateTime.TryParse(cell.InnerText, out DateTime dateVal))
{
res.CellValue = cell.InnerText;
res.CellValueDateTime = dateVal;
}
break;
case CellValues.Boolean:
res.CellValue = cell.InnerText;
switch (cell.InnerText)
{
case "0":
res.CellValueBoolean = false;
break;
default:
res.CellValueBoolean = true;
break;
}
break;
default:
res.CellValue = cell.InnerText;
break;
}
}
else
{
try
{
EPCore.SetFormattedValue(cell, ref res);
}
catch
{
res.CellValue = cell.InnerText;
}
}
}
rowModel.Cells.Add(res);
cellIdx += 1;
}
}
if (rowModel != null)
{
sheetModel.Rows.Add(rowModel);
}
rowIdx += 1;
cellIdx = 1;
}
efm.Sheets.Add(sheetModel);
}
}
return(efm);
}
