EasyExcel 百万级别数据高效率导入/导出 |
您所在的位置：网站首页 › java多线程处理大批量数据的方法 › EasyExcel 百万级别数据高效率导入/导出 |
EasyExcel 百万级别数据高效率导入/导出
|
🔭 嗨，您好 👋 我是 vnjohn，在互联网企业担任 Java 开发，CSDN 优质创作者 📖 推荐专栏：Spring、MySQL、Nacos、Java，后续其他专栏会持续优化更新迭代 🌲文章所在专栏：业务设计 🤔 我当前正在学习微服务领域、云原生领域、消息中间件等架构、原理知识 💬 向我询问任何您想要的东西，ID：vnjohn 🔥觉得博主文章写的还 OK，能够帮助到您的，感谢三连支持博客🙏 😄 代词: vnjohn ⚡ 有趣的事实：音乐、跑步、电影、游戏 目录 前言POI 框架特性对比大数据量解决思路导入导出 案例演示准备工作示例代码导入MyBatisJDBC 导出 操作时遇到的错误参考文献最后 前言在日常的开发中，用的比较多的方式就是 Apache 下的 POI 框架了，但在目前数据量大的时代下，这种方式显得已经不适合了 编写该篇文章主要是公司中实际上用到了这种场景，将它作为文章分享出来，如果这种方式的导入/导出能帮助到大家也挺开心的，或者大家也可以发表自己在这方面的看法，提出来，做为更好的一种方式去呈现 节点分以下几步走，先是介绍原始 Apache POI 以及由阿里巴巴开源的一框 POI 框架做对比，再是提出解决这方面问题上的思路，然后进行案例演示，最后记录一下在使用中遇到的错误点. POI 框架特性对比poi 依赖的基础接口：WorkBook，有几种实现子类需要进行区分，如下： HSSFWorkbook：Excel 2003(包含) 之前版本使用的子类对象，处理的文件格式都是 .xls 的，其是 poi 中最常用的方式，处理的行数在 6W+，一般处理的数据不超过这个大小就不会出现内存溢出的，这个量内存也是足够支撑的.XSSFWorkbook：Excel 2003-2007 使用的子类对象，目前还是有大量公司使用的这个，文件格式为 .xlsx，出现这个格式就是为了突破 HSSFWorkBook 6W 数据的局限，是为了针对Excel2007版本的 1048576行，16384 列，最多可以导出 104w 条数据，虽然在数据上增加了，但是内存的瓶颈也就来了，OOM 离之不远了.SXSSFWorkbook：该实现类是 POI3.8 之后的版本才有的,它可以操作 Excel2007 以后的所有版本 Excel,扩展名是 .xlsx，这种方式提供了一种低内存占用机制，存储百万数据丝毫不是问题，一般不会出现内存溢出（它使用硬盘来换内存，也就是说当内存数据到达一定时会采用硬盘来进行存储，内存里存储的只会是最新的数据），缺点：因为它使用到了硬盘，当数据到达硬盘以后，也就无法完成数据的克隆或者公式计算，sheet.clone() 已经无法被支持了在使用过程中，如果不涉及到 Excel 公式和样式并且数据量较大的情况下，推荐使用 SXSSFWorkbook；数据量不超过 6W~7W 也涉及到了公式的计算，推荐使用 XSSFWorkbook 大数据量解决思路使用传统的 poi 导入导出方式，当数据量过大时，明显会出现 OOM 异常，因此推荐大家使用阿里巴巴开源的 easyExcel 框架作为导入导出的媒介 GitHub - alibaba/easyexcel: 快速、简单避免OOM的处理Excel工具 导入 导入数据也分批次的导入，导入也需要集合来存储 Excel 行列的值，当这个批次被导入完成后，集合进行清空，防止重复处理且减少内存的占用在下面的案例中，会通过 MyBatis、JDBC 做批量的数据库插入操作，很明显你就会 JDBC 的效率比 MyBatis 高，因为 MyBatis 底层也是通过 JDBC 对数据库进行操作的，MyBatis 在处理结果集的时候都是一条一条数据进行循环遍历处理的，所以在这个基础上效率就下降了. 导出 不要一次性将数据量全量查询出来，如果几百万数据一下查出来，内存是吃不下的，所以应该对数据进行分批查出，比如：20W一次、10W一次将数据查询出来以后，进行 Excel 写入时，也应该做批次的写入操作，一行一行的写入会造成大量 IO 请求，性能随之就会下降几百万数据同时放在一个 sheet 里面，不然打开一个 sheet 都会停顿很长时间，操作时也会明显变慢，例如：一个 sheet 存储一百万数据导出到 excel 时，肯定需要依赖集合进行数据的临时存储，在每个批次的数据处理完以后，记得对集合进行清空，以便让 GC 知道这个可以提前进行回收了，内存也不会一直被占用. 案例演示 准备工作前期准备好 SQL 脚本和存储函数用于批量生成假数据 # 表结构脚本 CREATE TABLE `act_result_log` ( `id` int(10) NOT NULL AUTO_INCREMENT, `onlineseqid` varchar(32) DEFAULT NULL, `businessid` varchar(32) DEFAULT NULL, `becifno` varchar(32) DEFAULT NULL, `ivisresult` varchar(32) DEFAULT NULL, `createdby` varchar(32) DEFAULT NULL, `createddate` date DEFAULT NULL, `updateby` varchar(32) DEFAULT NULL, `updateddate` date DEFAULT NULL, `risklevel` varchar(32) DEFAULT NULL, `is_deleted` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `account_id_IDX` (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=utf8mb4; # 存储函数，生成五百万数据，等待时间会比较长 CREATE PROCEDURE demo.batch_generate ( ) BEGIN DECLARE i INT DEFAULT 1; WHILE i private static final long serialVersionUID = 1L; @TableId(value = "id", type = IdType.AUTO) private Long id; @TableField("onlineseqid") private String onlineseqid; @TableField("businessid") private String businessid; @TableField("becifno") private String becifno; @TableField("ivisresult") private String ivisresult; @TableField("createdby") private String createdby; @TableField("createddate") private LocalDate createddate; @TableField("updateby") private String updateby; @TableField("updateddate") private LocalDate updateddate; @TableField("risklevel") private String risklevel; }2、实体 Vo @Data public class ActResultLogVO implements Serializable { private static final long serialVersionUID = 1L; @ExcelProperty(value = "onlineseqid",index = 0) private String onlineseqid; @ExcelProperty(value = "businessid",index = 1) private String businessid; @ExcelProperty(value = "becifno",index = 2) private String becifno; @ExcelProperty(value = "ivisresult",index = 3) private String ivisresult; @ExcelProperty(value = "createdby",index = 4) private String createdby; @ExcelProperty(value = "createddate",index = 5) private LocalDate createddate; @ExcelProperty(value = "updateby",index = 6) private String updateby; @ExcelProperty(value = "updateddate",index = 7) private LocalDate updateddate; @ExcelProperty(value = "risklevel",index = 8) private String risklevel; }3、控制器 @RequestMapping("/excel") @RestController public class ExcelController { @Resource private EasyExcelUtil easyExcelUtil; @GetMapping("/export") public void exportExcel(HttpServletResponse response) { easyExcelUtil.dataExport300w(response); } }4、常量类 public class ExcelConstants { public static final Integer PER_SHEET_ROW_COUNT = 100*10000; public static final Integer PER_WRITE_ROW_COUNT = 20*10000; public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC = 10*10000; public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_MYBATIS = 5*10000; } 导入导入分两种方式进行，第一种是通过 MyBatis 编写 SQL 语句运行，不使用 MP 是因为它底层的批量新增是一条一条写入的，并不是实际意义上的批次；第二种是通过原生 JDBC 进行批量操作，使用手动提交事务的方式. 1、Service 接口 public interface IActResultLogService extends IService { /** * 通过分页参数查询一百w数据 * @return */ List findByPage100w(Integer pageNum,Integer pageSize); /** * 从 Excel 导入数据，批次为 10w，通过 JDBC * @param dataList * @return */ Map import2DBFromExcel10wByJDBC(List dataList); /** * 从 Excel 导入数据，批次为 10W，通过 MyBatis * @param actResultLogList */ void import2DBFromExcel10wByMybatis(List actResultLogList); }2、Service 接口实现类 @Slf4j @Service public class ActResultLogServiceImpl extends ServiceImpl implements IActResultLogService { @Override public List findByPage100w(Integer pageNum, Integer pageSize) { PageInfo pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() -> { QueryWrapper wrapper = new QueryWrapper(); // TODO 此处可以进行添加条件过滤 baseMapper.selectList(wrapper); }); return pageInfo.getList(); } // Service中具体业务逻辑 /** * 测试用Excel导入超过10w条数据,经过测试发现,使用 Mybatis 批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快 */ @Override public Map import2DBFromExcel10wByJDBC(List dataList) { Map result = new HashMap(); // 结果集中数据为0时,结束方法.进行下一次调用 if (dataList.size() == 0) { result.put("empty", "0000"); return result; } // JDBC分批插入+事务操作完成对10w数据的插入 Connection conn = null; PreparedStatement ps = null; try { long startTime = System.currentTimeMillis(); log.info("{} 条,开始导入到数据库时间:{}", dataList.size(), startTime + "ms"); conn = JDBCDruidUtils.getConnection(); // 控制事务:默认不提交 conn.setAutoCommit(false); String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values"; sql += "(?,?,?,?,?,?,?,?,?)"; ps = conn.prepareStatement(sql); // 循环结果集:这里循环不支持"烂布袋"表达式 for (int i = 0; i result.put("exception", "0000"); e.printStackTrace(); } finally { // 关连接 JDBCDruidUtils.close(conn, ps); } return result; } // 采用 mapper 编写 SQL 语句进行测试，效率明显比原生 JDBC 要低 @Override public void import2DBFromExcel10wByMybatis(List actResultLogList) { baseMapper.importToDb(actResultLogList); } } MyBatis1、Mapper.xml 映射文件 insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values ( #{item.onlineseqid},#{item.businessid},#{item.becifno},#{item.ivisresult},#{item.createdby},#{item.createddate}, #{item.updateby},#{item.updateddate},#{item.risklevel} )Excel.read() 方法需要一个监听器，当（事件）数据到达时，通过你自定义的这个监听器进行数据的流转处理. 2、EasyExcel 监听器 public class EasyExcelGeneralDataMybatisListener extends AnalysisEventListener { private IActResultLogService actResultLogService; /** * 用于存储读取的数据 */ private List dataList = new ArrayList(); public EasyExcelGeneralDataMybatisListener() { } public EasyExcelGeneralDataMybatisListener(IActResultLogService actResultLogService) { this.actResultLogService = actResultLogService; } @Override public void invoke(ActResultLogVO data, AnalysisContext context) { // 数据add进入集合 dataList.add(data); // size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入 if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC) { // 存入数据库:数据小于 1w 条使用 Mybatis 批量插入即可 saveData(); // 清理集合便于GC回收 dataList.clear(); } } /** * 保存数据到 DB */ private void saveData() { if (dataList.size() > 0) { actResultLogService.import2DBFromExcel10wByMybatis(StarBeanUtils.copyList(dataList, ActResultLogDO.class)); dataList.clear(); } } /** * Excel 中所有数据解析完毕会调用此方法 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); dataList.clear(); } }3、单元测试类 @Slf4j @RunWith(SpringRunner.class) @SpringBootTest(classes = StarApplication.class) public class EasyExcelTest { @Resource private IActResultLogService actResultLogService; // EasyExcel的读取Excel数据的API @Test public void import2DBFromExcel10wTest() { String fileName = "/Users/vnjohn/Downloads/export-excel.xlsx"; // 记录开始读取Excel时间,也是导入程序开始时间 long startReadTime = System.currentTimeMillis(); log.info("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + " ms------"); // 读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法 EasyExcel.read(fileName, ActResultLogVO.class, new EasyExcelGeneralDataMybatisListener(actResultLogService)) .registerConverter(new EasyExcelLocalDateConvert()).doReadAll(); // EasyExcel.read(fileName, new EasyExcelGeneralDataJDBCListener(actResultLogService)).doReadAll(); long endReadTime = System.currentTimeMillis(); log.info("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + " ms------"); log.info("------导入总花费时长：{}", ((endReadTime - startReadTime) / 1000) + "s------"); } }通过 Mybatis 导入，需要映射到具体的实体，JDK1.8 以后时间类型大部分都是使用 LocalDate 这种类型了，在 EasyExcel 中没有提供这种 Excel 时间列值到时间类型的转换，所以我们需要自定义一个转换器用来适配 4、时间转换类 public class EasyExcelLocalDateConvert implements Converter { @Override public Class supportJavaTypeKey() { return LocalDate.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public LocalDate convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd")); } @Override public WriteCellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new WriteCellData(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))); } }最后，执行 @Test 方法，结果如下： ------开始读取Excel的Sheet时间(包括导入数据过程):1657257416753ms ------结束读取Excel的Sheet时间(包括导入数据过程):1657257794753 ms------ ------导入总花费时长：378s------ JDBC1、数据库配置 properties 文件 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://127.0.0.1/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false username=root password=123456使用 JDBC 读取 Excel 每一行数据返回的数据类型是 Map，所以它对应的监听器的处理过程如下： public class EasyExcelGeneralDataJDBCListener extends AnalysisEventListener { private IActResultLogService actResultLogService; /** * 用于存储读取的数据 */ private List dataList = new ArrayList(); public EasyExcelGeneralDataJDBCListener() { } public EasyExcelGeneralDataJDBCListener(IActResultLogService actResultLogService) { this.actResultLogService = actResultLogService; } @Override public void invoke(Map data, AnalysisContext context) { // 数据add进入集合 dataList.add(data); // size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入 if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC) { // 存入数据库:数据小于 1w 条使用 Mybatis 批量插入即可 saveData(); // 清理集合便于GC回收 dataList.clear(); } } /** * 保存数据到 DB */ private void saveData() { if (dataList.size() > 0) { actResultLogService.import2DBFromExcel10wByJDBC(dataList); dataList.clear(); } } /** * Excel 中所有数据解析完毕会调用此方法 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); dataList.clear(); } }最后，执行 @Test 方法后，结果如下： ------开始读取Excel的Sheet时间(包括导入数据过程):1657258268921 ms------ ------结束读取Excel的Sheet时间(包括导入数据过程):1657258435024 ms------ ------导入总花费时长：166s------比较 MyBatis、原生 JDBC 执行后的结果可以看到，JDBC 的效率比 MyBatis 提升了 2.2 以上 导出导出的数据量按照 200W 来进行测试，100W 存到一个 sheet 里面，从数据库先查询总数，然后 /100W 得出多少个 sheet，外层循环是 sheet 数量，内层是要读取数据库的次数，比如：一次性查询出 20W 数据，那么一个 sheet 就需要查 5 次，因此查询次数在 sheet数*5 这个范围；注意：在每次从数据库读取完以后，记得清空集合的元素，方便提前被 GC 回收，防止内存的空间占用 1、导出工具类 @Slf4j @Component public class EasyExcelUtil { @Resource private IActResultLogService actResultLogService; /** * 导出逻辑代码 * * @param response */ public void dataExport300w(HttpServletResponse response) { OutputStream outputStream = null; try { long startTime = System.currentTimeMillis(); log.info("导出开始时间:{}", startTime); outputStream = response.getOutputStream(); WriteWorkbook writeWorkbook = new WriteWorkbook(); writeWorkbook.setOutputStream(outputStream); writeWorkbook.setExcelType(ExcelTypeEnum.XLSX); ExcelWriter writer = new ExcelWriter(writeWorkbook); String fileName = new String(("export-excel").getBytes(), StandardCharsets.UTF_8); // TODO WriteTable 标题这块可以作为公共的封装起来：通过反射获取变量上注解等 WriteTable table = new WriteTable(); List titles = new ArrayList(); titles.add(Collections.singletonList("onlineseqid")); titles.add(Collections.singletonList("businessid")); titles.add(Collections.singletonList("becifno")); titles.add(Collections.singletonList("ivisresult")); titles.add(Collections.singletonList("createdby")); titles.add(Collections.singletonList("createddate")); titles.add(Collections.singletonList("updateby")); titles.add(Collections.singletonList("updateddate")); titles.add(Collections.singletonList("risklevel")); table.setHead(titles); // 记录总数：实际中需要根据查询条件（过滤数据）进行统计即可， // TODO 此处写入限定的条数进行自测 // Integer totalCount = actResultLogService.count(); Integer totalCount = 200 * 10000; // 每一个Sheet存放100w条数据 Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT; // 每次写入的数据量20w Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT; // 计算需要的Sheet数量 int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1); // 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据) int oneSheetWriteCount = totalCount > sheetDataRows ? sheetDataRows / writeDataRows : totalCount % writeDataRows > 0 ? totalCount / writeDataRows + 1 : totalCount / writeDataRows; // 计算最后一个sheet需要写入的次数 int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1)); // 开始分批查询分次写入 // 注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数 List dataList = new ArrayList(); for (int i = 0; i // 集合复用,便于GC清理 dataList.clear(); // 分页查询一次20w List resultList = actResultLogService.findByPage100w(j + 1 + oneSheetWriteCount * i, writeDataRows); if (!CollectionUtils.isEmpty(resultList)) { resultList.forEach(item -> { dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(), item.getIvisresult(), item.getCreatedby(), LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), item.getUpdateby(), LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), item.getRisklevel())); }); } // 写数据 writer.write(dataList, sheet, table); } } // 下载EXCEL 以下代码可以作为公共的进行封装. setExcelRespProp(response, fileName); writer.finish(); outputStream.flush(); // 导出时间结束 long endTime = System.currentTimeMillis(); log.info("导出结束时间:{}", endTime + "ms"); log.info("导出所用时间:{}", (endTime - startTime) / 1000 + "秒"); } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream != null) { try { outputStream.close(); } catch (Exception e) { e.printStackTrace(); } } } } /** * 公共响应参数 */ public static void setResponseParam(HttpServletResponse response, String fileName) throws UnsupportedEncodingException { // 下载EXCEL 以下代码可以作为公共的进行封装. response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), StandardCharsets.ISO_8859_1) + ".xlsx"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); } /** * 通过反射方式将头部作为公共部分进行设置 */ public void setTitles(Class clazz) { List titles = new ArrayList(); for (Field declaredField : clazz.getDeclaredFields()) { ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class); if (null != annotation) { titles.add(Arrays.asList(annotation.value())); } } } /** * 设置excel下载响应头属性 */ public static void setExcelRespProp(HttpServletResponse response, String rawFileName){ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = null; try { fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20"); } catch (UnsupportedEncodingException e) { log.error("设置excel下载响应头属性，失败 {}",e.getMessage()); } response.setHeader("Content-disposition", "attachment;filename=utf-8''" + fileName + ".xlsx"); } }前面已经把控制器类贴出来了，请求地址：前缀/excel/export，我这边采用的测试工具为 safari 浏览器，如果是 windows 可以使用自带的微软浏览器，Google、 Postman 我这边在测试的时候可能是因为数据量太大的原因导致内存爆满，程序就异常退出了. 导出的时间结果如下： 导出结束时间:1657259099773ms 导出所用时间:71秒 操作时遇到的错误1、MySQL 允许最大的包文件参数调整 在进行 MyBatis 测试时，一下子堆积 10W 数据批量插入，MySQL 抛出异常提示传输的包文件过大，超出了参数的配置，导致无法正常插入，需要调整 show VARIABLES like ‘%max_allowed_packet%’，Windows 是在 server 根目录下的 my.ini 文件中，默认为 8M，将其调整 64M 即可；Linux/Mac 是在 server 根目录下的 my.cnf 文件里，Mac 无须调整，参数大小足够支撑了. 2、导入 Excel 列数据无法与实体属性映射问题 刚开始 ActResultLogVO 类上只是加了 @Getter/@Setter 注解，不是使用的 @Data 注解，导致无法映射，最终修改为 @Data 注解该问题不再出现，具体原因后续文章进行详细分析. 足以见识到 EasyExcel 支撑的强大数据量，对此底层实现后续可以详细了解和阅读，学习其强大的内功心法加粗样式 参考文献https://www.freesion.com/article/8852561865/ https://blog.csdn.net/weixin_44848900/article/details/117701981 EasyExcel 案例源码 最后如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！干货连连不断～ 推荐专栏：Spring、MySQL，订阅一波不再迷路 大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！ |
【本文地址】
今日新闻 |
推荐新闻 |