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;
      }
    }
  }
}