Web優(yōu)雅的實現大數據量Excel導出

在做系統后臺的時候，一般Excel表導出的方案是在服務器生成Excel表，返回給瀏覽器Excel文件地址，觸發(fā)下載。當要導出的Excel行數達到數萬行時，占用大量的服務器CPU或內存不說，還容易請求超時，還要改nginx配置。我就想，能否在前端瀏覽器創(chuàng)建Excel表呢？之后找到一個合適的js庫可以實現這個功能：js-xlsx。
官方github地址：https://github.com/SheetJS/js-xlsx

實現思路

1、 瀏覽器創(chuàng)建xlsx
2、 每次ajax向后端請求若干行的數據，直到請求完成
3、 觸發(fā)下載

本文省略php,web服務器等環(huán)境搭建步驟。看懂接下來代碼實現，需要有一些js,html,PHP,ajax的一些相關知識。

1、js-xlsx庫安裝使用

本文使用npm安裝js-xlsx,如果不用npm，可以直接去https://github.com/SheetJS/js-xlsx/releases下載壓縮包(很卡,我的網下不動)。

創(chuàng)建項目目錄后，執(zhí)行以下命令，下載js-xlsx包：

npm init
npm install xlsx

創(chuàng)建index.html在header中引入

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>demo</title>
    <script src="node_modules/xlsx/jszip.js"></script>
    <script src="node_modules/xlsx/xlsx.js"></script>
</head>

2、創(chuàng)建html頁面元素

index.html

<body>
    
<body>
<!--    進度顯示-->
    <span>導出進度:</span><span id="progressText">0%</span>
    <br>
<!--    開始導出按鈕-->
    <input type="button" id="startBtn" value="開始導出">
</body>
</body>

瀏覽器顯示：


image.png

3、添加js代碼，初始化excel工作簿全局變量/excel sheet全局變量，綁定按鈕點擊事件

<script>
    //每次請求多少行數據
    const ROWS_PER_REQ = 1000;
    //總行數
    let totalRows = 0;
    //excel工作簿全局變量
    let workBook = null;
    //excel sheet全局變量
    let workSheet = null;

    //開始導出按鈕綁定事件
    document.getElementById("startBtn").addEventListener('click',()=>{
        //初始化
        initWorkBook();
        console.log("導出開始!");
        getTotal();
    });

    function initWorkBook(){
        //創(chuàng)建excel工作簿對象
        workBook = XLSX.utils.book_new();
        //創(chuàng)建excel第一行，頭部
        let headArr = [
            ['第一列', '第二列', '第三列', '第四列', '第五列', '時間'],
        ];
        //根據頭部數組創(chuàng)建excel sheet
        workSheet = XLSX.utils.aoa_to_sheet(headArr);
        //把excel sheet添加到工作簿
        XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
    }

getTotal()函數見下章。
這里的js-xlsx庫的幾個用法解釋:

XLSX.utils.book_new(); 創(chuàng)建并返回一個excel工作簿對象，可以理解為excel文件

XLSX.utils.aoa_to_sheet(headArr); 接受一個二維數組作為參數，創(chuàng)建一個excel sheet對象(一個excel標簽頁)

XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
把excel sheet對象添加到工作簿對象,并命名為'sheet1'

4、發(fā)送Ajax請求從服務器獲取數據總行數

index.html:

    //發(fā)送Ajax請求從服務器獲取數據總行數
    function getTotal() {
       let xhr = new XMLHttpRequest();
       xhr.open('GET', 'get_data.php?getTotal=true');
       xhr.responseType = "json";
       xhr.onload = function(){
           //接收到總行數
           totalRows = xhr.response.total;
           console.log("總行數:",totalRows);

           //開始接收數據
           getRowsFromServer(0, ROWS_PER_REQ);
       };

       xhr.send();
    }

獲取到總行數后，保存到全局變量totalRows, getRowsFromServer函數見下章

后端get_data.php:

<?php

//設置返回json頭
header('Content-type: application/json');
//總行數
const TOTAL_ROW = 50000;

//獲取總行數請求
if(isset($_GET['getTotal'])){
    //此處一般情況是從數據庫獲取總行數，這里為了簡化直接返回
    $json = json_encode([
        'total'=>TOTAL_ROW,
    ]);
    echo $json;
    exit;
}

5、使用遞歸依次獲取數據

