EasyExcel的读写操作示例

0 引入依赖

<!-- xls格式excel依赖包 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!--xlsx格式excel依赖包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<!-- easyexcel依赖包 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>

1 写操作

1.1 创建实体类

学生类，包含学生编号和姓名

package com.yqj.serviceedu.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class DemoStudent {
    //表头内容
    @ExcelProperty("学生编号")
    private Integer no;
    @ExcelProperty("学生姓名")
    private String name;
}

1.2 写文件

向student.xlsx中写入10条数据

package com.yqj.serviceedu.excel;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {

    //写文件
    @Test
    public void testEasyExcelWrite(){
        String filePath = "D:/student.xlsx";
        EasyExcel.write(filePath,DemoStudent.class).sheet("学生信息").doWrite(getData());
    }

    public static List<DemoStudent> getData(){
        List<DemoStudent> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoStudent student = new DemoStudent();
            student.setNo(i);
            student.setName("yorick"+i);
            students.add(student);
        }
        return students;
    }
}

2 读文件

2.1 在实体类的注解中加入索引信息

package com.yqj.serviceedu.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class DemoStudent {
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer no;
    @ExcelProperty(value = "学生姓名",index = 1)
    private String name;
}

2.2 配置读取文件的监听器

package com.yqj.serviceedu.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.Map;

public class ExcelListener extends AnalysisEventListener<DemoStudent> {
    
    //读取除表头外的每行数据执行该方法
    @Override
    public void invoke(DemoStudent data, AnalysisContext context) {
        System.out.println(data);
    }

    //获取表头数据
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println(headMap);
    }

    //读取结束执行该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("finish");
    }
}

1.3 读文件

读取之前写入的那个文件 student.xlsx

package com.yqj.serviceedu.excel;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {

    //读文件
    @Test
    public void testEasyExcelRead(){
        String filePath = "D:/student.xlsx";
        EasyExcel.read(filePath,DemoStudent.class,new ExcelListener()).sheet().doRead();
    }
}