easyExcel 填充写时，动态合并单位格 - ToB企服应用市场:ToB评测及商务社交产业平台

/**
* @description: 获取第二个表格的合并策略
* @param secondTablelist
* @return: java.util.List<com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy>
* @author 30864
* @date: 2024/8/24 20:27
*/
public static List<OnceAbsoluteMergeStrategy> getSecondMergeStrategy(List<ProjectContractExpenditureStatisticDto> secondTablelist) {
int baseRowIndex = 3;
//合并开始行
int startRowIndex = 0;
//合并结束行
int endRowIndex = 0;
//合并开始列
int colStartIndex = 0;
//合并结束列
int colEndIndex = 0;
List<OnceAbsoluteMergeStrategy> strategyList = new ArrayList<>();
// 取出string列的数据
List<String> contractNoList = secondTablelist.stream().map(ProjectContractExpenditureStatisticDto::getContractNo).collect(Collectors.toList());
// 前后添加两个不计入合并的元素 , 类似链表中哨兵，简化索引越界的判断
contractNoList.add(0, "开始节点不计入合并");
contractNoList.add(contractNoList.size(), "结束节点不计入合并");
List<Integer> mergeColIndexList = Arrays.asList(0,1,2,3,4,5,6,13,14,15);
for (int index = 1; index < contractNoList.size() - 1; index++) {
// 当前的元素和数组中前一个元素是否相等
boolean equalsPrevious = Objects.equals(contractNoList.get(index), contractNoList.get(index - 1));
// 当前的元素和数组中后一个元素是否相等
boolean equalsAfter = Objects.equals(contractNoList.get(index), contractNoList.get(index + 1));
// 如果找到一个元素和不等于前面，但是等于后面，则这个元素的索引就是一个OnceAbsoluteMergeStrategy的开始行
if (!equalsPrevious && equalsAfter) {
startRowIndex = index;
// 如果找到一个元素和等于前面，但是不等于后面，则这个元素的索引就是一个OnceAbsoluteMergeStrategy的结束结束行
} else if (equalsPrevious && !equalsAfter) {
endRowIndex = index;
// 无合同号，不进行合并
if (StringUtils.equals(secondTablelist.get(startRowIndex).getContractName(), Constansts.NOT_HAS_CONTRACT_CONTENT) ){
continue;
}
// 创建单个合并策略，并添加到策略列表里面去
for (int colIndex : mergeColIndexList) {
OnceAbsoluteMergeStrategy strategy = new OnceAbsoluteMergeStrategy(baseRowIndex + startRowIndex, baseRowIndex + endRowIndex, colIndex, colIndex);
strategyList.add(strategy);
}
}
}
return strategyList;
}

复制代码

/**
* @param project
* @description: 导出项目
* @return: void
* @author 30864
* @date: 2024/8/1 9:05
*/
@Override
public void export(Project project, HttpServletResponse response) throws IOException {
Project dbProject = projectMapper.selectProjectByNo(project.getProjectNo());
String fileName = String.format("%s【%s】报告", dbProject.getProjectName(), dbProject.getProjectNo()) + System.currentTimeMillis() + ".xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
OutputStream os = response.getOutputStream();
String templateFileName = "template/template-project-report-new.xlsx";
InputStream templateFileIos = getClass().getClassLoader().getResourceAsStream(templateFileName);
if (null == templateFileIos) {
throw new ServiceException("找不到模板文件");
}
try (ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(templateFileIos).build()) {
ExcelWriterSheetBuilder excelWriterSheetBuilder1 = EasyExcel.writerSheet("项目执行情况-汇总");
ExcelWriterSheetBuilder excelWriterSheetBuilder2 = EasyExcel.writerSheet("项目执行情况-明细");
ExcelWriterSheetBuilder excelWriterSheetBuilder3 = EasyExcel.writerSheet("项目执行情况-审定数据");
StatisticFirstTableDataDto firstTableDataDto = firstTableData(project);
StatisticSecondTableDataDto secondTableDataDto = secondTableData(project);
StatisticThirdTableDataDto thirdTableDataDto = thirdTableData(project);
// 第二个表格添加合并单元格策略
List<OnceAbsoluteMergeStrategy> secondMergeStrategyList = getSecondMergeStrategy(secondTableDataDto.getExpenditureList());
for (OnceAbsoluteMergeStrategy secondMergeStrategy : secondMergeStrategyList ) {
excelWriterSheetBuilder2.registerWriteHandler(secondMergeStrategy);
}
// 第三个表格添加合并单元格策略
List<OnceAbsoluteMergeStrategy> thirdMergeStrategyList = getThirdMergeStrategy(thirdTableDataDto.getAuditList());
for (OnceAbsoluteMergeStrategy thirdMergeStrategy : thirdMergeStrategyList ) {
excelWriterSheetBuilder3.registerWriteHandler(thirdMergeStrategy);
}
WriteSheet writeSheet1 = excelWriterSheetBuilder1.build();
WriteSheet writeSheet2 = excelWriterSheetBuilder2.build();
WriteSheet writeSheet3 = excelWriterSheetBuilder3.build();
/** 这里注意入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行都会创建一行，然后下面的数据往后移动。
* 默认是false，会直接使用下一行，如果没有则创建。 forceNewRow 如果设置了true,有个缺点就是他会把所有的数据都放到内存了，所以慎用 */
// 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 totalData，warnList 然后多个list必须用 FillWrapper包裹
// HORIZONTAL 横向填充， VERTICAL 纵向填充
// 第一个表格合同维度列表统计
FillConfig contractListConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();
excelWriter.fill(new FillWrapper("firstTableData", firstTableDataDto.getContractList()), contractListConfig, writeSheet1);
// 第二个表格支出维度，列表统计
FillConfig secordTableConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();
excelWriter.fill(new FillWrapper("secondTableData", secondTableDataDto.getExpenditureList()), secordTableConfig, writeSheet2);
// 第三个表格概算类型维度，列表统计
FillConfig thirdTableConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();
excelWriter.fill(new FillWrapper("thirdTableData", thirdTableDataDto.getAuditList()), thirdTableConfig, writeSheet3);
// 填充其他非列表数据
Map<String, Object> data = new HashMap<>();
// 项目名称
data.put("projectName", dbProject.getProjectName());
data.put("projectNo", dbProject.getProjectNo());
// 第一个表格总计
data.put("firstTableTotal_totalPayMoney", firstTableDataDto.getTotalPayMoney());
data.put("firstTableTotal_totalUnpaidAmount", firstTableDataDto.getTotalUnpaidAmount());
// 第二个表格总计
data.put("secondTableTotal_totalPayMoney", secondTableDataDto.getTotalPayMoney());
data.put("secondTableTotal_unpaidAmount", secondTableDataDto.getUnpaidAmount());
// 第三个表格总计
data.put("thirdTableTotal_totalPayMoney", thirdTableDataDto.getTotalPayMoney());
data.put("thirdTableTotal_unpaidAmount", thirdTableDataDto.getUnpaidAmount());
data.put("thirdTableTotal_estimateAmount", thirdTableDataDto.getEstimateAmount());
excelWriter.fill(data, writeSheet1);
excelWriter.fill(data, writeSheet2);
excelWriter.fill(data, writeSheet3);
excelWriter.finish();
}
}

复制代码