How to map List of class object

[janniweigelt]

I have a class with a list of class, so how do I map for instance 3 columns into a object to add to the list?

[hughbe]

Can you share your column layout and the class structure

[janniweigelt]

Columns in Excel Name, Address, MondayLabel, MondayOpen, MondayClose, TuesdayLabel, TuesdayOpen, TuesdayClose and so on for each day

Class: string Name string Address List<BusinessHours> BusinessHours

Class BusinessHours string DayLabel string StartTime string EndTime

[hughbe]

Just to confirm, this is the file:

And we expect the following structure

{
    Name = "TheName",
    Address = "TheAddress",
    BusinessHours =
    {
        [0] =
        {
            DayLabel = "TheMondayLabel",
            StartTime = "TheMondayOpen",
            EndTime = "TheMondayClose",
        },
        [1] =
        {
            DayLabel = "TheTuesdayLabel",
            StartTime = "TheTuesdayOpen",
            EndTime = "TheTuesdayClose",
        },
        ...
    }
}

[hughbe]

I'm not sure this is currently supported, but it would be great to support this kind of use case.

I don't think we can retrofit this into existing APIs, so would need to develop something new. Have you got any ideas of how an API would look like that could do this generally.

Thinking about a workaround, you may be able to define a substructure

class Parent
{
    string Name
    string Address
    Child Child
}

class Child
{
    string MondayDayLabel
    string MondayStartTime
    string MondayEndTime
    string TuesdayDayLabel
    string TuesdayStartTime
    string TuesdayEndTime
    ...
}

Obviously you'd need to do custom mapping on the Child class because the names of the columns don't match the property names

[janniweigelt]

Hi yes that is exactly what I mean, unfortunately I am not able to change the structure as this is used for something else. I hope it will come at some point, but for now I will read each row as a datarow.

[hughbe]

Maybe you could try creating a class as a temporary structure (as above) and then convert it into the structure you use elsewhere

E.g.

var permanentStructure = new Class()
var temporaryStructure= ...; // Read from the excel sheet
permanentStructure.BusinessHours.Add(new BusinessHours
{
    DayLabel = temporaryStructure.MondayDayLabel,
    StartTime = temporaryStructure.MondayStartTime,
    EndTime = temporaryStructure.MondayEndTime
});
etc. etc.

[hughbe]

OK I've come up with probably the most disgusting workaround/hacky code I've ever written haha!

This does the job but obviously isn't extensible at all... I spent some time thinking about a generic way to do this but I really struggled

/cc @ltemimi this may help

[Fact]
public void ReadRow_MapNestedList_ReturnsExpected()
{
    using var importer = Helpers.GetImporter("NestedList.xlsx");
    importer.Configuration.RegisterClassMap<NestedListParentClassMap>();

    ExcelSheet sheet = importer.ReadSheet();
    sheet.ReadHeading();

    NestedListParentClass row1 = sheet.ReadRow<NestedListParentClass>();
    Assert.Equal("TheName", row1.Name);
    Assert.Equal("TheAddress", row1.Address);
    Assert.Equal(2, row1.BusinessHours.Count);
    Assert.Equal("TheMondayLabel", row1.BusinessHours[0].DayLabel);
    Assert.Equal("TheMondayOpen", row1.BusinessHours[0].StartTime);
    Assert.Equal("TheMondayClose", row1.BusinessHours[0].EndTime);
    Assert.Equal("TheTuesdayLabel", row1.BusinessHours[1].DayLabel);
    Assert.Equal("TheTuesdayOpen", row1.BusinessHours[1].StartTime);
    Assert.Equal("TheTuesdayClose", row1.BusinessHours[1].EndTime);
}

public class NestedListParentClass
{
    public string Name { get; set; }
    public string Address { get; set; }
    public List<BusinessHours> BusinessHours { get; set; }
}

public class BusinessHours
{
    public string DayLabel { get; set; }
    public string StartTime { get; set; }
    public string EndTime { get; set; }
}

public class NestedListParentClassMap : ExcelClassMap<NestedListParentClass>
{
    public NestedListParentClassMap()
    {
        Map(v => v.Name);
        Map(v => v.Address);
        Expression<Func<NestedListParentClass, List<BusinessHours>>> expression = v => v.BusinessHours;
        var businessMap = new ExcelPropertyMap(GetMemberExpression(expression).Member, new BusinessHoursMap());
        AddMap(businessMap, v => v.BusinessHours);
    }
}

public class BusinessHoursMap : IMap
{
    private int _previousRowIndex = -1;
    private int _currentIndex = 0;

