使用 POI 将大型结果集写入 Excel 文件 [英] Writing a large resultset to an Excel file using POI

问题描述

这是一种内联方式，带有 将大型结果集写入文件 但有问题的文件是 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.

以下是我编写的测试代码，用于检查编写如此大的结果集所需的时间以及对 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();
}

上面的代码运气不佳.创建的文件似乎增长迅速(每秒约 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.

有什么想法吗?

推荐答案

哦.我认为您将练习簿写了 944,000 次.您的 wb.write(bos) 调用位于内部循环中.我不确定这是否与 Workbook 类的语义非常一致?从我在该类的 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屋！