How to grab the sum of a jquery datatables column to use in aspx?

I generate the code for datatables in codebehind .cs file. I have some columns with currency values. How can I sum the whole column and grab it so that I can use it in the .cs file to fill a label?

Also is it possible to grab the value only of the shown results if the searchfilter is used? E.g. something is typed in the searchfilter so rows get hide and I need the sum only of the visible rows.

Thanks

aspx:

<div id="DIV_Table" runat="server"> </div> //the content will be filled code-behind
<asp:Label ID="lbl_Sum" runat="server"></asp:Label> //Label to show the sum

cs:
//datatable dt is the source
string html = "<table id='liste' class='display' cellspacing='0' style='width:100%'><thead>";
        //add header row
        html += "<tr>";

        for (int i = 0; i <= dt.Columns.Count - 1; i++)
        {
            html += "<th>" + dt.Columns[i].ColumnName + "</th>";
        }
        html += "</tr></thead><tbody>";

        //add rows
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            html += "<tr>";
        for (int y = 0; y <= dt.Columns.Count - 1; y++)
            {
            html += "<td>" + dt.Rows[i][y].ToString() + "</td>";
            }
            html += "</tr>";
          }

        //footer
        html += "</tbody><tfoot><tr>";
        foreach (DataColumn dc in dt.Columns) html += @"<th>" + dc.ColumnName + "</th>";
        html += "</tr></tfoot></table>";

        //javascript
        html += @"<script>
        $(document).ready(function() {

                      $('#liste').DataTable({                                    
                                fixedHeader: {
                                                header: true,
                                                footer: true
                                            },
                                
                                order: [[ 0, 'desc' ]],

                                paging: false,

                                columnDefs: [{ type: 'currency', targets: 7 }
                                             { targets: 8, visible: false},],

                                language: { 'decimal': ',',
                                            'thousands': '.',
                                            'sEmptyTable': 'Keine Daten in der Tabelle vorhanden',
                                            'sInfo': '_START_ bis _END_ von _TOTAL_ Einträgen',
                                            'sInfoEmpty': '0 bis 0 von 0 Einträgen',
                                            'sInfoFiltered': '(gefiltert von _MAX_ Einträgen)',
                                            'sInfoPostFix': '',
                                            'sInfoThousands': '.',
                                            'sLengthMenu': '_MENU_ Einträge anzeigen',
                                            'sLoadingRecords': 'Wird geladen...',
                                            'sProcessing': 'Bitte warten...',
                                            'sSearch': 'Suchen',
                                            'sZeroRecords': 'Keine Einträge vorhanden.',
                                            'oPaginate': {
                                            'sFirst': 'Erste',
                                            'sPrevious': 'Zurück',
                                            'sNext': 'Nächste',
                                            'sLast': 'Letzte'
                                            },
                                            'oAria': {
                                            'sSortAscending': ': aktivieren, um Spalte aufsteigend zu sortieren',
                                            'sSortDescending': ': aktivieren, um Spalte absteigend zu sortieren'
                                            }
                                         }, 
                            
                                        rowCallback: function(row, data, index)
                                        {
                                            if(data[29] == 'Abgeschlossen') { $(row).css('background-color', '#bfbfbf'); }
                                        },

                          });
                    });
                   </script>";

DIV_Table.InnerHtml = html;

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Will out changing to much of your current code you could adapt that .sum() datatables function you linked by running it on the datatables search.dt event, then also running it on page load for the inital value. See below (only the javascript is important to you – HTML and CSS is just for demo. Test by searching for like the letter “c” or the letter “f” to see the Total change based on the column 8 value):

var table;
$(document).ready(function() {

  $.fn.dataTable.Api.register('column().data().sum()', function() {
    return this.reduce(function(a, b) {
      var x = parseFloat(a) || 0;
      var y = parseFloat(b) || 0;
      return x + y;
    });
  });

  table = $('#liste').DataTable({
      fixedHeader: {
        header: true,
        footer: true
      },

      order: [
        [0, 'desc']
      ],

      paging: false,

      columnDefs: [{
        type: 'currency',
        targets: 7
      }, {
        targets: 8,
        visible: false
      }],

      language: {
        'decimal': ',',
        'thousands': '.',
        'sEmptyTable': 'Keine Daten in der Tabelle vorhanden',
        'sInfo': '_START_ bis _END_ von _TOTAL_ Einträgen',
        'sInfoEmpty': '0 bis 0 von 0 Einträgen',
        'sInfoFiltered': '(gefiltert von _MAX_ Einträgen)',
        'sInfoPostFix': '',
        'sInfoThousands': '.',
        'sLengthMenu': '_MENU_ Einträge anzeigen',
        'sLoadingRecords': 'Wird geladen...',
        'sProcessing': 'Bitte warten...',
        'sSearch': 'Suchen',
        'sZeroRecords': 'Keine Einträge vorhanden.',
        'oPaginate': {
          'sFirst': 'Erste',
          'sPrevious': 'Zurück',
          'sNext': 'Nächste',
          'sLast': 'Letzte'
        },
        'oAria': {
          'sSortAscending': ': aktivieren, um Spalte aufsteigend zu sortieren',
          'sSortDescending': ': aktivieren, um Spalte absteigend zu sortieren'
        }
      },

      rowCallback: function(row, data, index) {
        if (data[29] == 'Abgeschlossen') {
          $(row).css('background-color', '#bfbfbf');
        }
      },

    })
    .on('search.dt', function() {
      var total = table.column(7, {
        page: 'current'
      }).data().sum();
      $('.total-label').text(total);
    });

  var total = table.column(7, {
    page: 'current'
  }).data().sum();
  $('.total-label').text(total);
});
table {
  width: 100%;
  border-collapse: collapse;
}
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>

<h2>
  Total:
  <label class="total-label"></label>
</h2>
<hr>
<table id="liste">
  <thead>
    <tr>
      <td>Column 1</td>
      <td>Column 2</td>
      <td>Column 3</td>
      <td>Column 4</td>
      <td>Column 5</td>
      <td>Column 6</td>
      <td>Column 7</td>
      <td>Column 8</td>
      <td>Column 9</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>abc 1</td>
      <td>1</td>
      <td>hidden</td>
    </tr>
     <tr>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>cdf 2</td>
      <td>2</td>
      <td>hidden</td>
    </tr>
     <tr>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>fgh 3</td>
      <td>3</td>
      <td>hidden</td>
    </tr>
  </tbody>
</table>

You will also need to add the CssClass="total-label" to your <asp:Label /> or whatever class you want so you can reference it in javascript easily.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x