写一个大的结果集使用POI Excel文件 [英] Writing a large resultset to an Excel file using POI

问题描述

这是那种内嵌W / 写了大量的ResultSet到文件，但有问题的文件是一个Excel文件。

This is sort of inline w/ Writing a large ResultSet to a File but the file in question is an Excel file.

我使用Apache POI库来写一个Excel从ResultSet对象检索的大型数据集文件。这些数据可以从几千元记录约100万;不知道如何转换成Excel格式文件系统的字节。

I'm using the Apache POI library to write an Excel file with a large data set retrieved from a ResultSet object. The data could range from a few thousand records to about 1 million; not sure how this translates into file system bytes in Excel format.

下面是一个测试code我写检查出写这样一个大的结果集的时间，也表现暗示w.r.t CPU和放大器;内存。

The following is a test code I wrote to check out the time taken to write such a large result set and also the performance implication w.r.t CPU & Memory.

protected void writeResultsetToExcelFile(ResultSet rs, int numSheets, String fileNameAndPath) throws Exception {

    BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(fileNameAndPath));
    int numColumns = rs.getMetaData().getColumnCount();

    Workbook wb = ExcelFileUtil.createExcelWorkBook(true, numSheets);
    Row heading = wb.getSheetAt(0).createRow(1);

    ResultSetMetaData rsmd = rs.getMetaData();

    for(int x = 0; x < numColumns; x++) {
        Cell cell = heading.createCell(x+1);
        cell.setCellValue(rsmd.getColumnLabel(x+1));
    }

    int rowNumber = 2;
    int sheetNumber = 0;

    while(rs.next()) {

        if(rowNumber == 65001) {
            log("Sheet " + sheetNumber + "written; moving onto to sheet " + (sheetNumber + 1));
            sheetNumber++;
            rowNumber = 2;
        }

        Row row = wb.getSheetAt(sheetNumber).createRow(rowNumber);
        for(int y = 0; y < numColumns; y++) {
            row.createCell(y+1).setCellValue(rs.getString(y+1));
            wb.write(bos);
        }

        rowNumber++;
    }

    //wb.write(bos);

    bos.close();
}

没有多少运气上述code。这是创建的文件似乎迅速增长（〜每秒70MB）。因此，我停止执行后约10分钟（杀死当文件达到7GB的JVM），并试图打开该文件在Excel 2007年我打开它的那一刻，文件大小变得8K（！），只有头和第一行创建。不知道我在这里失踪。

Not much luck with the above code. The file which is created seems to grow rapidly (~70Mb per sec). So I stopped the execution after about 10 minutes (killed the JVM when the file reaches 7Gb) and tried to open the file in Excel 2007. The moment I open it, the file size becomes 8k(!) and only the header and the first row are created. Not sure what I'm missing here.

任何想法？

推荐答案

哦。我想你写出来的工作簿944000倍。您wb.write（BOS）的调用是在内部循环。我不知道这是在工作簿类的语义相当一致？从我可以在这个类的Javadoc中说，该方法写出的全部的工作簿指定的输出流。而且它会写出您添加至今一次的每一行每一行的东西长。

Oh. I think you're writing the workbook out 944,000 times. Your wb.write(bos) call is in the inner loop. I'm not sure this is quite consistent with the semantics of the Workbook class? From what I can tell in the Javadocs of that class, that method writes out the entire workbook to the output stream specified. And it's gonna write out every row you've added so far once for every row as the thing grows.

这就是为什么你看到的正是1行了。第一个工作簿（有一个行）写入到该文件是所有正在显示 - 然后7GB垃圾此后

This explains why you're seeing exactly 1 row, too. The first workbook (with one row) to be written out to the file is all that is being displayed - and then 7GB of junk thereafter.

这篇关于写一个大的结果集使用POI Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！