Your programming logic is wrong and produces overlapping merged regions. That is what Excel does not tolerate and alerts the error about.
Your program does the following:
It starts with int b=i; int a=i; where i points to the first row. The values in first row and second row are equal. So your program sets a = b; so that now both a and b point to the first row. Then it merges spreadsheet.addMergedRegion(new CellRangeAddress(a+1, b, 1, 1));. So it creates <mergeCell ref="B2:B1"/>. This is not really correct as it should be <mergeCell ref="B1:B2"/>. But that Excel would tolerate. Now i increases and points to second row. The values in second row and third row are not equal. So your program sets b = i;. Now b points to second row but a has not changed and points to first row. Now your code merges a+1 and b which both points to second row. So it creates <mergeCell ref="B2:B2"/>. That one-cell-merged-region is useless but would be tolerated by Excel. But it additionally overlaps the former created <mergeCell ref="B2:B1"/>. And here Excel's tolerance ends.
The following Minimal, Reproducible Example shows a working program logic. It additional shows what Minimal, Reproducible Example really means.
Have a Excel workbook ExcelExample.xlsx containing following in columns A and B of first sheet:
Col1 Col2
Record1 Value1
Record2 Value1
Record3 Value2
Record4 Value3
Record5 Value3
Record6 Value3
Record7 Value4
Record8 Value5
Record9 Value5
Record10 Value5
Record11 Value6
Record12 Value7
Record13 Value7
Record14 Value7
Record15 Value8
Record16 Value9
Record17 Value10
Record18 Value10
Record19 Value10
Record20 Value10
Then do running the following code:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelMergeRowsByContent {
static void mergeRowsByContent(Sheet sheet, int col) {
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
DataFormatter dataFormatter = new DataFormatter();
dataFormatter.setUseCachedValuesForFormulaCells(true);
int fromRow = firstRow;
int toRow = 0;
boolean merge = false;
while(fromRow <= lastRow) {
Row row1 = sheet.getRow(fromRow); if (row1 == null) row1 = sheet.createRow(fromRow);
Cell cell1 = row1.getCell(col);
String cellValue1 = dataFormatter.formatCellValue(cell1);
toRow = fromRow + 1;
while (toRow <= lastRow) { // check whether following values are equal
Row row2 = sheet.getRow(toRow); if (row2 == null) row2 = sheet.createRow(toRow);
Cell cell2 = row2.getCell(col);
String cellValue2 = dataFormatter.formatCellValue(cell2);
System.out.println("cell value in row " + fromRow + " = " + cellValue1 + "; cell value in row " + toRow + " = " + cellValue2);
if (cellValue2.equals(cellValue1)) {
merge = true; // merging is needed
toRow++; // try whether next row also has equal value
} else {
break; // values were not equal, so break searching
}
}
if (merge) {
toRow--; // merge only rows where values are equal, last toRow was not
System.out.println("merging from row " + fromRow + " to row " + toRow);
sheet.addMergedRegion(new CellRangeAddress(fromRow, toRow, col, col));
fromRow = toRow;
}
merge = false;
fromRow++;
}
}
public static void main(String[] args) throws Exception {
try (
FileInputStream in = new FileInputStream("./ExcelExample.xlsx"); FileOutputStream out = new FileOutputStream("./ExcelExampleNew.xlsx");
//FileInputStream in = new FileInputStream("./ExcelExample.xls"); FileOutputStream out = new FileOutputStream("./ExcelExampleNew.xls");
Workbook workbook = WorkbookFactory.create(in); ) {
Sheet sheet = workbook.getSheetAt(0);
mergeRowsByContent(sheet, 1);
workbook.write(out);
}
}
}
After that it should have produced following result in ExcelExampleNew.xlsx:
Disclaimer: This code is tested and runs using current apache poi 5.2.3. Older apache poi versions are outdated and not supported anymore.
