1
\$\begingroup\$

Example of usage with header:

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(@"\d\d\d\d-\d\d-\d\d");

        public object To(string value)
        {
            var match = BalanceDateMatcher.Match(value);
            return DateTime.Parse(match.Value, CultureInfo.InvariantCulture);
        }
    }
}

Example with headerless 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();
    }
}

Example read file

[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());
    }
}

Entry point for fluent mapping

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

Property mapper for named column (Excel with header)

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

Property config

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

Property reader example:

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

Column reader example

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

Public api for reader

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

The class that connects it all

public class ExcelTypeConfigurationMapper<TEntity>
{
    public ExcelTypeConfigurationMapper(IExcelTypeConfiguration<TEntity> config)
    {
        Builder = new ExcelTypeBuilder<TEntity>();
        config.Configure(Builder);
    }

    public ExcelTypeBuilder<TEntity> Builder { get; }
}

Service config

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<>));
}
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.