protected override object OnReadRow(ExcelRange range, ISheetModel model, IColumnModel[] columnMapping)
{
//skip rows where the first column is blank
if (range.Worksheet.Cells[range.Start.Row, 1].Value == null)
{
return(null);
}
return(base.OnReadRow(range, model, columnMapping));
}
protected virtual void OnWriteRow(ExcelRange range, ISheetModel model, object data)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
if (!model.Type.IsAssignableFrom(data.GetType()))
{
throw new ArgumentOutOfRangeException("Data type does not match column type");
}
var columns = model.Columns.Count;
var row = range.Start.Row;
var firstCol = range.Start.Column;
if (columns != (range.End.Column - range.Start.Column + 1))
{
throw new ArgumentOutOfRangeException("Columns in range does not match columns in model");
}
if (range.Start.Row != range.End.Row)
{
throw new ArgumentOutOfRangeException("Range has more than one row");
}
for (int i = 0; i < columns; i++)
{
var cell = range[row, firstCol + i]; //note: overwrites range with new address
var columnModel = model.Columns[i];
object value;
if (columnModel.Member is FieldInfo fieldInfo)
{
value = fieldInfo.GetValue(data);
}
else if (columnModel.Member is PropertyInfo propertyInfo)
{
value = propertyInfo.GetValue(data);
}
else
{
throw new InvalidOperationException("Column member expression is not a field or property");
}
var serializer = columnModel.WriteSerializer ?? DefaultWriteSerializer;
serializer(cell, value);
}
}
protected virtual void OnWriteSheet(ExcelWorksheet worksheet, ISheetModel model, IList data)
{
if (worksheet == null)
{
throw new ArgumentNullException(nameof(worksheet));
}
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
ExcelRange start = (model.WriteRangeLocator ?? DefaultWriteRangeLocator)(worksheet);
if (start == null)
{
throw new InvalidOperationException("No write range specified");
}
var headerRow = start.Start.Row;
var dataRow = headerRow + 1;
var firstCol = start.Start.Column;
var columns = model.Columns.Count;
if (columns == 0)
{
return;
}
for (int i = 0; i < columns; i++)
{
var columnModel = model.Columns[i];
var col = firstCol + i;
var cell = start[headerRow, col]; //note: overwrites start with new address
cell.Value = columnModel.Name;
columnModel.HeaderFormatter?.Invoke(cell);
}
for (int i = 0; i < data.Count; i++)
{
var cells = start[dataRow + i, firstCol, dataRow + i, firstCol + columns - 1]; //note: overwrites start with new address
OnWriteRow(cells, model, data[i]);
}
if (data.Count > 0)
{
for (int i = 0; i < columns; i++)
{
var columnModel = model.Columns[i];
var col = firstCol + i;
var cells = start[dataRow, col, dataRow + data.Count - 1, col]; //note: overwrites start with new address
columnModel.ColumnFormatter?.Invoke(cells);
}
}
for (int i = 0; i < columns; i++)
{
var columnModel = model.Columns[i];
var col = firstCol + i;
var cells = start[headerRow, col, dataRow + data.Count - 1, col]; //note: overwrites start with new address
columnModel.WritePolisher?.Invoke(cells);
}
var allCells = start[headerRow, firstCol, dataRow + data.Count - 1, firstCol + columns - 1]; //note: overwrites start with new address
model.WritePolisher?.Invoke(worksheet, allCells);
}
/// <summary>
/// Parses a row of data or returns null if the row should be skipped
/// </summary>
protected virtual object OnReadRow(ExcelRange range, ISheetModel model, IColumnModel[] columnMapping)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (model == null)
{
throw new ArgumentNullException(nameof(range));
}
if (columnMapping == null)
{
throw new ArgumentNullException(nameof(columnMapping));
}
var firstCol = range.Start.Column;
var row = range.Start.Row;
var columns = range.Columns;
if (range.Rows != 1)
{
throw new ArgumentOutOfRangeException(nameof(range), "Range must represent a single row of data");
}
if (columns != columnMapping.Length)
{
throw new ArgumentOutOfRangeException(nameof(columnMapping), "Number of columns in range does not match size of columnMapping array");
}
var obj = Activator.CreateInstance(model.Type);
if (range.Any(x => x.Value != null))
{
for (int colIndex = 0; colIndex < columns; colIndex++)
{
var col = colIndex + firstCol;
var columnModel = columnMapping[colIndex];
if (columnModel != null)
{
var cell = range[row, col]; // note that range[] resets range.Address to equal the new address
if (cell.Value == null)
{
if (!columnModel.Optional)
{
throw new ColumnDataMissingException(columnModel.Name, model.Name);
}
}
else
{
object value;
try {
if (columnModel.ReadSerializer != null)
{
value = columnModel.ReadSerializer(cell);
}
else
{
value = DefaultReadSerializer(cell, columnModel.Type);
}
}
catch (Exception e) {
throw new ParseDataException(cell.Address, columnModel.Name, model.Name, e);
}
if (value != null)
{
if (columnModel.Member is PropertyInfo propertyInfo)
{
propertyInfo.SetMethod.Invoke(obj, new[] { value });
}
else if (columnModel.Member is FieldInfo fieldInfo)
{
fieldInfo.SetValue(obj, value);
}
}
}
}
}
}
else
{
if (model.SkipEmptyRows)
{
obj = null;
}
else
{
foreach (var columnModel in columnMapping)
{
if (!columnModel.Optional)
{
throw new RowEmptyException(model.Name);
}
}
}
}
return(obj);
}
/// <summary>
/// Reads a worksheet and returns a set of <see cref="List{T}"/> of the entries.
/// <br/><br/>
/// Must not return null.
/// </summary>
protected virtual IList OnReadSheet(ExcelWorksheet worksheet, ISheetModel model)
{
if (worksheet == null)
{
throw new ArgumentNullException(nameof(worksheet));
}
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
ExcelRange dataRange = (model.ReadRangeLocator ?? DefaultReadRangeLocator)(worksheet);
if (dataRange == null)
{
//no data on sheet
if (model.Columns.Any(x => !x.Optional))
{
throw new SheetEmptyException(model.Name);
}
return(CreateListForSheet(model.Type));
}
IList data = CreateListForSheet(model.Type, dataRange.Rows - 1);
var headerRow = dataRange.Start.Row;
var firstCol = dataRange.Start.Column;
var columns = dataRange.Columns;
var firstRow = dataRange.Start.Row + 1;
var lastRow = dataRange.End.Row;
var columnMapping = new IColumnModel[columns];
var columnMapped = new bool[model.Columns.Count];
var modelColumns = model.Columns.ToList();
for (int colIndex = 0; colIndex < columns; colIndex++)
{
var col = colIndex + firstCol;
var cell = worksheet.Cells[headerRow, col];
if (cell.Value != null)
{
var headerName = cell.Text;
if (model.Columns.TryGetValue(headerName, out var columnModel))
{
var columnModelIndex = modelColumns.IndexOf(columnModel);
if (columnMapped[columnModelIndex])
{
throw new DuplicateColumnException(columnModel.Name, model.Name);
}
columnMapped[columnModelIndex] = true;
columnMapping[colIndex] = columnModel;
}
}
}
for (int i = 0; i < model.Columns.Count; i++)
{
if (columnMapped[i] == false && !model.Columns[i].Optional)
{
throw new ColumnMissingException(model.Columns[i].Name, model.Name);
}
}
for (int row = firstRow; row <= lastRow; row++)
{
var range = worksheet.Cells[row, firstCol, row, firstCol + columns - 1];
var obj = OnReadRow(range, model, columnMapping);
if (obj != null)
{
data.Add(obj);
}
}
return(data);
}
public object TestOnReadRow(ExcelRange range, ISheetModel model, IColumnModel[] columnMapping)
{
return(OnReadRow(range, model, columnMapping));
}
public IList TestOnReadSheet(ExcelWorksheet worksheet, ISheetModel model)
{
return(OnReadSheet(worksheet, model));
}
public void TestOnWriteRow(ExcelRange range, ISheetModel model, object data)
{
OnWriteRow(range, model, data);
}
public void TestOnWriteSheet(ExcelWorksheet worksheet, ISheetModel model, IList data)
{
OnWriteSheet(worksheet, model, data);
}
public BaseSheetController(ISheetModel <T> model)
: base(model)
{
}
bool ISheetModelLookup.TryGetValue(Type type, out ISheetModel value) => _typeDictionary.TryGetValue(type, out value);
bool ISheetModelLookup.TryGetValue(string sheetName, out ISheetModel value) => _sheetDictionary.TryGetValue(sheetName?.Trim().ToLower(), out value);
