使用其他行将jquery数据表导出为excel不起作用 [英] Exporting jquery datatable to excel with additional rows is not working IE
问题描述
我正在尝试使用jquery导出按钮选项将数据表导出到Excel工作表。我希望在excel文件中的表数据之前添加其他行。我在小提琴 https://jsfiddle.net/xevpdeo1/17/ 中做了类似的演示。它在Chrome和Firefox中运行良好,但在IE中却没有。在IE中,表数据之前的其他行在导出后为空。有人可以帮我解决这个问题吗?提前致谢
I am trying to export datatable using jquery export button options to excel sheet. And i want additional rows to be added before the table data in excel file. I made a similar demo in fiddle https://jsfiddle.net/xevpdeo1/17/ . It is working fine in Chrome and Firefox but not in IE. In IE the additional rows before the table data is empty after exporting. Can someone help me out with this? Thanks in advance
$(document).ready(function() {
var xlsBuilder = {
filename: 'business-group-sharers-',
sheetName: 'business-group-sharers-',
customize: function(xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var downrows = 4;
var clRow = $('row', sheet);
var msg;
//update Row
clRow.each(function() {
var attr = $(this).attr('r');
var ind = parseInt(attr);
ind = ind + downrows;
$(this).attr("r", ind);
});
// Update row > c
$('row c ', sheet).each(function() {
var attr = $(this).attr('r');
var pre = attr.substring(0, 1);
var ind = parseInt(attr.substring(1, attr.length));
ind = ind + downrows;
$(this).attr("r", pre + ind);
});
function Addrow(index, data) {
msg = '<row r="' + index + '">';
for (var i = 0; i < data.length; i++) {
var key = data[i].k;
var value = data[i].v;
msg += '<c t="inlineStr" r="' + key + index + '">';
msg += '<is>';
msg += '<t>' + value + '</t>';
msg += '</is>';
msg += '</c>';
}
msg += '</row>';
return msg;
}
var r1 = Addrow(1, [{
k: 'A',
v: 'Export Date :'
}, {
k: 'B',
v: '10-Jan-2017'
}]);
var r2 = Addrow(2, [{
k: 'A',
v: 'Account Name :'
}, {
k: 'B',
v: 'Melvin'
}]);
var r3 = Addrow(3, [{
k: 'A',
v: 'Account Id :'
}, {
k: 'B',
v: '021456321'
}]);
sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + r3 + sheet.childNodes[0].childNodes[1].innerHTML;
},
exportOptions: {
columns: [0, 1, 2, 3]
}
}
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [
$.extend(true, {}, xlsBuilder, {
extend: 'excel'
})
]
});
});
推荐答案
这里有解决方案。你必须使用javascript进行操作。 IE不支持innerHTML属性
here you have the solution. You have to manipulate with javascript. IE does not support innerHTML property
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var numrows = 4;
//update Row
clR.each(function () {
var attr = $(this).attr('r');
var ind = parseInt(attr);
ind = ind + numrows;
$(this).attr("r", ind);
});
// Create row before data
$('row c ', sheet).each(function (index) {
var attr = $(this).attr('r');
var pre = attr.substring(0, 1);
var ind = parseInt(attr.substring(1, attr.length));
ind = ind + numrows;
$(this).attr("r", pre + ind);
});
function Addrow(index, data) {
var row = sheet.createElement('row');
row.setAttribute("r", index);
for (i = 0; i < data.length; i++) {
var key = data[i].key;
var value = data[i].value;
var c = sheet.createElement('c');
c.setAttribute("t", "inlineStr");
c.setAttribute("s", "2");
c.setAttribute("r", key + index);
var is = sheet.createElement('is');
var t = sheet.createElement('t');
var text = sheet.createTextNode(value)
t.appendChild(text);
is.appendChild(t);
c.appendChild(is);
row.appendChild(c);
}
return row;
}
/*function Addrow(index, data) {
msg = '<row r="' + index + '">'
for (i = 0; i < data.length; i++) {
var key = data[i].key;
var value = data[i].value;
msg += '<c t="inlineStr" s="52" r="' + key + index + '">';
msg += '<is>';
msg += '<t >' + value + '</t>';
msg += '</is>';
msg += '</c>';
}
msg += '</row>';
return msg;
}*/
var r1 = Addrow(1, [{ key: 'A', value: '' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);
var r2 = Addrow(2, [{ key: 'A', value: '' }, { key: 'B', value: 'Report Date' }, { key: 'C', value: '' : '') }]);
var r3 = Addrow(3, [{ key: 'A', value: '' }, { key: 'B', value: ':' }, { key: 'C', value: '' }]);
var r4 = Addrow(4, [{ key: 'A', value: '' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);
var sheetData = sheet.getElementsByTagName('sheetData')[0];
sheetData.insertBefore(r4,sheetData.childNodes[0]);
sheetData.insertBefore(r3,sheetData.childNodes[0]);
sheetData.insertBefore(r2,sheetData.childNodes[0]);
sheetData.insertBefore(r1,sheetData.childNodes[0]);
这里有解决其他问题的方法
Here you have the Solution to your other issue
$(document).ready(function() {
var xlsBuilder = { filename: 'business-group-sharers-',
sheetName: 'business-group-sharers-',
customize: function(xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var cellsColumnE = sheet.querySelectorAll('row c[r^="E"]');
for(var i=1; i < cellsColumnE.length; i++){
var attr = cellsColumnE[i].getAttribute('r');
cellsColumnE[i].removeAttribute('s');
cellsColumnE[i].setAttribute('t', "inlineStr");
var value = (parseFloat(cellsColumnE[i].getElementsByTagName('v')[0].textContent) * 100) .toFixed(2);
var is = sheet.createElement('is');
var t = sheet.createElement('t');
var text = sheet.createTextNode(value + "%");
cellsColumnE[i].removeChild(cellsColumnE[i].lastChild);
t.appendChild(text);
is.appendChild(t);
cellsColumnE[i].appendChild(is);
}
},
exportOptions: {
columns: [0, 1, 2, 3, 5]
}
}
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [
$.extend(true, {}, xlsBuilder, {
extend: 'excel'
})
]
});
});
这篇关于使用其他行将jquery数据表导出为excel不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!