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