Merged rows

[juanch0x]

Hello, amazing job with DataExtractor!

but, is some solution about merged rows?

some like copying the merged row value on every List who has that value? or some kind of collection way?

[ipvalverde]

Thanks! Currently, the library has no support for merged rows... I guess this is something that would be useful.

If you have a table like this:

	A	B	C
1	Client	Property Location	Value
2	John	New York City, NY, US	1,750,000
3		Orlando, FL, US	370,000
4	Michael	Seattle, WA, US	1,350,000
5		Whitehorse, YT, CA	400,000
6		Halifax, NS, CA	399,900

This could be achieved in two ways, just like you said. The first, and easier one, is to simply aknowledge that a row is a merged row and obtain its value from the row above the current one. So for the previous example, with a data structure like this the library would simply work:

public class CustomerProperties
{
  public string Client { get; set; }
  public string Location { get; set; }
  public decimal? Value { get; set; }
}

The value for the merged cell would be replicated to all the rows within the merge. This will easily work across different levels of merged rows as well, so you can have one record that has a cell merged with the 3 rows above it, and another cell in the same row merged with 2 rows above it, it wouldn't matter for the library. If you want to have the values not duplicated, it could be simply done by the consumer of library using, for example the GroupBy method.

The second one would be the fancy one, where you could extract it to the following data structure:

public class CustomerProperties
{
  public string Client { get; set; }
  public Collection<Property> Properties { get; set; }
}

public class Property
{
  public string Location { get; set; }
  public decimal? Value { get; set; }
}

We could have something like this (for a row with a single merged cell):

using (var package = new ExcelPackage(".../file.xlsx"))
{
    var data = package.Workbook.Worksheets["worksheetMergeSample"]
        .Extract<CustomerProperties>()
       // The parameters would be
        .WithMergedProperty(
            p => p.Client, // The property that is merged
            "A", // The columns of such property
            p => p.Properties, // A property of a ICollection type that will hold the other values grouped by the merged property

            // A function where you can configure all the "Sub props" with the "otherProps" param being the generic type of the collection property previously defined (p.Properties)
            otherProps => otherProps.WithProperty(x => x.Location, "B") // Here you have definition for the other fields
                                       .WithProperty(x => x.Location, "C") // Porentially being able to set ANOTHER merged property creating a nesting
        )
        .GetData(2, 6)
        .ToList();
}