How to Edit existing large excel file with SXSSF Streaming api

0
votes

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.

1
javaapache-poi
SXSSF is for writing only. No reading/editing at all allowed. At most you can instantiate an SXSSFWorkbook and append data, but not edit existing data. I have an application which reads and edits excel files which are up to 14MB in size. Though I need to add these command line parameters -Xms4g -Xmx12g for it to work at all. Apache poi uses very much RAM to read files – XtremeBaumer
Also read these answers: stackoverflow.com/questions/46146161/… and stackoverflow.com/questions/55929312/… to decide if you should use new XSSFWorkbook(new File("file_path")) or new XSSFWorkbook(new FileInputStream(new File(excelPath))) – XtremeBaumer
Guys thank u for your valuable suggestions. Is there any other way to edit existing workbook in resource efficient way – Sudhanshu mani Mishra
How big are your files in MB? Usually 400k rows shouldn't be a problem unless you have the max amount of columns as well – XtremeBaumer
@XtremeBaumer actually it 18 mb excel file – Sudhanshu mani Mishra

1 Answers

1
votes

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

Spreadsheet API Feature Summary table

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.