导入与导出

Easypoi使用

1. 导出

 //导出Excel表格
    @RequestMapping("/download")
    public String employeeDownload(EmployeeQuery employeeQuery, HttpServletRequest request,ModelMap map){
        List<Employee> list = iEmployeeService.findSpec(employeeQuery);
        //获得图片的真实路径
        String realPath = request.getServletContext().getRealPath("");
        list.forEach(e ->{
            e.setHeadImage(realPath+e.getHeadImage());
        } );
        ExportParams params = new ExportParams("员工", "员工管理", ExcelType.XSSF);
        //表示导出的表格左边三列冻结,当没有设置的时候，默认的是前两行冻结
        //params.setFreezeCol(3);
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合,这个地方必须是list集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称
        //返回的名称 :easypoiExcelView -> 并没有找我的bean，而且当做一个路径去进行访问
        //  现在默认去找的视图解析器，而没有找我的那一个bean
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
    }
}

1.2配置xml

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

1.3导出

//    接收文件：MultipartFile  empFile文件名字必须和前面jsp页面传的name值保持一致
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response){
        ImportParams params = new ImportParams();
        //设置表头
        params.setHeadRows(1);
        try {
            //设置需要验证为true
            params.setNeedVerfiy(true);
            //设置验证规则
            params.setVerifyHandler(iExcelVerifyHandler);
            ExcelImportResult<Employee>  result = ExcelImportUtil.importExcelMore(
                    empFile.getInputStream(),
                    Employee.class, params);
            result.getList().forEach(e ->{
                //得到部门的对象
                Department department = iDepartmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
                //设置初始密码，因为导入的文件中没有密码
                String pwd = MD5Utils.creatPwd(e.getUsername());
                e.setPassword(pwd);
                e.setHeadImage("/images/head/avatar.png");
                //将每一个对象保存早到数据库
                iEmployeeService.save(e);
            } );
            if(result.isVerfiyFail()){
                //得到错误的工作簿
                Workbook failWorkbook = result.getFailWorkbook();
                response.setHeader("content-disposition", "attachment;filename=error.xlsx");
                ServletOutputStream out= response.getOutputStream();
                failWorkbook.write(out);
                out.flush();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "/import/index";
    }
}

1.4导入页面

<form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
    <input class="easyui-filebox" name="empFile" style="width:80%"
           data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
    <button class="easyui-linkbutton">导入</button>
</form>

1.5自定义用户唯一性校验

/**
 * 用户名唯一性校验
 */
@Component
public class IExcelVerifyHandlerCheck implements IExcelVerifyHandler<Employee> {
@Autowired
private IEmployeeService iEmployeeService;
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        Employee emp = iEmployeeService.findByUsername(employee.getUsername());
        System.out.println(emp);
        if(emp!=null){
            //如果用户名已存在就应该返回一个flase
        return new ExcelVerifyHandlerResult(false,"用户名已经存在");
        }
        return new ExcelVerifyHandlerResult(true);
    }
}