我的编程空间,编程开发者的网络收藏夹
学习永远不晚

详解Java如何实现百万数据excel导出功能

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

详解Java如何实现百万数据excel导出功能

前言

最近我做过一个MySQL百万级别数据的excel导出功能,已经正常上线使用了。

这个功能挺有意思的,里面需要注意的细节还真不少,现在拿出来跟大家分享一下,希望对你会有所帮助。

原始需求:用户在UI界面上点击全部导出按钮,就能导出所有商品数据。

咋一看,这个需求挺简单的。

但如果我告诉你,导出的记录条数,可能有一百多万,甚至两百万呢?

这时你可能会倒吸一口气。

因为你可能会面临如下问题:

  • 如果同步导数据,接口很容易超时。
  • 如果把所有数据一次性装载到内存,很容易引起OOM。
  • 数据量太大sql语句必定很慢。
  • 相同商品编号的数据要放到一起。
  • 如果走异步,如何通知用户导出结果?
  • 如果excel文件太大,目标用户打不开怎么办?

我们要如何才能解决这些问题,实现一个百万级别的excel数据快速导出功能呢?

1.异步处理

做一个MySQL百万数据级别的excel导出功能,如果走接口同步导出,该接口肯定会非常容易超时。

因此,我们在做系统设计的时候,第一选择应该是接口走异步处理。

说起异步处理,其实有很多种,比如:使用开启一个线程,或者使用线程池,或者使用job,或者使用mq等。

为了防止服务重启时数据的丢失问题,我们大多数情况下,会使用job或者mq来实现异步功能。

1.1 使用job

如果使用job的话,需要增加一张执行任务表,记录每次的导出任务。

用户点击全部导出按钮,会调用一个后端接口,该接口会向表中写入一条记录,该记录的状态为:待执行。

有个job,每隔一段时间(比如:5分钟),扫描一次执行任务表,查出所有状态是待执行的记录。

然后遍历这些记录,挨个执行。

需要注意的是:如果用job的话,要避免重复执行的情况。比如job每隔5分钟执行一次,但如果数据导出的功能所花费的时间超过了5分钟,在一个job周期内执行不完,就会被下一个job执行周期执行。

所以使用job时可能会出现重复执行的情况。

为了防止job重复执行的情况,该执行任务需要增加一个执行中的状态。

具体的状态变化如下:

  • 执行任务被刚记录到执行任务表,是待执行状态。
  • 当job第一次执行该执行任务时,该记录再数据库中的状态改为:执行中。
  • 当job跑完了,该记录的状态变成:完成或失败。

这样导出数据的功能,在第一个job周期内执行不完,在第二次job执行时,查询待处理状态,并不会查询出执行中状态的数据,也就是说不会重复执行。

此外,使用job还有一个硬伤即:它不是立马执行的,有一定的延迟。

如果对时间不太敏感的业务场景,可以考虑使用该方案。

1.2 使用mq

用户点击全部导出按钮,会调用一个后端接口,该接口会向mq服务端,发送一条mq消息。

有个专门的mq消费者,消费该消息,然后就可以实现excel的数据导出了。

相较于job方案,使用mq方案的话,实时性更好一些。

对于mq消费者处理失败的情况,可以增加补偿机制,自动发起重试。

RocketMQ自带了失败重试功能,如果失败次数超过了一定的阀值,则会将该消息自动放入死信队列。

2.使用easyexcel

我们知道在Java中解析和生成Excel,比较有名的框架有Apache POIjxl

但它们都存在一个严重的问题就是:非常耗内存,POI有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。

百万级别的excel数据导出功能,如果使用传统的Apache POI框架去处理,可能会消耗很大的内存,容易引发OOM问题。

easyexcel重写了POI对07版Excel的解析,之前一个3M的excel用POI sax解析,需要100M左右内存,如果改用easyexcel可以降低到几M,并且再大的Excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便。

