How to Export HTML Table to Excel Using JavaScript

How to export html table to excel using javascript

The reason the solution you found on the internet is no working is because of the line that starts var colCount. The variable mytable only has two elements being <thead> and <tbody>. The var colCount line is looking for all the elements within mytable that are <tr>. The best thing you can do is give an id to your <thead> and <tbody> and then grab all the values based on that. Say you had <thead id='headers'> :

function write_headers_to_excel() 
{
  str="";

  var myTableHead = document.getElementById('headers');
  var rowCount = myTableHead.rows.length;
  var colCount = myTableHead.getElementsByTagName("tr")[0].getElementsByTagName("th").length; 

var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;

for(var i=0; i<rowCount; i++) 
{   
    for(var j=0; j<colCount; j++) 
    {           
        str= myTableHead.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].innerHTML;
        ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;
    }
}

}

and then do the same thing for the <tbody> tag.

EDIT: I would also highly recommend using jQuery. It would shorten this up to:

function write_to_excel() 
{
 var ExcelApp = new ActiveXObject("Excel.Application");
 var ExcelSheet = new ActiveXObject("Excel.Sheet");
 ExcelSheet.Application.Visible = true; 

  $('th, td').each(function(i){
    ExcelSheet.ActiveSheet.Cells(i+1,i+1).Value = this.innerHTML;
  });
}

Now, of course, this is going to give you some formatting issues but you can work out how you want it formatted in Excel.

EDIT: To answer your question about how to do this for n number of tables, the jQuery will do this already. To do it in raw Javascript, grab all the tables and then alter the function to be able to pass in the table as a parameter. For instance:

var tables = document.getElementsByTagName('table');
for(var i = 0; i < tables.length; i++)
{
  write_headers_to_excel(tables[i]);
  write_bodies_to_excel(tables[i]);
}

Then change the function write_headers_to_excel() to function write_headers_to_excel(table). Then change var myTableHead = document.getElementById('headers'); to var myTableHead = table.getElementsByTagName('thead')[0];. Same with your write_bodies_to_excel() or however you want to set that up.

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

Excel export script works on IE7+, Firefox and Chrome.

function fnExcelReport()
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('headerTable'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Just create a blank iframe:

<iframe id="txtArea1" style="display:none"></iframe>

Call this function on:

<button id="btnExport" onclick="fnExcelReport();"> EXPORT </button>

export html table in excel

the issues is you are creating the sheet before looping into the other once :

exactly here :

ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
worksheetsXML += format(tmplWorksheetXML, ctx);
rowsXML = "";

this code working fine , but your rowsXML , is alredy saved and you did move to the other sheet.

what you can do is change the loop approch , or maybe store the rows and nameWS in a dictionary , wait unti the and of the loop and you can call those above lines.

to better understand what i mean , you can change your code to this :

  ctx = {rows: rowsXML + rowsXML, nameWS: wsnames[i] || 'Sheet' + i};

and then you will see that the same table is duplicated in each sheet. so the whole work that you need to do is to fill rowsXML with all tables in the same sheet before looping to other table or sheet.

EDIT:

first let's say that this presentation here :

 <button  onclick="tablesToExcel(['tbl1','tbl2'], ['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to Excel</button>

is not helpful in your case.
perhabs if it was like that :

 <button  onclick="tablesToExcel( [{ 'sheet': 'firstsheet','tables' : ['tbl1','tbl2']},{ 'sheet': 'secondsheet','tables' : ['tbl5']}], 'TestBook.xls', 'Excel')">Export to Excel</button>

because in this cases you are setting your sheet first.

EDIT:
and here is your working code fiddle :
http://jsfiddle.net/ad2mejco/

html :

 <button  onclick="tablesToExcel( [{ 'sheet': 'firstsheet','tables' : ['tbl1','tbl2']},{ 'sheet': 'secondsheet','tables' : ['tbl5']}], 'TestBook.xls', 'Excel')">Export to Excel</button>

Js:

  var tablesToExcel = (function() {
    var uri = 'data:application/vnd.ms-excel;base64,'
    , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
      + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
      + '<Styles>'
      + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
      + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
      + '</Styles>' 
      + '{worksheets}</Workbook>'
    , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
    , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
    return function(data, wbname, appname) {
      var ctx = "";
      var workbookXML = "";
      var worksheetsXML = "";
      var rowsXML = "";
     
     for (var x = 0; x < data.length; x++) {
     console.log(data[x]["sheet"]);
     
    tables = data[x]["tables"];
     
        for (var i = 0; i < tables.length; i++) {
        if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
        for (var j = 0; j < tables[i].rows.length; j++) {
                  rowsXML += '<Row>'

          for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
            var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
            var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
            var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
            dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;
            var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
            dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;
            ctx = {  attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''
                   , nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
                   , data: (dataFormula)?'':dataValue
                   , attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
                  };
            rowsXML += format(tmplCellXML, ctx);
          }
               rowsXML += '</Row>'

        }
   
      }
     
     

         ctx = {rows: rowsXML, nameWS: data[x]["sheet"] || 'Sheet' + i};
        worksheetsXML += format(tmplWorksheetXML, ctx);
        rowsXML = "";

     }
     

      ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
      workbookXML = format(tmplWorkbookXML, ctx);

console.log(workbookXML);

      var link = document.createElement("A");
      link.href = uri + base64(workbookXML);
      link.download = wbname || 'Workbook.xls';
      link.target = '_blank';
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }
  })();

Export HTML Table Data to Excel using JavaScript

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

Exporting HTML table to Excel using Javascript

On 2016-07-12, Microsoft pushed a security update for Microsoft Office. One of the effects of this update was to prevent HTML files from non-trusted domains from being opened by Excel, because they cannot be opened in Protected mode.

There is ALSO a registry setting that prevents Excel from opening files with the .XLS file extension whose contents do not match the official XLS file format, though it defaults to 'warn', not 'deny'.

Prior to this change, it was possible to save HTML data to a file with an XLS extension, and Excel would open it correctly - possibly giving a warning first that the file did not match the Excel format, depending on the user's value for the ExtensionHardening registry key (or related config values).

Microsoft has made a knowledge-base entry about the new behavior with some suggested workarounds.

Several web applications that previously relied on exporting HTML files as XLS have run into trouble as a result of the update - SalesForce is one example.

Answers from before July 12th 2016 to this and similar questions are likely to now be invalid.

It's worth noting that files produced ON THE BROWSER from remote data do not fall afoul of this protection; it only impedes files downloaded from a remote source that is not trusted. Therefore one possible approach is to generate the .XLS-labelled HTML file locally on the client.

Another, of course, is to produce a valid XLS file, which Excel will then open in Protected mode.

UPDATE: Microsoft has released a patch to correct this behavior: https://support.microsoft.com/en-us/kb/3181507

how to skip particular columns while exporting html table as excel using javascript

You could assign each element you want to remove with a class, like no-export. Then whenever you want to export, create a clone and remove anything with the no-export class. This fiddle should give you a good idea of how to do this.

https://jsfiddle.net/9wr2sc8g/1/

---

Related Topics