7

Could anyone explain me how to create the borders for the merged cells using Apache POI?
The Code I'm using is only affecting one cell.

sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
Cell monthCell = subheaderRow.createCell(2);
monthCell.setCellValue(2);
monthCell.setCellStyle(styles.get("month"));



style = wb.createCellStyle();

style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
// style.setFillPattern(CellStyle.SOLID_FOREGROUND);
// style.setFont(monthFont);
styles.put("month", style);
Sam
  • 513
  • 1
  • 11
  • 27
user3511026
  • 159
  • 1
  • 2
  • 17
  • Please see this like i am getting like this https://drive.google.com/file/d/0B0otc2Q4d7GgdFhjdnFrSlVDYlU/edit?usp=sharing – user3511026 May 08 '14 at 05:28
  • 1
    possible duplicate of [add border to merged cells in excel Apache poi java.?](http://stackoverflow.com/questions/13930668/add-border-to-merged-cells-in-excel-apache-poi-java) – Norbert Radyk May 08 '14 at 07:01
  • 1
    If you are using XSSF, this answer will show you how: http://stackoverflow.com/questions/11277281/adding-border-to-a-merged-region-in-poi-xssf-workbook/12708202#12708202 – Wee Shetland May 10 '14 at 19:09

2 Answers2

8

This can be done as below:

public void doMerge(int rowIndex, int columnIndex, int rowSpan, int columnSpan) {
    Cell cell = sheet.getRow(rowIndex).getCell(columnIndex);
    CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, columnIndex, columnIndex
            + columnSpan - 1);

    sheet.addMergedRegion(range);

    RegionUtil.setBorderBottom(cell.getCellStyle().getBorderBottom(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderTop(cell.getCellStyle().getBorderTop(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderLeft(cell.getCellStyle().getBorderLeft(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderRight(cell.getCellStyle().getBorderRight(), range, sheet, sheet.getWorkbook());

    RegionUtil.setBottomBorderColor(cell.getCellStyle().getBottomBorderColor(), range, sheet, sheet.getWorkbook());
    RegionUtil.setTopBorderColor(cell.getCellStyle().getTopBorderColor(), range, sheet, sheet.getWorkbook());
    RegionUtil.setLeftBorderColor(cell.getCellStyle().getLeftBorderColor(), range, sheet, sheet.getWorkbook());
    RegionUtil.setRightBorderColor(cell.getCellStyle().getRightBorderColor(), range, sheet, sheet.getWorkbook());
}
Alan Hay
  • 22,665
  • 4
  • 56
  • 110
1

If you want to add border to all the merged cells, first you got to create a dummy cells for all the rows and columns that are merged(not only for the cells you are using, but for ALL). Then apply the style. Say you want to merge cells from Column 1 to 10 and Rows 0 and 1. Create dummy cells for those rows and columns, and override this to create your cells.

Loop

Row myRow1= sheet.createRow((short) 0);
Row myRow2 = sheet.createRow((short) 1);
for (int i = 1; i <= 10; ++i) 
     {
            Cell blankCell1 = myRow1.createCell(i);
            blankCell1.setCellStyle(style);
            Cell blankCell2 = myRow2.createCell(i);
            blankCell2.setCellStyle(style);
     }

For Style

HSSFCellStyle style= wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);

Creating WorkBook

HSSFWorkbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Name of the Excel");

For Merging

sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 10)); //(fromRow, toRow, fromColumn,toColumn)

Hope this helps.

Sam
  • 513
  • 1
  • 11
  • 27