在Maven项目中使用easypoi完成Excel文件上传下载

导包:

 1 <!-- easypoi的支持 --> 2 <dependency> 3 <groupId>cn.afterturn</groupId> 4 <artifactId>easypoi-base</artifactId> 5 <version>3.2.0</version> 6 </dependency> 7 <dependency> 8 <groupId>cn.afterturn</groupId> 9 <artifactId>easypoi-web</artifactId>10 <version>3.2.0</version>11 </dependency>12 <dependency>13 <groupId>cn.afterturn</groupId>14 <artifactId>easypoi-annotation</artifactId>15 <version>3.2.0</version>16 </dependency>

 

一:文件下载

  

 1:前台准备导出按钮

 

 

 

1 <button class="easyui-linkbutton" iconCls="icon-search">数据导出</button>

  1.2 准备好view(beanName的视图解析器)

   在applicationContext-mvc.xml

<!--配置一下beanName的视图解析器 p:order="0":设置属性优先级--><bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" /><!--<bean class="org.springframework.web.servlet.view.BeanNameViewResolver"> <property name="order" value="0" /></bean>--><!-- 对于easypoi中准备发的视图(view)的扫描 --><context:component-scan base-package="cn.afterturn.easypoi.view" />

  1.3:在DownloadController完成后台导出

  

