How to merge cells and set value at the same time by using Apache POI?

Question

I want to merge some cells in my excel template file, and then set values in merged cells. Here is my java code. Before I add set value part, it works well on merging cells. But when I just add set value part, it seems like nothing changed even no cells merged. I also try to move set value part after merge cells part, then office remind me repair the output excel file.

What should I do to merge cells and set value at the same time?

for (int sht = 0; sht < 3; sht++) {
    sheet = workbook.getSheetAt(sht);
    row = 1;
    for (Data d : list) {
        // set value part
        cell = sheet.getRow(row).getCell(0);
        cell.setCellValue(d.a);
        cell = sheet.getRow(row).getCell(1);
        cell.setCellValue(d.b);
        cell = sheet.getRow(row).getCell(2);
        cell.setCellValue(d.c);
        // merge cells part
        sheet.addMergedRegion(new CellRangeAddress(row, row + 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(row, row + 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(row, row + 1, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(row, row + 1, 3, 3));
        //
        row += 2;
    }
}

Solution

You can setCellValue in excel cell and then merge the cells according to your requirements.

cell = sheet.getRow(row).getCell(0);
cell.setCellValue(d.a);

You can use sheet.addMergedRegion(new CellRangeAddress(rowFrom,rowTo,colFrom,colTo));

example :

sheet.addMergedRegion(new CellRangeAddress(1,1,4,2)); will merge from D2 to F2. 

Remember it is zero based indexing.

for detail refer BusyDeveloper's Guide