excel導(dǎo)出功能如何實(shí)現(xiàn)？

前言

最近我做過(guò)一個(gè)MySQL百萬(wàn)級(jí)別數(shù)據(jù)的excel導(dǎo)出功能，已經(jīng)正常上線(xiàn)使用了。

這個(gè)功能挺有意思的，里面需要注意的細(xì)節(jié)還真不少，現(xiàn)在拿出來(lái)跟大家分享一下，希望對(duì)你會(huì)有所幫助。

原始需求：用戶(hù)在UI界面上點(diǎn)擊全部導(dǎo)出按鈕，就能導(dǎo)出所有商品數(shù)據(jù)。

咋一看，這個(gè)需求挺簡(jiǎn)單的。

但如果我告訴你，導(dǎo)出的記錄條數(shù)，可能有一百多萬(wàn)，甚至兩百萬(wàn)呢？

這時(shí)你可能會(huì)倒吸一口氣。

因?yàn)槟憧赡軙?huì)面臨如下問(wèn)題：

1. 如果同步導(dǎo)數(shù)據(jù)，接口很容易超時(shí)。
2. 如果把所有數(shù)據(jù)一次性裝載到內(nèi)存，很容易引起OOM。
3. 數(shù)據(jù)量太大sql語(yǔ)句必定很慢。
4. 相同商品編號(hào)的數(shù)據(jù)要放到一起。
5. 如果走異步，如何通知用戶(hù)導(dǎo)出結(jié)果？
6. 如果excel文件太大，目標(biāo)用戶(hù)打不開(kāi)怎么辦？

我們要如何才能解決這些問(wèn)題，實(shí)現(xiàn)一個(gè)百萬(wàn)級(jí)別的excel數(shù)據(jù)快速導(dǎo)出功能呢？

1.異步處理

做一個(gè)MySQL百萬(wàn)數(shù)據(jù)級(jí)別的excel導(dǎo)出功能，如果走接口同步導(dǎo)出，該接口肯定會(huì)非常容易超時(shí)。

因此，我們?cè)谧?code>系統(tǒng)設(shè)計(jì)的時(shí)候，第一選擇應(yīng)該是接口走異步處理。

說(shuō)起異步處理，其實(shí)有很多種，比如：使用開(kāi)啟一個(gè)線(xiàn)程，或者使用線(xiàn)程池，或者使用job，或者使用mq等。

為了防止服務(wù)重啟時(shí)數(shù)據(jù)的丟失問(wèn)題，我們大多數(shù)情況下，會(huì)使用job或者mq來(lái)實(shí)現(xiàn)異步功能。

1.1 使用job

如果使用job的話(huà)，需要增加一張執(zhí)行任務(wù)表，記錄每次的導(dǎo)出任務(wù)。

用戶(hù)點(diǎn)擊全部導(dǎo)出按鈕，會(huì)調(diào)用一個(gè)后端接口，該接口會(huì)向表中寫(xiě)入一條記錄，該記錄的狀態(tài)為：待執(zhí)行。

有個(gè)job，每隔一段時(shí)間（比如：5分鐘），掃描一次執(zhí)行任務(wù)表，查出所有狀態(tài)是待執(zhí)行的記錄。

然后遍歷這些記錄，挨個(gè)執(zhí)行。

需要注意的是：如果用job的話(huà)，要避免重復(fù)執(zhí)行的情況。比如job每隔5分鐘執(zhí)行一次，但如果數(shù)據(jù)導(dǎo)出的功能所花費(fèi)的時(shí)間超過(guò)了5分鐘，在一個(gè)job周期內(nèi)執(zhí)行不完，就會(huì)被下一個(gè)job執(zhí)行周期執(zhí)行。

所以使用job時(shí)可能會(huì)出現(xiàn)重復(fù)執(zhí)行的情況。

為了防止job重復(fù)執(zhí)行的情況，該執(zhí)行任務(wù)需要增加一個(gè)執(zhí)行中的狀態(tài)。

具體的狀態(tài)變化如下：

1. 執(zhí)行任務(wù)被剛記錄到執(zhí)行任務(wù)表，是待執(zhí)行狀態(tài)。
2. 當(dāng)job第一次執(zhí)行該執(zhí)行任務(wù)時(shí)，該記錄再數(shù)據(jù)庫(kù)中的狀態(tài)改為：執(zhí)行中。
3. 當(dāng)job跑完了，該記錄的狀態(tài)變成：完成或失敗。

