extended WithCollectionProperty

[xavave]

Hi ipalverde, this is an excellent project ! This is not an issue, it's an improvment request

How can I achieve this ?

1. WithCollectionProperty : I need to extract 2 columns , not just one : Id and Value in screenshot : Id is RMID and value is : RM poids
2. I don't know by advance how many columns ID,value I can have , I would need to iterate through columns untill column name is empty I've tried to customize your code but I can't get it to work:

[xavave]

I could finally figure it out like that :

        var fileInfo = GetSpreadsheetFileInfo();
        using (var package = new ExcelPackage(fileInfo))
        {
            var data = package.Workbook.Worksheets["Laboratory 4 (Formule)"]
                .Extract<RowDataWithColumnBeingRow>()
                .WithProperty(p => p.Name, "B")
                .WithProperty(p => p.Code, "C")
                .WithCollectionProperty(p => p.MyCustomData,
                    item => item.id, 1,
                      item => item.Weight,
                    item => item.id, "F", "AK"
                   )
                .GetData(2, 4, 2)
                .ToList();
}

with MyCustomData defined as :

 public class ColumnData
    {
        public string Weight { get; set; }

        public string id { get; set; }
    } 

and

    public void SetPropertyValue(TRow dataInstance, int row, ExcelRange cellRange)
    {
        var collection = new TCollection();

        foreach (var cell in cellRange[this.initialColumn + row + ":" + this.finalColumn + row])
        {
            if (cell.Start.Column % 2 == 0)
            {
                var collectionItem = new TCollectionItem();

                // cell here will be a single cell, always.
                // So I get the column from that cell in order to obtain the header.
                int column = cell.Start.Column;
                var nextCell = cellRange[cell.Start.Row, column + 1];
                this.collectionItemHeadPropertySetter.SetPropertyValue(collectionItem, cellRange[this.headerRow, column]);
                this.collectionItemHeadPropertySetter.SetPropertyValue(collectionItem, cellRange[this.headerRow2, column+1]);

                this.collectionItemRowPropertySetter.SetPropertyValue(collectionItem, cell);
                collection.Add(collectionItem);
            }
        }

        this.setCollectionProperty(dataInstance, collection);
    }

btw I removed headerRow2; and collectionItemHeadPropertySetter2 which were useless

this solution is not an optimal one, but at least it works for my current requirements although I need to have the same type for Id and value

I had to declare ColumnData fields as string because it's not yet possible to use cellValueConverter with WithCollectionProperty and the double values in my excel worksheet at localized with a "," as a decimal separator so casting the value returns 0 with current code because it expects a "." as decimal separator

[xavave]

I've updated the code again so I can be able to set different types to id and value:

    public class ColumnData
    {
        public double Weight { get; set; }

        public int id { get; set; }
    }

with step used in :

    public IEnumerable<TRow> GetData(int fromRow, Predicate<int> @while, int step = 1)
    {
        for (int row = fromRow; @while(row); row++)
        {
            var dataInstance = new TRow();

            bool continueExecution = true;
            for (int index = 0; continueExecution && index < this.propertySetters.Count; index += step)
                continueExecution = this.propertySetters[index].SetPropertyValue(dataInstance, row, this.worksheet.Cells);

            if (!continueExecution)
            {
                yield return dataInstance;
                break;
            }

            foreach (var collectionPropertySetter in this.collectionColumnSetters)
                collectionPropertySetter.SetPropertyValue(dataInstance, row, this.worksheet.Cells);

            yield return dataInstance;
        }
    }

and

        var fileInfo = GetSpreadsheetFileInfo();
        using (var package = new ExcelPackage(fileInfo))
        {
            var data = package.Workbook.Worksheets["Laboratory"]
                .Extract<RowDataWithColumnBeingRow>()
                .WithProperty(p => p.Name, "B")
                .WithProperty(p => p.Code, "C")
                .WithCollectionProperty(p => p.MyCustomData,
                    item => item.id, item => item.Weight, 1,
                      item => item.id,
                    item => item.Weight, "F", "AK"
                   )
                .GetData(2, 4, 2) //last "2" is step to skip 1 column on each iteration
                .ToList();
}

with

    ICollectionPropertyConfiguration<TRow> WithCollectionProperty<TCollectionItem, THeaderValue, THeaderValue2, TRowValue, TRowValue2>(
        Expression<Func<TRow, List<TCollectionItem>>> propertyCollection,
        Expression<Func<TCollectionItem, THeaderValue>> headerProperty, 
Expression<Func<TCollectionItem, THeaderValue2>> headerProperty2,
 int headerRow,

        Expression<Func<TCollectionItem, TRowValue>> rowProperty,
        Expression<Func<TCollectionItem, TRowValue2>> rowProperty2,
        string startColumn, string endColumn)
        where TCollectionItem : class, new();

