EasyPoi集成SpringMVC

1.applicationContext-mvc.xml添加配置

• 扫描视图

<!-- 扫描easypoi的一些view:视图 -->
<context:component-scan base-package="cn.afterturn.easypoi.view" />

• easypoi视图解析器

<!-- bean的视图解析器  p:order="0":顺序在最前面 -->
<bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />

2.导出Excel（图片必须是真实路径）

@RequestMapping("/export")
public String exportExcel(ModelMap map, EmployeeQuery query, HttpServletRequest request){
    // 获取数据
    List<Employee> list = employeeService.findByQuery(query);
    ExportParams params = new ExportParams("员工管理", "员工表", ExcelType.XSSF);
    // 获取当前项目真是路径
    String realPath = request.getServletContext().getRealPath("");
    // 拼接头像路径
    list.forEach(employee -> {
        employee.setHeadImage(realPath + employee.getHeadImage());
    });
    // params.setFreezeCol(2); 冻结
    map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
    map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
    map.put(NormalExcelConstants.PARAMS, params);//参数
    map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称
    return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}

​​​3.导入Excel

• 上传form表单必须加 enctype="multipart/form-data
• 导入验证包

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

• 给字段加验证的注解

@Excel(name = "姓名")
@NotNull(message = "用户名不能为空")
private String username;

private String password;

@Excel(name = "邮箱",width = 20)
private String email;

@Excel(name = "年龄")
@Max(value = 60,message = "年龄不能超过60")
@Min(value = 18,message = "年龄不能小于18")
private Integer age;

@Excel(name = "头像",type = 2,height = 15)
private String headImage;ge;

• 自定义注解

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        //如果用户存在，返回false
        if (! employeeService.checkUsername(employee.getUsername())) {
            ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(false);
            result.setMsg("用户名已经存在！");
            return result;
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

• controller实现导入

@RequestMapping("/import")
public String importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception {
    System.out.println(multipartFile);

    ImportParams importParams = new ImportParams();
    importParams.setHeadRows(1);
    importParams.setTitleRows(1);
    // 设置验证
    importParams.setNeedVerfiy(true);
    importParams.setVerifyHandler(employeeExcelVerifyHandler);

    // 获取Excel导入结果
    ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
            Employee.class, importParams);

    // 获取通过验证的数据
    List<Employee> emp = result.getList();
    // 保存数据
    emp.forEach(employee -> {
        // 设置默认密码
        employee.setPassword("123");
        // 设置部门
        if (employee.getDepartment() != null){
            // 根据名称查找部门
            Department department = departmentService.FindByName(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 "/importExcel/import";
}

 

转载于:https://my.oschina.net/u/4107179/blog/3030545