easy excel实现一个接口任意表的Excel导入导出

发表于 分类于 阅读次数：

Java的web开发需要excel的导入导出工具，所以需要一定的工具类实现，如果是使用easypoi、Hutool导入导出excel，会非常的损耗内存，因此可以尝试使用easyexcel解决大数据量的数据的导入导出，且可以通过Java8的函数式编程解决该问题。

使用easyexcel，虽然不太会出现OOM的问题，但是如果是大数据量的情况下也会有一定量的内存溢出的风险，所以我打算从以下几个方面优化这个问题：

  • 使用Java8的函数式编程实现低代码量的数据导入
  • 使用反射等特性实现单个接口导入任意excel
  • 使用线程池实现大数据量的excel导入
  • 通过泛型实现数据导出

maven导入

1
2
3
4
5
6
<!--EasyExcel相关依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

使用泛型实现对象的单个Sheet导入

先实现一个类，用来指代导入的特定的对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("stu_info")
@ApiModel("学生信息")
//@ExcelIgnoreUnannotated 没有注解的字段都不转换
public class StuInfo {

    private static final long serialVersionUID = 1L;

    /**
     * 姓名
     */
    // 设置字体，此处代表使用斜体
//    @ContentFontStyle(italic = BooleanEnum.TRUE)
    // 设置列宽度的注解,注解中只有一个参数value，value的单位是字符长度，最大可以设置255个字符
    @ColumnWidth(10)
    // @ExcelProperty 注解中有三个参数value,index,converter分别代表表名，列序号，数据转换方式
    @ApiModelProperty("姓名")
    @ExcelProperty(value = "姓名",order = 0)
    @ExportHeader(value = "姓名",index = 1)
    private String name;

    /**
     * 年龄
     */
//    @ExcelIgnore不将该字段转换成Excel
    @ExcelProperty(value = "年龄",order = 1)
    @ApiModelProperty("年龄")
    @ExportHeader(value = "年龄",index = 2)
    private Integer age;

    /**
     * 身高
     */
    //自定义格式-位数
//    @NumberFormat("#.##%")
    @ExcelProperty(value = "身高",order = 2)
    @ApiModelProperty("身高")
    @ExportHeader(value = "身高",index = 4)
    private Double tall;

    /**
     * 自我介绍
     */
    @ExcelProperty(value = "自我介绍",order = 3)
    @ApiModelProperty("自我介绍")
    @ExportHeader(value = "自我介绍",index = 3,ignore = true)
    private String selfIntroduce;

    /**
     * 图片信息
     */
    @ExcelProperty(value = "图片信息",order = 4)
    @ApiModelProperty("图片信息")
    @ExportHeader(value = "图片信息",ignore = true)
    private Blob picture;

    /**
     * 性别
     */
    @ExcelProperty(value = "性别",order = 5)
    @ApiModelProperty("性别")
    private Integer gender;

    /**
     * 入学时间
     */
    //自定义格式-时间格式
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss:")
    @ExcelProperty(value = "入学时间",order = 6)
    @ApiModelProperty("入学时间")
    private String intake;

    /**
     * 出生日期
     */
    @ExcelProperty(value = "出生日期",order = 7)
    @ApiModelProperty("出生日期")
    private String birthday;


}

重写ReadListener接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
@Slf4j
public class UploadDataListener<T> implements ReadListener<T> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * Predicate用于过滤数据
     */
    private Predicate<T> predicate;

    /**
     * 调用持久层批量保存
     */
    private Consumer<Collection<T>> consumer;

    public UploadDataListener(Predicate<T> predicate, Consumer<Collection<T>> consumer) {
        this.predicate = predicate;
        this.consumer = consumer;
    }

    public UploadDataListener(Consumer<Collection<T>> consumer) {
        this.consumer = consumer;
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {

        if (predicate != null && !predicate.test(data)) {
            return;
        }
        cachedDataList.add(data);

        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            try {
                // 执行具体消费逻辑
                consumer.accept(cachedDataList);

            } catch (Exception e) {

                log.error("Failed to upload data!data={}", cachedDataList);
                throw new BizException("导入失败");
            }
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        if (CollUtil.isNotEmpty(cachedDataList)) {

            try {
                // 执行具体消费逻辑
                consumer.accept(cachedDataList);
                log.info("所有数据解析完成！");
            } catch (Exception e) {

                log.error("Failed to upload data!data={}", cachedDataList);

                // 抛出自定义的提示信息
                if (e instanceof BizException) {
                    throw e;
                }

                throw new BizException("导入失败");
            }
        }
    }
}

Controller层的实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@ApiOperation("只需要一个readListener，解决全部的问题")
    @PostMapping("/update")
    @ResponseBody
    public R<String> aListener4AllExcel(MultipartFile file) throws IOException {
        try {
            EasyExcel.read(file.getInputStream(),
                            StuInfo.class,
                            new UploadDataListener<StuInfo>(
                                    list -> {
                                        // 校验数据
//                                        ValidationUtils.validate(list);
                                        // dao 保存···
                                        //最好是手写一个，不要使用mybatis-plus的一条条新增的逻辑
                                        service.saveBatch(list);
                                        log.info("从Excel导入数据一共 {} 行 ", list.size());
                                    }))
                    .sheet()
                    .doRead();
        } catch (IOException e) {

            log.error("导入失败", e);
            throw new BizException("导入失败");
        }
        return R.success("SUCCESS");
    }

但是这种方式只能实现已存对象的功能实现，如果要新增一种数据的导入，那我们需要怎么做呢？

可以通过读取成Map，根据顺序导入到数据库中。

