这篇文章主要讲解了“Java中easypoi导入校验的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Java中easypoi导入校验的方法是什么”吧！
一、导入之基础校验
现在产品需要对导入的Excel进行校验，不合法的Excel不允许入库，需要返回具体的错误信息给前端，提示给用户，错误信息中需要包含行号以及对应的错误。
因为 EasyPOI 支持 Hibernate Validator ，所以直接使用就可以了，因为要将错误信息以及错误行号返回，所以需要用到 EasyPOI 的高级用法，实现 IExcelDataModel与 IExcelModel接口，IExcelDataModel负责设置行号，IExcelModel 负责设置错误信息
如果使用到了 @Pattern 注解，则字段类型必须是 String 类型，否则会抛出异常
本文中的原 Integer 类型的 gender 修改成为 String 类型的 genderStr，record 字段也修改为了 String 类型的 recordStr等等
同理如果校验 Date 类型字段，先将类型改成String，正则表达式参考下文写法。也就是说原本Integer类型的
这里需要注意，如果@Excel注解中设置了 replace 属性，则Hibernate Validator 校验的是替换后的值
导出时候的实体类
@Datapublic class TalentUserInputEntity{ @Excel(name = "姓名*") private String name; @Excel(name = "性别*") private Integer gender; @Excel(name = "手机号*") private String phone; @Excel(name = "开始工作时间*") private Date workTime; @Excel(name = "民族*") private String national; @Excel(name = "语言水平*") private String languageProficiency; @Excel(name = "出生日期*") private Date birth; @Excel(name = "职位*") private String jobsName; @Excel(name = "职位类型*") private String categoryName; @Excel(name = "薪资*") private Integer salary; @Excel(name = "工作地点*") private String workArea; @ExcelCollection(name = "工作经历*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育经历*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "获奖情况") private List<AwardsInputEntity> awardList; @ExcelCollection(name = "技能证书") private List<PunishmentInputEntity> punishmentList; @Excel(name = "特长") private String specialty;}导入时候的实体类
@Datapublic class TalentUserInputEntity implements IExcelDataModel, IExcelModel { // 时间格式校验正则 public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{2}( )(00:00:00)( )(CST)( )\\d{4}"; private int rowNum; private String errorMsg; @Excel(name = "姓名*") @NotBlank(message = "[姓名]不能为空") private String name; @Excel(name = "性别*", replace = {"男_0", "女_1"}) @Pattern(regexp = "[01]", message = "性别错误") private String genderStr; @Excel(name = "手机号*") private String phone; @Excel(name = "开始工作时间*") @Pattern(regexp = DATE_REGEXP, message = "[开始工作时间]时间格式错误") private String workTimeStr; @Excel(name = "民族*") @NotBlank(message = "[民族]不能为空") private String national; @Excel(name = "语言水平*") @NotBlank(message = "[语言水平]不能为空") private String languageProficiency; @Excel(name = "出生日期*") @Pattern(regexp = DATE_REGEXP, message = "[出生日期]时间格式错误") private String birthStr; @Excel(name = "职位*") @NotBlank(message = "[职位]不能为空") private String jobsName; @Excel(name = "职位类型*") @NotBlank(message = "[职位类型]不能为空") private String categoryName; @Excel(name = "薪资*", replace = {"3K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"}) @Pattern(regexp = "[123456]", message = "薪资信息错误") private String salaryStr; @Excel(name = "工作地点*") @NotBlank(message = "[工作地点]不能为空") private String workArea; @ExcelCollection(name = "工作经历*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育经历*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "获奖情况") private List<AwardsInputEntity> awardList; @ExcelCollection(name = "技能证书") private List<PunishmentInputEntity> punishmentList; @Excel(name = "特长") private String specialty; @Override public String getErrorMsg() { return errorMsg; } @Override public void setErrorMsg(String errorMsg) { this.errorMsg = errorMsg; } @Override public Integer getRowNum() { return rowNum; } @Override public void setRowNum(Integer rowNum) { this.rowNum = rowNum; } // 工作经历@Datapublic class ExperienceInputEntity { @Excel(name = "公司名称*") private String companyName; @Excel(name = "所在行业*") private String industry; @Excel(name = "开始时间*") @Pattern(regexp = DATE_REGEXP, message = "[工作经历][开始时间]时间格式错误") private String beginTimeStr; @Excel(name = "结束时间*") @Pattern(regexp = DATE_REGEXP, message = "[工作经历][结束时间]时间格式错误") private String finishTimeStr; @Excel(name = "职位名称*") private String jobTitle; @Excel(name = "所属部门*") private String department; @Excel(name = "工作内容*") private String description;}// 教育经历@Datapublic class EducationInputEntity { @Excel(name = "学校*") private String schoolName; @Excel(name = "学历*", replace = {"初中及以下_1", "中专_2", "高中_3", "大专_4", "本科_5", "硕士_6", "博士_7"}) @Pattern(regexp = "[1234567]", message = "学历信息错误") private String recordStr; @Excel(name = "开始年份*") @Pattern(regexp = DATE_REGEXP, message = "[教育经历][开始年份]时间格式错误") private String beginTimeStr; @Excel(name = "毕业年份*") @Pattern(regexp = DATE_REGEXP, message = "[教育经历][毕业年份]时间格式错误") private String finishTimeStr; @Excel(name = "专业*") private String profession;}}二、导入值自定义校验之重复值校验
上文所作的校验只是一些基本的校验，可能会有诸如Excel中重复行校验，Excel中数据与数据库重复校验等等。这种校验就无法通过 Hibernate Validator 来完成，只能写代码来实现校验逻辑了。
首先从简单的Excel数据与数据库值重复校验开始。为了便于演示，就不引入数据库了，直接Mock一些数据用来判断是否重复。
@Servicepublic class MockTalentDataService { private static List<TalentUser> talentUsers = new ArrayList<>(); static { TalentUser u1 = new TalentUser(1L, "凌风", "18311342567"); TalentUser u2 = new TalentUser(2L, "张三", "18512343567"); TalentUser u3 = new TalentUser(3L, "李四", "18902343267"); talentUsers.add(u1); talentUsers.add(u2); talentUsers.add(u3); } public boolean checkForDuplicates(String name, String phone) { // 姓名与手机号相等个数不等于0则为重复 return talentUsers.stream().anyMatch(e -> e.getName().equals(name) && e.getPhone().equals(phone)); }}其中Mock数据中 ID 为 1 的数据与示例Excel2 中的数据是重复的。
EasyPOI 提供了校验的接口，这需要我们自己写一个用于校验的类。在这个类中，可以对导入时的每一行数据进行校验，框架通过 ExcelVerifyHandlerResult 对象来判断是否校验通过，校验不通过需要传递 ErrorMsg。
@Componentpublic class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> { @Resource private MockTalentDataService mockTalentDataService; @Override public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) { StringJoiner joiner = new StringJoiner(","); // 根据姓名与手机号判断数据是否重复 String name = inputEntity.getName(); String phone = inputEntity.getPhone(); // mock 数据库 boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone); if (duplicates) { joiner.add("数据与数据库数据重复"); } if (joiner.length() != 0) { return new ExcelVerifyHandlerResult(false, joiner.toString()); } return new ExcelVerifyHandlerResult(true); }}修改校验处代码，设置校验类对象。
@Resourceprivate TalentImportVerifyHandler talentImportVerifyHandler;@PostMapping("/upload")public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ImportParams params = new ImportParams(); // 表头设置为2行 params.setHeadRows(2); // 行设置为0行，默认是0，可以不设置 params.setTitleRows(0); // 开启Excel校验 params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); System.out.println("是否校验失败: " + result.isVerfiyFail()); System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList())); for (TalentUserInputEntity entity : result.getFailList()) { int line = entity.getRowNum() + 1; String msg = "第" + line + "行的错误是：" + entity.getErrorMsg(); System.out.println(msg); } return true;}上传 示例Excel2 文件测试，结果输出：
而第七行的数据正是与Mock中的数据相重复的。
三、导入值自定义校验之Collection对象校验
上文中还有一个待解决的问题，就是Collection中的对象添加了Hibernate Validator 注解校验但是并未生效的问题，现在就来解决一下。上一步中实现了导入对象的校验类，校验类会校验Excel中的每一条数据， 那我是不是可以直接在校验类中校验Collection中对象了呢？实践证明行不通，因为这个校验类的verifyHandler方法只会被调用一次，所以Collection中只有一条记录。既然这里行不通的话，就只能对导入结果再进行校验了。
因为Collection中的数据EasyPOI校验不到，所以有问题的数据也可能会被框架放到result.getList()中而不是result.getFailList() 中，为了校验需要将两个集合合并为一个集合，使用 EasyPOI 自带的工具类 PoiValidationUtil 进行校验 Collection 中的对象。
@Resourceprivate TalentImportVerifyHandler talentImportVerifyHandler;@PostMapping("/upload")public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ImportParams params = new ImportParams(); // 表头设置为2行 params.setHeadRows(2); // 行设置为0行，默认是0，可以不设置 params.setTitleRows(0); // 开启Excel校验 params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); System.out.println("是否校验失败: " + result.isVerfiyFail()); System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList())); // 合并结果集 List<TalentUserInputEntity> resultList = new ArrayList<>(); resultList.addAll(result.getFailList()); resultList.addAll(result.getList()); for (TalentUserInputEntity inputEntity : resultList) { StringJoiner joiner = new StringJoiner(","); joiner.add(inputEntity.getErrorMsg()); // 校验Collection的元素 inputEntity.getExperienceList().forEach(e -> verify(joiner, e)); inputEntity.getEducationList().forEach(e -> verify(joiner, e)); inputEntity.getAwardList().forEach(e -> verify(joiner, e)); inputEntity.getPunishmentList().forEach(e -> verify(joiner, e)); inputEntity.setErrorMsg(joiner.toString()); } for (TalentUserInputEntity entity : result.getFailList()) { int line = entity.getRowNum() + 1; String msg = "第" + line + "行的错误是：" + entity.getErrorMsg(); System.out.println(msg); } return true;}private void verify(StringJoiner joiner, Object object) { String validationMsg = PoiValidationUtil.validation(object, null); if (StringUtils.isNotEmpty(validationMsg)) { joiner.add(validationMsg); }}上传 示例Excel2 ，结果如下：
四、导入值自定义校验之Excel重复行校验
上文中对Excel中数据与数据库数据进行重复校验，可有些需求是要求数据库在入库前需要对Excel的的重复行进行校验。这需要在校验类中完成，但校验类中并没有全部行的数据，该如何实现呢？博主的做法是将导入的数据放到 ThreadLocal 中进行暂存，从而达到在校验类中校验Excel重复行的目的。ThreadLocal使用注意完之后一定要及时清理！
首先定义什么叫重复行，完全相同的两行是重复行，本文中设定name 与 phone 相同的行为重复行，由于只需要比较这两个字段，所以我们需要重写导入对象的equals与hashCode方法。
@Datapublic class TalentUserInputEntity implements IExcelDataModel, IExcelModel { // 时间格式校验正则 public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{2}( )(00:00:00)( )(CST)( )\\d{4}"; private int rowNum; private String errorMsg; @Excel(name = "姓名*") @NotBlank(message = "[姓名]不能为空") private String name; @Excel(name = "性别*", replace = {"男_0", "女_1"}) @Pattern(regexp = "[01]", message = "性别错误") private String genderStr; @Excel(name = "手机号*") @Pattern(regexp = "[0-9]{11}", message = "手机号不正确") private String phone; @Excel(name = "开始工作时间*") @Pattern(regexp = DATE_REGEXP, message = "[开始工作时间]时间格式错误") private String workTimeStr; @Excel(name = "民族*") @NotBlank(message = "[民族]不能为空") private String national; @Excel(name = "语言水平*") @NotBlank(message = "[语言水平]不能为空") private String languageProficiency; @Excel(name = "出生日期*") @Pattern(regexp = DATE_REGEXP, message = "[出生日期]时间格式错误") private String birthStr; @Excel(name = "职位*") @NotBlank(message = "[职位]不能为空") private String jobsName; @Excel(name = "职位类型*") @NotBlank(message = "[职位类型]不能为空") private String categoryName; @Excel(name = "薪资*", replace = {"3K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"}) @Pattern(regexp = "[123456]", message = "薪资信息错误") private String salaryStr; @Excel(name = "工作地点*") @NotBlank(message = "[工作地点]不能为空") private String workArea; @ExcelCollection(name = "工作经历*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育经历*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "获奖情况") private List<AwardsInputEntity> awardList; @ExcelCollection(name = "技能证书") private List<PunishmentInputEntity> punishmentList; @Excel(name = "特长") private String specialty; @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; TalentUserInputEntity that = (TalentUserInputEntity) o; return Objects.equals(name, that.name) && Objects.equals(phone, that.phone); } @Override public int hashCode() { return Objects.hash(name, phone); } @Override public String getErrorMsg() { return errorMsg; } @Override public void setErrorMsg(String errorMsg) { this.errorMsg = errorMsg; } @Override public Integer getRowNum() { return rowNum; } @Override public void setRowNum(Integer rowNum) { this.rowNum = rowNum; }}修改校验类代码，实现重复行的校验逻辑
@Componentpublic class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> { private final ThreadLocal<List<TalentUserInputEntity>> threadLocal = new ThreadLocal<>(); @Resource private MockTalentDataService mockTalentDataService; @Override public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) { StringJoiner joiner = new StringJoiner(","); // 根据姓名与手机号判断数据是否重复 String name = inputEntity.getName(); String phone = inputEntity.getPhone(); // mock 数据库 boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone); if (duplicates) { joiner.add("数据与数据库数据重复"); } List<TalentUserInputEntity> threadLocalVal = threadLocal.get(); if (threadLocalVal == null) { threadLocalVal = new ArrayList<>(); } threadLocalVal.forEach(e -> { if (e.equals(inputEntity)) { int lineNumber = e.getRowNum() + 1; joiner.add("数据与第" + lineNumber + "行重复"); } }); // 添加本行数据对象到ThreadLocal中 threadLocalVal.add(inputEntity); threadLocal.set(threadLocalVal); if (joiner.length() != 0) { return new ExcelVerifyHandlerResult(false, joiner.toString()); } return new ExcelVerifyHandlerResult(true); } public ThreadLocal<List<TalentUserInputEntity>> getThreadLocal() { return threadLocal; }}由于校验类中使用了ThreadLocal，因此需要及时释放，修改导入处的代码。
@Resourceprivate TalentImportVerifyHandler talentImportVerifyHandler;@PostMapping("/upload")public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ExcelImportResult<TalentUserInputEntity> result; try { ImportParams params = new ImportParams(); // 表头设置为2行 params.setHeadRows(2); // 行设置为0行，默认是0，可以不设置 params.setTitleRows(0); // 开启Excel校验 params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); } finally { // 清除threadLocal 防止内存泄漏 ThreadLocal<List<TalentUserInputEntity>> threadLocal = talentImportVerifyHandler.getThreadLocal(); if (threadLocal != null) { threadLocal.remove(); } } System.out.println("是否校验失败: " + result.isVerfiyFail()); System.out.println("校验失败的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校验通过的集合:" + JSONObject.toJSONString(result.getList())); // 合并结果集 List<TalentUserInputEntity> resultList = new ArrayList<>(); resultList.addAll(result.getFailList()); resultList.addAll(result.getList()); for (TalentUserInputEntity inputEntity : resultList) { StringJoiner joiner = new StringJoiner(","); joiner.add(inputEntity.getErrorMsg()); // 校验Collection的元素 inputEntity.getExperienceList().forEach(e -> verify(joiner, e)); inputEntity.getEducationList().forEach(e -> verify(joiner, e)); inputEntity.getAwardList().forEach(e -> verify(joiner, e)); inputEntity.getPunishmentList().forEach(e -> verify(joiner, e)); inputEntity.setErrorMsg(joiner.toString()); } for (TalentUserInputEntity entity : result.getFailList()) { int line = entity.getRowNum() + 1; String msg = "第" + line + "行的错误是：" + entity.getErrorMsg(); System.out.println(msg); } return true;}private void verify(StringJoiner joiner, Object object) { String validationMsg = PoiValidationUtil.validation(object, null); if (StringUtils.isNotEmpty(validationMsg)) { joiner.add(validationMsg); }}导入示例Excel2，结果如下：
五、案例
实体类
CourseEntity.java
package com.mye.hl11easypoi.api.pojo;import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelCollection;import cn.afterturn.easypoi.excel.annotation.ExcelEntity;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;import cn.afterturn.easypoi.handler.inter.IExcelDataModel;import cn.afterturn.easypoi.handler.inter.IExcelModel;import lombok.Data;import java.util.List;@Data@ExcelTarget("courseEntity")public class CourseEntity implements java.io.Serializable, IExcelModel, IExcelDataModel { private String id; @Excel(name = "课程名称", orderNum = "0", width = 25, needMerge = true) private String name; // @ExcelEntity(id = "major") private TeacherEntity chineseTeacher; @ExcelEntity(id = "absent") private TeacherEntity mathTeacher; @ExcelCollection(name = "学生", orderNum = "3") private List<StudentEntity> students; private String errorMsg; //自定义一个errorMsg接受下面重写IExcelModel接口的get和setErrorMsg方法。 private Integer rowNum; //自定义一个rowNum接受下面重写IExcelModel接口的get和setRowNum方法。 @Override public String getErrorMsg() { return errorMsg; } @Override public void setErrorMsg(String errorMsg) { this.errorMsg = errorMsg; } @Override public Integer getRowNum() { return rowNum; } @Override public void setRowNum(Integer rowNum) { this.rowNum = rowNum; }}StudentEntity.java
package com.mye.hl11easypoi.api.pojo;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.util.Date;@Datapublic class StudentEntity implements java.io.Serializable { private String id; @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true") private String name; @Excel(name = "学生性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true") private int sex; @Excel(name = "出生日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width = 20) private Date birthday; @Excel(name = "进校日期", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd") private Date registrationDate;}TeacherEntity.java
package com.mye.hl11easypoi.api.pojo;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;@Datapublic class TeacherEntity { @Excel(name = "教师姓名", width = 30, orderNum = "1" ,isImportField = "true") private String name; @Excel(name = "教师性别", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true",orderNum = "2") private int sex;}自定义校验类
package com.mye.hl11easypoi.api.verifyHandler;import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;import com.mye.hl11easypoi.api.pojo.CourseEntity;public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> { @Override public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) { ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(); //假设我们要添加用户， //现在去数据库查询getName，如果存在则表示校验不通过。 //假设现在数据库中有个getName 测试课程 if ("测试课程".equals(courseEntity.getName())) { result.setMsg("该课程已存在"); result.setSuccess(false); return result; } result.setSuccess(true); return result; }}测试类
package com.mye.hl11easypoi;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.ImportParams;import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;import cn.afterturn.easypoi.excel.imports.ExcelImportService;import cn.hutool.json.JSONUtil;import com.mye.hl11easypoi.api.pojo.*;import com.mye.hl11easypoi.api.verifyHandler.MyVerifyHandler;import org.apache.poi.ss.usermodel.Workbook;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.util.*;@SpringBootTest(classes = Hl11EasypoiApplication.class)@RunWith(SpringRunner.class)public class TestPOI { @Test public void testExportExcel() throws Exception { List<CourseEntity> courseEntityList = new ArrayList<>(); CourseEntity courseEntity = new CourseEntity(); courseEntity.setId("1"); courseEntity.setName("测试课程"); // 第二个 CourseEntity courseEntity1 = new CourseEntity(); courseEntity1.setId("2"); courseEntity1.setName("数学"); TeacherEntity teacherEntity1 = new TeacherEntity(); teacherEntity1.setSex(1); teacherEntity1.setName("李老师"); TeacherEntity teacherEntity = new TeacherEntity(); teacherEntity.setName("张老师"); teacherEntity.setSex(1); courseEntity.setMathTeacher(teacherEntity); courseEntity1.setMathTeacher(teacherEntity1); List<StudentEntity> studentEntities = new ArrayList<>(); for (int i = 1; i <= 2; i++) { StudentEntity studentEntity = new StudentEntity(); studentEntity.setName("学生" + i); studentEntity.setSex(i); studentEntity.setBirthday(new Date()); studentEntities.add(studentEntity); } courseEntity.setStudents(studentEntities); courseEntity1.setStudents(studentEntities); courseEntityList.add(courseEntity); courseEntityList.add(courseEntity1); System.out.println(courseEntityList+"11111111111111"); Date start = new Date(); Workbook workbook = ExcelExportUtil.exportExcel( new ExportParams("导出测试", null, "测试"), CourseEntity.class, courseEntityList); System.out.println(new Date().getTime() - start.getTime()); File savefile = new File("E:/desktop/excel/"); if (!savefile.exists()) { savefile.mkdirs(); } FileOutputStream fos = new FileOutputStream("E:/desktop/excel/教师课程学生导出测试.xls"); workbook.write(fos); fos.close(); } @Test public void testImport2() throws Exception { // 参数1：导入excel文件流 参数2：导入类型 参数3：导入的配置对象 ImportParams importParams = new ImportParams(); importParams.setTitleRows(1); // 设置列占几行 importParams.setHeadRows(2); // 设置字段名称占几行 即header importParams.setNeedVerify(true);//开启校验 importParams.setVerifyHandler(new MyVerifyHandler()); importParams.setStartSheetIndex(0); // 设置从第几张表格开始读取，这里0代表第一张表，默认从第一张表读取 BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File("E:/desktop/excel/教师课程学生导出测试.xls"))); ExcelImportResult result = new ExcelImportService().importExcelByIs(bis, CourseEntity.class, importParams, true); //这个是正确导入的 List<CourseEntity> list = result.getList(); System.out.println("成功导入的集合："+JSONUtil.toJsonStr(list)); List<CourseEntity> failList = result.getFailList(); System.out.println("失败导入的集合"+JSONUtil.toJsonStr(failList)); for (CourseEntity courseEntity : failList) { int line = courseEntity.getRowNum(); String msg = "第" + line + "行的错误是:" + courseEntity.getErrorMsg(); System.out.println(msg); } //将错误excel信息返回给客户端 ExportParams exportParams = new ExportParams(); Workbook workbook = ExcelExportUtil.exportExcel(exportParams, CourseEntity.class, failList);// HttpServletResponse response = null;// response.setHeader("content-Type", "application/vnd.ms-excel");// response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户数据表","UTF-8") + ".xls");// response.setCharacterEncoding("UTF-8");// workbook.write(response.getOutputStream()); FileOutputStream fos = new FileOutputStream("E:/desktop/excel/用户数据表.xls"); workbook.write(fos); fos.close(); }}导出结果
导入结果
感谢各位的阅读，以上就是“Java中easypoi导入校验的方法是什么”的内容了，经过本文的学习后，相信大家对Java中easypoi导入校验的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！
