public static bool DeserializeWorkbook(object model, ExcelWorkbook source, XlsxSerializerSettings settings)
{
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
var mainsheetProcessed = false;
foreach (var sheetAssociation in SheetAssociation.GetSheetAssociations(model.GetType()))
{
var sheet = source.Worksheets[sheetAssociation.SheetName];
if (sheet == null)
{
continue;
}
if (sheetAssociation.BoundProperty == null)
{
mainsheetProcessed = true;
XlsSheetDeserializerCore.Deserialize(model, sheet, settings);
continue;
}
if (ReflectionHelper.GetIsCollection(sheetAssociation.BoundProperty.PropertyType, out var itemType,
true))
{
var collection = XlsCollectionDeserializerCore
.DeserializeCollection(itemType, sheet, () => Activator.CreateInstance(itemType), 0, 0, settings)
.OfType <object>().ToList();
ReflectionHelper.PopulateCollectionProperty(model, sheetAssociation.BoundProperty, collection);
continue;
}
var sheetModel = sheetAssociation.BoundProperty.GetValue(model, null);
if (sheetModel == null)
{
if (!sheetAssociation.BoundProperty.CanWrite)
{
continue;
}
sheetModel = Activator.CreateInstance(sheetAssociation.BoundProperty.PropertyType);
sheetAssociation.BoundProperty.SetValue(model, sheetModel, null);
}
XlsSheetDeserializerCore.Deserialize(sheetModel, sheet, settings);
}
return(mainsheetProcessed);
}
public void WriteCell(object propertyOwner, ExcelRange cell, XlsxSerializerSettings settings)
{
foreach (var cellSetup in m_cellSetups)
{
cellSetup.Apply(BoundProperty, cell);
}
foreach (var setter in m_cellValueAccessors)
{
setter.WriteCellValue(BoundProperty, propertyOwner, cell, settings);
}
}
public object ReadCell(Func <object> propertyOwnerFactory, ExcelRange cell, XlsxSerializerSettings settings)
{
if (cell.Value == null)
{
return(null);
}
var owner = propertyOwnerFactory();
foreach (var setter in m_cellValueAccessors)
{
setter.ReadCellValue(cell, owner, BoundProperty, settings);
}
return(owner);
}
public static void SerializeWorkbook(object model, ExcelWorkbook target, XlsxSerializerSettings settings)
{
if (model == null)
{
return;
}
foreach (var sheetAssociation in SheetAssociation.GetSheetAssociations(model.GetType()))
{
var sheet = target.Worksheets[sheetAssociation.SheetName];
if (sheet == null)
{
sheet = target.Worksheets.Add(sheetAssociation.SheetName);
foreach (var setup in sheetAssociation.SheetSetup)
{
setup.SetupSheet(sheet);
}
}
object sheetModelValue;
if (sheetAssociation.BoundProperty == null)
{
sheetModelValue = model;
XlsSheetSerializerCore.Serialize(model.GetType(), model, sheet, settings);
continue;
}
if ((!sheetAssociation.BoundProperty.CanRead) ||
((sheetModelValue = sheetAssociation.BoundProperty.GetValue(model)) == null))
{
continue;
}
if (ReflectionHelper.GetIsCollection(sheetAssociation.BoundProperty.PropertyType,
out var collectionItemType, true))
{
XlsCollectionSerializerCore.SerializeCollection(collectionItemType, (IEnumerable)sheetModelValue, sheet, 0, 0, settings);
}
public static void Deserialize(object modelInstance, ExcelWorksheet source, XlsxSerializerSettings settings)
{
if (modelInstance == null)
{
throw new ArgumentNullException(nameof(modelInstance));
}
var mapping = DocumentMapper.GetMapping(modelInstance.GetType());
foreach (var rule in mapping.Values)
{
if (rule.CellLocation.Row == null)
{
continue;
}
var sourceCell = source.Cells[rule.CellLocation.Row.Value + 1, rule.CellLocation.Column + 1];
rule.ReadCell(() => modelInstance, sourceCell, settings);
}
}
public static void Serialize(Type modelType, object model, ExcelWorksheet target, XlsxSerializerSettings settings)
{
var mapping = DocumentMapper.GetMapping(modelType);
foreach (var m in mapping.Values)
{
if (m.CellLocation.Row == null)
{
continue;
}
var cell = target.Cells[m.CellLocation.Row.Value + 1, m.CellLocation.Column + 1];
m.WriteCell(model, cell, settings);
}
}
public void ReadCellValue(ExcelRange cell, object owner, PropertyInfo targetProperty, XlsxSerializerSettings settings)
{
if (ReflectionHelper.GetIsCollection(targetProperty.PropertyType, out var collectionItemType, true))
{
var collection = XlsCollectionDeserializerCore.DeserializeCollection(collectionItemType, cell.Worksheet,
() => Activator.CreateInstance(collectionItemType), cell.Start.Row - 1, cell.Start.Column - 1, settings).OfType<object>().ToList();
ReflectionHelper.PopulateCollectionProperty(owner, targetProperty, collection);
return;
}
var pao = new PropertyAndOwnerInstance(targetProperty, owner);
Func<PropertyAndOwnerInstance, bool> defaultShouldProcessCellDecision = (p) => p.Property.CanWrite;
Func<ExcelRange, Type, PropertyAndOwnerInstance, IValueConverter, object> defaultConvertCellValueToType = (cl, tp, p, converter) =>
{
var cellValue = cl.Value;
if (cellValue == null)
{
return null;
}
return converter.FromCellValue(tp, cellValue);
};
Action<PropertyAndOwnerInstance, object> defaultPropertySetter = (p, v) => p.Property.SetValue(p.PropertyOwner, v);
settings.CellReaderInterceptor.ReadCell(cell,
owner,
targetProperty,
defaultShouldProcessCellDecision,
defaultConvertCellValueToType,
defaultPropertySetter,
settings);
}
public virtual void WriteCellValue(PropertyInfo sourceProperty, object owner, ExcelRange cell, XlsxSerializerSettings settings)
{
Func<PropertyAndOwnerInstance, object> defaultValueReader = (pao) =>
{
if (!pao.Property.CanRead || (!string.IsNullOrWhiteSpace(cell.Formula)) || (!string.IsNullOrWhiteSpace(cell.FormulaR1C1)))
{
return null;
}
return pao.Property.GetValue(pao.PropertyOwner, null);
};
Action<Type, object, ExcelRange, IValueConverter> defaultWrite = (t, val, cel, con) =>
{
var formula = cell.Formula;
var formulaRC = cell.FormulaR1C1;
cell.Value = con.ToCellValue(t, val, cell);
if (!string.IsNullOrWhiteSpace(formula))
{
cell.Formula = formula;
}
if (!string.IsNullOrWhiteSpace(formulaRC))
{
cell.FormulaR1C1 = formulaRC;
}
};
settings.CellWriterInterceptor.Write(owner, sourceProperty, defaultValueReader, cell, defaultWrite, settings);
}
public static void SerializeCollection(Type itemType, IEnumerable collection, ExcelWorksheet target, int startRow, int startColumn, XlsxSerializerSettings settings)
{
var mapping = DocumentMapper.GetMapping(itemType);
var sheetRow = 1 + startRow;
if (mapping.HasHeader)
{
for (var i = mapping.StartColumn; i <= mapping.EndColumn; i++)
{
var headerCell = target.Cells[sheetRow, i + 1 + startColumn];
mapping.SetupHeader(i, headerCell);
}
sheetRow++;
}
foreach (var item in collection)
{
foreach (var columnMapping in mapping.Values)
{
columnMapping.WriteCell(item, target.Cells[sheetRow, columnMapping.CellLocation.Column + 1 + startColumn], settings);
}
sheetRow++;
}
}
public object ReadCell(Func <object> propertyOwner, ExcelRange cell, XlsxSerializerSettings settings)
{
return(cell.Value);
}
public void WriteCell(object propertyOwner, ExcelRange cell, XlsxSerializerSettings settings)
{
cell.Value = propertyOwner;
}
public override void WriteCellValue(PropertyInfo sourceProperty, object owner, ExcelRange cell, XlsxSerializerSettings settings)
{
if (ReflectionHelper.GetIsCollection(sourceProperty.PropertyType, out var collectionItemType, true))
{
var value = sourceProperty.GetValue(owner, null) as IEnumerable ?? new object[0];
XlsCollectionSerializerCore.SerializeCollection(collectionItemType, value, cell.Worksheet, cell.Start.Row - 1, cell.Start.Column - 1, settings);
return;
}
base.WriteCellValue(sourceProperty, owner, cell, settings);
}
public static IEnumerable DeserializeCollection(Type collectionItemType, ExcelWorksheet source, Func <object> newItemFactory, int startRow, int startColumn, XlsxSerializerSettings settings)
{
var mapping = DocumentMapper.GetMapping(collectionItemType);
var sheetRow = (mapping.HasHeader ? 2 : 1) + startRow;
for (; sheetRow <= (source.Dimension?.End?.Row ?? 0); sheetRow++)
{
object item = null;
foreach (var cellMapping in mapping.Values)
{
var cell = source.Cells[sheetRow, cellMapping.CellLocation.Column + 1 + startColumn];
try
{
var readingResult = cellMapping.ReadCell(item == null ? newItemFactory : () => item, cell, settings);
if ((item == null) || (readingResult != null))
{
item = readingResult;
}
if (item is IHasSourceRowIndex rowReference)
{
rowReference.SourceRowIndex = sheetRow;
}
}
catch (Exception ex)
{
throw new XlsxSerializerException(cell, ex);
}
}
if (item == null)
{
yield break;
}
yield return(item);
}
}
