How to export all rows from Datatables using Ajax?

Question

I am using new feature in Datatables: "HTML5 export buttons". I am loading data with Ajax.

https://datatables.net/extensions/buttons/examples/html5/simple.html

The problem is that it only export the page that is currently displayed.

I am exporting like this:

buttons: [     {         extend: 'pdfHtml5',         text: 'PDF',         exportOptions: {             "columns": ':visible',         }     }, ] 

How can I export all rows?

Answer 1

You need to tell the AJAX function to get all data, then do the export but cancel the actual draw so that all of that data isn't loading into the DOM. The full data will still exist in memory for the DataTables API though, so you need to refresh it to the way it was before the export.

var oldExportAction = function (self, e, dt, button, config) {     if (button[0].className.indexOf('buttons-excel') >= 0) {         if ($.fn.dataTable.ext.buttons.excelHtml5.available(dt, config)) {             $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config);         }         else {             $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config);         }     } else if (button[0].className.indexOf('buttons-print') >= 0) {         $.fn.dataTable.ext.buttons.print.action(e, dt, button, config);     } };  var newExportAction = function (e, dt, button, config) {     var self = this;     var oldStart = dt.settings()[0]._iDisplayStart;      dt.one('preXhr', function (e, s, data) {         // Just this once, load all data from the server...         data.start = 0;         data.length = 2147483647;          dt.one('preDraw', function (e, settings) {             // Call the original action function              oldExportAction(self, e, dt, button, config);              dt.one('preXhr', function (e, s, data) {                 // DataTables thinks the first item displayed is index 0, but we're not drawing that.                 // Set the property to what it was before exporting.                 settings._iDisplayStart = oldStart;                 data.start = oldStart;             });              // Reload the grid with the original page. Otherwise, API functions like table.cell(this) don't work properly.             setTimeout(dt.ajax.reload, 0);              // Prevent rendering of the full data to the DOM             return false;         });     });      // Requery the server with the new one-time export settings     dt.ajax.reload(); }; 

and:

    buttons: [         {             extend: 'excel',             action: newExportAction         }, 

Answer 2

Thanks a lot to the user "kevinpo". He has given the way how all records from jquery datatable to be downloaded as excel when server side processing is On. Based on his answer, here i have complete export functionality implemented (copy, excel, csv, pdf, print) for server side processing.

inside $(document).ready() define the below function & call this function on action of each export button like below :

/* For Export Buttons available inside jquery-datatable "server side processing" - Start - due to "server side processing" jquery datatble doesn't support all data to be exported - below function makes the datatable to export all records when "server side processing" is on */  function newexportaction(e, dt, button, config) {     var self = this;     var oldStart = dt.settings()[0]._iDisplayStart;     dt.one('preXhr', function (e, s, data) {         // Just this once, load all data from the server...         data.start = 0;         data.length = 2147483647;         dt.one('preDraw', function (e, settings) {             // Call the original action function             if (button[0].className.indexOf('buttons-copy') >= 0) {                 $.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config);             } else if (button[0].className.indexOf('buttons-excel') >= 0) {                 $.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ?                     $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) :                     $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config);             } else if (button[0].className.indexOf('buttons-csv') >= 0) {                 $.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ?                     $.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) :                     $.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config);             } else if (button[0].className.indexOf('buttons-pdf') >= 0) {                 $.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ?                     $.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) :                     $.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config);             } else if (button[0].className.indexOf('buttons-print') >= 0) {                 $.fn.dataTable.ext.buttons.print.action(e, dt, button, config);             }             dt.one('preXhr', function (e, s, data) {                 // DataTables thinks the first item displayed is index 0, but we're not drawing that.                 // Set the property to what it was before exporting.                 settings._iDisplayStart = oldStart;                 data.start = oldStart;             });             // Reload the grid with the original page. Otherwise, API functions like table.cell(this) don't work properly.             setTimeout(dt.ajax.reload, 0);             // Prevent rendering of the full data to the DOM             return false;         });     });     // Requery the server with the new one-time export settings     dt.ajax.reload(); }; //For Export Buttons available inside jquery-datatable "server side processing" - End 

And for buttons, define like below

"buttons": [{                "extend": 'copy',                "text": '<i class="fa fa-files-o" style="color: green;"></i>',                "titleAttr": 'Copy',                                               "action": newexportaction             },             {                "extend": 'excel',                "text": '<i class="fa fa-file-excel-o" style="color: green;"></i>',                "titleAttr": 'Excel',                                               "action": newexportaction             },             {                "extend": 'csv',                "text": '<i class="fa fa-file-text-o" style="color: green;"></i>',                "titleAttr": 'CSV',                                               "action": newexportaction             },             {                "extend": 'pdf',                "text": '<i class="fa fa-file-pdf-o" style="color: green;"></i>',                "titleAttr": 'PDF',                                               "action": newexportaction             },             {                "extend": 'print',                "text": '<i class="fa fa-print" style="color: green;"></i>',                "titleAttr": 'Print',                                                "action": newexportaction             }], 

That's it. Now your download is ready.