Menu
To convert HTML table data into excel, we need to use the SheetJS library. Using SheetJs we can easily convert our table data into an Xls file. We can download the js file from Github or directly use the CDN hosted file. We are done with HTML markup and import Sheetjs library.
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>TableExport - The simple, easy-to-implement library to export HTML tables to xlsx, xls, csv, and txt files.
To use this library, simple call the TableExport constructor:
new TableExport(document.getElementsByTagName("table"));
// OR simply
TableExport(document.getElementsByTagName("table"));
// OR using jQuery
$("table").tableExport();
Additional properties can be passed-in to customize the look and feel of your tables, buttons, and exported data. See here more info
http://wsnippets.com/export-html-table-data-excel-sheet-using-jquery/ try this link it might solve your problem
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With