1準備好要導入的Excel，注意Excel的標題要和domain中的 @Excel(name = "標題")一樣

easypoi獲取索引_用户名

 

 

 

 

 

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";
    }


}