這樣導(dǎo)出數(shù)據(jù)的功能，在第一個(gè)job周期內(nèi)執(zhí)行不完，在第二次job執(zhí)行時(shí)，查詢(xún)待處理狀態(tài)，并不會(huì)查詢(xún)出執(zhí)行中狀態(tài)的數(shù)據(jù)，也就是說(shuō)不會(huì)重復(fù)執(zhí)行。

此外，使用job還有一個(gè)硬傷即：它不是立馬執(zhí)行的，有一定的延遲。

如果對(duì)時(shí)間不太敏感的業(yè)務(wù)場(chǎng)景，可以考慮使用該方案。

1.2 使用mq

用戶(hù)點(diǎn)擊全部導(dǎo)出按鈕，會(huì)調(diào)用一個(gè)后端接口，該接口會(huì)向mq服務(wù)端，發(fā)送一條mq消息。

有個(gè)專(zhuān)門(mén)的mq消費(fèi)者，消費(fèi)該消息，然后就可以實(shí)現(xiàn)excel的數(shù)據(jù)導(dǎo)出了。

相較于job方案，使用mq方案的話(huà)，實(shí)時(shí)性更好一些。

對(duì)于mq消費(fèi)者處理失敗的情況，可以增加補(bǔ)償機(jī)制，自動(dòng)發(fā)起重試。

RocketMQ自帶了失敗重試功能，如果失敗次數(shù)超過(guò)了一定的閥值，則會(huì)將該消息自動(dòng)放入死信隊(duì)列。

2.使用easyexcel

我們知道在Java中解析和生成Excel，比較有名的框架有Apache POI和jxl。

但它們都存在一個(gè)嚴(yán)重的問(wèn)題就是：非常耗內(nèi)存，POI有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問(wèn)題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲(chǔ)都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。

百萬(wàn)級(jí)別的excel數(shù)據(jù)導(dǎo)出功能，如果使用傳統(tǒng)的Apache POI框架去處理，可能會(huì)消耗很大的內(nèi)存，容易引發(fā)OOM問(wèn)題。

而easyexcel重寫(xiě)了POI對(duì)07版Excel的解析，之前一個(gè)3M的excel用POI sax解析，需要100M左右內(nèi)存，如果改用easyexcel可以降低到幾M，并且再大的Excel也不會(huì)出現(xiàn)內(nèi)存溢出；03版依賴(lài)POI的sax模式，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡(jiǎn)單方便。

需要在maven的pom.xml文件中引入easyexcel的jar包：

<dependency>
    <groupId>com.alibaba<span class="hljs-name"groupId>
    <artifactId>easyexcel<span class="hljs-name"artifactId>
    <version>3.0.2<span class="hljs-name"version>
<span class="hljs-name"dependency>

之后，使用起來(lái)非常方便。

讀excel數(shù)據(jù)非常方便：

@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 這里 需要指定讀用哪個(gè)class去讀，然后讀取第一個(gè)sheet 文件流會(huì)自動(dòng)關(guān)閉
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

寫(xiě)excel數(shù)據(jù)也非常方便：

@Test
public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
    // 這里 需要指定寫(xiě)用哪個(gè)class去讀，然后寫(xiě)到第一個(gè)sheet，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
    // 如果這里想使用03 則 傳入excelType參數(shù)即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

easyexcel能大大減少占用內(nèi)存的主要原因是：在解析Excel時(shí)沒(méi)有將文件數(shù)據(jù)一次性全部加載到內(nèi)存中，而是從磁盤(pán)上一行行讀取數(shù)據(jù)，逐個(gè)解析。

3.分頁(yè)查詢(xún)

百萬(wàn)級(jí)別的數(shù)據(jù)，從數(shù)據(jù)庫(kù)一次性查詢(xún)出來(lái)，是一件非常耗時(shí)的工作。

即使我們可以從數(shù)據(jù)庫(kù)中一次性查詢(xún)出所有數(shù)據(jù)，沒(méi)出現(xiàn)連接超時(shí)問(wèn)題，這么多的數(shù)據(jù)全部加載到應(yīng)用服務(wù)的內(nèi)存中，也有可能會(huì)導(dǎo)致應(yīng)用服務(wù)出現(xiàn)OOM問(wèn)題。

因此，我們從數(shù)據(jù)庫(kù)中查詢(xún)數(shù)據(jù)時(shí)，有必要使用分頁(yè)查詢(xún)。比如：每頁(yè)5000條記錄，分為200頁(yè)查詢(xún)。

public Page<User> searchUser(SearchModel searchModel) {
    List<User> userList = userMapper.searchUser(searchModel);
    Page<User> pageResponse = Page.create(userList, searchModel);
    pageResponse.setTotal(userMapper.searchUserCount(searchModel));
    return pageResponse;
}

