如何使用 SXSSF Streaming api 编辑现有的大型 excel 文件 [英] How to Edit existing large excel file with SXSSF Streaming api

问题描述

我有一个包含 400,000 行的大型 .xlsx excel 表格.我想在现有的工作簿中读写.

当我尝试使用 Apache poi 在 java 中读取它时，使用以下代码:

FileInputStream fileInputStream = new FileInputStream(new File(excelPath));工作簿 wb = new XSSFWorkbook(fileInputStream);

此代码的第二行占用 RAM 高达 5GB.

Apache POI 提供了一个 SXSSF Streaming API 来处理大型 Excel 文件.

最后一列显示SXSSF只能写文件，不能读文件.

要读取文件、流式传输，第三列显示您需要使用 XSSF 事件模型.

因此，要修改文件、流式传输，以免使用大量内存，您需要使用一个 API 读取并使用另一个 API 写入新文件.

I have a large .xlsx excel sheet with 400,000 rows. I want to read and write in this existing workbook.

When i tried to read it in java with Apache poi, with following code:

FileInputStream fileInputStream = new FileInputStream(new File(excelPath));
Workbook wb = new XSSFWorkbook(fileInputStream);

Second line of this code takes RAM upto 5gb.

Apache POI has given a SXSSF Streaming API to handle large Excel file.

http://poi.apache.org/components/spreadsheet/how-to.html#sxssf

Now, when I instantiate SXSSF workbook with constructor without any parameter, it creates new Workbook and does not persist existing data of workbook. And other constructor of SXSSF workbook takes instance of XSSF workbook. And the problem starts arise here. When i made instance of XSSF workbook for my excel file, RAM goes high and OUTOFMEMORY exception thrown.

Is there any way to do read and write opration on existing Large excel workbook with more then 400,000 rows.

解决方案

Look at the bottom of the "Overview" page of POI. It has this table:

Spreadsheet API Feature Summary

The last column shows that SXSSF can only write file, not read them.

To read files, streaming, the third column shows that you need to use the XSSF eventmodel.

So, to modify a file, streaming, so as t not use a lot of memory, you need to read with one API and writing a new file with another API.

这篇关于如何使用 SXSSF Streaming api 编辑现有的大型 excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！