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

问题描述

我有一个大型的.xlsx excel表，具有40万行.我想在这个现有的工作簿中进行读写.

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

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

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);

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

Second line of this code takes RAM upto 5gb.

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

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

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

现在，当我使用不带任何参数的构造函数实例化SXSSF工作簿时，它将创建新的工作簿，并且不会持久保存工作簿的现有数据. SXSSF工作簿的其他构造函数以XSSF工作簿的实例为例.问题开始出现在这里.当我为我的excel文件制作XSSF工作簿实例时，RAM变高并且抛出了OUTOFMEMORY异常.

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.

有什么方法可以对现有的40万行以上的大型excel工作簿进行读写操作.

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

推荐答案

查看" POI 的概述"页面.它具有此表:

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

电子表格API功能摘要

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

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

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

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

因此，要修改文件并进行流传输，以免占用大量内存，则需要使用一个API进行读取，并使用另一个API编写新文件.

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屋！