Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Determine Merged Cells in a Certain Row

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.

like image 712
Israel Sato Avatar asked Apr 02 '13 03:04

Israel Sato


1 Answers

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());
        }
    }
}
like image 68
boris1993 Avatar answered Sep 25 '22 03:09

boris1993