本章提供了java使用阿里的easyexcel实现Excel导入,导出示例,还有网络下载的方法。
模板大家可以随意制作,本例中使用模板表头及数据已经截图,以供参考。
easyexcel背景:
Java解析,生成Excel比较有名的框架有Apache poi,jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版的Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大.easyexcel重写了POI对07版的Excel的解析,能够原本一个3M的Excel的用POI sax依然需要100M左右内存降低到KB级别,并且再大的Excel中不会出现内存溢出,03版依赖POI的萨克斯模式。在上层做了模型转换的封装,让使用者更加简单方便。
优缺点比较:
easyexcel在解析耗时上比poiuserModel模式弱了一些主要原因是我内部采用了反射做模型字段映射,中间我也加了缓存,但感觉这点差距可以接受的但在内存消耗上差别就比较明显了,easye userxodel就不一大了,简直就要爆掉了。想想一个excel解析200M,同时有20个人再用估计一台机器就挂了。
本例中,工具类整合了easyexcel的导入导出,对导入的数据操作在监听类中实现,在这里可以做一些自己的业务逻辑,这也是使用easyexcel的一个原因,可以更加清晰的去关注业务逻辑。
1.准备:d:\\ 2007.xlsx
内容如下:
2.添加依赖(建议去maven仓库搜索easyexcel下载最新的依赖)
<groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beat1</version>
3.添加Excel的映射实体
public class ExcelEntity extends BaseRowModel { @ExcelProperty(index = 0 , value = "工号") private String staff_code; @ExcelProperty(index = 1 , value = "姓名") @ExcelProperty(index = 2 , value = "性别") @ExcelProperty(index = 3 , value = "联系电话") @ExcelProperty(index = 4 , value = "邮箱") @ExcelProperty(index = 5 , value = "微信号") @ExcelProperty(index = 6 , value = "部门") private Integer department_id; @ExcelProperty(index = 7 , value = "职位") private Integer position_id; public String getStaff_code() { public void setStaff_code(String staff_code) { this.staff_code = staff_code; public String getName() { public void setName(String name) { public void setSex(String sex) { public void setTel(String tel) { public String getEmail() { public void setEmail(String email) { public String getWeixin() { public void setWeixin(String weixin) { public Integer getDepartment_id() { public void setDepartment_id(Integer department_id) { this.department_id = department_id; public Integer getPosition_id() { public void setPosition_id(Integer position_id) { this.position_id = position_id; public String toString() { "staff_code='" + staff_code + '\'' + ", name='" + name + '\'' + ", email='" + email + '\'' + ", weixin='" + weixin + '\'' + ", department_id=" + department_id + ", position_id=" + position_id +
4.添加导入数据监听类
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.zdhs.cms.common.excelUtils.entity.ExcelEntity; import java.util.ArrayList; * 整个excel解析结束会执行doAfterAllAnalysed()方法 * 下面只是我写的一个样例而已,可以根据自己的逻辑修改该类。 public class ExcelListener extends AnalysisEventListener { private List<Object> datas = new ArrayList<Object>(); public void invoke(Object object, AnalysisContext context) { System.out.println("当前行:"+context.getCurrentRowNum()); System.out.println(object); datas.add(object);//数据存储到list,供批量处理,或后续自己业务逻辑处理。 doSomething(object);//根据自己业务做处理 private void doSomething(Object object) { ExcelEntity excel = (ExcelEntity) object; public void doAfterAllAnalysed(AnalysisContext context) { // datas.clear();//解析结束销毁不用的资源 public List<Object> getDatas() { public void setDatas(List<Object> datas) {
5.编写导入导出工具类
import com.alibaba.excel.ExcelReader; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.BaseRowModel; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.support.ExcelTypeEnum; import com.zdhs.cms.common.excelUtils.entity.ExcelEntity; import com.zdhs.cms.common.excelUtils.listen.ExcelListener; import java.util.ArrayList; public class ExcelUtils { * @param clazz Excel实体映射类 public static Boolean readExcel(InputStream is, Class clazz){ BufferedInputStream bis = null; bis = new BufferedInputStream(is); AnalysisEventListener listener = new ExcelListener(); ExcelReader excelReader = EasyExcelFactory.getReader(bis, listener); excelReader.read(new Sheet(1, 1, clazz)); } catch (IOException e) { * @param clazz Excel实体映射类 public static Boolean writeExcel(OutputStream os, Class clazz, List<? extends BaseRowModel> data){ BufferedOutputStream bos= null; bos = new BufferedOutputStream(os); ExcelWriter writer = new ExcelWriter(bos, ExcelTypeEnum.XLSX); //写第一个sheet, sheet1 数据全是List<String> 无模型映射关系 Sheet sheet1 = new Sheet(1, 0,clazz); writer.write(data, sheet1); } catch (IOException e) { public static void main(String[] args) { FileInputStream fis = null; fis = new FileInputStream("D:\\2007.xlsx"); Boolean flag = ExcelUtils.readExcel(fis, ExcelEntity.class); System.out.println("导入是否成功:"+flag); } catch (FileNotFoundException e) { } catch (IOException e) { FileOutputStream fos = null; fos = new FileOutputStream("D:\\export.xlsx"); //FileOutputStream fos, Class clazz, List<? extends BaseRowModel> data List<ExcelEntity> list = new ArrayList<>(); for (int i = 0; i < 5; i++){ ExcelEntity excelEntity = new ExcelEntity(); excelEntity.setName("我是名字"+i); Boolean flag = ExcelUtils.writeExcel(fos,ExcelEntity.class,list); System.out.println("导出是否成功:"+flag); } catch (FileNotFoundException e) { } catch (IOException e) {
6.大功告成!在ExcelUtils中运行主方法,简单的导入导出就已经完成了!
7.运行结果截图:
7.1导入数据控制台打印:
7.2导出数据添加姓名+循环次数:
另外,阿里easyexcel还提供了复杂格式的表头使用,和表格样式,有需要的小伙伴可以去github上一睹真容。
附:阿里巴巴开源easyexcel github地址:https://github.com/alibaba/easyexcel
8.最后多一嘴,文件导入,导出,下载模板的时候,Controller层的写法也加上来,记录一下。
* @Date : 2019/5/24 17:03 * @Description : TODO用一句话描述此类的作用 @Api(tags = "ExcelDownloadController", description = "Excel 导入下载模板,导出 相关接口") @RequestMapping("/excel") public class ExcelDownloadController { private static final Logger log = LoggerFactory.getLogger(ExcelDownloadController.class); StaffService staffService; @ApiOperation(value="接口说明",httpMethod="POST",notes="单个文件上传") @RequestMapping(value = "/upload") public ResponseMsg upload(@RequestParam("file") MultipartFile file) { ResponseMsg msg = new ResponseMsg(); String fileName = file.getOriginalFilename(); log.info("上传的文件名为:" + fileName); String suffixName = fileName.substring(fileName.lastIndexOf(".")); log.info("文件的后缀名为:" + suffixName); File tmpFile = File.createTempFile("tmp", null); //将MultipartFile 转换为 File 临时文件 file.transferTo(tmpFile); InputStream inputStream = new FileInputStream(tmpFile); ExcelUtils.readExcel(inputStream, ExcelModel.class); } catch (IllegalStateException e) { } catch (IOException e) { @ApiOperation(value="接口说明",httpMethod="POST",notes="下载模板,用于填写导入数据") @RequestMapping("/downloadExcel") public void cooperation(HttpServletRequest request, HttpServletResponse response) { ServletOutputStream out = null; out = response.getOutputStream(); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); String fileName = "导入模板"; response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName,"UTF-8")+".xlsx"); ExcelUtils.writeExcel(out,ExcelModel.class,null); } catch (IOException e) { } catch (IOException e) { @ApiOperation(value="接口说明",httpMethod="POST",notes="导出数据文件") @RequestMapping("/downloadExcelData") public void cooperationData(HttpServletRequest request, HttpServletResponse response) { ServletOutputStream out = null; out = response.getOutputStream(); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); String fileName = "导出明细"; response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName,"UTF-8")+".xlsx"); //List<ExcelModel> data = 此处为 数据接口 返回一个list ; System.out.println("把数据明细放在list data中:请完善查询数据接口调用,并把查询结果写入list data中"); Boolean flag = ExcelUtils.writeExcel(out, ExcelModel.class, data); System.out.println("导出是否成功:"+flag); } catch (IOException e) { } catch (IOException e) {
感谢博主:https://www.cnblogs.com/kungfupanda/p/9472137.html
|