I'm currently using NPOI library to read the value of all cells in one row of a xlsx file and return a List<string>. However, if a cell is part of a merged cell, then I need to get the value of that merged cell instead of a blank value. (In NPOI, if a cell is part of a merged cell but is not the top-most and left-most cell in that merged region, then when using FormatCellValue on that cell, we got a empty string).

Let say that I have already retrieved the necessary row and get an IRow object, and there is no empty cell at the left of this row. Also, my data will always be string, there will be no other data type.

This is what I came up with:

private List<string> readRow(IRow row)
    var rs = new List<string>();

    // Since there would be no emtpy cell, 
    // we start from the left-most cell and read until the end.
    for (int colIndex = 0; colIndex < row.LastCellNum; colIndex++)
        var cell = row.GetCell(colIndex);

        // Get the value of this cell, whether it is part of a merged cell or not.
        var cellValue = getCellValue(cell);

    return rs;

And the method to get cell value:

private string getCellValue(ICell cell)
    var dataFormatter = new DataFormatter(CultureInfo.CurrentCulture);

    // If this is not part of a merge cell,
    // just get this cell's value like normal.
    if (!cell.IsMergedCell)
        return dataFormatter.FormatCellValue(cell);

    // Otherwise, we need to find the value of this merged cell.
        // Get current sheet.
        var currentSheet = cell.Sheet;

        // Loop through all merge regions in this sheet.
        for (int i = 0; i < currentSheet.NumMergedRegions; i++)
            var mergeRegion = currentSheet.GetMergedRegion(i);

            // If this merged region contains this cell.
            if (mergeRegion.FirstRow <= cell.RowIndex && cell.RowIndex <= mergeRegion.LastRow &&
                mergeRegion.FirstColumn <= cell.ColumnIndex && cell.ColumnIndex <= mergeRegion.LastColumn)
                // Find the top-most and left-most cell in this region.
                var firstRegionCell = currentSheet.GetRow(mergeRegion.FirstRow)

                // And return its value.
                return dataFormatter.FormatCellValue(firstRegionCell);
        // This should never happen.
        throw new Exception("Cannot find this cell in any merged region");

I was surprised to find that there is no simpler way to get value of merged cell using NPOI. What can I improve here?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.