0

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.

Sudhanshu mani Mishra
  • 13
  • 7
  • 1
    `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 Oct 22 '19 at 09:18
  • Also read these answers: https://stackoverflow.com/questions/46146161/apache-poi-fileinputstream-works-file-object-fails-nullpointerexception/46149469#46149469 and https://stackoverflow.com/questions/55929312/apache-poi-appending-data-to-xlsx-file-when-task-ran-twice/55937539#55937539 to decide if you should use `new XSSFWorkbook(new File("file_path"))` or `new XSSFWorkbook(new FileInputStream(new File(excelPath)))` – XtremeBaumer Oct 22 '19 at 09:24
  • Guys thank u for your valuable suggestions. Is there any other way to edit existing workbook in resource efficient way – Sudhanshu mani Mishra Oct 22 '19 at 09:35
  • 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 Oct 22 '19 at 09:56
  • @XtremeBaumer actually it 18 mb excel file – Sudhanshu mani Mishra Oct 22 '19 at 10:08
  • If you want to keep styles etc and edit the existing data, then your best bet would be to use `-Xms8g -Xmx16g`. Maybe even more RAM. Since its not clear what exactly you want to do with the excel file, its hard to suggest a proper solution – XtremeBaumer Oct 22 '19 at 10:14

1 Answers1

1

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.

Andreas
  • 154,647
  • 11
  • 152
  • 247
  • `I want to read and write in this existing workbook` sounds like he wants to edit existing content. That would require **usermodel** instead of **eventmodel** – XtremeBaumer Oct 22 '19 at 09:25
  • @XtremeBaumer OP is asking for ***streaming*** solution, because file is too large for **usermodel**. If you're streaming, you must create new file from old file, you cannot edit in-place. – Andreas Oct 22 '19 at 09:27
  • Ah, you mean to copy from the source file to a new file. That wasn't quite clear. Though copying **everything** from one workbook to another is quite some work and not necessarily possible (I tried it). In the end its probably more convenient to increase the max available RAM for the application. – XtremeBaumer Oct 22 '19 at 09:55
  • Actually the newly created workbook does not contains the data of old workbook until i give the instance of old workbook in sxssf contructor. – Sudhanshu mani Mishra Oct 22 '19 at 10:05
  • 2
    Yet you can have issues copying everything form one workbook to another. There is probably not even a need to pass the instance of the old workbook to the new `SXSSFWorkbook` – XtremeBaumer Oct 22 '19 at 10:16
  • @SudhanshuMishra did you find solution.. I need help with same can you post your answer here – ardjavatake2selenium Feb 20 '20 at 18:55