V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Kimen
V2EX  ›  Java

POI 如何快速导出千万级数据的 Excel

  •  
  •   Kimen ·
    Kimentanm · 2021-08-11 17:26:37 +08:00 · 5244 次点击
    这是一个创建于 950 天前的主题,其中的信息可能已经有所发展或是发生改变。

    在使用 POI 导出一千万行数据到 Excel 时,CPU 长时间爆炸💥
    现在的解决方案是:

    • 使用 SXSSFWorkbook 类
    • 每 50W 数据划分 sheet
    • 采用多线程,每个 sheet 开一个线程去跑

    现已探明的问题:
    一千万的数据在创建 Excel 前都存储在 list 中,当 list 占用内存很小时,导出很快,一旦 list 变得很大,分段导出都会变的很慢

    53 条回复    2021-08-13 16:30:18 +08:00
    mhycy
        1
    mhycy  
       2021-08-11 17:28:07 +08:00
    先不说导出,就是成功导出了,这千万级数据的 Excel 有办法开么
    Kimen
        2
    Kimen  
    OP
       2021-08-11 17:31:08 +08:00
    @mhycy 哎,客户要求的,之前 100w 试过是可以打开的
    fengpan567
        3
    fengpan567  
       2021-08-11 17:45:40 +08:00
    为什么要搞一个 1000W 大小的 list 出来,不能分页查吗?
    fox1751
        4
    fox1751  
       2021-08-11 17:49:15 +08:00   ❤️ 1
    搞明白客户的真实需求吧.
    7925250
        5
    7925250  
       2021-08-11 17:53:05 +08:00   ❤️ 1
    可以改造成流式导出,一千万的数据 (1.3GB) 导出,耗时 2 分钟
    wangkun025
        6
    wangkun025  
       2021-08-11 18:03:53 +08:00
    Excel 目前单个 sheet 只能容纳 104 万行。不过可以分 sheet,sheet 数量没有限制。
    chendy
        7
    chendy  
       2021-08-11 18:22:05 +08:00
    直接走数据库查询导出 csv 吧
    MidGap
        8
    MidGap  
       2021-08-11 18:30:42 +08:00
    曾经踩过内存溢出的坑,就是用使用 SXSSFWorkbook 类这个方法解决的,但是这样 IO 好像就会变密集,慢也是无法避免的。。 本身这个需求感觉就很扯淡,使劲 battle 一下多好~ 或者用 csv 啊,简单多了
    MidGap
        9
    MidGap  
       2021-08-11 18:31:42 +08:00
    @fengpan567 excel 的 append 操作很坑,要先读到内存再往下写我记得
    aguesuka
        10
    aguesuka  
       2021-08-11 18:48:23 +08:00   ❤️ 8
    直接导出 csv 文件, 能提出导出 1000 万行 excel 数据的客户估计也分不清这两种数据的区别
    franklinray
        11
    franklinray  
       2021-08-11 18:52:06 +08:00
    导出 1000 万行 excel,一般电脑都打不开吧。打开了谁能去查看数据……如果只是为了导出数据,然后导入其他系统做分析,换成 csv 不好吗
    binsys
        12
    binsys  
       2021-08-11 19:10:06 +08:00
    我处理大量 EXCEL 数据用 MS 的 OPENXMLSDK ,在直接以 XML SAX 形式写入
    pengtdyd
        13
    pengtdyd  
       2021-08-11 19:36:26 +08:00
    什么样的电脑可以打开 1000w 的 excel,很难想象
    jzphx
        14
    jzphx  
       2021-08-11 19:47:33 +08:00
    easyexcel 尝试用过了吗
    liuxu
        15
    liuxu  
       2021-08-11 21:24:46 +08:00
    这种阔气的问题,只有 java 节点提的出来
    xuanbg
        16
    xuanbg  
       2021-08-11 21:40:30 +08:00
    50 万行的 excel,我 i9/32G 的 mbp 打开是能打开,但很费劲。100 万行是想都不敢想,何况 1000 万行。SB 客户不能惯着
    7228897
        17
    7228897  
       2021-08-11 21:51:44 +08:00
    单位的电脑 4G 内存,打开 40 万条的月数据已经卡的不能动了
    chenqh
        18
    chenqh  
       2021-08-11 21:54:30 +08:00
    1KW 估计内存会炸把
    EscYezi
        19
    EscYezi  
       2021-08-11 23:45:05 +08:00 via iPhone
    之前用 easyExcel 看官网说是擅长导出大量数据,楼主可以体验一下🤣
    jorneyr
        20
    jorneyr  
       2021-08-12 08:06:10 +08:00
    这是要把整个数据库都给搬家了
    Kimen
        21
    Kimen  
    OP
       2021-08-12 08:06:35 +08:00
    @fengpan567 数据实时在产生,分页查会导致第二页和第一页数据有重复
    Kimen
        22
    Kimen  
    OP
       2021-08-12 08:07:21 +08:00
    @chendy csv 碰到数据里面带逗号的都懵逼了
    Kimen
        23
    Kimen  
    OP
       2021-08-12 08:10:01 +08:00
    @jzphx 没有用过 easyexcel,我看文档 easyexcel 是基于注解的,但是我的数据流是 List<Map<String, Object>>这种形式的,不知道能不能用
    chendy
        24
    chendy  
       2021-08-12 08:10:58 +08:00
    @Kimen #22 能转义,不用程序实现直接用数据库的导出功能
    Kimen
        25
    Kimen  
    OP
       2021-08-12 08:11:30 +08:00
    @franklinray csv 就怕遇到逗号,用户在导出之前也不知道自己能不能打开,就想看看能不能导出来。看来我要跟领导提一下,限制用户的导出数量
    Kimen
        26
    Kimen  
    OP
       2021-08-12 08:13:18 +08:00
    @jorneyr 心里已经锤死客户一千万遍了
    A1exlee
        27
    A1exlee  
       2021-08-12 09:06:38 +08:00
    不建议一次性将一千万数据加载到 list,可以考虑分页读库,比如每次 load 100 万条,然后写入一个 excel,分开写 10 个 excel,通过编号确认顺序,最后客户端打包下载。
    fpure
        28
    fpure  
       2021-08-12 09:21:22 +08:00
    @MidGap SXSSFWorkbook 还要记得清理硬盘缓存,也是坑
    spLite
        29
    spLite  
       2021-08-12 09:31:53 +08:00
    数据库导出 Excel 或者 csv,应用程序只负责把导出的文件传输给用户。
    “用户在导出之前也不知道自己能不能打开” —— 可以先手动搞一个 1000W 的 Excel 让客户打开看看么,如果他自己看下打开费劲,说不定自己就否了这方案了。
    doudou1523102
        30
    doudou1523102  
       2021-08-12 09:42:38 +08:00
    建议分批导出,然后进行压缩
    kifile
        31
    kifile  
       2021-08-12 10:06:25 +08:00
    csv 不是也可以用 excel 打开吗?还是流式卸乳的,考虑考虑
    speedofstephen
        32
    speedofstephen  
       2021-08-12 10:15:06 +08:00
    poi 有流式 api 写 excel 的。建议不要全量放在 list 中
    lwjef
        33
    lwjef  
       2021-08-12 10:28:12 +08:00   ❤️ 1
    创建个占用空间很大的假 excel,反正用户打不开。(doge
    MidGap
        34
    MidGap  
       2021-08-12 10:28:32 +08:00
    @Kimen CSV 的分隔符可以指定,不一定非要逗号的。。
    onionKnight888
        35
    onionKnight888  
       2021-08-12 11:05:26 +08:00
    @liuxu 哈哈 你永远无法理解客户的需求有多奇葩
    zhenglin
        36
    zhenglin  
       2021-08-12 11:26:02 +08:00
    建议导出为多个 csv 再压缩成 zip 文件,SB 需求不能接!再说千万数据在一个 Excel 里他如果是普通电脑能打开么?
    2bNot2b
        37
    2bNot2b  
       2021-08-12 11:37:57 +08:00
    excel 有行数上限,我记得 xls 是 10W 多吧,xlsx 是 1048576
    wqhui
        38
    wqhui  
       2021-08-12 11:56:00 +08:00
    上面三个方法我当时都用了,poi 我记得有个流模式,类似于窗口控制,读一部分写一部分这个会快很多,忘了是不是叫 SXSSFWorkbook,但占内存还是挺多的,我当时三百万记录差不多耗 2G 内存,不过为什么不分几个 excel 文件然后打成压缩包呢,excel 太大打开可能会崩溃
    aitaii
        39
    aitaii  
       2021-08-12 12:24:46 +08:00
    excel 很大,你的电脑忍一下
    Lemeng
        40
    Lemeng  
       2021-08-12 13:26:11 +08:00
    千万级,客户也是秀逗
    kg2019
        41
    kg2019  
       2021-08-12 13:27:41 +08:00
    easyExce 了解一下
    levon
        42
    levon  
       2021-08-12 13:30:49 +08:00
    分文件吧,存在一个 excel 里干吗用
    jack778
        43
    jack778  
       2021-08-12 13:42:23 +08:00
    那么大弄成一个文件怎么操作呢,客户的电脑配置有多牛呢
    Hallelu
        44
    Hallelu  
       2021-08-12 13:50:57 +08:00
    之前跟一个客户做 excel 导出的功能,他说数据多,直接给他声明,单个 excel 超过一百万,不做....
    est
        45
    est  
       2021-08-12 14:13:44 +08:00
    .csv 斥候
    dayeye2006199
        46
    dayeye2006199  
       2021-08-12 14:35:15 +08:00
    建议走 tsv + 份文件 + 压缩的路线吧。
    excel 虽然很牛。。但也不是万能的。
    Vegetable
        47
    Vegetable  
       2021-08-12 14:38:04 +08:00
    实际上,这个性能瓶颈时 Excel 本身带来的,Excel 天生不适合大规模数据,导出到 Excel 是一个非常耗时的操作。建议不要分 Sheet,而是分工作簿。
    wxw752
        48
    wxw752  
       2021-08-12 15:15:27 +08:00
    easyexcel 导出 List<Map<String, Object>> 这种格式的 我昨天刚写完
    wxy1991
        49
    wxy1991  
       2021-08-12 19:18:20 +08:00
    除了 easyexcel 还有个 eec,不用依赖 poi,效率比 easyexcel 还要高,不过你这很明显需求不合理,1000w 的 excel 我就不信有电脑能打开,我的破电脑打开个 500m 的 word 感觉就要卡死了
    someonedeng
        50
    someonedeng  
       2021-08-12 22:34:03 +08:00
    真给他导出了,他怎么打开? 能不能分成多个 excel 文件再打包 zip ?
    beginor
        51
    beginor  
       2021-08-13 07:53:01 +08:00 via Android
    软件工程师,多动脑子,不是程序猿。
    dcncy
        52
    dcncy  
       2021-08-13 08:11:18 +08:00 via iPhone
    之前做过一个 700w+的数据导出,每一行 40 多列。
    使用的是 easyExcel 导的,分多个 excel,多个 sheet,多线程跑效率能快点。
    janda
        53
    janda  
       2021-08-13 16:30:18 +08:00
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5853 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 42ms · UTC 02:39 · PVG 10:39 · LAX 19:39 · JFK 22:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.