一、POI实现导入导出
1.引入依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency
2.Excel的创建和导出
创建Excel以99乘法表为例
public class ExcelTest { //创建一个Excel文件 //在文件里加一个99乘法法 //把这文件保存在项目中 @Test public void testCreateExcel() throws Exception{ //1.创建一个Excel文件(内存中) SXSSFWorkbook wb = new SXSSFWorkbook(); //2.创建一张表 Sheet sheet = wb.createSheet("99乘法表"); //3.创建行 for (int i = 1; i <= 9; i++) { Row row = sheet.createRow(i-1); //4.创建列(格子) for (int j = 1; j <= i; j++) { Cell cell = row.createCell(j-1); //5.格子中加数据 cell.setCellValue(i+"*"+j+"="+(i*j)); } } //从内存中写出来 FileOutputStream out = new FileOutputStream("99.xlsx"); wb.write(out); out.close(); } }
读取Excel
@Test public void readExcel() throws Exception{ File file = new File("employee-3.xlsx"); FileInputStream fis = new FileInputStream(file); //1.读取一个Excel文件(内存中) Workbook wb = new XSSFWorkbook(fis); //2.拿到第个sheet表 Sheet sheet = wb.getSheetAt(0); //3.拿到wb中的行(不要拿头部) int lastRowNum = sheet.getLastRowNum(); for (int i = 1; i <= lastRowNum; i++) { Row row = sheet.getRow(i); //4.拿到每一列(格子) short lastCellNum = row.getLastCellNum(); for (int j = 0; j < lastCellNum; j++) { Cell cell = row.getCell(j); System.out.print(cell.getStringCellValue()+" "); } System.out.println(); } }
3.SpringMVC导出
设置下载配置
//下面设置好直接使用即可导出 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型 response.setHeader("Content-disposition", "attachment;filename="+filename); response.setHeader("Pragma", "No-cache");//设置不要缓存 OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close();
导出js页面设置
<a href="/employee/download " class="easyui-linkbutton" iconCls="icon-redo" plain="true">导出</a>
导出Controller实现
@RequestMapping("/download") public void download(HttpServletResponse response) throws Exception{ //准备下载的文件名 String filename = "employee.xlsx"; response.setHeader("Content-disposition", filename); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型 response.setHeader("Content-disposition", "attachment;filename="+filename); response.setHeader("Pragma", "No-cache"); //1.创建一个Excel文件(内存中) SXSSFWorkbook wb = new SXSSFWorkbook(); //2.创建一张表 Sheet sheet = wb.createSheet("99乘法表"); //3.创建行 for (int i = 1; i <= 9; i++) { Row row = sheet.createRow(i-1); //4.创建列(格子) for (int j = 1; j <= i; j++) { Cell cell = row.createCell(j-1); //5.格子中加数据 cell.setCellValue(i+"*"+j+"="+(i*j)); } } //从内存中写出来 OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
4.EasyPOI导入导出Excel
引入依赖(和POI的冲突)
<!-- 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>
准备一个类
public class Employee implements Serializable { private Long id; @Excel(name = "用户名称") private String username; @Excel(name = "邮件",width = 20) private String email; // 省略了getter,setter }
EasyPOI导出测试
@Test public void testExcel() throws Exception{ //准备员工数据 Employee e1 = new Employee(); e1.setId(1L); e1.setUsername("张三"); e1.setEmail("zhang@qq.com"); Employee e2 = new Employee(); e2.setId(2L); e2.setUsername("李四"); e2.setEmail("li@qq.com"); List<Employee> list = new ArrayList<>(); list.add(e1); list.add(e2); /** * 进行相应的展出 * 参数1:一些基本配置(表头等) * 参数2:导出的类型 * 参数3:导出的数据 */ Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), Employee.class, list); //保存数据 FileOutputStream fos = new FileOutputStream("emp.xls"); workbook.write(fos); fos.close(); }
问题:类中的字段是Boolean或者对象怎么办?
@ExcelTarget(value="emp")//注意要在类名上面加这个注解 public class Employee { @Excel private Integer age = 18; @Excel(name = "性别",replace={"男_true","女_false"}) private Boolean sex = true; @ExcelEntity(id="dept") //对应另一个关连对象 private Department department; @Excel(name="头像",type = 2) //type = 2 :代表这个一个图片展示 private String headImage;
@ExcelTarget(value="dept") public class Department { //部门名称 @Excel(name="部门名称_emp,名称_dept") private String name;
代码测试
Department department1 = new Department(); department1.setId(1L); department1.setName("教学部"); Department department2 = new Department(); department2.setId(2L); department2.setName("IT部"); //准备员工数据 Employee e1 = new Employee(); e1.setId(1L); e1.setUsername("张三"); e1.setEmail("zhang@qq.com"); e1.setDepartment(department1); e1.setHeadImage("images/user.png"); Employee e2 = new Employee(); e2.setId(2L); e2.setUsername("李四"); e2.setEmail("li@qq.com"); e2.setDepartment(department2); e2.setHeadImage("images/user-red.png"); List<Employee> list = new ArrayList<>(); list.add(e1); list.add(e2); /** * 进行相应的展出 * 参数1:一些基本配置(表头等) * 参数2:导出的类型 * 参数3:导出的数据 */ Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("员工信息","员工数据"), Employee.class, list); //保存数据 FileOutputStream fos = new FileOutputStream("emp.xls"); workbook.write(fos); fos.close();
controller实现
@RequestMapping("/download") public String download(ModelMap map,EmployeeQuery query, HttpServletRequest request) { //拿到所有数据 List<Employee> list = employeeService.findByQuery(query); //获取到真实路径 //解决了下载的图片的路径问题 String realPath = request.getServletContext().getRealPath(""); list.forEach(e -> { e.setHeadImage(realPath+e.getHeadImage()); System.out.println(e.getHeadImage()); }); //设置一些属性 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, "employee");//文件名称 //返回的名称 :easypoiExcelView -> 并没有找我的bean,而且当做一个路径去进行访问 // 现在默认去找的视图解析器,而没有找我的那一个bean return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称 }
特别注意:要使用 return NormalExcelConstants.EASYPOI_EXCEL_VIEW
spring的.xml 需要加上:
<!-- 扫描easypoi的一些view:视图 --> <context:component-scan base-package="cn.afterturn.easypoi.view" /> <!-- bean的视图解析器 p:order="0":顺序在最前面 --> <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />
EasyPOI导入
导入页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> <%@include file="/WEB-INF/views/head.jsp" %> </head> <body> <!-- 上传必需是:post,enctype="multipart/form-data"--> <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> </body> </html>
设置验证
<!-- JSR 303 规范验证包 --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-validator</artifactId> <version>5.2.4.Final</version> </dependency>
@Excel(name="用户名") @NotNull(message = "用户名不为空") private String username; private String password; @Excel(name="邮件",width = 25) private String email; @Excel(name="年纪") @Max(value = 80,message = "max 最大值不能超过80") private Integer age;
controller实现
@RequestMapping("/employeeXlsx") public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception { //准备导入的参数 ImportParams params = new ImportParams(); params.setTitleRows(1); // need:需要 verfiy:核实,验证 params.setNeedVerfiy(true); //需要验证 //excel导入的一个结果 ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore( empFile.getInputStream(), Employee.class, params); //引入正确的list(正常保存) result.getList().forEach(e ->{ // System.out.println(e+","+e.getDepartment()); //根据部门名称拿到它的部门,再放到对应的员工中 Department dept = departmentService.findByName(e.getDepartment().getName()); e.setDepartment(dept); //给一个默认密码 e.setPassword("123"); employeeService.save(e); }); // //引入错误的list // result.getFailList().forEach(e ->{ // System.out.println("错误的:"+e); // }); //如果有错误,就直接导出错误文件到前台 // Verfiy:检验 Fail:失败 if(result.isVerfiyFail()){ //如果验证失败,代码到这里面来 //失败的文件已经准备好了 Workbook failWorkbook = result.getFailWorkbook(); //把这个文件导出 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型 response.setHeader("Content-disposition", "attachment;filename=error.xlsx"); response.setHeader("Pragma", "No-cache");//设置不要缓存 OutputStream ouputStream = response.getOutputStream(); failWorkbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } return "import"; }
自定义验证规则
实现IExcelVerifyHandler接口,扫描他并交给spring管理
<context:component-scan base-package="cn.itsource.aisell.common" />
/** * 准备一个自定义验证 */ @Component public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> { @Autowired private IEmployeeService employeeService; @Override public ExcelVerifyHandlerResult verifyHandler(Employee employee) { ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true); //如果存在,返回false if(!employeeService.checkUsername(employee.getUsername())){ //代表用户名重复 result.setMsg("用户名重复"); result.setSuccess(false); } return result; } }
@Autowired private EmployeeExcelVerifyHandler employeeExcelVerifyHandler; @RequestMapping("/employeeXlsx") public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception { //准备导入的参数 ImportParams params = new ImportParams(); params.setTitleRows(1); // need:需要 verfiy:核实,验证 params.setNeedVerfiy(true); //需要验证 //加入自定义验证 params.setVerifyHandler(employeeExcelVerifyHandler); .... return "import"; }