CODEWITHSUNDEEP
javaapache-poi
Rakesh
Rakesh

Reputation: 594

Unable to understand how to merge Rows in POI

I'm writing a program where I need to merge rows in Excel sheet. Currently, I'm able to merge the starting rows, but when coming to the end, I'm unable to know where it is going wrong. 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) throws IOException {
        FileInputStream fin = new FileInputStream(
                new File("C:\\D\\Sheets\\Sample Sheets\\dummy.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, tempNum = 0;
        System.out.println(row);
        for (int i = 2; i < (row - 1); i++) {
            currentAssociate = sheet.getRow(i).getCell(0).toString();
            currentLawName = sheet.getRow(i).getCell(1).toString();
            currentCountry = sheet.getRow(i).getCell(2).toString();
            currentPages = sheet.getRow(i).getCell(3).toString();

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

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

            }

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

    private static void sendRangeToMergeCells(int startIndex, int finalIndex, HSSFSheet sheet) {
        System.out.println(startIndex + "\t" + (startIndex + finalIndex));
        CellRangeAddress region = CellRangeAddress
                .valueOf("D" + (startIndex + 1) + ":D" + ((startIndex + finalIndex) + 1));
        sheet.addMergedRegion(region);
    }

}

Below is my Excel Sheet

SourceExcel:

enter image description here

Current output:

enter image description here

Expected output:

enter image description here

Upvotes: 1

Views: 1506

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11493

Actually your merge logic is fine, it is your break logic (the logic which determines when to merge the rows) that is missing a bit.

If the last row in your spreadsheet matches the previous row, no merge will be performed because execution will follow the first branch of if (currentAssociate.equals(previousAssociate) && ... and the loop ends. You have to test for and execute the merge logic if necessary one last time after the for loop completes.

Add the following after your for loop:

if (finalIndex > 0) {
    sendRangeToMergeCells(startIndex, finalIndex, sheet);
}

this will merge the last rows if necessary.

Upvotes: 4

Related Questions