html table to excel
Excel HTML Javascript Web Development

Convert HTML table to Excel file with javascript

One of the method that we use to display a numeric data on web page is using tables. But if we want to let the user be able to download that HTML table, we have to convert it into a file. In this tutorial, I’m going to show you how to convert HTML table to Excel file with javascript.

Our goal in this example is to create a javascript function that will be executed when user click the download link. To generate xlsx file from client side only, we need to put together an xls file in XML format. You can study the full SpreadsheetDataXML documentation here but to sum it up, it’s just a way to describe the excel file in HTML/XML format.

For example, we can create an excel file with bold “Hello World” text in cell A1 with in XML format below.

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head><xml>
<x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
   <x:Name>Test Sheet</x:Name>
   <x:WorksheetOptions>
      <x:Panes></x:Panes></x:WorksheetOptions>
</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook>
</xml></head>
<body><table border='1px'>
 <tr>
    <td><b>Hello World</b></td>
 </tr>
</table></body>
</html>

As shown above, you can use HTML (and CSS if needed) to describe the table. So if you want to export any table on your webpage, you can just copy its HTML code and stuff it in the XML.

Assuming that the table has id=”myTable”, with little help from jQuery, our function should look like this.

function fnExcelReport() {
 var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
 tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
 tab_text = tab_text + '<x:Name>Test Sheet</x:Name>';
 tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
 tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';
 tab_text = tab_text + "<table border='1px'>";
 
//get table HTML code
 tab_text = tab_text + $('#myTable').html();
 tab_text = tab_text + '</table></body></html>';
}

Now the excel file content is ready. Next, we will make the browser take it and popup the download button. For Chrome and Firefox, we can change the data_type in href but that won’t work with IE. We’ll need to use Blob object as a workaround.

var data_type = 'data:application/vnd.ms-excel';
 
 var ua = window.navigator.userAgent;
 var msie = ua.indexOf("MSIE ");
 //For IE
 if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
      if (window.navigator.msSaveBlob) {
      var blob = new Blob([tab_text], {type: "application/csv;charset=utf-8;"});
      navigator.msSaveBlob(blob, 'Test file.xls');
      }
 } 
//for Chrome and Firefox 
else {
 $('#test').attr('href', data_type + ', ' + encodeURIComponent(tab_text));
 $('#test').attr('download', 'Test file.xls');
}

For complete code and working example, see jsfiddle here or you can see step by step tutorial video below!

Written By

 

5 comments

  1. hi thanks that is great
    but it’s doesnt work with all char and show like this -> بهمن معمارزاده

    how i can fix this?

  2. Hi thanks for this method. Any suggestions on how to export two tables in 2 different worksheets of excel file?

  3. how can i modify it, if i want the file to be saved on a specific location, without the download button. (for automation purposes. thanks

  4. function ExportToExcel(tableid) {

    var tab_text = “”;
    var textRange; var j = 0;
    tab = document.getElementById(tableid);//.getElementsByTagName(‘table’); // id of table
    if (tab==null) {
    return false;
    }
    if (tab.rows.length == 0) {
    return false;
    }

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

    tab_text = tab_text + “”;
    tab_text = tab_text.replace(/]*>|/g, “”);//remove if u want links in your table
    tab_text = tab_text.replace(/]*>/gi, “”); // remove if u want images in your table
    tab_text = tab_text.replace(/]*>|/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, “download.xls”);
    }
    else //other browser not tested on IE 11
    //sa = window.open(‘data:application/vnd.ms-excel,’ + encodeURIComponent(tab_text));
    try {
    var blob = new Blob([tab_text], { type: “application/vnd.ms-excel” });
    window.URL = window.URL || window.webkitURL;
    link = window.URL.createObjectURL(blob);
    a = document.createElement(“a”);
    if (document.getElementById(“caption”)!=null) {
    a.download=document.getElementById(“caption”).innerText;
    }
    else
    {
    a.download = ‘download’;
    }

    a.href = link;

    document.body.appendChild(a);

    a.click();

    document.body.removeChild(a);
    } catch (e) {
    }

    return false;
    //return (sa);
    }

Leave a Reply

Your email address will not be published. Required fields are marked *