1. 介绍

  EastExcel是Alibaba的开源工具,比POI更加快速高效,使用起来也简单。
  官网地址为:https://alibaba-easyexcel.github.io/index.html,这里就不再赘述,只是记录下我们当时一个业务需要根据数据格式动态合并单元格。

2.需求

  图1是从库里查出来的数据做了一些合并,图2是最后需要合并的格式,主要是对坐席人员、所属机构、合作机构进行合并,因为所有的数据都是动态的,所以需要动态合并单元格,如果知道固定的合并格式,则easyexcel提供了OnceAbsoluteMergeStrategy和LoopMergeStrategy的简单合并策略,但是这个比较麻烦,所以我继承了AbstractMergeStrategy类来自定义我自己的合并策略。

  • 图1 原始数据:
    在这里插入图片描述
  • 图2 需求格式:
    在这里插入图片描述

3.代码

3.1 pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

3.2 Controller

  1. 通过getExcelDtoList()方法获取了测试数据
	@RequestMapping(value="/exportXls", method = RequestMethod.GET)
    public void exportXls(HttpServletResponse response) throws IOException {
        String message = "下载文件失败";
        try{
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = UUID.randomUUID().toString().replaceAll("-", "").substring(0, 7);
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //获取测试数据
            List<ExcelDto> excelDtoList = getExcelDtoList();
            //合并策略map
            Map<String, List<RowRangeDto>> strategyMap = ExcelUtil.addMerStrategy(excelDtoList);
            EasyExcel.write(response.getOutputStream(), ExcelDto.class)
                    //注册合并策略
                    .registerWriteHandler(new BizMergeStrategy(strategyMap))
                    .sheet("Sheet1").doWrite(excelDtoList);
        }catch (Exception e) {
            e.printStackTrace();
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println(JSON.toJSONString(JsonResponse.fail(message)));
        }
    }

    private List<ExcelDto> getExcelDtoList() {
        return JSONArray.parseArray("[{\"applyCount\":2,\"avgTime\":12.5,\"bizUnit\":\"-\",\"connCount\":2,\"connRate\":1,\"coopOrg\":\"蔚蓝\",\"orgName\":\"朝阳支行\",\"signerName\":\"admin\"},{\"applyCount\":1,\"avgTime\":0,\"bizUnit\":\"北京\",\"connCount\":0,\"connRate\":0,\"coopOrg\":\"长安新生\",\"orgName\":\"朝阳支行\",\"signerName\":\"admin\"},{\"applyCount\":1,\"avgTime\":11,\"bizUnit\":\"山西\",\"connCount\":1,\"connRate\":1,\"coopOrg\":\"长安新生\",\"orgName\":\"朝阳支行\",\"signerName\":\"admin\"},{\"applyCount\":2,\"avgTime\":0,\"bizUnit\":\"北京\",\"connCount\":0,\"connRate\":0,\"coopOrg\":\"长安新生\",\"orgName\":\"丰台支行\",\"signerName\":\"张三\"},{\"applyCount\":1,\"avgTime\":0,\"bizUnit\":\"-\",\"connCount\":0,\"connRate\":0,\"coopOrg\":\"蔚蓝\",\"orgName\":\"朝阳支行\",\"signerName\":\"张三\"},{\"applyCount\":2,\"avgTime\":0,\"bizUnit\":\"北京\",\"connCount\":0,\"connRate\":0,\"coopOrg\":\"长安新生\",\"orgName\":\"朝阳支行\",\"signerName\":\"张三\"},{\"applyCount\":1,\"avgTime\":0,\"bizUnit\":\"山西\",\"connCount\":0,\"connRate\":0,\"coopOrg\":\"长安新生\",\"orgName\":\"朝阳支行\",\"signerName\":\"张三\"}]", ExcelDto.class);
    }

3.3 ExcelUtil代码

  1. 这个类主要是逻辑判断,生成自定义合并策略,map格式如图,就是说对0列的1-3行,4-7行合并,对1列的1-3行,5-7行合并,对2列的2-3行,6-7行合并:
    在这里插入图片描述
