EasyExcel 低内存导出大数据量的Excel方案探索 50万行 50列 (附:实现代码)

文章探讨了如何使用EasyExcel处理50万行50列的大数据量Excel导出,通过对比单次全量导出、分批查询导出、多线程导出等方式,分析了CPU和内存消耗,提出分批写入和限制JVM堆大小的优化方案,减少了内存和CPU的峰值,提高了性能。同时,文章提到了文件打包成ZIP以方便用户下载,并讨论了响应给客户的不同方式,包括异步处理和文件服务器的使用。
摘要由CSDN通过智能技术生成

1.前言

最近接到一个需求,需要从服务器中导出大量数据到Excel中,数据量大概为50万行,50列,借助这个机会,就想对使用使用低内存导出大数据量的方案进行探索,总结出一个通用可行性方案,以方便日后随时可以使用,同时也分享一下探索的过程。

2.准备工作

技术栈选型
市面上常用的导出 Excel 的工具有 apache poijxlsAlibaba EasyExcel 等,在以往的使用经验以及查询的资料中显示,前面两者对于导出的优化不太好,在大数据量导出时容易OOM,Easy Excel 针对OOM问题做过优化,而且社区活跃,使用比较简单,此处选择EasyExcel。
附:《EasyExcel官方文档》

环境准备

  • 客户端:工作使用笔记本电脑,CPU为 8核i5 低压,排除其他应用消耗后的可用内存约为4GB。
  • 数据库:本地安装的 MySQL 数据库。
  • 服务:新建SpringBoot服务,除需要验证的Excel功能以外,没有其他功能的消耗。

数据准备
在本地 MySQL 数据库中插入 50w 条数据备用。

探索目标

  • 能否正常导出Excel文件
  • 导出过程中的 CPU、内存等消耗情况
  • 各操作步骤的耗时
  • 导出的文件大小,文件能否正常操作

3.导出测试

对不同的导出方式进行验证,通过 Java visual VM 来观察导出过程中的CPU与内存使用情况,这里会使用到一个插件Visual GC,用来查看JVM的各分代信息,不太清楚这个插件的同学可以查看这篇文章《垃圾回收算法和垃圾收集器》中的第5点,里面有工具所在位置,以及插件的安装方法。

下面主要导出到本地硬盘(后面会补充响应给用户客户端的验证),展示的是导出过程中的性能相关指标,实现代码会放在后面的第4点中。

3.1.单次查询、全量导出

一次性将数据全部查询出来,放到内存中,再将所有的数据插入的Excel中。
在这里插入图片描述

查询耗时:19710 ,约 20 s
导出耗时:87588 ,约 88 s
内存消耗:峰值约为 1.5 GB
CPU消耗:约有 20s 左右的高消耗,峰值约为 65%
文件大小:约 120 MB

通过我的工作电脑打开这个 Excel 文件,大约耗时1分钟,打开后基本上就卡死了,无法操作。

通过输出的性能指标来看,需要针对性的做以下几点优化:

  • 减少CPU的消耗
  • 减少内存的使用
  • 缩小Excel文件的大小

首先,缩小Excel文件的大小可以考虑将文件进行拆分,例如:拆分为每个文件5w数据,生成10个文件,再将10个文件打包成zip进行下载。

其次,减少内存的使用,可以考虑将一次性查询 50w 的数据,修改为分批次查询每次查询5w条数据,这样与上面的缩小文件大小不谋而合。生成了一个Excel之后,已经插入到Excel文件中的数据就不需要存在与内存中了,此时通过 GC 清理掉,就可以减少内存的消耗。

最后,减少CPU的消耗,目前阶段暂时还不清楚 CPU 的消耗主要是存在于何处,可以先做上面的两个优化,再观察一下效果。

3.2.多次查询,多个文件,单次写入

由于我在准备数据阶段插入的数据是比较均匀的,所以理论上50w数据占用1.5GB,那么 5w 条数据占用的内存 就是 150MB,为了尽可能的模拟实际使用场景,此处将JVM的堆大小限制为:-Xms256m -Xmx256m ,再次执行导出,结果如下:
在这里插入图片描述
在这里插入图片描述

查询+导出耗时:耗时:106525 ,约 107s
内存消耗:峰值约为170 MB
CPU消耗:持续约 2.5 min,峰值约为 35%