需要在mavenpom.xml文件中引入easyexcel的jar包:

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

之后,使用起来非常方便。

读excel数据非常方便:

@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

写excel数据也非常方便:

 @Test
public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

easyexcel能大大减少占用内存的主要原因是:在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。

3.分页查询

百万级别的数据,从数据库一次性查询出来,是一件非常耗时的工作。

即使我们可以从数据库中一次性查询出所有数据,没出现连接超时问题,这么多的数据全部加载到应用服务的内存中,也有可能会导致应用服务出现OOM问题。

因此,我们从数据库中查询数据时,有必要使用分页查询。比如:每页5000条记录,分为200页查询。

public Page<User> searchUser(SearchModel searchModel) {
    List<User> userList = userMapper.searchUser(searchModel);
    Page<User> pageResponse = Page.create(userList, searchModel);
    pageResponse.setTotal(userMapper.searchUserCount(searchModel));
    return pageResponse;
}

每页大小pageSize和页码pageNo,是SearchModel类中的成员变量,在创建searchModel对象时,可以设置设置这两个参数。

然后在Mybatis的sql文件中,通过limit语句实现分页功能:

limit #{pageStart}, #{pageSize}

其中的pagetStart参数,是通过pageNo和pageSize动态计算出来的,比如:

pageStart = (pageNo - 1) * pageSize;

4.多个sheet

我们知道,excel对一个sheet存放的最大数据量,是有做限制的,一个sheet最多可以保存1048576行数据。否则在保存数据时会直接报错:

invalid row number (1048576) outside allowable range (0..1048575)

如果你想导出一百万以上的数据,excel的一个sheet肯定是存放不下的。

因此我们需要把数据保存到多个sheet中。

5.计算limit的起始位置

我之前说过,我们一般是通过limit语句来实现分页查询功能的:

limit #{pageStart}, #{pageSize}

其中的pagetStart参数,是通过pageNo和pageSize动态计算出来的,比如:

pageStart = (pageNo - 1) * pageSize;

如果只有一个sheet可以这么玩,但如果有多个sheet就会有问题。因此,我们需要重新计算limit的起始位置。

例如:

ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
int totalPage = searchUserTotalPage(searchModel);

if(totalPage > 0) {
   Page<User> page = Page.create(searchModel);
   int sheet = (totalPage % maxSheetCount == 0) ? totalPage / maxSheetCount: (totalPage / maxSheetCount) + 1;
   for(int i=0;i<sheet;i++) {
      WriterSheet writeSheet = buildSheet(i,"sheet"+i);
      int startPageNo = i*(maxSheetCount/pageSize)+1;
      int endPageNo = (i+1)*(maxSheetCount/pageSize);
      while(page.getPageNo()>=startPageNo && page.getPageNo()<=endPageNo) {
        page = searchUser(searchModel);
        if(CollectionUtils.isEmpty(page.getList())) {
            break;
        }
        
        excelWriter.write(page.getList(),writeSheet);
        page.setPageNo(page.getPageNo()+1);
     }
   }
}

这样就能实现分页查询,将数据导出到不同的excel的sheet当中。

6.文件上传到OSS

由于现在我们导出excel数据的方案改成了异步,所以没法直接将excel文件,同步返回给用户。

因此我们需要先将excel文件存放到一个地方,当用户有需要时,可以访问到。

这时,我们可以直接将文件上传到OSS文件服务器上。

通过OSS提供的上传接口,将excel上传成功后,会返回文件名称和访问路径。

我们可以将excel名称和访问路径保存到表中,这样的话,后面就可以直接通过浏览器,访问远程excel文件了。

而如果将excel文件保存到应用服务器,可能会占用比较多的磁盘空间。

一般建议将应用服务器和文件服务器分开,应用服务器需要更多的内存资源或者CPU资源,而文件服务器需要更多的磁盘资源。

7.通过WebSocket推送通知

通过上面的功能已经导出了excel文件,并且上传到了OSS文件服务器上。

