Attempt to write an excel file with Apache POI causing OutOfMemoryError

Question

I have a program which writes an excel file. It uses Apache POI to write excel 2007 files (I have more than 256 colums so I have to use it). The program works. I've tested it out on very small files but if I use more rows it runs out of memory.

Here's the stack trace :

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at java.util.Arrays.copyOf(Unknown Source)
    at java.io.ByteArrayOutputStream.write(Unknown Source)
    at org.apache.poi.openxml4j.opc.internal.MemoryPackagePartOutputStream.write(MemoryPackagePartOutputStream.java:88)
    at org.apache.xmlbeans.impl.store.Cursor._save(Cursor.java:590)
    at org.apache.xmlbeans.impl.store.Cursor.save(Cursor.java:2544)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.save(XmlObjectBase.java:212)
    at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:2480)
    at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:2439)
    at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:196)
    at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:200)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:204)
    at model.Conversione.traduzioneFile(Conversione.java:219)
    at model.Main.scriviFile(Main.java:75)
    at model.Main.main(Main.java:51)

The error occurs (according to the stacktrace) at the line where I write "workbook.write(fileOut)" where fileOut is a FileOutputStream. That implies that there's obviously enough memory for ALL of the java objects to store the excel file, but for some reason as it's writing to the hard disk it must grab a whole lot more memory.

Just to tell you, I have attempted to increase the java heap size up until 1 gig (by adding -Xms128m -Xmx1024m) but that still doesn't seem to do the tric.

Help! O.o

EXAMPLE OF CODE:

..

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//I'M USING A DATABASE 
import DAO.EventoDAO;
import DAO.ParametroDAO;

public class Conversion {

public static void traduzioneFile(File read, File write){
    FileOutputStream fos=null;


    try {
        fos = new FileOutputStream(write);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    if (fos!=null) {

        try{

            Workbook wb = new XSSFWorkbook() ;

            Sheet sheet = wb.createSheet();

            //I'm reading from a table in a .txt file , converting values, and putting them in a table..

            FileInputStream fis;
            try {
                fis = new FileInputStream(fileLettura);
                InputStreamReader isr=new InputStreamReader(fis);
                BufferedReader br=new BufferedReader(isr);
                String line=br.readLine();

                //here there are some variables
                while(line!=null) {

                    Row row = null;
                    row=sheet.createRow((short)row_number);


                                            //arrayLinea contains all the words of the line
                    while (column_number<arrayLinea.length){
                    value=arrayLinea[column_number];
 //if value is ok i translate it and put it in a cell
                       row.createCell((short)contatoreColonne).setCellValue(value);
                                    contatoreColonne++                                  

                        }
                        //next line
                        linea=br.readLine();
                        row_line++;

                }



        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }catch (Exception ex){
            ex.printStackTrace();

        }


        wb.write(fos);
        fos.flush();
        fos.close();

    }catch (FileNotFoundException e){
    }catch (IOException e){
    }catch (Exception e){

    }

}
}

I hope it is readable.. however i'm scanning each line, translating values column per column, putting them in cells... That part is ok.. I tested it with systems.out.println ^^ but after the last line saying "translating complete, starting writing", the error occurs..

ok.. i'm editing the post showing a simpler code than the real one because it is part of a quite complicated work :D .. — Lucia Belardinelli, Dec 07 '11 at 15:02
Did you try to check the memory usage of jvm in task manager? Did you try to set the newGC (see java.sun.com/docs/hotspot/gc1.4.2/faq.html or acevedoalberto.wordpress.com/2009/01/16/jvmtuning)? And you can also try the MAT for check the existing heap for memory leaks, it can help you very much — gaffcz, Dec 07 '11 at 15:08
What version of Apache POI are you using? And if it isn't POI 3.8 beta 4 (or recently nightly build), does switching to that help? — Gagravarr, Dec 07 '11 at 22:19
Looked at http://stackoverflow.com/a/5038492/701884 this answer to a similar question here on SO? — Wivani, Dec 09 '11 at 12:58

score 14 · Answer 1 · answered Dec 21 '11 at 10:01

14

Writing .xlsx files with POI uses a lot of memory. 1 gig is probably not enough for this.

Recently Apache POI introduced a new API (SXSSF) which is a streaming implementation used for writing .xlsx files. Haven't used it myself yet, but perhaps this is something you can look into.

answered Dec 21 '11 at 10:01

Turismo

2,064
2
16
23

2

I just tried it - switched out XSSFWorkbook() with SXSSFWorkbook() and everything worked perfectly right away and very, very quickly. THANK YOU! – Kimberley Coburn Jan 09 '15 at 12:36
1

Be aware of the limitation on automatic size column computation: https://stackoverflow.com/a/49514411/458157 @Turismo Thank you :) – gouessej Mar 27 '18 at 13:38

score 1 · Answer 2 · answered Jan 27 '16 at 16:39

Try increasing the heap space.

If you are using Eclipse, then Right click your project folder choose Run as->Run Configuration->Arguments tab.

In the arguments tab, try setting these parameters,

-Xms to set initial Java heap size

-Xmx to set maximum Java heap size

-Xss to set java thread stack size

Example:

-Xms1024M -Xmx1524M

Then run the program.

I hope this should work.

Sorry for such late reply :D

score 0 · Answer 3 · answered Dec 07 '11 at 15:16

0

You should put a debug break point and trace your codes.

Somewhere there i believe is an infinite loop that is never ending and causing that OOM error.

answered Dec 07 '11 at 15:16

Oh Chin Boon

23,028
51
143
215

no ..the coding part is ok.. i've tested it several times.. but if the file contains more than a specific number of lines (number that i'll tell you in a minute, when i catch it :D) the exception come in wb.write(fos)... – Lucia Belardinelli Dec 07 '11 at 15:22
mmm.. I can't modify column size.. Each file I translate has a number of columns that I don't know but I have to handle each colums it shows ... – Lucia Belardinelli Dec 07 '11 at 16:02
Excel 2003 yes... but with XSSF you can create Excel 2007 and 2010 files ^^ – Lucia Belardinelli Dec 07 '11 at 16:29

Attempt to write an excel file with Apache POI causing OutOfMemoryError

3 Answers3

Linked