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