一、为什么获取行号
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("存储数据库成功!");
}
}