EasyExcel实现自定义表头

最近在做Excel导出的时候遇到这样一个问题，有一个可以切换的列表，根据不同的维度去统计数量以及计算比例，其它的字段都是一样的，只有维度的字段不一样。而easyexcel是通过@ExcelProperty注解来指定表头的，如果要实现这个功能我就要创建好几个类，这时我就在想，能不能动态的实现表头呢？

打个比方来说，统计一个班级里面男女同学的数量以及比例，也可以切换成统计一个班级里面各个年龄段的同学的数量以及比例。这两个列表的导出模板如下图所示：

 能不能只用一个类来实现这个功能呢？答案是肯定可以的，下面我们来一起实现这个功能吧~

（1）在pom里面引入easyexcel依赖

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

（2）创建 StudentExcelBean类

@Data
@Builder
public class StudentExcelBean {

    @ExcelProperty(value = "序号", index = 0)
    private Integer serialNumber;

    @ExcelProperty(value = "${titleName}", index = 1)
    private String title;

    @ExcelProperty(value = "数量", index = 2)
    private Integer num;

    @ExcelProperty(value = "占比", index = 3)
    private String ratio;

}

 title字段的@ExcelProperty注解的value属性值是${titleName}，因为后面可以将这个标识进行替换，用这种方式去实现动态表头。这里的${titleName}可以是任何标识符，只要在第三步里面按照特定的标识去替换即可。

（3）创建一个TitleCellWriteHandler类去实现CellWriteHandler接口，并重写beforeCellCreate方法

public class TitleCellWriteHandler implements CellWriteHandler {
    private final Map<String, String> titleMap;
    private final PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${","}");

    public TitleCellWriteHandler(Map<String, String> titleMap) {
        this.titleMap = titleMap;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head != null) {
            List<String> titleNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(titleNameList)) {
               titleMap.forEach((k, v) -> {
                   Properties properties = new Properties();
                   properties.setProperty(k, v);
                   titleNameList.replaceAll(value -> propertyPlaceholderHelper.replacePlaceholders(value, properties));
               });
            }
        }
    }

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

    }

    @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) {

    }

}

 这里我们可以看到在重写的beforeCellCreate方法里面去遍历map，将包含key中关键字的都替换成了value。这里用map的好处就是，可以通过map传参就能实现多个字段的自定义替换。

（4）创建一个EasyExcelUtil工具类去实现导出的方法

public class EasyExcelUtil {

    public static void exportData(HttpServletResponse response, String excelName, String sheetName,
                                           Class clazz, List data, Map<String, String> headMap) throws Exception {
        OutputStream outputStream = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

        EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(new TitleCellWriteHandler(headMap))
                .sheet(sheetName)
                .doWrite(data);
        outputStream.flush();
    }

}

（5）创建一个EasyExcelController测试导出的方法

@RestController
@RequestMapping(value = "/easyExcelTest")
public class EasyExcelController {

    @SneakyThrows
    @PostMapping("exportSexStatistics")
    public void exportSexStatistics(HttpServletResponse response) {
        // 测试数据
        List<StudentExcelBean> dataList = new ArrayList<StudentExcelBean>(){{
            add(StudentExcelBean.builder().serialNumber(1).title("男").num(50).ratio("50%").build());
            add(StudentExcelBean.builder().serialNumber(2).title("女").num(50).ratio("50%").build());
        }};

        EasyExcelUtil.exportData(response, "导出",
                "sheet1",
                StudentExcelBean.class,
                dataList,
                new HashMap<String, String>() {{put("titleName", "性别");}});
    }

    @SneakyThrows
    @PostMapping("exportAgeStatistics")
    public void exportAgeStatistics(HttpServletResponse response) {
        // 测试数据
        List<StudentExcelBean> dataList = new ArrayList<StudentExcelBean>(){{
            add(StudentExcelBean.builder().serialNumber(1).title("11-12岁").num(20).ratio("20%").build());
            add(StudentExcelBean.builder().serialNumber(2).title("13-14岁").num(20).ratio("20%").build());
            add(StudentExcelBean.builder().serialNumber(3).title("15-16岁").num(20).ratio("20%").build());
            add(StudentExcelBean.builder().serialNumber(4).title("17-18岁").num(20).ratio("20%").build());
            add(StudentExcelBean.builder().serialNumber(5).title("19-20岁").num(20).ratio("20%").build());
        }};

        EasyExcelUtil.exportData(response, "导出",
                "sheet1",
                StudentExcelBean.class,
                dataList,
                new HashMap<String, String>() {{put("titleName", "年龄段");}});
    }

}

这里我们分别写了两个接口来测试不同的导出，下面是两个接口导出的excel截图

这里可以看到我们已经实现了这个效果，即调用导出方法的时候通过map传参的方式去修改指定的值，在实际开发中可以通过前端type传参来改变map中对应的值即可。 

点点关注不迷路，下期将带来更多实用的小技巧~