easyExcel 填充写时，动态合并单元格

本文主要是介绍easyExcel 填充写时，动态合并单元格，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、定义合并单元格策略获取方法

/** * @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;}

解释： 需求：根据合同编码判断是否要合并，相同合同合并单元格

1、根据待写入数据记录的集合list， 获取合同编号集合

2、取当前记录及其前一条记录，判断当前记录的合同号  = 前一条记录合同号 ？

如果不相等，则当然前记录就是待合并单元格的起始行

3、取当前记录及其后一条记录，判断当前记录的合同号 = 后一条记录合同号 ？ 

如果不相等，则当前记录就是待合并单元格的结束行

4、起始结尾行号确定了，需要确定起始结尾的列号，由于每列内容不一样，所以合同属性涉及的列号，都要一一合并，（这里涉及的列号 List<Integer> mergeColIndexList=Arrays.asList(0,1,2,3,4,5,6,13,14,15);）所以这里把涉及的列号定义了一个集合，然后其中的每一列都按照 起始行、结束行去合并。

2、写入时将合并策略注册到ExcelWriterSheetBuilder

/*** @param project* @description: 导出项目* @return: void* @author 30864* @date: 2024/8/1 9:05*/@Overridepublic 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();}}

解释: 这里涉及三个sheet的写入，只需关注 excelWriterSheetBuilder2 即可

这篇关于easyExcel 填充写时，动态合并单元格的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---