easyexcel读取

pom依赖，easyexcel是必须的，lombok非必须，它可以在实体类上使用@Data注释，无需生成get、set方法

    <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

　　实体类

public class Province {

    @ExcelProperty(index = 0)
    private Long provinceId;

    @ExcelProperty(index = 1)
    private String provinceName;

    @ExcelProperty(index = 2)
    private Long cityId;

    @ExcelProperty(index = 3)
    private String cityName;

    public Long getCityId() {
        return cityId;
    }

    public void setCityId(Long cityId) {
        this.cityId = cityId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public Long getProvinceId() {
        return provinceId;
    }

    public void setProvinceId(Long provinceId) {
        this.provinceId = provinceId;
    }

    public String getProvinceName() {
        return provinceName;
    }

    public void setProvinceName(String provinceName) {
        this.provinceName = provinceName;
    }
}

　　excel监听器(需注意的是，要将dao传入进去，也就是每一个监听器只操作一种类型的

/**
 * 使用EasyExcel处理excel文件
 * @Version 1.0.0
 * @Description
 */
public class ProvinceExcelListener extends AnalysisEventListener<Province> {

    private ProvinceMapper provinceMapper;

    /**
     * 批处理阈值
     */
    private static final int BATCH_COUNT = 1000;
    List<Province> list = new ArrayList<Province>(BATCH_COUNT); //存放数据

    public ProvinceExcelListener(ProvinceMapper provinceMapper){
        this.provinceMapper = provinceMapper;
    }

    @Override
    public void invoke(Province province, AnalysisContext context) {
        list.add(province);
        //内存存放超过多少条数据，则先存放到数据库中；保存完后，清空内存
        if(list.size() >= BATCH_COUNT){
            long beforeTime = System.currentTimeMillis();
            saveData();
            list.clear();//清空内存
            System.out.println("处理1000数据所需的时间："+(System.currentTimeMillis()-beforeTime));
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
　　　　　　saveData();//解析完成后需要调用一次，不大于BATCH_COUNT就没处理
    }

    /**
     * 保存数据
     */
    private void saveData(){
        for(Province province : list){
            provinceMapper.add(province);
        }
    }
}

　　测试类

@Test
    void contextLoads()throws Exception {

        String fileName = "C:/Users/zwk/Desktop/工作簿1.xlsx";
        long beforeTime = System.currentTimeMillis();
        EasyExcel.read(fileName,Province.class,new ProvinceExcelListener(provinceMapper)).sheet().doRead();
        System.out.println("读取数据所花的时间："+(System.currentTimeMillis()-beforeTime)+"毫秒");
    }

　　注：空的也能读取，不用特殊处理