JavaScript导出HTML表格到Excel时嵌套axios导致后两列空白问题排查

问题根源 #

你当前的问题是嵌套异步请求没有等待完成就执行了导出逻辑导致的：
addCellBrand和addCellLanguage两个函数内部的Axios请求是异步执行的，函数会直接返回还没填充内容的空<td>节点，等你调用exportTableToExcel导出表格时，这两个异步接口还没返回结果，也就没往对应<td>里写入内容，因此导出的Excel里这两列为空白。你后续在控制台能看到完整数据，是因为导出操作执行完后，异步请求才返回并完成了DOM内容填充。

解决方案 #

调整逻辑，先把所有需要的异步数据全部获取完成，再生成表格、执行导出，修改后的代码如下：

import Axios from "axios";

window.onload = function (){
    const exportExcelBtn = document.getElementById("Export-Excel");

    const body = document.getElementsByTagName("body")[0];
    const tbl = document.createElement("table");
    tbl.setAttribute("border", "2");
    tbl.setAttribute("id", "tblId");
    const tblbody = document.createElement("tbody");

    const getFieldsFromApi = async () => {
        const GetUrl = "abc.net"
        const Doorkey = {username: "token", password: "token"}
        const response = await Axios.get(GetUrl, {auth: Doorkey})
        return response.data.users
    }

    // 抽取获取品牌数据的公共方法
    const getBrandFromApi = async (id) => {
        const GetUrl = `abc.net/${id}`
        const Doorkey = {username: "token", password: "token"}
        const response = await Axios.get(GetUrl, {auth: Doorkey})
        return response.data.organizations[0].name
    }

    // 抽取获取语言数据的公共方法
    const getLanguageFromApi = async (localeId) => {
        const GetUrl = `abc.net/${localeId}/fieldList`
        const Doorkey = {username: "token", password: "token"}
        const response = await Axios.get(GetUrl, {auth: Doorkey})
        return response.data.locale.presentation_name
    }

    const addCell = value => {
        const cell = document.createElement("td");
        const cellText = document.createTextNode(value);
        cell.appendChild(cellText);
        return cell
    }

    const exportTableToExcel = (tbl, filename = "") => {
        let downloadLink;
        const dataType = 'application/vnd.ms-excel';
        const tableSelect = document.getElementById(tbl);
        let tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
        filename = filename?filename+'.xls':'excel_data.xls';          
        downloadLink = document.createElement("a");
        document.body.appendChild(downloadLink);

        if(navigator.msSaveOrOpenBlob){
            let blob = new Blob(['\ufeff', tableHTML], {
                type: dataType
            });
            navigator.msSaveOrOpenBlob( blob, filename );
        }else{
            downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
            downloadLink.download = filename;
            downloadLink.click();
        }
    };

    const getData = async () => {
        const values = await getFieldsFromApi()
        // 等待所有用户对应的异步数据全部获取完成
        const userFullDataList = await Promise.all(values.map(async field => {
            const brandName = await getBrandFromApi(field.id)
            const languageName = await getLanguageFromApi(field.locale_id)
            return {
                ...field,
                brandName,
                languageName
            }
        }))
        // 所有数据就绪后再生成表格
        userFullDataList.forEach(field => {
            const row = document.createElement("tr");
            row.appendChild(addCell(field.name));
            row.appendChild(addCell(field.email));
            row.appendChild(addCell(field.role_type));            
            row.appendChild(addCell(field.brandName));              
            row.appendChild(addCell(field.languageName));            
            tblbody.appendChild(row);
        })
        tbl.appendChild(tblbody);
        body.appendChild(tbl);
        // 此时表格内容全部填充完成，再执行导出
        exportTableToExcel(tbl.id);
    };

    exportExcelBtn.addEventListener("click", getData);
}

优化建议 #

如果用户数据量较大，同时发起大量异步请求可能会触发接口限流，可根据实际情况调整为批量请求接口，或者给Promise.all加上并发数限制，避免请求失败。

内容的提问来源于stack exchange，提问作者Moon Factory