Creating the border for the merged cells in POI

问题:

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

回答1:

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

回答2:

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.