优化效果明显,导出时间几乎没有变化,内存峰值下降了88%,CPU峰值下降了46%

从CPU的角度来看,可以猜测生成Excel时,插入的数据量越小,则CPU的消耗就越小,带着这样的猜想,进一步进行验证。同时也可以注意一下上面第二张图中的老年代 Full GC 的次数,可以和下一个方案做一下对比。

3.3.多次查询,多个文件,多次写入

之所以有这么一个验证,主要是看到了 EasyExcel文档中有这么一个注释:
在这里插入图片描述
这里取一个极限值5000,将5w条数据再次拆分为10份,写入Excel的时候分10次写入,再看一下消耗情况。
在这里插入图片描述
在这里插入图片描述

耗时:173848 ms 约 170s
内存消耗:峰值约为190 MB
CPU消耗:持续约 2.5 min,峰值约为 17%


先看 CPU ,CPU 的峰值进一步降低了50%,则我们先前的猜测是正确的。

内存的峰值上虽然多了20MB,但是最小值也也降低到了50MB左右,这里把两张图对比可以可以直观的感受到区别:
在这里插入图片描述
同时再对比一下visual GC中的两个图,Full GC的次数由20次缩小为7次,证明大部分的内存在新生代就已经回收了。

也就是说,当前这种方案是可以承受更低的堆内存限制的,之所以峰值还是会上去,是因为垃圾回收器自动回收垃圾时会有一个阈值,在没有超过这个阈值的时候,垃圾回收器也不会随意的回收内存,毕竟回收内存是会产生停顿时间的。

3.4.多线程导出探索

如果使用多线程并发生成10个文件,理论上速度会更快,但是由于同样的,数据会全部加载到内存中,对于内存的消耗也会大增,抱着不死心的态度,还是验证一下。
将代码修改为多线程之后,执行导出:
在这里插入图片描述
这次CPU的消耗简直不忍直视,我在截完图后,电脑就直接卡死了只能强制重启。不死心的我,换了另一台台式机进行测试,配置为 i7 9700 8核16线程,这次顺利导出了,耗时约为10s,但是即使是这种配置,CPU的峰值也飙升到了70%左右。


硬件性能来换时间是可以实现的,但是需求的硬件配置太高,现在服务器又那么贵,实现的时候需要综合评估功能收益与硬件成本,在导出实时性要求不高的情况下,使用以时间换空间的方式更为合适。

3.5.文件打包成ZIP

响应给用户时,需要将多个文件打包成一个ZIP文件,不然用户就得下载10次,体验极差。

至于压缩方面,Excel本身对数据就已经做了压缩,再将其打包成zip的形式所带来的数据压缩率就很低了,所以几乎不考虑压缩的问题。

这里的打包主要是通过hutool包提供的压缩方式,文件服务器使用的是腾讯云的COS,先测试一下执行时间以及性能消耗。
在这里插入图片描述

打包耗时:5459
内存和CPU几乎没有消耗

3.6.响应给用户

完成ZIP的打包之后,就需要将文件响应给用户了,有两种形式:

  • 通过Response同步响应给用户
  • 上传到文件系统,给用户返回一个下载地址

第一种方式,只能做成同步响应的,也就是说,用户在点击下载之后需要在当前页面等待3到5分钟,不能做其他的操作。

第二种方式,可以做成异步响应的,用户在点击下载之后,返回一个“提交成功,正在处理中”的提示,用户就可以做其他事情去了,待上传到文件系统成功之后将下载地址通知给用户即可。
通知的方式多种多样,可以用邮件通知、企业微信群通知、站内信通知,甚至可以做一个下载记录列表将下载地址存入到数据库中。

同时,对于多个用户同时下载的情况,也可以通过队列进行排队,一次只处理1个或两个(需要通过硬件来评估)导出请求。

4.实现代码

引入依赖

 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>3.2.1</version>
 </dependency>

 <dependency>
     <groupId>cn.hutool</groupId>
     <artifactId>hutool-core</artifactId>
     <version>4.6.1</version>
 </dependency>
 
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>

我这里使用的ORM框架是mybatis-plus,可以换成任意自己喜欢的


单次查询,全量导出

