当前位置:   article > 正文

【Apache POI】Excel操作(四):Excel大数据量的写入_poi excel 导入最大数据量是多少

poi excel 导入最大数据量是多少
迷茫代表着你身边还有选择,焦虑意味着你手上还有时间。

前言

之前在下面这期Excel操作:

【Apache POI】Excel操作(一):Excel本地写入基本操作的实现

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

第一,后缀名不同:

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

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

而我们如果点开Excel的工作簿Workbook,我们会发现这是一个接口。而这个接口下面有三个实现类分别是:HSSFWorkbookXSSFWorkbook以及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);

    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

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

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

07版:

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

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

    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

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

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

速率比较

既然本期博客讲的是大数据量的写入,那么我们必然要关心一个问题:那就是时间问题!!! 作为一名开发,你总不可能在写入数据量多的时候,导出个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");
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

运行可以得出耗时为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");
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

运行可以得出耗时为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");
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

然后,成功内存溢出,即: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");
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

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

第一,创建的对象:

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

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

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

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

而,为什么SXSSF版本比XSSF版本速率快这么多呢?

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

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

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

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

往期回顾

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

【Apache POI】Excel操作(一):Excel本地写入基本操作的实现

【Apache POI】Excel操作(二):Excel本地写入基本操作的实现(进阶版)

【Apache POI】Excel操作(三):Excel在浏览器端即Web端写入操作的实现

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/88895
推荐阅读
相关标签
  

闽ICP备14008679号