【Apache POI】Excel操作（四）：Excel大数据量的写入

最新推荐文章于 2024-05-13 13:51:17 发布
最新推荐文章于 2024-05-13 13:51:17 发布
阅读量2.2k 收藏 8
点赞数 2
分类专栏： Excel读写 文章标签： poi excel java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qizhi_hu/article/details/109561415
版权
迷茫代表着你身边还有选择，焦虑意味着你手上还有时间。

有目录，不迷路

前言

之前在下面这期Excel操作：

【Apache POI】Excel操作（一）：Excel本地写入基本操作的实现

中说到Excel有两个版本，分别是HSSF以及XSSF：
在这里插入图片描述
以及他们之间的两点不同：

第一，后缀名不同：

在这里插入图片描述
第二，最多写入的数据量也就是数据行数不同：

  1. 03版本的.xlsExcel文件最多为65536行。
  2. 07版本的.xlsxExcel文件则最多为1048576行，刚好是65536也就是03版本的16倍。

而我们如果点开Excel的工作簿Workbook，我们会发现这是一个接口。而这个接口下面有三个实现类分别是：HSSFWorkbook、XSSFWorkbook以及SXSSFWorkbook。

在这里插入图片描述
也就是说Excel似乎是有着三个版本：

  1. HSSF版本
  2. XSSF版本
  3. SXSSF版本

那么，多出来的这个SXSSF版本有什么用呢？以及这几个版本之间对于程序而言有什么具体的区别？

本期博客为你揭晓答案！！！

超量数据

本期博客所需环境如依赖等已经在以下这期博客中介绍过了，就不多加赘述：

【Apache POI】Excel操作（一）：Excel本地写入基本操作的实现

接着上面既然我们说到03版本的.xlsExcel文件最多为65536行以及07版本的.xlsxExcel文件则最多为1048576行，那我们试着来创建一下超过数据限制的行数(当然，这也是写入过量数据的前奏)：

03版：

	/**
     * 03版写入超量的数据
     */
    @Test
    public void write03ExcessData(){

        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();
        // 创建第65537行
        sheet.createRow(65536);

    }

运行得：
在这里插入图片描述
即超过了(0…65535)的行数限制：

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

07版：

	/**
     * 07版写入超量的数据
     */
    @Test
    public void write07ExcessData(){

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();
        // 创建第65537行
        sheet.createRow(1048576);

    }

运行得：
在这里插入图片描述
即超过了(0…1048575)的行数限制：

java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575)

速率比较

既然本期博客讲的是大数据量的写入，那么我们必然要关心一个问题：那就是时间问题！！！ 作为一名开发，你总不可能在写入数据量多的时候，导出个Excel让用户等它个地老天荒吧！

那就让我们分别来测试一下03版本和07版本写入65536行数据的耗时吧！

03版写入65536行数据耗时测试：

	/**
     * 03版本写入65536行数据
     */
    @Test
    public void write03BigData() throws Exception {

        // 获取当前系统的毫秒数
        long startTime = System.currentTimeMillis();

        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();

        // 外层for循环分别创建65536行
        for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
            // 创建第 rowNumber+1 行
            Row row = sheet.createRow(rowNumber);

            // 内层循环分别创建11列并写入列的索引
            for (int cellNumber = 0; cellNumber < 11; cellNumber++) {
                row.createCell(cellNumber).setCellValue(cellNumber);
            }
        }

        // 生成流
        FileOutputStream out = new FileOutputStream(PATH + File.separator + "03BigDataTest.xls");
        // 写入
        workbook.write(out);
        // 关流
        out.close();
        System.out.println("excel生成完毕!!!");

        // 获取当前系统的毫秒数
        long endTime = System.currentTimeMillis();
        // 计算耗时并打印在控制台
        System.out.println("耗时: " + (double)(endTime - startTime)/1000 + "s");
    }

运行可以得出耗时为2.29秒：
在这里插入图片描述
那我们再来看看07版本选手的表现吧！
07版写入65536行数据耗时测试：

 /**
     * 07版本写入65536行数据
     */
    @Test
    public void write07BigData() throws Exception {

        // 获取当前系统的毫秒数
        long startTime = System.currentTimeMillis();

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();

        // 外层for循环分别创建65536行
        for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
            // 创建第 rowNumber+1 行
            Row row = sheet.createRow(rowNumber);

            // 内层循环分别创建11列并写入列的索引
            for (int cellNumber = 0; cellNumber < 11; cellNumber++) {
                row.createCell(cellNumber).setCellValue(cellNumber);
            }
        }

        // 生成流
        FileOutputStream out = new FileOutputStream(PATH + File.separator + "07BigDataTest.xlsx");
        // 写入
        workbook.write(out);
        // 关流
        out.close();
        System.out.println("excel生成完毕!!!");

        // 获取当前系统的毫秒数
        long endTime = System.currentTimeMillis();
        // 计算耗时并打印在控制台
        System.out.println("耗时: " + (double)(endTime - startTime)/1000 + "s");
    }

