一、为什么获取行号
web开发中excel导入是常见需求，几乎是标配。导入的时候难免做数据校验，如果数据重复或者不符合业务要求，需要给前端友好的提示，那么行号必不可少。
二、如何获取
自定义监听器实现AnalysisEventListener<T>接口，重写invoke方法；
@Override public void invoke(T data, AnalysisContext context) { ReadRowHolder readRowHolder = context.readRowHolder(); Integer rowIndex = readRowHolder.getRowIndex(); data.setRowIndex(rowIndex+1); }
三、完整示例
1、导入依赖
<!--核心--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency> <!--非必须--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.22</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.80</version> </dependency>
2、编写Excel导入类
RowIndex 可以不是父类，如果多表导入都需要行号，建议使用父类，规范命名
import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor public class RowIndex { private Integer rowIndex; }
mport lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import lombok.experimental.Accessors; import java.util.Date; import java.util.List; /** * 用户 Excel 导入 VO */ @Data @Builder @AllArgsConstructor @NoArgsConstructor @Accessors(chain = false) // 设置 chain = false，避免用户导入有问题 public class UserImportExcelVO extends RowIndex { @ExcelProperty("姓名(必填)") private String nickname; @ExcelProperty("密码(必填)") private String password;; @ExcelProperty("部门ID(必填)") private String deptId; @ExcelProperty("用户邮箱") private String email; @ExcelProperty("手机号码(必填)") private String mobile; @ExcelProperty("职级(必填)") private String rank; @ExcelProperty("毕业院校(必填)") private String school; @ExcelProperty("工资卡号(必填)") private String salaryCard; /** * 身份证 */ @ExcelProperty("身份证(必填)") private String identityCard; /** * 学历 */ @ExcelProperty("学历(必填)") private String xueliExcel; /** * 入职日期 */ @ExcelProperty("入职日期(必填)") private String entryDateStr; /** * 备注 */ @ExcelProperty("备注") private String remark; private List<Long> deptExcelIds; /** * 入职日期 */ private Date entryDate; }
3、自定义监听器
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.exception.ExcelDataConvertException; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.CellExtra; import com.alibaba.excel.read.metadata.holder.ReadRowHolder; import com.alibaba.fastjson.JSON; import lombok.extern.slf4j.Slf4j; import java.util.List; import java.util.Map; @Slf4j public class UserExcelListener extends AnalysisEventListener<UserImportExcelVO> { List<UserImportExcelVO> res; public UserExcelListener(List<UserImportExcelVO> res) { this.res=res; } //解析异常调用 @Override public void onException(Exception exception, AnalysisContext context) throws Exception { log.error("解析失败，但是继续解析下一行:{}", exception.getMessage()); if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception; log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()); } } //解析表头 @Override public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) { } //解析每行数据调用 @Override public void invoke(UserImportExcelVO data, AnalysisContext context) { ReadRowHolder readRowHolder = context.readRowHolder(); Integer rowIndex = readRowHolder.getRowIndex(); data.setRowIndex(rowIndex+1); res.add(data); } @Override public void extra(CellExtra extra, AnalysisContext context) { } //所有数据解析完成后调用 @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("所有数据解析完成！"); log.info("解析数据:{}",JSON.toJSONString(res)); } }
4、编写接口
sheet(0).headRowNumber(1).doReadSync():导入excel第一张表，表头占一行,同步执行
@PostMapping("/import") @ApiOperation("导入用户") @ApiImplicitParam(name = "file", value = "Excel 文件", required = true, dataTypeClass = MultipartFile.class) public CommonResult<Boolean> importExcel(@RequestParam("file") MultipartFile file) throws Exception { List<UserImportExcelVO> res=new ArrayList<>(16); EasyExcel.read(file.getInputStream(), UserImportExcelVO.class, new UserExcelListener(res)).sheet(0).headRowNumber(1).doReadSync(); userService.importUsers(res); return success(true); }
5、校验处理
数据格式、判重、数据值是否合规等；
import cn.hutool.core.collection.CollUtil; import cn.hutool.core.date.DateTime; import cn.hutool.core.date.DateUtil; import cn.hutool.core.io.IoUtil; import cn.hutool.core.lang.Validator; import cn.hutool.core.util.IdcardUtil; import cn.hutool.core.util.NumberUtil; import cn.hutool.core.util.StrUtil;
@Override @Transactional(rollbackFor = Exception.class) // 添加事务，异常则回滚所有导入 public void importUsers(List<UserImportExcelVO> importUsers) { //获取职级 List<PostDO> rankList = postService.getPosts(null, Collections.singleton(CommonStatusEnum.ENABLE.getStatus())); List<String> rankNames = rankList.stream().map(PostDO::getName).collect(Collectors.toList()); //获取学历 List<DictDataDO> xueliList = dictDataMapper.selectByDictType("system_user_xueli"); List<String> xueliValueList = xueliList.stream().map(DictDataDO::getValue).collect(Collectors.toList()); if (CollUtil.isEmpty(importUsers)) { throw exception(USER_IMPORT_LIST_IS_EMPTY); } importUsers = importUsers.stream().filter((tem) -> tem.getNickname() != null || tem.getPassword() != null || tem.getDeptId() != null || tem.getMobile() != null || tem.getRank() != null || tem.getSchool() != null || tem.getSalaryCard() != null || tem.getIdentityCard() != null || tem.getXueliExcel() != null || tem.getEntryDate() != null).collect(Collectors.toList()); importUsers.forEach((importUser) -> { checkImortExcel(importUser, rankNames,xueliValueList); }); List<AdminUserDO> insertUserList=new ArrayList<>(); for (UserImportExcelVO importUser : importUsers) { AdminUserDO insertUser=new AdminUserDO(); BeanUtils.copyProperties(importUser,insertUser); insertUser.setXueli(Integer.parseInt(importUser.getXueliExcel())); //TODO [100,101]部门回显处理 insertUser.setDeptIds(JSON.toJSONString(importUser.getDeptExcelIds())); insertUser.setUsername(importUser.getMobile()); insertUserList.add(insertUser); } userMapper.insertBatch(insertUserList); insertUserList.forEach((tem)->{ tem.setJobNumber(tem.getId()); userMapper.updateById(tem); }); log.info("导入数据:{}",JSON.toJSONString(importUsers)); } private void checkImortExcel(UserImportExcelVO importUser, List<String> rankNames,List<String> xueliValueList) { Integer rowIndex = importUser.getRowIndex(); String nickname = importUser.getNickname(); String str = "第%s行"; String s1 = ","; if (StrUtil.isEmpty(nickname)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，姓名不能为空", rowIndex))); } String password = importUser.getPassword(); if (StrUtil.isEmpty(password)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，密码不能为空", rowIndex))); } if (password.length() < 8) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，密码不能小于8位", rowIndex))); } String deptId = importUser.getDeptId(); if (StrUtil.isEmpty(deptId)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，用户部门不能为空", rowIndex))); } List<Long> deptIds = new ArrayList<>(3); if (deptId.contains(s1)) { String[] split = deptId.split(s1); for (String s : split) { deptIds.add(Long.valueOf(s)); } } else { deptIds.add(Long.valueOf(deptId)); } importUser.setDeptExcelIds(deptIds); checkDeptIds(deptIds,rowIndex,str); String email = importUser.getEmail(); if (StrUtil.isEmpty(email)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，邮箱格式不能为空", rowIndex))); } if (!Validator.isEmail(email)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，邮箱格式不正确", rowIndex))); } String mobile = importUser.getMobile(); if (StrUtil.isEmpty(mobile)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，手机号码不能为空", rowIndex))); } if (!Validator.isMobile(mobile)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，手机号码格式不正确", rowIndex))); } String rank = importUser.getRank(); if (StrUtil.isEmpty(rank)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，职级不能为空", rowIndex))); } if (!rankNames.contains(rank)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，职级有误", rowIndex))); } String school = importUser.getSchool(); if (StrUtil.isEmpty(school)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，毕业院校不能为空", rowIndex))); } String salaryCard = importUser.getSalaryCard(); if (StrUtil.isEmpty(salaryCard)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，工资卡号不能为空", rowIndex))); } if (!NumberUtil.isNumber(salaryCard)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，工资卡号不正确", rowIndex))); } String identityCard = importUser.getIdentityCard(); if (StrUtil.isEmpty(identityCard)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，身份证号码不能为空", rowIndex))); } if (!IdcardUtil.isValidCard(identityCard)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，身份证号码:%s不正确", rowIndex, identityCard))); } String xueli = importUser.getXueliExcel(); if (StrUtil.isEmpty(xueli)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，学历不能为空", rowIndex))); } if (!xueliValueList.contains(xueli)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，学历有误", rowIndex))); } String entryDate = importUser.getEntryDateStr(); if (entryDate == null) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，入职日期不能为空", rowIndex))); } if (entryDate.length()!=8||!Validator.isNumber(entryDate)) { throw ServiceExceptionUtil.exception(new ErrorCode(String.format(str + "，入职日期:%s格式有误", rowIndex,entryDate))); } DateTime yyyyMM = DateUtil.parse(entryDate, "yyyyMMdd"); importUser.setEntryDate(yyyyMM); }
提示：如果数据表比较大分批处理，官方demo
package com.alibaba.easyexcel.test.demo.read; import java.util.List; import java.util.Map; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.exception.ExcelDataConvertException; import com.alibaba.excel.metadata.data.ReadCellData; import com.alibaba.excel.read.listener.ReadListener; import com.alibaba.excel.util.ListUtils; import com.alibaba.fastjson.JSON; import lombok.extern.slf4j.Slf4j; /** * 读取转换异常 * * @author Jiaju Zhuang */ @Slf4j public class DemoExceptionListener implements ReadListener<ExceptionDemoData> { /** * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5; private List<ExceptionDemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); /** * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 * * @param exception * @param context * @throws Exception */ @Override public void onException(Exception exception, AnalysisContext context) { log.error("解析失败，但是继续解析下一行:{}", exception.getMessage()); // 如果是某一个单元格的转换异常 能获取到具体行号 // 如果要获取头的信息 配合invokeHeadMap使用 if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception; log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()); } } /** * 这里会一行行的返回头 * * @param headMap * @param context */ @Override public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) { log.info("解析到一条头数据:{}", JSON.toJSONString(headMap)); } @Override public void invoke(ExceptionDemoData data, AnalysisContext context) { context. log.info("解析到一条数据:{}", JSON.toJSONString(data)); if (cachedDataList.size() >= BATCH_COUNT) { saveData(); cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); log.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { log.info("{}条数据，开始存储数据库！", cachedDataList.size()); log.info("存储数据库成功！"); } }
