I have this jquery function that works, except I need to add something. I have a filter setup on the table to hide columns. When I hide columns and click export it uses all fields regardless. How would I only export the table columns that are not hidden?
var write_to_excel = (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]--></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))
}
})()
I think I need something like.
if($(table).display.style!="none")
Thanks in advance.
The easiest way is to just remove the columns from the table before you pass it in to the format function. Since you're throwing away the page anyway to navigate to a data URI, it's not important to preserve the hidden columns.
The way you remove the hidden columns depends on how the columns are made hidden in the first place. If it's just by applying display:none to the hidden TDs, you can do it by making this change to the code.
return function(table, name) {
if (!table.nodeType)
table = document.getElementById(table);
$(table).find(':not(:visible)').remove();
var ctx = {
worksheet: name || 'Worksheet',
table: table.innerHTML
};
window.location.href = uri + base64(format(template, ctx));
};
Or you could use simple jQuery to export the content in a table to Excel format, using window.open() method. This will also solve the display:none issue in a simpler manner :)
window.open(MIMEtype,replace);
MIMEtype: Optional. The type of document you are writing to. Default value is "text/html".
replace: Optional. If set, the history entry for the new document inherits the history entry from the document which opened this document.
Below jQuery code will export the table data to Excel.
$("#btnExport").click(function(e) {
window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
e.preventDefault();
});
If your table HTML contain special characters, then before sending the html to excel it must be encoded.
$("#btnExport").click(function(e) {
window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('#dvData').html()));
e.preventDefault();
});
jsFiddle Example - http://jsfiddle.net/jquerybyexample/xhYcD/
$("#btnExport").click(function(e) {
window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('#export').html()));
});
