【Apache POI】Excel操作（四）：Excel大数据量的写入_poi excel 导入最大数据量是多少

迷茫代表着你身边还有选择，焦虑意味着你手上还有时间。

前言

之前在下面这期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);

    }
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一小时搞定通俗易懂