EPPLUS using loadfromcollection with merged cells

Question

I have to fill an excel like this

Where I have merged cells on each row. I'm trying to use loadfromcollection, but it ignores the merged cells and it fills each single row.

Please did you know any quick way to do it?

---

edit: I think that one way is to implement a custom loadfromcollection where the steps are:

1. Iterate collection
2. For each object, read properties/fields using reflection
3. Iterate cell on each row
4. check if range address is in worksheet.mergecells
5. fill it with property/field value

But in case of complex worksheet (with some loadfromcollection), I think that this approach could be heavy with a big cost.

---

edit 2: Add an example: Code like this

public class MyObj
{
  public string first {get; set;}
  public string second {get; set;}
}
...
List<MyObj> myList = new List<MyObj>() {
   new MyObj() {first = "first1", second = "second1"},
   new MyObj() {first = "first2", second = "second2"} };
...
ws.Cells["A1"].LoadFromCollection(myList);

In a normal worksheet, loadfromcollection has the following output:

If I merge column A and B after filled, I get this:

In case that my worksheet template is

and then I try to fill it using loadfromcollection I get this result

loadfromcollection ignores merged cell, and fill column A and B, but my expectation is

Answer 1

The problem is that this is an ´intended´ behavior in excel. When you have values in Columns A and B and you merge them, only the value in column A will remain. LoadFromCollection does not have an overload for this kind of scenario.

I can think of several ´workarounds´ which could work, depending on how general you want the code to be.

1. Your solution of iterating through object properties with reflection would work. If the ´merging´ is always two columns, I´d recommend using a column counter and add 2 to it each property iteration.

So something like this (where of course you already defined the excel package and worksheet etc and the start row and column):

var type = testObject.GetType();
var properties = type.GetProperties();
            
foreach (var property in properties)
{
       cell = worksheet.Cells[row, column];
       cell.Value = property.GetValue(testObject);

       column += 2;
}

2. Alternatively, you could decide to remove the merging from the template and move it to your code instead. This may provide a little bit more consistency and flexibility as well.

Code above would then look more like:

var type = testObject.GetType();
var properties = type.GetProperties();
            
foreach (var property in properties)
{
       cellRange = worksheet.Cells[row, column, row, column + 1];

       cellRange.Merge = true;
       cellRange.Value = property.GetValue(testObject);
       
       column += 2;
}

You could even go a bit 'crazy' and make the range variable based on the property. You make a switch statement with all known properties which should be 2 merged cells, those which should be 3 merged cells etc. Then make the default 1 cell/column. This way you gain total control over the output and it's much easier to adjust in the future.

Edit

My case is a little bit more complex, not all my mergedcells are composed by 2 cells

Some code to illustrate my above statement with some code to help address this. Used the old switch notation as you said it was an 'old project' and I assume you aren't using C# 8 or higher yet.

var type = testObject.GetType();
var properties = type.GetProperties();
int columnMergeSize;
            
foreach (var property in properties)
{
       columnMergeSize = GetPropertyColumnMergeSize(property.Name);

       cellRange = worksheet.Cells[row, column, row, column + columnMergeSize];

       cellRange.Merge = true;
       cellRange.Value = property.GetValue(testObject);
       
       column += 2;
}

private int GetPropertyColumnMergeSize(string propertyName)
{
    switch (propertyName)
    {
        case "Property1":
        case "Property2":
        case "Property3":
        case "Property4":
            return 2;
        case "Property5":
        case "Property6":
            return 3;
        default:
            return 1;
    }
}

This will be less heavy than having to read out the properties of the cell each time to see if it's 'merged' or not. I've also found that if you remove nearly all formatting from an excel template it will load much faster speeding up your programme.

You can either decide to put this in a service and just inject the method into wherever it's needed with DI, or you can make it a static method and use it.