一.导入excel
注意:敲代码之前需要加载hutool的依赖和poi的依赖,poi.version 要高于3.17版本,这个17理解为十七,比这个版本小的一般有3.8,3.9之类的,不废话了,具体依赖在下面
cn.hutool hutool-poi 4.6.6org.apache.poi poi-ooxml 3.17
下面直接上代码
ExcelReader reader = ExcelUtil.getReader("d://tmp//test.xlsx", 0); assembleReader(reader); List all = reader.readAll(ImportEmploymentEntity.class); Employment employmentList = BeanUtils.transform(all, Employment.class);
private void assembleReader(ExcelReader reader) { reader.addHeaderAlias("岗位", "position"); reader.addHeaderAlias("岗位类型", "positionType"); reader.addHeaderAlias("工作地点", "workplace"); reader.addHeaderAlias("工作经验", "workExperience"); reader.addHeaderAlias("年龄限制", "ageRange"); reader.addHeaderAlias("教育要求", "education"); reader.addHeaderAlias("待遇", "treatment"); reader.addHeaderAlias("需要人数", "number"); reader.addHeaderAlias("工作职责", "responsibility"); reader.addHeaderAlias("工作要求", "qualification"); reader.addHeaderAlias("发布日期", "releaseDate"); reader.addHeaderAlias("截止日期", "deadline"); }
在这里我是获取本地d盘tmp目录下test.xlsx文件,如下是内容
![cf10f64123b94302a66f96cfe0fd902c.png](https://i-blog.csdnimg.cn/blog_migrate/70749b5f8f03a59686fca178580d61cc.jpeg)
接收到的对象这两个参数都是有的,当然可以不使用对象接,官方提供了其他方式接参数,例如map等,我这种方式针对数据量较多的excel可能效率较低,官方提供了Excel03SaxReader,Excel07SaxReader分别针对excel2003,excel2007处理大型数据的excel,有兴趣的小伙伴可以去了解一下,我现在的实现可以满足业务就不继续研究了。
二.导出excel
依赖同上
还是直接上代码
ArrayList mysqlLoggerVos = CollUtil.newArrayList(mysqlLoggerVoList); ExcelWriter writer = ExcelUtil.getWriter(); writer = assembleSystemWriter(writer); writer.write(mysqlLoggerVos, true); write(writer, response);
private ExcelWriter assembleSystemWriter(ExcelWriter writer) { Sheet sheet = writer.getSheet(); writer.setSheet(0); writer.renameSheet(0, "系统日志记录"); writer.getSheet(); writer.setSheet("系统日志记录"); for (int i = 0; i < 10; i++) { if (i <= 6) { sheet.setColumnWidth(i, 4000); } else { sheet.setColumnWidth(i, 8000); } } writer.merge(9, "审计日志记录"); writer.addHeaderAlias("dbName", "数据库名称"); writer.addHeaderAlias("dbType", "数据库类型"); writer.addHeaderAlias("dbIp", "数据库ip"); writer.addHeaderAlias("userName", "用户名"); writer.addHeaderAlias("ip", "登陆源ip地址"); writer.addHeaderAlias("mac", "登陆MAC"); writer.addHeaderAlias("operateType", "操作事件"); writer.addHeaderAlias("loggerType", "日志类型"); writer.addHeaderAlias("operateContent", "操作详情"); writer.addHeaderAlias("operateTime", "操作时间"); return writer; }
private void write(ExcelWriter writer, HttpServletResponse response) throws IOException { // 一次性写出内容,使用默认样式,强制输出标题 // response为HttpServletResponse对象 response.setContentType("application/vnd.ms-excel;charset=utf-8"); // test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码 response.setHeader("Content-Disposition", "attachment;filename=logger_record.xls"); ServletOutputStream out = response.getOutputStream(); writer.flush(out, true); // 关闭writer,释放内存 writer.close(); // 此处记得关闭输出Servlet流 IoUtil.close(out); }
在这里我们直接将一個list直接写出,具体需要的参数,格式调整,请参考官方api,有问题可以在下面留言哦