Java 用于阅读的 Apache POI 流 (SXSSF)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33786219/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apache POI Streaming (SXSSF) for Reading
提问by bruno_cw
I need to read large excel files and import their data to my application.
我需要读取大型 excel 文件并将它们的数据导入我的应用程序。
Since POI takes up a large amount of heap to work, often throwing OutOfMemoryerrors, I found out that there is a StreamingAPI for handling excel data in a serial fashion (rather than loading the file completely into memory)
由于POI占用大量堆工作，经常抛出OutOfMemory错误，我发现有一个StreamingAPI用于以串行方式处理excel数据（而不是将文件完全加载到内存中）
I created a xlsxworkbook, with a single worksheet, and typed in several values in cells and came up with the following code to attempt reading it:
我创建了一个xlsx工作簿，只有一个工作表，并在单元格中输入了几个值，并想出了以下代码来尝试阅读它：
public static void main(String[] args) throws Throwable {
// keep 100 rows in memory, exceeding rows will be flushed to disk
SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(new FileInputStream("C:\test\tst.xlsx")));
SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);
Row row = sheet.getRow(0);
//row is always null
while(row.iterator().hasNext()){ //-> NullPointerException
System.out.println(row.getCell(0).getStringCellValue());
}
}
However, despite being able to get its worksheets properly, it always comes with empty (null) rows.
但是，尽管能够正确获取其工作表，但它始终带有空 ( null) 行。
I have researched and found out several examples of the Streaming API in the internet, but none of them are about readingexisting files, they're all about generating excel files.
我在互联网上研究并发现了几个 Streaming API 的例子，但它们都不是关于读取现有文件，它们都是关于生成 excel 文件。
Is it actually possible to read data from existing .xlsxfiles in a stream?
实际上是否可以从.xlsx流中的现有文件中读取数据？
采纳答案by bruno_cw
After digging up some more, I found out this library:
在挖掘了更多之后，我发现了这个库：
If you've used Apache POI in the past to read in Excel files, you probably noticed that it's not very memory efficient. Reading in an entire workbook will cause a severe memory usage spike, which can wreak havoc on a server.
There are plenty of good reasons for why Apache has to read in the whole workbook, but most of them have to do with the fact that the library allows you to read and write with random addresses. If (and only if) you just want to read the contents of an Excel file in a fast and memory effecient way, you probably don't need this ability. Unfortunately, the only thing in the POI library for reading a streaming workbook requires your code to use a SAX-like parser. All of the friendly classes like Row and Cell are missing from that API.
This library serves as a wrapper around that streaming API while preserving the syntax of the standard POI API. Read on to see if it's right for you.
如果您过去曾使用 Apache POI 读取 Excel 文件，您可能会注意到它的内存效率不是很高。阅读整个工作簿会导致严重的内存使用量激增，这会对服务器造成严重破坏。
Apache 必须读取整个工作簿的原因有很多，但其中大多数都与库允许您使用随机地址进行读写有关。如果（且仅当）您只想以快速且内存有效的方式读取 Excel 文件的内容，您可能不需要此功能。不幸的是，POI 库中唯一用于读取流式工作簿的内容需要您的代码使用类似 SAX 的解析器。该 API 中缺少所有友好类，如 Row 和 Cell。
该库充当该流 API 的包装器，同时保留标准 POI API 的语法。请继续阅读，看看它是否适合您。
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)
.sheetName("sheet1") // name of sheet to use (overrides sheetIndex)
.read(is); // InputStream or File for XLSX file (required)
There is also SAX Event API, which reads the document and parse its contents through events.
还有SAX Event API，它通过事件读取文档并解析其内容。
If memory footprint is an issue, then for XSSF, you can get at the underlying XML data, and process it yourself. This is intended for intermediate developers who are willing to learn a little bit of low level structure of .xlsx files, and who are happy processing XML in java. Its relatively simple to use, but requires a basic understanding of the file structure. The advantage provided is that you can read a XLSX file with a relatively small memory footprint.
如果内存占用是一个问题，那么对于 XSSF，您可以获取底层 XML 数据并自行处理。这适用于愿意学习一些 .xlsx 文件的低级结构并乐于用 Java 处理 XML 的中级开发人员。它使用起来相对简单，但需要对文件结构有基本的了解。提供的优点是您可以读取占用内存相对较小的 XLSX 文件。