as i have no constructor with cellValueConverter , I've made a quick hack :

    bool IsDigitsOnly(string str)
    {
        foreach (char c in str)
        {
            if (c < '0' || c > '9')
                return false;
        }
        return true;
    }

and

    protected bool SetPropertyValue(TModel dataInstance, ExcelRangeBase cell)
    {
        // This instance should be created only if there is at least one callback function defined.
        var context = (this.validateValue != null || this.validateCastedValue != null) ?
            new PropertyExtractionContext(new CellAddress(cell))
            :
            null;

        if (this.validateValue != null)
        {
            this.validateValue(context, cell.Value);
            if (context.Aborted)
                return false;
        }
        TValue value;
        if (cellValueConverter == null)
        {
            var cellText = cell.Text.Trim().Replace(",", "").Replace(".", "");
            if (IsDigitsOnly(cellText))
                cell.Value = double.Parse(cell.Text.Trim().Replace(",", "."));
            value = cell.GetValue<TValue>();

        }
        else
            value = this.cellValueConverter(cell.Value);

        if (this.validateCastedValue != null)
        {
            this.validateCastedValue(context, value);
            if (context.Aborted)
                return false;
        }

        setPropertyValueAction(dataInstance, value);
        return true;
    }

but I'm sure that you would have a cleaner and more simple solution e.g. passing a func of items so we could have more than 2 customitems like id/ value

[ipvalverde]

Hi @xavave , I'm glad this library is being useful to you.

I think I've found a generic solution for this problem. Let me know if an API like this would suffice for your requirements.

Considering a model like this:

public class MyRowModel
{
    public string Code { get; set; }
    public DateTime UpdateDate { get; set; }
    public string Comments { get; set; }
    public List<RmInfo> Rms { get; set; }
}
public class RmInfo
{
    public string Id { get; set; }
    public string PoIds { get; set; }
}

The code for extracting the data would be:

                var data = package.Workbook.Worksheets["MyWorksheet"]
                    .Extract<MyRowModel>()
                    .WithProperty(p => p.Code, "C")
                    .WithProperty(p => p.UpdateDate, "D")
                    .WithProperty(p => p.Comments, "E")

                   // New overload:
                    .WithCollectionProperty(p => p.Rms,
                        1, // The index of the row where the "header" is located
                        "C", // The column letter indicating where this collection starts

                        // This is an action with a parameter that can be used to configure the
                       // columns for the collection (property "Rms")
                        cfg => cfg
                                .WithProperty(rmInfo => rmInfo.Id, "RM ID") // Maps a property to the column header
                                .WithProperty(rmInfo=> rmInfo.PoIds, "RM poids"))
                               // Finishes the "WithCollectionProperty" method call
                    .GetData(2, 4)
                    .ToList();

In this example, the extractor will start looking for columns with a header that matches the ones configured in the inner WithProperty methods ("RM ID" and "RM poids"). This search will start in the column "C" and it will go on until there are no more cells with text. Another option would be to stop looking if the column text does not match any of the properties configured.

I expect to have this published in a new beta release by Monday.

[xavave]

Yes, it's exactly what I need , thank you so much ! I was currently modifing your source code , I was in need for 3 nested columns and 1 convertdatafunc for each nested column, so I have added :

    ICollectionPropertyConfiguration<TRow> WithCollectionProperty<TCollectionItem, THeaderValue, THeaderValue2, THeaderValue3, TRowValue, TRowValue2, TRowValue3>(
        Expression<Func<TRow, List<TCollectionItem>>> propertyCollection,
        Expression<Func<TCollectionItem, THeaderValue>> headerProperty, Expression<Func<TCollectionItem, THeaderValue2>> headerProperty2,
         Expression<Func<TCollectionItem, THeaderValue3>> headerProperty3,
        int headerRow,
        Expression<Func<TCollectionItem, TRowValue>> rowProperty,
        Expression<Func<TCollectionItem, TRowValue2>> rowProperty2,
        Expression<Func<TCollectionItem, TRowValue3>> rowProperty3,
          Func<object, TRowValue> convertDataFunc1,
        Func<object, TRowValue2> convertDataFunc2,
        Func<object, TRowValue3> convertDataFunc3,
        string startColumn, string endColumn)
        where TCollectionItem : class, new();

but your solution is much better

[ipvalverde]

Hi @xavave I just published the version 2.0.0-alpha2. There is no documentation yet but you can check this unit test with an example. The spreadsheet can be download from here.

[ipvalverde]

Sorry @xavave , I can't see your screenshots. I've noticed that I only implemented support for the List<T> type property.

Can you make sure that:

• You are referencing the latest version (2.0.0-alpha0002);
• The collection property of your model is of type List<T>;

If the problem still persists, could you provide a small fully-functional sample of code where I can reproduce it (in a github repository, or just pasting the code into the comments)?

[xavave]

