easypoi校验数据时获取excel行号和错误信息

什么是easypoi

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法

本文不讲easypoi怎么引入配置使用 有兴趣的可以了解 easypoi传送门

easypoi校验excel实体类怎么获取错误信息和excel的行号

实现了两个接口：

• IExcelModel：获取错误数据
• IExcelDataModel：获取错误行号

import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;

/**
 * @Description
 * <p>
 *    easyPoi的导入校验基础类  通过实现以下两个接口拿到错误的行号和错误信息
 * </p>
 * @author liang
 * @date 2021/5/28 15:04
 */
public class ExcelVerifyInfo implements IExcelModel, IExcelDataModel {

    private String errorMsg;

    private int rowNum;


    @Override
    public Integer getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

}

需要用到的excel实体类

只需要继承此类 省略get和set方法

import cn.afterturn.easypoi.excel.annotation.Excel;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.io.Serializable;

public class ImportVipUserExcel extends ExcelVerifyInfo implements Serializable {

    @Excel(name = "*姓名", orderNum = "1")
    @NotBlank(message = "不能为空")
    private String vuName;

    @Excel(name = "*手机号", orderNum = "2")
    @NotBlank(message = "不能为空")
    @Pattern(regexp = CommonUtils.REGEX_PHONE,message = "格式不正确")
    private String vuPhone;

    @Excel(name = "身份证号码", orderNum = "3")
    @Pattern(regexp = CommonUtils.REGEX_ID_CARD,message = "格式不正确")
    private String vuIdCard;

    @Excel(name = "邮箱", orderNum = "4")
    @Pattern(regexp = CommonUtils.REGEX_EMAIL,message = "格式不正确")
    private String vuEmail;

    @Excel(name = "单位", orderNum = "5")
    private String vuOrg;
}

调用的客户端代码

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResultVO importVipUser(MultipartFile file) {
        InputStream is;
        try {
            is = file.getInputStream();
        } catch (IOException e) {
            return new ResultVO(ResultEnum.EXCEL_EXCEPTION);
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true);
        List<ImportVipUserExcel> dataList;
        try {
            ExcelImportResult<ImportVipUserExcel> result = ExcelImportUtil.importExcelMore(is,ImportVipUserExcel.class,params);
            if (CommonUtils.isEmpty(result.getList()) && CommonUtils.isEmpty(result.getFailList())){
                return new ResultVO(ResultEnum.EXCEL_EXCEPTION.getCode(), "数据行不能为空", null);
            }
            if (result.isVerifyFail()){
                ImportVipUserExcel excel = result.getFailList().get(0);
                return new ResultVO(ResultEnum.EXCEL_EXCEPTION.getCode(),"excel中第"+(excel.getRowNum()+1)+"行的 "+excel.getErrorMsg(),null);
            }
            dataList = result.getList();
        } catch (Exception e) {
            logger.error("初始化导入价格的excel转换实体类失败!");
            return new ResultVO(ResultEnum.ERROR, e);
        }
        return new ResultVO(ResultEnum.SUCCESS);
    }