jQuery datatables exporting data to Excel

I am trying to export directly from the datatable to Excel.

Starting with the ajax call:

displayRecords();
function displayRecords()
{
  $.ajax({
    url: 'process/getRecords.php',
    type: 'POST',
    data: '',
    dataType: 'html',
    success: function(data, textStatus, jqXHR)
    {
      var jsonObject = $.parseJSON(data); 
      var table = $('#resultsTable').DataTable({    
      {
        "data": jsonObject,
        "columns": [
          {"data": "JOB_REFERENCE"},
          {"data": "VOYAGE_REFERENCE"},
          // few more columns
        ],
        "iDisplayLength": 25,
        "scrollY": 500,
        "scrollX": true,
        "bDestroy": true,
        "paging": true,
        "stateSave": true
      }
    },
    error: function(jqHHR, textStatus, errorThrown)
    {
      console.log('fail: '+ errorThrown);
    }
  });

   // button click to export results
   var tableresults = $('#resultsTable').dataTable();

   $("#btnExport").on('click', function(e) 
   {
     e.preventDefault();
     window.open('data:application/vnd.ms-excel,' +
       encodeURIComponent(table[0].outerHTML));
   });
}

Using all of the above, I can successfully export the results from the datatable. I can even use the filter search to drill down to a smaller data set, and export the results.

I was using the following fiddle: http://jsfiddle.net/donpayne/jzdjdo3z/

The problem I am having lies with the Show Entries dropdown of the datatable. Typically, the dropdown is set to 10. Whether you filter the search down or not, if the total record count is greater than the Show Entries dropdown, the Excel sheet will only return the total amount set in the dropdown.

You can test what I am talking about in the fiddle. Set the Show Entries dropdown to 10, then export to Excel. If you'll notice, there are 58 total records in that table. The Excel sheet will only return 10 records.

I need the to return all the records. If I have 2000 records, and the Show Entries dropdown is set to 10, I need the exported Excel sheet to include all 2000 records.

The same if I filter the search down to about 56 records; when I export to excel, I should have a total of 56 records on that spreadsheet, regardless of what the Show Entries dropdown is set to.

As stated, I referenced the code from the fiddle and altered it to fit my datatable.

Solution

I think the best thing to do is to remove the paging, then do the export, then turn the paging back on once it's done.

I made a couple minor changes:

$(function () 
{
    var table = $('#example').DataTable();

    $("#btnExport").click(function(e) 
    {
        table.page.len( -1 ).draw();
        window.open('data:application/vnd.ms-excel,' + 
            encodeURIComponent($('#example').parent().html()));
      setTimeout(function(){
        table.page.len(10).draw();
      }, 1000)

    });
});

Updated fiddle: http://jsfiddle.net/jzdjdo3z/176/

Page Length docs: https://datatables.net/reference/api/page.len()

Paging option docs: https://datatables.net/reference/option/paging

I'm not sure why initializing with dataTables vs DataTables made a difference, but it did. So keep an eye out for that.