尝试使用 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)"的那一行(根据堆栈跟踪)，其中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 gig(通过添加 -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屋！