Confusion with merge using POI

Question

I'm writing a program in POI to merge cells and I'm able to merge them. there is a column with number content, and when I merge this column and when I open my sheet and select this column to my surprise this shows the count and sum as if it is not merged.

Below is my Excel.

Here the count should be 5 and sum should be 530 but this shows this as 25 and 2650

Below is my code.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class RowsMerge {
    // public static void main(String[] args)
    public static void main(String[] args) throws IOException {
        FileInputStream fin = new FileInputStream(
                new File("C:\\D\\Sheets\\Quality Sheet\\Quality_template.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(fin);
        HSSFSheet sheet = workbook.getSheetAt(0);

        int row = sheet.getPhysicalNumberOfRows();
        String currentLawName, currentCountry, currentAssociate, previousLawName, previousCountry, previousAssociate;
        String currentPages, previousPages;
        int startIndex = 1, finalIndex = 0;

        System.out.println(row);
        for (int i = 2; i < row; i++) {
            currentAssociate = sheet.getRow(i).getCell(1).toString();
            currentLawName = sheet.getRow(i).getCell(3).toString();
            currentCountry = sheet.getRow(i).getCell(4).toString();
            currentPages = sheet.getRow(i).getCell(5).toString();

            previousAssociate = sheet.getRow(i - 1).getCell(1).toString();
            previousLawName = sheet.getRow(i - 1).getCell(3).toString();
            previousCountry = sheet.getRow(i - 1).getCell(4).toString();
            previousPages = sheet.getRow(i - 1).getCell(5).toString();

            if (currentAssociate.equals(previousAssociate) && currentCountry.equals(previousCountry)
                    && currentLawName.equals(previousLawName) && currentPages.equals(previousPages)) {
                finalIndex += 1;
                if (((i + 1) == row)) {
                    System.out.println("yes");
                    finalIndex += 1;
                    sendRangeToMergeCells(startIndex + 1, finalIndex - 1, sheet, workbook);
                }
            } else {
                sendRangeToMergeCells(startIndex + 1, finalIndex, sheet, workbook);
                startIndex = i;
                finalIndex = 0;
            }

        }
        FileOutputStream fileOut = new FileOutputStream("C:\\D\\Sheets\\Quality Sheet\\new.xls");
        workbook.write(fileOut);
        fileOut.close();
    }

    private static void sendRangeToMergeCells(int startIndex, int finalIndex, HSSFSheet sheet, HSSFWorkbook workbook) {
        System.out.println(startIndex + "\t" + (startIndex + finalIndex));

        CellRangeAddress region = CellRangeAddress.valueOf("F" + (startIndex) + ":F" + ((startIndex + finalIndex)));

        sheet.addMergedRegion(region);

    }

}

How can I fix this?

Answer 1

This may be the result of a bug, but a workaround may be to shift the rows up instead of merging them.

public class RowsMerge {

// public static void main(String[] args)
public static void main(String[] args) throws IOException {
    FileInputStream fin = new FileInputStream(
            new File("C:\\D\\Sheets\\Quality Sheet\\Quality_template.xls"));
    HSSFWorkbook workbook = new HSSFWorkbook(fin);
    HSSFSheet sheet = workbook.getSheetAt(0);

    int lastRow = sheet.getLastRowNum();
    String currentLawName, currentCountry, currentAssociate, previousLawName, previousCountry, previousAssociate;
    String currentPages, previousPages;
    int rowCount = 0;

    System.out.println(row);
    for (int i = 2; i <= lastRow; i++) {
        currentAssociate = sheet.getRow(i).getCell(1).toString();
        currentLawName = sheet.getRow(i).getCell(3).toString();
        currentCountry = sheet.getRow(i).getCell(4).toString();
        currentPages = sheet.getRow(i).getCell(5).toString();

        previousAssociate = sheet.getRow(i - 1).getCell(1).toString();
        previousLawName = sheet.getRow(i - 1).getCell(3).toString();
        previousCountry = sheet.getRow(i - 1).getCell(4).toString();
        previousPages = sheet.getRow(i - 1).getCell(5).toString();

        if (currentAssociate.equals(previousAssociate) && currentCountry.equals(previousCountry)
                && currentLawName.equals(previousLawName) && currentPages.equals(previousPages)) {
            rowCount += 1;
            }
        } else {
            shiftRowsUp(sheet, i, rowCount);
            i -= rowCount;
            rowCount = 0;
        }
    }
    shiftRowsUp(sheet, i, rowCount);

    FileOutputStream fileOut = new FileOutputStream("C:\\D\\Sheets\\Quality Sheet\\new.xls");
    workbook.write(fileOut);
    fileOut.close();
}

private void shiftRowsUp(Sheet sheet, int startRow, int rowCount) {
    if (rowCount > 0) {
        int lastRow = sheet.getLastRowNum();
        if (lastRow - startRow < rowCount) {
            for (int i = startRow - rowCount; i < startRow: i++) {
                sheet.removeRow(sheet.getRow(i));
            }
        }
        sheet.shiftRows(startRow, lastRow, -rowCount);
    }
}

}

Note, this looping assumes that there are no gaps in the rows of the spreadsheet.