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

最新推荐文章于 2025-09-11 10:13:00 发布

原创最新推荐文章于 2025-09-11 10:13:00 发布 · 1.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#windows

easyExcel 专栏收录该内容

4 篇文章

订阅专栏

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

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