I would like to add a second <tr> to the footer of my datatable in order to display the grand total (after doing some sums).

All the calculations are already working, I just want to inject the value of rowSum when j >= 12 in the 2nd row of the footer.

Concept of the desired result (cropped image):

This is what I've tried:

1. ✓ Adding a second <tr> to the datatable (working)
2. ✗ Inject the value of rowSum to the last column of the second row of the footer (I guess I am doing something wrong here)

And this is the part of the code that I think is wrong:

$('#example > tfoot  > tr').each(function(index, tr) {
    var rowSum = 0;
    for (let j = 0; j < 12; j  ) {
        if (j >= 2) {
            var tdCellValue = numberFromString(tr.cells[j].innerText);
            rowSum = rowSum   tdCellValue;
        }
        if (j >= 11) {
            tr.cells[12].innerText = rowSum;
            tr[1].cells[12].innerText = rowSum;
        }
    }
});

Here I tried to access to the second row of the footer: tr[1].cells[12].innerText = rowSum; and I think this is where I made the mistake.

DEMO JS BIN

Does anyone know how can I access the second row of the datatable footer and inject the value properly?

CodePudding user response：

Just replace your JS code with the following. Here is Demo of the working file.

<script>
    $(document).ready(function() {
    
        
        var DT1 = $('#example').DataTable({
    
            columnDefs: [{
                orderable: false,
                className: 'select-checkbox',
                targets: 0,
            }],
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            order: [
                [1, 'asc']
            ],
            "language": {
                "info": "Showing _START_ to _END_ of _TOTAL_ Projects",
                "infoEmpty": "Showing 0 to 0 of 0 Projects",
                "infoFiltered": "(filtered from _MAX_ total Projects)"
            },
            "pagingType": "numbers",
            dom: 'rtip',
            initComplete: function(settings, json) {
                // calculate the sum when table is first created:
                doSum();
            }
        });
    
        $('#example').on('draw.dt', function() {
            // re-calculate the sum whenever the table is re-displayed:
            doSum();
        });
        $(".selectAll").on("click", function(e) {
            if ($(this).is(":checked")) {
                DT1.rows().select();
            } else {
                DT1.rows().deselect();
            }
        });
        
        // This provides the sum of all records:
        function doSum() {
            // get the DataTables API object:
            var table = $('#example').DataTable();
            // set up the initial (unsummed) data array for the footer row:
            var totals = ['', 'Total:', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
            // iterate all rows - use table.rows( {search: 'applied'} ).data()
            // if you want to sum only filtered (visible) rows:
            totals = table.rows().data()
                // sum the amounts:
                .reduce(function(sum, record) {
                    for (let i = 2; i <= 12; i  ) {
                        sum[i] = sum[i]   numberFromString(record[i]);
                    }
                    return sum;
                }, totals);
            // place the sum in the relevant footer cell:
            for (let i = 1; i <= 12; i  ) {
                var column = table.column(i);
                $(column.footer()).html(formatNumber(totals[i]));
            }
            
            
            $('#example > tbody  > tr').each(function(index, tr) { 
         var rowSum=0;
            for(let j=0; j<12; j  )
            {               
                if(j>=2){
                 var tdCellValue=  numberFromString(tr.cells[j].innerText);
                 rowSum=rowSum tdCellValue;
                }
                if(j >=11){
                 tr.cells[12].innerText=rowSum;          
                }
            }
        });
        var grandTotal=0;
       $('#example > tfoot  > tr').each(function(index, tr) {
              if(index<1){
              grandTotal=0;
                var rowSum = 0;
                for (let j = 0; j < 12; j  ) {
                    if (j >= 2) {
                        var tdCellValue = numberFromString(tr.cells[j].innerText);
                        rowSum = rowSum   tdCellValue;
                    }
                    if (j >= 11) {
                        tr.cells[12].innerText = rowSum;                    
                        grandTotal=rowSum;
                    }
                }
              }else{
                 tr.cells[11].innerText = "Grand Total";
                 tr.cells[12].innerText = grandTotal;
              }
            });
        }
    
        function numberFromString(s) {
            return typeof s === 'string' ?
                s.replace(/[\$,]/g, '') * 1 :
                typeof s === 'number' ?
                s : 0;
        }
    
        function formatNumber(n) {
            return n.toLocaleString(); // or whatever you prefer here
        }
    });
    </script>

CodePudding user response：

If you want to calculator value of last column, you need get sum of all cells in this row ( not 2 rows with $('#example > tfoot > tr').each -> in html , you have 2 tr )

$('#example > tfoot  > tr').each(function(index, tr) {
    if (index == 0) {
        var rowSum = 0;
        for (let j = 0; j < totalCol; j  ) {
            if (j >= 2 && j < 12) {
                var tdCellValue = numberFromString(tr.cells[j].innerText);
                rowSum = rowSum   tdCellValue;
            }
            if (j >= 12) {
                tr.cells[12].innerText = rowSum;
                //    tr[1].cells[12].innerText = rowSum;
            }
        }
    }
});