Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser
I have a HTML table in velocity template. I want to export the html table data to excel using either java script or jquery, comatibale with all browser. I am using below script
<script type="text/javascript">
function ExportToExcel(mytblId){
var htmltable= document.getElementById('my-table-id');
var html = htmltable.outerHTML;
window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
}
</script>
This script works fine in Mozilla Firefox,it pops-up with a dialog box of excel and ask for open or save options. But when I tested same script in Chrome browser it is not working as expected,when clicked on button there is no pop-up for excel. Data gets downloaded in a file with "file type : file" , no extension like .xls There are no errors in chrome console.
Jsfiddle example :
http://jsfiddle.net/insin/cmewv/
This works fine in mozilla but not in chrome.
Chrome browser Test Case :
First Image:I click on Export to excel button
and result :
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>
Datatable plugin solves the purpose best and allows us to export the HTML table data into Excel , PDF , TEXT. easily configurable.
Please find the complete example in below datatable reference link :
https://datatables.net/extensions/buttons/examples/html5/simple.html
(screenshot from datatable reference site)
This could help
function exportToExcel(){
var htmls = "";
var uri = 'data:application/vnd.ms-excel;base64,';
var 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]--></head><body><table>{table}</table></body></html>';
var base64 = function(s) {
return window.btoa(unescape(encodeURIComponent(s)))
};
var format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
};
htmls = "YOUR HTML AS TABLE"
var ctx = {
worksheet : 'Worksheet',
table : htmls
}
var link = document.createElement("a");
link.download = "export.xls";
link.href = uri + base64(format(template, ctx));
link.click();
}
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/[email protected]/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.xlsx`, // 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.xlsx`, // 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
Hope this will help someone :-)
Instead of using window.open you can use a link with the onclick event.
And you can put the html table into the uri and set the file name to be downloaded.
Live demo :
function exportF(elem) {
var table = document.getElementById("table");
var html = table.outerHTML;
var url = 'data:application/vnd.ms-excel,' + escape(html); // Set your html table into url
elem.setAttribute("href", url);
elem.setAttribute("download", "export.xls"); // Choose the file name
return false;
}<table id="table" border="1">
<tr>
<td>
Foo
</td>
<td>
Bar
</td>
</tr>
</table>
<a id="downloadLink" onclick="exportF(this)">Export to excel</a>