EasyExce复杂场景应用（动态合并标题,填充导出联合使用）

已于 2022-06-24 11:31:40 修改
阅读量1.1k 收藏 6
点赞数
分类专栏： easyexcle 文章标签： java 开发语言 阿里云
于 2022-06-24 11:28:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JavaSwx/article/details/125441293
版权

EasyExce复杂场景应用（动态合并标题,填充导出联合使用）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言

提示：这里可以添加本文要记录的大概内容：

提示：以下是本篇文章正文内容，下面案例可供参考

一、EasyExcel是什么？

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址: https://github.com/alibaba/easyexcel

官方文档: https://www.yuque.com/easyexcel/doc/easyexcel

B站视频: https://www.bilibili.com/video/BV1Ff4y1U7Qc

二、使用步骤

1.引入库

代码如下（示例）：

	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>${easyexcel.version}</version>
     </dependency>

2.组装填充数据

代码如下（示例）：

 List<EvaluateOpinionVo> evaluateOpinionVoList = empEvaluateDetailBizService
                        .getEvaluateCapacityVoList(evaluateInstancePo, queryDto, t.getTargetUserId());
                if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) {

                    String finalName = evaluateOpinionVoList.stream().map(EvaluateOpinionVo::getEvaluateUserName)
                            .collect(Collectors.joining("\n"));
                    String finalRoleName = t.getEmpRoleName() + "\n";
                    String finalOrgName = t.getEmpOrgName() + "\n";
                    paramMap.put("finalName", finalName);
                    paramMap.put("finalId", Joiner.on("\n")
                            .join(evaluateOpinionVoList.stream().map(EvaluateOpinionVo::getEvaluateUserId)
                                    .collect(Collectors.toList())));
                    paramMap.put("finalOrgName", finalOrgName);
                    paramMap.put("finalRoleName", finalRoleName);
                }
                paramMap.put("title", evaluateInstancePo.getEvaluateName());
                paramMap.put("targetName", t.getTargetUserName());
                paramMap.put("targetId", String.valueOf(t.getTargetUserId()));
                if (ExamineEnum.AbilityModeCode.LEAD.getCode().equals(templateInstancePo.getAbilityMode())) {
                    paramMap.put("targetOrgName", t.getOrgName());
                    paramMap.put("targetRoleName", t.getRoleName());
                } else {
                    paramMap.put("targetOrgName", t.getEmpOrgName());
                    paramMap.put("targetRoleName", t.getEmpRoleName());
                }

该处模拟数据查找的数据，不用纠结代码。

3.填充数据

代码如下（示例）：

 WriteSheet sheet = EasyExcel
                        .writerSheet(t.getEvaluateInstanceId() + t.getTargetUserName() + t.getTargetUserId())
                        .registerWriteHandler(
                                new ExcelFillCellMergeStrategy(mergeColumnIndex, mergeRowIndex, standardColumnIndex))
                        .build();

我这边业务场景比较复杂。填充多个模板就预先定义了多个空白目标，sheet名称按规则命名

  ByteArrayOutputStream bos = new ByteArrayOutputStream();
        //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
        XSSFWorkbook workbook = new XSSFWorkbook(in);

        if (CollectionUtils.isNotEmpty(sheetName)) {
            // workbook.setSheetName(0, sheetName.get(0));
            //复制空白sheet
            for (int j = 0; j < sheetName.size(); j++) {
                workbook.createSheet(sheetName.get(j));
            }
        }

        if (CollectionUtils.isNotEmpty(targetPos)) {
            //设置模板的第一个sheet的名称
            workbook.setSheetName(0, "sheet1");

            for (int i = 0; i < targetPos.size() - 1; i++) {
                //复制模板，得到第i个sheet
                workbook.cloneSheet(0,
                        targetPos.get(i).getEvaluateInstanceId() + targetPos.get(i).getTargetUserName() + targetPos
                                .get(i).getTargetUserId());
            }
            //克隆sheet小于1就不用操作
            if (targetPos.size() > 1) {
                workbook.removeSheetAt(0);
                workbook.cloneSheet(sheetName.size(),
                        targetPos.get(targetPos.size() - 1).getEvaluateInstanceId() + targetPos
                                .get(targetPos.size() - 1).getTargetUserName() + targetPos.get(targetPos.size() - 1)
                                .getTargetUserId());

            }
            if (targetPos.size() == 1) {
                workbook.cloneSheet(0, targetPos.get(targetPos.size() - 1).getEvaluateInstanceId() + targetPos
                        .get(targetPos.size() - 1).getTargetUserName() + targetPos.get(targetPos.size() - 1)
                        .getTargetUserId());
                workbook.removeSheetAt(0);

            }
        }

填充多个模板需要预先定义sheet不然会报错哦

4.拦截器（实现动态合并重点）