    public bool TryGetValue(ExcelSheet sheet, int rowIndex, IExcelDataReader reader, MemberInfo member, out object value)
    {
        // Note: only works for 2 days (Monday, Tuesday) as written, but easy to extend.
        var result = new List<BusinessHours>();
        for (int i = 0; i < 2; i++)
        {
            if (_previousRowIndex != rowIndex)
            {
                _previousRowIndex = rowIndex;
                _currentIndex = 0;
            }

            string prefix;
            switch (_currentIndex)
            {
                case 0:
                    prefix = "Monday";
                    break;
                case 1:
                    prefix = "Tuesday";
                    break;
                default:
                    throw new NotImplementedException();
            }

            // Onto the next column.
            _currentIndex++;

            // Format: "<DayOfWeek>DayLabel"
            var labelReader = new ColumnNameValueReader(prefix + "Label");
            var startTimeReader = new ColumnNameValueReader(prefix + "Open");
            var endTimeReader = new ColumnNameValueReader(prefix + "Close");
            if (!labelReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult labelResult) ||
                !startTimeReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult startTimeResult) ||
                !endTimeReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult endTimeResult))
            {
                throw new InvalidOperationException("No such column");
            }

            result.Add(new BusinessHours
            {
                DayLabel = labelResult.StringValue,
                StartTime = startTimeResult.StringValue,
                EndTime = endTimeResult.StringValue
            });            
        }

        value = result;
        return true;
    }
}

[ltemimi]

Hi

Thanks I understand, I have broken up the excel sheet and used linq join (it works similar to your solution)

I have a question. : Can I read data from specific ranges in a sheet that would help no end

regards Laz

---

From: Hugh Bellamy [email protected] Sent: 23 February 2021 17:33 To: hughbe/excel-mapper [email protected] Cc: ltemimi [email protected]; Mention [email protected] Subject: Re: [hughbe/excel-mapper] How to map List of class object (#64)

OK I've come up with probably the most disgusting workaround/hacky code I've ever written haha!

This does the job but obviously isn't extensible at all... I spent some time thinking about a generic way to do this but I really struggled

/cc @ltemimihttps://github.com/ltemimi this may help

[Fact] public void ReadRow_MapNestedList_ReturnsExpected() { using var importer = Helpers.GetImporter("NestedList.xlsx"); importer.Configuration.RegisterClassMap<NestedListParentClassMap>();

ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();

NestedListParentClass row1 = sheet.ReadRow<NestedListParentClass>();
Assert.Equal("TheName", row1.Name);
Assert.Equal("TheAddress", row1.Address);
Assert.Equal(2, row1.BusinessHours.Count);
Assert.Equal("TheMondayLabel", row1.BusinessHours[0].DayLabel);
Assert.Equal("TheMondayOpen", row1.BusinessHours[0].StartTime);
Assert.Equal("TheMondayClose", row1.BusinessHours[0].EndTime);
Assert.Equal("TheTuesdayLabel", row1.BusinessHours[1].DayLabel);
Assert.Equal("TheTuesdayOpen", row1.BusinessHours[1].StartTime);
Assert.Equal("TheTuesdayClose", row1.BusinessHours[1].EndTime);

}

public class NestedListParentClass { public string Name { get; set; } public string Address { get; set; } public List<BusinessHours> BusinessHours { get; set; } }

public class BusinessHours { public string DayLabel { get; set; } public string StartTime { get; set; } public string EndTime { get; set; } }

public class NestedListParentClassMap : ExcelClassMap<NestedListParentClass> { public NestedListParentClassMap() { Map(v => v.Name); Map(v => v.Address); Expression<Func<NestedListParentClass, List<BusinessHours>>> expression = v => v.BusinessHours; var businessMap = new ExcelPropertyMap(GetMemberExpression(expression).Member, new BusinessHoursMap()); AddMap(businessMap, v => v.BusinessHours); } }

public class BusinessHoursMap : IMap { private int _previousRowIndex = -1; private int _currentIndex = 0;

public bool TryGetValue(ExcelSheet sheet, int rowIndex, IExcelDataReader reader, MemberInfo member, out object value)
{
    // Note: only works for 2 days (Monday, Tuesday) as written, but easy to extend.
    var result = new List<BusinessHours>();
    for (int i = 0; i < 2; i++)
    {
        if (_previousRowIndex != rowIndex)
        {
            _previousRowIndex = rowIndex;
            _currentIndex = 0;
        }

        string prefix;
        switch (_currentIndex)
        {
            case 0:
                prefix = "Monday";
                break;
            case 1:
                prefix = "Tuesday";
                break;
            default:
                throw new NotImplementedException();
        }

        // Onto the next column.
        _currentIndex++;

        // Format: "<DayOfWeek>DayLabel"
        var labelReader = new ColumnNameValueReader(prefix + "Label");
        var startTimeReader = new ColumnNameValueReader(prefix + "Open");
        var endTimeReader = new ColumnNameValueReader(prefix + "Close");
        if (!labelReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult labelResult) ||
            !startTimeReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult startTimeResult) ||
            !endTimeReader.TryGetValue(sheet, rowIndex, reader, out ReadCellValueResult endTimeResult))
        {
            throw new InvalidOperationException("No such column");
        }

        result.Add(new BusinessHours
        {
            DayLabel = labelResult.StringValue,
            StartTime = startTimeResult.StringValue,
            EndTime = endTimeResult.StringValue
        });
    }

    value = result;
    return true;
}

}

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/hughbe/excel-mapper/issues/64#issuecomment-784373288, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABJ5F5AL5PCCW7LOKNBITILTAPREZANCNFSM4X2PO3HA.