EasyPoi导入校验+导入自定义查询等校验

首先第一步引入POM配置

<!--easypoi插件导入导出 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.3</version>
        </dependency>
<!--easypoi插件导入导出 end-->

导入代码：

    @ApiOperation(value = "导入经销商银行", notes = "导入经销商银行", httpMethod = "POST")
    @RequestMapping(value = "/importDealerBank", method = {RequestMethod.POST})
    @Transactional(rollbackFor = Exception.class)
    public ResponseEntity<RestResponse> importDealerBank(@RequestParam(value = "file",required = false) MultipartFile file){
        ImportParams importParams = new ImportParams();
        // 数据处理
        importParams.setHeadRows(1);
        importParams.setTitleRows(1);
        importParams.setNeedVerify(true); //设置验证支持
        importParams.setVerifyHandler(talentImportVerifyHandler);//自定义校验例如数据库查询判断重复 如果不需要自定义校验可以注释此行
        try {
           
            ExcelImportResult<ExportDealerBankVo> list = ExcelImportUtil.importExcelMore(file.getInputStream(),ExportDealerBankVo.class, importParams);
        //无校验导入
        // List<ExportDealerBankVo> list = ExcelImportUtil.importExcel(file.getInputStream(), ExportDealerBankVo.class, importParams);
            if(list.getFailList().size()==0){
                return RestResponse.createFailRes("上传0条请按照模板格式上传");
            }else {
                List<ExportDealerBankVo> successList = list.getList();//TODO 成功的
                List<ExportDealerBankVo> failList  = list.getFailList();//TODO 失败的
                System.out.println("是否校验失败: " + list.isVerifyFail());
                System.out.println("校验失败的集合:" + JSONObject.toJSONString(list.getFailList()));
                System.out.println("校验通过的集合:" + JSONObject.toJSONString(list.getList()));
                List<String> listString = new ArrayList<>();
                if(CollectionUtils.isNotEmpty(failList)){
                    for (ExportDealerBankVo entity : failList) { //失败的
                        int line = entity.getRowNum() + 1;
                        String msg = "第" + line + "行的错误原因：" + entity.getErrorMsg()+"\n";
                        listString.add(msg);
                    }
                    throw new CommonException("校验成功："+successList.size()+"条\n 校验失败："+failList.size()+"条\n"+Joiner.on(",").join(listString));
                }
                //处理 successList 正确的自行处理逻辑。。。
               
            }
            return RestResponse.createSuccessRes("导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            return RestResponse.createFailRes(e.getMessage());
        }
    }

准备需要的类 要保证poi版本一致我用的4.3.0 不然会出现 integer 和int 类型不匹配的问题

创建：ExcelVerifyInfo 实现以下俩个方法是为了获取 行数 和错误Msg

implements IExcelModel, IExcelDataModel

import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
 
@Data
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;
    }
}

再拿VO类去继承以上ExcelVerifyInfo  为了获取行号和错误信息

 
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.wl.ltx.dealer.ExcelVerifyInfo;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.Date;
 
/**
* 导出银行对象
*/
@Data
@ApiModel(value="ExportDealerBankVo对象", description="ExportDealerBankVo对象")
public class ExportDealerBankVo extends ExcelVerifyInfo  {
 
    // 时间格式校验正则
    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}";
 
    @ApiModelProperty(value = "经销商ID")
    private String dealerId;
 
    @ApiModelProperty(value = "经销商编码")
    @Excel(name = "经销商编码",width = 20)
    @NotBlank(message = "不能为空")
    private String dealerCode;
 
    @ApiModelProperty(value = "经销商名称")
    @Excel(name = "经销商名称",width = 20)
    @NotBlank(message = "不能为空")
    private String dealerName;
 
    @ApiModelProperty(value = "银行国家")
    @Excel(name = "银行国家",width = 20)
    @NotBlank(message = "不能为空")
    private String bankCountry;
 
    @ApiModelProperty(value = "账户持有人")
    @Excel(name = "账户持有人",width = 20)
    @NotBlank(message = "不能为空")
    private String accountHolder;
 
    @ApiModelProperty(value = "银行名称")
    @Excel(name = "银行名称",width = 35)
    @NotBlank(message = "不能为空")
    private String bankName;
 
    @ApiModelProperty(value = "银行编码")
    @Excel(name = "银行编码",width = 20)
    @NotBlank(message = "不能为空")
    private String bankCode;
 
    @ApiModelProperty(value = "银行账号")
    @Excel(name = "银行账号",width = 30)
    @NotBlank(message = "不能为空")
    private String bankAccount;
 
    @ApiModelProperty(value = "银行联号")
    @Excel(name = "银行联号",width = 20)
    @NotBlank(message = "不能为空")
    private String affiliated;
 
    @ApiModelProperty(value = "公私标识")
    @Excel(name = "公私标识",replace = { "私户_1", "公户_2"},width = 20)
    @NotBlank(message = "不能为空")
    private String pubPriSign;
 
    // 参考
    // @Excel(name = "薪资*", replace = {"3K以下_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K以上_6"})
    // @Pattern(regexp = "[123456]", message = "薪资信息错误")
    @ApiModelProperty("主账号标识")
    @Excel(name = "主账号标识",replace = { "主账户_0001", "备案账户_0002","备案账户_''","备案账户_null"},width = 20)
    @NotBlank(message = "不能为空 例如(主账户,备案账户)")
    private String bkvid;
 
    @ApiModelProperty("有效起始日期")
    @Excel(name = "有效起始日期",databaseFormat = "yyyy-MM-dd",format = "yyyy-MM-dd",width = 20)
    @NotNull(message = "不能为空")
    private Date bkValidFrom;
 
}

如果要实现自定义的错误信息已经进行查询数据库是否存在错误数据用到以下，如果不用请忽略

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.wl.ltx.dealer.sql.vo.ExportDealerBankVo;
import org.springframework.stereotype.Component;
import java.util.StringJoiner;
 
@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<ExportDealerBankVo> {
 
    @Override
    public ExcelVerifyHandlerResult verifyHandler(ExportDealerBankVo inputEntity) {
        StringJoiner joiner = new StringJoiner(",");
        // 此处可以新增为自定义校验
        // 根据姓名与手机号判断数据是否重复
        //        String name = inputEntity.getName();
        //        String phone = inputEntity.getPhone();
        //        // mock 数据库
        //        boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
        //        if (duplicates) {
        //            joiner.add("数据与数据库数据重复");
        //        }
        if (joiner.length() != 0) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

下面要打开

 以下看效果