代码如下（示例）：

 public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private int[] mergeColumnIndex;

    private int mergeRowIndex;

    private int standardColumnIndex;

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();

        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);

        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 获取上一行第0列单元格和本行第0列单元格

        Cell preRowFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(standardColumnIndex);

        Object preRowFirstData = preRowFirstCell.getCellTypeEnum() == CellType.STRING ? preRowFirstCell.getStringCellValue() : preRowFirstCell.getNumericCellValue();

        Cell curRowFirstCell = cell.getSheet().getRow(curRowIndex).getCell(standardColumnIndex);

        Object curRowFirstData = curRowFirstCell.getCellTypeEnum() == CellType.STRING ? curRowFirstCell.getStringCellValue() : curRowFirstCell.getNumericCellValue();

        // 当前列不为标杆列，根据标杆列上下两个单元格值是否一致来合并

        // 当前列为标杆列并且上下两个单元格一致则合并

        if ((curColIndex == standardColumnIndex && preData.equals(curData)) || (curColIndex != standardColumnIndex && preRowFirstData.equals(curRowFirstData) && preData.equals(curData)) && preData != "") {

            Sheet sheet = writeSheetHolder.getSheet();

            List mergeRegions = sheet.getMergedRegions();

            boolean isMerged = false;

            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {

                CellRangeAddress cellRangeAddr = (CellRangeAddress) mergeRegions.get(i);

                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元

                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {

                    sheet.removeMergedRegion(i);

                    cellRangeAddr.setLastRow(curRowIndex);

                    sheet.addMergedRegion(cellRangeAddr);

                    isMerged = true;

                }

            }

            // 若上一个单元格未被合并，则新增合并单元

            if (!isMerged) {

                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);

                sheet.addMergedRegion(cellRangeAddress);

            }

        }

    }

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int[] mergeColumnIndex, int mergeRowIndex, int standardColumnIndex) {
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
        this.standardColumnIndex = standardColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        Sheet sheet = writeSheetHolder.getSheet();
        Workbook workbook = sheet.getWorkbook();
        // xlsx格式，如果是老版本格式的话就用 HSSFRichTextString
        XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
        if (!StringUtils.isBlank(String.valueOf(richString))) {
            Font font = workbook.createFont();
            if (cell.getStringCellValue().equals("负责人")) {
                font.setBold(true);
                font.setFontHeightInPoints((short) 10);
                richString.applyFont(font);
                // 再设置回每个单元格里
                cell.setCellValue(richString);
                //遍历循环前列同行的数据设置样式
                for (int i = 1; i < curColIndex; i++) {
                    Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - i);

                    XSSFRichTextString str = new XSSFRichTextString(preCell.getStringCellValue());
                    if (StringUtils.isBlank(String.valueOf(str))) {
                        continue;
                    }
                    str.applyFont(font);
                    // 再设置回每个单元格里
                    preCell.setCellValue(str);
                }

            } else if (cell.getStringCellValue().equals("参与人") || cell.getStringCellValue().equals("未参与")) {
                font.setColor(IndexedColors.GREY_50_PERCENT.getIndex());
                richString.applyFont(font);
                // 再设置回每个单元格里
                cell.setCellValue(richString);
                //遍历循环前列同行的数据设置样式
                for (int j = 1; j < curColIndex; j++) {
                    Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - j);
                    XSSFRichTextString str = new XSSFRichTextString(preCell.getStringCellValue());
                    if (StringUtils.isBlank(String.valueOf(str))) {
                        continue;
                    }
                    str.applyFont(font);
                    // 再设置回每个单元格里
                    preCell.setCellValue(str);
                }

            }

        }

        if (mergeColumnIndex == null) {
            return;
        }
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }
    }

就是导出数据前对规则进行一些拦截

5.组织导出数据

