背景
使用Apache Poi做Excel复杂表头导出，涉及表格合并，合并后调用RegionUtil设置边框效果无效。如下所示
解决
查了一波资料后都是采用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;
}
}
最终效果
评论区