public void export() {
    long t1 = System.currentTimeMillis();
    List<MemberInfo> memberInfos = memberInfoMapper.selectList(new QueryWrapper<>());
    long t2 = System.currentTimeMillis();
    
    String fileName = "d://excel/simpleWrite" + System.currentTimeMillis() + ".xlsx";
  
    EasyExcel.write(fileName, MemberInfo.class)
            .sheet("模板")
            .doWrite(() -> memberInfos);
            
    long t3 = System.currentTimeMillis();
    System.out.println("查询耗时:" + (t2 - t1));
    System.out.println("导出耗时:" + (t3 - t2));

}

多次查询,多个文件,单次写入

public void export2() {
    long t1 = System.currentTimeMillis();
    for (int i = 0; i < 10; i++) {
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        QueryWrapper<MemberInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.last("limit " + i * 50000 + ",50000");
        List<MemberInfo> memberInfos = memberInfoMapper.selectList(queryWrapper);
        
        String fileName = "d://excel/simpleWrite" + "模板" + i + System.currentTimeMillis() + ".xlsx";
        
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, MemberInfo.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(memberInfos, writeSheet);
        }
    }

    long t2 = System.currentTimeMillis();
    System.out.println("耗时:" + (t2 - t1));
}

多次查询,多个文件,多次写入

public void export4() {
    long t1 = System.currentTimeMillis();
    for (int i = 0; i < 10; i++) {

        String fileName = "d://excel/simpleWrite" + "模板" + i + System.currentTimeMillis() + ".xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, MemberInfo.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();

            for (int j = 0; j < 10; j++) {
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                QueryWrapper<MemberInfo> queryWrapper = new QueryWrapper<>();
                queryWrapper.last("limit " + (10 * i + j) * 5000 + ",5000");
                List<MemberInfo> memberInfos = memberInfoMapper.selectList(queryWrapper);
                
                excelWriter.write(memberInfos, writeSheet);
            }
        }
    }

    long t2 = System.currentTimeMillis();
    System.out.println("耗时:" + (t2 - t1));
}

打包

 public void pack() {
    long t1 = System.currentTimeMillis();
    ZipUtil.zip("d://excel", "d://zip/导出数据.zip");
    long t2 = System.currentTimeMillis();

    System.out.println("打包耗时:" + (t2 - t1));
}

多线程探索

public void export3() {
     long t1 = System.currentTimeMillis();

     CountDownLatch countDownLatch = new CountDownLatch(10);

     for (int i = 0; i < 10; i++) {
         int finalI = i;
         new Thread(() -> {
             String fileName = "d://excel/simpleWrite" + "模板" + finalI + System.currentTimeMillis() + ".xlsx";
             try (ExcelWriter excelWriter = EasyExcel.write(fileName, MemberInfo.class).build()) {

                 QueryWrapper<MemberInfo> queryWrapper = new QueryWrapper<>();
                 queryWrapper.last("limit " + finalI * 50000 + ",50000");
                 List<MemberInfo> memberInfos = memberInfoMapper.selectList(queryWrapper);
                 
                 WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
                 
                 excelWriter.write(memberInfos, writeSheet);
                 countDownLatch.countDown();
             }
         }).start();
     }
     try {
         countDownLatch.await();
     } catch (InterruptedException e) {
         e.printStackTrace();
     }
     long t2 = System.currentTimeMillis();
     System.out.println("耗时:" + (t2 - t1));
 }

上传到文件服务器涉及到公司信息就不放在这里了,我这边使用的是腾讯云的COS,可以替换为你们自己公司所用的文件服务器。

5.结语

从几个方面总结一下低内存导出大数据量的Excel的方法:

  • 降低存入到内存中的数据,使用分批次查询、分批次插入数据的方式。
  • 尽可能的减少并发,避免使用多线程操作Excel,同时,还可以通过队列做异步和限流,排队处理导出请求。
  • 考虑到Excel文件过大无法操作,可以将一个大文件拆分为多个小文件。

以上,是从Demo的角度验证了可行性,实际生产使用还需要考虑到文件的过期、文件的加密等,如果是发布到容器中还需要考虑文件路径无法找到等问题,但是与主题关系不大,这里就不做过多的讨论了,可以实际遇到问题再做分析和考虑。


