OutOfMemoryError: Java heap for loading an 8 MB file

Question

I am using Grails 2.4.4 and trying to download the .xlsx file using the apache poi plugin, but I get a JAVA Heap size exception when the file size is about 8 MB.

My controller has the following actions and methods: -

def uploadForm() { String fileName = "D:\\File.xlsx" Map excelSheetMap = process(fileName) } Map process(String fileName) { ExcelBuilder excelBuilder = new ExcelBuilder(fileName) //Getting JAVA Heap Size exception here when I am trying to create an object //of ExcelBuilder with the file } 

The ExcelBuilder.groovy class file looks something like this:

 class ExcelBuilder { Workbook workbook ExcelBuilder(String fileName) { new File(fileName).withInputStream { is -> workbook = new XSSFWorkbook(is) } } } 

I also tried using the grails-excel-import plugin, but I get the same exception.

Can anyone suggest how to import large Excel files into grails. Thanks in advance.

Answer 1

Poi really has a lot of memory. Please see: http://poi.apache.org/spreadsheet/index.html

You can try SXSSF. SXSSF is an API-compatible XSSF streaming extension that should be used when very large spreadsheets are to be created and space is limited.

Answer 2

The problem with this “XSSF API” is that when I created the root object (workbook) for the excel sheet, the empty space is filled when the object was created, and there is not a lot of space to create the workbook object. Thus, sheet processing after heap size processing is less possible because the excel sheet can be much larger.

 new File(fileName).withInputStream { is -> workbook = new XSSFWorkbook(is) //Getting JAVA Heap Size exception here when I am trying to create an object } 

So, after some R&D, I found another API for processing a large excel sheet, that is, "XSSF and SAX (Event API)". But for this you can get the basic XML data and process it yourself.

Here you can find the full documentation - https://poi.apache.org/spreadsheet/how-to.html

thanks