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);}@SneakyThrowsprivate 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)) {// 存储完成清理 listcachedDataList = 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);}/*** 这个每一条数据解析都会来调用*/@Overridepublic void invoke(XxxEO data, AnalysisContext context) {log.info("解析到一条数据:{}行", context.readSheetHolder().getRowIndex()+1);super.validThenSave(data,context);}@Overridepublic ErrMsgEO bizValid(XxxEO data) {List<MyTest> list = myTestService.list();System.out.println(list);return null;}/*** 所有数据解析完成了 都会来调用*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {super.lastSave(context);log.info("所有数据解析完成！");}@Overridepublic boolean saveData(AnalysisContext context) {
//            log.info("{}条数据，开始存储数据库！", super.getCachedDataList().size());log.info("执行保存逻辑 {}" ,context.readSheetHolder().getRowIndex());return false;}
}

控制层调用的方法

    @Autowiredprivate 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;}