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'
})
]
});
});
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'
})
]
});
});
As @Jonatan Perez put it, Internet Explorer does not support innerHTML, nor insertAdjacentHTML for that matter. And other additional problems can occur.
To sum it up, you have to switch from this:
// Get the sheet objects
var sSh = xlsx.xl['styles.xml'];
var styleSheet = sSh.childNodes[0];
numFmts = styleSheet.childNodes[0];
cellXfs = styleSheet.childNodes[5];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
// Set a custom format ID
var formatID = 300;
// Create a custom number format (percent). Note that using " instead of \" in the format code does not work for Internet Explorer!
var newNumberFormat = '<numFmt formatCode="0.0" numFmtId="' + formatID + '"/>';
// Create a custom style based on this number format (bold centered rounded)
var newStyle =
'<xf numFmtId="' + formatID + '" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1">horizontal="center" />>';
// Append the new format next to the other ones
numFmts.innerHTML += newNumberFormat;
// Append the style next to the other ones
cellXfs.childNodes[5].innerHTML += newStyle;
// Use the new style on cell A1
$('c[r=A1]', sheet).attr('s', cellXfs.length);
To this:
// Get the sheet objects
var sSh = xlsx.xl['styles.xml'];
var styleSheet = sSh.childNodes[0];
numFmts = styleSheet.childNodes[0];
cellXfs = styleSheet.childNodes[5];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
// Set a custom format ID
var formatID = 300;
/// In what follows, use "createElementNS" everytime the attribute has an uppercase letter; otherwise, Chrome and Firefox will break the XML by lowercasing it
// Using this instead of "" (required for Excel 2007+, not for 2003)
var ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
// Create a custom number format
var newNumberFormat = document.createElementNS(ns, "numFmt");
newNumberFormat.setAttribute("numFmtId", formatID);
newNumberFormat.setAttribute("formatCode", "0.0");
// Append the new format next to the other ones
numFmts.appendChild(newNumberFormat);
// Create a custom style
var lastStyleNum = $('cellXfs xf', sSh).length - 1;
var styleNum = lastStyleNum + 1;
var newStyle = document.createElementNS(ns, "xf");
// Customize style
newStyle.setAttribute("numFmtId", formatID);
newStyle.setAttribute("fontId", 2);
newStyle.setAttribute("fillId", 0);
newStyle.setAttribute("borderId", 0);
newStyle.setAttribute("applyFont", 1);
newStyle.setAttribute("applyFill", 1);
newStyle.setAttribute("applyBorder", 1);
newStyle.setAttribute("xfId", 0);
newStyle.setAttribute("applyNumberFormat", 1);
// Alignment (optional)
var align = document.createElementNS(ns, "alignment");
align.setAttribute("horizontal", "center");
newStyle.appendChild(align);
// Append the style next to the other ones
cellXfs.appendChild(newStyle);
// Use the new style on "Age" column
$('row:not(:eq(1)) c[r^=D]', sheet).attr('s', styleNum);
LIVE DEMO
Solution inspired by Raghul and rf1234 in the forums of DataTables.net:
https://www.datatables.net/forums/discussion/comment/116614/#Comment_116641
https://datatables.net/forums/discussion/36045/excel-export-add-rows-and-data#Comment_103911
Also based on the following SO answers:
Keep uppercase using attr() with jquery (case sensitive)
Create XML DOM Element while keeping case sensitivity
