easypoi集成springmvc导入excel文件

╰半夏微凉° 2022-02-24 03:14 329阅读 0赞

## （一）导包 ##

<dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-base</artifactId>
          <version>3.2.0</version>
        </dependency>
        <dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-web</artifactId>
          <version>3.2.0</version>
        </dependency>
        <dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-annotation</artifactId>
          <version>3.2.0</version>
        </dependency>
    
        
        <dependency>
          <groupId>org.hibernate</groupId>
          <artifactId>hibernate-validator</artifactId>
          <version>5.2.4.Final</version>
        </dependency>

## （二）domain准备 ##

员工类

@Entity
    @Table(name = "employee")
    @ExcelTarget(value = "employee")
    public class Employee extends BaseDomain{
        @Excel(name = "用户名",width = 15)
        @NotBlank(message = "用户名不能为空")
        private String username;
        private String password;
        @Excel(name = "邮箱",width = 20)
        @Email
        @NotBlank(message = "邮箱不能为空")
        private String email;
        @Excel(name = "年龄")
        @Max(value = 65,message = "年龄不能超过65")
        @Min(value = 18,message = "年龄最小是18")
        private Integer age;
        @Excel(name = "头像",type = 2,width = 15,height = 40)
        private String headImage;
        private int enable;//0：启用员工 1：账户被禁用  2：账户不可用
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "department_id")
        @ExcelEntity
        private Department department;
    
    
        @ManyToMany
        @JoinTable(name = "employee_role",joinColumns = {@JoinColumn(name = "employee_id")},
                inverseJoinColumns = {@JoinColumn(name = "role_id")})
        private Set<Role> roles = new HashSet<>();

部门类

@Entity
    @Table(name = "department")
    @ExcelTarget("department")
    public class Department extends BaseDomain {
        @Excel(name = "名称_department,部门名称_employee")
        private String name;

## （三）controller准备 ##

@RequestMapping("/emp")
       public void importEmp(@RequestParam(value = "file", required = false) MultipartFile multipartFile, HttpServletRequest request, HttpServletResponse response) throws IOException {
    
            //获取图片名称
            String fileName = multipartFile.getOriginalFilename();
            String mimeType = request.getServletContext().getMimeType(fileName);
            //此处应该判断mime类型
    
            ImportParams params = new ImportParams();
            //params.setTitleRows(1);
            params.setHeadRows(1);
            params.setNeedVerfiy(true);
            params.setVerifyHandler(employeeExcelVerifyHandler);
    
    
            try {
                result = ExcelImportUtil.importExcelMore(
                        multipartFile.getInputStream(),
                        Employee.class, params);
            } catch (Exception e) {
                e.printStackTrace();
            }
    
            if (result.getList() != null) {
                result.getList().forEach(employee -> {
                    //设置初始密码
                    employee.setPassword("123456");
                    Department department = departmentService.queryByName(employee.getDepartment().getName());
                    employee.setDepartment(department);
                    employeeService.save(employee);
                });
            }
    
    //        if (result.isVerfiyFail()) { //验证失败 下载错误的文件
               /* Workbook failWorkbook = result.getFailWorkbook();
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
                response.setHeader("Content-disposition", "attachment;filename=error.xlsx");
                response.setHeader("Pragma", "No-cache");//设置不要缓存
                OutputStream ouputStream = response.getOutputStream();
                failWorkbook.write(ouputStream);
                ouputStream.flush();
                ouputStream.close();*/
    //        }
    //        return  "import/index";
        }