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!
hi thanks that is great
but it’s doesnt work with all char and show like this -> بهمن معمارزاده
how i can fix this?
Probably encoding issue. Try force encoding at xml header in fnExcelReport() like this
Hi thanks for this method. Any suggestions on how to export two tables in 2 different worksheets of excel file?
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
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);
}