/** * 导出就是下载(下载就是把一个流 从服务器端 -> 客户端) * * EasypoiSingleExcelView : 注解导出的view */@RequestMapping("/export")public String export(EmployeeQuery query,ModelMap map, HttpServletRequest request){ //根据查询条件拿到所有数据 List<Employee> list = employeeService.queryAll(query); //导出的属性 ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF); //params.setFreezeCol(2); 冻结 //request:获取到真实路径 String realPath = request.getServletContext().getRealPath(""); System.out.println(realPath); list.forEach(e->{ e.setHeadImage(realPath+e.getHeadImage()); }); map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合 map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体 map.put(NormalExcelConstants.PARAMS, params);//参数 map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称 //根据返回的名称去找一个bean对象(在SpringMVC称之为view) // /WEB-INF/views/easypoiExcelView // 我们直接要文档的的操作是没有成功的 2.文档的情况和我们项目中的情况有一点区别 return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称 "easypoiExcelView"}

 

 

  二:上传功能

  1:配置上传解析器注意,名字必需叫 multipartResolver

 1 <!--配置上传解析器--> 2 <bean id="multipartResolver" 3 class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> 4 <!-- 默认编码 --> 5 <property name="defaultEncoding" value="utf-8" /> 6 <!-- 文件大小最大值 --> 7 <property name="maxUploadSize" value="10485760000" /> 8 <!-- 内存中的最小值 --> 9 <property name="maxInMemorySize" value="40960" />10 </bean>

  2 准备导入页面import.jsp

 1 <%@ page contentType="text/html;charset=UTF-8" language="java" %> 2 <html> 3 <head> 4 <title>Title</title> 5 <%@ include file="/WEB-INF/views/head.jsp"%> 6 </head> 7 <body> 8  9 <%--10  上传只能是post请求11 上传必需设置 : enctype="multipart/form-data"12 --%>13 <!-- 上传必需是:post,enctype="multipart/form-data"-->14 <form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">15 <input class="easyui-filebox" name="empFile" style="width:80%"16 data-options="prompt:‘选择一个文件...‘,buttonText: ‘选择文件‘" />17 <button class="easyui-linkbutton">导入</button>18 </form>19 </body>20 </html>

  

  3: 后台ImportController完成导入功能

 1 @Controller 2 @RequestMapping("/import") 3 public class ImportController extends BaseController { 4  5  @Autowired 6 private IDepartmentService departmentService; 7  8 @RequestMapping("/index") 9 public String index(){10 return "import";11  }12 13 14 15 /**16  * 上传的文件使用 MultipartFile类型来接收17  * @param empFile18  * @return19 */20 @RequestMapping("/employeeXlsx")21 public String employeeXlsx(MultipartFile empFile) throws Exception {22 // System.out.println(empFile);23 // System.out.println(empFile.getName()); //empFile:上传控件的名称24 // System.out.println(empFile.getOriginalFilename()); //emp-poi.xlsx:上传文件的名称25 // System.out.println(empFile.getContentType()); //mime类型 office excel 200726 // System.out.println(empFile.getSize()); //文件大小27 //我们需要拿到上传的文件(输入流)28 // empFile.getInputStream()29 //一.使用EasyPoi获取文件数据30 //1.1 设置参数31 ImportParams params = new ImportParams();32 //params.setTitleRows(1);33 params.setHeadRows(1);34 // 1.2 拿到文件中的数据35 List<Employee> list = ExcelImportUtil.importExcel(empFile.getInputStream(),36 Employee.class, params);37 //二.保存员工数据38 list.forEach(e->{39 //导入时加上一个默认密码 40 e.setPassword("123");41 //通过部门名称拿到部门数据(要有id)42 Department dept = departmentService.findByName(e.getDepartment().getName());43 //把部门设置到员工数据中44  e.setDepartment(dept);45  employeeService.save(e);46  } );47 48 return "import";49  }50 51 }

 

  

三.导入验证功能

  1 导入验证包支持 pom.xml

1 <!-- JSR 303 规范验证包 -->2 <dependency>3 <groupId>org.hibernate</groupId>4 <artifactId>hibernate-validator</artifactId>5 <version>5.2.4.Final</version>6 </dependency>

 

  2 domain中添加验证方法

  注解普通验证

 1 @Entity 2 @Table(name = "employee") 3 public class Employee extends BaseDomain { 4  5 @Excel(name = "用户名") 6 @NotNull(message = "用户名不能空") 7 private String username; 8 private String password; 9 @Excel(name = "年龄")10 @Max(value = 100)11 @Min(value = 18)12 private Integer age;13 @Excel(name = "邮箱",width = 20)14  @NotNull15 private String email;16  ...17 }

 

   3:自定义验证

 1 实现IExcelVerifyHandler 2  3  4  5 /** 6  * 自定义验证(我们会在这里做唯一性的验证) 7 */ 8 @Component 9 public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {10 11  @Autowired12 private IEmployeeService employeeService;13 /**14  *15  * ExcelVerifyHandlerResult16  * suceess :代表验证成功还是失败(如果用户名重复,就代表失败)17  * msg:失败的原因18 */19  @Override20 public ExcelVerifyHandlerResult verifyHandler(Employee employee) {21 22 ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);23 //如果根据用户名获取到用户,代表这个用户已经存在24 Employee tempEmp = employeeService.findByUsername(employee.getUsername());25 if(tempEmp!=null){26 result.setSuccess(false);27 result.setMsg("用户名重复");28  }29 30 return result;31  }32 }
把这个类交给Spring管理(千万不要忘了让SpringMvc去扫描到它)
1 <!--扫描工具包-->2 <context:component-scan base-package="com.logo.aisell.util"/>

  4实现验证功能

@Controller@RequestMapping("/import")public class ImportController extends BaseController { @Autowired private IEmployeeService employeeService; @Autowired private IDepartmentService departmentService; @Autowired private EmployeeExcelVerifyHandler employeeExcelVerifyHandler; @RequestMapping("/index") public String index(){ return "import"; } @RequestMapping("/employeeXlsx") public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception { //一.使用EasyPoi获取文件数据 ImportParams params = new ImportParams(); params.setHeadRows(1); params.setNeedVerfiy(true); //设置验证支持 params.setVerifyHandler(employeeExcelVerifyHandler); //设置一个验证处理器 //二.获取excel中的数据,封装成了一个结果对象(有很多东西) ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore( empFile.getInputStream(), Employee.class, params); //三.获到正确的数据,并把它们保存到数据库 List<Employee> list = result.getList(); list.forEach(e->{ e.setPassword("123"); Department dept = departmentService.findByName(e.getDepartment().getName()); e.setDepartment(dept); employeeService.save(e); }); //四.如果有错误,把错误数据返回到前台(让前台下载一个错误的excel) //4.1判断是否有错误 if(result.isVerfiyFail()){ //4.2拿到错误的文件薄 Workbook failWorkbook = result.getFailWorkbook(); //把这个文件导出 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型 response.setHeader("Content-disposition", "attachment;filename=error.xlsx"); //告诉浏览下载的是一个附件,名字叫做error.xlsx response.setHeader("Pragma", "No-cache");//设置不要缓存 OutputStream ouputStream = response.getOutputStream(); failWorkbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } return "import"; }}

 

相关文章