/// <summary>
/// Generic extension method yielding objects of specified type from table.
/// </summary>
/// <remarks>
/// Only primitives and enums are supported as property.
/// Currently supports only tables with header.
/// </remarks>
/// <typeparam name="T">Type to map to. Type should be a class and should have parameter-less constructor.</typeparam>
/// <param name="table">Table object to fetch</param>
/// <param name="configurationAction"></param>
/// <returns>An enumerable of the generating type</returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelTable table, Action <ExcelReadConfiguration <T> > configurationAction = null) where T : new()
{
ExcelReadConfiguration <T> configuration = ExcelReadConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
if (table.IsEmpty(configuration.HasHeaderRow))
{
yield break;
}
List <ExcelTableColumnDetails> mapping = PrepareMappings(table, configuration).Where(x => x.ColumnPosition >= 0).ToList();
ExcelAddress bounds = table.GetDataBounds();
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
var item = new T();
foreach (ExcelTableColumnDetails map in mapping)
{
var exists = table.WorkSheet.Cells[row, map.ColumnPosition + table.Address.Start.Column];
object cell = exists.Value;
PropertyInfo property = map.PropertyInfo;
try
{
TrySetProperty(item, property, cell);
}
catch (Exception ex)
{
var exceptionArgs = new ExcelExceptionArgs
{
ColumnName = table.Columns[map.ColumnPosition].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.ColumnPosition + table.Address.Start.Column)
};
if (configuration.ThrowValidationExceptions && ex is ValidationException)
{
throw new ExcelValidationException(ex.Message, ex)
.WithArguments(exceptionArgs);
}
if (configuration.ThrowCastingExceptions)
{
throw new ExcelException(string.Format(configuration.CastingExceptionMessage, exceptionArgs.ColumnName, exceptionArgs.CellAddress.Address, exceptionArgs.CellValue, exceptionArgs.ExpectedType.Name), ex)
.WithArguments(exceptionArgs);
}
}
}
configuration.OnCaught?.Invoke(item, row);
yield return(item);
}
}
/// <summary>
/// Converts the worksheet into list of objects as enumerable
/// </summary>
/// <typeparam name="T">Type of object</typeparam>
/// <param name="worksheet"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelWorksheet worksheet, Action <ExcelReadConfiguration <T> > configurationAction = null) where T : class, new()
{
ExcelReadConfiguration <T> configuration = DefaultExcelReadConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
return(worksheet.AsExcelTable(configuration.HasHeaderRow).AsEnumerable(configurationAction));
}
/// <summary>
/// Generic extension method yielding objects of specified type from table.
/// </summary>
/// <remarks>
/// Only primitives and enums are supported as property.
/// Currently supports only tables with header.
/// </remarks>
/// <typeparam name="T">Type to map to. Type should be a class and should have parameterless constructor.</typeparam>
/// <param name="table">Table object to fetch</param>
/// <param name="configurationAction"></param>
/// <returns>An enumerable of the generating type</returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelTable table, Action <ExcelReadConfiguration <T> > configurationAction = null) where T : class, new()
{
ExcelReadConfiguration <T> configuration = DefaultExcelReadConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
if (!table.IsEmpty(configuration.HasHeaderRow))
{
List <KeyValuePair <int, PropertyInfo> > mapping = PrepareMappings(table, configuration).ToList();
ExcelAddress bounds = table.GetDataBounds();
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
var item = (T)Activator.CreateInstance(typeof(T));
foreach (KeyValuePair <int, PropertyInfo> map in mapping)
{
object cell = table.WorkSheet.Cells[row, map.Key + table.Address.Start.Column].Value;
PropertyInfo property = map.Value;
try
{
TrySetProperty(item, property, cell);
}
catch (Exception ex)
{
var exceptionArgs = new ExcelExceptionArgs
{
ColumnName = table.Columns[map.Key].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.Key + table.Address.Start.Column)
};
if (configuration.ThrowValidationExceptions && ex is ValidationException)
{
throw new ExcelValidationException(ex.Message, ex)
.WithArguments(exceptionArgs);
}
if (configuration.ThrowCastingExceptions)
{
throw new ExcelException(string.Format(configuration.CastingExceptionMessage, exceptionArgs.ColumnName, exceptionArgs.CellAddress.Address, exceptionArgs.CellValue, exceptionArgs.ExpectedType.Name), ex)
.WithArguments(exceptionArgs);
}
}
}
configuration.OnCaught?.Invoke(item, row);
yield return(item);
}
}
}
/// <summary>
/// Validates the Excel table against the generating type.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table"></param>
/// <param name="configurationAction"></param>
/// <returns>An enumerable of <see cref="ExcelExceptionArgs" /> containing</returns>
public static IEnumerable <ExcelExceptionArgs> Validate <T>(this ExcelTable table, Action <ExcelReadConfiguration <T> > configurationAction = null) where T : class, new()
{
ExcelReadConfiguration <T> configuration = DefaultExcelReadConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
List <KeyValuePair <int, PropertyInfo> > mapping = PrepareMappings(table, configuration).ToList();
var result = new LinkedList <ExcelExceptionArgs>();
ExcelAddress bounds = table.GetDataBounds();
var item = new T();
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
foreach (KeyValuePair <int, PropertyInfo> map in mapping)
{
object cell = table.WorkSheet.Cells[row, map.Key + table.Address.Start.Column].Value;
PropertyInfo property = map.Value;
try
{
TrySetProperty(item, property, cell);
}
catch
{
result.AddLast(new ExcelExceptionArgs
{
ColumnName = table.Columns[map.Key].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.Key + table.Address.Start.Column)
});
}
}
}
return(result);
}
/// <summary>
/// Prepares mapping using the type and the attributes decorating its properties
/// </summary>
/// <typeparam name="T">Type to parse</typeparam>
/// <param name="table">Table to get columns from</param>
/// <param name="configuration"></param>
/// <returns>A list of mappings from column index to property</returns>
private static IEnumerable <KeyValuePair <int, PropertyInfo> > PrepareMappings <T>(ExcelTable table, ExcelReadConfiguration <T> configuration)
{
// Get only the properties that have ExcelTableColumnAttribute
List <ExcelTableColumnAttributeAndPropertyInfo> propertyInfoAndColumnAttributes = typeof(T).GetExcelTableColumnAttributesWithPropertyInfo();
// Build property-table column mapping
foreach (var propertyInfoAndColumnAttribute in propertyInfoAndColumnAttributes)
{
PropertyInfo propertyInfo = propertyInfoAndColumnAttribute.PropertyInfo;
ExcelTableColumnAttribute columnAttribute = propertyInfoAndColumnAttribute.ColumnAttribute;
int col = -1;
// There is no case when both column name and index is specified since this is excluded by the attribute
// Neither index, nor column name is specified, use property name
if (columnAttribute.ColumnIndex == 0 && string.IsNullOrWhiteSpace(columnAttribute.ColumnName) && table.Columns[propertyInfo.Name] != null)
{
col = table.Columns[propertyInfo.Name].Position;
}
else if (columnAttribute.ColumnIndex > 0 && table.Columns[columnAttribute.ColumnIndex - 1] != null) // Column index was specified
{
col = table.Columns[columnAttribute.ColumnIndex - 1].Position;
}
else if (!string.IsNullOrWhiteSpace(columnAttribute.ColumnName) && table.Columns.FirstOrDefault(x => x.Name.Equals(columnAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)) != null) // Column name was specified
{
col = table.Columns.First(x => x.Name.Equals(columnAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)).Position;
}
if (col == -1)
{
throw new ExcelValidationException(string.Format(configuration.ColumnValidationExceptionMessage, columnAttribute.ColumnName ?? propertyInfo.Name))
.WithArguments(new ExcelExceptionArgs
{
ColumnName = columnAttribute.ColumnName,
ExpectedType = propertyInfo.PropertyType,
PropertyName = propertyInfo.Name,
CellValue = table.WorkSheet.Cells[table.Address.Start.Row, columnAttribute.ColumnIndex + table.Address.Start.Column].Value,
CellAddress = new ExcelCellAddress(table.Address.Start.Row, columnAttribute.ColumnIndex + table.Address.Start.Column)
});
}
yield return(new KeyValuePair <int, PropertyInfo>(col, propertyInfo));
}
}
