2

I am using Apache POI to read excel file. My file is about 100MB, and i got this java.lang.OutOfMemoryError: Java heap while loading workbook

XSSFWorkbook workbook = new XSSFWorkbook(excelFilePath);

My computer RAM is 8GB so its can't afford to use -Xmx too large. So my question is how can i handle this error?

EDIT

Samples of my data (My data is about 1087490 rows):

enter image description here

Improve this question
5
  • 2
    For example by doing some experimenting? We can't tell you which number for -Xmx will work for your machine and your workload. You on the other hand can easily test that yourself?! Commented Dec 19, 2017 at 9:12
  • What data makes your excel file 100 mb, is it text or image or what..be specific Commented Dec 19, 2017 at 9:13
  • I already experimented it. its look like my machine cannot afford this XSSFWorkbook but is there any other library or ways to make my machine can read 100MB excel file? Commented Dec 19, 2017 at 9:14
  • What does jvisualvm say about this? Commented Dec 19, 2017 at 9:19
  • In the long past I used a JDBC-ODBC bridge too (one needs to create an ODBC data source). It no longer is supported for java 8. There might be some alternative, or you would need to import the data with java 7, say into an embedded database like H2. Commented Dec 19, 2017 at 10:18

1 Answer 1

Reset to default
5

First of all - you have to make sure that your files are within the well defined limitations of the tool - see here.

Beyond that, it seems to be a pretty common problem to run out of memory for larger files, and one solution is to process XML data "manually":

If memory footprint is an issue, then for XSSF, you can get at the underlying XML data, and process it yourself. This is intended for intermediate developers who are willing to learn a little bit of low level structure of .xlsx files, and who are happy processing XML in java. Its relatively simple to use, but requires a basic understanding of the file structure. The advantage provided is that you can read a XLSX file with a relatively small memory footprint.

( quoted from here )

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

5 Comments

Also good to mention in this case are the default limitations
My data is about 1087490 records (rows). i will try test this solution
The SXSSF (Streaming Usermodel API) can only be used "when very large spreadsheets have to be produced". For reading huge files XSSF and SAX (Event API) is needed instead.
@AxelRichter Thanks, too ;-) ... will show my gratitude elsewhere
Sory for late reply. Thanks a lot! Its working! but little bit slow, maybe because too many data

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.