Html Table to Excel JavaScript

Javascript to export html table to Excel

If you add:

<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>

in the head of the document it will start working as expected:

<script type="text/javascript">
var tableToExcel = (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><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>'
, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
return function(table, name) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))
}
})()
</script>

Updated Fiddle Here.

export html table in excel

the issues is you are creating the sheet before looping into the other once :

exactly here :

ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
worksheetsXML += format(tmplWorksheetXML, ctx);
rowsXML = "";

this code working fine , but your rowsXML , is alredy saved and you did move to the other sheet.

what you can do is change the loop approch , or maybe store the rows and nameWS in a dictionary , wait unti the and of the loop and you can call those above lines.

to better understand what i mean , you can change your code to this :

  ctx = {rows: rowsXML + rowsXML, nameWS: wsnames[i] || 'Sheet' + i};

and then you will see that the same table is duplicated in each sheet. so the whole work that you need to do is to fill rowsXML with all tables in the same sheet before looping to other table or sheet.

EDIT:

first let's say that this presentation here :

 <button  onclick="tablesToExcel(['tbl1','tbl2'], ['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to Excel</button>

is not helpful in your case.
perhabs if it was like that :

 <button  onclick="tablesToExcel( [{ 'sheet': 'firstsheet','tables' : ['tbl1','tbl2']},{ 'sheet': 'secondsheet','tables' : ['tbl5']}], 'TestBook.xls', 'Excel')">Export to Excel</button>

because in this cases you are setting your sheet first.

EDIT:
and here is your working code fiddle :
http://jsfiddle.net/ad2mejco/

html :

 <button  onclick="tablesToExcel( [{ 'sheet': 'firstsheet','tables' : ['tbl1','tbl2']},{ 'sheet': 'secondsheet','tables' : ['tbl5']}], 'TestBook.xls', 'Excel')">Export to Excel</button>

Js:

  var tablesToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
+ '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
+ '<Styles>'
+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
+ '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
+ '</Styles>'
+ '{worksheets}</Workbook>'
, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
return function(data, wbname, appname) {
var ctx = "";
var workbookXML = "";
var worksheetsXML = "";
var rowsXML = "";

for (var x = 0; x < data.length; x++) {
console.log(data[x]["sheet"]);

tables = data[x]["tables"];

for (var i = 0; i < tables.length; i++) {
if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
for (var j = 0; j < tables[i].rows.length; j++) {
rowsXML += '<Row>'

for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;
var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;
ctx = { attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''
, nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
, data: (dataFormula)?'':dataValue
, attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
};
rowsXML += format(tmplCellXML, ctx);
}
rowsXML += '</Row>'

}

}



ctx = {rows: rowsXML, nameWS: data[x]["sheet"] || 'Sheet' + i};
worksheetsXML += format(tmplWorksheetXML, ctx);
rowsXML = "";

}


ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
workbookXML = format(tmplWorkbookXML, ctx);

console.log(workbookXML);

var link = document.createElement("A");
link.href = uri + base64(workbookXML);
link.download = wbname || 'Workbook.xls';
link.target = '_blank';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
})();

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>

Download HTML table to Excel by using Javascript

The getElementsByTagName() function returns an array so you have to get the first element.

let tableHTML = tableSelect[0].outerHTML.replace(/ /g, '%20');

Exporting HTML table to Excel using Javascript

On 2016-07-12, Microsoft pushed a security update for Microsoft Office. One of the effects of this update was to prevent HTML files from non-trusted domains from being opened by Excel, because they cannot be opened in Protected mode.

There is ALSO a registry setting that prevents Excel from opening files with the .XLS file extension whose contents do not match the official XLS file format, though it defaults to 'warn', not 'deny'.

Prior to this change, it was possible to save HTML data to a file with an XLS extension, and Excel would open it correctly - possibly giving a warning first that the file did not match the Excel format, depending on the user's value for the ExtensionHardening registry key (or related config values).

Microsoft has made a knowledge-base entry about the new behavior with some suggested workarounds.

Several web applications that previously relied on exporting HTML files as XLS have run into trouble as a result of the update - SalesForce is one example.

Answers from before July 12th 2016 to this and similar questions are likely to now be invalid.

It's worth noting that files produced ON THE BROWSER from remote data do not fall afoul of this protection; it only impedes files downloaded from a remote source that is not trusted. Therefore one possible approach is to generate the .XLS-labelled HTML file locally on the client.

Another, of course, is to produce a valid XLS file, which Excel will then open in Protected mode.

UPDATE: Microsoft has released a patch to correct this behavior: https://support.microsoft.com/en-us/kb/3181507



Related Topics



Leave a reply



Submit