基于EasyExcel操作Excel

介绍：

Java操作excel的基本是Apace POI，原生的Apace POI用起来有些复杂，今天介绍的EasyExcel是基于Apace POI的，简化了Java端操作excel的方式。

所需依赖：

#所需依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

基本写操作：

@Data
public class Student {
    @ExcelProperty(index = 0, value = "主键")
    private Integer id;

    /**
     * 姓名
     */
    @ExcelProperty(index = 1, value = "姓名")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(index = 2,value = "年龄")
    private Integer age;
}

@Test
public void studentsWrite() {
    String fileName = "D:" + File.separator + "demo.xlsx";
    String sheetName = "test";
    List<Student> students = new ArrayList<>();
    students.add(new Student(1, "aa", 20));
    students.add(new Student(2, "bb", 22));
    students.add(new Student(3, "cc", 23));
    students.add(new Student(4, "dd", 24));
    EasyExcel.write(fileName, Student.class).sheet(sheetName).doWrite(students);
}

基本写操作的效果图如下：

基本读操作：

public class StudentListener extends AnalysisEventListener<Student> {

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

    List<Student> list = new ArrayList<Student>();

    @Override
    public void invoke(Student student, AnalysisContext analysisContext) {
        System.out.println("解析到一条数据:" + JSON.toJSONString(student));
        list.add(student);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("最后一次解析了" + list.size() + "条数据！");
        System.out.println("所有数据解析完成！");
    }
}
    @Test
    public void studentsRead() {
        String fileName = "D:\\students.xls";
        EasyExcel.read(fileName, Student.class, new StudentListener()).sheet().doRead();
    }

基本读操作的效果图如下：