EasyExcel 百万级别数据高效率导入/导出 |
您所在的位置：网站首页 › 数据库模拟数据导入 › 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 { 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 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 |
【本文地址】