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