运行可以得出耗时为12.271秒：
在这里插入图片描述
What？？？同样的数据量，07版本耗时竟然是03版本的五六倍？要是在数据量更多的情况下写出07版本的代码，那还不是连夜被老板叫过去加班吗？而且03版本的数据量比较多的情况下还不能用，简直就是屋落偏逢连夜雨。

原因剖析

那么为什么03版的HSSF和07版的XSSF会有那么大的差距呢？

原来，03版的HSSF在写入过程中，是将数据写入到缓存中，然后再一次性写入磁盘，所以HSSF的速率很快。当然，他的缺点也很明显，就是能够写入的数据量不够大！！！

而，07版本XSSF是通过内存加载数据，非常的消耗内存，速率也比较低下。虽然XSSF理论上能够写入1048576行数据，但是它也很容易发生内存溢出，如果我们试着用XSSF来写入一百万条数据：

	/**
     * 07版本写入一百万行数据
     */
    @Test
    public void write07BigData() throws Exception {

        // 获取当前系统的毫秒数
        long startTime = System.currentTimeMillis();

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();

        // 外层for循环分别创建65536行
        for (int rowNumber = 0; rowNumber < 999999; rowNumber++) {
            // 创建第 rowNumber+1 行
            Row row = sheet.createRow(rowNumber);

            // 内层循环分别创建11列并写入列的索引
            for (int cellNumber = 0; cellNumber < 11; cellNumber++) {
                row.createCell(cellNumber).setCellValue(cellNumber);
            }
        }

        // 生成流
        FileOutputStream out = new FileOutputStream(PATH + File.separator + "07BigDataTest.xlsx");
        // 写入
        workbook.write(out);
        // 关流
        out.close();
        System.out.println("excel生成完毕!!!");

        // 获取当前系统的毫秒数
        long endTime = System.currentTimeMillis();
        // 计算耗时并打印在控制台
        System.out.println("耗时: " + (double)(endTime - startTime)/1000 + "s");
    }

然后，成功内存溢出，即：java.lang.OutOfMemoryError: Java heap space：
在这里插入图片描述

超级版本大救星

那么，现在问题来了：就目前而言，Excel大数据量的写入如20万条数据。不能用03版本，只能用07版本，但是07版本XSSF的效率低下，并且容易发生内存溢出。那么，我们应该怎么办呢？

这个时候，本文开始提出的另一个版本-SXSSF，也就是 Super - XSSF，XSSF超级版就横空出世了：

我们再来测一下SXSSF写入65536行数据的耗时：

	/**
     * SXSSF版本写入65536行数据耗时统计
     */
    @Test
    public void writeSXSSFBigData() throws Exception {

        // 获取当前系统的毫秒数
        long startTime = System.currentTimeMillis();

        // 创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet();

        // 外层for循环分别创建65536行
        for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
            // 创建第 rowNumber+1 行
            Row row = sheet.createRow(rowNumber);

            // 内层循环分别创建11列并写入列的索引
            for (int cellNumber = 0; cellNumber < 11; cellNumber++) {
                row.createCell(cellNumber).setCellValue(cellNumber);
            }
        }

        // 生成流
        FileOutputStream out = new FileOutputStream(PATH + File.separator + "07BigDataTestSuper.xlsx");
        // 写入
        workbook.write(out);
        // 关流
        out.close();
        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        System.out.println("excel生成完毕!!!");

        // 获取当前系统的毫秒数
        long endTime = System.currentTimeMillis();
        // 计算耗时并打印在控制台
        System.out.println("耗时: " + (double)(endTime - startTime)/1000 + "s");
    }

代码与07版本的其实只有两处不同：

第一，创建的对象：

// 创建工作簿
Workbook workbook = new SXSSFWorkbook();

第二，由于SXSSF会生成临时文件，所以需要清除临时文件：

// 清除临时文件
((SXSSFWorkbook)workbook).dispose();

最后运行得：
在这里插入图片描述
从12秒多到3秒多，节省了近3倍的时间，简直是质的飞越！

而，为什么SXSSF版本比XSSF版本速率快这么多呢？

原来，SXSSF在写入过程中会产生临时文件，它默认有100条记录保存在内存中，如果超过一百条，则在最前面的数据会被写入到临时文件里。

当然，我们也可以自定义内存中保存记录的数量，如我想保存120条：

 // 创建工作簿
Workbook workbook = new SXSSFWorkbook(120);

