Java匯出Excel

不要亂摸發表於2018-07-06

JavaExcel

前言

眾所周知，導Excel分為兩步：

抓取資料（查資料）
寫資料到Excel檔案

這兩步都比較耗時間，一般我們從資料庫查資料，然後組裝資料，最後寫資料。

查資料不是本節的重點，主要是SQL，索引這一塊，此處不討論。本節重點是寫資料。

問題

當資料量小（比如，幾千幾萬條）的時候可以採用同步的方式，不用考慮別的。

而當資料量大的時候（比如，幾十上百萬）的時候問題就暴露出來了。

首先，慢是肯定的了。少則幾十秒，多則幾十分鐘都是有可能的。

這還是小問題，最要命的因為一個匯出把系統搞掛了。。。

筆者曾經見過，因為一個匯出，系統直接掛了，還嚴重拖慢了同一臺機器上的其它應用，最終當機了。。。

究其原因，大量資料堆積在記憶體中，可能會造成記憶體溢位。誇張一點，幾百萬條資料每條資料幾十個欄位都放到記憶體中，要等到全部寫完這些記憶體才會釋放。

方案

針對單個工作表（sheet）的行數限制，可以分多個工作表
針對單個檔案太大不容易開啟，可以分多個檔案，最終打成壓縮包
針對記憶體溢位，可以分批導，每次導一批資料，分多次導

建議

非同步下載！非同步！非同步！非同步！
如果對樣式沒什麼要求，也不用公式的話，強烈推薦匯出CSV格式
可以採用多執行緒的方式，先查總數，然後分一下看需要多少個執行緒，每個執行緒讀取一部資料並寫入單獨Excel檔案；當然，也可以多執行緒讀，單執行緒寫
分批導，這一點跟上一步類似

思路

客戶端發起下載請求以後，服務端非同步執行下載任務並生成下載檔案，客戶端讀取這個檔案下載。

那麼問題來了，客戶端怎麼知道服務端下載檔案已經生成好了呢？

有一個方案是：WebSocket

客戶端發起下載請求並收到服務端的響應以後和服務端建立一個WebSocket連線，這樣服務端生成完檔案以後就可以主動通知客戶端了。

元件

關於導Excel的元件，筆者用過以下4種：

CSV
POI
JXLS
EasyPoi

其中，POS就不用說了，CSV真的很快，不熟悉CSV的請參考《Java匯出CSV檔案》，JXLS用模板的方式也很方便，可以預先定義好樣式格式，easypoi是在poi基礎上做了封裝，使用註解就能輕鬆完成匯出。

Apache POI

HSSF與XSSF基本用法

@Test
public void testHSSF() throws Exception {
    //  建立一個工作簿
    HSSFWorkbook wb = new HSSFWorkbook();
    //  建立一個工作表
    HSSFSheet sheet = wb.createSheet();

    //  建立字型
    HSSFFont font1 = wb.createFont();
    HSSFFont font2 = wb.createFont();
    font1.setFontHeightInPoints((short) 14);
    font1.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
    font2.setFontHeightInPoints((short) 12);
    font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
    //  建立單元格樣式
    HSSFCellStyle css1 = wb.createCellStyle();
    HSSFCellStyle css2 = wb.createCellStyle();
    HSSFDataFormat df = wb.createDataFormat();
    //  設定單元格字型及格式
    css1.setFont(font1);
    css1.setDataFormat(df.getFormat("#,##0.0"));
    css2.setFont(font2);
    css2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    //  建立行
    for (int i = 0; i < 20; i++) {
        HSSFRow row = sheet.createRow(i);
        for (int j = 0; j < 10; j = j + 2) {
            HSSFCell cell = row.createCell(j);
            cell.setCellValue("Spring");
            cell.setCellStyle(css1);

            HSSFCell cell2 = row.createCell(j+1);
            cell2.setCellValue(new HSSFRichTextString("Hello! " + j));
            cell2.setCellStyle(css2);
        }
    }

    //  寫檔案
    FileOutputStream fos = new FileOutputStream("G:/wb.xls");
    wb.write(fos);
    fos.close();
}

@Test
public void testSS() throws IOException {
    Workbook[] wbs = {new HSSFWorkbook(), new XSSFWorkbook()};
    for (int i = 0; i < wbs.length; i++) {
        Workbook wb = wbs[i];
        CreationHelper creationHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet();
        for (int j = 0; j < 10; j++) {
            Row row = sheet.createRow(j);
            Cell cell = row.createCell(0);
            cell.setCellValue(creationHelper.createRichTextString("ABC"));
        }

        String filename = "G:/workbook.xls";
        if (wb instanceof XSSFWorkbook) {
            filename = filename + "x";
        }
        wb.write(new FileOutputStream(filename));
        wb.close();
    }
}

JXLS基本用法

