随笔- 18 文章- 0 评论- 0 阅读- 5250

java导入Excel(使用阿里巴巴的easyexcel)

1. 第一步引入pom

引入easyexcel 的pom依赖

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

我输出时和实体类字段Date(时间格式化)用到了fastjson 用到了就引入，用不到可以不引

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.75</version>
</dependency>

2. 第二步写代码

一定要有无参构造

@Data
public class ExcelEntity {
    private String name;
    @JSONField(format = "yyyy-MM-dd")
    private Date birthday;
    private Integer age;
}

此处我延续了AnalysisEventListener的泛型，（易扩展）此类不支持Spring IOC

public class ExcelListener<T> extends AnalysisEventListener<T> {

    private static final int BATCH_COUNT = 5;
    List<T> list = new ArrayList<T>();
    // 此处可传Spring IOC对象进行保存数据库
    public ExcelListener(){
    };
    // 读取每一条
    @Override
    public void invoke(T data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
        list.add(data);
    }

     // 读取完毕后
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("读取完成！");
        saveData();
        list.clear();
    }
    
    // 保存操作
    private void saveData(){
        System.out.println("存入数据库");
    }
}

写接口Controller

@PostMapping("/addExcel")
public String addExcel(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), ExcelEntity.class, new ExcelListener<ExcelEntity>()).sheet().doRead();
    return "ok";
}

3. 测试结果

成功结果！

控制台输出结果！

{"age":1,"birthday":"2020-11-19","name":"张1"}
{"age":2,"birthday":"2020-11-19","name":"张2"}
{"age":3,"birthday":"2020-11-19","name":"张3"}
{"age":4,"birthday":"2020-11-19","name":"张4"}
{"age":5,"birthday":"2020-11-19","name":"张5"}
{"age":6,"birthday":"2020-11-19","name":"张6"}
存入数据库
{"age":7,"birthday":"2020-11-19","name":"张7"}
{"age":8,"birthday":"2020-11-19","name":"张8"}
{"age":9,"birthday":"2020-11-19","name":"张9"}
{"age":10,"birthday":"2020-11-19","name":"张10"}
读取完成！
存入数据库