Using NPOI to Retrieve the Value of a Merged Cell from an Excel Spreadsheet

In Excel, if a cell is merged with other cells, the first cell in the merged region is the one that has the actual value. The other cells in the region are blank. The merged regions are kept in the worksheet object, since they can span multiple rows and columns.

To get the value, you need to:

1. Check whether the current cell is merged by looking at the IsMergedCell property on the cell itself.
2. If the cell is merged, loop through the merged regions on the worksheet to find the one containing that cell.
3. Once the containing region is found, get the first cell from the region.
4. Get the value from that cell.

Here is a helper method I wrote which should do the trick:

public static ICell GetFirstCellInMergedRegionContainingCell(ICell cell)
{
    if (cell != null && cell.IsMergedCell)
    {
        ISheet sheet = cell.Sheet;
        for (int i = 0; i < sheet.NumMergedRegions; i++)
        {
            CellRangeAddress region = sheet.GetMergedRegion(i);
            if (region.ContainsRow(cell.RowIndex) && 
                region.ContainsColumn(cell.ColumnIndex))
            {
                IRow row = sheet.GetRow(region.FirstRow);
                ICell firstCell = row?.GetCell(region.FirstColumn);
                return firstCell;
            }
        }
        return null;
    }
    return cell;
}

Then as you are looping through your cells you can just call this method for every cell. If the cell is merged, it will return the cell that has the value for that merged region, otherwise it will just return the original cell back. So then you don't have to think about it anymore.

cell = GetFirstCellInMergedRegionContainingCell(cell);
if (cell != null)
{
    // get the value
}