如何使用Apache POI加载大型xlsx文件？

Question

我有一个大的。xlsx文件（141 MB，包含293413行，每行62列），我需要在其中执行一些操作。

我在加载此文件时遇到问题（OutOfMemoryError），因为POI在XSSF（xlsx）工作簿上占用了大量内存。

这个SO问题与此类似，提出的解决方案是增加VM的已分配/最大内存。

它似乎适用于这种文件大小（9MB），但对我来说，即使分配所有可用的系统内存，它也根本不起作用。（嗯，考虑到文件的大小是原来的15倍以上，这也就不足为奇了）

我想知道是否有任何方法可以加载工作簿，使其不会消耗所有内存，而且不需要对XSSF的底层XML进行基于处理的加载。（换句话说，保持清教徒POI解决方案）

如果没有困难，欢迎您说出（“没有”），并为我指出“XML”解决方案的方法。




Answer 1

Apache POI、HSSF和XSSF中的Excel支持支持3种不同的模式。

一种是完整的、类似DOM的内存“UserModel”，它支持读写。使用通用的SS（电子表格）接口，您可以基本透明地为HSSF（.xls）和XSSF（.xlsx）编码。然而，它需要大量内存。

POI还支持流式只读方式来处理文件，即EventModel。这比UserModel低得多，并且使您非常接近文件格式。对于HSSF（.xls），您可以获得一个记录流，还可以获得一些处理这些记录的帮助（缺少单元格、格式跟踪等）。对于XSSF（.xlsx），您可以从文件的不同部分获得SAX事件流，这有助于获得文件的正确部分，还可以轻松处理文件的常见但很小的位。

仅对于XSSF（.xlsx），POI还支持只写流式写入，适合低级别但低内存的写入。不过，它基本上只支持新文件（某些类型的附加是可能的）。没有等价的HSSF，而且由于在许多记录中来回的字节偏移量和索引偏移量，所以很难做到。。。

对于您的具体情况，正如您在澄清性评论中所描述的，我认为您应该使用XSSF EventModel代码。请参阅POI文档以开始，然后尝试查看POI和Tika中使用它的这三个类以了解更多详细信息。

Answer 2

通过使用文件而不是流，可以提高内存使用率。（最好使用流式API，但流式API有局限性，请参阅http://poi.apache.org/spreadsheet/index.html)

因此

Workbook workbook = WorkbookFactory.create(inputStream);

做

Workbook workbook = WorkbookFactory.create(new File("yourfile.xlsx"));

这是根据：http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

文件与InputStreams

“打开工作簿时，无论是. xls HSSFWorkbook还是. xlsx XSSFWorkbook，都可以从文件或InputStream加载工作簿。使用File对象可以降低内存消耗，而InputStream需要更多内存，因为它必须缓冲整个文件。”

Answer 3

我在Web服务器环境中也遇到了类似的情况。上载的典型大小约为150k行，如果从一个请求中消耗大量内存，那就不太好了。Apache POI流API可以很好地实现这一点，但它需要完全重新设计读取逻辑。我已经有一堆使用标准API的读取逻辑，我不想重做，所以我写了以下内容：https://github.com/monitorjbl/excel-streaming-reader

它不完全是标准XSSFWorkbook类的替代品，但如果您只是遍历行，它的行为类似：

import com.monitorjbl.xlsx.StreamingReader;

InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx"));
StreamingReader reader = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .sheetIndex(0)        // index of sheet to use (defaults to 0)
        .read(is);            // InputStream or File for XLSX file (required)

for (Row r : reader) {
  for (Cell c : r) {
    System.out.println(c.getStringCellValue());
  }
}     

使用它有一些警告；由于XLSX工作表的结构方式，并非所有数据都可以在流的当前窗口中使用。但是，如果您只是尝试从单元格中读取简单数据，它可以很好地工作。