CODEWITHSUNDEEP
javaexcelmemoryapache-poi
Lucia Belardinelli
Lucia Belardinelli

Reputation: 737

Attempt to write an excel file with Apache POI causing OutOfMemoryError

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..

Upvotes: 5

Views: 8283

Answers (3)

Praveenupadrasta
Praveenupadrasta

Reputation: 51

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

Upvotes: 1

Turismo
Turismo

Reputation: 2134

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.

Upvotes: 15

HJW
HJW

Reputation: 23443

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.

Upvotes: 0

Related Questions