CODEWITHSUNDEEP
datatables
Cheknov
Cheknov

Reputation: 2082

Total of multiple columns using datatables footer callback

I am trying to implement a datatable with multiple columns containing numerical values.

I would like to show the sum of each column in the table footer.

I got some inspiration from here.

LIVE JS BIN DEMO

Upvotes: 5

Views: 13264

Answers (2)

artico
artico

Reputation: 366

Simple code, focus in the "footerCallback" of the DataTable:

var table = $('#example').DataTable( {
    data: [
        ...
    ],
    columns: [
        ...
    ],
    footerCallback: function (row, data, start, end, display) {
        var api = this.api();

        // Sum each of 4 columns, beginning with col[0]:
        for(var i=0; i<=3; i++) {
            let sum = api.column(i).data().sum();
            $(api.column(i).footer()).html(sum);
        }
    }
});

Upvotes: 2

andrewJames
andrewJames

Reputation: 21908

The following is a minimal solution focusing on the creation of multiple column totals in the footer row. You would need to re-apply your CSS, checkboxes and overall HTML structure:

$(document).ready(function() {

  var table = $('#example').DataTable( {
    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();
  } );

  // 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 = ['','Totals',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 <= 8; i++) {
          sum[i] = sum[i] + numberFromString(record[i]);
        } 
        return sum;
      }, totals ); 
    // place the sum in the relevant footer cell:
    for (let i = 1; i <= 8; i++) {
      var column = table.column( i );
      $( column.footer() ).html( formatNumber(totals[i]) );
    }
  }

  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
  }

} );
<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
                                                <thead>
        <tr>
            <th class="text-center"><input type="checkbox" class="selectAll" name="selectAll" value="all"></th>
            <th>ID</th>
            <th>Fee1</th>
            <th>Fee2</th>
            <th>Fee3</th>
            <th>Fee4</th>
            <th>Fee5</th>
            <th>Fee6</th>
            <th>Sub Total</th>
            <th>Copy</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td></td>
            <td>AF-01</td>
            <td>100,001</td>
            <td>100,002</td>
            <td>100,003</td>
            <td>100,004</td>
            <td>100,005</td>
            <td>100,006</td>
            <td>100,007</td>
            <td></td>
        </tr>
        <tr>
              <td></td>
              <td>AF-01</td>
              <td>100,000</td>
              <td>100,000</td>
              <td>100,000</td>
              <td>100,000</td>
              <td>100,000</td>
              <td>100,000</td>
              <td>100,000</td>
              <td></td>
          </tr>
    </tbody>
  <tfoot>
            <tr>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
            </tr>
        </tfoot>
    </table>

</div>



</body>
</html>

I added notes inline in the code - but one point to note: If you want to adjust the sum so that it reflects visible (unfiltered) data, you can replace this:

table.rows( ).data()

with this:

table.rows( {search: 'applied'} ).data()

Upvotes: 7

Related Questions