代码如下（示例）：

 ArrayList<CarrerEvadataVo> vos = new ArrayList<>();

                //从团队名称开始组装数据
                targetDetailSelfList.forEach(s -> {
                    List<CareerTeamValueVo> carrerEvadata = s.getCarrerEvadata();
                    if (CollectionUtils.isEmpty(carrerEvadata)) {
                        carrerEvadata.addAll(careerTeamValueVos);
                    }
                    for (int i = 0; i < carrerEvadata.size(); i++) {
                        //事项名称
                        CarrerEvadataVo carrerEvadataVo1 = new CarrerEvadataVo();
                        carrerEvadataVo1.setName(carrerEvadata.get(i).getTeamName());
                        carrerEvadataVo1.setAbilt(s.getDimensionName());
                        carrerEvadataVo1.setSelfEvaResult(s.getSelfEvaluateResult());
                        carrerEvadataVo1.setSelfEvaReason(s.getSelfEvaluateComment());

                        if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) {
                            StringBuffer finalEvaResult = new StringBuffer();
                            StringBuffer finalEvaReason = new StringBuffer();
                            evaluateOpinionVoList.forEach(e -> {
                                for (int d = 0; d < e.getResultDetailPoList().size(); d++) {
                                    if (s.getExamineScoreRuleDimensionInstanceId()
                                            .equals(e.getResultDetailPoList().get(d)
                                                    .getExamineScoreRuleDimensionInstanceId())) {
                                        if (evaluateOpinionVoList.size() > 1) {
                                            finalEvaResult
                                                    .append(e.getEvaluateUserName() + ": " + e.getResultDetailPoList()
                                                            .get(d).getEvaluateGrade() + "\n");
                                            finalEvaReason
                                                    .append(e.getEvaluateUserName() + ": " + e.getResultDetailPoList()
                                                            .get(d).getDimensionOpinion() + "\n");
                                        } else {
                                            finalEvaResult
                                                    .append(e.getResultDetailPoList().get(d).getEvaluateGrade() + "\n");
                                            finalEvaReason.append(e.getResultDetailPoList().get(d).getDimensionOpinion()
                                                    + "\n");
                                        }
                                        carrerEvadataVo1.setFinalEvaResult(finalEvaResult.toString());
                                        carrerEvadataVo1.setFinalEvaReason(finalEvaReason.toString());
                                        break;
                                    }


                                }
                            });
                        }

                        vos.add(carrerEvadataVo1);
                        ArrayList<CareerTeamValueTaskVo> newList = new ArrayList<>();
                        Integer count = 0;
                        initData(carrerEvadata.get(i).getTeamValueTaskList(), newList, count);
                        //Collections.reverse(newList);
                        for (int j = 0; j < newList.size(); j++) {
                            CarrerEvadataVo carrerEvadataVo3 = new CarrerEvadataVo();
                            if (newList.get(j).getLevel() == 1) {
                                carrerEvadataVo3.setFirstTaskName(newList.get(j).getTaskName());
                            } else if (newList.get(j).getLevel() == 2) {
                                carrerEvadataVo3.setSecondTaskName(newList.get(j).getTaskName());
                            } else {
                                carrerEvadataVo3.setThirdTaskName(newList.get(j).getTaskName());
                            }
                            carrerEvadataVo3.setType(Joiner.on(",")
                                    .join(newList.get(j).getValueTypeList().stream().map(ValueTypeVo::getTagName)
                                            .collect(Collectors.toList())));
                            carrerEvadataVo3.setRelation(Joiner.on(",")
                                    .join(newList.get(j).getOrgList().stream().map(OrgVo::getName)
                                            .collect(Collectors.toList())));
                            carrerEvadataVo3.setTarget(newList.get(j).getValueGoal());
                            carrerEvadataVo3.setResult(newList.get(j).getAchievement());
                            carrerEvadataVo3.setRole(newList.get(j).getRoleName());
                            carrerEvadataVo3.setAbilt(s.getDimensionName());
                            carrerEvadataVo3.setSelfEvaResult(s.getSelfEvaluateResult());
                            carrerEvadataVo3.setSelfEvaReason(s.getSelfEvaluateComment());

                            if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) {
                                StringBuffer finalEvaResult = new StringBuffer();
                                StringBuffer finalEvaReason = new StringBuffer();
                                evaluateOpinionVoList.forEach(e -> {
                                    for (int d = 0; d < e.getResultDetailPoList().size(); d++) {
                                        if (s.getExamineScoreRuleDimensionInstanceId()
                                                .equals(e.getResultDetailPoList().get(d)
                                                        .getExamineScoreRuleDimensionInstanceId())) {
                                            if (evaluateOpinionVoList.size() > 1) {
                                                finalEvaResult.append(e.getEvaluateUserName() + ": " + e
                                                        .getResultDetailPoList().get(d).getEvaluateGrade() + "\n");
                                                finalEvaReason.append(e.getEvaluateUserName() + ": " + e
                                                        .getResultDetailPoList().get(d).getDimensionOpinion() + "\n");
                                            } else {
                                                finalEvaResult
                                                        .append(e.getResultDetailPoList().get(d).getEvaluateGrade()
                                                                + "\n");
                                                finalEvaReason
                                                        .append(e.getResultDetailPoList().get(d).getDimensionOpinion()
                                                                + "\n");
                                            }
                                            carrerEvadataVo3.setFinalEvaResult(finalEvaResult.toString());
                                            carrerEvadataVo3.setFinalEvaReason(finalEvaReason.toString());
                                            break;
                                        }
                                    }
                                });
                            }

                            vos.add(carrerEvadataVo3);
                        }

                    }
                });

                excelWriter.fill(paramMap, sheet);
                excelWriter.fill(vos, sheet);

最后导出填充一起执行

6.导出效果数据

效果如下（示例）：
在这里插入图片描述

比较潦草多多见谅

总结

提示：这里对文章进行总结：
以上就是今天要讲的内容，本文仅仅简单介绍了easyExcel的使用，而easyExcel提供了大量能使我们快速便捷地处理数据的函数和方法。!

JavaSwx
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值