public class ExcelUtil {
    /**
     * @Author: TheBigBlue
     * @Description: 添加合并策略
     * @Date: 2020/3/16
     * @Param:
     * @return:
     **/
    public static Map<String, List<RowRangeDto>> addMerStrategy(List<ExcelDto> excelDtoList) {
        Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
        ExcelDto preExcelDto = null;
        for (int i = 0; i < excelDtoList.size(); i++) {
            ExcelDto currDto = excelDtoList.get(i);
            if (preExcelDto != null) {
                //从第二行开始判断是否需要合并
                if (currDto.getSignerName().equals(preExcelDto.getSignerName())) {
                    //如果坐席人员一样,则可合并坐席人员一列
                    fillStrategyMap(strategyMap, "0", i);
                    //如果坐席一样,并且所属机构一样,则可合并所属机构一列
                    if (currDto.getOrgName().equals(preExcelDto.getOrgName())) {
                        fillStrategyMap(strategyMap, "1", i);
                        //如果坐席、所属机构一样,并且合作机构也一样,则可合并合作机构一列
                        if (currDto.getCoopOrg().equals(preExcelDto.getCoopOrg())) {
                            fillStrategyMap(strategyMap, "2", i);
                        }
                    }
                }
            }
            preExcelDto = currDto;
        }
        return strategyMap;
    }

    /**
     * @Author: TheBigBlue
     * @Description: 新增或修改合并策略map
     * @Date: 2020/3/16
     * @Param:
     * @return:
     **/
    private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
        List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (RowRangeDto dto : rowRangeDtoList) {
            //分段list中是否有end索引是上一行索引的,如果有,则索引+1
            if (dto.getEnd() == index) {
                dto.setEnd(index + 1);
                flag = true;
            }
        }
        //如果没有,则新增分段
        if (!flag) {
            rowRangeDtoList.add(new RowRangeDto(index, index + 1));
        }
        strategyMap.put(key, rowRangeDtoList);
    }

3.4 BizMergeStrategy代码

  1. 这个类继承AbstractMergeStrategy抽象类,实现merge方法,进行自定义合并策略,传入自定义的合并策略map,解析此map,添加合并请求。
  2. 这个类的关键是那个if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {},注释中也写了,因为merge这个方法是对每个cell操作的,这个merge方法是会重复执行的,如果要合并A2:A3,当当前操作Cell=A2时,要求合并A2,A3,没有问题,当当前操作Cell=A3时,又要求合并A2,A3,但这时已经合并了,所以最后导出的文件在打开时会有问题,需要修复。所以这里要求,如果指定了合并哪些单元格,那就执行一次merge方法,我这里是因为我已经有了合并策略的map,知道需要合并哪些单元格,所以让merge方法只执行一次,那就让rowIndex=1,columnIndex=1,执行这一次的时候,就告诉excel对象合并上面map那样的要求,所以所有的单元格都被操作了一次,最后的结果也是正确的合并了。再次记录一下。
public class BizMergeStrategy extends AbstractMergeStrategy {

    private Map<String, List<RowRangeDto>> strategyMap;
    private Sheet sheet;

    public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的,
             * 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3,
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }
}

3.5 ExcelDto代码

  1. 这个实体类是映射最后文档的head信息的。
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ExcelDto implements Serializable {

    @ExcelProperty(value = "坐席人员", index = 0)
    private String signerName;
    @ExcelProperty(value = "所属机构", index = 1)
    private String orgName;
    @ExcelProperty(value = "合作机构", index = 2)
    private String coopOrg;
    @ExcelProperty(value = "所属事业部", index = 3)
    private String bizUnit;
    @ExcelProperty(value = "视频申请次数", index = 4)
    private int applyCount;
    @ExcelProperty(value = "首次接通数量", index = 5)
    private int connCount;
    @ExcelProperty(value = "首次接通率", index = 6)
    private double connRate;
    @ExcelProperty(value = "平均面谈时长", index = 7)
    private double avgTime;
}

3.6 RowRangeDto代码

  1. 这个实体类是分段的起始位置DTO。
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
    private int start;
    private int end;
}
Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