CODEWITHSUNDEEP
javaandroidexcelapache-poi
androidDev
androidDev

Reputation: 1227

Merging cells in Excel using Apache POI

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

Upvotes: 84

Views: 204844

Answers (6)

abhay kumar
abhay kumar

Reputation: 1

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

Upvotes: 0

Jesús Sánchez
Jesús Sánchez

Reputation: 751

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

Upvotes: 7

Aishi Mitra
Aishi Mitra

Reputation: 1

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.

Upvotes: 0

Suchita Mukherjee
Suchita Mukherjee

Reputation: 871

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

Upvotes: 16

Sankumarsingh
Sankumarsingh

Reputation: 10089

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

Upvotes: 181

Vishwanath Kalaje
Vishwanath Kalaje

Reputation: 75

The best answer

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

Upvotes: 5

Related Questions