jQuery datatables exporting data to Excel

Question

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.

I worked on this for a bit - my solution was to use table.page.len(-1) to disable paging then restore it. but it occurred to me - you're possibly trying to pass 2000 rows of data through through a url string, aren't you? Aren't you limited in the amount of data you can pass through there? — dmgig
@dgig - I am not limited to the amount of data. I just picked the number 2000. Although, currently, the table I am working with does have a little over 2100 records. This table could hold more or less. I hope that answers your question. — John Beasley
Might help: datatables.net/extensions/buttons/examples/initialisation/… — Karlo Kokkak
@KarloKokkak - I was able to achieve the results I needed thanks to your suggestion. — John Beasley

dmgig dmgig · Accepted Answer · 2018-05-23T20:21:53

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.

jQuery datatables exporting data to Excel

1 Answers