Apache POI花费大量时间来写入工作簿 [英] Apache POI is taking extremely large time to write into the Workbook
问题描述
我正在使用Apache POI创建记录并将其保存到工作簿.我几乎有 5000多个新记录要写入并保存到工作簿中.但是，在将fileOutputStream写入工作簿时，执行基本上停止并且变慢了.
I'm using Apache POI to create and save records into Workbook. I have almost 5000+ new records to be written and saved into the workbook. But at the time of writing the fileOutputStream into the workbook, the execution basically halts and slowed down.
我的意思是说，在执行此行时:
What I mean to say is, at the time of executing this line:
workbook.write(fileOutputStream);
几乎停止处理5000多个记录.我确认花了将近 1个小时(！)来写入工作簿.
it almost stops to process 5000+ records. I validated that it's taking nearly 1 hour (!) to write in the workbook.
如何改善性能并克服此缺点?请建议...
How can I improve the performance and overcome this drawback?? Please suggest...
** 注意:其余代码是与 Apache POI 相关的正常代码，它们执行得很好，没有问题，因此我没有提及所有代码.只有我被卡在上面那行.
** Note: The rest of the codes are normal Apache POI related codes and they are executing fine, no issue, hence I didnot mention all of them. Only I got stuck at the above line.
我在这里找到了一个讨论: FileOutputStream(Apachhe POI)需要花费很长时间保存
I found one discussion here: FileOutputStream (Apachhe POI) taking too long time to save
但是，它没有帮助我.我需要保存整个文件.
but, it did not help me. I need to save the whole file.
推荐答案
让我们举一个具体的例子来讨论:
Let's have a concrete example we can talk about:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.util.GregorianCalendar;
class CreateExcel100000Rows {
public static void main(String[] args) throws Exception {
System.out.println("whole program starts " + java.time.LocalDateTime.now());
try (
//Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
int rows = 100000;
if (workbook instanceof HSSFWorkbook) rows = 65536;
Object[][] data = new Object[rows][4];
data[0] = new Object[] {"Value", "Date", "Formatted value", "Formula"};
for (int i = 1; i < rows; i++) {
data[i] = new Object[] {1.23456789*i, new GregorianCalendar(2000, 0, i), 1.23456789*i, "ROUND(A" + (i+1) + ",2)"};
}
DataFormat dataFormat = workbook.createDataFormat();
CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
CellStyle numberStyle = workbook.createCellStyle();
numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));
Sheet sheet = workbook.createSheet();
sheet.setColumnWidth(0, 12*256);
sheet.setColumnWidth(1, 35*256);
sheet.setColumnWidth(2, 17*256);
sheet.setColumnWidth(3, 10*256);
for (int r = 0; r < data.length; r++) {
Row row = sheet.createRow(r);
for (int c = 0; c < data[0].length; c++) {
Cell cell = row.createCell(c);
if (r == 0) cell.setCellValue((String)data[r][c]);
if (r > 0 && c == 0) {
cell.setCellValue((Double)data[r][c]);
} else if (r > 0 && c == 1) {
cell.setCellValue((GregorianCalendar)data[r][c]);
cell.setCellStyle(dateStyle);
} else if (r > 0 && c == 2) {
cell.setCellValue((Double)data[r][c]);
cell.setCellStyle(numberStyle);
} else if (r > 0 && c == 3) {
cell.setCellFormula((String)data[r][c]);
}
}
}
System.out.println("write starts " + java.time.LocalDateTime.now());
workbook.write(fileout);
System.out.println("write ends " + java.time.LocalDateTime.now());
if (workbook instanceof SXSSFWorkbook) ((SXSSFWorkbook)workbook).dispose();
}
System.out.println("whole program ends " + java.time.LocalDateTime.now());
}
}
此代码创建了一个 HSSFWorkbook ，其第一页从第1行填充到第65,536行，在 A:D 列中具有不同类型的单元格值.
This code creates a HSSFWorkbook having the first sheet filled from row 1 to row 65,536 having different kind of cell values in columns A:D.
使用 java -Xms256M -Xmx512M ，即从256到512 MByte的堆空间，这总共需要2秒钟. HSSFWorkbook.write 不到一秒钟.
Using java -Xms256M -Xmx512M, that is heap space from 256 to 512 MByte, this takes 2 seconds in whole. HSSFWorkbook.write takes less than a second.
如果愿意
...
try (
Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
...
此代码创建了一个 XSSFWorkbook ，其第一张工作表从第1行填充到第100,000行，并且在 A:D 列中具有不同类型的单元格值.
This code creates a XSSFWorkbook having the first sheet filled from row 1 to row 100,000 having different kind of cell values in columns A:D.
使用 java -Xms256M -Xmx512M ，即从256到512 MByte的堆空间，这总共需要7秒钟. XSSFWorkbook.write 需要2秒钟.可以通过提供更多可用的堆空间来改善这一点.
Using java -Xms256M -Xmx512M, that is heap space from 256 to 512 MByte, this takes 7 seconds in whole. XSSFWorkbook.write takes 2 seconds. This can be improved by giving more available heap space.
如果愿意
...
try (
//Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
Workbook workbook = new SXSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx")
//Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls")
) {
...
此代码创建了一个 SXSSFWorkbook ，其第一张工作表从第1行填充到第100,000行，并且在 A:D 列中具有不同类型的单元格值.
This code creates a SXSSFWorkbook having the first sheet filled from row 1 to row 100,000 having different kind of cell values in columns A:D.
使用 java -Xms256M -Xmx512M ，即从256到512 MByte的堆空间，这总共需要2秒钟. SXSSFWorkbook.write 不到一秒钟.
Using java -Xms256M -Xmx512M, that is heap space from 256 to 512 MByte, this takes 2 seconds in whole. SXSSFWorkbook.write takes less than a second.
注意:使用 SXSSFWorkbook ，(((SXSSFWorkbook)workbook).dispose())对于摆脱使用的临时文件是必需的.
Note: Using SXSSFWorkbook, ((SXSSFWorkbook)workbook).dispose() is necessary to get rid of the used temporary files.
这篇关于Apache POI花费大量时间来写入工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
