Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser
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>
How to export html table to excel using javascript
The reason the solution you found on the internet is no working is because of the line that starts var colCount
. The variable mytable
only has two elements being <thead>
and <tbody>
. The var colCount
line is looking for all the elements within mytable
that are <tr>
. The best thing you can do is give an id to your <thead>
and <tbody>
and then grab all the values based on that. Say you had <thead id='headers'>
:
function write_headers_to_excel()
{
str="";
var myTableHead = document.getElementById('headers');
var rowCount = myTableHead.rows.length;
var colCount = myTableHead.getElementsByTagName("tr")[0].getElementsByTagName("th").length;
var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;
for(var i=0; i<rowCount; i++)
{
for(var j=0; j<colCount; j++)
{
str= myTableHead.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].innerHTML;
ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;
}
}
}
and then do the same thing for the <tbody>
tag.
EDIT: I would also highly recommend using jQuery. It would shorten this up to:
function write_to_excel()
{
var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;
$('th, td').each(function(i){
ExcelSheet.ActiveSheet.Cells(i+1,i+1).Value = this.innerHTML;
});
}
Now, of course, this is going to give you some formatting issues but you can work out how you want it formatted in Excel.
EDIT: To answer your question about how to do this for n
number of tables, the jQuery will do this already. To do it in raw Javascript, grab all the tables and then alter the function to be able to pass in the table as a parameter. For instance:
var tables = document.getElementsByTagName('table');
for(var i = 0; i < tables.length; i++)
{
write_headers_to_excel(tables[i]);
write_bodies_to_excel(tables[i]);
}
Then change the function write_headers_to_excel()
to function write_headers_to_excel(table)
. Then change var myTableHead = document.getElementById('headers');
to var myTableHead = table.getElementsByTagName('thead')[0];
. Same with your write_bodies_to_excel()
or however you want to set that up.
How do I export multiple html tables to excel?
var tablesToExcel = (function () {
var uri = 'data:application/vnd.ms-excel;base64,'
, 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>'
, templateend = '</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head>'
, body = '<body>'
, tablevar = '<table>{table'
, tablevarend = '}</table>'
, bodyend = '</body></html>'
, worksheet = '<x:ExcelWorksheet><x:Name>'
, worksheetend = '</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>'
, worksheetvar = '{worksheet'
, worksheetvarend = '}'
, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
, wstemplate = ''
, tabletemplate = '';
return function (table, name, filename) {
var tables = table;
for (var i = 0; i < tables.length; ++i) {
wstemplate += worksheet + worksheetvar + i + worksheetvarend + worksheetend;
tabletemplate += tablevar + i + tablevarend;
}
var allTemplate = template + wstemplate + templateend;
var allWorksheet = body + tabletemplate + bodyend;
var allOfIt = allTemplate + allWorksheet;
var ctx = {};
for (var j = 0; j < tables.length; ++j) {
ctx['worksheet' + j] = name[j];
}
for (var k = 0; k < tables.length; ++k) {
var exceltable;
if (!tables[k].nodeType) exceltable = document.getElementById(tables[k]);
ctx['table' + k] = exceltable.innerHTML;
}
//document.getElementById("dlink").href = uri + base64(format(template, ctx));
//document.getElementById("dlink").download = filename;
//document.getElementById("dlink").click();
window.location.href = uri + base64(format(allOfIt, ctx));
}
})();
And the HTML
<html>
<head>
<title>JS to Excel</title>
</head>
<body>
<table id="1">
<tr><td>Hi</td></tr>
<tr><td>Hey</td></tr>
<tr><td>Hello</td></tr>
</table>
<table id="2">
<tr><td>Night</td></tr>
<tr><td>Evening</td></tr>
<tr><td>Nite</td></tr>
</table>
<a id="dlink" style="display:none;"></a>
<input type="button" onclick="tablesToExcel(['1', '2'], ['first', 'second'], 'myfile.xls')" value="Export to Excel">
<script src="~/Views/JS/JSExcel.js" type="text/javascript"></script>
</body>
</html>
NOTE: this doesn't work on IE ('data too small' error) and on Firefox both tables are put on the same sheet.
Credit also to this thread - HTML Table to Excel Javascript
Related Topics
Calculating Page Load Time in JavaScript
JavaScript with Embedded Ruby: How to Safely Assign a Ruby Value to a JavaScript Variable
Using Socket.Io in Express 4 and Express-Generator's /Bin/Www
Download a Div in a HTML Page as PDF Using JavaScript
Resolving Require Paths with Webpack
Differencebetween Regexp's Exec() Function and String's Match() Function
Jquery Beforeunload When Closing (Not Leaving) the Page
Convert Camelcasetext to Title Case Text
Triggering Onclick Event Using Middle Click
Angular: Can't Find Promise, Map, Set and Iterator
Detect If Hovering Over Element with Jquery
Set a Request Header in JavaScript
What Is the Lifecycle of an Angularjs Controller
Jquery or JavaScript to Find Memory Usage of Page
Event.Preventdefault() VS. Return False (No Jquery)
Ckeditor 4: Uncaught Typeerror: Cannot Read Property 'Langentries' of Null