如果觉得本文有帮助的话,可以帮忙点点赞哦!

  • 16
    点赞
  • 54
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
### 回答1: EasyExcel是一个基于Java的简单易用的Excel操作工具,它提供了批数据导出的功能。 在大量数据导出时,EasyExcel具有以下优点: 1. 速度快:EasyExcel采用了基于事件模型的解析方式,可以处理非常大的Excel文件而不会引起内存溢出。它可以将数据直接写到OutputStream中,避免了频繁的IO操作,因此导出速度非常快。 2. 内存消耗较EasyExcel使用了一种流式写入的方式,将数据逐行写入Excel文件,这样可以很好地控制内存的消耗。即使处理非常大的数据集,也不会出现内存溢出的问题。 3. 支持多种数据源:EasyExcel支持从各种数据源中读取数据,如数据库、集合、文件等,并可以将数据导出Excel中。这样可以满足不同场景下的数据导出需求。 4. 操作简单:EasyExcel提供了简单易用的API,可以方便地配置导出的格式、样式、宽等。同时,它还支持导出多个Sheet,可以根据具体需求进行灵活配置。 5. 良好的兼容性:EasyExcelExcel文件的兼容性非常好,支持导出.xlsx和.xls格式的文件,同时还支持设置密码、加密和设置单元格样式等功能。这样可以满足不同用户的需求。 无论是导出数据还是大量数据EasyExcel都能够快速高效地完成任务。它的简单易用性、内存消耗和良好的兼容性使其成为处理大量数据导出的理想工具。 ### 回答2: EasyExcel是一款用于Excel文件读写操作的Java工具库,它提供了简单易用的API以实现大量数据导出功能。以下是使用EasyExcel进行大量数据导出的步骤: 1. 引入EasyExcel库:首先需要在Java项目中引入EasyExcel的依赖库,可以通过Maven或者Gradle等构建工具进行引入。 2. 创建导出模板:在Excel文件中,先创建一个模板,定义好数据的表头和格式。可以指定每的标题、宽、格式等,以满足不同的需求。 3. 构建数据源:准备好需要导出数据源,可以通过数据库查询、文件读取等方式获取数据。 4. 创建导出任务:使用EasyExcel的API创建一个导出任务,并指定导出Excel文件路径、模板等参数。 5. 填充数据:通过遍历数据源,将数据逐行填充到Excel文件中。可以使用EasyExcel提供的write方法将数据写入到指定的Sheet中。 6. 设置样式和格式:根据需求,可以对导出数据进行样式和格式的设置,如设置单元格的字体、背景色、边框等。 7. 执行导出操作:执行导出任务,通过调用EasyExcel的API将数据写入到Excel文件中。 8. 导出结果处理:根据导出的结果进行相应的处理,如判断是否导出成功、记录日志等。 使用EasyExcel进行大量数据导出的优势在于其简洁易用的API,可以轻松处理大量数据导出需求。同时,EasyExcel导出速度较快,可以高效地处理大规模的数据导出任务。另外,EasyExcel对于复杂的数据格式和样式也有很好的支持,可以满足不同场景下的导出需求。 ### 回答3: EasyExcel是一款简单易用的Java Excel工具类库,广泛应用于数据导出和导入的场景。对于大量数据导出EasyExcel提供了高效的解决方案。 首先,EasyExcel采用了基于流式操作的方式来处理数据导入导出,不会一次性将所有数据读入内存,而是通过分批加载数据,减小了内存的占用。这样即使数据很大,也能够快速导出。 其次,EasyExcel通过多线程的方式来提升导出速度。通过配置线程数,可以将任务分发给多个线程同时处理,加快导出的速度。同时,EasyExcel还支持设置每个线程处理的数据,可以根据服务器性能进行灵活调整,进一步提高导出效率。 另外,EasyExcel还支持将导出任务拆分为多个子任务进行处理,可以将数据按照某种规则或条件进行拆分,每个子任务独立导出。这样可以充分利用服务器的多核资源,加速导出进程。 总之,EasyExcel通过分批加载数据、多线程处理和子任务拆分等方式,有效解决了大量数据导出的问题。无论是导出百万、千万、亿级的数据,都能够高效快速地完成导出任务。同时,EasyExcel还提供了丰富的API和灵活的配置,方便开发者根据具体需求进行定制。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

挥之以墨

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值