JAVA笔记-如何将百万级数据高效的导出到Excel表单

àì夳堔傛蜴生んèń 2022-02-27 10:20 246阅读 0赞

[https://www.cnblogs.com/zou90512/p/3989450.html][https_www.cnblogs.com_zou90512_p_3989450.html]

今天，一朋友问我使用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 13.2

类库需求： POI-3.10-Final

1 <dependency>
    2             <groupId>org.apache.poi</groupId>
    3             <artifactId>poi</artifactId>
    4             <version>3.10-FINAL</version>
    5         </dependency>

新建一个Maven项目。

根据笔者以往的经验，直接使用POI写了一份代码。执行的时候直接报错了。

![复制代码][copycode.gif]

1 public static void Excel2003Operate(String filePath) throws Exception {
     2         HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
     3         HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
     4         for (int i = 0; i < 10000; i++) {
     5             HSSFRow hssfRow = sheet.createRow(i);
     6             for (int j = 0; j < 10; j++) {
     7                 HSSFCellUtil.createCell(hssfRow, j, String.valueOf(Math.random()));
     8             }
     9         }
    10         FileOutputStream out = new FileOutputStream("workbook.xlsx");
    11         hssfWorkbook.write(out);
    12         out.close();
    13     }

![复制代码][copycode.gif]

1 Connected to the target VM, address: '127.0.0.1:62382', transport: 'socket'
     2 Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
     3     at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:131)
     4     at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)
     5     at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:128)
     6     at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:342)
     7     at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:323)
     8     at dev.tinyz.excel.POIUtil.Excel2003Operate(POIUtil.java:23)
     9     at dev.tinyz.excel.Main.main(Main.java:16)
    10 Disconnected from the target VM, address: '127.0.0.1:62382', transport: 'socket'

![复制代码][copycode.gif]

运行直接报错了。仔细看了报错信息之后发现。POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。

发现类库居然没有XSSF相关的类。着笔者傻眼了说。于是去POI官网查看。发现完整的POI类库包含的内容很多。于是详细了解了一下每个部分的具体作用：

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

![复制代码][copycode.gif]

1 <dependency>
     2             <groupId>org.apache.poi</groupId>
     3             <artifactId>poi-ooxml</artifactId>
     4             <version>3.10-FINAL</version>
     5         </dependency>
     6         <dependency>
     7             <groupId>org.apache.poi</groupId>
     8             <artifactId>poi-ooxml-schemas</artifactId>
     9             <version>3.10-FINAL</version>
    10         </dependency>

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

1 public static void Excel2007AboveOperateOld(String filePath) throws IOException {
     2         XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
     3         // 获取第一个表单
     4         Sheet first = workbook.getSheetAt(0);
     5         for (int i = 0; i < 100000; i++) {
     6             Row row = first.createRow(i);
     7             for (int j = 0; j < 11; j++) {
     8                 if(i == 0) {
     9                     // 首行
    10                     row.createCell(j).setCellValue("column" + j);
    11                 } else {
    12                     // 数据
    13                     if (j == 0) {
    14                         CellUtil.createCell(row, j, String.valueOf(i));
    15                     } else
    16                         CellUtil.createCell(row, j, String.valueOf(Math.random()));
    17                 }
    18             }
    19         }
    20         // 写入文件
    21         FileOutputStream out = new FileOutputStream("workbook.xlsx");
    22         workbook.write(out);
    23         out.close();
    24     }

![复制代码][copycode.gif]

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

1 Cast time : 49699

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

再次回到POI的官网。[http://poi.apache.org/spreadsheet/index.html][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...

![232124458731553.jpg][]

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

![复制代码][copycode.gif]

1 public static void Excel2007AboveOperate(String filePath) throws IOException {
     2         XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(new File(filePath)));
     3         SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
     4 //            Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
     5         Sheet first = sxssfWorkbook.getSheetAt(0);
     6         for (int i = 0; i < 100000; i++) {
     7             Row row = first.createRow(i);
     8             for (int j = 0; j < 11; j++) {
     9                 if(i == 0) {
    10                     // 首行
    11                     row.createCell(j).setCellValue("column" + j);
    12                 } else {
    13                     // 数据
    14                     if (j == 0) {
    15                         CellUtil.createCell(row, j, String.valueOf(i));
    16                     } else
    17                         CellUtil.createCell(row, j, String.valueOf(Math.random()));
    18                 }
    19             }
    20         }
    21         FileOutputStream out = new FileOutputStream("workbook.xlsx");
    22         sxssfWorkbook.write(out);
    23         out.close();
    24     }

![复制代码][copycode.gif]

多次运行测试。查看数据

1 Cast time : 11604

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

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

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

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

1 Cast time : 87782

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

晒一下成就：

![232135482643116.jpg][]

源码下载：[http://pan.baidu.com/s/1bnw9pYB][http_pan.baidu.com_s_1bnw9pYB]

笔者能力有限。暂时只是使用POI类库实现了相对高效的批量写入。假如有更好的类库或者是方法的朋友。欢迎留言分享。多谢指点。。O(∩\_∩)O哈哈~

**作者**：TinyZ  
出处：[http://www.cnblogs.com/zou90512/][http_www.cnblogs.com_zou90512]  
关于作者：努力学习，天天向上。不断探索学习，提升自身价值。记录经验分享。  
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接  
如有问题，可以通过 zou90512@126.com 联系我，非常感谢。  
笔者网店: [http://aoleitaisen.taobao.com][http_aoleitaisen.taobao.com]. 欢迎广大读者围观

[https_www.cnblogs.com_zou90512_p_3989450.html]: https://www.cnblogs.com/zou90512/p/3989450.html
[copycode.gif]: /images/20220227/9b5b26711ba44de38e5577dfc52584ab.png
[http_poi.apache.org_spreadsheet_index.html]: http://poi.apache.org/spreadsheet/index.html
[232124458731553.jpg]: /images/20220227/ef3bbb21febe47c3b27b6dd152cbd20a.png
[232135482643116.jpg]: /images/20220227/922146bd0cdc4f8b83745ea92f742bee.png
[http_pan.baidu.com_s_1bnw9pYB]: http://pan.baidu.com/s/1bnw9pYB
[http_www.cnblogs.com_zou90512]: http://www.cnblogs.com/zou90512/
[http_aoleitaisen.taobao.com]: http://aoleitaisen.taobao.com/