I'm using this code:
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-:office:office" xmlns:x="urn:schemas-microsoft-:office:excel" xmlns=""><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))
}
})()
Here is my submit button:
<td><input type="button" onclick="tableToExcel('project_table', 'W3C Example Table')" value="Export to Excel"></td>
The problem I'm having is some of my inputs are dropdowns and inputs but they are hidden on the time of building the excel file. So when the excel files first builds you'll see all the data and within 2 seconds all the data that has the hidden drop downs and inputs behind them go blank and render the html.
Pic1:
Pic2:
I'm trying to use textExtraction but it's not working for me.
$('#project_table').tablesorter({
textExtraction: myTextExtraction
});
//todo: try getting the dataTable to work...
// it might be way better, just not sure how it would handle
// adding unrelated rows on the fly (ments)
//$('#project_table').dataTable();
var myTextExtraction = function(node)
{
var elem = $(node);
var theVal = null;
if (elem.hasClass('edit')) {
elem.children().each(function() {
if ($(this).css('display') != 'none') {
if ($(this).is('span')) {
theVal = $(this).text();
} else { //all other element types (input, select, etc)
theVal = $(this).val();
}
}
});
} else {
theVal = elem.text();
}
//console.log(theVal);
var c = node.childNodes.length;
if (c == 1) {
theVal = node.innerHTML.trim();
} else if (c == 5) {
theVal = node.childNodes[3].innerHTML.trim();
}
//console.log(theVal);
return theVal;
}
Can anyone lend a hand, thank you.
Also if anyone knows an IE8 fix, it seems to only work in chrome and FF.
I'm using this code:
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-:office:office" xmlns:x="urn:schemas-microsoft-:office:excel" xmlns="http://www.w3/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))
}
})()
Here is my submit button:
<td><input type="button" onclick="tableToExcel('project_table', 'W3C Example Table')" value="Export to Excel"></td>
The problem I'm having is some of my inputs are dropdowns and inputs but they are hidden on the time of building the excel file. So when the excel files first builds you'll see all the data and within 2 seconds all the data that has the hidden drop downs and inputs behind them go blank and render the html.
Pic1:
Pic2:
I'm trying to use textExtraction but it's not working for me.
$('#project_table').tablesorter({
textExtraction: myTextExtraction
});
//todo: try getting the dataTable to work...
// it might be way better, just not sure how it would handle
// adding unrelated rows on the fly (ments)
//$('#project_table').dataTable();
var myTextExtraction = function(node)
{
var elem = $(node);
var theVal = null;
if (elem.hasClass('edit')) {
elem.children().each(function() {
if ($(this).css('display') != 'none') {
if ($(this).is('span')) {
theVal = $(this).text();
} else { //all other element types (input, select, etc)
theVal = $(this).val();
}
}
});
} else {
theVal = elem.text();
}
//console.log(theVal);
var c = node.childNodes.length;
if (c == 1) {
theVal = node.innerHTML.trim();
} else if (c == 5) {
theVal = node.childNodes[3].innerHTML.trim();
}
//console.log(theVal);
return theVal;
}
Can anyone lend a hand, thank you.
Also if anyone knows an IE8 fix, it seems to only work in chrome and FF.
Share Improve this question edited Nov 3, 2014 at 14:41 Taryn 248k57 gold badges370 silver badges408 bronze badges asked Nov 6, 2013 at 22:22 Head WayHead Way 3232 gold badges4 silver badges16 bronze badges 6-
4
Restrictions on
data:uri use in IE are covered here: basically your current approach will not work on IE8/9 (or 10?). An alternative would be to POST the content to a server and have it return the content with the relevant headers. – Tim Williams Commented Nov 6, 2013 at 23:23 - Any idea why it's turning the drop down fields to blank when it's merged into excel? I posted a picture above top left part of image one. – Head Way Commented Nov 7, 2013 at 20:41
- As @TimWilliams pointed out - you could save yourself a ton of headaches by simply hitting the server and serving back the headers needed to serve up a file that Excel can open. – scunliffe Commented Nov 12, 2013 at 22:25
- @TimWilliams Good point about the URI/base64 limits in IE...it can't handle URI's over 32kb. – Michael Commented Nov 13, 2013 at 4:34
- @HeadWay are you using PHP, because if so, PHPExcel is an awesome library written to export server tables to Excel (phpexcel.codeplex.). Something you ought to consider also is if you're using URI's and the user doesn't have the correct version of the Excel App (say 2003 or if 2013 es out with a different protocol), you client side code wouldn't work. – Michael Commented Nov 13, 2013 at 4:36
3 Answers
Reset to default 1I remand to use DataTables jQuery plugin which manage sort, search, ajax, export and more. See http://datatables
If you can't use the server, an alternative approach may be to add a button to copy the table to a user's clipboard for pasting into excel. This side steps the header problem and gives you a similar level of background control over the extraction.
You can check out a lib like clippy to assist with this (github uses it for repo url copying).
you can use HTML table export
http://ngiriraj./pages/htmltable_export/demo.php#