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