public virtual IProcessingResult <IEnumerable <IXlsxParsedRow <T> > > Parse(Stream stream)
{
Raise.ArgumentNullException.IfIsNull(stream, nameof(stream));
var parsedEntities = new List <IXlsxParsedRow <T> >();
var errors = new List <IError>();
using (var spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
IProcessingResult <IXlsxParserConfig <T> > configBuildingResult = configFactory.Create(spreadsheetDocument);
if (configBuildingResult.Errors != null && configBuildingResult.Errors.Any())
{
return(new ProcessingResult <IEnumerable <IXlsxParsedRow <T> > >(Enumerable.Empty <IXlsxParsedRow <T> >(), configBuildingResult.Errors));
}
IXlsxParserConfig <T> config = configBuildingResult.Result;
var firstRow = config.FirstRow;
var lastRow = config.LastRow;
var firstColumn = XlsxColumnAddressConverter.ToInt(config.FirstColumn);
var lastColumn = XlsxColumnAddressConverter.ToInt(config.LastColumn);
var firstSheet = config.FirstSheet;
var lastSheet = config.LastSheet;
var fieldsMap = config.FieldsMap;
var typeAccessor = config.TypeAccessor;
var valueConverter = config.ValueConverter;
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
SharedStringTable sharedStringTable = workbookPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault()?
.SharedStringTable;
string[] sharedStrings = sharedStringTable?
.Select(x => x.InnerText)?
.ToArray();
sharedStringTable = null;
var sheets = workbookPart.Workbook.Descendants <Sheet>();
int sheetIndex = 1;
foreach (var sheet in sheets)
{
if (sheetIndex < firstSheet)
{
sheetIndex++;
continue;
}
string currentSheetName = sheet.Name;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
var rows = sheetData.Elements <Row>();
var rowIndex = 0;
foreach (var row in rows)
{
var rowNumber = rowIndex + 1;
if (rowNumber < firstRow)
{
rowIndex++;
continue;
}
T entity = (T)Activator.CreateInstance(typeof(T));
bool entityHasErrors = false;
var cells = row.Elements <Cell>();
var cellIndex = 0;
foreach (var cell in cells)
{
string cellAddress;
int columnNumber;
string column;
if (!string.IsNullOrEmpty(cell.CellReference))
{
cellAddress = cell.CellReference;
column = cellAddress.Replace(rowNumber.ToString(), string.Empty);
columnNumber = XlsxColumnAddressConverter.ToInt(column);
}
else
{
columnNumber = cellIndex + 1;
column = XlsxColumnAddressConverter.ToString(columnNumber);
cellAddress = $"{column}{rowNumber}";
}
if (columnNumber < firstColumn)
{
cellIndex++;
continue;
}
if (!fieldsMap.Has(column))
{
cellIndex++;
continue;
}
IEntityField <T> entityField = fieldsMap[column];
string rawValue = cell.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
rawValue = sharedStrings[int.Parse(rawValue)];
}
else if (cell.CellFormula != null)
{
if (cell.CellValue == null)
{
return(new ProcessingResult <IEnumerable <IXlsxParsedRow <T> > >(Enumerable.Empty <IXlsxParsedRow <T> >(), new[] { new XlsxProtectedViewError() }));
}
rawValue = cell.CellValue?.InnerText;
}
if (cell.DataType != null && cell.DataType.Value == CellValues.Error)
{
if (entityField.Type.GetTypeInfo().IsValueType ? Activator.CreateInstance(entityField.Type) != null : false)
{
errors.Add(new XlsxCellValueError(entityField.Description, cellAddress, currentSheetName, cell.CellValue.InnerText));
entityHasErrors = true;
}
else
{
rawValue = string.Empty;
}
}
if (valueConverter.TryConvert(rawValue, entityField.Type, out object value))
{
typeAccessor[entity, entityField.Name] = value;
}
else
{
errors.Add(new XlsxDataTypeError(entityField.Description, cellAddress, currentSheetName));
entityHasErrors = true;
}
if (columnNumber == lastColumn)
{
break;
}
cellIndex++;
}
if (!entityHasErrors)
{
parsedEntities.Add(new XlsxParsedRow <T>(rowNumber, currentSheetName, entity));
}
if (rowNumber == lastRow)
{
break;
}
rowIndex++;
}
if (sheetIndex == lastSheet)
{
break;
}
sheetIndex++;
}
}
return(new ProcessingResult <IEnumerable <IXlsxParsedRow <T> > >(parsedEntities, errors));
}
/// <summary>
/// Sets the value of a cell, optionally converting it to a string first.
/// </summary>
/// <param name="value">Value to set in the cell</param>
/// <param name="cell">Cell where to set the value</param>
/// <param name="sharedStringTable">Structure of the spreadsheet that contains the actual string values</param>
/// <param name="stylesheet">Style section of the spreadsheet, containing formatting information</param>
/// <param name="writeAsString">When true, the value will be converted to a string before writing to the cell</param>
private static void SetCellValue(object value, Cell cell, SharedStringTable sharedStringTable, Stylesheet stylesheet, bool writeAsString)
{
if (writeAsString)
{
value = Convert.ToString(value, CultureInfo.InvariantCulture);
}
if (value is string)
{
// For strings, we put them in the shared string table and reference by index, just like Office would
var tuple = sharedStringTable.Select((v, i) => System.Tuple.Create(v, i)).FirstOrDefault(t => t.Item1.InnerText.Equals(value));
int index;
if (tuple == null)
{
// The string is not in the table, so we add it
sharedStringTable.AppendChild(new SharedStringItem(new Text((string)value)));
index = (int)sharedStringTable.Count.Value;
// Yes, you need to update these manually
sharedStringTable.Count++;
sharedStringTable.UniqueCount++;
}
else
{
index = tuple.Item2;
}
cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture));
cell.DataType = CellValues.SharedString;
}
else if (value is bool)
{
cell.CellValue = new CellValue(Convert.ToInt32((bool)value).ToString(CultureInfo.InvariantCulture));
cell.DataType = CellValues.Boolean;
}
else if (value is DateTime)
{
const int DateTimeFormatId = 22;
// Search for a style using the DateTime format id
var tuple = stylesheet.CellFormats.Select((v, i) => System.Tuple.Create((CellFormat)v, i)).FirstOrDefault(p => p.Item1.NumberFormatId != null && p.Item1.NumberFormatId.Value == DateTimeFormatId);
uint index;
if (tuple == null)
{
// No such style exists so we create one
stylesheet.CellFormats.AppendChild(new CellFormat()
{
NumberFormatId = DateTimeFormatId, FormatId = 0
});
index = stylesheet.CellFormats.Count.Value;
// We update this for the sake of consistency
stylesheet.CellFormats.Count++;
}
else
{
index = (uint)tuple.Item2;
}
cell.StyleIndex = index;
cell.CellValue = new CellValue(((DateTime)value).ToOADate());
cell.DataType = null;
}
else
{
// This is for long and double. Also acts as the default behavior for things we do not specially handle.
cell.CellValue = new CellValue(Convert.ToString(value, CultureInfo.InvariantCulture));
cell.DataType = null;
}
}
