您当前的位置:首页 > 电脑百科 > 软件技术 > office百科

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

时间:2023-02-03 12:18:11  来源:51CTO  作者:苏三呀
做一个MySQL百万数据级别的Excel导出功能,如果走接口同步导出,该接口肯定会非常容易超时。因此,我们在做系统设计的时候,第一选择应该是接口走异步处理。

 

 

前言

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

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

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

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

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

图片

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

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

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

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

1、异步处理

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

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

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

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

(1)使用job

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

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

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

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

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

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

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

具体的状态变化如下:

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

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

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

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

(2)使用mq

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

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

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

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

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

2、使用easyexcel

我们知道在JAVA​中解析和生成Excel​,比较有名的框架Apache POI和jxl。

但它们都存在一个严重的问题就是:非常耗内存​,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模式,在上层做了模型转换的封装,让使用者更加简单方便。

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

 

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.2</version>
</dependency>
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

 

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

读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();
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

 

写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());
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

 

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;
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

 

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

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

 

limit #{pageStart}, #{pageSize}
  • 1.

 

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

 

pageStart = (pageNo - 1) * pageSize;
  • 1.

 

4、多个sheet

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

 

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

 

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

图片

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

图片

5、计算limit的起始位置

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

 

limit #{pageStart}, #{pageSize}
  • 1.

 

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

 

pageStart = (pageNo - 1) * pageSize;
  • 1.

 

如果只有一个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);
     }
   }
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.

 

这样就能实现分页查询,将数据导出到不同的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>
  • 1.
  • 2.
  • 3.
  • 4.

 

使用起来挺方便的。

我们可以加一张专门的通知表,记录通过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环境,原本以为会非常顺利,但后面却还是出现了性能问题。

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



