EasyExcel 校验后导入

引入pom

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

触发校验类

import com.baomidou.mybatisplus.extension.api.R;
import lombok.experimental.UtilityClass;

import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

@UtilityClass
public class MyValidatorUtils {

      public <T> R valid(Validator validator, T t){
        Set<ConstraintViolation<T>> violations = validator.validate(t);

        Iterator<ConstraintViolation<T>> iterator = violations.iterator();
        List<String> msgList = new ArrayList<>();
        while (iterator.hasNext()) {
            ConstraintViolation<T> cvl = iterator.next();
            msgList.add(cvl.getMessageTemplate());
        }
         if (msgList.isEmpty()) {
             return null;
         }else {
             return R.failed(String.join(",", msgList));
         }
    }
}

封装的easyexcel 导入

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.api.R;
import com.my.test.eo.listen.ErrMsgEO;
import lombok.SneakyThrows;
import lombok.experimental.UtilityClass;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@UtilityClass
public class MyExcelUtils {

    public R importData(MultipartFile file,Class clazz, MyReadListener readListener){
        return baseImportData(file,clazz,readListener,2);
    }

    @SneakyThrows
    private R baseImportData(MultipartFile file,Class clazz, MyReadListener readListener,Integer headRowNumber){
        EasyExcel.read(file.getInputStream(), clazz, readListener).sheet().headRowNumber(headRowNumber).doRead();
        List<ErrMsgEO> errList = readListener.getErrList();
        return R.ok(errList);
    }
}

封装的监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.baomidou.mybatisplus.extension.api.R;
import com.my.test.eo.listen.ErrMsgEO;
import lombok.extern.slf4j.Slf4j;

import javax.validation.Validator;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public abstract class MyReadListener<T> implements ReadListener<T> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 获取要提交的缓存数据
     */
    public List<T> getCachedDataList() {
        return cachedDataList;
    }
    /**
     * 记录错误信息的list
     */
    private List<ErrMsgEO> errList = new ArrayList<>();

    /**
     * 传入的校验对象
     */
    private Validator validator;

    /**
     * 结束语，统计导入成功失败的条数
     */
    private String resultMsg;

    public List<ErrMsgEO> getErrList() {
        return errList;
    }
    public void  addErr(ErrMsgEO errMsgEO){
        getErrList().add(errMsgEO);
    }

    public String getResultMsg() {
        return resultMsg;
    }

    private void setResultMsg(String resultMsg) {
        this.resultMsg = resultMsg;
    }

    private Validator getValidator() {
        return validator;
    }

    public void setValidator(Validator validator) {
        this.validator = validator;
    }
    /**
     * 数据对象加入批量提交缓存list
     */
    public void addData(T data){
        cachedDataList.add(data);
    }

    /**
     * 校验一个数据对象，有错误就返回错误信息对象
     */
    public ErrMsgEO validOne(T data, AnalysisContext context){
        R valid = MyValidatorUtils.valid(getValidator(), data);
        if (null!=valid) {
            ErrMsgEO eo= new ErrMsgEO();
            eo.setErr(valid.getMsg());
            eo.setRowNum(context.readRowHolder().getRowIndex().toString());
            return eo;
        }else {
            return null;
        }
    }

    /**
     * 保存业务逻辑 子类实现
     */
    public abstract boolean saveData(AnalysisContext context);

    /**
     * 业务逻辑的校验
     */
    public abstract ErrMsgEO bizValid(T data);

    /**
     * 判断最后的数据是否能保存
     */
    public boolean lastCanSave(){
        return getCachedDataList().size() >0;
    }



    /**
     * 校验对象数据， 错误就加入错误信息list，正确就加入缓存数据池
     */
    public void validThenAdd(T data, AnalysisContext context){
        ErrMsgEO valid = validOne(data, context);
        if (null!=valid) {
            addErr(valid);
        }else {
            ErrMsgEO eo = bizValid(data);
            if (null!=eo){
                addErr(eo);
            }else {
                addData(data);
            }
        }
    }

    /**
     *达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
     */
    public void tryThenSave(AnalysisContext context){
        if (cachedDataList.size() >= BATCH_COUNT) {
//            批量保存
            if (saveData(context)) {
                // 存储完成清理 list
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }
    }


    /**
     *  校验然后保存
     */
    public void validThenSave(T data, AnalysisContext context) {
        validThenAdd(data, context);
        tryThenSave(context);
    }

    /**
     * 填充导入结果提示
     */
    public void fillResultMsg(AnalysisContext context){
        int errNum=getErrList().size();
        int okNum=context.readSheetHolder().getApproximateTotalRowNumber()-errNum-context.readSheetHolder().getHeadRowNumber();
        setResultMsg("成功导入："+okNum+"条数据，失败："+errNum+"条数据");
    }

    public void lastSave(AnalysisContext context){
        if (lastCanSave()) {
            saveData(context);
        }else {
            log.info("木有保存");
        }
        fillResultMsg(context);
    }
}

调用的监听器

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener extends MyReadListener<XxxEO> {



    private IMyTestService myTestService;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public DemoDataListener(IMyTestService service,Validator validator) {
        this.myTestService = service;
        super.setValidator(validator);
    }

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(XxxEO data, AnalysisContext context) {
        log.info("解析到一条数据:{}行", context.readSheetHolder().getRowIndex()+1);
        super.validThenSave(data,context);
    }

    @Override
    public ErrMsgEO bizValid(XxxEO data) {
        List<MyTest> list = myTestService.list();
        System.out.println(list);
        return null;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        super.lastSave(context);
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean saveData(AnalysisContext context) {
//            log.info("{}条数据，开始存储数据库！", super.getCachedDataList().size());
        log.info("执行保存逻辑 {}" ,context.readSheetHolder().getRowIndex());
        return false;

    }
}

控制层调用的方法

    @Autowired
    private Validator validator;

    @PostMapping("/importData")
    @ApiOperation("导入")
    public R importData(@RequestPart("file") MultipartFile file, @RequestParam("id") String id) throws Exception {
        if (id.length()>20) {
            return R.failed("长度过长");
        }
        if (!file.getOriginalFilename().contains(".xlsx")) {
            return R.failed("只能导入的xlsx文件");
        }
        DemoDataListener listener = new DemoDataListener(myTestService,validator);
        R r = MyExcelUtils.importData(file, XxxEO.class, listener);
        System.out.println(listener.getResultMsg());
        return r;
    }