一、为什么获取行号

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("存储数据库成功！");
    }
}