Exporting HTML table to Excel using Javascript

Question

I am exporting HTML table to xls foramt. After exporting if you open it in Libre Office, it works fine but the same opens a blank screen in Microsoft Office.

I don't want a jquery solution please provide any javascript solution. Please help.

function fnExcelReport() {
    var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange;
    var j = 0;
    tab = document.getElementById('table'); // 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);
}

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

    Call this function on

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

    <table id="table">
  <thead>
        <tr>
            <th>Head1</th>
            <th>Head2</th>
            <th>Head3</th>
            <th>Head4</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>11</td>
            <td>12</td>
            <td>13</td>
            <td>14</td>
        </tr>
        <tr>
            <td>21</td>
            <td>22</td>
            <td>23</td>
            <td>24</td>
        </tr>
        <tr>
            <td>31</td>
            <td>32</td>
            <td>33</td>
            <td>34</td>
        </tr>
        <tr>
            <td>41</td>
            <td>42</td>
            <td>43</td>
            <td>44</td>
        </tr>
    </tbody>
    </table>

Answer 1

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

Answer 2

SheetJS seems perfect for this.

To export your table as an excel file use the code in this link(along with SheetJS)

Just plug in your table element's id into export_table_to_excel

See Demo

Answer 3

If CSV format is good for you, here is an example.

• Ok...I just read a comment where you explicitly say it isn't good for you. My bad for not learning to read before coding.

As far I know, Excel can handle CSV.

function fnExcelReport() {
var i, j;
var csv = "";

var table = document.getElementById("table");

var table_headings = table.children[0].children[0].children;
var table_body_rows = table.children[1].children;

var heading;
var headingsArray = [];
for(i = 0; i < table_headings.length; i++) {
  heading = table_headings[i];
  headingsArray.push('"' + heading.innerHTML + '"');
}

csv += headingsArray.join(',') + ";\n";

var row;
var columns;
var column;
var columnsArray;
for(i = 0; i < table_body_rows.length; i++) {
  row = table_body_rows[i];
  columns = row.children;
  columnsArray = [];
  for(j = 0; j < columns.length; j++) {
      var column = columns[j];
      columnsArray.push('"' + column.innerHTML + '"');
  }
  csv += columnsArray.join(',') + ";\n";
}

  download("export.csv",csv);
}

//From: http://stackoverflow.com/a/18197511/2265487
function download(filename, text) {
    var pom = document.createElement('a');
    pom.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(text));
    pom.setAttribute('download', filename);

    if (document.createEvent) {
        var event = document.createEvent('MouseEvents');
        event.initEvent('click', true, true);
        pom.dispatchEvent(event);
    }
    else {
        pom.click();
    }
}

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

Call this function on

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

<table id="table">
  <thead>
    <tr>
      <th>Head1</th>
      <th>Head2</th>
      <th>Head3</th>
      <th>Head4</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>11</td>
      <td>12</td>
      <td>13</td>
      <td>14</td>
    </tr>
    <tr>
      <td>21</td>
      <td>22</td>
      <td>23</td>
      <td>24</td>
    </tr>
    <tr>
      <td>31</td>
      <td>32</td>
      <td>33</td>
      <td>34</td>
    </tr>
    <tr>
      <td>41</td>
      <td>42</td>
      <td>43</td>
      <td>44</td>
    </tr>
  </tbody>
</table>

Answer 4

add this to your head:

<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>

and add this as your javascript:

<script type="text/javascript">
var tableToExcel = (function() {
  var uri = 'data:application/vnd.ms-excel;base64,'
    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>'
    , 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(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()
</script>

Jfiddle: http://jsfiddle.net/cmewv/537/

Answer 5

try this

<table id="exportable">
<thead>
      <tr>
          //headers
      </tr>
</thead>
<tbody>
         //rows
</tbody>
</table>

Script for this

var blob = new Blob([document.getElementById('exportable').innerHTML], {
            type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
        });
saveAs(blob, "Report.xls");

Answer 6

You can use tableToExcel.js to export table in excel file.

This works in a following way :

1). Include this CDN in your project/file

<script src="https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js"></script>

2). Either Using JavaScript:

<button id="btnExport" onclick="exportReportToExcel(this)">EXPORT REPORT</button>

function exportReportToExcel() {
  let table = document.getElementsByTagName("table"); // you can use document.getElementById('tableId') as well by providing id to the table tag
  TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
    name: `export.xls`, // fileName you could use any name
    sheet: {
      name: 'Sheet 1' // sheetName
    }
  });
}

3). Or by Using Jquery

<button id="btnExport">EXPORT REPORT</button>

$(document).ready(function(){
    $("#btnExport").click(function() {
        let table = document.getElementsByTagName("table");
        TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
           name: `export.xls`, // fileName you could use any name
           sheet: {
              name: 'Sheet 1' // sheetName
           }
        });
    });
});

You may refer to this github link for any other information

https://github.com/linways/table-to-excel/tree/master

or for referring the live example visit the following link

https://codepen.io/rohithb/pen/YdjVbb

This will download the export.xls file

Hope this will help someone :-)

Answer 7

<hrml>
  <head>
     <script language="javascript">
      function exportF() {
  //Format your table with form data
  document.getElementById("input").innerHTML = document.getElementById("text").value;
   document.getElementById("input1").innerHTML = document.getElementById("text1").value;
  var table = document.getElementById("table");
  var html = table.outerHTML;

  var url = 'data:application/vnd.C:\\Users\WB-02\desktop\Book1.xlsx,' + escape(html); // Set your html table into url 
  var link = document.getElementById("downloadLink");
  link.setAttribute("href", url);
  link.setAttribute("download", "export.xls"); // Choose the file name
  link.click(); // Download your excel file   
  return false;
}
    </script>
 </head>
 <body>
<form onsubmit="return exportF()">
  <input id="text" type="text" />
  <input id="text1" type="text" />
  <input type="submit" />
</form>

<table id="table" style="display: none">
  <tr>
    <td id="input">
    <td id="input1">
    </td>
  </tr>
</table>
<a style="display: none" id="downloadLink"></a>
</body>
</html>

Answer 8

İf you have a too much column , try to use this code. You can split easily.

function iterate( tab,  startIndex , rowCount){

    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    J=startIndex;

    for(j = startIndex ; j < rowCount ; 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)); 
  
  
} 
 
function fnExcelReport()
{
    var indirilecekSayi = 250;
  
    var toplamSatirSayisi = 0;

    var baslangicSAyisi = 0;

    var sonsatirsayisi = 0;
   
    tab = document.getElementById('myTable'); // id of table
    var maxRowCount = tab.rows.length;
    toplamSatirSayisi = maxRowCount;

  
  
    sonsatirsayisi=indirilecekSayi;
    
  
    
    var kalan = toplamSatirSayisi % indirilecekSayi;

    var KalansızToplamSatir=ToplamSatirSayisi-kalan;
    var kacKati=Tsh / indirilecekSayi;



 alert(maxRowCount);
    alert(kacKati);


    for (let index = 0; index <= kacKati; index++) {
        
        if (index==kacKati) {
           
            baslangicSAyisi =sonsatirsayisi;
           
            sonsatirsayisi=sonsatirsayisi+kalan;
           
            iterate(tab, baslangicSAyisi, sonsatirsayisi);
      
        }else{

           
           
            iterate(tab , baslangicSAyisi , sonsatirsayisi);
          
            baslangicSAyisi=sonsatirsayisi;
           
            
            sonsatirsayisi=sonsatirsayisi+indirilecekSayi;
            if(sonsatirsayisi>ToplamSatirSayisi){
                sonsatirsayisi=baslangicSAyisi;
            }

        }
            

    }

}