Apache Poi 4.1.0合并单元格后，RegionUtil设置边框无效问题解决

野性酷女 2023-10-12 08:02 15阅读 0赞

## 背景 ##

使用Apache Poi做Excel复杂表头导出，涉及表格合并，合并后调用RegionUtil设置边框效果无效。如下所示  
![format_png][]

## 解决 ##

查了一波资料后都是采用RegionUtil。没办法，采用将每个单元格设置样式，包括合并的每个单元格。

// 第二行表头：涉及表头合并
    row = sheet.createRow(1);
    // 增加此部分代码，将合并的，没有填值的也设置边框样式
    int preColNum = 16;
    for (int j = 0; j < preColNum; j++) { 
    	HSSFCell cell = row.createCell(j);
    	cell.setCellStyle(style);
    }
    // 设置有值得单元样式
    for (int i = 0; i < excelHeader1.length; i++) { 
    	HSSFCell cell = row.createCell(i + preColNum);
    	cell.setCellValue(excelHeader1[i]);
    	cell.setCellStyle(style);
    	sheet.autoSizeColumn(i + preColNum, true);// 自动调整宽度
    }
    // 动态合并单元格
    for (int i = 0; i < headnum1.length; i++) { 
    	sheet.autoSizeColumn(i, true);
    	String[] temp = headnum1[i].split(",");
    	Integer startrow = Integer.parseInt(temp[0]);
    	Integer overrow = Integer.parseInt(temp[1]);
    	Integer startcol = Integer.parseInt(temp[2]);
    	Integer overcol = Integer.parseInt(temp[3]);
    	CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
    	sheet.addMergedRegion(cra);
    	// 调用RegionUtil设置无效
    	// this.setRegionBorder(BorderStyle.THIN, cra, sheet);
    }

## 完整代码 ##

整理后，完整代码如下：

public class RoomReserveExportUtil { 
        private void setRegionBorder(BorderStyle border, CellRangeAddress region, Sheet sheet) { 
            RegionUtil.setBorderBottom(border, region, sheet);
            RegionUtil.setBorderLeft(border, region, sheet);
            RegionUtil.setBorderRight(border, region, sheet);
            RegionUtil.setBorderTop(border, region, sheet);
        }
    
