Using Epplus to generate xlsx with nested list

Question

I am using EPPlus to generate a xlsx document.

My model below includes a List<string> and this is where things got complicated for me:

                var tableBody = worksheet.Cells["B2:B2"].LoadFromCollection(
                    from f in saaOperators.OrderBy(x => x.Identifier).Where(t => t.Identifier.StartsWith(worksheet.Name) ||

                    (t.Identifier.StartsWith("W") && worksheet.Name.Equals("BIR")))

                    from u in f.Units

                    select new { f.Identifier, f.Name, f.Profile, u }, true);

If I am doing select new {f.Identifier, f.Name, f.Profile, f.Units } it returns only the first item in the Units list.

If I am doing select new { f.Identifier, f.Name, f.Profile, u } for every item in Units it creates new rows with duplicate Identifier, Name and Profile.

Here is my model class:

public class SaaOperator
{
    public string Identifier { get; set; }
    public string Name { get; set; }
    public string Profile { get; set; }
    public List<string> Units { get; set; } = new List<string>();
}

What is the proper way to identify cells with the same value for Identifier, Name and Profile and merge them?

For example, in the screenshot below I need to merge B3:B4, B5:B6, C3:C4, C5:C6, D3:D4, D5:D6.

I know I can use worksheet.Cells["B3:B4"].Merge = true;, but I need a way to programatically identify the start and end cell with the same value.

EDIT 1: Added a screenshot with duplicated values.

EDIT 2 - Based on Ernie's comment, I need a way to programatically search the worksheet for cells with the same value and merge them.

Answer 1

Based on the comments, I would avoid the LoadFromCollection method and do a traditional for since what you are doing is too specific. And I would also avoid trying to merge cells since that makes thing unnecessarily complicated. This should do it:

var data = saaOperators
    .Where(t => t.Identifier.StartsWith(worksheet.Name) || (t.Identifier.StartsWith("W") && worksheet.Name.Equals("BIR")))
    .OrderBy(x => x.Identifier)
    .ToList();

var r = 2;

worksheet.Cells[r, 1].Value = "Identifier";
worksheet.Cells[r, 2].Value = "Name";
worksheet.Cells[r, 3].Value = "Profile";
worksheet.Cells[r, 4].Value = "Unit";
r++;

for (var i = 0; i < data.Count; i++)
{
    var op = data[i];
    worksheet.Cells[r + i, 1].Value = op.Identifier;
    worksheet.Cells[r + i, 2].Value = op.Name;
    worksheet.Cells[r + i, 3].Value = op.Profile;

    if (!op.Units.Any())
        continue;

    for (var j = 0; j < op.Units.Count; j++)
        worksheet.Cells[r + i + j, 4].Value = op.Units[j];

    r += op.Units.Count - 1;
}