数据表上的行分组和小计 [英] RowGrouping and Subtotal on Datatable
本文介绍了数据表上的行分组和小计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在数据表上使用 RowGrouping.对于每个组，我需要对某些列(Encum、Paid、Balance)和 Total 对每列的所有行(Encum、Paid、Balance)进行小计.我对这个数据表很陌生，无法达到我需要的结果.这是我正在使用的代码.我需要显示其中 3 列的 $value 以及标题处的小计.请在这里帮助我，jsfiddle 中的代码:jsfiddle.net/6r2pjbg8
I am using RowGrouping on Datatable. For each Group I need to do Subtotal on some of the columns (Encum, Paid, Balance) and Total on all of the rows for each Column (Encum, Paid, Balance). I am quite new to this Datatable and not able to achieve the result that I need. Here is the code I'm using. I need to display $value of 3 of those columns and also the subtotal at the header.
Please help me out here, code in jsfiddle: jsfiddle.net/6r2pjbg8
var table = S$('#EncumbranceSummaryTable').DataTable({
"columnDefs": [
{ "visible": false, "targets":0 }
],
"stateSave": false,
"stateDuration": 60*60*24*365,
"data" : POnbrsDetails,
"columns":[
{"sTitle": "PO #", "data": "PO_Nbr" },
{"sTitle": "FY", "data": "FY" },
{"sTitle": "LN", "data": "LN" },
{"sTitle": "F/O/A","data":"FOA"},
{"sTitle": "Project ID", "data": "Proj_ID" },
{"sTitle": "Srce Type", "data": "Srce_Type" },
{"sTitle": "Encumbrance","data":"Encum_Amt"},
{"sTitle": "Paid","data":"Paid"},
{"sTitle": "Balance", "data": "Balance" }
],
"drawCallback": function ( settings ) {
var api = this.api();
var rows = api.rows( {page:'current'} ).nodes();
var last=null;
var colonne = api.row(0).data().length;
var totale = new Array();
totale['Totale']= new Array();
var groupid = -1;
var subtotale = new Array();
api.column(0, {page:'current'} ).data().each( function ( group, i ) {
debugger;
if ( last !== group ) {
S$(rows).eq( i ).before(
'<tr class="group"><td colspan="5">'+group+'</td></tr>'
);
last = group;
}
//sub total
val = api.row(api.row($(rows).eq( i )).index()).data(); //current order index
$.each(val,function(index2,val2){
debugger;
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.replace('€',"").replace('.',"").replace(',',"."));
subtotale[groupid][index2] += 1;
totale['Totale'][index2] += 2;
});
} );
$('tbody').find('.group').each(function (i,v) {
var rowCount = $(this).nextUntil('.group').length;
//$(this).find('td:first').append($('<span />', { 'class': 'rowCount-grid' }).append($('<b />', { 'text': ' ('+rowCount+')' })));
var subtd = '';
for (var a=0;a<3;a++)
{
subtd += '<td>'+ subtotale[i][a] +'</td>';
}
$(this).append(subtd);
});
}
});
推荐答案
这对您有用吗?
var POdata = [{
"PO_Nbr": "3000202173",
"FY": 2015,
"LN": "1.1",
"FOA": "2500/T7935002/T790225",
"Proj_ID": "TLRR922B",
"Srce_Type": "6251",
"Encum_Amt": 66033.62,
"Paid": 36508.35,
"Balance": 29525.27
}, {
"PO_Nbr": "3000202173",
"FY": 2015,
"LN": "1.2",
"FOA": "2600/T7935002/T790226",
"Proj_ID": "TLRR922B",
"Srce_Type": "6251",
"Encum_Amt": 212824.56,
"Paid": 117665.45,
"Balance": 95159.11
}, {
"PO_Nbr": "3000143638",
"FY": 2014,
"LN": "1.1",
"FOA": "2500/T7935002/T790225",
"Proj_ID": "TLRR922B",
"Srce_Type": "6251",
"Encum_Amt": 32350.84,
"Paid": 32350.84,
"Balance": 0
}, {
"PO_Nbr": "3000143638",
"FY": 2014,
"LN": "1.2",
"FOA": "2600/T7935002/T790226",
"Proj_ID": "TLRR922B",
"Srce_Type": "6251",
"Encum_Amt": 104265.84,
"Paid": 104265.84,
"Balance": 0
}, {
"PO_Nbr": "3000079267",
"FY": 2013,
"LN": "1.1",
"FOA": "2500/T7935002/T790225",
"Proj_ID": "TLRR922B",
"Srce_Type": "9025",
"Encum_Amt": 21339.91,
"Paid": 21339.91,
"Balance": 0
}, {
"PO_Nbr": "3000079267",
"FY": 2013,
"LN": "1.2",
"FOA": "2600/T7935002/T790226",
"Proj_ID": "TLRR922B",
"Srce_Type": "9025",
"Encum_Amt": 68778.06,
"Paid": 68778.06,
"Balance": 0
}, {
"PO_Nbr": "3000038524",
"FY": 2012,
"LN": "1.1",
"FOA": "2500/T7935002/T790225",
"Proj_ID": "TLRR922B",
"Srce_Type": "9025",
"Encum_Amt": 12752.54,
"Paid": 12752.54,
"Balance": 0
}, {
"PO_Nbr": "3000038524",
"FY": 2012,
"LN": "1.2",
"FOA": "2600/T7935002/T790226",
"Proj_ID": "TLRR922B",
"Srce_Type": "9025",
"Encum_Amt": 41101.1,
"Paid": 41101.1,
"Balance": 0
}];
var table = $('#EncumbranceSummaryTable').DataTable({
"data": POdata,
"columns": [
{
"title": "PO #",
"data": "PO_Nbr",
"visible": false
}, {
"title": "FY",
"data": "FY"
}, {
"title": "LN",
"data": "LN",
"sortable": false
}, {
"title": "F/O/A",
"data": "FOA",
"sortable": false
}, {
"title": "Project ID",
"data": "Proj_ID",
"sortable": false
}, {
"title": "Srce Type",
"data": "Srce_Type",
"sortable": false
}, {
"title": "Encumbrance",
"data": "Encum_Amt",
"sortable": false
}, {
"title": "Paid",
"data": "Paid",
"sortable": false
}, {
"title": "Balance",
"data": "Balance",
"sortable": false
}
],
"drawCallback": function (settings) {
var api = this.api();
var rows = api.rows({
page: 'current'
}).nodes();
var last = null;
api.column(0, {
page: 'current'
}).data().each(function (group, i) {
if (last !== group) {
$(rows).eq(i).before(
$("<tr></tr>", {
"class": "group",
"data-id": group
}).append($("<td></td>", {
"colspan": 5,
"class": "pocell",
"text": "PO # " + group
})).append($("<td></td>", {
"id": "e" + group,
"class": "noCount",
"text": "0.00"
})).append($("<td></td>", {
"id": "p" + group,
"class": "noCount",
"text": "0.00"
})).append($("<td></td>", {
"id": "b" + group,
"class": "noCount",
"text": "0.00"
})).prop('outerHTML'));
last = group;
}
val = api.row(api.row($(rows).eq(i)).index()).data();
$("#e" + val.PO_Nbr).text(parseFloat($("#e" + val.PO_Nbr).text()) + parseFloat(val.Encum_Amt));
$("#p" + val.PO_Nbr).text(parseFloat($("#p" + val.PO_Nbr).text()) + parseFloat(val.Paid));
$("#b" + val.PO_Nbr).text(parseFloat($("#b" + val.PO_Nbr).text()) + parseFloat(val.Balance));
});
},
"footerCallback": function (row, data, start, end, display) {
var api = this.api();
$(api.column(6).footer()).html(
api.column(6).data().reduce(function (a, b) {
return parseFloat(a) + parseFloat(b);
})
);
$(api.column(7).footer()).html(
api.column(7).data().reduce(function (a, b) {
return parseFloat(a) + parseFloat(b);
})
);
$(api.column(8).footer()).html(
api.column(8).data().reduce(function (a, b) {
return parseFloat(a) + parseFloat(b);
})
);
}
});
工作 JSFiddle.
这篇关于数据表上的行分组和小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
