引入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> {private static final int BATCH_COUNT = 5;private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);public List<T> getCachedDataList() {return cachedDataList;}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;}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;}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);}}}public void tryThenSave(AnalysisContext context){if (cachedDataList.size() >= BATCH_COUNT) {
if (saveData(context)) {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);}
}
调用的监听器
@Slf4j
public class DemoDataListener extends MyReadListener<XxxEO> {private IMyTestService myTestService;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("执行保存逻辑 {}" ,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;}