@Test
public void abc() throws IOException {
    long t1 = System.currentTimeMillis();

    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 100000; i++) {
        userList.add(new User("zhangsan", "10001"));
    }

    InputStream is = new FileInputStream("G:/object_collection_template.xlsx");
    OutputStream os = new FileOutputStream("G:/object_collection_out.xlsx");
    Context context = new Context();
    context.putVar("users", userList);
    JxlsHelper.getInstance().processTemplate(is, os, context);

    long t2 = System.currentTimeMillis();
    System.out.println(t2 - t1);
}

SXSSF

SXSSF擴充套件自XSSF，用於當非常大的工作表要匯出且記憶體受限制的時候。SXSSF佔用很少的記憶體是因為它限制只能訪問滑動視窗中的資料，而XSSF可以訪問文件中所有資料。那些不在滑動視窗中的資料是不能訪問的，因為它們已經被寫到磁碟上了。

你可以通過new SXSSFWorkbook(int windowSize)來指定視窗的大小，也可以通過SXSSFSheet#setRandomAccessWindowSize(int windowSize)來設定每個工作表的視窗大小。

當通過createRow()建立一個新行的時候，總的行數可能會超過視窗大小，這個時候行號最低的那行會被重新整理到磁碟而且不能通過getRow()訪問。

預設的視窗大小是100。如果設定為-1，則表示不限，這就意味著沒有記錄會被自動重新整理到磁碟，除非你手動呼叫flushRow()重新整理。

注意，SXSSF會產生臨時檔案，你必須總是明確地清理它們，通過呼叫dispose方法。

/**
 * 寫一個工作表，視窗大小是100
 * 當達到101行的時候，行號為0的行（rownum=0）被重新整理到磁碟，並從記憶體中刪除
 * 當行號達到102的時候，rownum=1的行被重新整理到磁碟，並從記憶體中刪除
 * 也就是說記憶體中最多儲存100行，就是一個滑動視窗
 */
@Test
public void testWindow() throws IOException {
    //  在記憶體中儲存100行，當行數超過100時將其重新整理到磁碟
    System.out.println(Runtime.getRuntime().freeMemory());

    SXSSFWorkbook wb = new SXSSFWorkbook(100);
    SXSSFSheet sheet = wb.createSheet();
    for (int i = 0; i < 1000; i++) {
        SXSSFRow row = sheet.createRow(i);
        for (int j = 0; j < 10; j++) {
            SXSSFCell cell = row.createCell(j);
            cell.setCellValue(new CellReference(cell).formatAsString());
        }
    }

    //  行號小於900的行已經被重新整理到磁碟，無法訪問
    for (int rownum = 0; rownum < 900; rownum++) {
        Assert.assertNull(sheet.getRow(rownum));
    }

    //  最後100行仍然在記憶體中
    for (int rownum = 900; rownum < 1000; rownum++) {
        Assert.assertNotNull(sheet.getRow(rownum));
    }

    FileOutputStream fos = new FileOutputStream("G:/sxssf.xlsx");
    wb.write(fos);
    fos.close();

    //  處理工作表在磁碟上產生的臨時檔案
    wb.dispose();
}

/**
 * 關閉自動重新整理，並且手動控制哪些資料被寫到磁碟
 */
@Test
public void testAutoFlush() throws IOException {
    //  關閉自動重新整理，並且在記憶體中累積所有的行
    SXSSFWorkbook wb = new SXSSFWorkbook(-1);
    SXSSFSheet sheet = wb.createSheet();
    for (int rownum = 0; rownum < 1000; rownum++) {
        Row row = sheet.createRow(rownum);
        for (int cellnum = 0; cellnum < 10; cellnum++) {
            Cell cell = row.createCell(cellnum);
            cell.setCellValue(new CellReference(cell).formatAsString());
        }

        //  手動控制重新整理多少行到磁碟
        if (rownum % 100 == 0) {
            //  保留最後100行，其餘的重新整理到磁碟
            sheet.flushRows(100);
//                sheet.flushRows();  //  所有行，全部重新整理到磁碟
        }
    }

    FileOutputStream fos = new FileOutputStream("G:/sxssf2.xlsx");
    wb.write(fos);
    fos.close();

    //  刪除產生的臨時檔案
    wb.dispose();
}

/**
 * SXSSF重新整理工作表資料到磁碟（每個工作表一個臨時檔案），而且，臨時檔案可能會增長到非常大。
 * 例如，對於一個20M的csv資料它的臨時xml資料有可能會變得超過1G
 * 如果你任務臨時檔案的的大小是一個問題的話，那麼你可以告訴SXSSF用gzip來壓縮它。
 * SXSSFWorkbook wb = new SXSSFWorkbook();
 * wb.setCompressTempFiles(true); // temp files will be gzipped
 */

Maven依賴

<dependencies>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-csv</artifactId>
        <version>1.5</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls</artifactId>
        <version>2.4.5</version>
    </dependency>

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls-poi</artifactId>
        <version>1.0.15</version>
    </dependency>


    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
</dependencies>