尝试使用Apache POI写入Excel文件，导致OutOfMemoryError [英] Attempt to write an excel file with Apache POI causing OutOfMemoryError

问题描述

我有一个写入Excel文件的程序. 它使用Apache POI编写excel 2007文件(我有256个以上的列，因此我必须使用它).该程序有效.我已经在很小的文件上对其进行了测试，但是如果我使用更多的行，则会耗尽内存.

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.

这是堆栈跟踪:

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)

在我写"workbook.write(fileOut)"的行中发生了错误(根据stacktrace)，其中fileOut是FileOutputStream.这意味着显然所有Java对象都有足够的内存来存储excel文件，但是由于某些原因，由于要写入硬盘，它必须占用更多的内存.

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.

仅告诉您，我试图将Java堆的大小增加到1个千兆字节(通过添加-Xms128m -Xmx1024m)，但这似乎还是不符合标准.

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.

帮助！哦

代码示例:

..

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){

    }

}
}

我希望它是可读的..但是我正在扫描每一行，翻译每列的值列，将它们放在单元格中……那部分还可以..我用systems.out.println ^^进行了测试. 但是最后一行说翻译完成，开始写"之后，就会发生错误.

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

推荐答案

使用POI编写.xlsx文件会占用大量内存. 1个演出可能还不够.

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

最近，Apache POI引入了新的API( SXSSF )，它是用于写入.xlsx文件的流式实现.我还没有使用过它，但是也许您可以研究一下.

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.

这篇关于尝试使用Apache POI写入Excel文件，导致OutOfMemoryError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！