Export HTML Table Data to Excel using JavaScript

Question

I tried to follow the reference : export HTML to Excel using JavaScript. It works for the first three columns of my data. But the last two columns, which I used nested axios to find out more data), are not working. I am not sure if the nested axios could be the issue.

• In my local test, I can see the whole data (or when I console.log("tableSelect"), I still can see the whole data
• After I export to excel, I can only see the first three columns data, the rest two are blanks

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);
}

Please can someone help to see what could be the issue? Thanks in advance

Answer 1

The problem is with the addCellBrand and addCellLanguage functions. They are asynchronous and you need to wait at the end to return cell, but in the addCell function, you are trying to do synchronous action and returning the cell at the end of the function's block properly.

So, you need to do your async action correctly:

in the addCellBrand and addCellLanguage function (they are similar so do the same for 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();
    });
  };

Now, you are implemented an asynchronous function to get the cell properly with creating a new promise.

It's time to use this async method in the 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);
})