JAVA将百万级数据高效的导出到Excel表单

今天，一朋友问我使用JAVA有没有什么办法导出百万级的数据到Excel工作表。

当时我的第一个念头就是这真的是一个好疯狂的念头。然后就想假如真的有这样类似的需求，我自己应该怎么做呢？

ps: 首先科普一下基础知识

Excel 2003及以下的版本。一张表最大支持65536行数据，256列。也就是说excel2003完全不可能满足百万数据导出的需求。

Excel 2007-2010版本。一张表最大支持1048576行，16384列；

笔者使用的是office 2010，更高的版本笔者没有使用过，暂时无法判断。

由此看来百万级的数据量对Excel自身已经是属于接近极限的程度。

假如我们有更大的需求怎么办呢？

既然单表支持最大是104w条数据，那么更大的需求量我们就只能通过程序级分表操作的方式来实现了。O(∩_∩)O哈哈~

对于操作Excel的类库。笔者其实了解的并不是很多。只是很早以前使用过POI这个类库，感觉很不错。于是决定从它入手。看看POI有没有什么比较有效的好点的解决办法。由于笔者以前使用的POI版本比较低。而且使用于excel 2003版本。所以遇到了不少问题。

编辑器： Intellij IDEA 2017.03
类库需求： POI-3.10-Final

以下是03版本的POM引用以及测试代码

<dependency>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi</artifactId>
             <version>3.10-FINAL</version>
</dependency>

以下是测试代码：
入参 ：20000， 20 结果： 耗时7S
200000， 20 结果：内存溢出

public static void XSSFWorkbook(int rows, int col) throws Exception {
        long start = System.currentTimeMillis();
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        Sheet first = workbook1.createSheet("sheet1");
        for (int i = 0; i < rows; i++) {
            Row row = first.createRow(i);
            for (int j = 0; j < col; j++) {
                if(i == 0) {
                    // 首行
                    row.createCell(j).setCellValue("column" + j);
                } else {
                    // 数据
                    if (j == 0) {
                        CellUtil.createCell(row, j, String.valueOf(i));
                    } else
                        CellUtil.createCell(row, j, String.valueOf(Math.random()));
                }
            }
        }
        FileOutputStream out = new FileOutputStream("E:/XSSFWorkbookworkbook.xlsx");
        workbook1.write(out);
        out.close();
        System.out.println(("XSSFWorkbook "+(System.currentTimeMillis()-start)/1000));
    }

以下是07版本的POM引用 以及测试代码

<dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.10-FINAL</version>
          </dependency>
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml-schemas</artifactId>
              <version>3.10-FINAL</version>
         </dependency>

以下是测试代码：
入参 ：20000， 20 结果： 3S
200000， 20 结果：17S

public static void SXSSFWorkbook(int rows, int col) throws Exception {
        long start = System.currentTimeMillis();
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
        Sheet first = sxssfWorkbook.createSheet("sheet1");
        for (int i = 0; i < rows; i++) {
            Row row = first.createRow(i);
            for (int j = 0; j < col; j++) {
                if(i == 0) {
                    // 首行
                    row.createCell(j).setCellValue("column" + j);
                } else {
                    // 数据
                    if (j == 0) {
                        CellUtil.createCell(row, j, String.valueOf(i));
                    } else
                        CellUtil.createCell(row, j, String.valueOf(Math.random()));
                }
            }
        }
        FileOutputStream out = new FileOutputStream("E:/SXSSFWorkbookworkbook.xlsx");
        sxssfWorkbook.write(out);
        out.close();
        System.out.println(("SXSSFWorkbook "+(System.currentTimeMillis()-start)/1000));
    }

再次回到POI的官网。http://poi.apache.org/spreadsheet/index.html

官方提到自POI3.8版本开始提供了一种SXSSF的方式，用于超大数据量的操作。于是…

原文：

SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced…
　　
　　笔者能力有限。暂时只是使用POI类库实现了相对高效的批量写入。假如有更好的类库或者是方法的朋友。欢迎留言分享。多谢指点！