Merging cells in Excel using Apache POI

Question

Is there any other way to merge cells in Excel using Apache POI library?

I was trying using the following, but its not working

// selecting the region in Worksheet for merging data
CellRangeAddress region = CellRangeAddress.valueOf("A" + rowNo + ":D"
            + rowNo);

// merging the region
sheet1.addMergedRegion(region);

What do you mean by "not working"? Throws an error? Upsets Excel? Merges a different region? Sits around all day watching tv? ;-) — Gagravarr, Sep 10 '13 at 10:54

score 179 · Accepted Answer · edited May 21 '18 at 06:38

179

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

example sheet.addMergedRegion(new CellRangeAddress(1,1,1,4)); will merge from B2 to E2. Remember it is zero based indexing (ex. POI version 3.12).

for detail refer BusyDeveloper's Guide

edited May 21 '18 at 06:38

Stefano

3
3

answered Sep 10 '13 at 11:04

Sankumarsingh

9,889
11
50
74

12

if after this, Excel says it has to repair the file, make sure you're not overlapping cells when merging – golimar Mar 11 '16 at 17:35
@golimar What do you mean by overlapping cells? – James Kleeh Oct 05 '16 at 16:11
@JamesKleeh For example, creating one merged region A1+A2 and another merged region A2+A3 – golimar Oct 05 '16 at 21:14
2

Note that there are two versions of CellRangeAddress: the one to use is `org.apache.poi.ss.util.CellRangeAddress` whereas the other one `org.apache.poi.hssf.util.CellRangeAddress` is now deprecated. See https://stackoverflow.com/questions/22267679/what-to-use-instead-of-deprecated-cellrangeaddress-valueof-in-apachepoi – gordon613 Nov 17 '19 at 12:38

score 12 · Answer 2 · answered Jul 17 '18 at 14:51

You can use :

sheet.addMergedRegion(new CellRangeAddress(startRowIndx, endRowIndx, startColIndx,endColIndx));

Make sure the CellRangeAddress does not coincide with other merged regions as that will throw an exception.

If you want to merge cells one above another, keep column indexes same
If you want to merge cells which are in a single row, keep the row indexes same
Indexes are zero based

For what you were trying to do this should work:

sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 3));

The most wholesome answer. Also explains general guidelines for merging. — Saideep Ullal, Apr 29 '21 at 09:35

Jesús Sánchez · Answer 3 · 2021-05-25T14:12:05.987

I create a method that merge cells and put border if you want.

protected void setMerge(Sheet sheet, int numRow, int untilRow, int numCol, int untilCol, boolean border) {
    CellRangeAddress cellMerge = new CellRangeAddress(numRow, untilRow, numCol, untilCol);
    sheet.addMergedRegion(cellMerge);
    if (border) {
        setBordersToMergedCells(sheet, cellMerge);
    }

}  

protected void setBordersToMergedCells(Sheet sheet, CellRangeAddress rangeAddress) {
    RegionUtil.setBorderTop(BorderStyle.MEDIUM, rangeAddress, sheet);
    RegionUtil.setBorderLeft(BorderStyle.MEDIUM, rangeAddress, sheet);
    RegionUtil.setBorderRight(BorderStyle.MEDIUM, rangeAddress, sheet);
    RegionUtil.setBorderBottom(BorderStyle.MEDIUM, rangeAddress, sheet);
}

score 5 · Answer 4 · edited Jul 19 '17 at 13:00

5

The best answer

sheet.addMergedRegion(new CellRangeAddress(start-col,end-col,start-cell,end-cell));

edited Jul 19 '17 at 13:00

answered Jul 19 '17 at 12:40

Vishwanath Kalaje

75
1
4

4

"The best answer" probably shouldn't include minus signs in variables. Also, the variables are outright wrong: columns are in the third and fourth position, not the first and second. – Reinderien Aug 11 '19 at 23:02

score 0 · Answer 5 · edited Dec 02 '19 at 07:46

0

syntax is:

sheet.addMergedRegion(new CellRangeAddress(start-col,end-col,start-cell,end-cell));

Example:

sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 5));

Here the cell 0 to cell 5 will be merged of the 4th row.

edited Dec 02 '19 at 07:46

Jayesh Babu

1,389
2
20
34

answered Dec 02 '19 at 06:22

Aishi Mitra

1

Merging cells in Excel using Apache POI

5 Answers5

Linked

Related