利用EasyExcel实现excel模板导出

1.导入相关依赖

<properties>
    <easyexcel.version>3.1.4</easyexcel.version>
</properties>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
    <exclusions>
        <exclusion>
            <artifactId>poi</artifactId>
            <groupId>org.apache.poi</groupId>
        </exclusion>
        <exclusion>
            <artifactId>poi-ooxml</artifactId>
            <groupId>org.apache.poi</groupId>
        </exclusion>
        <exclusion>
            <artifactId>poi-ooxml-schemas</artifactId>
            <groupId>org.apache.poi</groupId>
        </exclusion>
    </exclusions>
</dependency>

2.编辑模板

1.原始模板：

2.编辑模板：

 3.业务代码

serviceImpl实现类：

 public void exportExcelSettlement(String settSerialNo, HttpServletResponse response)throws IOException {
        //获取模板路径
        InputStream template = new PathMatchingResourcePatternResolver().getResource("templates/excel/settlement.xlsx").getInputStream();
        if (template.available() == 0) {
            log.error("【导出对xx】获取xx模板失败");
            throw EXPORT_TEMPLATES_EXCEPTION_SETT;
        }
        //获取数据
        SettlementDetailVO vo = querySettlementDetail(settSerialNo);
        List<SettlementDetailGoodsVO> goodsVOS = querySettlementDetailGoods(settSerialNo);
        //判断数据是否存在
        if (ObjectUtils.isEmpty(vo)) {
            log.error("【导出xx】xx信息不存在");
            throw SETT_NOT_EXIST_EXCEPTION;
        }
        if (CollectionUtils.isEmpty(goodsVOS)) {
            log.error("【导出xx】xx信息不存在");
            throw SETT_NOT_EXIST_EXCEPTION;
        }
        response.setContentType("application/json;charset=utf-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(vo.getBuyersName() + "-" + "对账单" + ".xlsx", "UTF-8"));
        //ExcelWriter该对象用于通过POI将值写入Excel
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template).build();
        //构建excel的sheet
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        //控制集合数据垂直填充;ps.集合中的数据需要以FillWrapper()包裹起来才能被解析
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();
        //表格goods的数据填充
        excelWriter.fill(new FillWrapper("goods", goodsVOS), fillConfig, writeSheet);
        //其他数据填充
        excelWriter.fill(convertObject(vo), writeSheet);
        excelWriter.finish();
    }

 //其余数据的转换
private SettlementDetailVO convertObject(SettlementDetailVO info) {
        SettlementDetailVO vo = new SettlementDetailVO();
        vo.setSettAmount(info.getSettAmount());
        vo.setSettName(info.getSettName());
        return vo;
    }