Tags:Excel   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
Excel排序时如何忽略空格进行排序?
在 Excel 中进行数据排序时,有时候数据中会包含空格,这可能会影响排序的准确性和结果。在这种情况下,我们可以通过一些技巧来使 Excel 跳过空格进行排序。接下来,我将详细介绍在...【详细内容】
2024-01-09  Search: Excel  点击:(115)  评论:(0)  加入收藏
Excel技巧:用表格抠图,轻松抠出透明签字
有一个电子文件,需要进行进行签名,如果我们直接用手机拍照,然后截图上传的效果不好,然后你也不会用专业图像软件来进行处理,其实用Excel就可以简单几步,快速的进行搞定 只需要简单...【详细内容】
2023-12-18  Search: Excel  点击:(61)  评论:(0)  加入收藏
深入解析NPOI库:掌握如何在.NET应用中灵活读取和修改Excel文件
一、NPOI库简介NPOI(Non-Profit Open Source Software for .NET)是一个开源的.NET库,用于读取和写入Microsoft Office格式文件,包括Excel、Word和PowerPoint等。它提供了一套丰...【详细内容】
2023-11-02  Search: Excel  点击:(261)  评论:(0)  加入收藏
Python文件操作:JSON、CSV、TSV、Excel和Pickle文件序列化
文件操作是Python编程的重要部分,它涉及处理各种文件格式,包括JSON、CSV、TSV、Excel和Pickle。一、JSON文件操作1.1 什是JSON?JSON(JavaScript Object Notation)是一种轻量级数...【详细内容】
2023-10-26  Search: Excel  点击:(77)  评论:(0)  加入收藏
Python大型Excel文件处理:快速导入、导出与批量处理
Python 是一种功能强大的编程语言,它提供了丰富的库和工具,使得处理大型 Excel 文件变得容易和高效。下面将介绍如何使用 Python 快速导入、导出和批量处理大型 Excel 文件。...【详细内容】
2023-09-20  Search: Excel  点击:(258)  评论:(0)  加入收藏
如何批量将Excel转换为PDF?介绍四个效果较好的方案
大家在日常办公时,应该会经常遇到Excel表格,它可以帮助我们统计数据。但在某些特殊的情况下,改动一个数字都会影响整个数据的真实性,所以很多时候为了保证数据没有被其他人篡改...【详细内容】
2023-09-18  Search: Excel  点击:(193)  评论:(0)  加入收藏
Python处理Excel文件并打包EXE可执行文件
01推荐用openpyxl库处理Excel表在现代的数据驱动时代,Excel表格成为各行各业中最常用的数据存储和处理工具之一。然而,对于大量数据或需要自动化处理的任务来说,手动操作Excel...【详细内容】
2023-08-29  Search: Excel  点击:(352)  评论:(0)  加入收藏
Python之父加入3年,微软终于对Python下手:直接放进Excel!
撰稿 | 言征Excel 中也可以用 Python 了! 就在昨晚,微软团队宣布了 Excel 的 Python 原生集成公测版。这意味着 Python 里的库、数据分析和可视化的能力都在桌面版的 Excel 中...【详细内容】
2023-08-24  Search: Excel  点击:(271)  评论:(0)  加入收藏
如果Excel工作表被加密,没有密码无法撤销保护,可以尝试以下方法解决
如果我们自己设置了excel加密,但是因为长时间没有使用忘记了密码,当我们想要编辑excel文件的时候,就发现没办法输入正确excel密码了。那么,excel工作表保护就没办法取消了嘛?我们...【详细内容】
2023-08-23  Search: Excel  点击:(349)  评论:(0)  加入收藏
Excel变天!微软把Python「塞」进去了,直接可搞机器学习
喜大普奔!微软把Python弄进Excel了!搭建一个机器学习天气预测模型,在Excel里即可实现。而且无需任何设置,在单元格里输入“=PY”,就能开搞。数据清理、预测分析、可视化等等任务,...【详细内容】
2023-08-23  Search: Excel  点击:(262)  评论:(0)  加入收藏
▌简易百科推荐
如何取消word文档自动编号?
在word文档中,当我们编辑有序号的文本内容时,word会自动识别给文本编号,如果直接删除序号再按回车键,序号又会自动填充。但有时候我们并不需要它自动编号或不喜欢它的编号格式,如...【详细内容】
2024-03-11  系统正在升级ing    Tags:word   点击:(24)  评论:(0)  加入收藏
Word文档无法调整行间距,怎么办?
在word文档中,有时我们选取文字,对文字设置行间距,会发现行间距没有变化或者变化不明显,这种情况该如何解决呢?下面给大家介绍一下两种解决方法。封面方法步骤方法一:段落设置1、...【详细内容】
2024-03-08  系统正在升级ing    Tags:Word文档   点击:(28)  评论:(0)  加入收藏
忘记Word文档密码?教你如何快速解密解决!
在网上找回word文档密码、去除word文档密码非常简单。具体步骤如下:1.百度搜索““六牛助手”官网”;2.点击“立即开始”并在用户中心上传需要解密的文件,稍等片刻即可找回密码...【详细内容】
2024-03-01  志卜爱生活    Tags:Word文档   点击:(30)  评论:(0)  加入收藏
如何设置Word表格每页都有表头?
在word制作表格过程中,如果表格有多页内容,通常只有首页会有标题表头(如图1),就会需要频繁翻回第一页去查看表头内容,使得浏览表格没有那么方便,这时候就需要让表格每一页都显示表...【详细内容】
2024-03-01  系统正在升级ing    Tags:Word   点击:(34)  评论:(0)  加入收藏
Word中密码设置、重置、删除和查找的方法,看这篇就差不多了
这篇文章解释了如何加密和解锁Word文档,以及当你忘记密码是什么时,从Word文档中删除密码的最佳方法。本文中的说明适用于Microsoft Word for Microsoft 365、Word 2019、Word...【详细内容】
2024-02-06  驾驭信息纵横科技    Tags:Word   点击:(98)  评论:(0)  加入收藏
Excel排序时如何忽略空格进行排序?
在 Excel 中进行数据排序时,有时候数据中会包含空格,这可能会影响排序的准确性和结果。在这种情况下,我们可以通过一些技巧来使 Excel 跳过空格进行排序。接下来,我将详细介绍在...【详细内容】
2024-01-09  编程资料站    Tags:Excel   点击:(115)  评论:(0)  加入收藏
别再被误导了!预装正版Office是什么意思?
在购买电脑时,销售员总是热情地告诉我们,这款电脑预装了最新版的Office软件,无需额外购买。然而,当我们欢天喜地地把新电脑带回家,却发现这个所谓的【预装正版Office】,并不像我们...【详细内容】
2024-01-03  Excel函数表姐  今日头条  Tags:Office   点击:(119)  评论:(0)  加入收藏
Office永久免费激活方法
新电脑预装Office却不知道如何激活?小编教你一波Office永久免费激活方法注意啦~请先确认您的机器有预装永久免费使用版Office 家庭和学生版确认方法①外箱上有大大的贴纸确认...【详细内容】
2023-12-29  听我硕    Tags:Office   点击:(107)  评论:(0)  加入收藏
Excel技巧:用表格抠图,轻松抠出透明签字
有一个电子文件,需要进行进行签名,如果我们直接用手机拍照,然后截图上传的效果不好,然后你也不会用专业图像软件来进行处理,其实用Excel就可以简单几步,快速的进行搞定 只需要简单...【详细内容】
2023-12-18  Excel自学成才  今日头条  Tags:Excel   点击:(61)  评论:(0)  加入收藏
如何在pdf上编辑文字?编辑教程看这几个
如何在pdf上编辑文字?在过去的几年中,随着越来越多的人使用电子文档进行工作,PDF格式变得越来越重要。PDF文件通常用于各种用途,如电子书、报告、手册等。在使用PDF文件时,大家可...【详细内容】
2023-08-09  咸鱼蛋生活    Tags:pdf   点击:(342)  评论:(0)  加入收藏
站内最新
站内热门
站内头条