How to Determine Merged Cells in a Certain Row

Question

Here's what I know so far:

1. You use the method sheet.getNumMergedRegions() to get the number of merged regions in a particular sheet
2. You loop through each count and use the method sheet.getMergedRegion(i) and assign to a CellRangeAddress variable
3. Then you use the isInRange(rowIndex, colIndex) function to see if a specific cell is part of the merged region.

But what I wanted to accomplish is this: I want to see if it's possible to determine merged cells given only a specific row. Like if i have a certain row, I wanna know the count of all merged regions found under that row only.

I'd be forever grateful if anyone can share their ideas or suggestions about this matter.

Answer 1

Here a code sample that does what you want I believe. The getNbOfMergedRegions gives back, well, the number of merged regions in a specific row. Remember that in POI row numbers start at zero !

package test;

import java.io.File;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class Main {

    public static int getNbOfMergedRegions(Sheet sheet, int row)
    {
        int count = 0;
        for(int i = 0; i < sheet.getNumMergedRegions(); ++i)
        {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.getFirstRow() <= row && range.getLastRow() >= row)
                ++count;
        }
        return count;
    }

    public static void main(String[] args) {
        File file = new File("/Users/enicolas/Downloads/test.xls");
        try 
        {
            Workbook wb = WorkbookFactory.create(file);  // Line 409 for ref to the exception stack trace
            Sheet sheet = wb.getSheetAt(0);
            for(int row = 0; row < 20; ++row)
            {
                int n = getNbOfMergedRegions(sheet, row);
                System.out.println("row [" + row + "] -> " + n + " merged regions");
            }
            System.out.println(wb);
        }
        catch (Throwable e) 
        {
            e.printStackTrace();
        }
    }
}

Answer 2

I've got a solution for this. Basically I just translated your thoughts into Java code.

First, you'll need these 2 methods.

The first one helps you determine if the given cell is in a merged cell.

/**
 * Get the index of the merged cell in all the merged cells
 * if the given cell is in a merged cell.
 * Otherwise, it will return null.
 *
 * @param sheet  The Sheet object
 * @param row    The row number of this cell
 * @param column The column number of this cell
 * @return The index of all merged cells, which will be useful for {@link Sheet#getMergedRegion(int)}
 */
private Integer getIndexIfCellIsInMergedCells(Sheet sheet, int row, int column) {
    int numberOfMergedRegions = sheet.getNumMergedRegions();

    for (int i = 0; i < numberOfMergedRegions; i++) {
        CellRangeAddress mergedCell = sheet.getMergedRegion(i);

        if (mergedCell.isInRange(row, column)) {
            return i;
        }
    }

    return null;
}

And the second one helps you fetch the content from it.

/**
 * Get the value from a merged cell
 *
 * @param sheet       The Sheet object
 * @param mergedCells The {@link CellRangeAddress} object fetched from {@link Sheet#getMergedRegion(int)} method
 * @return The content in this merged cell
 */
private String readContentFromMergedCells(Sheet sheet, CellRangeAddress mergedCells) {

    if (mergedCells.getFirstRow() != mergedCells.getLastRow()) {
        return null;
    }

    return sheet.getRow(mergedCells.getFirstRow()).getCell(mergedCells.getFirstColumn()).getStringCellValue();
}

Then you can iterate the rows and columns in this sheet, and do different things based on if this cell is in a merged cell or not.

for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
    Row row = sheet.getRow(rowNum);
    if (row == null) {
        continue;
    }

    int lastColumn = row.getLastCellNum();

    for (int columnNum = 0; columnNum < lastColumn; columnNum++) {
        // Determine if this cell is in a merged cell
        Integer mergedCellIndex = getIndexIfCellIsInMergedCells(sheet, rowNum, columnNum);

        if (mergedCellIndex != null) {
            // If it is in a merged cell
            // then get it
            CellRangeAddress cell = sheet.getMergedRegion(mergedCellIndex);

            // Do your logic here
            log.info("Cell is in a merged cell");
            log.info("Content is {}",
                    readContentFromMergedCells(sheet, sheet.getMergedRegion(mergedCellIndex)));

            // Get the last column of this merged cell
            int lastColumnOfThisMergedCell = sheet.getMergedRegion(mergedCellIndex).getLastColumn();

            // And skip those merged cells
            // since the columnNum will increase 1 on next loop
            // so you have to minus 1 here
            columnNum = columnNum + lastColumnOfThisMergedCell - 1;
            log.info("Next column being processed is {}", columnNum);
        } else {
            // If it is not in a merged cell
            // hence, an "individual" cell
            Cell cell = row.getCell(columnNum, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }

            // Then you can simply do your logic
            // and continue to the next loop
            log.info("Cell is an individual cell");
            log.info("Content is {}",
                    row.getCell(columnNum).getStringCellValue());
        }
    }
}