EasyPOI的使用
EasyPOI：作用是对办公文件(word,excel.ppt)进行上传和下载，特点就是easy
maven项目导包：
<!--easypoi的支持包-->
<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>
官方文档:http://easypoi.mydoc.io/
导出功能：
EasyPOI可以继承SpringMVC
扫描view和 配置视图解析器
<!--扫描excel的view包--> <context:component-scan base-package="cn.afterturn.easypoi.view" /> <context:component-scan base-package="cn.king.aisell.common" /> <!--根据bean的名称去返回 p:order顺序在前面 --> <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />
准备前台页面和传入相应的查询数据
<form id="searchForm" method="post" action="/employee/export"> 用户名: <input name="username" class="easyui-text" style="width:80px"> 邮箱: <input name="email" class="easyui-text" style="width:80px"> 部门:<input name="departmentId" class="easyui-combobox" name="dept" panelHeight="auto" style="width:120px" data-options="valueField:'id',textField:'name',url:'/util/departmentList',editable:false"/> <a href="javascript:;" data-cmd="search" class="easyui-linkbutton" iconCls="icon-search">搜索</a> <a href="javascript:;" data-cmd="reset" class="easyui-linkbutton" data-options="iconCls:'icon-reload'">重置</a> <shiro:hasPermission name="employee:export"> <button class="easyui-linkbutton" iconCls="icon-print" plain="true">导出</button> </shiro:hasPermission> </form>
后台接收参数进行导出
//员工数据导出 @RequestMapping("/export") public String exportEmployee(ModelMap map, EmployeeQuery query, HttpServletRequest request){ //通过高级查询拿数据 List<Employee> list = iEmployeeService.findByQuery(query); //拿到员工头像路径 String realPath = request.getServletContext().getRealPath(""); list.forEach(e -> { e.setHeadImage(realPath + e.getHeadImage()); }); //将从数据库中拿到的信息导出到前台 ExportParams params = new ExportParams("员工管理", "员工信息表", ExcelType.XSSF); // 数据集合(导出的数据) map.put(NormalExcelConstants.DATA_LIST, list); //导出实体 map.put(NormalExcelConstants.CLASS, Employee.class); //参数 map.put(NormalExcelConstants.PARAMS, params); //文件名称 map.put(NormalExcelConstants.FILE_NAME, "员工信息表"); //View名称 return NormalExcelConstants.EASYPOI_EXCEL_VIEW; }
导入功能
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>导入管理</title>
<jsp:include page="BaseUI.jsp"></jsp:include>
</head>
<body>
<form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
<input class="easyui-filebox" name="empFile" style="width:20%"
data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
<button class="easyui-linkbutton">导入</button>
</form>
<h3 style="color: red">上传说明</h3>
<h4>
上传必须以下载的excel格式为准
</h4>
<h4>
用户名不得为空值或重复得值
</h4>
<h4>
年龄应为20-80岁之间
</h4>
<h4>
如果上传的文件不符合要求
</h4>
<h4>
在上传提交后会出现上传失败文件，里面有上传失败的内容和原因
</h4>
<h4>
若无提示，则表示上传成功
</h4>
</body>
</html>
导入后台
//上传员工excel的功能 @RequestMapping("/employeeXlsx") public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{ //设置导入的参数 ImportParams params = new ImportParams(); //设置需要验证 params.setNeedVerfiy(true); params.setHeadRows(1); //加入自定义验证 params.setVerifyHandler(employeeExcelVerifyHandler); //获得excel但如的结果 ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore( empFile.getInputStream(), Employee.class, params); //拿到正确的数据，然后把它们保存到数据库 List<Employee> list = result.getList(); list.forEach(e ->{ //从数据库中查询部门 String deptName = e.getDepartmentId().getName(); Department dept = iDepartmentService.findByName(deptName); e.setDepartmentId(dept); //设置初始密码 String md5EncryptionByPassword = MD5EncryptionByPassword.MD_5_ENCRYPTION_BY_PASSWORD.getMD5EncryptionByPassword("123"); e.setPassword(md5EncryptionByPassword); iEmployeeService.save(e); }); //如果有错误 if(result.isVerfiyFail()) { //获得Workbook对象，以返回失败文档 Workbook failWorkbook = result.getFailWorkbook(); //设置响应的文件类型 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //attachment:不要用浏览器打开 response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx"); //设置不要缓存 response.setHeader("Pragma", "No-cache"); OutputStream ouputStream = response.getOutputStream(); failWorkbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } return "importManage"; }
导入验证包
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-validator</artifactId> <version>5.2.4.Final</version> </dependency>
domain类加上验证的注解
@Excel(name = "用户名") @NotNull(message = "用户名不为空") private String username; private String password; @Excel(name = "邮箱",width = 20) private String email; @Excel(name = "头像",type = 2,height = 30) private String headImage; @Excel(name = "年龄") @Max(value = 80,message = "年龄最大值不能超过80") @Min(value = 20,message = "年龄最大值不能小于20") private Integer age; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "department_id") @ExcelEntity//导出的是实体对象 private Department departmentId;
加上自定义验证
扫描自定义验证的包 <context:component-scan base-package="cn.king.aisell.common" />
自定义验证功能
@Component public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> { @Autowired private IEmployeeService iemployeeService; @Override public ExcelVerifyHandlerResult verifyHandler(Employee employee) { //写逻辑验证用户名是否重复 boolean success = iemployeeService.checkName(employee.getUsername()); if(success){ //true代表可以用 return new ExcelVerifyHandlerResult(true); } return new ExcelVerifyHandlerResult(false,"用户名重复"); } }
完成controller上传
//上传员工excel的功能 @RequestMapping("/employeeXlsx") public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{ //设置导入的参数 ImportParams params = new ImportParams(); //设置需要验证 params.setNeedVerfiy(true); params.setHeadRows(1); //加入自定义验证 params.setVerifyHandler(employeeExcelVerifyHandler); //获得excel但如的结果 ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore( empFile.getInputStream(), Employee.class, params); //拿到正确的数据，然后把它们保存到数据库 List<Employee> list = result.getList(); list.forEach(e ->{ //从数据库中查询部门 String deptName = e.getDepartmentId().getName(); Department dept = iDepartmentService.findByName(deptName); e.setDepartmentId(dept); //设置初始密码 String md5EncryptionByPassword = MD5EncryptionByPassword.MD_5_ENCRYPTION_BY_PASSWORD.getMD5EncryptionByPassword("123"); e.setPassword(md5EncryptionByPassword); iEmployeeService.save(e); }); //如果有错误 if(result.isVerfiyFail()) { //获得Workbook对象，以返回失败文档 Workbook failWorkbook = result.getFailWorkbook(); //设置响应的文件类型 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //attachment:不要用浏览器打开 response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx"); //设置不要缓存 response.setHeader("Pragma", "No-cache"); OutputStream ouputStream = response.getOutputStream(); failWorkbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } return "importManage"; }
