The table uses footerCallback() to populate the footer cell with a total sum of the durations (hours:minutes:seconds) shown in each row.
This then uses a reduce() function to accumulate the running total of durations. The final total is displayed in the table’s footer.
total = api
.column(0)
.data()
.reduce( function(cume, current, idx, api) {
return addDurations(cume, current);
}, "00:00:00" );
The "00:00:00" value represents the starting value used by reduce(). It is needed here because the values in each column are assumed to be in the format of a string, as hh:mm:ss.
The full stand-alone code for the page:
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Demo</title>
<script src="https://code.jquery.com/jquery-3.5.1.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>Durations</th>
</tr>
</thead>
<tbody>
<tr>
<td>09:04:45</td>
</tr>
<tr>
<td>23:45:56</td>
</tr>
<tr>
<td>114:16:19</td>
</tr>
</tbody>
<tfoot>
<tr>
<th></th>
</tr>
</tfoot>
</table>
</div>
<script type="text/javascript">
// https://stackoverflow.com/users/544542/pee2pee
$(document).ready(function() {
var table = $('#example').DataTable( {
"footerCallback": function ( row, data, start, end, display ) {
var api = this.api(), data;
// Total over all pages:
total = api
.column(0)
.data()
.reduce( function(cume, current, idx, api) {
return addDurations(cume, current);
}, "00:00:00" ); // initial value is this string.
// Update footer:
$( api.column(0).footer() ).html(total);
}
} );
} );
// a reduce() function, to add the durations in the column:
function addDurations(cumeDuration, colDuration) {
var cumeSeconds = durationToSeconds(cumeDuration);
cumeSeconds += durationToSeconds(colDuration);
return secondsToDuration(cumeSeconds);
}
// takes a string "hh:mm:ss", creates an int:
function durationToSeconds(duration) {
var hms = duration.split(":");
return (parseInt(hms[0]) * 60 * 60) + (parseInt(hms[1]) * 60) + parseInt(hms[2]);
}
// takes an int, creates a string "hh:mm:ss":
function secondsToDuration(seconds) {
var hrs = Math.trunc(seconds / (60 * 60));
// may need to pad mins and secs with leading zero:
var mins = String(Math.trunc((seconds - (hrs * 60 * 60)) /60)).padStart(2, '0');
var secs = String(seconds % 60).padStart(2, '0');
return hrs + ":" + mins + ":" + secs;
}
</script>
</body>
</html>
rows().every(), columns().every() and cells().every() – Use when you want to perform an API method on each selected item, but there is a performance penalty.
each() – similar to every() but doesn’t create a new API instance and doesn’t change the callback function’s scope. Lower level.
iterator() – For all other data types that the DataTables API can carry we use the each() to access the data in the instance.
The Table
We assume a simple table containing the following:
Get only one cell without the HTML tags – row 3 column 2:
var oneSelectedCell = table.cell(2, 1);
var node = oneSelectedCell.node();
console.log(node.textContent);
Output:
foxtrot
One Column
Get column 2’s data:
var oneSelectedColumn = table.column(1).data();
for (var i = 0; i < oneSelectedColumn.length; i++) {
console.log(oneSelectedColumn[i]);
}
Output:
bravo
delta
<b>foxtrot</b>
Get column 2’s data via the nodes:
var oneSelectedColumn = table.column(1).nodes();
for (var i = 0; i < oneSelectedColumn.length; i++) {
console.log(oneSelectedColumn[i].textContent);
}
Output:
bravo
delta
foxtrot
Row Iterator
Use a ‘row’ iterator to get row 2:
table.rows(1).iterator( 'row', function ( context, index ) {
console.log( $( this.row( index ).node() ) );
} );
Column Iterator
Use a ‘column’ iterator to get each column:
table.columns().iterator( 'column', function ( context, index ) {
console.log( context );
console.log( index );
console.log( $( this.column( index ) ) );
} );
The slice() Function
table.rows().every( function ( ) {
var data = this.data();
// get a subset of the row data, excluding the first cell
// if the values are stored as strings, map then to numbers
var values = data.slice(1).map(Number);
// the ... operator expands the array into values for the
// max() function to operate on:
console.log( data[0] + ': ' + Math.max( ...values ) );
} );
Here are links to (and notes about) some key pages from the DataTables documentation and web site. The documentation is extensive – and sometimes feels like a bit of a sprawl. These are items I have been referring to more frequently recently.
But it may be easier to iterate over a single column. The following function sums up values in the first column, if the checkbx in the second column is checked:
function doSum() {
var table = $('#example').DataTable();
var checkboxNodes = table.column(0).nodes();
var amountData = table.column(1).data();
var sum = 0;
for (var i = 0; i < checkboxNodes.length; i++) {
if( $(checkboxNodes[i]).find("input").is(':checked') ) {
sum += amountData[i];
}
}
console.log(sum);
}
Utility Functions
map() – Create a new API instance with the result set defined by the values returned from the callback function.
toArray() – Create a native Javascript array object from an API instance.
to$() – Convert the API instance to a jQuery object, with the objects from the instance’s result set in the jQuery result set.
Excel options – In particular, see the customize and exportOptions descriptions:
customize – modify the XSLX file that is created by Buttons. It takes 3 parameters:
xlsx – object containing the excel files (see below)
btn – the button configuration object
tbl – the table’s DataTables API instance
exportOptions – selects the data to be exported, using the buttons.exportData() function described above.
Excel built-in styles – a short list of formatting styles provided in the spreadsheet created by DataTables.
Excel Files
The structure of the Excel files used in the above customize parameter:
{
"_rels": {
".rels": xml
},
"xl": {
"_rels": {
"workbook.xml.rels": xml
},
"workbook.xml": xml,
"styles.xml": xml,
"worksheets": {
"sheet1.xml": xml
}
},
"[Content_Types].xml": xml
}
This is how the xlsx.xl.worksheets['sheet1.xml'] reference is constructed (see example below).
Example Code
(document).ready(function() {
$('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
{
extend: 'excelHtml5',
title: '', // no title row in excel sheet
text: 'Excel', // label for the export button
// export everything except the first row and first col:
exportOptions: {
rows: function ( idx, data, node ) {
if (idx > 0) {
return data;
}
},
columns: function ( idx, data, node ) {
if (idx > 0) {
return data;
}
}
},
// format the output with a thin black border on each cell:
customize: function ( xlsx, btn, tbl ) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$( 'row c', sheet ).attr( 's', '25' );
}
}
]
} );
} );
Another exportOptions fragment – this time for modifying header data (and there is also format.body and format.footer):
exportOptions: {
format: {
header: function ( data ) {
var n = data.indexOf("<select>");
if (n > -1) {
return data.substring(0, n);
} else {
return data;
}
}
}
}
Search Plug-In
The DataTables search plug-in is accessed using the following:
$.fn.dataTable.ext.search
This allows you to provide completely customized searching.
The official example for a range search uses the following code:
$.fn.dataTable.ext.search.push(
function( settings, searchData, index, rowData, counter ) {
var min = parseInt( $('#min').val(), 10 );
var max = parseInt( $('#max').val(), 10 );
var age = parseFloat( searchData[3] ) || 0; // using the data from the 4th column
if ( ( isNaN( min ) && isNaN( max ) ) ||
( isNaN( min ) && age <= max ) ||
( min <= age && isNaN( max ) ) ||
( min <= age && age <= max ) )
{
return true;
}
return false;
}
);
Be aware: $.fn.dataTable.ext.search is an array – which is why you push() your search function onto it. If you want to use multiple different custom search functions, you need to pop() the unwanted one off the array first:
When working with dot notation, property identifiers can only be alphanumeric (and _ and $). Properties cannot start with a number, or contain periods.
If a JSON key contains dots as part of its text, for example…
"foo" : { "baz.bat": "value" }
…then this will interfere with the dot notation process.
Alternatively, you can use JavaScript bracket notation to navigate into a data hierarchy:
foo["baz.bat"]
Naming Standards
DataTables has two naming standards:
older Hungarian notation names (for versions prior to 1.10)
newer camelCase names (from 1.10 onwards)
If you are using the latest version of DataTables (from version 1.10 onwards), you can use either the new naming standard or the old one:
Orthogonal data is described here. It allows you to store different versions of a data item. For example, the display value of a date may be Mon 23rd Feb, 2019 but the sort value may be 20190223.
Orthogonal data has the following types:
display
sort
filter
type
The first three are self-explanatory. The type type is used for “type detection”.
What does that mean?
DataTables supports automatic detection of different data types. See columns.type for background. The type type, as described on this page:
The type detection plug-in documentation does not shed much additional light on this. An answer from the DataTables forum indicates that this is not something we should typically need to handle, outside of the above advice to “match the sort value”.
The DataTables API
There are three main ways to access the DataTables API:
$( selector ).DataTable();
$( selector ).dataTable().api();
new $.fn.dataTable.Api( selector );
See here for details and notes explaining the differences between these syntaxes. In summary:
The result from each is an instance of the DataTables API object which has the tables found by the selector in its context. It is important to note the difference between $( selector ).DataTable() and $( selector ).dataTable(). The former returns a DataTables API instance, while the latter returns a jQuery object.
If you need to access the DataTables API from within a DataTable callback function, you can use the JavaScript this keyword.
The following render function can be used in combination with DataTables orthogonal data support:
$('#example').DataTable( {
columnDefs:[{
targets: 4,
render: function ( data, type, row ) {
//console.log(data); // the raw date as a string
var d = moment(data, 'DD-MM-YYYY'); // converted to a moment object
var sortString = moment(d).format('YYYYMMDD'); // converted to a new string format
//console.log(sortString);
var cellContents = '<center>' + data + '</center>';
if ( type === 'display' || type === 'filter' ) {
return cellContents;
} else {
return sortString;
}
}
}]
} );
DateTime Plug-In
This is introduced here. The plug-in can be incorporated using this – but also relies on the above moment.js library:
DataTables will automatically checking to see if the data in a column matches any of the given datetime types you have registered using $.fn.dataTable.moment().
It also uses the DataTables ordering extension ($.fn.dataTable.ext.type.order) to automatically provide sorting for your registered date formats.
HTML5 Attributes
Another “orthogonal data” approach is to use DataTables support for HTML5 attributes. For example:
In GitHub the PHP source files for server-side processing examples are here. This is where you can find the ssp.class.php file which is used by this example.
document.getElementById('contents'); //returns a HTML DOM Object
var contents = $('#contents'); //returns a jQuery Object
In jQuery, to get the same result as document.getElementById, you can access the jQuery Object and get the first element in the object (Remember JavaScript objects act similar to associative arrays).
var contents = $('#contents')[0]; //returns a HTML DOM Object
