如何使用Apache POI加载大型xlsx文件？-Java 学习之路

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

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

This SO question类似，提出的解决方案是增加VM的分配/最大内存 .

它似乎适用于那种文件大小（9MB），但对我来说，即使分配所有可用的系统内存，它也只是不起作用 . （嗯，考虑到文件大于15倍，这并不奇怪）

我想知道是否有任何方法以不占用所有内存的方式加载工作簿，但是，没有基于（进入）XSSF的底层XML进行处理 . （换句话说，维护清教徒POI解决方案）

如果没有强硬的话，欢迎你说（“没有 . ”），并指出了解决“XML”问题的方法 .

7 回答

我在网络服务器环境中处于类似情况 . 上传的典型大小是〜150k行，并且它不想重做，所以我写了这个：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表的结构方式，并非所有数据都在流的当前窗口中可用 . 但是，如果您只是尝试从单元格中读取简单数据，那么它的效果非常好 .

回复于 2024-04-29T06:07:49+08:00

6
可以通过使用File而不是Stream来改进内存使用 . （最好使用流式API，但Streaming 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

Files vs InputStreams

“当打开工作簿时，无论是.xls HSSFWorkbook还是.xlsx XSSFWorkbook，都可以从File或InputStream加载工作簿 . 使用File对象可以降低内存消耗，而InputStream需要更多内存，因为它有缓冲整个文件 . “
回复于 2024-04-29T06:07:49+08:00
0

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

一个是完整的DOM-Like内存“UserModel”，它支持读写 . 使用通用的SS（SpreadSheet）接口，您可以基本透明地编写HSSF（.xls）和XSSF（.xlsx） . 但是，它需要大量的内存 .

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

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

对于您的具体情况，如您的澄清评论中所述，我认为您将要使用XSSF EventModel代码 . 请参阅the POI documentation开始使用，然后尝试查看POI和Tika中的these three classes，它们会将其用于更多详细信息 .

回复于 2024-04-29T06:07:49+08:00
11

POI现在包含这些案例的API . SXSSF http://poi.apache.org/spreadsheet/index.html它不会在内存中加载所有内容，因此它可以允许您处理此类文件 .

注意：我已经读过SXSSF作为写入API . 加载应该使用XSSF完成而无需输入流文件（以避免在内存中完全加载它）

回复于 2024-04-29T06:07:49+08:00

查看这篇文章 . 我将展示如何使用SAX解析器处理XLSX文件 .

https://stackoverflow.com/a/44969009/4587961

简而言之，我扩展了处理XLSX filez的XML结构的 org.xml.sax.helpers.DefaultHandler . t是事件解析器 - SAX .

class SheetHandler extends DefaultHandler {

    private static final String ROW_EVENT = "row";
    private static final String CELL_EVENT = "c";

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;

    private List<String> cellCache = new LinkedList<>();
    private List<String[]> rowCache = new LinkedList<>();

    private SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {
        // c => cell
        if (CELL_EVENT.equals(name)) {
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        } else if (ROW_EVENT.equals(name)) {
            if (!cellCache.isEmpty()) {
                rowCache.add(cellCache.toArray(new String[cellCache.size()]));
            }
            cellCache.clear();
        }

        // Clear contents cache
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        // v => contents of a cell
        // Output after we've seen the string contents
        if(name.equals("v")) {
            cellCache.add(lastContents);
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }

    public List<String[]> getRowCache() {
        return rowCache;
    }
}

然后我解析XML presending XLSX文件

private List<String []> processFirstSheet(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ);
    XSSFReader r = new XSSFReader(pkg);
    SharedStringsTable sst = r.getSharedStringsTable();

    SheetHandler handler = new SheetHandler(sst);
    XMLReader parser = fetchSheetParser(handler);
    Iterator<InputStream> sheetIterator = r.getSheetsData();

    if (!sheetIterator.hasNext()) {
        return Collections.emptyList();
    }

    InputStream sheetInputStream = sheetIterator.next();
    BufferedInputStream bisSheet = new BufferedInputStream(sheetInputStream);
    InputSource sheetSource = new InputSource(bisSheet);
    parser.parse(sheetSource);
    List<String []> res = handler.getRowCache();
    bisSheet.close();
    return res;
}

public XMLReader fetchSheetParser(ContentHandler handler) throws SAXException {
    XMLReader parser = new SAXParser();
    parser.setContentHandler(handler);
    return parser;
}

回复于 2024-04-29T06:07:49+08:00

45

您可以使用SXXSF而不是使用HSSF . 我可以用200000行生成excel .

回复于 2024-04-29T06:07:49+08:00

基于来自poi探索的monitorjbl的答案和测试套件，以下为多张xlsx文件工作，具有200K记录（大小> 50 MB）：

import com.monitorjbl.xlsx.StreamingReader;
. . .
try (
        InputStream is = new FileInputStream(new File("sample.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is);
) {
    DataFormatter dataFormatter = new DataFormatter();
    for (Sheet sheet : workbook) {
        System.out.println("Processing sheet: " + sheet.getSheetName());
        for (Row row : sheet) {
            for (Cell cell : row) {
                String value = dataFormatter.formatCellValue(cell);
            }
        }
    }
}

回复于 2024-04-29T06:07:49+08:00

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

7 回答

相关问题