GemBox SpreadSheet and merged cells

Question

To illustrate, create a new Excel file, with merged cell. e.g. row 2 below has columns A and B merged:

If you run code like this, where worksheet is a GemBox.Spreadsheet.ExcelWorksheet:

string v1 = worksheet.Cells[1, 0].GetFormattedValue();  // cell A2 ?
string v2 = worksheet.Cells[1, 1].GetFormattedValue();  // cell B2 ?

Both v1 and v2 will contain "xyz merged". I'm guessing this is by design for merged cells. Is there an easy way to obtain "xyz merged" for the 1st merged cell (A2), and return null for all subsequent merged cells in the same merged range? I saw there is a cell.MergedRange property, which returns not null when a cell is merged, so may be able to use that in conjunction with FirstColumnIndex, LastColumnIndex, FirstRowIndex, LastRowIndex properties, to work out if the cell is the first one in the merged range. Wondering if there might be an easier (or more efficient) way ?

score 1 · Accepted Answer · answered Oct 25 '22 at 13:06

Yes, this is by design, you'll need to use the ExcelCell.MergedRange property to get the desired result.
For example, try something like this:

public static class GemBoxSpreadsheetExtension
{
    public static string GetFormattedValue(this ExcelCell cell, bool skipMergedCell)
    {
        if (!skipMergedCell || cell.MergedRange == null || cell.MergedRange[0] == cell)
            return cell.GetFormattedValue();

        return null;
    }
}

You could use this extension method like this:

string v1 = worksheet.Cells[1, 0].GetFormattedValue(true);
string v2 = worksheet.Cells[1, 1].GetFormattedValue(true);

GemBox SpreadSheet and merged cells

1 Answers1