文章目录前言一、依赖二、读取excel指定sheet页数据分批处理1.excel列名对应的映射实体类2.监听类用于处理读取的excel行数据3.测试三、读取小数据量excel不分批四、数据写入excel1.数据量比较小可以一次性写入2.数据量大分批多次写入五、excel文件的上传和下载1.下载2. 上传六、分批读取并写入综合示例前言EasyExcel是一个专为Java设计的高效Excel处理工具它旨在帮助开发者在处理大文件时避免内存溢出问题同时提供简单易用的API来进行Excel的读写操作。相较于Apache POI和jxl等其他库EasyExcel通过优化的解析算法显著降低了内存消耗特别是对于07版以上的Excel文件其内存占用量远低于使用POI SAX模式解析时的需求几乎消除了内存溢出的风险。此外EasyExcel对03版Excel的支持则是基于POI的SAX模式并进行了进一步的模型转换封装提高了使用的便捷性。easyexcel githubeasyexcel 官网简介easyexcel 读、写、填充示例一、依赖dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion4.0.3/version/dependency二、读取excel指定sheet页数据分批处理假设有 excel 如下1.excel列名对应的映射实体类使用ExcelProperty(value “”)来标识excel列名importcom.alibaba.excel.annotation.ExcelProperty;importlombok.Data;importjava.math.BigDecimal;importjava.util.Date;DatapublicclassRowData{/** 企业订单号 */ExcelProperty(value企业订单号)privateStringenterpriseOrderNo;/** 所在部门新编号 */ExcelProperty(value所在部门新编号)privateIntegerdepartmentNewNo;/** 乘车人姓名 */ExcelProperty(value乘车人姓名)privateStringpassengerName;/** 订单总金额 */ExcelProperty(value订单总金额)privateBigDecimaltotalOrderAmount;/** 企业实付金额 */ExcelProperty(value企业实付金额)privateBigDecimalenterpriseActualPayment;/** 订单类型 */ExcelProperty(value订单类型)privateStringorderType;/** 下单时间 */ExcelProperty(value下单时间)privateDateorderTime;}2.监听类用于处理读取的excel行数据importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.read.listener.ReadListener;importcom.alibaba.excel.util.ListUtils;importcom.alibaba.fastjson2.JSON;importhai.tang.model.RowData;importjava.util.List;// 有个很重要的点 RowDataListener 不能被spring管理所以如果使用spring要每次读取excel都要new,然后里面用到spring可以构造方法传进去publicclassRowDataListenerimplementsReadListenerRowData{/** * 每1000条存储数据库或者进行其他操作然后清理list 方便内存回收 */privatestaticfinalintBATCH_COUNT1000;/** * 缓存的数据 */privateListRowDatacachedDataListListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/** * 假设这个是一个DAO当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */// private DemoDAO demoDAO;// public RowDataListener() {// 这里是demo所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数// demoDAO new DemoDAO();// }/** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * param demoDAO */// public RowDataListener(DemoDAO demoDAO) {// this.demoDAO demoDAO;// }/** * 这个每一条数据解析都会来调用 * * param data one row value. Is is same as {link AnalysisContext#readRowHolder()} * param context */Overridepublicvoidinvoke(RowDatadata,AnalysisContextcontext){System.out.println(解析到一条数据:{}JSON.toJSONString(data));cachedDataList.add(data);// 达到BATCH_COUNT了需要去存储一次数据库防止数据几万条数据在内存容易OOMif(cachedDataList.size()BATCH_COUNT){saveData();// 存储完成清理 listcachedDataListListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/** * 所有数据解析完成了 都会来调用 * * param context */OverridepublicvoiddoAfterAllAnalysed(AnalysisContextcontext){// 这里也要保存数据确保最后遗留的数据也存储到数据库saveData();System.out.println(所有数据解析完成);}/** * 存储到数据库或者可改为对数据进行其他操作 */privatevoidsaveData(){System.out.println({}条数据开始存储数据库cachedDataList.size());//demoDAO.save(cachedDataList);System.out.println(存储数据库成功);}}3.测试StringfileNameC:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx;// 这里 需要指定读用哪个class去读然后读取用车订单sheet默认第一行是表头会默认从第二行开始读。结束后文件流会自动关闭EasyExcel.read(fileName,RowData.class,newRowDataListener()).sheet(用车订单).doRead();//如果前3行是表头要从第4行开始读取可以使用headRowNumber(3)//EasyExcel.read(fileName, RowData.class, new RowDataListener()).sheet(用车订单).headRowNumber(3).doRead();三、读取小数据量excel不分批如果上传的excel文件只有几千行数据量不大可以不分批读取。下面的示例读取excel文件获取第一个sheet页里从第4行开始的数据行前3行是表头。Controller层RestControllerRequestMapping(value/apiList)Api(tags接口列表)Slf4jpublicclassUsercontroller{AutowiredUserConsumeruserConsumer;ApiOperation(excel文件读取)GetMapping(value/v1/readExcel,consumesMediaType.MULTIPART_FORM_DATA_VALUE)publicvoidreadExcel(RequestParam(valuefile)MultipartFilefile)throwsIOException{userConsumer.readExcel(file);}}excel字段映射类/** *excel里的列名映射 */DataExcelIgnoreUnannotatedpublicclassExcelRowModel{ExcelProperty(姓名)privateStringname;ExcelProperty(性别)privateStringgender;ExcelProperty(分数)privateStringscore;}excel监听类importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.event.AnalysisEventListener;importcom.xiaohaitang.somedemo.model.ExcelRowModel;importlombok.extern.slf4j.Slf4j;importjava.util.ArrayList;importjava.util.List;Slf4jpublicclassUploadExcelListenerextendsAnalysisEventListenerExcelRowModel{privateListExcelRowModeldtoListnewArrayList();publicListExcelRowModelgetList(){returndtoList;}Overridepublicvoidinvoke(ExcelRowModelconfigsDto,AnalysisContextanalysisContext){dtoList.add(configsDto);}OverridepublicvoiddoAfterAllAnalysed(AnalysisContextanalysisContext){log.info(EXCECL明细信息sheet页读取完成, 读取到的明细信息条数: {},dtoList.size());}}service类:importcom.alibaba.excel.EasyExcelFactory;importcom.alibaba.excel.ExcelReader;importcom.alibaba.excel.read.metadata.ReadSheet;importcom.xiaohaitang.somedemo.model.ExcelRowModel;importcom.xiaohaitang.somedemo.utils.JsonUtils;importlombok.extern.slf4j.Slf4j;importorg.springframework.stereotype.Service;importorg.springframework.web.multipart.MultipartFile;importjava.io.IOException;importjava.util.List;Slf4jServicepublicclassUserConsumer{publicvoidreadExcel(MultipartFilefiles)throwsIOException{ListExcelRowModelexcelRowsparseSelfDrivingFromExcel(files);//处理excel里读取到的数据行 excelRows//todo}//解析获取上传的excel文件privateListExcelRowModelparseSelfDrivingFromExcel(MultipartFileexcel)throwsIOException{UploadExcelListenerlistenernewUploadExcelListener();ExcelReaderexcelReaderEasyExcelFactory.read(excel.getInputStream()).build();//获取第一个sheet页里从第4行开始的数据ReadSheetbatchInfoSheetEasyExcelFactory.readSheet(0).head(ExcelRowModel.class).headRowNumber(3).registerReadListener(listener).build();excelReader.read(batchInfoSheet);returnlistener.getList();}}四、数据写入excel1.数据量比较小可以一次性写入StringfileNameC:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx;//从数据库查询出的数据或者构造的数据ListRowDatalist...;//如果数据只有一万行左右可以一次性写入。将数据写入 用车订单 sheet 页EasyExcel.write(fileName,RowData.class).sheet(用车订单).doWrite(list);2.数据量大分批多次写入StringfileNameC:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx;try(ExcelWriterexcelWriterEasyExcel.write(fileName,RowData.class).build()){// 这里注意 如果同一个sheet只要创建一次WriteSheetwriteSheetEasyExcel.writerSheet(用车订单).build();// 这里调用了五次每次获得一批数据写入。实际使用时根据数据库分页的总的页数来for(inti0;i5;i){// 分页去数据库查询数据或者构建数据 这里可以去数据库查询每一页的数据ListRowDatalist...;excelWriter.write(list,writeSheet);}}五、excel文件的上传和下载1.下载/** * 文件下载失败了会返回一个有部分数据的Excel * p * 1. 创建excel对应的实体对象 参照{link DownloadData} * p * 2. 设置返回的 参数 * p * 3. 直接写这里注意finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大 */GetMapping(download)publicvoiddownload(HttpServletResponseresponse)throwsIOException{// 这里注意 有同学反应使用swagger 会导致各种问题请直接用浏览器或者用postmanresponse.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系StringfileNameURLEncoder.encode(测试,UTF-8).replaceAll(\\,%20);response.setHeader(Content-disposition,attachment;filename*utf-8fileName.xlsx);ListRowDatalist...;EasyExcel.write(response.getOutputStream(),RowData.class).sheet(sheet).doWrite(list);}2. 上传/** * 文件上传 * p1. 创建excel对应的实体对象 参照{link UploadData} * p2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link UploadDataListener} * p3. 直接读即可 */PostMapping(upload)ResponseBodypublicStringupload(MultipartFilefile)throwsIOException{EasyExcel.read(file.getInputStream(),RowData.class,newUploadDataListener(uploadDAO)).sheet().doRead();returnsuccess;}六、分批读取并写入综合示例下面的代码会读取excel文件里的指定sheet页然后读取指定列的数据最后新建一个excel文件把读取到的列数据写入到新建的excel文件的指定sheet里。importcom.alibaba.excel.ExcelWriter;importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.read.listener.ReadListener;importcom.alibaba.excel.util.ListUtils;importcom.alibaba.excel.write.metadata.WriteSheet;importcom.alibaba.excel.write.metadata.style.WriteCellStyle;importcom.alibaba.excel.write.style.HorizontalCellStyleStrategy;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.VerticalAlignment;importjava.util.List;// 有个很重要的点 RowDataListener 不能被spring管理所以如果使用spring要每次读取excel都要new,然后里面用到spring可以构造方法传进去publicclassRowDataListenerimplementsReadListenerDiDiReconcileExcelParseModel{/** * 每读取1000条数据进行操作然后清理list 方便内存回收 */privatestaticfinalintBATCH_COUNT1000;/** * 缓存的数据 */privateListDiDiReconcileExcelParseModelcachedDataListListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/** * 用于写入excel */privateExcelWriterexcelWriternull;/** * 用于写入excel */privateWriteSheetwriteSheetnull;publicRowDataListener(ExcelWriterexcelWriter,WriteSheetwriteSheet){this.excelWriterexcelWriter;this.writeSheetwriteSheet;}/** * 获取表头和其他行的水平和垂直居中设置 */publicstaticHorizontalCellStyleStrategygetHorizontalCellStyleStrategy(){// 设置表头单元格样式WriteCellStyleheadWriteCellStylenewWriteCellStyle();// 设置水平居中headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 设置垂直居中headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置除表头外的其他行的内容单元格样式WriteCellStylecontentWriteCellStylenewWriteCellStyle();// 设置水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 设置垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 使用自定义样式策略returnnewHorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);}/** * 每一条数据解析都会调用该方法一次 * * param data one row value. Is is same as * param context */Overridepublicvoidinvoke(DiDiReconcileExcelParseModeldata,AnalysisContextcontext){//System.out.println(解析到一条数据: JSON.toJSONString(data));cachedDataList.add(data);// 达到BATCH_COUNT了需要去存储一次数据库防止数据几万条数据在内存容易OOMif(cachedDataList.size()BATCH_COUNT){saveData();// 存储完成清理 listcachedDataListListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/** * 当所有数据全部解析读取完后 会调用该方法一次 * * param context */OverridepublicvoiddoAfterAllAnalysed(AnalysisContextcontext){// 这里也要保存数据确保最后遗留的数据也存储到数据库saveData();System.out.println(所有数据写入完成);excelWriter.finish();if(excelWriter!null){excelWriter.close();}}/** * 保存数据 */privatevoidsaveData(){System.out.println(写入cachedDataList.size()条数据);excelWriter.write(cachedDataList,writeSheet);}}测试StringwriteFileC:\\Users\\LEPLOP\\Desktop\\写入.xlsx;ExcelWriterexcelWriterEasyExcel.write(writeFile,DiDiReconcileExcelParseModel.class).build();WriteSheetwriteSheetEasyExcel.writerSheet(用车订单)//设置垂直和水平居中.registerWriteHandler(RowDataListener.getHorizontalCellStyleStrategy()).build();//从 readFile 中的 用车订单 sheet中读取指定的列数据DiDiReconcileExcelParseModel 里指定的列然后通过excelWriter, writeSheet 写入到 writeFile中StringreadFileC:\\Users\\LEPLOP\\Desktop\\test\\账单.xlsx;EasyExcel.read(readFile,DiDiReconcileExcelParseModel.class,newRowDataListener(excelWriter,writeSheet)).sheet(用车订单).doRead();根据测试读取一个14万行并有216列的excel文件然后挑选其中6列写入新的excel文件大概耗时不到3分钟