it's working but not when I clone the alpha2 repository from https://github.com/ipvalverde/EPPlus.DataExtractor/tree/2.0.0-alpha2 the updated test method file https://github.com/ipvalverde/EPPlus.DataExtractor/blob/2.0.0-alpha2/src/EPPlus.DataExtractor.Tests/WorksheetExtensionsTests.cs is not in the downloaded clone and the dataextractor source is not up to date , it works (all file are included and compilation is OK) when I use "download zip" instead of "clone" button the nuget package has the same issue than the clone button so as a workaround I downloaded the zipped version in git and referenced the dataextractor dll directly in my project without nuget btw, I've also updated EPPLUS reference to the last sunday version 4.5.1 in data extractor project that I have locally

and it's working fine now :)

[xavave]

Hi I just had to add a new contructor with convertDataFunc to fullfill my requirments: in ICollectionPropertyConfiguration:

  IColumnToCollectionConfiguration<TCollectionItem> WithProperty<TColumnValue>(
   Expression<Func<TCollectionItem, TColumnValue>> columnValueProperty, string columnHeader, 
Func<object, TColumnValue> convertDataFunc);

then in class ColumnToCollectionConfiguration:

        public IColumnToCollectionConfiguration<TCollectionItem> WithProperty<TColumnValue>(
   Expression<Func<TCollectionItem, TColumnValue>> columnValueProperty, string columnHeader, Func<object, TColumnValue> convertDataFunc)
    {
        var dataExtractor = new RowDataExtractor<TCollectionItem, TColumnValue>(columnValueProperty, convertDataFunc);

        this.propertiesSettersByHeader.Add(columnHeader, dataExtractor);

        return this;
    }

and in RowDataExtractor :

internal class RowDataExtractor<TModel, TValue> : PropertyValueSetter<TModel, TValue>,
    IRowDataExtractor<TModel>
    where TModel : class, new()
{
    public RowDataExtractor(Expression<Func<TModel, TValue>> propertyExpression, Func<object, TValue> convertDataFunc) : base(propertyExpression, convertDataFunc, null, null)
    {}

    void IRowDataExtractor<TModel>.SetPropertyValue(TModel dataInstance, ExcelRangeBase cellRange)
    {
        base.SetPropertyValue(dataInstance, cellRange);
    }
}

[xavave]

Would it be also possible to add 2 collection properties like this ? (the prices2 collection should be added to price1 collection as price1 and price2 are 2 distinct "Price" instances) when I try this below, I get a compilation error on the first cfg2.WithProperty:

Error CS1061 'Price' does not contain a definition for 'WithProperty' and no extension method 'WithProperty' accepting a first argument of type 'Price' could be found (are you missing a using directive or an assembly reference?)

  .WithCollectionProperty(p => p.Prices, 1, "A",
 cfg => cfg
 .WithProperty(pr => pr.RawMaterialId, "Index")
 .WithProperty(pr => pr.PriceValue, "price1", fnConvertPrice))
 .WithCollectionProperty(p => p.Prices, 1, "A",
 cfg2 => cfg2
 .WithProperty(pr => pr.RawMaterialId, "Index")
 .WithProperty(pr => pr.PriceValue, "price2", fnConvertPrice)
 )

[xavave]

A good point would be also to be able to map an object value not related to excel : e.g.: .WithProperty(pr => pr.PriceCurrencyId, 1) below:

.WithCollectionProperty(p => p.Prices, 1, "A",
cfg => cfg
.WithProperty(pr => pr.RawMaterialId, "Index")
.WithProperty(pr => pr.PriceCurrencyId, 1)
 .WithProperty(pr => pr.PriceValue, "price1", fnConvertPrice)

[xavave]

I've also noticed a possible issue :

I'm importing some prices and PriceValue (of type double) is filled with the good value from excel:

    var importedPrices2 = ws.Extract<Price>()
          .WithProperty(p => p.RawMaterialId, "A")
          .WithProperty(p => p.PriceValue, "S", fnConvertPrice)
           .GetData(2, (row) => ws.Cells[row, 2].Value != null).ToList();

but if I do the same with :

    var importedRawMaterials = ws.Extract<RawMaterial>()
    .WithProperty(...)
   .WithProperty(...)
    .WithProperty(...)
  .WithCollectionProperty(p => p.Prices, 1, "A",
   cfg => cfg
   .WithProperty(pr => pr.RawMaterialId, "Index")
   .WithProperty(pr => pr.PriceValue, "price1", fnConvertPrice)
  ).GetData(2, (row) => ws.Cells[row, 2].Value != null).Where(s => s.Id > -1).ToList();

 var newList = importedRawMaterials.ToList());

but

 newList .First().Prices.First().PriceValue== 0;//all "PriceValues" are equal 0 (but not supposed to be so)

The PriceValue as been lost after ToList() call when I put a break point on fnConvertPrice, the value is filled correctly

If I don't use fnConvertPrice, I get the same issue