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版本。所以遇到了不少问题。

poi-ooxml和poi-ooxml-schemas是poi对2007及以上版本的扩充。于是在maven依赖中增加：

 
 2             org.apache.poi
 3             poi-ooxml
 4             3.10-FINAL
 5         
 6         
 7             org.apache.poi
 8             poi-ooxml-schemas
 9             3.10-FINAL
10         

赶紧修改自己的代码。实现了支持Excel 2010版本。瞬间有种大功告成的感觉，有木有。。O(∩_∩)O哈哈~。好有成就感的说。

 public static void Excel2007AboveOperateOld(String filePath) throws IOException {
          XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
          // 获取第一个表单
          Sheet first = workbook.getSheetAt(0);
          for (int i = 0; i < 100000; i++) {
              Row row = first.createRow(i);
              for (int j = 0; j < 11; 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("workbook.xlsx");
         workbook.write(out);
         out.close();
     }

赶紧运行跑起来。第一次测试写入1w条数据。耗时8秒多点。感觉写入速度好慢，1w条8秒，100w。。我的天。这效率完全不能接受。于是测试10w，看看测试一下是不是真的写入速度过慢。测试结果让人崩溃。

测试导出10w条数据到excel耗时将近50秒。于是这种方式被暂时放弃。成就感瞬间被打落在地。

　　

　　再次回到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...

马上开动修改代码。代码如下：

public static void Excel2007AboveOperate(String filePath) throws IOException {
        XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(new File(filePath)));
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
//            Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
        Sheet first = sxssfWorkbook.getSheetAt(0);
        for (int i = 0; i < 100000; i++) {
            Row row = first.createRow(i);
            for (int j = 0; j < 11; 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("workbook.xlsx");
        sxssfWorkbook.write(out);
        out.close();
    }

多次运行测试。查看数据

1 Cast time : 11604

　　看到数据的瞬间感觉，哇塞。好给力的说。居然从将近50秒缩短带11秒。。。

　　为什么都是代码差距就这么大呢？

　　原来，SXSSF实现了一套自动刷入数据的机制。当数据数量达到一定程度时(用户可以自己设置这个限制)。像文本中刷入部分数据。这样就缓解了程序运行时候的压力。达到高效的目的。O(∩_∩)O哈哈~

　　再一次测试单表写入100w条数据。

1 Cast time : 87782

　　将近90秒就完成了100w条数据的写入。O(∩_∩)O哈哈~。   虽然看上去依旧有一点慢。但是考虑到数据量这样的耗时，想来已经是可以接受的了。100w条数据生成的Excel表单居然有136mb。打开就这个文档都花了不少时间。哈哈