Easypoi Excel导入校验 两种方式
案例一
用JSR 303校验
所用数据
结果
Controller层
@CrossOrigin@PostMapping("/importStudentVerify")publicStringimportStudentVerify(@RequestParam("file")MultipartFilefile,HttpServletResponseresponse)throwsException{if(file.isEmpty()){thrownewException("error file is empty");}ImportParamsparams=newImportParams();//这里也要设置，否则会有异常params.setTitleRows(0);params.setHeadRows(1);params.setNeedVerify(true);//这里结合JSR303用不用自定义的，下一案例用自定义的//params.setVerifyHandler(verifyResult);//这里要用importExcelMore方法ExcelImportResult<StudentImportVerifyEntity2>studentImportEntitiesResult=ExcelImportUtil.importExcelMore(file.getInputStream(),StudentImportVerifyEntity2.class,params);//获取失败的数据List<StudentImportVerifyEntity2>failStudentsImport=studentImportEntitiesResult.getFailList();System.out.println(failStudentsImport);List<StudentImportVerifyEntity2>studentImportEntities=studentImportEntitiesResult.getList();//没有失败的if(CollectionUtil.isEmpty(failStudentsImport)){List<Student>students=studentImportEntities.stream().map(e->{Studentstudent=newStudent();student.setName(e.getName());student.setSex(e.getSex());// 创建一个LocalTime实例，例如午夜12点LocalTimelocalTime=LocalTime.MIDNIGHT;LocalDatebirthDayLocalDate=LocalDate.parse(e.getBirthDay(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));//LocalDate转LocalDateTimeLocalDateTimebirthDayLocalDateTime=LocalDateTime.of(birthDayLocalDate,localTime);student.setBirthDay(birthDayLocalDateTime);LocalDateregistrationLocalDate=LocalDate.parse(e.getRegistrationDate(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));LocalDateTimeregistrationLocalDateTime=LocalDateTime.of(registrationLocalDate,localTime);student.setRegistrationDate(registrationLocalDateTime);returnstudent;}).collect(Collectors.toList());studentService.saveBatch(students);return"成功";}return"excel有错误数据";}StudentImportVerifyEntity2
//这里要实现IExcelModel接口才有errorMsg输出@DatapublicclassStudentImportVerifyEntity2implementsjava.io.Serializable,IExcelModel{/** * 学生姓名 */@Excel(name="学生姓名",orderNum="0")//这里要加上JSR 303校验@NotNull(message="学生姓名不能为空")privateStringname;/** * 学生性别 */@Excel(name="学生性别",replace={"男_1","女_0"},orderNum="1",suffix="生")privateintsex;@Excel(name="出生日期",orderNum="2",importFormat="yyyy-MM-dd")privateStringbirthDay;@Excel(name="进校日期",orderNum="3",importFormat="yyyy-MM-dd")privateStringregistrationDate;privateStringerrorMsg;@OverridepublicStringgetErrorMsg(){returnerrorMsg;}@OverridepublicvoidsetErrorMsg(StringerrorMsg){this.errorMsg=errorMsg;}}案例二
自定义校验处理器
处理器：
@ServicepublicclassVerifyResultimplementsIExcelVerifyHandler<StudentImportVerifyEntity>{@OverridepublicExcelVerifyHandlerResultverifyHandler(StudentImportVerifyEntitystudentImportVerifyEntity){ExcelVerifyHandlerResultresult=newExcelVerifyHandlerResult(true);if(Objects.isNull(studentImportVerifyEntity.getName())){result.setSuccess(false);result.setMsg("姓名不能为空");returnresult;}result.setSuccess(true);returnresult;}}Controller层
@ResourceprivateVerifyResultverifyResult;@CrossOrigin@PostMapping("/importStudentVerify")publicStringimportStudentVerify(@RequestParam("file")MultipartFilefile,HttpServletResponseresponse)throwsException{if(file.isEmpty()){thrownewException("error file is empty");}ImportParamsparams=newImportParams();//这里也要设置，否则会有异常params.setTitleRows(0);params.setHeadRows(1);params.setNeedVerify(true);//这里设置处理器params.setVerifyHandler(verifyResult);//注意这里用的entity是StudentImportVerifyEntityExcelImportResult<StudentImportVerifyEntity>studentImportEntitiesResult=ExcelImportUtil.importExcelMore(file.getInputStream(),StudentImportVerifyEntity.class,params);//获取失败的数据List<StudentImportVerifyEntity>failStudentsImport=studentImportEntitiesResult.getFailList();System.out.println(failStudentsImport);List<StudentImportVerifyEntity>studentImportEntities=studentImportEntitiesResult.getList();//没有失败的if(CollectionUtil.isEmpty(failStudentsImport)){List<Student>students=studentImportEntities.stream().map(e->{Studentstudent=newStudent();student.setName(e.getName());student.setSex(e.getSex());// 创建一个LocalTime实例，例如午夜12点LocalTimelocalTime=LocalTime.MIDNIGHT;LocalDatebirthDayLocalDate=LocalDate.parse(e.getBirthDay(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));//LocalDate转LocalDateTimeLocalDateTimebirthDayLocalDateTime=LocalDateTime.of(birthDayLocalDate,localTime);student.setBirthDay(birthDayLocalDateTime);LocalDateregistrationLocalDate=LocalDate.parse(e.getRegistrationDate(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));LocalDateTimeregistrationLocalDateTime=LocalDateTime.of(registrationLocalDate,localTime);student.setRegistrationDate(registrationLocalDateTime);returnstudent;}).collect(Collectors.toList());studentService.saveBatch(students);return"成功";}return"excel有错误数据";}StudentImportVerifyEntity
@DatapublicclassStudentImportVerifyEntityimplementsIExcelDataModel,IExcelModel,java.io.Serializable{/** * 学生姓名 */@Excel(name="学生姓名",orderNum="0")//JSR 303的注解注掉了走的是自定义处理器//@NotNull(message = "学生姓名不能为空")privateStringname;/** * 学生性别 */@Excel(name="学生性别",replace={"男_1","女_0"},orderNum="1",suffix="生")privateintsex;@Excel(name="出生日期",orderNum="2",importFormat="yyyy-MM-dd")privateStringbirthDay;@Excel(name="进校日期",orderNum="3",importFormat="yyyy-MM-dd")privateStringregistrationDate;/** * 行号 */privateintrowNum;/** * 错误消息 */privateStringerrorMsg;@OverridepublicStringgetErrorMsg(){returnerrorMsg;}@OverridepublicvoidsetErrorMsg(StringerrorMsg){this.errorMsg=errorMsg;}@OverridepublicintgetRowNum(){returnrowNum;}@OverridepublicvoidsetRowNum(inti){this.rowNum=rowNum;}}
pom引用
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>5.2.4.Final</version></dependency><dependency><groupId>javax.el</groupId><artifactId>javax.el-api</artifactId><version>3.0.0</version></dependency><dependency><groupId>org.glassfish.web</groupId><artifactId>javax.el</artifactId><version>2.2.6</version></dependency>