EasyExcel导入Excel和导出Excel教程 女爷i 2022-10-26 11:29 741阅读 0赞 EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 它底层是基于POI做的封装和优化,避免了POI耗费内存,内存溢出的问题。笔者之前做导出、导入Excel也是使用POI,后来在生产上总是出现内存溢出的问题,在寻求解决方案的路途中,就找到了EasyExcel。比POI要快很多,做过简单的测试,使用EasyExcel2.1.6和POI3.17做了一个导出的对比测试,写入50000条数据,easyexcel耗时2.5s,poi耗时12s;写入10万条数据,easyexcel耗时3.4s,poi耗时22s。所以若是优化导出的话,替换掉POI吧。接下来我们看简单的导出和导入的demo。 ## 1.集成ExasyExcel ## <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> </dependency> 我们点到easyexcel2.1.6的pom文件后,看到它依赖的poi版本是3.17,工程中若是已经集成了poi,需要把poi版本改到和easyexcel依赖的版本一致才行,3.17是最低支持的版本。 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTU2MTg1_size_16_color_FFFFFF_t_70][] ## 2.导出示例 ## ### 2.1实体与excel列的映射 ### @ExcelIgnore把不需要写到Excel的字段标注上; @ExcelProperty标注是excel的列,value属性设置列的名称,index设置列的序号,从0开始,converter设置的转换器。 由于id是long类型,excel对数字只支持15位,超过的数字都会变成0,所以此处用到了官方提供的LongStringConverter转换器(将单元格的类型从long类型转换为字符串类型),写入Excel的时候,会根据我们的转换器来设置单元格的类型。官方提供了很多的转换器,在com.alibaba.excel.converters下面,我们也可以自定义转换器。 @Data public class PartnerBusinessOrderResponse { /** * 主键id */ @ExcelProperty(value = "订单号",index = 0,converter= LongStringConverter.class) private Long id; /** * APPID */ @ExcelIgnore private Long appId; /** * 订单号 */ @ExcelProperty(value = "订单号",index=1) private String orderNo; /** * 业务类型名称 */ @ExcelProperty(value = "业务类型",index=2) private String businessTypeName; /** * 业务编码 */ @ExcelProperty(value = "业务编码",index=3) private String businessCode; /** * 业务名称 */ @ExcelProperty(value = "业务名称",index=4) private String businessName; /** * 办理号码 */ @ExcelProperty(value = "办理号码",index=5) private String handleMobile; /** * 办理时间 */ @ExcelIgnore private Long handleTime; /** *办理时间---格式化 * @return */ @ExcelProperty(value = "办理时间",index=6) private String handleTimeString; /** * 办理渠道:1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他 */ @ExcelIgnore private String handleChannel; /** * 办理渠道:1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他 */ @ExcelProperty(value = "办理渠道",index=7) private String handleChannelString; /** * 平台名称 */ @ExcelProperty(value = "所属平台",index=8) private String platformName; /** * 归属工号 */ @ExcelProperty(value = "归属工号",index=9) private String belongEmployeeNum; /** * 四级机构名称 */ @ExcelProperty(value = "地市",index=10) private String fourOrgName; /** * 五级机构名称 */ @ExcelProperty(value = "区县",index=11) private String fiveOrgName; /** *直属高级合伙人姓名 */ @ExcelProperty(value = "归属合伙人姓名",index=12) private String partnerLeaderName; /** *直属高级合伙人手机号 */ @ExcelProperty(value = "归属合伙人手机号",index=13) private String partnerLeaderMobile; /** * 合伙人等级名称 */ @ExcelProperty(value = "合伙人等级",index=14) private String partnerLevelName; /** *经办人 */ @ExcelProperty(value = "经办人姓名",index=15) private String partnerName; /** *经办人手机号 */ @ExcelProperty(value = "经办人手机号",index=16) private String partnerMobile; /** * 办理状态(1:成功;2:失败) */ @ExcelIgnore private Integer handleStatus; /** * 办理状态(1:成功;2:失败)---中文 */ @ExcelProperty(value = "是否办理成功",index=17) private String handleStatusString; } ### 2.2导出excel的代码 ### @RequestMapping(value = "/export") public void exportExcel(HttpServletRequest httpServletRequest, HttpServletResponse response) { try { //获取数据 List<PartnerBusinessOrderResponse> data = pageResponse.getRecords(); if (CollectionUtils.isEmpty(data)) { data.add(new PartnerBusinessOrderResponse()); } //attachment指定独立文件下载 不指定则回浏览器中直接打开 String fileName = "业务订单导出_" + DateUtil.formatDate(new Date(), DateUtil.YEARMONTHDAYHHMMSS) + ".xlsx"; response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); //导出excel EasyExcel.write(response.getOutputStream(), PartnerBusinessOrderResponse.class).sheet("合伙人业务订单").doWrite(data); log.info("业务订单导出end"); } catch (Exception e) { log.error("业务订单导出异常打印:", e); } finally { try { response.flushBuffer(); } catch (IOException e) { log.error("业务订单导出输出流关闭失败: {}", e); } } } 就是3行代码,就完成的导出Excel。 EasyExcel类的源码 package com.alibaba.excel; public class EasyExcel extends EasyExcelFactory { public EasyExcel() { } } EasyExcelFactory的源码 package com.alibaba.excel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.event.WriteHandler; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.read.builder.ExcelReaderBuilder; import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder; import com.alibaba.excel.read.listener.ReadListener; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder; import com.alibaba.excel.write.builder.ExcelWriterTableBuilder; import java.io.File; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; public class EasyExcelFactory { public EasyExcelFactory() { } /** @deprecated */ @Deprecated public static List<Object> read(InputStream in, Sheet sheet) { final List<Object> rows = new ArrayList(); (new ExcelReader(in, (Object)null, new AnalysisEventListener<Object>() { public void invoke(Object object, AnalysisContext context) { rows.add(object); } public void doAfterAllAnalysed(AnalysisContext context) { } }, false)).read(sheet); return rows; } /** @deprecated */ @Deprecated public static void readBySax(InputStream in, Sheet sheet, AnalysisEventListener listener) { (new ExcelReader(in, (Object)null, listener)).read(sheet); } /** @deprecated */ @Deprecated public static ExcelReader getReader(InputStream in, AnalysisEventListener listener) { return new ExcelReader(in, (Object)null, listener); } /** @deprecated */ @Deprecated public static ExcelWriter getWriter(OutputStream outputStream) { return write().file(outputStream).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build(); } /** @deprecated */ @Deprecated public static ExcelWriter getWriter(OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) { return write().file(outputStream).excelType(typeEnum).needHead(needHead).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build(); } /** @deprecated */ @Deprecated public static ExcelWriter getWriterWithTemp(InputStream temp, OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) { return write().withTemplate(temp).file(outputStream).excelType(typeEnum).needHead(needHead).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build(); } /** @deprecated */ @Deprecated public static ExcelWriter getWriterWithTempAndHandler(InputStream temp, OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead, WriteHandler handler) { return write().withTemplate(temp).file(outputStream).excelType(typeEnum).needHead(needHead).registerWriteHandler(handler).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build(); } public static ExcelWriterBuilder write() { return new ExcelWriterBuilder(); } public static ExcelWriterBuilder write(File file) { return write((File)file, (Class)null); } public static ExcelWriterBuilder write(File file, Class head) { ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder(); excelWriterBuilder.file(file); if (head != null) { excelWriterBuilder.head(head); } return excelWriterBuilder; } public static ExcelWriterBuilder write(String pathName) { return write((String)pathName, (Class)null); } public static ExcelWriterBuilder write(String pathName, Class head) { ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder(); excelWriterBuilder.file(pathName); if (head != null) { excelWriterBuilder.head(head); } return excelWriterBuilder; } public static ExcelWriterBuilder write(OutputStream outputStream) { return write((OutputStream)outputStream, (Class)null); } public static ExcelWriterBuilder write(OutputStream outputStream, Class head) { ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder(); excelWriterBuilder.file(outputStream); if (head != null) { excelWriterBuilder.head(head); } return excelWriterBuilder; } public static ExcelWriterSheetBuilder writerSheet() { return writerSheet((Integer)null, (String)null); } public static ExcelWriterSheetBuilder writerSheet(Integer sheetNo) { return writerSheet(sheetNo, (String)null); } public static ExcelWriterSheetBuilder writerSheet(String sheetName) { return writerSheet((Integer)null, sheetName); } public static ExcelWriterSheetBuilder writerSheet(Integer sheetNo, String sheetName) { ExcelWriterSheetBuilder excelWriterSheetBuilder = new ExcelWriterSheetBuilder(); if (sheetNo != null) { excelWriterSheetBuilder.sheetNo(sheetNo); } if (sheetName != null) { excelWriterSheetBuilder.sheetName(sheetName); } return excelWriterSheetBuilder; } public static ExcelWriterTableBuilder writerTable() { return writerTable((Integer)null); } public static ExcelWriterTableBuilder writerTable(Integer tableNo) { ExcelWriterTableBuilder excelWriterTableBuilder = new ExcelWriterTableBuilder(); if (tableNo != null) { excelWriterTableBuilder.tableNo(tableNo); } return excelWriterTableBuilder; } public static ExcelReaderBuilder read() { return new ExcelReaderBuilder(); } public static ExcelReaderBuilder read(File file) { return read((File)file, (Class)null, (ReadListener)null); } public static ExcelReaderBuilder read(File file, ReadListener readListener) { return read((File)file, (Class)null, readListener); } public static ExcelReaderBuilder read(File file, Class head, ReadListener readListener) { ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder(); excelReaderBuilder.file(file); if (head != null) { excelReaderBuilder.head(head); } if (readListener != null) { excelReaderBuilder.registerReadListener(readListener); } return excelReaderBuilder; } public static ExcelReaderBuilder read(String pathName) { return read((String)pathName, (Class)null, (ReadListener)null); } public static ExcelReaderBuilder read(String pathName, ReadListener readListener) { return read((String)pathName, (Class)null, readListener); } public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) { ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder(); excelReaderBuilder.file(pathName); if (head != null) { excelReaderBuilder.head(head); } if (readListener != null) { excelReaderBuilder.registerReadListener(readListener); } return excelReaderBuilder; } public static ExcelReaderBuilder read(InputStream inputStream) { return read((InputStream)inputStream, (Class)null, (ReadListener)null); } public static ExcelReaderBuilder read(InputStream inputStream, ReadListener readListener) { return read((InputStream)inputStream, (Class)null, readListener); } public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) { ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder(); excelReaderBuilder.file(inputStream); if (head != null) { excelReaderBuilder.head(head); } if (readListener != null) { excelReaderBuilder.registerReadListener(readListener); } return excelReaderBuilder; } public static ExcelReaderSheetBuilder readSheet() { return readSheet((Integer)null, (String)null); } public static ExcelReaderSheetBuilder readSheet(Integer sheetNo) { return readSheet(sheetNo, (String)null); } public static ExcelReaderSheetBuilder readSheet(String sheetName) { return readSheet((Integer)null, sheetName); } public static ExcelReaderSheetBuilder readSheet(Integer sheetNo, String sheetName) { ExcelReaderSheetBuilder excelReaderSheetBuilder = new ExcelReaderSheetBuilder(); if (sheetNo != null) { excelReaderSheetBuilder.sheetNo(sheetNo); } if (sheetName != null) { excelReaderSheetBuilder.sheetName(sheetName); } return excelReaderSheetBuilder; } } 3.导入 ### 3.1定义导入的对象 ### @Data public class TestData { private String partnerMobile; private String role; } ### 3.2写导入的监听类,一行一行读取 ### @Slf4j public class TestListener extends AnalysisEventListener<TestData> { /** * 每隔100条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收 */ private static final int BATCH_COUNT = 100; List<TestData> list = new ArrayList<TestData>(); @Override public void invoke(TestData data, AnalysisContext context) { log.info("解析到一条数据:{}", JSONObject.toJSON(data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); log.info("所有数据解析完成!"); } /** * 加上存储数据库 */ private void saveData() { log.info("{}条数据,开始存储数据库!", list.size()); log.info("存储数据库成功!"); } } ### 3.3读取代码 ### @RequestMapping(value = "/testImport", method = RequestMethod.POST) @ResponseBody public BaseResponse testImport(@RequestParam("file") MultipartFile multipartFile){ BaseResponse baseResponse = BackResponseUtil.getBaseResponse(ReturnCodeEnum.CODE_1000.getCode()); try{ EasyExcel.read(multipartFile.getInputStream(), TestData.class, new TestListener()).sheet().doRead(); }catch (Exception e){ baseResponse = BackResponseUtil.getBaseResponse(ReturnCodeEnum.CODE_1005.getCode()); } return baseResponse; } 就这样读取就完成了工文件流中读取解析Excel数据的功能。 [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTU2MTg1_size_16_color_FFFFFF_t_70]: /images/20221024/aa547bb249c747328cce2b8c343834f8.png
还没有评论,来说两句吧...