easyExcel批量导入数据

1.依赖：

<!--easypoi依赖，excel导入导出-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>

<!--fastjson-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.83</version>
</dependency>

<!--lombok-->

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.6</version>
</dependency>

<!--mybatis-plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

2.通用listener ※

package com.**.example.conf;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.extension.service.IService;
import lombok.SneakyThrows;
import org.springframework.stereotype.Component;

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

/**
 * @author 
 * @date 2024/01/12
 **/
@Component
public class ObjectExcelListener<T extends Object> extends AnalysisEventListener<T> {
    List<T> addList = new ArrayList<>();
    private static final int BATCH_COUNT = 10000;

    private IService<T> baseDao;

      

/**
 * 所有数据解析完了会来调用
 * @param context
*/
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }
    /**
     * 返回数据
     *
     * @return 返回读取的数据集合
     **/
    public List<T> getDatas() {
        return addList;
    }
    /**
     * 设置读取的数据集合
     *
     * @param datas 设置读取的数据集合
     **/
    public void setDatas(List<T> datas) {
        this.addList = datas;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param testCategory
     * @param analysisContext
     */
    @SneakyThrows
    @Override
    public void invoke(T testCategory, AnalysisContext analysisContext) {
        addList.add(testCategory);
//        if (addList.size() >= BATCH_COUNT ) {
//            saveData();
//            addList.clear();
//        }
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        if (addList.isEmpty()) {
            return;
        }
        baseDao.saveBatch(addList);
    }

}

3.实体类：

package com.**.example.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.sql.Date;
import java.time.LocalDate;


/**
 * @author 
 * @date 2024/01/11
 **/
@Data
@TableName("t_species_info")
public class SpeciesEntity {

    @ExcelIgnore
    @TableId(type = IdType.ASSIGN_UUID)
    private String speciesId;

    @ExcelProperty("名称")
    private String speciesName;

    @ExcelProperty("特有类型")
    private String isSpecific;

    @ExcelProperty("物种英文名")
    private String speciesNameEn;

    @ExcelProperty("物种分类")
    private String speciesType;

    @ExcelProperty("国家保护级别")
    private String protectLevel;

    @ExcelProperty("华盛顿公约濒危等级分类(CITES)")
    private String cites;

    @ExcelProperty("世界自然保护联盟濒危物种等级(IUCN)")
    private String iucn;

    @ExcelProperty("是否是极小种群")
    private int isTinyPopulation;

    @ExcelProperty("评估信息")
    private String evaluateInfo;

    @ExcelProperty("形态特征")
    private String feature;

    @ExcelProperty("数量")
    private String speciesNum;

    @ExcelProperty("数量单位")
    private String numUnit;

    @ExcelProperty("面积")
    private String speciesArea;

    @ExcelProperty("面积单位")
    private String areaUnit;

    @ExcelProperty("景区位置分布")
    private String distributedPosition;

    @ExcelProperty("功能用途")
    private String otherPoint;

    @ExcelProperty("是否为古树名木")
    private int ancientTreeFlag;

    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("入住时间")
    private String checkInTime;

    @ExcelProperty("入住年龄")
    private String checkInAge;
}

4.excel

5.读取文件

public int saveSpeciesData(MultipartFile file) {
    List<SpeciesEntity> list = null;
    long l = System.currentTimeMillis();
    try {
        list = EasyExcel.read(file.getInputStream(), SpeciesEntity.class, new ObjectExcelListener()).sheet().doReadSync();
        System.out.println(JSONArray.toJSONString(list));
        this.saveBatch(list);
    } catch (IOException e) {
        e.printStackTrace();
    }
    log.info("导入：{}条数据耗时：{}", list.size(), System.currentTimeMillis() - l);
    return list.size();
}