Processing Merged Cell Values in Apache POI

When working with spreadsheets, combining adjacent cells often requires aggregating their underlying data beforehand. Once a region is merged, only the top-left cell retains its original value, while the rest are erased. Therefore, any arithmetic operation must occur prior to applying the merge.

Procedure Overview

Implementation Steps

1. Loading the Spreadsheet

Initialize the workbook object from the source file using Apache POI's factory method.

File sourceFile = new File("data_input.xlsx");
Workbook excelWorkbook = WorkbookFactory.create(sourceFile);
Sheet activeSheet = excelWorkbook.getSheetAt(0);

2. Aggregating Values Before Merging

Iterate through the designated columns to compute the total. It is crucial to perform this step before invoking the merge command to prevent data loss.

int targetRowIdx = 0;
int startColIdx = 0;
int endColIdx = 4;
double totalSum = 0.0;

Row dataRow = activeSheet.getRow(targetRowIdx);
for (int colPointer = startColIdx; colPointer <= endColIdx; colPointer++) {
    Cell dataCell = dataRow.getCell(colPointer);
    if (dataCell != null && dataCell.getCellType() == CellType.NUMERIC) {
        totalSum += dataCell.getNumericCellValue();
    }
}

3. Executing the Merge

Define the boundaries of the cell range and add it to the sheet.

CellRangeAddress mergeZone = new CellRangeAddress(targetRowIdx, targetRowIdx, startColIdx, endColIdx);
activeSheet.addMergedRegion(mergeZone);

4. Updating the Merged Cell and Saving

Assign the calculated total to the primary cell of the merged region and write the modified workbook to a new file.

Cell primaryCell = dataRow.getCell(startColIdx);
if (primaryCell == null) {
    primaryCell = dataRow.createCell(startColIdx);
}
primaryCell.setCellValue(totalSum);

FileOutputStream destinationStream = new FileOutputStream("data_output.xlsx");
excelWorkbook.write(destinationStream);
destinationStream.close();
excelWorkbook.close();