結(jié)論

通過 POI的SXSSFWorkbook，使用操作系統(tǒng)的臨時文件來作為緩存，可以生成超大的excel 文件（我自己測試到500W,就沒往下測了）。

記得使用壓縮。關(guān)鍵代碼

SXSSFWorkbook wb = null;
try {
    wb = new SXSSFWorkbook();
    wb.setCompressTempFiles(true); //壓縮臨時文件，很重要，否則磁盤很快就會被寫滿
    ...
} finally {
    if (wb != null) {
        wb.dispose();// 刪除臨時文件，很重要，否則磁盤可能會被寫滿
    }
}

背景

由于業(yè)務需要，最近要做一個導出超大數(shù)據(jù)的功能。之間已經(jīng)有人做過一版，由于受到POI 導出超大數(shù)據(jù)量時會出錯的影響，它把一個大文件拆成很多個小文件，然后再壓縮下載，結(jié)果經(jīng)常出現(xiàn)少一兩個文件的問題。

目標

支持單個 excel 的 sheet 導出100w 的數(shù)據(jù)

方案

導出 csv 文件

首先想到的是導出 csv 文件，最方便。但是調(diào)研后，也是最快放棄的，因為它存在兩個很嚴重的問題：

• 不同系統(tǒng)上的編碼不一樣，需要人工選擇，對于普通用戶不做好
• 沒有優(yōu)化和數(shù)據(jù)壓縮，數(shù)據(jù)量越大，csv 文件的大小比 excel 更大，當數(shù)據(jù)導出超過10w 時，csv 文件大小是 excel 的1.5倍

每行30個字段,每個字段里的內(nèi)容由 Math.random()產(chǎn)生

導出 excel 文件

大數(shù)據(jù)量的情況下，csv 的表現(xiàn)較差。只能考慮 excel. 對 excel 作了一個簡單的測試

cpu 使用率均指穩(wěn)定時的 cpu 使用率

發(fā)現(xiàn)幾個很嚴重的問題：

• 隨著數(shù)據(jù)量的增大，cpu使用率直線上升，這會給系統(tǒng)帶來很大的風險
• 當數(shù)據(jù)量超過10w 時，會出現(xiàn) OOM 異常

excel 在內(nèi)存里存儲地越來越大，研究到了瓶頸。要解決這個問題，有兩種方案：

• 先生成多個小 execel 文件，最后合并成一個大文件。查了文檔，發(fā)現(xiàn)Java 里的工具都是先讀出來，再寫到 Workbook 對象里, 這樣還是會碰到同樣的問題。如果用 excel 的工具，則運維成本過大，因此這個方案行不通
• 參考操作系統(tǒng)里的虛擬內(nèi)存，用這個來突破 機器的內(nèi)存限制。但是磁盤的性能很差，這樣做的效率很低。

這時，在 POI 的文檔里發(fā)現(xiàn)了SXSSFWorkbook，其支持使用臨時文件，可以用來生成超大 Excel 文件。

Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF.

SXSSF is an API-compatible streaming extension of XSSF to be used when very large
 spreadsheets have to be produced, and heap space is limited. SXSSF achieves its
 low memory footprint by limiting access to the rows that are within a sliding window,
 while XSSF gives access to all rows in the document. Older rows that are no longer
 in the window become inaccessible, as they are written to the disk.

In auto-flush mode the size of the access window can be specified, to hold a certain
number of rows in memory. When that value is reached, the creation of an additional
row causes the row with the lowest index to to be removed from the access window and
written to disk. Or, the window size can be set to grow dynamically; it can be trimmed
periodically by an explicit call to flushRows(int keepRows) as needed.

Due to the streaming nature of the implementation, there are the following
limitations when compared to XSSF:
 * Only a limited number of rows are accessible at a point in time.
 * Sheet.clone() is not supported.
 * Formula evaluation is not supported

以下是 SXSSFWorkbook的測試結(jié)果：

使用緩存文件導出 excel

