var truncatedLength = 38; // set this to whatever you prefer
var table;
var origDataMap = new Map(); // the original (full) data for long text
$(document).ready(function() {
table = $('#TableName').DataTable({
columnDefs: [{
targets: ['_all'],
render: function(data, type, row, meta) {
// sets up initially minimized text for content longer than truncatedLength:
var api = new $.fn.dataTable.Api(meta.settings);
var node = api.cell(meta.row, meta.col, {
order: 'index'
}).node();
var hasToggler = $(node).children().length > 0;
if (type === 'display' && !hasToggler && data.length > truncatedLength + 5) {
origDataMap.set(meta.row + '-' + meta.col, data);
var displayData = '<span>' + data.substr(0, truncatedLength) + '... </span>' +
'<button class="moreless-button" data-state="trunc"' +
' onclick="showMoreOrLess(this, ' + meta.row + ', ' + meta.col + ')">more</button>';
return displayData;
} else {
return data;
}
}
}]
});
});
//
// toggles the DOM table cell's contents (but it
// does not change the underlying DataTables data):
//
function showMoreOrLess(node, rowId, colId) {
var displayData;
var state = $(node).attr('data-state');
var origData = origDataMap.get(rowId + '-' + colId);
var cellNode = $(node).parent();
if (state === 'trunc') {
displayData = '<span>' + origData + ' </span>' +
'<button class="moreless-button" data-state="full"' +
' onclick="showMoreOrLess(this, ' + rowId + ', ' + colId + ')">less</button>';
} else {
displayData = '<span>' + origData.substr(0, truncatedLength) + '... </span>' +
'<button class="moreless-button" data-state="trunc"' +
' onclick="showMoreOrLess(this, ' + rowId + ', ' + colId + ')">more</button>';
}
cellNode.html(displayData);
}
The CSS:
/* makes a button look like a link */
.moreless-button {
background: none!important;
border: none;
padding: 0!important;
font-family: arial, sans-serif;
color: #069;
text-decoration: underline;
cursor: pointer;
}
The HTML:
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Demo</title>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.10.24/dataRender/ellipsis.js"></script>
</head>
<body>
<div style="margin: 20px;">
<table id="TableName" class="display dataTable cell-border" style="width:100%">
<thead>
<tr>
<th>Number</th>
<th>Severity</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>Number123</td>
<td>S1</td>
<td>Description size keeps on varying</td>
</tr>
<tr>
<td>Number345</td>
<td>S9 major alarm severe major alarm severe major alarm severe</td>
<td>Unable to log on</td>
</tr>
<tr>
<td>Number234</td>
<td>S1</td>
<td>Flashing red lights in the corner of my eyes</td>
</tr>
<tr>
<td>Number765</td>
<td>S3</td>
<td>Description again is very very very long very very very long very very very long</td>
</tr>
<tr>
<td>Number678</td>
<td>S1</td>
<td>Description size keeps on varying</td>
</tr>
<tr>
<td>Number543</td>
<td>S3</td>
<td>Lorem ipsum dolor sit amet, consectetur "adipiscing" elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute
irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
This approach allows you to process the JSON response from the ajax call, before passing the row array data to DataTables. You can therefore re-arrange the JSON data, and process additional data in the JSON if needed.
In this specific example, the option uses a function. In this case, it’s up to you to make the actual ajax call (e.g. using jQuery’s ajax API, as shown above).
The three parameters made available in this option are:
Parameter
Notes
data
The data to be sent to the server.
callback
Callback function that must be executed when the required data has been obtained from the ajax request. That data should be passed into the callback as the only parameter.
settings
The DataTable settings object.
In our example the callback function re-names the data array from row_objects to data:
This is needed because DataTables expects the data array to be called data – and the normal approach (using dataSrc: 'row_objects') is not available when the ajax function is used.
An example of the JSON handled by the above code is:
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.
