EasyExcel复杂模板填充

1.引入pom依赖
 

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

2.编辑模板

{xxx}代表map填充(填入key)或者bean填充(填入属性名)

{.xxx}代表list填充,填入对应属性名 

3.填充数据

        //其他信息
        OtherInfo otherInfo = new OtherInfo();
        otherInfo.setDate(new Date())
                .setPhone("13000000000")
                .setMarketName("城东菜市场");
        //蔬菜信息
        List<VegetableInfo> vegetableInfos = new ArrayList<>();
        vegetableInfos.add(new VegetableInfo().setName("白菜")
                .setUnitPrice("1.50")
                .setTradePrice("1.20")
                .setArrivalDate(new Date())
                .setPlaceOrigin("河北省"));
        vegetableInfos.add(new VegetableInfo().setName("胡萝北")
                .setUnitPrice("1.00")
                .setTradePrice("0.80")
                .setArrivalDate(new Date())
                .setPlaceOrigin("辽宁省"));
        vegetableInfos.add(new VegetableInfo().setName("西红柿")
                .setUnitPrice("2.11")
                .setTradePrice("1.88")
                .setArrivalDate(new Date())
                .setPlaceOrigin("河南省"));
        int[] arr = {1};
        vegetableInfos = vegetableInfos.stream().
peek(vegetableInfo ->vegetableInfo.setId(arr[0] ++)).collect(Collectors.toList());

 

注意模板的第一列是填充序号的,这里我是在bean添加了这么一个字段使用stream流来给他添加的序号,java使用stream的代码会被编译成匿名内部类,而匿名内部类想要使用外部变量则必须是fian修饰的,所以这里不可以使用基础int或者Integetr类型,使用int数组是可以的,因为对象本身并没有改变.另外我查了好多资料也没发找到easyExcel自定义添加序号的方法,翻看源码找到ExcelWriteFillExecutor.fill()中对list数据进行了遍历写入.麻烦知道的大佬留下方法!

4.设置响应头获取response
 

private HttpServletResponse getHttpServletResponse() {
        response.setContentType("application/octet-stream");
        response.setHeader("content-type", "application/octet-stream");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            fileName = URLEncoder.encode("蔬菜价格表.xlsx", "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        return response;
    }

5.获取模板,填充模板
 

        //获取response
        HttpServletResponse response = this.getHttpServletResponse();
        //获取模板
        ClassPathResource classPathResource = new ClassPathResource("templates/蔬菜价格表.xlsx");
        try (InputStream inputStream = classPathResource.getInputStream();
             ServletOutputStream outputStream = response.getOutputStream()) {
            //设置输出流和模板信息
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //开启自动换行,自动换行表示每次写入一条list数据是都会重新生成一行空行,此选项默认是关闭的,需要提前设置为true
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(vegetableInfos, fillConfig, writeSheet);
            excelWriter.fill(otherInfo, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }

6.查看生成效果