CODEWITHSUNDEEP
c#excelclosedxml
DeveloperLV
DeveloperLV

Reputation: 1781

How to merge same values together using C# and ClosedXML?

I'm trying to merge the same values together for Excel using ClosedXML package in C#.

This is my current output.. it's not merging the same values together and leaving some out.

e.g. No.7 merged two cells and not the third.

e.g. No.9 keeps merging 2 cells and repeats the same merge.

enter image description here

My current logic:

// Foreach record
int i = 1;

foreach (var record in ...)
{
    i++;

    // Minimum Months
    // Create Value
    worksheet.Cell($"D{i}").Value = record.MinimumMonths.ToString();


    // Evaluate value - if they're the same then merge
    if(i > 2 && worksheet.Cell($"D{i}").Value.ToString() == worksheet.Cell($"D{i - 1}").Value.ToString())
    {
        worksheet.Range($"D{i - 1}:D{i}").Merge();
    }    
}

I don't understand where my logic is going wrong. How can I merge all the same values together?

Upvotes: 0

Views: 1177

Answers (1)

Francois Botha
Francois Botha

Reputation: 4849

worksheet.Range($"D{i - 1}:D{i}").Merge();

You shouldn't be merging your cells in pairs. Identify the first and last cells of a range of cells with the same value and merge that entire range in one .Merge() statement.

Also notice that when you merge a range of cells, only the top left cell keeps its value. All the other cells' values in the merged range are cleared. This is in line with what Excel does.

Your .Value.ToString() equality comparison is a bit naive. Rather check with cell1.Value is double d1 && cell2.Value is double d2 && d1.Equals(d2).

Upvotes: 1

Related Questions