每頁(yè)大小pageSize和頁(yè)碼pageNo，是SearchModel類(lèi)中的成員變量，在創(chuàng)建searchModel對(duì)象時(shí)，可以設(shè)置設(shè)置這兩個(gè)參數(shù)。

然后在Mybatis的sql文件中，通過(guò)limit語(yǔ)句實(shí)現(xiàn)分頁(yè)功能：

limit #{pageStart}, #{pageSize}

其中的pagetStart參數(shù)，是通過(guò)pageNo和pageSize動(dòng)態(tài)計(jì)算出來(lái)的，比如：

pageStart = (pageNo - 1) * pageSize;

4.多個(gè)sheet

我們知道，excel對(duì)一個(gè)sheet存放的最大數(shù)據(jù)量，是有做限制的，一個(gè)sheet最多可以保存1048576行數(shù)據(jù)。否則在保存數(shù)據(jù)時(shí)會(huì)直接報(bào)錯(cuò)：

invalid row number (1048576) outside allowable range (0..1048575)

如果你想導(dǎo)出一百萬(wàn)以上的數(shù)據(jù)，excel的一個(gè)sheet肯定是存放不下的。

因此我們需要把數(shù)據(jù)保存到多個(gè)sheet中。

5.計(jì)算limit的起始位置

我之前說(shuō)過(guò)，我們一般是通過(guò)limit語(yǔ)句來(lái)實(shí)現(xiàn)分頁(yè)查詢(xún)功能的：

limit #{pageStart}, #{pageSize}

其中的pagetStart參數(shù)，是通過(guò)pageNo和pageSize動(dòng)態(tài)計(jì)算出來(lái)的，比如：

pageStart = (pageNo - 1) * pageSize;

如果只有一個(gè)sheet可以這么玩，但如果有多個(gè)sheet就會(huì)有問(wèn)題。因此，我們需要重新計(jì)算limit的起始位置。

例如：

ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
int totalPage = searchUserTotalPage(searchModel);

if(totalPage > 0) {
   Page<User> page = Page.create(searchModel);
   int sheet = (totalPage % maxSheetCount == 0) ? totalPage / maxSheetCount: (totalPage / maxSheetCount) + 1;
   for(int i=0;i

這樣就能實(shí)現(xiàn)分頁(yè)查詢(xún)，將數(shù)據(jù)導(dǎo)出到不同的excel的sheet當(dāng)中。

6.文件上傳到OSS

由于現(xiàn)在我們導(dǎo)出excel數(shù)據(jù)的方案改成了異步，所以沒(méi)法直接將excel文件，同步返回給用戶(hù)。

因此我們需要先將excel文件存放到一個(gè)地方，當(dāng)用戶(hù)有需要時(shí)，可以訪(fǎng)問(wèn)到。

這時(shí)，我們可以直接將文件上傳到OSS文件服務(wù)器上。

通過(guò)OSS提供的上傳接口，將excel上傳成功后，會(huì)返回文件名稱(chēng)和訪(fǎng)問(wèn)路徑。

我們可以將excel名稱(chēng)和訪(fǎng)問(wèn)路徑保存到表中，這樣的話(huà)，后面就可以直接通過(guò)瀏覽器，訪(fǎng)問(wèn)遠(yuǎn)程excel文件了。

而如果將excel文件保存到應(yīng)用服務(wù)器，可能會(huì)占用比較多的磁盤(pán)空間。

一般建議將應(yīng)用服務(wù)器和文件服務(wù)器分開(kāi)，應(yīng)用服務(wù)器需要更多的內(nèi)存資源或者CPU資源，而文件服務(wù)器需要更多的磁盤(pán)資源。

7.通過(guò)WebSocket推送通知

通過(guò)上面的功能已經(jīng)導(dǎo)出了excel文件，并且上傳到了OSS文件服務(wù)器上。

接下來(lái)的任務(wù)是要本次excel導(dǎo)出結(jié)果，成功還是失敗，通知目標(biāo)用戶(hù)。

有種做法是在頁(yè)面上提示：正在導(dǎo)出excel數(shù)據(jù)，請(qǐng)耐心等待。

然后用戶(hù)可以主動(dòng)刷新當(dāng)前頁(yè)面，獲取本地導(dǎo)出excel的結(jié)果。

但這種用戶(hù)交互功能，不太友好。

還有一種方式是通過(guò)webSocket建立長(zhǎng)連接，進(jìn)行實(shí)時(shí)通知推送。

如果你使用了SpringBoot框架，可以直接引入webSocket的相關(guān)jar包：

<dependency>
  <groupId>org.springframework.boot<span class="hljs-name"groupId>
  <artifactId>spring-boot-starter-websocket<span class="hljs-name"artifactId>
<span class="hljs-name"dependency>

使用起來(lái)挺方便的。

我們可以加一張專(zhuān)門(mén)的通知表，記錄通過(guò)webSocket推送的通知的標(biāo)題、用戶(hù)、附件地址、閱讀狀態(tài)、類(lèi)型等信息。

能更好的追溯通知記錄。

webSocket給客戶(hù)端推送一個(gè)通知之后，用戶(hù)的右上角的收件箱上，實(shí)時(shí)出現(xiàn)了一個(gè)小窗口，提示本次導(dǎo)出excel功能是成功還是失敗，并且有文件下載鏈接。

當(dāng)前通知的閱讀狀態(tài)是未讀。

用戶(hù)點(diǎn)擊該窗口，可以看到通知的詳細(xì)內(nèi)容，然后通知狀態(tài)變成已讀。

8.總條數(shù)可配置

我們?cè)谧鰧?dǎo)百萬(wàn)級(jí)數(shù)據(jù)這個(gè)需求時(shí)，是給用戶(hù)用的，也有可能是給運(yùn)營(yíng)同學(xué)用的。