接下来的任务是要本次excel导出结果,成功还是失败,通知目标用户。

有种做法是在页面上提示:正在导出excel数据,请耐心等待。

然后用户可以主动刷新当前页面,获取本地导出excel的结果。

但这种用户交互功能,不太友好。

还有一种方式是通过webSocket建立长连接,进行实时通知推送。

如果你使用了SpringBoot框架,可以直接引入webSocket的相关jar包:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-websocket</artifactId>
</dependency>

使用起来挺方便的。

我们可以加一张专门的通知表,记录通过webSocket推送的通知的、用户、附件地址、阅读状态、类型等信息。

能更好的追溯通知记录。

webSocket给客户端推送一个通知之后,用户的右上角的收件箱上,实时出现了一个小窗口,提示本次导出excel功能是成功还是失败,并且有文件下载链接。

当前通知的阅读状态是未读。

用户点击该窗口,可以看到通知的详细内容,然后通知状态变成已读。

8.总条数可配置

我们在做导百万级数据这个需求时,是给用户用的,也有可能是给运营同学用的。

其实我们应该站在实际用户的角度出发,去思考一下,这个需求是否合理。

用户拿到这个百万级别的excel文件,到底有什么用途,在他们的电脑上能否打开该excel文件,电脑是否会出现太大的卡顿了,导致文件使用不了。

如果该功能上线之后,真的发生发生这些情况,那么导出excel也没有啥意义了。

因此,非常有必要把记录的总条数,做成可配置的,可以根据用户的实际情况调整这个配置。

比如:用户发现excel中有50万的数据,可以正常访问和操作excel,这时候我们可以将总条数调整成500000,把多余的数据截取掉。

其实,在用户的操作界面,增加更多的查询条件,用户通过修改查询条件,多次导数据,可以实现将所有数据都导出的功能,这样可能更合理一些。

此外,分页查询时,每页的大小,也建议做成可配置的。

通过总条数和每页大小,可以动态调整记录数量和分页查询次数,有助于更好满足用户的需求。

9.order by商品编号

之前的需求是要将相同商品编号的数据放到一起。

例如:

编号商品名称仓库名称价格
1笔记本北京仓7234
1笔记本上海仓7235
1笔记本武汉仓7236
2平板电脑成都仓7236
2平板电脑大连仓3339

但我们做了分页查询的功能,没法将数据一次性查询出来,直接在Java内存中分组或者排序。

因此,我们需要考虑在sql语句中使用order by 商品编号,先把数据排好顺序,再查询出数据,这样就能将相同商品编号,仓库不同的数据放到一起。

此外,还有一种情况需要考虑一下,通过配置的总记录数将全部数据做了截取。

但如果最后一个商品编号在最后一页中没有查询完,可能会导致导出的最后一个商品的数据不完整。

因此,我们需要在程序中处理一下,将最后一个商品删除。

但加了order by关键字进行排序之后,如果查询sql中join了很多张表,可能会导致查询性能变差。

那么,该怎么办呢?

总结

最后用两张图,总结一下excel异步导数据的流程。

如果是使用mq导数据:

如果是使用job导数据:

这两种方式都可以,可以根据实际情况选择使用。

我们按照这套方案的开发了代码,发到了pre环境,原本以为会非常顺利,但后面却还是出现了性能问题。

后来,我们用了两招轻松解决了性能问题。

以上就是详解Java如何实现百万数据excel导出功能的详细内容,更多关于Java数据excel导出的资料请关注编程网其它相关文章!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

详解Java如何实现百万数据excel导出功能

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

详解Java如何实现百万数据excel导出功能

这篇文章主要为大家详细介绍了Java如何实现百万数据excel导出功能,文中的示例代码讲解详细,具有一定的借鉴价值,需要的可以参考一下
2023-02-03

百万数据Excel导出功能如何实现?

