Web优雅的实现大数据量Excel导出

在做系统后台的时候，一般Excel表导出的方案是在服务器生成Excel表，返回给浏览器Excel文件地址，触发下载。当要导出的Excel行数达到数万行时，占用大量的服务器CPU或内存不说，还容易请求超时，还要改nginx配置。我就想，能否在前端浏览器创建Excel表呢？之后找到一个合适的js库可以实现这个功能：js-xlsx。
官方github地址： https://github.com/SheetJS/js-xlsx
1、浏览器创建xlsx
2、每次ajax向后端请求若干行的数据，直到请求完成
3、触发下载
本文省略php,web服务器等环境搭建步骤。看懂接下来代码实现，需要有一些js,html,PHP,ajax的一些相关知识。
1、js-xlsx库安装使用

本文使用 npm 安装js-xlsx,如果不用 npm ，可以直接去 https://github.com/SheetJS/js-xlsx/releases 下载压缩包(很卡,我的网下不动)。
创建项目目录后，执行以下命令，下载js-xlsx包：
npm init
npm install xlsx
创建index.html在header中引入
<!DOCTYPE html>
<html lang="en">
    <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、创建html页面元素
index.html
<!--    进度显示-->
    <span>导出进度:</span><span id="progressText">0%</span>
<!--    开始导出按钮-->
    <input type="button" id="startBtn" value="开始导出">
</body>
</body>
浏览器显示：
        workSheet = XLSX.utils.aoa_to_sheet(headArr);
        //把excel sheet添加到工作簿
        XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
getTotal()函数见下章。

这里的js-xlsx库的几个用法解释:
XLSX.utils.book_new(); 创建并返回一个excel工作簿对象，可以理解为excel文件
XLSX.utils.aoa_to_sheet(headArr); 接受一个二维数组作为参数，创建一个excel sheet对象(一个excel标签页)
XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');

把excel sheet对象添加到工作簿对象,并命名为'sheet1'
4、发送Ajax请求从服务器获取数据总行数
index.html:
    //发送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:
//设置返回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
   /**发送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文件，并触发浏览器下载

第一个参数：excel工作簿实例

第二个参数：下载的文件名，文件格式会根据文件后缀自动识别，本例是xlsx， 其他格式本例用不到，想进一步了解可以参考官网
后端发送数据的逻辑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">
    <meta charset="UTF-8">
    <title>demo</title>
    <script src="node_modules/xlsx/jszip.js"></script>
    <script src="node_modules/xlsx/xlsx.js"></script>
</head>
<!--    进度显示-->
    <span>导出进度:</span><span id="progressText">0%</span>
<!--    开始导出按钮-->
    <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(){
        //创建excel工作簿对象
        workBook = XLSX.utils.book_new();
        //创建excel第一行，头部
        let headArr = [
            ['第一列', '第二列', '第三列', '第四列', '第五列', '时间'],
        //根据头部数组创建excel sheet
        workSheet = XLSX.utils.aoa_to_sheet(headArr);
        //把excel sheet添加到工作簿
        XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
    //发送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();
   /**发送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
//设置返回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，导出无压力
本文为原创，代码可以随意使用，无版权，转载请注明原地址
本文代码不能直接用在生产环境，只用作流程演示，如需使用需要修改增加安全性等等。