index.html

   /**發(fā)送Ajax請求分批從服務器拿數據
    *
    * @param offset 從第幾行數據開始
    * @param limit 每次取出多少行
    */
    function getRowsFromServer(offset, limit) {

        let xhr = new XMLHttpRequest();
        //拼接get字段
        xhr.open('GET', `get_data.php?offset=${offset}&limit=${limit}`);
        xhr.responseType = "json";

        //接收到數據
        xhr.onload = function(){
            /*格式:
            * [
            *    [第一列數據，第二列數據，第三列數據,.....],
            *    [第一列數據，第二列數據，第三列數據,.....],
            *    .......
            * ]
            * */
            //寫入到excel sheet
            XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1});
            //寫入excel后釋放內存
            xhr.response = null;

            let hasGetNum = offset + limit; //當前已取到的行數
            console.log(`已寫入${hasGetNum}行`);
            //更新進度顯示
            updateProgress(hasGetNum, totalRows);
            //如果沒取完,遞歸獲取下一批數據
            if(hasGetNum < totalRows){
                //剩余沒傳行數
                let rest = totalRows - hasGetNum;
                //計算下一批數據的數量
                let nextLimit = rest > ROWS_PER_REQ ? ROWS_PER_REQ : rest;
                //獲取下一批數據
                getRowsFromServer(hasGetNum, nextLimit);
            }else{
                //收取完成，下載excel
                XLSX.writeFile(workBook, 'export.xlsx');
                //總行數制0
                totalRows = 0;
            }
        };
        xhr.send();
    }

    /**更新進度顯示
     *
     * @param now   當前已更新行數
     * @param total 總行數
     */
    function updateProgress(now, total){
        //計算百分比
        let percent = (now/total * 100).toFixed(2);
        //更新dom
        document.getElementById("progressText").innerHTML = percent;
    }

每次獲取完數據使用updateProgress更新進度
這里多了幾個js-xlsx庫的用法:

XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1})
作用：把數據添加到excel sheet
第一個參數：要添加的excel sheet 實例
第二個參數：數據二維數組(本例是從服務器返回)
第三個參數：從哪里插入數據，{origin:-1}代表從最后一行的下一行開始新增數據，其他用法本例用不到，想進一步了解可以參考官網

XLSX.writeFile(workBook, 'export.xlsx')
作用：生成一個excel文件，并觸發(fā)瀏覽器下載
第一個參數：excel工作簿實例
第二個參數：下載的文件名，文件格式會根據文件后綴自動識別，本例是xlsx， 其他格式本例用不到，想進一步了解可以參考官網

后端發(fā)送數據的邏輯get_data.php:

//獲取數據請求
$offset = intval($_GET['offset']);
$limit = intval($_GET['limit']);
//返回
echo json_encode(getData($offset, $limit));

//從數據庫獲取數據
function getData($offset, $limit)
{
    //此處一般情況是從數據庫獲取數據，這里為了簡化直接生成數據
    $data = [];
    for ($i = 0; $i < $limit; $i++) {
        $lineNum = $offset + $i + 1; //行號
        $row = ["第${lineNum}行第1列數據", "第${lineNum}行第2列數據", "第${lineNum}行第3列數據", "第${lineNum}行第4列數據", "第${lineNum}行第5列數據", date('Y-m-d H:i:s')];
        $data[] = $row;
    }
    usleep(300000);//模擬耗時操作,暫停300ms
    return $data;
}

本demo完整的前后端代碼：

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>demo</title>
    <script src="node_modules/xlsx/jszip.js"></script>
    <script src="node_modules/xlsx/xlsx.js"></script>
</head>
<body>
<!--    進度顯示-->
    <span>導出進度:</span><span id="progressText">0%</span>
    <br>
<!--    開始導出按鈕-->
    <input type="button" id="startBtn" value="開始導出">
