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
– Gagravarr, Commented Sep 10, 2013 at 10:54

Stefano · Accepted Answer · 2018-05-21 06:38:27Z

182

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, 2018 at 6:38

Stefano

33 bronze badges

answered Sep 10, 2013 at 11:04

Sankumarsingh

10.1k11 gold badges54 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

golimar Over a year ago

if after this, Excel says it has to repair the file, make sure you're not overlapping cells when merging

James Kleeh Over a year ago

@golimar What do you mean by overlapping cells?

golimar Over a year ago

@JamesKleeh For example, creating one merged region A1+A2 and another merged region A2+A3

gordon613 Over a year ago

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 stackoverflow.com/questions/22267679/…

Suchita Mukherjee · Accepted Answer · 2018-07-17 14:51:50Z

16

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));

answered Jul 17, 2018 at 14:51

Suchita Mukherjee

8817 silver badges6 bronze badges

1 Comment

Saideep Ullal Over a year ago

The most wholesome answer. Also explains general guidelines for merging.

Jesús Sánchez · Accepted Answer · 2021-05-25 14:12:05Z

7

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);
}

edited May 25, 2021 at 14:12

answered Feb 5, 2018 at 23:45

Jesús Sánchez

75111 silver badges17 bronze badges

Comments

Vishwanath Kalaje · Accepted Answer · 2017-07-19 13:00:00Z

5

The best answer

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

edited Jul 19, 2017 at 13:00

user3956566

answered Jul 19, 2017 at 12:40

Vishwanath Kalaje

751 silver badge4 bronze badges

1 Comment

Reinderien Over a year ago

"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.

Jayesh Babu · Accepted Answer · 2019-12-02 07:46:03Z

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 2, 2019 at 7:46

Jayesh Babu

1,4492 gold badges21 silver badges39 bronze badges

answered Dec 2, 2019 at 6:22

Aishi Mitra

1

Comments

RamPrakash · Accepted Answer · 2025-01-22 09:52:15Z

0

The answers of sheet.addMergedRegion(r1, r2, c1, c2) ends up creating a merged cell but it does not give you center across the selection result

edited Jan 22 at 9:52

RamPrakash

1,8403 gold badges21 silver badges28 bronze badges

answered Jan 21 at 4:45

abhay kumar

12 bronze badges

Collectives™ on Stack Overflow

Merging cells in Excel using Apache POI

6 Answers 6

4 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related