Adding a border to a federated region in an XSSF POI tutorial

Question

I am using apache poi 3.7 and I need to put the border in a range of cells or a merged area.

how can i apply the border to the merged area when the sheet and workbook type is xssf. In the HSSF type, I use RegionUtil- / HSSFRegionutil, but if I use the first object (Regionutil) in the XSSF type, it does not work and puts the black background color in the cell range.

Regionutil usually works with CellRangeAddress, and I can not find information about this problem. I do not know if CellRangeAddres calls this.

Answer 1

To do this, you need to add an empty cell to each cell of the merged area, and then add the corresponding borders to each cell. For example, the following code will create a merged area of ​​5 cells on the same line, with a border around the entire merged area and text centered on the area.

XSSFWorkbook wb = new XSSFWorkbook(); CellStyle borderStyle = wb.createCellStyle(); borderStyle.setBorderBottom(CellStyle.BORDER_THIN); borderStyle.setBorderLeft(CellStyle.BORDER_THIN); borderStyle.setBorderRight(CellStyle.BORDER_THIN); borderStyle.setBorderTop(CellStyle.BORDER_THIN); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); Sheet sheet = wb.createSheet("Test Sheet"); Row row = sheet.createRow(1); for (int i = 1; i <= 5; ++i) { Cell cell = row.createCell(i); cell.setCellStyle(borderStyle); if (i == 1) { cell.setCellValue("Centred Text"); } } sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5)); 

Answer 2

Do this for a few lines.

 Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet sheet = wb.createSheet(); CellStyle borderStyle = wb.createCellStyle(); borderStyle.setBorderBottom(CellStyle.BORDER_THIN); borderStyle.setBorderLeft(CellStyle.BORDER_THIN); borderStyle.setBorderRight(CellStyle.BORDER_THIN); borderStyle.setBorderTop(CellStyle.BORDER_THIN); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); Sheet sheet1 = wb.createSheet("Test Sheet"); Row row = null; Cell cell; for (int i = 1; i <= 5; ++i) { row = sheet1.createRow(i); for(int j=1;j<=5;j++){ cell= row.createCell(j); cell.setCellStyle(borderStyle); if (i == 1 && j==1) { cell.setCellValue("Centred Text"); } } } sheet1.addMergedRegion(new CellRangeAddress(1, 5, 1, 5)); 

Answer 3

 private void setBordersToMergedCells(XSSFWorkbook workBook, XSSFSheet sheet) { int numMerged = sheet.getNumMergedRegions(); for(int i= 0; i<numMerged;i++){ CellRangeAddress mergedRegions = sheet.getMergedRegion(i); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, mergedRegions, sheet, workBook); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, mergedRegions, sheet, workBook); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, mergedRegions, sheet, workBook); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, mergedRegions, sheet, workBook); } } 

Answer 4

@Jesanagua just saved my life, I just had to swap a bit to match 3.17.

 private void setBordersToMergedCells(HSSFSheet sheet) { int numMerged = sheet.getNumMergedRegions(); for (int i = 0; i < numMerged; i++) { CellRangeAddress mergedRegions = sheet.getMergedRegion(i); RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegions, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegions, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegions, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegions, sheet); } }