        /** * 动态合并单元格 * @param headNum 表头数字，“0,2,0,0” ===> “起始行，截止行，起始列，截止列” * @param sheet */
        private void mergeCell(String[] headNum, Sheet sheet) { 
            // 动态合并单元格
            for (int i = 0; i < headNum.length; i++) { 
                sheet.autoSizeColumn(i, true);
                String[] temp = headNum[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
    
                CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
                sheet.addMergedRegion(cra);
    // this.setRegionBorder(BorderStyle.THIN, cra, sheet);
            }
        }
    
        /** * 设置合并表格，空缺单元格样式 * @param row * @param startNum * @param endNum * @param style */
        private void setEmptyCellStyle(HSSFRow row, int startNum, int endNum, HSSFCellStyle style) { 
            for (int j = startNum; j < endNum; j++) { 
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(style);
            }
        }
    
        public HSSFWorkbook exportGoods(List<RoomReserveExtend> list) { 
            //int titleRow = 6;//表头标题及副标题占6行
            //int tableBody = titleRow+1;//表头开始
            // 声明String数组，并初始化元素（表头名称）
            //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
            String[] excelHeader0 = { 
                    "项目名称",
                    "招标编号",
                    "招标单位",
                    "项目类别",
                    "项目区域",
                    "开标时间",
                    "评标时间",
                    "审核状态",
                    "招标方式",
                    "招标组织形式",
                    "招标代理机构",
                    "出席开标人员姓名(代理机构)",
                    "缴款单位(中标单位)",
                    "中标金额(元)",
                    "中标金额说明",
                    "缴款通知书时间",
                    "缴款金额(元)", "缴款金额(元)", "缴款金额(元)", "缴款金额(元)", "缴款金额(元)",
                    "换票情况", "换票情况"
    
            };
            // “0,2,0,0” ===> “起始行，截止行，起始列，截止列”
            String[] headnum0 = { 
                    "0,2,0,0",
                    "0,2,1,1",
                    "0,2,2,2",
                    "0,2,3,3",
                    "0,2,4,4",
                    "0,2,5,5",
                    "0,2,6,6",
                    "0,2,7,7",
                    "0,2,8,8",
                    "0,2,9,9",
                    "0,2,10,10",
                    "0,2,11,11",
                    "0,2,12,12",
                    "0,2,13,13",
                    "0,2,14,14",
                    "0,2,15,15",
                    "0,0,16,20",
                    "0,0,21,22"
            };
            // 第二行表头字段，其中的空的双引号是为了补全表格边框
            String[] excelHeader1 = { 
                    "合计5=(1+2)",
                    "场地租赁费(1)",
                    "服务费", "服务费", "服务费",
                    "时间",
                    "发票号码"
            };
            // 合并单元格
            String[] headnum1 = { 
                    "1,2,16,16",
                    "1,2,17,17",
                    "1,1,18,20",
                    "1,2,21,21",
                    "1,2,22,22"
            };
    
            // 第三行表头字段
            String[] excelHeader2 = { 
                    "新标准(2)",
                    "旧标准(2)",
                    "减负情况4=(3-2)"
            };
    
    // String[] headnum2 = { 
    // "2,2,18,18",
    // "2,2,19,19",
    // "2,2,20,20"
    // };
    
            // 声明一个工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = wb.createSheet("项目导出结果");
    
            // 生成一种样式style
            HSSFCellStyle style = wb.createCellStyle();
            // 设置样式
            style.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
    
            // 生成一种字体
            HSSFFont font = wb.createFont();
            // 设置字体
            font.setFontName("微软雅黑");
            // 设置字体大小
            font.setFontHeightInPoints((short) 12);
            // 在样式中引用这种字体
            style.setFont(font);
    
    // // 生成标题样式style1
    // HSSFCellStyle style1 = wb.createCellStyle();
    // // 设置样式
    // style1.setAlignment(HorizontalAlignment.CENTER);
    // style1.setVerticalAlignment(VerticalAlignment.CENTER);
    //
    // // 生成标题字体1
    // HSSFFont font1 = wb.createFont();
    // // 设置字体
    // font1.setFontName("微软雅黑");
    // // 设置字体大小
    // font1.setFontHeightInPoints((short) 25);
    // // 字体加粗
    // font1.setBold(true);
    // // 在样式中引用这种字体
    // style1.setFont(font1);
    
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < excelHeader0.length; i++) { 
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader0[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            }
            // 动态合并单元格
            this.mergeCell(headnum0, sheet);
    
            // 第二行表头
            row = sheet.createRow(1);
            int preColNum = 16;
            // 设置合并单元格，空缺单元格样式
            this.setEmptyCellStyle(row, 0, preColNum, style);
    
            for (int i = 0; i < excelHeader1.length; i++) { 
                HSSFCell cell = row.createCell(i + preColNum);
                cell.setCellValue(excelHeader1[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i + preColNum, true);// 自动调整宽度
            }
            // 动态合并单元格
            this.mergeCell(headnum1, sheet);
    
            // 第三行表头
            row = sheet.createRow(2);
            preColNum = 18;
            // 设置合并单元格，空缺单元格样式
            this.setEmptyCellStyle(row, 0, preColNum, style);
    
            for (int i = 0; i < excelHeader2.length; i++) { 
                HSSFCell cell = row.createCell(i + preColNum);
                cell.setCellValue(excelHeader2[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i + preColNum, true);// 自动调整宽度
            }
            // 动态合并单元格
    // this.mergeCell(headnum2, sheet);
    
            // 设置合并单元格，空缺单元格样式
            this.setEmptyCellStyle(row, 21, 23, style);
    
            for (int i = 0; i < list.size(); i++) { 
                RoomReserveExtend vo = list.get(i);
                row = sheet.createRow(i + 3);
    
                HSSFCell cell0 = row.createCell(0);
                cell0.setCellValue(vo.getProjectName());
    
                HSSFCell cell1 = row.createCell(1);
                cell1.setCellValue(vo.getTenderCode());
    
                HSSFCell cell2 = row.createCell(2);
                cell2.setCellValue(vo.getTenderUnit());
    
                HSSFCell cell3 = row.createCell(3);
                cell3.setCellValue(vo.getProjectType());
    
                HSSFCell cell4 = row.createCell(4);
                cell4.setCellValue(vo.getArea());
    
                HSSFCell cell5 = row.createCell(5);
                cell5.setCellValue(vo.getApplyOpenTime());
    
                HSSFCell cell6 = row.createCell(6);
                cell6.setCellValue(vo.getEvalTime());
    
                HSSFCell cell7 = row.createCell(7);
                cell7.setCellValue(vo.getManageStatus());
    
                HSSFCell cell8 = row.createCell(8);
                cell8.setCellValue(vo.getTenderType());
    
                HSSFCell cell9 = row.createCell(9);
                cell9.setCellValue(vo.getTenderOrgType());
    
                HSSFCell cell10 = row.createCell(10);
                cell10.setCellValue(vo.getAgencyName());
    
                HSSFCell cell11 = row.createCell(11);
                cell11.setCellValue(vo.getOpenUsersShow());
    
                HSSFCell cell12 = row.createCell(12);
                cell12.setCellValue(vo.getAgencyName());
    
                HSSFCell cell13 = row.createCell(13);
                cell13.setCellValue(String.valueOf(vo.getTradePrice()));
    
                HSSFCell cell14 = row.createCell(14);
                cell14.setCellValue(vo.getTradePriceDesc());
    
                HSSFCell cell15 = row.createCell(15);
                cell15.setCellValue("");
    
                HSSFCell cell16 = row.createCell(16);
                cell16.setCellValue(vo.getAgencyName());
    
                HSSFCell cell17 = row.createCell(17);
                cell17.setCellValue(vo.getAgencyName());
    
                HSSFCell cell18 = row.createCell(18);
                cell18.setCellValue(vo.getAgencyName());
    
                HSSFCell cell19 = row.createCell(19);
                cell19.setCellValue(vo.getAgencyName());
    
                HSSFCell cell20 = row.createCell(20);
                cell20.setCellValue(vo.getAgencyName());
    
                HSSFCell cell21 = row.createCell(21);
                cell21.setCellValue(vo.getAgencyName());
    
                HSSFCell cell22 = row.createCell(22);
                cell22.setCellValue(vo.getAgencyName());
            }
            return wb;
        }
    }

## 最终效果 ##

![format_png 1][]

转载请注明：[溜爸 » Apache Poi 4.1.0合并单元格后，RegionUtil设置边框无效问题解决][_ Apache Poi 4.1.0_RegionUtil]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS85LzIyLzE2ZDU2ZDQxMmVkN2FkMjY?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS85LzIyLzE2ZDU2ZDQxMmVmZGVkM2U?x-oss-process=image/format,png
[_ Apache Poi 4.1.0_RegionUtil]: http://silianpan.cn/index.php/2019/09/22/apache_poi_regionutil_border_type