简单的EXCEL导入实战（错误数据记录原因，正确数据入库）

以导入招生信息为例

1. 首先在项目pom文件中引入

  <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

1. 根据EXCEL生成对应的实体类（该类实现IExcelModel接口）

   • 可以通过注解做一些基本校验
   • 继承IExcelModel为了记录错误数据的原因

@Data
public class  AutonomyApplicantsAdminMode implements IExcelModel{
    @Excel(name = "姓名")
    @NotBlank
    @Pattern(regexp = "[\\u4E00-\\u9FA5]{2,5}", message = "姓名中文2-5位")
    private String name;
    @Excel(name = "招录省份")
    private String province;
    @Excel(name="年份")
    private String year;
    @Excel(name = "性别")
    private String sex;
    @Excel(name = "准考证号")
    private String confirmation;
    @Excel(name = "证件类型")
    private String certificateType;
    @Excel(name = "证件号码")
    private String certificateNo;
    @Excel(name = "联系电话")
    private String mobile;
    @Excel(name = "毕业学校")
    private String graduateInstitutions;
    @Excel(name = "专业课成绩")
    private String majorScore;
    @Excel(name = "文化课成绩")
    private String cultureScore;
    @Excel(name = "调剂")
    private String whether;
    @Excel(name = "报考志愿")
    private String volunteerIds;
    @Excel(name = "考试科目")
    private String examinationSubjects;
    private String errorMsg;

1. 创建校验数据的处理类（该类实现IExcelVerifyHandler接口 ）

• 以下代码具体业务处理不用关心，可以不看
• 在重写的方法中做数据校验，传入的参数object就是EXCEL每行数据对应的实体类对象，可以对所有字段进行校验（比如数据库中是否 存在，数据转换等等）
• 

public class AutonomyApplicantsAdminExcelHandler implements IExcelVerifyHandler {

    private static ApplicantsAdminDao applicantsAdminDao;
    private static MajorDao majorDao;
    static {
        TwoTuple<ApplicantsAdminDao, MajorDao> bean = SpringUtil.getBean(ApplicantsAdminDao.class, MajorDao.class);
        applicantsAdminDao=bean.first;
        majorDao=bean.second;
    }
        @Override
    public ExcelVerifyHanlderResult verifyHandler(Object obj) {
        StringBuffer msg=new StringBuffer();
        ExcelVerifyHanlderResult excelVerifyHanlderResult = new ExcelVerifyHanlderResult();
        if(obj instanceof AutonomyApplicantsAdminMode){
//--------------------------------------------------------------------------------------------------------------------------
                //校验报考志愿
                
//-----------------------------------------------------------------------------------------------------------------------
                //校验证件号是否唯一
            
//-----------------------------------------------------------------------------------------------------------------------
                //判断准考证号是否唯一
            
//end--------------------------------------------------------------------------------------------------------------------
            if(msg!=null&&StringUtils.isNotBlank(msg.toString())){
                excelVerifyHanlderResult.setMsg(msg.toString());
                excelVerifyHanlderResult.setSuccess(false);
            }else {
                excelVerifyHanlderResult.setSuccess(true);
            }
        }
        return excelVerifyHanlderResult;
    }
}

1. 最后数据业务处理类

-new 一个数据处理的处理类

• 调用工具类方法返回result

result.getList()返回正确数据list集合；
result.getFailList()返回错误数据list集合；
result.getFailWorkbook();返回错误数据的Workbook

@Service
public class ExcelImportServicempl implements ExcelImportService {
    
     * @return
     */
    @Override
    public ExcelImportOutputVO AutonomyExcelImport(MultipartFile file) {
        ExcelImportOutputVO excelImportOutputVO=new ExcelImportOutputVO();
        IExcelVerifyHandler excelHandler = new AutonomyApplicantsAdminExcelHandler();
        ExcelImportResult<AutonomyApplicantsAdminMode> result= ImportExcelUtil.asMultipartFileObtain(file,AutonomyApplicantsAdminMode.class,excelHandler);
        List<AutonomyApplicantsAdminMode> succList = result.getList();
       //正确数据处理
        ...........................
        //错误数据处理
        if(result.isVerfiyFail()){
           
            Workbook failWorkbook = result.getFailWorkbook();
          

        }
        return excelImportOutputVO;
    }
}

工具类方法贴出：

 /**
     *      获取数据集
     * @param file
     *            一个代表型的类(往往用来代表要上传的文件)
     * @param pojoClass
     *            Excel对象Class
     * @param excelVerifyHandler
     *            实现IExcelVerifyHandler接口的类（拓展一些自定义的错误校验）
     */

    public static <T>ExcelImportResult<T> asMultipartFileObtain(MultipartFile file, Class<T> pojoClass, IExcelVerifyHandler excelVerifyHandler){
        ImportParams importParams = new ImportParams();
        // 数据处
        importParams.setVerifyHanlder(excelVerifyHandler);
        // 需要验证
        importParams.setNeedVerfiy(true);
        ExcelImportResult result=null;
        try {
           result = ExcelImportUtil.importExcelMore(file.getInputStream(),pojoClass,
                    importParams);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

-------------------结束