引入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 > { 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) ; } @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 ( "执行保存逻辑 {}" , 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; }