其實(shí)我們應(yīng)該站在實(shí)際用戶(hù)的角度出發(fā)，去思考一下，這個(gè)需求是否合理。

用戶(hù)拿到這個(gè)百萬(wàn)級(jí)別的excel文件，到底有什么用途，在他們的電腦上能否打開(kāi)該excel文件，電腦是否會(huì)出現(xiàn)太大的卡頓了，導(dǎo)致文件使用不了。

如果該功能上線(xiàn)之后，真的發(fā)生發(fā)生這些情況，那么導(dǎo)出excel也沒(méi)有啥意義了。

因此，非常有必要把記錄的總條數(shù)，做成可配置的，可以根據(jù)用戶(hù)的實(shí)際情況調(diào)整這個(gè)配置。

比如：用戶(hù)發(fā)現(xiàn)excel中有50萬(wàn)的數(shù)據(jù)，可以正常訪(fǎng)問(wèn)和操作excel，這時(shí)候我們可以將總條數(shù)調(diào)整成500000，把多余的數(shù)據(jù)截取掉。

其實(shí)，在用戶(hù)的操作界面，增加更多的查詢(xún)條件，用戶(hù)通過(guò)修改查詢(xún)條件，多次導(dǎo)數(shù)據(jù)，可以實(shí)現(xiàn)將所有數(shù)據(jù)都導(dǎo)出的功能，這樣可能更合理一些。

此外，分頁(yè)查詢(xún)時(shí)，每頁(yè)的大小，也建議做成可配置的。

通過(guò)總條數(shù)和每頁(yè)大小，可以動(dòng)態(tài)調(diào)整記錄數(shù)量和分頁(yè)查詢(xún)次數(shù)，有助于更好滿(mǎn)足用戶(hù)的需求。

9.order by商品編號(hào)

之前的需求是要將相同商品編號(hào)的數(shù)據(jù)放到一起。

例如：

但我們做了分頁(yè)查詢(xún)的功能，沒(méi)法將數(shù)據(jù)一次性查詢(xún)出來(lái)，直接在Java內(nèi)存中分組或者排序。

因此，我們需要考慮在sql語(yǔ)句中使用order by 商品編號(hào)，先把數(shù)據(jù)排好順序，再查詢(xún)出數(shù)據(jù)，這樣就能將相同商品編號(hào)，倉(cāng)庫(kù)不同的數(shù)據(jù)放到一起。

此外，還有一種情況需要考慮一下，通過(guò)配置的總記錄數(shù)將全部數(shù)據(jù)做了截取。

但如果最后一個(gè)商品編號(hào)在最后一頁(yè)中沒(méi)有查詢(xún)完，可能會(huì)導(dǎo)致導(dǎo)出的最后一個(gè)商品的數(shù)據(jù)不完整。

因此，我們需要在程序中處理一下，將最后一個(gè)商品刪除。

但加了order by關(guān)鍵字進(jìn)行排序之后，如果查詢(xún)sql中join了很多張表，可能會(huì)導(dǎo)致查詢(xún)性能變差。

那么，該怎么辦呢？

總結(jié)

最后用兩張圖，總結(jié)一下excel異步導(dǎo)數(shù)據(jù)的流程。

如果是使用mq導(dǎo)數(shù)據(jù)：

如果是使用job導(dǎo)數(shù)據(jù)：

這兩種方式都可以，可以根據(jù)實(shí)際情況選擇使用。