Пример использования с заголовком:
public class AccountBalanceConfig : IExcelTypeConfiguration<AccountBalanceFile>
{
public void Configure(IExcelTypeBuilder<AccountBalanceFile> builder)
{
builder
.WithHeader(3, file => file.Balance)
.Property("Kontobenämning", ab => ab.AccountName)
.AutoFormat()
.Property("Nummer", ab => ab.BankAccount)
.AutoFormat()
.Property(1, 1, ab => ab.BalanceDate)
.UseConvention<BalanceDateConvention>()
.Property("Bokfört saldo", ab => ab.Balance)
.AutoFormat();
}
private class BalanceDateConvention : IConvention
{
private static readonly Regex BalanceDateMatcher = new Regex(@"dddd-dd-dd");
public object To(string value)
{
var match = BalanceDateMatcher.Match(value);
return DateTime.Parse(match.Value, CultureInfo.InvariantCulture);
}
}
}
Пример с Excel без заголовка:
public class AccountTransactionConfig : IExcelTypeConfiguration<AccountTransactionFile>
{
public void Configure(IExcelTypeBuilder<AccountTransactionFile> builder)
{
builder
.WithNoHeader(file => file.Transactions)
.Property(0, at => at.Account)
.AutoFormat()
.Property(1, at => at.TransactionDate)
.AutoFormat()
.Property(4, at => at.Reference)
.AutoFormat()
.Property(6, at => at.Amount)
.AutoFormat()
.Property(7, at => at.Type)
.AutoFormat()
.Property(8, at => at.Information)
.AutoFormat()
.Property(9, at => at.RemoteAccountNumber)
.AutoFormat()
.Property(10, at => at.RemoteClearingOrAddress)
.AutoFormat()
.Property(11, at => at.RemoteName)
.AutoFormat();
}
}
Пример файла для чтения
[TestClass]
public class When_parsing_a_balance_excel : ExcelTest
{
private AccountBalanceFile _balance;
[TestInitialize]
public async Task Context()
{
await using var stream = GetResource("xlsx");
_balance = await Get<ExcelSerializer<AccountBalanceFile>>().DeserializeAsync(stream);
}
[TestMethod]
public void It_should_parse_file_correctly()
{
Assert.AreEqual(3, _balance.Balance.Count());
}
}
Начальная точка для свободного картографирования
public class ExcelTypeBuilder<TExcelFileType> : IExcelTypeBuilder<TExcelFileType>
{
private readonly List<IMapper> _mappers = new List<IMapper>();
public INamedColumnTypeBuilder<TEntity> WithHeader<TEntity>(int headerRow, Expression<Func<TExcelFileType, IEnumerable<TEntity>>> entities) where TEntity : new()
{
var mapper = new NamedColumnTypeBuilder<TEntity>(headerRow, (entities.Body as MemberExpression)?.Member as PropertyInfo);
_mappers.Add(mapper);
return mapper;
}
public IIndexedTypeBuilder<TEntity> WithNoHeader<TEntity>(Expression<Func<TExcelFileType, IEnumerable<TEntity>>> entities) where TEntity : new()
{
var mapper = new IndexedColumnTypeBuilder<TEntity>((entities.Body as MemberExpression)?.Member as PropertyInfo);
_mappers.Add(mapper);
return mapper;
}
public Task<TRead> ReadAsync<TRead>(Stream stream) where TRead : TExcelFileType, new()
{
if (!_mappers.Any()) throw new Exception("No config exists for entity type");
using var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration { LeaveOpen = true });
var dataSet = reader.AsDataSet();
var instance = new TRead();
foreach (var mapper in _mappers)
{
mapper.Read(instance, dataSet);
}
return Task.FromResult(instance); //We have other parsers that are async they share interface
}
}
Сопоставитель свойств для именованного столбца (Excel с заголовком)
public class NamedColumnTypeBuilder<TEntity> : INamedColumnTypeBuilder<TEntity>, IMapper where TEntity : new()
{
private readonly int _headerRow;
private readonly PropertyInfo _propertyInfo;
private readonly List<IColumnReader> _columnReaders = new List<IColumnReader>();
public NamedColumnTypeBuilder(int headerRow, PropertyInfo propertyInfo)
{
_headerRow = headerRow;
_propertyInfo = propertyInfo ?? throw new ArgumentException("Property expression could not be resolved");
}
IPropertyMapper<INamedColumnTypeBuilder<TEntity>> INamedColumnTypeBuilder<TEntity>.Property<TProperty>(int row, int column, Expression<Func<TEntity, TProperty>> property)
{
var formatter = new PropertyMapper<INamedColumnTypeBuilder<TEntity>>(this, (property.Body as MemberExpression)?.Member as PropertyInfo);
_columnReaders.Add(new SpecificColumnReader(row, column, formatter));
return formatter;
}
public IPropertyMapper<INamedColumnTypeBuilder<TEntity>> Property<TProperty>(string column, Expression<Func<TEntity, TProperty>> property)
{
var formatter = new PropertyMapper<INamedColumnTypeBuilder<TEntity>>(this, (property.Body as MemberExpression)?.Member as PropertyInfo);
_columnReaders.Add(new NamedColumnReader(column,formatter));
return formatter;
}
public void Read(object parentInstance, DataSet dataSet)
{
var table = dataSet.Tables[0];
var rows = table.Rows;
var header = rows[_headerRow].ItemArray.Select((o, i) => (o, i)).ToDictionary(info => ((string)info.o).ToLower(), info => info.i);
var collection = new List<TEntity>();
for (int i = _headerRow + 1; i < rows.Count; i++)
{
var row = rows[i];
var instance = new TEntity();
var cancelRead = _columnReaders.Any(reader =>
{
if (!reader.CanRead(table, header, row)) return true;
reader.Read(instance, table, header, row);
return false;
});
if (cancelRead) break;
collection.Add(instance);
}
_propertyInfo.SetValue(parentInstance, collection);
}
}
Конфигурация недвижимости
public class PropertyMapper<TParent> : IPropertyMapper<TParent>, IFormatter
{
private readonly TParent _parent;
private readonly PropertyInfo _propertyInfo;
private PropertyReader _reader;
public PropertyMapper(TParent parent, PropertyInfo propertyInfo)
{
_parent = parent;
_propertyInfo = propertyInfo ?? throw new ArgumentException("Property expression could not be resolved");
}
public TParent AutoFormat()
{
_reader = new PropertyReader(_propertyInfo);
return _parent;
}
public TParent Format(string format)
{
_reader = new FormatPropertyReader(format, _propertyInfo);
return _parent;
}
public TParent UseConvention<TConvention>() where TConvention : IConvention, new()
{
_reader = new ConventionPropertyReadWriter(_propertyInfo, new TConvention());
return _parent;
}
public void Read(object instance, object value)
{
_reader.Read(instance, value);
}
public bool AcceptsNull => _reader.AcceptsNull;
}
Пример программы чтения свойств:
public class PropertyReader
{
protected readonly PropertyInfo Property;
protected readonly Type PropertyType;
protected readonly bool IsNullable;
public PropertyReader(PropertyInfo property)
{
Property = property;
PropertyType = property.PropertyType;
var underlyingType = Nullable.GetUnderlyingType(Property.PropertyType);
if (underlyingType != null)
{
IsNullable = true;
PropertyType = underlyingType;
}
}
public bool AcceptsNull => IsNullable || PropertyType.IsClass;
public void Read(object instance, object value)
{
var propValue = IsNullable && (value == null || value is DBNull) ? null : value is string str ? ChangeType(str) : TryCast(value);
Property.SetValue(instance, propValue);
}
private object TryCast(object value)
{
if (value == null) return null;
if (value.GetType() != PropertyType)
{
return Convert.ChangeType(value, PropertyType);
}
return value;
}
protected virtual object ChangeType(string value)
{
if (PropertyType.IsEnum)
return Enum.Parse(PropertyType, value);
return Convert.ChangeType(value, PropertyType, CultureInfo.InvariantCulture);
}
}
Пример чтения столбца
public abstract class ColumnReader
{
protected readonly IFormatter Formatter;
protected ColumnReader(IFormatter formatter)
{
Formatter = formatter;
}
protected object Read(DataRow row, int column)
{
if (column >= row.ItemArray.Length)
{
if (!Formatter.AcceptsNull) throw new Exception("Column is missing and Property does not allow null");
return null;
}
return row[column];
}
}
public class NamedColumnReader : ColumnReader, IColumnReader
{
private readonly string _column;
private readonly IFormatter _formatter;
public NamedColumnReader(string column, IFormatter formatter) : base(formatter)
{
_column = column.ToLower();
_formatter = formatter;
}
public void Read(object instance, DataTable table, Dictionary<string, int> header, DataRow row)
{
_formatter.Read(instance, Read(row, header[_column]));
}
public bool CanRead(DataTable table, Dictionary<string, int> header, DataRow row)
{
return _formatter.AcceptsNull || !(row[header[_column]] is DBNull);
}
}
Публичный api для читателя
public class ExcelSerializer<TExcelFile> : IDeserializeStream<TExcelFile> where TExcelFile : new()
{
private readonly ExcelTypeBuilder<TExcelFile> _builder;
public ExcelSerializer(ExcelTypeConfigurationMapper<TExcelFile> mapper)
{
_builder = mapper.Builder;
}
public Task<TExcelFile> DeserializeAsync(Stream stream)
{
return _builder.ReadAsync<TExcelFile>(stream);
}
}
Класс, который все это связывает
public class ExcelTypeConfigurationMapper<TEntity>
{
public ExcelTypeConfigurationMapper(IExcelTypeConfiguration<TEntity> config)
{
Builder = new ExcelTypeBuilder<TEntity>();
config.Configure(Builder);
}
public ExcelTypeBuilder<TEntity> Builder { get; }
}
Конфигурация службы
private static IServiceCollection AddExcelParsing(this IServiceCollection collection)
{
return collection.AddAllTransient(typeof(IExcelTypeConfiguration<>), typeof(AccountTransactionConfig)) //Helper method that hooks all IExcelTypeConfiguration<> in assembly of type AccountTransactionConfig
.AddSingleton(typeof(ExcelTypeConfigurationMapper<>))
.AddTransient(typeof(IDeserializeStream<>), typeof(ExcelSerializer<>))
.AddTransient(typeof(ExcelSerializer<>));
}