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.

Iterating

Iterators in DataTables

A blog post covering:

rows().every(), columns().every() and cells().every(),

each()

iterator()

The differences, and when to use each one.

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.

Server Side

The main server side processing manual page.

Sent parameters - the data which DataTables automatically sends to the server.

Returned data - the data which is passed from the server to DataTables.

Understanding the two sets of data which are passed to and from DataTables is key to understanding how to make server-side processing work.

A simple JSON structure sent from DataTables to the server might look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
{
    "draw": "5",
    "columns[0][data]": "id",
    "columns[0][name]": "",
    "columns[0][searchable]": "true",
    "columns[0][orderable]": "true",
    "columns[0][search][value]": "",
    "columns[0][search][regex]": "false",
    "columns[1][data]": "name",
    "columns[1][name]": "",
    "columns[1][searchable]": "true",
    "columns[1][orderable]": "true",
    "columns[1][search][value]": "",
    "columns[1][search][regex]": "false",
    "columns[2][data]": "description",
    "columns[2][name]": "",
    "columns[2][searchable]": "true",
    "columns[2][orderable]": "true",
    "columns[2][search][value]": "",
    "columns[2][search][regex]": "false",
    "order[0][column]": "1",
    "order[0][dir]": "asc",
    "start": "30",
    "length": "10",
    "search[value]": "",
    "search[regex]": "false"
}

And a simple JSON structure sent from the server to DataTables might look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
{
    "draw": 1,
    "recordsTotal": 1000,
    "recordsFiltered": 1000,
    "data": [{
        "id": 1,
        "name": "widget_1",
        "description": "This is a description for widget 1"
    }, {
        "id": 2,
        "name": "widget_2",
        "description": "This is a description for widget 2"
    }, { ...
    }, {
        "id": 10,
        "name": "widget_10",
        "description": "This is a description for widget 10"
    }]
}

Buttons

buttons.exportData( [options] ) - choose which data to export from a DataTable to a spreadsheet, PDF, etc.

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
    "_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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
(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):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
$.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;
    }
);

Drilling Down into JSON

DataTables uses the standard JavaScript dotted object notation to drill down into JSON data structures.

Nested Objects

Accessing nested objects is described here.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "name": "Tiger Nixon",
  "hr": {
    "position": "System Architect",
    "salary": "$3,120",
    "start_date": "2011/04/25"
  },
  "contact": [
    "Edinburgh",
    "5421"
  ]
}

And:

1
2
3
4
5
6
7
8
"columns": [
  { "data": "name" },
  { "data": "hr.position" },
  { "data": "contact.0" },
  { "data": "contact.1" },
  { "data": "hr.start_date" },
  { "data": "hr.salary" }
]

Note how contact.0 refers to the first element of the contact array. This is a DataTables extension to the JavaScript dot-notation syntax.

Nested Arrays

For arrays, there is an additional array bracket syntax, which can be used to concatenate array values.

Example:

1
2
3
4
5
6
{
  "name": [
    "Nixon",
    "Tiger"
  ]
}

And:

1
2
3
"columns": [
  { "data": "name[, ]" }
]

This results in each cell containing the concatenated values from the array, with each value separated by a comma and a space (, ):

Nixon, Tiger

See here for details.

Limitations

JavaScript dotted notation has some limitations:

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:

Naming in DataTables 1.10

There is a page in the official documentation which documents the mapping between the two naming standards:

Converting parameter names for 1.10

Download Builder

Download builder: https://datatables.net/download/

Compatibility

Compatibility charts: https://datatables.net/download/compatibility

Dates and Times

There are various ways to handle the display, filtering, and sorting of datetimes in DataTables.

moment.js

The monent.js library can be used:

https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.27.0/moment.min.js

See the monent.js documentation. It also has extensive internationalization support.

Assume a field containing the following date:

1
<td>25-12-2019</td>

The following render function can be used in combination with DataTables orthogonal data support:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$('#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:

https://cdn.datatables.net/plug-ins/1.10.22/sorting/datetime-moment.js

Example:

1
2
3
4
5
6
$(document).ready(function() {
  $.fn.dataTable.moment( 'HH:mm MMM D, YY' ); // 04:01 Jan 27, 15
  $.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' ); // Tue, February 3rd, 2015

  $('#example').DataTable();
} );

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:

1
2
3
4
5
6
7
8
<tr>
    <td data-search="Tiger Nixon">T. Nixon</td>
    <td>System Architect</td>
    <td>Edinburgh</td>
    <td>61</td>
    <td data-order="1303682400">Mon 25th Apr 11</td>
    <td data-order="3120">$3,120/m</td>
</tr>

DataTables has built-in support for the following:

  • data-sort and data-order - for ordering data
  • data-filter and data-search - for searching data

Read more about these HTML5 custom data-* attributes here.