Merging cells in Excel using Apache POI
168,320
Solution 1
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
Solution 2
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));
Solution 3
The best answer
sheet.addMergedRegion(new CellRangeAddress(start-col,end-col,start-cell,end-cell));
Solution 4
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);
}
Author by
androidDev
Updated on July 05, 2022Comments
-
androidDev almost 2 years
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);
-
golimar over 8 yearsif after this, Excel says it has to repair the file, make sure you're not overlapping cells when merging
-
James Kleeh over 7 years@golimar What do you mean by overlapping cells?
-
golimar over 7 years@JamesKleeh For example, creating one merged region A1+A2 and another merged region A2+A3
-
Reinderien almost 5 years"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.
-
gordon613 over 4 yearsNote that there are two versions of CellRangeAddress: the one to use is
org.apache.poi.ss.util.CellRangeAddress
whereas the other oneorg.apache.poi.hssf.util.CellRangeAddress
is now deprecated. See stackoverflow.com/questions/22267679/… -
Saideep Ullal about 3 yearsThe most wholesome answer. Also explains general guidelines for merging.