JAVA - 编写Excel文件时的Apache POI OutOfMemoryError

I am writing an Excel File using Apache POI.

我正在使用Apache POI编写Excel文件。

I want to write in it all the data of myResultSet

我想在其中写入myResultSet的所有数据

which has the fieldnames(columns) stored in the String[] fieldnames.

其中包含存储在String []字段名中的字段名(列)。

I have 70000 rows and 27 columns

我有70000行和27列

My Code:

String xlsFilename = "myXLSX.xlsx";
org.apache.poi.ss.usermodel.Workbook myWorkbook = new XSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet mySheet = myWorkbook.createSheet("myXLSX");
Row currentRow = mySheet.createRow(0);
for (int k = 0; k < fieldNames.length; k++) {
    // Add Cells Of Title Of ResultsTable In Excel File
    currentRow.createCell(k).setCellValue(fieldNames[k]);
}

for (int j = 0; j < countOfResultSetRows; j++) {
    myResultSet.next();
    currentRow = mySheet.createRow(j + 1);
    for (int k = 0; k < fieldNames.length; k++) {
        currentRow.createCell(k).setCellValue(myResultSet.getString(fieldNames[k]));
        System.out.println("Processing Row " + j);
    }
}

FileOutputStream myFileOutputStream = new FileOutputStream(xlsFilename);
myWorkbook.write(myFileOutputStream);
myFileOutputStream.close();

My problem is that while writing the rows the program is getting slower and slower.

我的问题是,在编写行时,程序变得越来越慢。

When it reaches row 3500 it stops with the Exception:

当它到达第3500行时,它会以异常停止:

Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space at java.lang.AbstractStringBuilder.(AbstractStringBuilder.java:45) at java.lang.StringBuffer.(StringBuffer.java:79)

线程“Thread-3”中的异常java.lang.OutOfMemoryError:java.lang.AbstractStringBuilder中的Java堆空间。(AbstractStringBuilder.java:45)java.lang.StringBuffer。(StringBuffer.java:79)

It seems I'm out of memory.

我好像忘记了。

How can I solve this.

我该怎么解决这个问题。

Is there a way to store my data to a temporary file every 1000 of them (for example)?

有没有办法将我的数据存储到每1000个临时文件中(例如)?

What would you suggest?

你会建议什么?

I had the same problem using jxl and never solve it either (JAVA - Out Of Memory Error while writing Excel Cells in jxl)

我有同样的问题使用jxl并且从来没有解决它(JAVA - 在jxl中编写Excel单元时出现内存不足错误)

Now I need xlsx files anyway, so I have to use POI.

现在我还需要xlsx文件,所以我必须使用POI。

5 个解决方案

#1

There seems to be an approach which creates data file in XML format first and then replacing that XML with existing template xlsx file.

似乎有一种方法首先以XML格式创建数据文件,然后用现有模板xlsx文件替换该XML。

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

this is not applicable for xls format files though.

这不适用于xls格式文件。

#2

How about allowing your app to use more memory (like -Xmx500m for 500 MB)?

允许你的应用程序使用更多内存(如-Xmx500m为500 MB)怎么样?

#3

Assign more memory to the heap when running your program:

在运行程序时为堆分配更多内存:

$ java -Xms256m -Xmx1024m NameOfYourClass

#4

I've been there more than once.

我不止一次去过那里。

Are you running this running on top of an application server?

您是否在应用程序服务器上运行此程序?

What I've done in the past as was mentioned by Pablo, is to increase the heap space, but make sure that it is being increased for the application server that you are running on.

我过去所做的就像Pablo所提到的那样,是增加堆空间,但要确保为正在运行的应用程序服务器增加堆空间。

I have also had to really optimize the code when doing this.

在执行此操作时,我还必须真正优化代码。

Since you are outputting to a .xlsx file, XML takes quite a bit of memory. Not sure if it would work for you in this situation or not, but if you can create a normal .xls do that and than convert it at the end into a .xlsx file (using Apache POI of course).

由于您输出的是.xlsx文件,因此XML需要相当多的内存。在这种情况下不确定它是否适合你,但如果你可以创建一个普通的.xls,那么最后将它转换成.xlsx文件(当然使用Apache POI)。

#5

Use SXSSFWorkbook instead of XSSFWorkbook, this is used for streaming User model Api

使用SXSSFWorkbook而不是XSSFWorkbook,这用于流式用户模型Api

Source: https://coderanch.com/t/612234/Write-Huge-Excel-file-Xlsx

Hopefully this will help you.

希望这会对你有所帮助。

#1