Add border to merged cells in excel Apache poi jav

问题:

I'm using Apache POI and I need to put a border in a range of cells or merged region. I am merging the cells with three rows and five columns. But I am not able to add the border to it. So how do I do this?

回答1:

My solution was to merge the cells by their positions, then created a cell (reference to the first block of the merged cells) to assign a value and then set the border throught the HSSFRegionUtil

// Merges the cells
CellRangeAddress cellRangeAddress = new CellRangeAddress(start, start, j, j + 1);
sheet.addMergedRegion(cellRangeAddress);

// Creates the cell
Cell cell = CellUtil.createCell(row, j, entry.getKey());

// Sets the borders to the merged cell
HSSFRegionUtil.setBorderTop(CellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
HSSFRegionUtil.setBorderLeft(CellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
HSSFRegionUtil.setBorderRight(CellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
HSSFRegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);

回答2:

You will find the RegionUtil class useful for settings the borders for a range of cells.Look here:

http://poi.apache.org/apidocs/index.html

回答3:

First of all it would be nice to know which sheet format you are trying to create. Because when you merge null cells in HSSF it is totally normal while XSSF creates malformed file that cause errors while opening it in Microsoft EXCEL. Styles tend to behave equally in both cases. You need to assign same style (in your case style that includes the border) to each cell you are merging. My suggestion create a function that checks and corrects, sets style to all cells in the merging region. Here is example of my own:

private static final XSSFColor COLOR_ORANGE         = new XSSFColor(new java.awt.Color(254, 253, 189));
private static final XSSFColor COLOR_GREY           = new XSSFColor(new java.awt.Color(191, 190, 154));

. . .

        XSSFCellStyle styleSubHeader = (XSSFCellStyle) wb.createCellStyle();
        styleSubHeader.setFont(fontBold);
        styleSubHeader.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleSubHeader.setAlignment(CellStyle.ALIGN_CENTER);
        styleSubHeader.setFillForegroundColor(COLOR_ORANGE);
        styleSubHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleSubHeader.setBorderBottom(CellStyle.BORDER_THIN);
        styleSubHeader.setBottomBorderColor(COLOR_GREY);
        styleSubHeader.setBorderLeft(CellStyle.BORDER_THIN);
        styleSubHeader.setLeftBorderColor(COLOR_GREY);
        styleSubHeader.setBorderRight(CellStyle.BORDER_THIN);
        styleSubHeader.setRightBorderColor(COLOR_GREY);
        styleSubHeader.setBorderTop(CellStyle.BORDER_THIN);
        styleSubHeader.setTopBorderColor(COLOR_GREY);

. . .

 /**
 * Checking if every row and cell in merging region exists, and create those which are not    
 * @param sheet in which check is performed
 * @param region to check
 * @param cellStyle cell style to apply for whole region
 */
private void cleanBeforeMergeOnValidCells(XSSFSheet sheet,CellRangeAddress region, XSSFCellStyle cellStyle )
{
    for(int rowNum =region.getFirstRow();rowNum<=region.getLastRow();rowNum++){
        XSSFRow row= sheet.getRow(rowNum);
        if(row==null){
            sheet.createRow(rowNum);
            logger.trace("while check row "+rowNum+" was created");
        }
        for(int colNum=region.getFirstColumn();colNum<=region.getLastColumn();colNum++){
            XSSFCell currentCell = row.getCell(colNum); 
           if(currentCell==null){
               currentCell = row.createCell(colNum);
               logger.trace("while check cell "+rowNum+":"+colNum+" was created");
           }    

           currentCell.setCellStyle(cellStyle);

        }
    }


}

And finally you call it before actual merge call something like this:

CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, mergeStart, cellIndex+cellOffset);
cleanBeforeMergeOnValidCells(row.getSheet(),region,styleSubHeader );
row.getSheet().addMergedRegion(region);// merging cells that has a title name

Hope it helps.

回答4:

I recommend you use getMergedRegions, that method will return the list of merged regions in the sheet. Then you can iterate each region to apply borders. For example:

private void setBordersToMergedCells(Workbook workBook, Sheet sheet) {
  List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
  for (CellRangeAddress rangeAddress : mergedRegions) {
    RegionUtil.setBorderTop(CellStyle.BORDER_THIN, rangeAddress, sheet, workBook);
    RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, rangeAddress, sheet, workBook);
    RegionUtil.setBorderRight(CellStyle.BORDER_THIN, rangeAddress, sheet, workBook);
    RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, rangeAddress, sheet, workBook);
  }
}

then, you can call to this method when you did all the merged that you need in mySheet.

回答5:

Use:

int rownumm=0;
rownumm++;

Row row = sheet.createRow(rownumm);
Cell cell = row.createCell(0);
cell.setCellValue(web.getUrl());
cell.setCellStyle(styles.get("font"));//font for text

CellRangeAddress region = CellRangeAddress.valueOf("$A$"+ (rownumm) + ":$E$+" + (rownumm));
frame(region, sheet, wb); 

And use method:

private static void frame(CellRangeAddress region,Sheet sheet, Workbook wb){
    sheet.addMergedRegion(region);

    final short borderMediumDashed = CellStyle.BORDER_MEDIUM;
    RegionUtil.setBorderBottom(borderMediumDashed, region, sheet, wb);
    RegionUtil.setBorderTop(borderMediumDashed, region, sheet, wb);
    RegionUtil.setBorderLeft(borderMediumDashed, region, sheet, wb);
    RegionUtil.setBorderRight(borderMediumDashed, region, sheet, wb);
}

See also "Using the Convenience Functions" on link:

https://poi.apache.org/spreadsheet/quick-guide.html#FooterPageNumbers

回答6:

    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());