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