Issue with reading Excel Data using Apache POI

Question

Dears, I have a problem reading the Excel data and loading it to DB which is more than 15000 rows as it is causing OutOfMemory error. It's a Java Swing application where there is one action to load the excel and another action is there to write complete data to DB.

Is it possible to read around 3000 records every time and load it to cache and then write it to DB without causing Memory Issue instead of loading 15k records at a time ? Anyone can help me?

You would need to post an example of your code demonstrating the problem, along with any error messages you get. — Terry Carmen
– Terry Carmen, Commented Aug 15, 2018 at 18:20
You may need to consider using a streaming approach - I presume you're currently using XSSFWorkbook or HSSFWorkbook which hold all the data from the workbook in memory. See github.com/apache/poi/blob/trunk/src/examples/src/org/apache/… or github.com/monitorjbl/excel-streaming-reader — PJ Fanning
– PJ Fanning, Commented Aug 15, 2018 at 19:24
Dear Fanning, I was able to read the data using Excel Streaming Reader but it is not considering blank spaces. Suppose, If the first column is empty in an excel, It is filling up that data with the values of second columns when I read. Any idea how to fix it ? — Nithin
– Nithin, Commented Aug 16, 2018 at 12:34
Maybe you should submit a bug report to excel-streaming-reader team. If the excel-streaming-reader isn't working for you, maybe you could try using the example I linked above. — PJ Fanning
– PJ Fanning, Commented Aug 16, 2018 at 19:02

Amir · Accepted Answer · 2019-01-03 19:49:59Z

1

You can use org.apache.poi.xssf.eventusermodel.XSSFReader. This library uses SAX parser to read xlsx files, which is also very memory efficient. Second option is to use excel-streaming-reader. It contains cell information also. It also works perfectly if there are empty cells. I have done using both methods.

edited Jan 3, 2019 at 19:49

Amir

1,0001 gold badge6 silver badges15 bronze badges

answered Jan 3, 2019 at 13:22

vishal shikotara

335 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amir Over a year ago

It's good to see some positive suggestions. You can improve your answer by providing some example code.

Collectives™ on Stack Overflow

Issue with reading Excel Data using Apache POI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related