EasyPOI结合SpringMVC使用

easypoi view 项目是为了更简单的方便搭建在导出时候的操作，利用spring mvc 的view 封装，更加符合spring mvc的风格 view下面包括多个 view的实现

* EasypoiBigExcelExportView 大数据量导出

* EasypoiMapExcelView map 列表导出

* EasypoiPDFTemplateView pdf导出

* EasypoiSingleExcelView 注解导出

* EasypoiTemplateExcelView 模板导出

* EasypoiTemplateWordView word模板导出

* MapGraphExcelView 图表导出

view的是使用方法大同小异，都有一个对应的bean，里面保护指定的参数常量 同意用modelmap.put(‘常量参数名’，‘值’)就可以，最后返回这个view名字

注解目录扫描的时候加上 cn.afterturn.easypoi.view 就可以使用了

实例

1.applicationContext-mvc.xml

<!-- 扫描easypoi中所有的view -->
<context:component-scan base-package="cn.afterturn.easypoi.view" />
<!-- Bean解析器,级别高于默认解析器,寻找bean对象进行二次处理 -->
<bean id="beanNameViewResolver"
      class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1">
</bean>

2.controller层

导出

/**
*导出
*/
@RequestMapping("/export")
public String export(ModelMap map, EmployeeQuery query, HttpServletRequest request){
    //得到查找条件的结果
    List<Employee> list = employeeService.queryAll(query);
    //拿到当前项目的真实路径
    String realPath = request.getServletContext().getRealPath("");
    list.forEach(e->{
        String headImage = e.getHeadImage();
        // 设置图片路径
        e.setHeadImage(realPath+headImage);
    });
    //设置一些属性
    ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF);
    //冻结
    //params.setFreezeCol(3);
    map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
    map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
    map.put(NormalExcelConstants.PARAMS, params);//参数
    map.put(NormalExcelConstants.FILE_NAME, "员工");//文件名称
    //返回的名称 :easypoiExcelView -> 并没有找我的bean，而且当做一个路径去进行访问
    //  现在默认去找的视图解析器，而没有找我的那一个bean
    return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}

导入

1.导入页面

<!-- 上传请配置enctype -->
<form action="/import/xlsx" method="post" enctype="multipart/form-data">
    <input class="easyui-filebox" name="xlsxFile" data-options="prompt:'选择一个文件...'" style="width:80%">
    <button class="easyui-linkbutton" type="submit">确定</button>
</form>

2.controller层

    @RequestMapping("/xlsx")
    public String importXlsx(MultipartFile xlsxFile, HttpServletRequest request, HttpServletResponse response) throws Exception{
        ImportParams params = new ImportParams();
        params.setTitleRows(1); //注意:这里有两个表头
        List<Employee> list = ExcelImportUtil.importExcel(
               xlsxFile.getInputStream(),
                Employee.class, params);
        for (Employee employee : list) {
            employee.setPassword("123"); //默认密码123
            if(employee.getDepartment()!=null) {
                Department department = departmentService.findByName(employee.getDepartment().getName());
                employee.setDepartment(department);
            }
            employeeService.save(employee);
        }
        return "import";
    }
}

导入验证功能

1.引入包

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

2.domain类

  

  @Excel(name = "用户名")
    @NotBlank(message = "用户名不能为空")
    private String username;
    private String password;
    @Excel(name="邮件",width = 30)
    private String email;
    @Excel(name="年纪")
    @Max(value = 80,message = "max 最大值不能超过15")
    private Integer age;

3.controller层

  

  /**
     *导出
     */
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletRequest request, HttpServletResponse response) throws Exception{
        //设置导入参数
        ImportParams params = new ImportParams();
        //设置标题和头的行数
//        params.setTitleRows(1);
        params.setHeadRows(1);
        //设置需要验证为true
        params.setNeedVerfiy(true);
        //设计验证规则  自己定义的校验器
        params.setVerifyHandler(employeeVerifyHandler);
        //把上传的文件变成数据   导入时拿到更多的信息
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, params);
        //正确的数据直接保存
        result.getList().forEach(e->{
            //设置一个默认密码
            e.setPassword("123456");
            //获取部门对象
            if(e.getDepartment()!=null) {
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
            }
            employeeService.save(e);
        });
        //有错误的数据返回工作薄给前台
        if(result.isVerfiyFail()){
            Workbook workbook = result.getFailWorkbook();
            //设置响应头
            response.setHeader("content-disposition", "attachment;filename=error.xlsx");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        }
        return "import";
    }

4.自定义验证

自定义验证需要实现IExcelVerifyHandler接口

(注:让Spring来扫描到这个类)

@Component
public class MyVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;
    //验证用户名是否重复
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
        if (!employeeService.checkUsername(employee.getUsername())) {
            result.setMsg("该用户已存在");
            result.setSuccess(false);
            return result;
        }
        result.setSuccess(true);
        return result;
    }
}

 关注公众号，获取免费软件、资料，笔记等。