EasyPoi导入验证功能

1准备好要导入的Excel，注意Excel的标题要和domain中的 @Excel(name = "标题")一样

1 导入验证包支持

<!-- JSR 303 规范验证包 -->
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

　　

2 domain中添加验证方法

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain {

    @Excel(name = "名字")
    @NotNull(message = "用户名不能空")
    private String username;
    private String password;
    @Excel(name = "年龄")
    @Max(value = 100)
    @Min(value = 18)
    private Integer age;
    @Excel(name = "邮箱",width = 20)
    @NotNull
    private String email;
    ...
}

3 自定义验证

• 实现IExcelVerifyHandler
• 把这个类交给Spring管理(千万不要忘了让Spring去扫描到它)

  /**
   * 自定义验证(我们会在这里做唯一性的验证)
   */
  @Component
  public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
  
      @Autowired
      private IEmployeeService employeeService;
      /**
       *
       * ExcelVerifyHandlerResult
       *   suceess :代表验证成功还是失败(如果用户名重复，就代表失败)
       *   msg:失败的原因
       */
      @Override
      public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
  
          ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
          //如果根据用户名获取到用户，代表这个用户已经存在
          Employee tempEmp = employeeService.findByUsername(employee.getUsername());
          if(tempEmp!=null){
              result.setSuccess(false);
              result.setMsg("用户名重复");
          }
  
          return  result;
      }
  }

  3 实现验证功能

• @Controller
  @RequestMapping("/import")
  public class ImportController extends BaseController {
  
      @Autowired
      private IEmployeeService employeeService;
      @Autowired
      private IDepartmentService departmentService;
      @Autowired
      private EmployeeExcelVerifyHandler employeeExcelVerifyHandler;
  
      @RequestMapping("/index")
      public String index(){
          return "import";
      }
  
  
      @RequestMapping("/employeeXlsx")
      public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
          //一.使用EasyPoi获取文件数据
          ImportParams params = new ImportParams();
          params.setHeadRows(1);
          params.setNeedVerfiy(true); //设置验证支持
          params.setVerifyHandler(employeeExcelVerifyHandler); //设置一个验证处理器
  
          //二.获取excel中的数据，封装成了一个结果对象(有很多东西)
          ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                  empFile.getInputStream(),
                  Employee.class, params);
          //三.获到正确的数据，并把它们保存到数据库
          List<Employee> list = result.getList();
          list.forEach(e->{
              e.setPassword("123");
              Department dept = departmentService.findByName(e.getDepartment().getName());
              e.setDepartment(dept);
              employeeService.save(e);
          });
          //四.如果有错误，把错误数据返回到前台(让前台下载一个错误的excel)
         //4.1判断是否有错误
          if(result.isVerfiyFail()){
              //4.2拿到错误的文件薄
              Workbook failWorkbook = result.getFailWorkbook();
  
              //把这个文件导出
              response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
              response.setHeader("Content-disposition", "attachment;filename=error.xlsx"); //告诉浏览下载的是一个附件，名字叫做error.xlsx
              response.setHeader("Pragma", "No-cache");//设置不要缓存
              OutputStream ouputStream = response.getOutputStream();
              failWorkbook.write(ouputStream);
              ouputStream.flush();
              ouputStream.close();
          }
  
          return "import";
      }
  
  
  }