ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,541 questions
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 36%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELH%3C/text%3E%3C/svg%3E)
Hi @RAVI,
After repeated testing, I have tested the code that meets your needs. You can refer to it.
<script type="text/javascript">
$(document).ready(function () {
var thArray = [];
$('#list > thead > tr > th').each(function () {
thArray.push($(this).text())
})
var rowCount = $('table#list tbody tr').length;
sessionStorage.setItem("rowCount", rowCount);
// Remove the formatting to get integer data for summation
var intVal = function (i) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '') * 1 :
typeof i === 'number' ?
i : 0;
};
$('#list').DataTable({
"ordering": false,
dom: 'Bfrtip',
"buttons": [{
extend: 'excel',
footer: true,
// title: "Test",
exportOptions: {
columns: [0, 1, 2, 3],
format: {
header: function (data, index, column) {
return thArray[index]
}
}
}
}
],
lengthMenu: [
[2000, 1500, 1000, 500, -1],
[2000, 1500, 1000, 500, 'All'],
],
initComplete: function () {
this.api().columns([2]).every(function () {
var title = this.header();
var colTitle = this.header().innerHTML;
//replace spaces with dashes
title = $(title).html().replace(/[\W]/g, '-');
var column = this;
var select = $('<select id="' + title + '" class="select2" ></select>')
.appendTo($(column.header()).empty())
.on('change', function () {
//Get the "text" property from each selected data
//regex escape the value and store in array
var data = $.map($(this).select2('data'), function (value, key) {
return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
});
//if no data selected use ""
if (data.length === 0) {
data = [""];
}
//join array into string with regex or (|)
var val = data.join('|');
//search for the option(s) selected
column
.search(val ? val : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {
select.append('<option value="' + d + '">' + d + '</option>');
});
//use column title as selector and placeholder
$('#' + title).select2({
multiple: true,
closeOnSelect: false,
width: '100%',
placeholder: "" + colTitle
});
//initially clear select otherwise first option is selected
$('.select2').val(null).trigger('change');
});
},
footerCallback: function (tfoot, data, start, end, display) {
let api = this.api();
api.column(0).footer().innerHTML = "GRAND TOTAL";
// Total over all pages
total = api
.column(2, { search: 'applied' })
.data()
.reduce((a, b) => intVal(a) + intVal(b), 0);
// Update footer
api.column(2).footer().innerHTML = total;
total1 = api
.column(3, { search: 'applied' })
.data()
.reduce((a, b) => intVal(a) + intVal(b), 0);
// Update footer
api.column(3).footer().innerHTML = total1;
},
"drawCallback": function (settings) {
var api = this.api();
var rows = api.rows({ page: 'current' }).nodes();
var colonne = api.row(0).data().length;
var totale = new Array();
totale['Totale'] = new Array();
var groupid = -1;
var subtotale = new Array();
var b = api.column(0).data().unique().reverse();
var last = b[0];
api.column(0, { page: 'current' }).data().each(function (group, i) {
if (last !== group) {
groupid++;
$(rows).eq(i-1).after(
'<tr class="group" style="font-weight: bold;background:forestgreen; text-align: left;border: 1px solid black"><td colspan="2">' + last + '</td></tr>'
);
last = group;
}
val = api.row(api.row($(rows).eq(i)).index()).data(); //current order index
$.each(val, function (index2, val2) {
if (typeof subtotale[groupid] == 'undefined') {
subtotale[groupid] = new Array();
}
if (typeof subtotale[groupid][index2] == 'undefined') {
subtotale[groupid][index2] = 0;
}
if (typeof totale['Totale'][index2] == 'undefined') { totale['Totale'][index2] = 0; }
valore = Number(val2);
subtotale[groupid][index2] += valore;
totale['Totale'][index2] += valore;
});
});
$('tbody').find('.group').each(function (i, v) {
$(this).find('td:first').append($('<span />', { 'class': 'rowCount-grid' }));
var subtd = '';
for (var a = 2; a < colonne; a++) {
subtd += '<td>' + subtotale[i][a] + '</td>';
}
$(this).append(subtd);
});
}
});
});
</script>
Best regards,
Lan Huang
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.