javascript - 使用 JavaScript 将 HTML 表格数据导出到 Excel

Question

我尝试遵循参考：使用 JavaScript 将 HTML 导出到 Excel。它适用于我数据的前三列。但是最后两列（我使用嵌套 axios 来查找更多数据）不起作用。我不确定嵌套的 axios是否是问题所在。

• 在我的本地测试中，我可以看到整个数据（或者当 I 时console.log("tableSelect")，我仍然可以看到整个数据
• 我导出到excel后只能看到前三列数据，其余两列都是空白

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 getData = async () => {
    const values = await getFieldsFromApi()
    values.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(addCellBrand(field.id));              // when export to excel, this data is not shown 
        row.appendChild(addCellLanguage(field.locale_id));    // when export to excel, this data is not shown         
        tblbody.appendChild(row);
    })
    tbl.appendChild(tblbody);
    body.appendChild(tbl);
    const exportTableToExcel = (tbl, filename = "") => {
        let downloadLink;
        const dataType = 'application/vnd.ms-excel';
        const tableSelect = document.getElementById(tbl);
        let tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
        // Specify file name
        filename = filename?filename+'.xls':'excel_data.xls';          
        // Create download link element
        downloadLink = document.createElement("a");
        document.body.appendChild(downloadLink);

        if(navigator.msSaveOrOpenBlob){
            let blob = new Blob(['\ufeff', tableHTML], {
                type: dataType
            });
            navigator.msSaveOrOpenBlob( blob, filename );
        }else{
            // Create a link to the file
            downloadLink.href = 'data:' + dataType + ', ' + tableHTML;

            // Setting the file name
            downloadLink.download = filename;
                    
            //triggering the function
            downloadLink.click();
        }
                
        };
        exportTableToExcel(tbl.id); //this is export the excel file
};

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

    return cell
}

const addCellBrand = value => {
    const cell = document.createElement("td");
    const getBrandFromApi = async () => {
        const GetUrl = `abc.net/${value}`
        const Doorkey = {username: "token", password: "token"}
    
        const response = await Axios.get(GetUrl, {auth: Doorkey})
        const getData = response.data.organizations[0].name
        const cellText = document.createTextNode(getData); 
        cell.appendChild(cellText);
    }
    getBrandFromApi();

    return cell
}

const addCellLanguage = value => {
    const cell = document.createElement("td");
    const getLanguageFromApi = async () => {
        const GetUrl = `abc.net/${value}/fieldList`
        const Doorkey = {username: "token", password: "token"}
    
        const response = await Axios.get(GetUrl, {auth: Doorkey})
        const getData = response.data.locale.presentation_name
        const cellText = document.createTextNode(getData); 
        cell.appendChild(cellText);     
    }
    getLanguageFromApi();

    return cell
}

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

请有人帮忙看看可能是什么问题？提前致谢

标签： javascriptexcelaxios

Answer 1

问题在于addCellBrandandaddCellLanguage函数。它们是异步的，您需要在结束时等待return cell，但在addCell函数中，您正在尝试执行同步操作并cell正确返回函数块末尾的 。

因此，您需要正确执行异步操作：

在addCellBrandandaddCellLanguage函数中（它们相似，所以对 addCellLanguage 执行相同操作）：

const addCellBrand = (value) => {
    const cell = document.createElement("td");
    return new Promise((resolve, reject) => {
      const getBrandFromApi = async () => {
        const GetUrl = `abc.net/${value}`;
        const Doorkey = { username: "token", password: "token" };

        try {
          const response = await Axios.get(GetUrl, {auth: Doorkey});
          const getData = response.data.organizations[0].name
          const cellText = document.createTextNode(getData);
          cell.appendChild(cellText);
          resolve(cell); // here for returning your cell after all
        } catch (err) {
          reject(cell);  // don't forgot about the failure case on your API call
        }
      };
      getBrandFromApi();
    });
  };

现在，您实现了一个异步函数来cell正确地创建一个新的 Promise。

是时候在下面使用这个异步方法了getData：

const values = await getFieldsFromApi()
values.forEach(async (field) => {
    const cellBrand = await addCellBrand(field.id) 
    const cellLanguage = await addCellLangugae(field.local_id)

    const row = document.createElement("tr");
    row.appendChild(addCell(field.name));
    row.appendChild(addCell(field.email));
    row.appendChild(addCell(field.role_type));            
    row.appendChild(cellBrand);
    row.appendChild(cellLanguage);         
    tblbody.appendChild(row);
})