Here's what I know so far:
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.
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());
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With