您好,登錄后才能下訂單哦!
這篇文章主要講解了“Java中easypoi導(dǎo)入校驗(yàn)的方法是什么”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“Java中easypoi導(dǎo)入校驗(yàn)的方法是什么”吧!
現(xiàn)在產(chǎn)品需要對導(dǎo)入的Excel進(jìn)行校驗(yàn),不合法的Excel不允許入庫,需要返回具體的錯(cuò)誤信息給前端,提示給用戶,錯(cuò)誤信息中需要包含行號以及對應(yīng)的錯(cuò)誤。
因?yàn)?EasyPOI 支持 Hibernate Validator ,所以直接使用就可以了,因?yàn)橐獙㈠e(cuò)誤信息以及錯(cuò)誤行號返回,所以需要用到 EasyPOI 的高級用法,實(shí)現(xiàn) IExcelDataModel
與 IExcelModel
接口,IExcelDataModel
負(fù)責(zé)設(shè)置行號,IExcelModel
負(fù)責(zé)設(shè)置錯(cuò)誤信息
如果使用到了 @Pattern 注解,則字段類型必須是 String 類型,否則會拋出異常
本文中的原 Integer
類型的 gender
修改成為 String
類型的 genderStr
,record
字段也修改為了 String 類型的 recordStr
等等
同理如果校驗(yàn) Date 類型字段,先將類型改成String,正則表達(dá)式參考下文寫法。也就是說原本Integer
類型的
這里需要注意,如果@Excel注解中設(shè)置了 replace
屬性,則Hibernate Validator 校驗(yàn)的是替換后的值
導(dǎo)出時(shí)候的實(shí)體類
@Data public class TalentUserInputEntity{ @Excel(name = "姓名*") private String name; @Excel(name = "性別*") private Integer gender; @Excel(name = "手機(jī)號*") private String phone; @Excel(name = "開始工作時(shí)間*") 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 = "工作地點(diǎn)*") private String workArea; @ExcelCollection(name = "工作經(jīng)歷*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育經(jīng)歷*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "獲獎(jiǎng)情況") private List<AwardsInputEntity> awardList; @ExcelCollection(name = "技能證書") private List<PunishmentInputEntity> punishmentList; @Excel(name = "特長") private String specialty; }
導(dǎo)入時(shí)候的實(shí)體類
@Data public class TalentUserInputEntity implements IExcelDataModel, IExcelModel { // 時(shí)間格式校驗(yàn)正則 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; /** * 錯(cuò)誤消息 */ private String errorMsg; @Excel(name = "姓名*") @NotBlank(message = "[姓名]不能為空") private String name; @Excel(name = "性別*", replace = {"男_0", "女_1"}) @Pattern(regexp = "[01]", message = "性別錯(cuò)誤") private String genderStr; @Excel(name = "手機(jī)號*") private String phone; @Excel(name = "開始工作時(shí)間*") @Pattern(regexp = DATE_REGEXP, message = "[開始工作時(shí)間]時(shí)間格式錯(cuò)誤") private String workTimeStr; @Excel(name = "民族*") @NotBlank(message = "[民族]不能為空") private String national; @Excel(name = "語言水平*") @NotBlank(message = "[語言水平]不能為空") private String languageProficiency; @Excel(name = "出生日期*") @Pattern(regexp = DATE_REGEXP, message = "[出生日期]時(shí)間格式錯(cuò)誤") 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 = "薪資信息錯(cuò)誤") private String salaryStr; @Excel(name = "工作地點(diǎn)*") @NotBlank(message = "[工作地點(diǎn)]不能為空") private String workArea; @ExcelCollection(name = "工作經(jīng)歷*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育經(jīng)歷*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "獲獎(jiǎng)情況") 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; } // 工作經(jīng)歷 @Data public class ExperienceInputEntity { @Excel(name = "公司名稱*") private String companyName; @Excel(name = "所在行業(yè)*") private String industry; @Excel(name = "開始時(shí)間*") @Pattern(regexp = DATE_REGEXP, message = "[工作經(jīng)歷][開始時(shí)間]時(shí)間格式錯(cuò)誤") private String beginTimeStr; @Excel(name = "結(jié)束時(shí)間*") @Pattern(regexp = DATE_REGEXP, message = "[工作經(jīng)歷][結(jié)束時(shí)間]時(shí)間格式錯(cuò)誤") private String finishTimeStr; @Excel(name = "職位名稱*") private String jobTitle; @Excel(name = "所屬部門*") private String department; @Excel(name = "工作內(nèi)容*") private String description; } // 教育經(jīng)歷 @Data public class EducationInputEntity { @Excel(name = "學(xué)校*") private String schoolName; @Excel(name = "學(xué)歷*", replace = {"初中及以下_1", "中專_2", "高中_3", "大專_4", "本科_5", "碩士_6", "博士_7"}) @Pattern(regexp = "[1234567]", message = "學(xué)歷信息錯(cuò)誤") private String recordStr; @Excel(name = "開始年份*") @Pattern(regexp = DATE_REGEXP, message = "[教育經(jīng)歷][開始年份]時(shí)間格式錯(cuò)誤") private String beginTimeStr; @Excel(name = "畢業(yè)年份*") @Pattern(regexp = DATE_REGEXP, message = "[教育經(jīng)歷][畢業(yè)年份]時(shí)間格式錯(cuò)誤") private String finishTimeStr; @Excel(name = "專業(yè)*") private String profession; } }
上文所作的校驗(yàn)只是一些基本的校驗(yàn),可能會有諸如Excel中重復(fù)行校驗(yàn),Excel中數(shù)據(jù)與數(shù)據(jù)庫重復(fù)校驗(yàn)等等。這種校驗(yàn)就無法通過 Hibernate Validator 來完成,只能寫代碼來實(shí)現(xiàn)校驗(yàn)邏輯了。
首先從簡單的Excel數(shù)據(jù)與數(shù)據(jù)庫值重復(fù)校驗(yàn)開始。為了便于演示,就不引入數(shù)據(jù)庫了,直接Mock一些數(shù)據(jù)用來判斷是否重復(fù)。
@Service public class MockTalentDataService { private static List<TalentUser> talentUsers = new ArrayList<>(); static { TalentUser u1 = new TalentUser(1L, "凌風(fēng)", "18311342567"); TalentUser u2 = new TalentUser(2L, "張三", "18512343567"); TalentUser u3 = new TalentUser(3L, "李四", "18902343267"); talentUsers.add(u1); talentUsers.add(u2); talentUsers.add(u3); } /** * 校驗(yàn)是否重復(fù) */ public boolean checkForDuplicates(String name, String phone) { // 姓名與手機(jī)號相等個(gè)數(shù)不等于0則為重復(fù) return talentUsers.stream().anyMatch(e -> e.getName().equals(name) && e.getPhone().equals(phone)); } }
其中Mock數(shù)據(jù)中 ID 為 1 的數(shù)據(jù)與示例Excel2 中的數(shù)據(jù)是重復(fù)的。
EasyPOI 提供了校驗(yàn)的接口,這需要我們自己寫一個(gè)用于校驗(yàn)的類。在這個(gè)類中,可以對導(dǎo)入時(shí)的每一行數(shù)據(jù)進(jìn)行校驗(yàn),框架通過 ExcelVerifyHandlerResult
對象來判斷是否校驗(yàn)通過,校驗(yàn)不通過需要傳遞 ErrorMsg
。
@Component public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> { @Resource private MockTalentDataService mockTalentDataService; @Override public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) { StringJoiner joiner = new StringJoiner(","); // 根據(jù)姓名與手機(jī)號判斷數(shù)據(jù)是否重復(fù) String name = inputEntity.getName(); String phone = inputEntity.getPhone(); // mock 數(shù)據(jù)庫 boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone); if (duplicates) { joiner.add("數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)重復(fù)"); } if (joiner.length() != 0) { return new ExcelVerifyHandlerResult(false, joiner.toString()); } return new ExcelVerifyHandlerResult(true); } }
修改校驗(yàn)處代碼,設(shè)置校驗(yàn)類對象。
@Resource private TalentImportVerifyHandler talentImportVerifyHandler; @PostMapping("/upload") public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ImportParams params = new ImportParams(); // 表頭設(shè)置為2行 params.setHeadRows(2); // 標(biāo)題行設(shè)置為0行,默認(rèn)是0,可以不設(shè)置 params.setTitleRows(0); // 開啟Excel校驗(yàn) params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); System.out.println("是否校驗(yàn)失敗: " + result.isVerfiyFail()); System.out.println("校驗(yàn)失敗的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校驗(yàn)通過的集合:" + JSONObject.toJSONString(result.getList())); for (TalentUserInputEntity entity : result.getFailList()) { int line = entity.getRowNum() + 1; String msg = "第" + line + "行的錯(cuò)誤是:" + entity.getErrorMsg(); System.out.println(msg); } return true; }
上傳 示例Excel2 文件測試,結(jié)果輸出:
而第七行的數(shù)據(jù)正是與Mock中的數(shù)據(jù)相重復(fù)的。
上文中還有一個(gè)待解決的問題,就是Collection中的對象添加了Hibernate Validator 注解校驗(yàn)但是并未生效的問題,現(xiàn)在就來解決一下。上一步中實(shí)現(xiàn)了導(dǎo)入對象的校驗(yàn)類,校驗(yàn)類會校驗(yàn)Excel中的每一條數(shù)據(jù), 那我是不是可以直接在校驗(yàn)類中校驗(yàn)Collection中對象了呢?實(shí)踐證明行不通,因?yàn)檫@個(gè)校驗(yàn)類的verifyHandler方法只會被調(diào)用一次,所以Collection中只有一條記錄。既然這里行不通的話,就只能對導(dǎo)入結(jié)果再進(jìn)行校驗(yàn)了。
因?yàn)镃ollection中的數(shù)據(jù)EasyPOI校驗(yàn)不到,所以有問題的數(shù)據(jù)也可能會被框架放到result.getList()中而不是result.getFailList() 中,為了校驗(yàn)需要將兩個(gè)集合合并為一個(gè)集合,使用 EasyPOI 自帶的工具類 PoiValidationUtil 進(jìn)行校驗(yàn) Collection 中的對象。
@Resource private TalentImportVerifyHandler talentImportVerifyHandler; @PostMapping("/upload") public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ImportParams params = new ImportParams(); // 表頭設(shè)置為2行 params.setHeadRows(2); // 標(biāo)題行設(shè)置為0行,默認(rèn)是0,可以不設(shè)置 params.setTitleRows(0); // 開啟Excel校驗(yàn) params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); System.out.println("是否校驗(yàn)失敗: " + result.isVerfiyFail()); System.out.println("校驗(yàn)失敗的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校驗(yàn)通過的集合:" + JSONObject.toJSONString(result.getList())); // 合并結(jié)果集 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()); // 校驗(yàn)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 + "行的錯(cuò)誤是:" + 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 ,結(jié)果如下:
上文中對Excel中數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)進(jìn)行重復(fù)校驗(yàn),可有些需求是要求數(shù)據(jù)庫在入庫前需要對Excel的的重復(fù)行進(jìn)行校驗(yàn)。這需要在校驗(yàn)類中完成,但校驗(yàn)類中并沒有全部行的數(shù)據(jù),該如何實(shí)現(xiàn)呢?博主的做法是將導(dǎo)入的數(shù)據(jù)放到 ThreadLocal 中進(jìn)行暫存,從而達(dá)到在校驗(yàn)類中校驗(yàn)Excel重復(fù)行的目的。ThreadLocal使用注意完之后一定要及時(shí)清理!
首先定義什么叫重復(fù)行,完全相同的兩行是重復(fù)行,本文中設(shè)定name 與 phone 相同的行為重復(fù)行,由于只需要比較這兩個(gè)字段,所以我們需要重寫導(dǎo)入對象的equals與hashCode方法。
@Data public class TalentUserInputEntity implements IExcelDataModel, IExcelModel { // 時(shí)間格式校驗(yàn)正則 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; /** * 錯(cuò)誤消息 */ private String errorMsg; @Excel(name = "姓名*") @NotBlank(message = "[姓名]不能為空") private String name; @Excel(name = "性別*", replace = {"男_0", "女_1"}) @Pattern(regexp = "[01]", message = "性別錯(cuò)誤") private String genderStr; @Excel(name = "手機(jī)號*") @Pattern(regexp = "[0-9]{11}", message = "手機(jī)號不正確") private String phone; @Excel(name = "開始工作時(shí)間*") @Pattern(regexp = DATE_REGEXP, message = "[開始工作時(shí)間]時(shí)間格式錯(cuò)誤") private String workTimeStr; @Excel(name = "民族*") @NotBlank(message = "[民族]不能為空") private String national; @Excel(name = "語言水平*") @NotBlank(message = "[語言水平]不能為空") private String languageProficiency; @Excel(name = "出生日期*") @Pattern(regexp = DATE_REGEXP, message = "[出生日期]時(shí)間格式錯(cuò)誤") 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 = "薪資信息錯(cuò)誤") private String salaryStr; @Excel(name = "工作地點(diǎn)*") @NotBlank(message = "[工作地點(diǎn)]不能為空") private String workArea; @ExcelCollection(name = "工作經(jīng)歷*") private List<ExperienceInputEntity> experienceList; @ExcelCollection(name = "教育經(jīng)歷*") private List<EducationInputEntity> educationList; @ExcelCollection(name = "獲獎(jiǎng)情況") 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; } }
修改校驗(yàn)類代碼,實(shí)現(xiàn)重復(fù)行的校驗(yàn)邏輯
@Component public 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(","); // 根據(jù)姓名與手機(jī)號判斷數(shù)據(jù)是否重復(fù) String name = inputEntity.getName(); String phone = inputEntity.getPhone(); // mock 數(shù)據(jù)庫 boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone); if (duplicates) { joiner.add("數(shù)據(jù)與數(shù)據(jù)庫數(shù)據(jù)重復(fù)"); } 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("數(shù)據(jù)與第" + lineNumber + "行重復(fù)"); } }); // 添加本行數(shù)據(jù)對象到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; } }
由于校驗(yàn)類中使用了ThreadLocal,因此需要及時(shí)釋放,修改導(dǎo)入處的代碼。
@Resource private TalentImportVerifyHandler talentImportVerifyHandler; @PostMapping("/upload") public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { ExcelImportResult<TalentUserInputEntity> result; try { ImportParams params = new ImportParams(); // 表頭設(shè)置為2行 params.setHeadRows(2); // 標(biāo)題行設(shè)置為0行,默認(rèn)是0,可以不設(shè)置 params.setTitleRows(0); // 開啟Excel校驗(yàn) params.setNeedVerfiy(true); params.setVerifyHandler(talentImportVerifyHandler); result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), TalentUserInputEntity.class, params); } finally { // 清除threadLocal 防止內(nèi)存泄漏 ThreadLocal<List<TalentUserInputEntity>> threadLocal = talentImportVerifyHandler.getThreadLocal(); if (threadLocal != null) { threadLocal.remove(); } } System.out.println("是否校驗(yàn)失敗: " + result.isVerfiyFail()); System.out.println("校驗(yàn)失敗的集合:" + JSONObject.toJSONString(result.getFailList())); System.out.println("校驗(yàn)通過的集合:" + JSONObject.toJSONString(result.getList())); // 合并結(jié)果集 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()); // 校驗(yàn)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 + "行的錯(cuò)誤是:" + 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); } }
導(dǎo)入示例Excel2,結(jié)果如下:
實(shí)體類
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; /** * 課程名稱 * needMerge 是否需要縱向合并單元格(用于list創(chuàng)建的多個(gè)row) */ @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 = "學(xué)生", orderNum = "3") private List<StudentEntity> students; private String errorMsg; //自定義一個(gè)errorMsg接受下面重寫IExcelModel接口的get和setErrorMsg方法。 private Integer rowNum; //自定義一個(gè)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; @Data public class StudentEntity implements java.io.Serializable { /** * id */ private String id; /** * 學(xué)生姓名 */ @Excel(name = "學(xué)生姓名", height = 20, width = 30, isImportField = "true") private String name; /** * 學(xué)生性別 */ @Excel(name = "學(xué)生性別", 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 = "進(jìn)校日期", 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; @Data public class TeacherEntity { /** * 教師名稱 * isImportField 導(dǎo)入Excel時(shí),對Excel中的字段進(jìn)行校驗(yàn),如果沒有該字段,導(dǎo)入失敗 */ @Excel(name = "教師姓名", width = 30, orderNum = "1" ,isImportField = "true") private String name; /** * 教師性別 * replace 值的替換,`replace = {"男_1", "女_2"} `將值為1的替換為男 * suffix 文字后綴 */ @Excel(name = "教師性別", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true",orderNum = "2") private int sex; }
自定義校驗(yàn)類
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(); //假設(shè)我們要添加用戶, //現(xiàn)在去數(shù)據(jù)庫查詢getName,如果存在則表示校驗(yàn)不通過。 //假設(shè)現(xiàn)在數(shù)據(jù)庫中有個(gè)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("測試課程"); // 第二個(gè) CourseEntity courseEntity1 = new CourseEntity(); courseEntity1.setId("2"); courseEntity1.setName("數(shù)學(xué)"); 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("學(xué)生" + 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("導(dǎo)出測試", 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/教師課程學(xué)生導(dǎo)出測試.xls"); workbook.write(fos); fos.close(); } @Test public void testImport2() throws Exception { // 參數(shù)1:導(dǎo)入excel文件流 參數(shù)2:導(dǎo)入類型 參數(shù)3:導(dǎo)入的配置對象 ImportParams importParams = new ImportParams(); importParams.setTitleRows(1); // 設(shè)置標(biāo)題列占幾行 importParams.setHeadRows(2); // 設(shè)置字段名稱占幾行 即header importParams.setNeedVerify(true);//開啟校驗(yàn) importParams.setVerifyHandler(new MyVerifyHandler()); importParams.setStartSheetIndex(0); // 設(shè)置從第幾張表格開始讀取,這里0代表第一張表,默認(rèn)從第一張表讀取 BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File("E:/desktop/excel/教師課程學(xué)生導(dǎo)出測試.xls"))); ExcelImportResult result = new ExcelImportService().importExcelByIs(bis, CourseEntity.class, importParams, true); //這個(gè)是正確導(dǎo)入的 List<CourseEntity> list = result.getList(); System.out.println("成功導(dǎo)入的集合:"+JSONUtil.toJsonStr(list)); List<CourseEntity> failList = result.getFailList(); System.out.println("失敗導(dǎo)入的集合"+JSONUtil.toJsonStr(failList)); for (CourseEntity courseEntity : failList) { int line = courseEntity.getRowNum(); String msg = "第" + line + "行的錯(cuò)誤是:" + courseEntity.getErrorMsg(); System.out.println(msg); } //將錯(cuò)誤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("用戶數(shù)據(jù)表","UTF-8") + ".xls"); // response.setCharacterEncoding("UTF-8"); // workbook.write(response.getOutputStream()); FileOutputStream fos = new FileOutputStream("E:/desktop/excel/用戶數(shù)據(jù)表.xls"); workbook.write(fos); fos.close(); } }
導(dǎo)出結(jié)果
導(dǎo)入結(jié)果
感謝各位的閱讀,以上就是“Java中easypoi導(dǎo)入校驗(yàn)的方法是什么”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對Java中easypoi導(dǎo)入校驗(yàn)的方法是什么這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。