做一个MySQL百万数据级别的excel导出功能,如果走接口同步导出,该接口肯定会非常容易超时。因此,我们在做系统设计的时候,第一选择应该是接口走异步处理。
excelMySQL2024-12-13

如何使用Java实现百万数据的Excel导出功能?

本文介绍了如何使用Java实现百万数据的Excel导出功能,通过分批次导出和多线程并发处理的方法,有效避免了内存溢出问题和导出效率低下的问题,同时也保证了Excel文件的可读性和易用性。

如何使用java实现百万级别数据导出excel

这篇文章主要介绍了如何使用java实现百万级别数据导出excel的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇如何使用java实现百万级别数据导出excel文章都会有所收获,下面我们一起来看看吧。在业务系统中,
2023-07-05

Java如何实现导出Excel功能

这篇文章将为大家详细讲解有关Java如何实现导出Excel功能,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。刚写了个导入Excel表格,现在来写个导出,其实形式都差不多,废话不多说,贴代码<
2023-06-21

基于EasyExcel实现百万级数据导入导出详解

大数据的导入和导出,相信大家在日常的开发、面试中都会遇到。本文将为大家详细介绍一下如何利用EasyExcel实现百万级数据导入导出,需要的可以参考一下
2023-01-28

Vue如何实现导出Excel功能

小编给大家分享一下Vue如何实现导出Excel功能,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!1.前端主导流程:1.点击页面中导出按钮(注册点击事件)2.事件回调里面,发送请求后台数据3.对后台数据进行处理,完成想要的效
2023-06-20

使用 Spring Boot + EasyExcel 实现百万级数据导入导出功能开发

在实际开发中,大家需要根据具体的业务需求对数据的处理逻辑进行进一步的优化和完善。同时,还需要考虑异常处理、数据校验等方面,以确保系统的稳定性和可靠性。

利用Golang实现数据导出功能详解

标题:利用Golang实现数据导出功能详解随着信息化程度的提升,很多企业和组织需要将存储在数据库中的数据导出到不同的格式中,以便进行数据分析、报表生成等用途。本文将介绍如何利用Golang编程语言实现数据导出功能,包括连接数据库、查询数据
利用Golang实现数据导出功能详解
2024-02-28

Java高级特性-注解:注解实现Excel导出功能

注解是 Java 的一个高级特性,Spring 更是以注解为基础,发展出一套“注解驱动编程”。
Java注解Excel2024-12-03

C#中如何使用NPOI实现Excel导入导出功能

本文小编为大家详细介绍“C#中如何使用NPOI实现Excel导入导出功能”,内容详细,步骤清晰,细节处理妥当,希望这篇“C#中如何使用NPOI实现Excel导入导出功能”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧
2023-06-29

如何处理大数据量的Excel导出功能?

在使用Spring Boot和MyBatis进行报表导出功能开发时,处理大数据量的Excel文件(从几十兆到几个G)的导出是一个常见而又具有挑战性的任务。本文将详细介绍如何高效地处理大数据量的Excel导出,包括如何生成Excel文件并导出

Easypoi如何实现复杂excel文件导出功能

本篇内容介绍了“Easypoi如何实现复杂excel文件导出功能”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!之所以用Easypoi我是看中
2023-06-21

JS如何实现将数据导出到Excel

今天小编给大家分享一下JS如何实现将数据导出到Excel的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。方法一:将table标
2023-07-02

如何使用MongoDB实现数据的批量导入、导出功能

如何使用MongoDB实现数据的批量导入、导出功能MongoDB是一种NoSQL数据库,作为一种非关系型数据库,其在数据存储和查询上有着很大的灵活性和性能优势。对于需要批量导入和导出数据的应用场景,MongoDB也提供了相应的工具和接口。本
2023-10-22

基于Java SSM如何实现Excel数据批量导入

今天就跟大家聊聊有关基于Java SSM如何实现Excel数据批量导入,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。导入Maven依赖com
2023-06-21

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录