可以看到，其在性能與資源耗用上都比較平均，至此，問題完美解決。

SXSSFWorkbook在使用上有一些注意項

• Note that SXSSF allocates temporary files that you must always clean up explicitly, by calling the dispose method.
• ?

SXSSF flushes sheet data in temporary files (a temp file per sheet) and the size
of these temporary files can grow to a very large value. For example, for a 20 MB
csv data the size of the temp xml becomes more than a gigabyte. If the size of the
 temp files is an issue, you can tell SXSSF to use gzip compression:

  SXSSFWorkbook wb = new SXSSFWorkbook();
  wb.setCompressTempFiles(true); // temp files will be gzipped

測試代碼

生成 csv

    private static void prcoessCSV(int rowsNum) throws Exception {
        try {
            long startTime = System.currentTimeMillis();
            final int NUM_OF_ROWS = rowsNum;
            final int NUM_OF_COLUMNS = 30;


            File file = new File("ooxml-scatter-chart_" + rowsNum + ".csv");
            BufferedWriter bf = new BufferedWriter(new FileWriter(file));
            StringBuffer sb = new StringBuffer();
            try {
                for (int rownum = 0; rownum < NUM_OF_ROWS; rownum++) {
                    for (int cellnum = 0; cellnum < NUM_OF_COLUMNS; cellnum++) {
                        sb.append(Math.random());
                        if ((cellnum + 1) != NUM_OF_COLUMNS) {
                            sb.append(",");
                        }
                    }
                    sb.append("\n");
                    if (rownum % 10000 == 0) {
                        bf.write(sb.toString());
                        sb = new StringBuffer();
                    }
                }
                bf.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            long endTime = System.currentTimeMillis();
            System.out.println("process " + rowsNum + " spent time:" + (endTime - startTime));

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

excel，不使用緩存

       try {
            long startTime = System.currentTimeMillis();
            final int NUM_OF_ROWS = rowsNum;
            final int NUM_OF_COLUMNS = 30;


            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("Sheet 1");
            // Create a row and put some cells in it. Rows are 0 based.
            Row row;
            Cell cell;
            for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
                row = sheet.createRow(rowIndex);
                for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
                    cell = row.createCell(colIndex);
                    cell.setCellValue(Math.random());
                }
            }

            // Write the output to a file
            FileOutputStream out = new FileOutputStream("ooxml-scatter-chart_XSSF_" + rowsNum + ".xlsx");
            wb.write(out);
            out.close();
            wb.close();

            long endTime = System.currentTimeMillis();
            System.out.println("process " + rowsNum + " spent time:" + (endTime - startTime));

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }

excel,使用緩存

       try {
            long startTime = System.currentTimeMillis();
            final int NUM_OF_ROWS = rowsNum;
            final int NUM_OF_COLUMNS = 30;

            SXSSFWorkbook wb = null;
            try {
                wb = new SXSSFWorkbook();
                wb.setCompressTempFiles(true); //壓縮臨時文件，很重要，否則磁盤很快就會被寫滿
                Sheet sh = wb.createSheet();
                int rowNum = 0;
                for (int num = 0; num < NUM_OF_ROWS; num++) {
                    if (num % 100_0000 == 0) {
                        sh = wb.createSheet("sheet " + num);
                        rowNum = 0;
                    }
                    rowNum++;
                    Row row = sh.createRow(rowNum);
                    for (int cellnum = 0; cellnum < NUM_OF_COLUMNS; cellnum++) {
                        Cell cell = row.createCell(cellnum);
                        cell.setCellValue(Math.random());
                    }
                }

                FileOutputStream out = new FileOutputStream("ooxml-scatter-chart_SXSSFW_" + rowsNum + ".xlsx");
                wb.write(out);
                out.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                if (wb != null) {
                    wb.dispose();// 刪除臨時文件，很重要，否則磁盤可能會被寫滿
                }
            }

            long endTime = System.currentTimeMillis();
            System.out.println("process " + rowsNum + " spent time:" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }