目录

• • 依赖
  • 写入excel
  • 读取excel

依赖

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

写入excel

1. 实体类

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataDemo {
    @ExcelProperty("学生编号")  //excel表头名称
    private Integer stuNum;
    @ExcelProperty("学生名称")
    private String stuName;
}

2. Api写法

@SpringBootTest
public class EasyExcelTest {
    @Test
    public void test(){
        //实现写操作
        //1. 设置要写入的文件路径
        String filename = "E:\\excel\\write.xlsx";
        //2. 准备数据
        List<ExcelDataDemo> data = new ArrayList<>();
        data.add(new ExcelDataDemo(201430317,"诺亚"));
        data.add(new ExcelDataDemo(201430329,"雷杰多"));
        data.add(new ExcelDataDemo(201430365,"撒加"));
        //3. 调用easyexcel的方法实现写操作。**写完后，文件流会自动关闭**
        //   参数1：文件名称  参数2：实体类
        //   sheet：excel文件底部栏的sheet
        EasyExcel.write(filename, ExcelDataDemo.class).sheet("学生列表").doWrite(data);
    }
}

生成write.xlsx文件：

内容：

读取excel

1. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ReadExcelDemo {
    @ExcelProperty(value = "学生编号",index = 0)  //index表示excel第几列
    private Integer stuNum;
    @ExcelProperty(value = "学生名称",index = 1)
    private String stuName;
}

2. 创建监听

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jarvis.eduservice.entity.ReadExcelDemo;

import java.util.Map;

public class ExcelListener extends AnalysisEventListener<ReadExcelDemo> {

    //一行一行读取excel中的内容，跳过表头直接读取第二行内容
    @Override
    public void invoke(ReadExcelDemo data, AnalysisContext analysisContext) {
        System.out.println("****"+data);
        //业务逻辑
        doSomething(data);//根据自己业务做处理
    }
    //读取表头
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头："+headMap);
    }
    //读取后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3. Api写法

@Test
public void testRead(){
    //实现读操作
    //1. 设置要读取的文件路径
    String filename = "E:\\excel\\write.xlsx";
    //2. 读取  会先读表头，然后读数据
    EasyExcel.read(filename, ReadExcelDemo.class,new ExcelListener()).sheet().doRead();
}

=====output:
表头：{0=学生编号, 1=学生名称}
****ReadExcelDemo(stuNum=201430317, stuName=诺亚)
****ReadExcelDemo(stuNum=201430329, stuName=雷杰多)
****ReadExcelDemo(stuNum=201430365, stuName=撒加)