I have structured my project in the following manner: https://jsfiddle.net/Eufragio/u342qgoz/1/
When I export to excel, I require a better layout or a more visible method to display my results
$(document).ready( function () {
var table = $('#example').DataTable({
dom: 'Btirp',
buttons: [{
extend: 'csvHtml5',
text: 'CSV',
filename: 'csv_file',
footer: true
},
{
extend: 'excelHtml5',
text: 'Excel',
filename: 'excel_file',
footer: true
}],
//Total General
"footerCallback": function (row, data, start, end, display) {
var api = this.api(),
data;
// Function to convert data to integer for summation
var intVal = function (i) {
return typeof i === 'string' ?
i.replace(/[\L,]/g, '') * 1 :
typeof i === 'number' ?
i : 0;
};
/*
// Total over all pages
total = api
.column(5)
.data()
.reduce(function(a, b) {
return intVal(a) + intVal(b);
}, 0);
// Total over this page
pageTotal = api
.column(5, {
page: 'current'
})
.data()
.reduce(function(a, b) {
return intVal(a) + intVal(b);
}, 0);
// Update footer
$(api.column([5, 3]).footer()).html(
// '' + pageTotal + ' ( L' + total + ' total)'
//'' + total.toFixed(2)
'' + total
);
*/
// Total over all pages
total = api
.column(3)
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Total over this page
pageTotal = api
.column(3, {
page: 'current'
})
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Update footer
$(api.column(3).footer()).html(
// '' + pageTotal + ' ( L' + total + ' total)'
//'' + total.toFixed(2)
'' + total
);
},
"columnDefs": [{
"visible": false,
"targets": 2
}],
"order": [
[2, 'asc']
],
"displayLength": 25,
"drawCallback": function (settings) {
var api = this.api();
var rows = api.rows({
page: 'all'
}).nodes();
var last = null;
// Function to convert data to integer for summation
var intVal = function (i) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '') * 1 :
typeof i === 'number' ?
i : 0;
};
var groupTotal = {};
api.column(2, {
page: 'all'
}).data().each(function (group, i) {
group_assoc = group.replace(' ', "_");
console.log(group_assoc);
if (typeof groupTotal[group_assoc] != 'undefined') {
groupTotal[group_assoc] = groupTotal[group_assoc] + intVal(api.column(5).data()[i]);
/*
$(api.column(2).footer()).html(
'' + total[group_assoc]
);
*/
} else {
groupTotal[group_assoc] = intVal(api.column(5).data()[i]);
}
if (last !== group) {
$(rows).eq(i).before(
'<tr class="group"><td colspan="4">' + group + '</td><td class="' + group_assoc + '"></td></tr>'
);
last = group;
}
});
var footerText = [];
var footerTotal = [];
for (var key in groupTotal) {
$("." + key).html("L" + groupTotal[key].toFixed(2));
footerText.push(key);
footerTotal.push("L" + groupTotal[key].toFixed(2));
}
$(api.column(4).footer()).html(
footerText.join('<br>')
);
$(api.column(5).footer()).html(
footerTotal.join('<br>')
);
}
});
});
My issue arises when I export to excel, as the generated results appear like this: https://i.sstatic.net/f8Yg2.png
The desired result that I am aiming for can be seen here: https://i.sstatic.net/3sfxN.png
How can I achieve this desired result? Do you have any recommendations on alternative methods to display the data?