好了，到目前为止Apach POI的写入操作就结束了。接下来的博客将给大家带来Apach POI对Excel的读取操作以及EasyExcel对Excel的读写操作，感兴趣的小伙伴可以关注我哦！

往期回顾

以下是往期Excel操作的回顾：

【Apache POI】Excel操作（一）：Excel本地写入基本操作的实现

【Apache POI】Excel操作（二）：Excel本地写入基本操作的实现（进阶版）

【Apache POI】Excel操作（三）：Excel在浏览器端即Web端写入操作的实现

参考资料：【狂神说Java】POI及EasyExcel一小时搞定通俗易懂

古阙月
关注
  • 2
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
poi写入大数据到Excel及调优
moerduo0的博客
02-17 692
Excel版本介绍及写入参考上一篇： https://blog.csdn.net/moerduo0/article/details/113833765 创建一个maven项目并导入poi依赖 <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance
使用POI导出大数据量到EXCEL
08-11
java版本的使用POI导出大数据量到EXCEL
最全POI简介，以及使用POI技术实现Excel文件的导入导出案例，消息队列面试题
最新发布
2301_82243700的博客
05-13 1125
小编利用空余时间整理了一份《MySQL性能调优手册》，初衷也很简单，就是希望能够帮助到大家，减轻大家的负担和节省时间。关于这个，给大家看一份学习大纲（PDF）文件，每一个分支里面会有详细的介绍。这里都是以图片形式展示介绍，如要下载原文件以及更多的性能调优笔记（MySQL+Tomcat+JVM）！本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录需要这份系统化的资料的朋友，可以点击这里获取的介绍。
excel的大数据量用POI写入
香菜的纯技术博客
03-30 314
[url=http://www.neverevernote.com/?p=12]excel的大数据量用POI写入 香菜个人博客[/url] 有50万的数据需要分析处理。 处理完毕后写入excel中，excel里需要用一个sheet放所有数据(excel用的是2007以后的版本,行数限制从老版的6万多突破到了100多万) 直接写入跑了一会就OUTOFMEMORY了 多番寻找终于找到...
大数据写入Excel
yuing
10-23 297
import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.OutputStreamWriter;i...
Apache POI操作批量导入MySQL数据库
我要记录学习博客
06-18 805
POI使用到的相关maven依赖坐标如下： POI的相关操作结果 1、POI操作入门案例 1.1、从Excel文件读取数据1 输出结果如下： POI操作Excel表格封装了几个核心对象： 1.2、从Excel文件读取数据2 1.3、向Excel文件写入数据 2、使用POI批量导入数据到MySQL数据库 创建一个数据库的表 创建对应的实体类 2.1、创建导入数据的Excel模板文件 修改页面提供一个下载批量导入数据的模板按钮 文件上传前端代码实现 提交函数 2.2、批量上传文件的后端代码编写
Apache POI和EasyExcel 第三集：Apache POI的Excel大数据量写入（分为03版的xls、07版的xlsx、升级版SXSSF）
XRT_knives的博客
12-12 1555
Apache POI和EasyExcel 第三集：Apache POI的Excel大数据量写入（分为03版的xls、07版的xlsx、升级版SXSSF） 一、结果 我的03跑了1.204秒，07跑了5.173秒，升级版跑了1.96秒，大家的会是多少呢？ 二、代码实现 @Test public void testWrite03BigData() throws IOException { //记录开始时间 long begin = System.curre
IO(11) : 大量数据快速写入文件
Lxinccode的博客
12-10 709
import java.io.BufferedWriter; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStreamWriter; import java.nio.charset.StandardCharsets; import java.util.LinkedList; import java.util.List; /** * @Author: liyue * @Date: 2021/12..
惊了！如何通过阿里 EasyExcel 7 行代码, 优雅地实现 Excel 文件导出功能？
Harry博客
06-28 1800
目录 一、前言 二、Apache poi、jxl 的缺陷 三、阿里出品的 EasyExcel，安利一波 四、EasyExcel 解决了什么 五、快速上手 六、特殊场景支持 七、Web 下载示例代码 八、需要注意的点 九、总结 一、前言 关于导出 Excel 文件，可以说是大多数服务中都需要集成的功能。那么，要如何优雅快速地（偷懒地）去实现这个功能呢？ 你可能第一想法是：这还不简单？用 Apache 开源框架 poi
excel导出 报错（Invalid row number (1048576) outside allowable range (0..1048575)）
Sucker的小笔记
06-18 4451
不是代码问题 ，是excel支持的sheet最大行数为1048576行，解决办法是超过最大行数后新建一个sheet页
XSSFWorkbook读取excel模版后写入的数据过多造成OOM，使用SXSSFWorkbook解决
FinelyYang的专栏
12-27 1万+
最近在项目中遇到这样的场景，将一批json文件（上千个）数据转为list后，按照给定的excel模版格式，填充到模版中生成最终的excel文件。但是因为json文件过多，或者json文件中转为list后的数据量过大造成了oom异常：java.lang.OutOfMemoryError:Java heap space。 原因在于XSSFWorkbook创建的 book sheet row cell 等，此时是存在内存的并没有持久化，那么随着数据量增大内存的需求量也就增大，那么很大可能就是要 ...
poi 大量数据写入
AntHub的博客
04-29 1825
文章目录poi poi 2003 版本: HSSFWorkbook 2007 版本以上: XSSFWorkbook 读取 excel： 大批量数据: 使用 poi 官方提供的excel 转换为 csv 格式来读取； 生成 excel 大批量数据: 使用SXXFWorkbook类进行大批量写入操作 写入大批量数据示例: package org.bird.poi; import java.io.File; import java.io.FileOutputStream; impor
超大Excel文件的写入
qq_59616295的博客
07-13 1160
对于超大Excel文件的写入，对比两种不同的方法，选择更合适的
已解决java.lang.IllegalArgumentException异常的正确解决方法，亲测有效！！！
小明的Java问道之路
01-07 2万+
IllegalArgumentException是由于传递给方法的参数非法或不正确而引发的，要解决这个问题，需要在调用方法前验证输入，并在方法内部使用断言来检查参数的有效性。
java.lang.IllegalArgumentException 异常报错完美解决
辰小白
07-12 15万+
目录 修改JDK使用版本 修改开发工具idea配置（eclipse的直接跳过这个看下面） 修改开发工具eclipse配置 学习spring依赖注入的时候碰到这个坑，折腾了许久，记录一下以防其他小伙伴入坑！ 该异常主要原因是因为JDK与Spring版本不一致。要么更换jdk，要么重新下载符合jdk的springjar包 我本地装了两个JDK版本，一个1.8，一个1.7，原本以为只要修改一...
Java 通过POI技术实现对Excel中大量数据的读和写操作
qq_36511997的博客
07-14 2388
POI技术
错误提示：java.lang.IllegalArgumentException解决方法
热门推荐
飞翔射手座
03-09 22万+
我的程序出现这个错误的原因是编译环境和运行环境的jdk版本不一致，首先是spring mvc的版本是3.*的，而我本机的jdk是jdk8，查阅资料后得知他俩的兼容性不是很好，有问题，只能用jdk7及以下的版本，所以我就在myeclipse中直接导入了一个jdk1.7，但是这样依然没有解决，这是因为，这样导入后，我的运行环境和编译环境不一致，所以就要经过以下的设置： 选中项目，单击右键-》Prope
大数据最全使用POI进行写操作_poi写入数据
2401_84181536的博客
05-04 926
/ 在Excel工作簿中建一工作表，其名为缺省值 Sheet0。//-1：关闭 auto-flushing，将所有数据存在内存中。优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。// 新建一输出文件流（注意：要先创建文件夹）// 新建一输出文件流（注意：要先创建文件夹）//创建一个SXSSFWorkbook。// 把相应的Excel 工作簿存盘。// 创建单元格（col 1-1）// 创建单元格（col 1-2）// 创建单元格（col 2-1）//xls文件最大支持65536行。
Apache POI实现Excel导入读取数据和写入数据并导出
m0_61820867的博客
07-26 1886
接收Apache POI的背景，以及利用POI实现excel的导入读取以及写入导出
java大数据量导出excel
06-06
Java 处理大数据量导出 Excel 的常用方法是使用 Apache POI 库。以下是基本步骤： 1. 创建工作簿和工作表对象。 ``` Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Sheet1"); ``` 2. 创建表头并写入数据。 ``` Row headerRow = sheet.createRow(0); Cell headerCell1 = headerRow.createCell(0); headerCell1.setCellValue("Column1"); Cell headerCell2 = headerRow.createCell(1); headerCell2.setCellValue("Column2"); ``` 3. 遍历数据集合，逐行写入。 ``` List<List<String>> data = getData(); int rowIndex = 1; for (List<String> rowData : data) { Row row = sheet.createRow(rowIndex++); int cellIndex = 0; for (String cellData : rowData) { Cell cell = row.createCell(cellIndex++); cell.setCellValue(cellData); } } ``` 4. 将数据写入 Excel 文件并保存。 ``` FileOutputStream outputStream = new FileOutputStream("output.xlsx"); workbook.write(outputStream); outputStream.close(); ``` 注意：在处理大数据量时，应该使用流式写入和内存优化等技巧来减少内存占用和提高导出速度。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值