/// <summary>
/// Generates columns for all public properties on the type
/// </summary>
/// <returns></returns>
internal List <WorksheetColumn <T> > AutoGenerateColumns()
{
var columns = new List <WorksheetColumn <T> >();
List <KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> > propertyAttributePairs = typeof(T).GetExcelTableColumnAttributes <T>();
foreach (KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> propertyAttributePair in propertyAttributePairs)
{
PropertyInfo property = propertyAttributePair.Key;
ExcelTableColumnAttribute mappingAttribute = propertyAttributePair.Value;
bool isNullableProperty = property.PropertyType.IsNullable();
string header = !string.IsNullOrEmpty(mappingAttribute.ColumnName) ? mappingAttribute.ColumnName : Regex.Replace(property.Name, "[a-z][A-Z]", m => $"{m.Value[0]} {m.Value[1]}");
var column = new WorksheetColumn <T>
{
Header = header,
Map = GetGetter <T>(property.Name),
ConfigureColumn = c => c.AutoFit(),
ConfigureHeader = c => { c.Style.Font.Bold = !isNullableProperty; }
};
columns.Add(column);
}
return(columns);
}
/// <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 <ExcelTableColumnDetails> PrepareMappings <T>(ExcelTable table, ExcelReadConfiguration <T> configuration)
{
// Get only the properties that have ExcelTableColumnAttribute
List <ExcelTableColumnDetails> propertyInfoAndColumnAttributes = typeof(T).GetExcelTableColumnAttributesWithPropertyInfo();
// Build property-table column mapping
foreach (var propertyInfoAndColumnAttribute in propertyInfoAndColumnAttributes)
{
PropertyInfo propertyInfo = propertyInfoAndColumnAttribute.PropertyInfo;
ExcelTableColumnAttribute columnAttribute = propertyInfoAndColumnAttribute.ColumnAttribute;
if (columnAttribute.ColumnIndex <= 0 && string.IsNullOrEmpty(columnAttribute.ColumnName))
{
columnAttribute.ColumnName = propertyInfo.Name;
}
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) &&
CheckColumnByNameIfExists(table, propertyInfo.Name, columnAttribute.IsOptional))
{
col = table.Columns[propertyInfo.Name].Position;
}
else if (columnAttribute.ColumnIndex > 0 &&
CheckColumnByIndexIfExists(table, columnAttribute.ColumnIndex - 1, columnAttribute.IsOptional)) // 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 (!columnAttribute.IsOptional && 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 ExcelTableColumnDetails(col, propertyInfo, columnAttribute));
}
}
/// <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 <KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> > propertyAttributePairs = typeof(T).GetExcelTableColumnAttributes <T>();
// Build property-table column mapping
foreach (KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> propertyAttributePair in propertyAttributePairs)
{
PropertyInfo property = propertyAttributePair.Key;
ExcelTableColumnAttribute mappingAttribute = propertyAttributePair.Value;
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 (mappingAttribute.ColumnIndex == 0 && string.IsNullOrWhiteSpace(mappingAttribute.ColumnName))
{
col = table.Columns[property.Name].Position;
}
else if (mappingAttribute.ColumnIndex > 0) // Column index was specified
{
col = table.Columns[mappingAttribute.ColumnIndex - 1].Position;
}
else if (!string.IsNullOrWhiteSpace(mappingAttribute.ColumnName) && table.Columns.FirstOrDefault(x => x.Name.Equals(mappingAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)) != null) // Column name was specified
{
col = table.Columns.First(x => x.Name.Equals(mappingAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)).Position;
}
if (col == -1)
{
throw new ExcelValidationException(string.Format(configuration.ColumnValidationExceptionMessage, mappingAttribute.ColumnName))
.WithArguments(new ExcelExceptionArgs
{
ColumnName = mappingAttribute.ColumnName,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = table.WorkSheet.Cells[table.Address.Start.Row, mappingAttribute.ColumnIndex + table.Address.Start.Column].Value,
CellAddress = new ExcelCellAddress(table.Address.Start.Row, mappingAttribute.ColumnIndex + table.Address.Start.Column)
});
}
yield return(new KeyValuePair <int, PropertyInfo>(col, property));
}
}
/// <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>
/// <returns>A list of mappings from column index to property</returns>
private static List <KeyValuePair <int, PropertyInfo> > PrepareMappings <T>(ExcelTable table)
{
var mapping = new List <KeyValuePair <int, PropertyInfo> >();
// Get only the properties that have ExcelTableColumnAttribute
List <KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> > propertyAttributePairs = typeof(T).GetExcelTableColumnAttributes <T>();
// Build property-table column mapping
foreach (KeyValuePair <PropertyInfo, ExcelTableColumnAttribute> propertyAttributePair in propertyAttributePairs)
{
PropertyInfo property = propertyAttributePair.Key;
ExcelTableColumnAttribute mappingAttribute = propertyAttributePair.Value;
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 (mappingAttribute.ColumnIndex == 0 && string.IsNullOrWhiteSpace(mappingAttribute.ColumnName))
{
col = table.Columns[property.Name].Position;
}
else if (mappingAttribute.ColumnIndex > 0) // Column index was specified
{
col = table.Columns[mappingAttribute.ColumnIndex - 1].Position;
}
else if (!string.IsNullOrWhiteSpace(mappingAttribute.ColumnName) && table.Columns.First(x => x.Name.Equals(mappingAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)) != null) // Column name was specified
{
col = table.Columns.First(x => x.Name.Equals(mappingAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)).Position;
}
if (col == -1)
{
throw new ArgumentException($"{mappingAttribute.ColumnName} column could not found on the worksheet");
}
mapping.Add(new KeyValuePair <int, PropertyInfo>(col, property));
}
return(mapping);
}
public ExcelTableColumnAttributeAndPropertyInfo(PropertyInfo propertyInfo, ExcelTableColumnAttribute columnAttribute)
{
PropertyInfo = propertyInfo;
ColumnAttribute = columnAttribute;
}