</body>
<script>

    //每次請求多少行數據
    const ROWS_PER_REQ = 1000;
    //總行數
    let totalRows = 0;
    //excel工作簿全局變量
    let workBook = null;
    //excel sheet全局變量
    let workSheet = null;

    //開始導出按鈕綁定事件
    document.getElementById("startBtn").addEventListener('click',()=>{
        //初始化
        initWorkBook();
        console.log("導出開始!");
        getTotal();
    });

    function initWorkBook(){
        //創(chuàng)建excel工作簿對象
        workBook = XLSX.utils.book_new();
        //創(chuàng)建excel第一行，頭部
        let headArr = [
            ['第一列', '第二列', '第三列', '第四列', '第五列', '時間'],
        ];
        //根據頭部數組創(chuàng)建excel sheet
        workSheet = XLSX.utils.aoa_to_sheet(headArr);
        //把excel sheet添加到工作簿
        XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
    }

    //發(fā)送Ajax請求從服務器獲取數據總行數
    function getTotal() {
       let xhr = new XMLHttpRequest();
       xhr.open('GET', 'get_data.php?getTotal=true');
       xhr.responseType = "json";
       xhr.onload = function(){
           //接收到總行數
           totalRows = xhr.response.total;
           console.log("總行數:",totalRows);

           //開始接收數據
           getRowsFromServer(0, ROWS_PER_REQ);
       };

       xhr.send();
    }


   /**發(fā)送Ajax請求分批從服務器拿數據
    *
    * @param offset 從第幾行數據開始
    * @param limit 每次取出多少行
    */
    function getRowsFromServer(offset, limit) {

        let xhr = new XMLHttpRequest();
        //拼接get字段
        xhr.open('GET', `get_data.php?offset=${offset}&limit=${limit}`);
        xhr.responseType = "json";

        //接收到數據
        xhr.onload = function(){
            /*格式:
            * [
            *    [第一列數據，第二列數據，第三列數據,.....],
            *    [第一列數據，第二列數據，第三列數據,.....],
            *    .......
            * ]
            * */
            //寫入到excel sheet
            XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1});
            //寫入excel后釋放內存
            xhr.response = null;

            let hasGetNum = offset + limit; //當前已取到的行數
            console.log(`已寫入${hasGetNum}行`);
            //更新進度顯示
            updateProgress(hasGetNum, totalRows);
            //如果沒取完,遞歸獲取下一批數據
            if(hasGetNum < totalRows){
                //剩余沒傳行數
                let rest = totalRows - hasGetNum;
                //計算下一批數據的數量
                let nextLimit = rest > ROWS_PER_REQ ? ROWS_PER_REQ : rest;
                //獲取下一批數據
                getRowsFromServer(hasGetNum, nextLimit);
            }else{
                //收取完成，下載excel
                XLSX.writeFile(workBook, 'export.xlsx');
                //總行數制0
                totalRows = 0;
            }
        };
        xhr.send();
    }

    /**更新進度顯示
     *
     * @param now   當前已更新行數
     * @param total 總行數
     */
    function updateProgress(now, total){
        //計算百分比
        let percent = (now/total * 100).toFixed(2);
        //更新dom
        document.getElementById("progressText").innerHTML = percent;
    }


</script>
</html>

get_data.php

<?php

//設置返回json頭
header('Content-type: application/json');
//總行數
const TOTAL_ROW = 50000;

//獲取總行數請求
if(isset($_GET['getTotal'])){
    //此處一般情況是從數據庫獲取總行數，這里為了簡化直接返回
    $json = json_encode([
        'total'=>TOTAL_ROW,
    ]);
    echo $json;
    exit;
}

//獲取數據請求
$offset = intval($_GET['offset']);
$limit = intval($_GET['limit']);
//返回
echo json_encode(getData($offset, $limit));

//從數據庫獲取數據
function getData($offset, $limit)
{
    //此處一般情況是從數據庫獲取數據，這里為了簡化直接生成數據
    $data = [];
    for ($i = 0; $i < $limit; $i++) {
        $lineNum = $offset + $i + 1; //行號
        $row = ["第${lineNum}行第1列數據", "第${lineNum}行第2列數據", "第${lineNum}行第3列數據", "第${lineNum}行第4列數據", "第${lineNum}行第5列數據", date('Y-m-d H:i:s')];
        $data[] = $row;
    }
    usleep(300000);//模擬耗時操作,暫停300ms
    return $data;
}

簡單性能測試一下：

5w行數據，占用內存200MB，導出無壓力


xlsx50w.PNG

20w行數據，占用內存600MB，導出無壓力


xlsx20w.PNG

image.png

100w行數據，js報錯，內存不夠

結束語：

對比服務端生成excel的方案，我覺得有以下一些優(yōu)點:

  • 性能更好，導出速度快
  • 有進度指示器，對用戶友好
  • 分批生成，不會觸發(fā)請求超時
  • 服務器壓力大大降低
  • 和后端查詢列表頁面的邏輯基本一致，后端幾乎不用增加功能直接用

缺點：前端邏輯比較復雜

注意：
  • 本文為原創(chuàng)，代碼可以隨意使用，無版權，轉載請注明原地址
  • 本文代碼不能直接用在生產環(huán)境，只用作流程演示，如需使用需要修改增加安全性等等。
  • 如果使用es6的async，await 語法，數據接收的代碼比本例中的遞歸更簡潔
  • 能生成excel表格最大行數只和客戶端配置有關
  • js-xlsx還要很多用法，如調整單元格格式等，更多的用法可以參考官網
  • 本文前端代碼在Chrome 77內核的瀏覽器下運行正常，其他瀏覽器兼容性本文不做探討
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容