How to define merged cells in a specific row

Question

Here is what I know so far:

• You use the sheet.getNumMergedRegions () method to get the number of merged regions in a specific sheet.
• You look at each counter and use the sheet.getMergedRegion (i) method and assign the variable CellRangeAddress
• Then you use the isInRange function (rowIndex, colIndex) to see if a particular cell is part of a merged area.

But I wanted to do the following: I want to see if it is possible to define merged cells defined only by a specific row. As if I had a specific line, I want to know the number of all merged areas found only under that line.

I would always be grateful if someone could share their ideas or suggestions on this.

Answer 1

Here is an example of code that does what you want, I believe. GetNbOfMergedRegions returns, well, the number of combined regions in a particular row. Remember that in POI numbers, numbers start from scratch!

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 have a solution for this. Essentially, I just translated your thoughts into Java code.

First you will need these 2 methods.

The first helps determine if a 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 helps you extract 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 over the rows and columns on this sheet and perform different actions depending on whether this cell is in the 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()); } } }