Search code examples
jquerydatatablesexport-to-exceldatatables-1.10

Export ALL rows from DataTables.js while displaying only some of them and keeping the pagination

On a DataTable, I need :

  • To export to Excel ALL rows
  • Display only some rows on screen, based on the value of the ItemId column
  • Keep the pagination efficient with 30 VISIBLE rows per page

To display only the desired rows on screen, I'm using the drawCallback

// Each row has an ItemId property. Several rows may have the same ItemId
// I want to display only the first row for each ItemId, but export all rows
drawCallback: function (settings) {
    var api = this.api();
    var ids = [];
                
    $.each(api.rows({ page: 'current' }).data(), function (i, v) {
        if ($.inArray(v.ItemId, ids) != -1)
            $("#myDatatable tbody tr:eq(" + i + ")").css("visibility", "collapse")
        else
            ids.push(v.ItemId);
    })
},

However, I'm using the pagination system, and I want to display 30 visible rows per page.

  • If I use the above drawCallback, I only have 1 or 2 visible rows per page, and it's not what I want. However, rows which should be hidden are indeed hidden and the Export works well, as it exports ALL rows. Issue here is the pagination
  • If I remove the drawCallback, all the rows are displayed on screen which is not what I want neither. The export works (obviously) well as all rows are displayed and exported. Issue here is the displayed rows

How to keep 30 visible rows on screen, while exporting all of them (visible + hidden) ?

For the record, I'm using DataTables 1.10.21

Solution

  • The Problem

    I think removing rows from the HTML table in the way shown in the question will always lead to the problem you are describing: Too few rows on one page.

    This is because you have removed the data from the HTML table (the DOM) but DataTables (the JavaScript object where all your data is stored) does not know anything about these changes. So, it thinks you have a full page of data, showing 30 records.

    One Solution

    You can avoid this problem by using the DataTables filtering mechanism - but it is somewhat awkward, because (as you show in the question) how you filter a row depends on the data in previous rows.

    Here is one approach:

    1. Create a hidden column in the table, and populate that with a specific text value indicating whether a row should be displayed or not.

    2. When exporting to Excel, that is where you ensure all rows (visible and hidden) are exported - and where you ensure our extra hidden column, from (1) above, is not exported.

    3. Add logic to the end of the "document ready" function to calculate which rows are shown/hidden. This logic is basically the equivalent of what you have in your question.

    The code:

    $(document).ready(function() {

var firstFound = "##first_occurrence##"; // needs to be unique - must not clash with other data in the table

var table = $('#myDatatable').DataTable( {
  data: dataSet.data, // my test data is sourced from a JavaScript variable (see below)
  pageLength: 5, // just for testing
  columns: [
    { title: "Name", data: "name" },
    { title: "Office", data: "office" },
    { title: "Position", data: "position" }, // this is my equivalent of your ItemId column
    { title: "Start date", data: "start_date" },
    { title: "Salary", data: "salary" },
    { data: "id", visible: false } // this is where the 'firstFound' value is placed
  ],
  search: {
    search: firstFound // forces filtering to be applied
  },
  dom: 'Brtip',
  buttons: [
    { 
      title: '',
      extend: 'excel', 
      text: 'To Excel',
      exportOptions: {
        rows: { order:'current', search: 'none' }, // export all rows
        columns: [ 0, 1, 2, 3, 4 ] // export all columns apart from the final column in my table
      }
    }
  ]
} );

// the logic to populate the hidden column in my table:

var positions = [];

// column index 5 is the hidden column, used for filtering.
// column index 2 is my "job position" data (accountant, etc).
table.rows().every( function (rowIdx, tableLoop, rowLoop) {
  var filterCell = table.cell( { row: rowIdx, column: 5 } );
  var position = table.cell( { row: rowIdx, column: 2 } ).data();
  if ($.inArray(position, positions) === -1) {
    filterCell.data(firstFound);
    positions.push(position);
  } else {
    filterCell.data("");
  }
} );

// finally, re-draw the table to ensure the data changes are applied:
table.draw();

} );

    This shows you one approach. It may not be exactly what you need. For example, I have deliberately hidden the global filter box. Once the data is filtered and displayed, you cannot re-filter it in some other way.

    For reference, my sample test data looks like this:

    var dataSet = {
  "data": [
    {
      "id": "1",
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
    },
    ... // and so on...
    {
      "id": "2",
      "name": "Garrett Winters",
      "position": "Accountant",
      "salary": "$170,750",
      "start_date": "2011/07/25",
      "office": "Tokyo",
      "extn": "8422"
    }
  ]
};