How to Determine Merged Cells in a Certain Row

apache-poi, java

Solution

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();
        }
    }
}

Problem

Here's what I know so far: - You use the method sheet.getNumMergedRegions() to get the number of merged regions in a particular sheet - You loop through each count and use the method sheet.getMergedRegion(i) and assign to a CellRangeAddress variable - 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.

Original source