通过实现单个Sheet中任意一种数据的导入

Controller层的实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    @ApiOperation("只需要一个readListener，解决全部的问题")
    @PostMapping("/listenMapDara")
    @ResponseBody
    public R<String> listenMapDara(@ApiParam(value = "表编码", required = true)
                                   @NotBlank(message = "表编码不能为空")
                                   @RequestParam("tableCode") String tableCode,
                                   @ApiParam(value = "上传的文件", required = true)
                                   @NotNull(message = "上传文件不能为空") MultipartFile file) throws IOException {
        try {
            //根据tableCode获取这张表的字段，可以作为insert与剧中的信息
            EasyExcel.read(file.getInputStream(),
                            new NonClazzOrientedListener(
                                    list -> {
                                        // 校验数据
//                                        ValidationUtils.validate(list);

                                        // dao 保存···
                                        log.info("从Excel导入数据一共 {} 行 ", list.size());
                                    }))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            log.error("导入失败", e);
            throw new BizException("导入失败");
        }
        return R.success("SUCCESS");
    }

重写ReadListener接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

@Slf4j
public class NonClazzOrientedListener implements ReadListener<Map<Integer, String>> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    private List<List<Object>> rowsList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private List<Object> rowList = new ArrayList<>();
    /**
     * Predicate用于过滤数据
     */
    private Predicate<Map<Integer, String>> predicate;

    /**
     * 调用持久层批量保存
     */
    private Consumer<List> consumer;

    public NonClazzOrientedListener(Predicate<Map<Integer, String>> predicate, Consumer<List> consumer) {
        this.predicate = predicate;
        this.consumer = consumer;
    }

    public NonClazzOrientedListener(Consumer<List> consumer) {
        this.consumer = consumer;
    }

    /**
     * 添加deviceName标识
     */
    private boolean flag = false;

    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
        consumer.accept(rowsList);
        rowList.clear();
        row.forEach((k, v) -> {
            log.debug("key is {},value is {}", k, v);
            rowList.add(v == null ? "" : v);
        });
        rowsList.add(rowList);
        if (rowsList.size() > BATCH_COUNT) {
            log.debug("执行存储程序");
            log.info("rowsList is {}", rowsList);
            rowsList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        consumer.accept(rowsList);
        if (CollUtil.isNotEmpty(rowsList)) {
            try {
                log.debug("执行最后的程序");
                log.info("rowsList is {}", rowsList);
            } catch (Exception e) {

                log.error("Failed to upload data!data={}", rowsList);

                // 抛出自定义的提示信息
                if (e instanceof BizException) {
                    throw e;
                }

                throw new BizException("导入失败");
            } finally {
                rowsList.clear();
            }
        }
    }

这种方式可以通过把表中的字段顺序存储起来，通过配置数据和字段的位置实现数据的新增，那么如果出现了导出数据模板/手写excel的时候顺序和导入的时候顺序不一样怎么办？

可以通过读取header进行实现，通过表头读取到的字段，和数据库中表的字段进行比对，只取其中存在的数据进行排序添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    /**
     * 这里会一行行的返回头
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        //该方法必然会在读取数据之前进行
        Map<Integer, String> columMap = ConverterUtils.convertToStringMap(headMap, context);
        //通过数据交互拿到这个表的表头
//        Map<String,String> columnList=dao.xxxx();
        Map<String, String> columnList = new HashMap();
        columMap.forEach((key, value) -> {
            if (columnList.containsKey(value)) {
                filterList.add(key);
            }
        });
        //过滤到了只存在表里面的数据，顺序就不用担心了，可以直接把filterList的数据用于排序，可以根据mybatis做一个动态sql进行应用

        log.info("解析到一条头数据:{}", JSON.toJSONString(columMap));
        // 如果想转成成 Map<Integer,String>
        // 方案1： 不要implements ReadListener 而是 extends AnalysisEventListener
        // 方案2： 调用 ConverterUtils.convertToStringMap(headMap, context) 自动会转换
    }

那么这些问题都解决了，如果出现大数据量的情况，如果要极大的使用到cpu，该怎么做呢？

可以尝试使用线程池进行实现

使用线程池进行多线程导入大量数据

Java中线程池的开发与使用与原理我可以单独写一篇文章进行讲解，但是在这边为了进行好的开发我先给出一套固定一点的方法。

由于ReadListener不能被注册到IOC容器里面，所以需要在外面开启

详情可见Spring Boot通过EasyExcel异步多线程实现大数据量Excel导入,百万数据30秒

通过泛型实现对象类型的导出

1
2
3
4
5
6
7
8
9
10
11
12

public <T> void commonExport(String fileName, List<T> data, Class<T> clazz, HttpServletResponse response) throws IOException {
    if (CollectionUtil.isEmpty(data)) {
        data = new ArrayList<>();
    }
    //设置标题
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream()).head(clazz).sheet("sheet1").doWrite(data);
}

直接使用该方法可以作为公共的数据的导出接口

如果想要动态的下载任意一组数据怎么办呢？可以使用这个方法

1
2
3
4
5
6
7
8
9
10
11
public void exportFreely(String fileName, List<List<Object>> data, List<List<String>> head, HttpServletResponse response) throws IOException {
        if (CollectionUtil.isEmpty(data)) {
            data = new ArrayList<>();
        }
        //设置标题
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream()).head(head).sheet("sheet1").doWrite(data);
    }

什么？不仅想一个接口展示全部的数据与信息，还要增加筛选条件？这个后期我可以单独写一篇文章解决这个问题。

今天的分享就到这里了。