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 :
This question is related to
javascript
jquery
html
excel
google-chrome
The answer is
Simplest way using Jquery
Just add this:
<script src="https://cdn.jsdelivr.net/gh/linways/[email protected]/dist/tableToExcel.js"></script>
Then add Jquery script:
<script type="text/javascript">
$(document).ready(function () {
$("#exportBtn1").click(function(){
TableToExcel.convert(document.getElementById("tab1"), {
name: "Traceability.xlsx",
sheet: {
name: "Sheet1"
}
});
});
});
</script>
Then add HTML button:
<button id="exportBtn1">Export To Excel</button><br><br>
Note: "exportBtn1" will be button ID and
"tab1" will be table ID
Very Easy Code
Follow this instruction
Create excel.php file in your localhost root directory and copy and past this code.
Like this
http://localhost/excel.php?fileName=excelfile&link=1
<!-- http://localhost/excel.php?fileName=excelfile&link=2 -->
<!DOCTYPE html>
<html>
<head>
<title>Export excel file from html table</title>
</head>
<body style="display:
<?php
if( $_REQUEST['link'] == 1 ){
echo 'none';
}
?>;
">
<!-- --------Optional-------- -->
Excel <input type="radio" value="1" name="exportFile">
Others <input type="radio" value="2" name="exportFile">
<button onclick="myFunction()">Download</button>
<br>
<br>
<!-- --------/Optional-------- -->
<table width="100%" id="tblData">
<tbody>
<tr>
<th>Student Name</th>
<th>Group</th>
<th>Roll No.</th>
<th>Class</th>
<th>Contact No</th>
</tr>
<tr>
<td>Bulbul Sarker</td>
<td>Science</td>
<td>1</td>
<td>Nine</td>
<td>01724....</td>
</tr>
<tr>
<td>Karim</td>
<td>Science</td>
<td>3</td>
<td>Nine</td>
<td>0172444...</td>
</tr>
</tbody>
</table>
</body>
</html>
<style>
table#tblData{
border-collapse: collapse;
}
#tblData th,
#tblData td{
border:1px solid #CCC;
text-align: center;
}
</style>
<script type="text/javascript">
/*--------Optional--------*/
function myFunction() {
let val = document.querySelector('input[name="exportFile"]:checked').value;
if(val == 1)
{
this.exportTableToExcel();
}
}
/*--------/Optional--------*/
function exportTableToExcel(){
let filename2 = "<?php echo $_REQUEST['fileName']; ?>";
let tableId = 'tblData';
var downloadLink;
var dataType = 'application/vnd.ms-excel';
var tableSelect = document.getElementById(tableId);
var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
// Specify file name
let filename = filename2?filename2+'.xls':'excel_data.xls';
// Create download link element
downloadLink = document.createElement("a");
document.body.appendChild(downloadLink);
if(navigator.msSaveOrOpenBlob){
var blob = new Blob(['\ufeff', tableHTML], {
type: dataType
});
navigator.msSaveOrOpenBlob( blob, filename);
}else{
// Create a link to the file
downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
// Setting the file name
downloadLink.download = filename;
//triggering the function
downloadLink.click();
}
}
</script>
<?php
if( $_REQUEST['link'] == 1 ){
echo '<script type="text/javascript">
exportTableToExcel();
</script>';
}
?>
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 :-)
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
My version of @sampopes answer
function exportToExcel(that, id, hasHeader, removeLinks, removeImages, removeInputParams) {
if (that == null || typeof that === 'undefined') {
console.log('Sender is required');
return false;
}
if (!(that instanceof HTMLAnchorElement)) {
console.log('Sender must be an anchor element');
return false;
}
if (id == null || typeof id === 'undefined') {
console.log('Table id is required');
return false;
}
if (hasHeader == null || typeof hasHeader === 'undefined') {
hasHeader = true;
}
if (removeLinks == null || typeof removeLinks === 'undefined') {
removeLinks = true;
}
if (removeImages == null || typeof removeImages === 'undefined') {
removeImages = false;
}
if (removeInputParams == null || typeof removeInputParams === 'undefined') {
removeInputParams = true;
}
var tab_text = "<table border='2px'>";
var textRange;
tab = $(id).get(0);
if (tab == null || typeof tab === 'undefined') {
console.log('Table not found');
return;
}
var j = 0;
if (hasHeader && tab.rows.length > 0) {
var row = tab.rows[0];
tab_text += "<tr bgcolor='#87AFC6'>";
for (var l = 0; l < row.cells.length; l++) {
if ($(tab.rows[0].cells[l]).is(':visible')) {//export visible cols only
tab_text += "<td>" + row.cells[l].innerHTML + "</td>";
}
}
tab_text += "</tr>";
j++;
}
for (; j < tab.rows.length; j++) {
var row = tab.rows[j];
tab_text += "<tr>";
for (var l = 0; l < row.cells.length; l++) {
if ($(tab.rows[j].cells[l]).is(':visible')) {//export visible cols only
tab_text += "<td>" + row.cells[l].innerHTML + "</td>";
}
}
tab_text += "</tr>";
}
tab_text = tab_text + "</table>";
if (removeLinks)
tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");
if (removeImages)
tab_text = tab_text.replace(/<img[^>]*>/gi, "");
if (removeInputParams)
tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");
var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");
if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
{
myIframe.document.open("txt/html", "replace");
myIframe.document.write(tab_text);
myIframe.document.close();
myIframe.focus();
sa = myIframe.document.execCommand("SaveAs", true, document.title + ".xls");
return true;
}
else {
//other browser tested on IE 11
var result = "data:application/vnd.ms-excel," + encodeURIComponent(tab_text);
that.href = result;
that.download = document.title + ".xls";
return true;
}
}
Requires an iframe
<iframe id="myIframe" style="opacity: 0; width: 100%; height: 0px;" seamless="seamless"></iframe>
Usage
$("#btnExportToExcel").click(function () {
exportToExcel(this, '#mytable');
});
You can use a library like ShieldUI to do that.
It supports exporting to both XML and XLSX widely-used Excel formats.
More details here: http://demos.shieldui.com/web/grid-general/export-to-excel
_x000D_
_x000D_
_x000D_
_x000D_
function exportToExcel() {_x000D_
var tab_text = "<tr bgcolor='#87AFC6'>";_x000D_
var textRange; var j = 0, rows = '';_x000D_
tab = document.getElementById('student-detail');_x000D_
tab_text = tab_text + tab.rows[0].innerHTML + "</tr>";_x000D_
var tableData = $('#student-detail').DataTable().rows().data();_x000D_
for (var i = 0; i < tableData.length; i++) {_x000D_
rows += '<tr>'_x000D_
+ '<td>' + tableData[i].value1 + '</td>'_x000D_
+ '<td>' + tableData[i].value2 + '</td>'_x000D_
+ '<td>' + tableData[i].value3 + '</td>'_x000D_
+ '<td>' + tableData[i].value4 + '</td>'_x000D_
+ '<td>' + tableData[i].value5 + '</td>'_x000D_
+ '<td>' + tableData[i].value6 + '</td>'_x000D_
+ '<td>' + tableData[i].value7 + '</td>'_x000D_
+ '<td>' + tableData[i].value8 + '</td>'_x000D_
+ '<td>' + tableData[i].value9 + '</td>'_x000D_
+ '<td>' + tableData[i].value10 + '</td>'_x000D_
+ '</tr>';_x000D_
}_x000D_
tab_text += rows;_x000D_
var data_type = 'data:application/vnd.ms-excel;base64,',_x000D_
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 border="2px">{table}</table></body></html>',_x000D_
base64 = function (s) {_x000D_
return window.btoa(unescape(encodeURIComponent(s)))_x000D_
},_x000D_
format = function (s, c) {_x000D_
return s.replace(/{(\w+)}/g, function (m, p) {_x000D_
return c[p];_x000D_
})_x000D_
}_x000D_
_x000D_
var ctx = {_x000D_
worksheet: "Sheet 1" || 'Worksheet',_x000D_
table: tab_text_x000D_
}_x000D_
document.getElementById("dlink").href = data_type + base64(format(template, ctx));_x000D_
document.getElementById("dlink").download = "StudentDetails.xls";_x000D_
document.getElementById("dlink").traget = "_blank";_x000D_
document.getElementById("dlink").click();_x000D_
}Here Value 1 to 10 are column names that you are getting
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();
}
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 :
_x000D_
_x000D_
_x000D_
_x000D_
function exportF(elem) {_x000D_
var table = document.getElementById("table");_x000D_
var html = table.outerHTML;_x000D_
var url = 'data:application/vnd.ms-excel,' + escape(html); // Set your html table into url _x000D_
elem.setAttribute("href", url);_x000D_
elem.setAttribute("download", "export.xls"); // Choose the file name_x000D_
return false;_x000D_
}<table id="table" border="1">_x000D_
<tr>_x000D_
<td>_x000D_
Foo_x000D_
</td>_x000D_
<td>_x000D_
Bar_x000D_
</td>_x000D_
</tr>_x000D_
</table>_x000D_
_x000D_
<a id="downloadLink" onclick="exportF(this)">Export to excel</a>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)
http://wsnippets.com/export-html-table-data-excel-sheet-using-jquery/ try this link it might solve your problem
Regarding to sampopes answer from Jun 6 '14 at 11:59:
I have insert a css style with font-size of 20px to display the excel data greater. In sampopes code the leading <tr> tags are missing, so i first output the headline and than the other tables lines within a loop.
function fnExcelReport()
{
var tab_text = '<table border="1px" style="font-size:20px" ">';
var textRange;
var j = 0;
var tab = document.getElementById('DataTableId'); // id of table
var lines = tab.rows.length;
// the first headline of the table
if (lines > 0) {
tab_text = tab_text + '<tr bgcolor="#DFDFDF">' + tab.rows[0].innerHTML + '</tr>';
}
// table data lines, loop starting from 1
for (j = 1 ; j < lines; j++) {
tab_text = tab_text + "<tr>" + tab.rows[j].innerHTML + "</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
// console.log(tab_text); // aktivate so see the result (press F12 in browser)
var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");
// if Internet Explorer
if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
txtArea1.document.open("txt/html","replace");
txtArea1.document.write(tab_text);
txtArea1.document.close();
txtArea1.focus();
sa = txtArea1.document.execCommand("SaveAs", true, "DataTableExport.xls");
}
else // other browser not tested on IE 11
sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
return (sa);
}
My merge of these examples:
https://www.codexworld.com/export-html-table-data-to-excel-using-javascript https://bl.ocks.org/Flyer53/1de5a78de9c89850999c
function exportTableToExcel(tableId, filename) {
let dataType = 'application/vnd.ms-excel';
let extension = '.xls';
let base64 = function(s) {
return window.btoa(unescape(encodeURIComponent(s)))
};
let 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>';
let render = function(template, content) {
return template.replace(/{(\w+)}/g, function(m, p) { return content[p]; });
};
let tableElement = document.getElementById(tableId);
let tableExcel = render(template, {
worksheet: filename,
table: tableElement.innerHTML
});
filename = filename + extension;
if (navigator.msSaveOrOpenBlob)
{
let blob = new Blob(
[ '\ufeff', tableExcel ],
{ type: dataType }
);
navigator.msSaveOrOpenBlob(blob, filename);
} else {
let downloadLink = document.createElement("a");
document.body.appendChild(downloadLink);
downloadLink.href = 'data:' + dataType + ';base64,' + base64(tableExcel);
downloadLink.download = filename;
downloadLink.click();
}
}
Source: Stackoverflow.com