How to Export All DataTables into Multiple Excel Sheets?

akarshashok7

Currently i hava a server-side Processing DataTable of 29000 records , when i try to export this to excel, html.datatables.net limits the length to 5K. i have to export the rest of the records (24K), by spliting them into multiple files.

Below is the example of my code, which export's all the data, how and where can i iterate and download the excel multiple times?

$('#yTable').DataTable({
      serverSide: true,
      dom: 'Bfrtip',
      ajax: //my ajax call...,
      buttons : [{
                    extend: 'excel',
                    text: 'Export to Excel',
                    action: newExportAction,
                    customize: function(xlsx) {...}
                }],
      columns: columnsToShow,
      columnDefs: colDefs,
      deferRender: true
      });

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

allan

With server-side processing enabled, personally I wouldn't attempt to do the export client-side at all. Do it server-side where the data is and then download the new file. Doing it on the client-side mitigates any advantage server-side processing gives you, since you need to load all of the data into the client-side anyway!

That said:

how and where can i iterate and download the excel multiple times?

You'd need to base your code off this function. You'll notice that in it, it uses buttons.exportData() to get the data to export. That will just retrieve all of the data - the function was never designed to do multi-part files. So that is where you'd need to modify the code to get the rows you need.

However, as I say, I don't believe this is the best way to do it. I'd encourage you to do it server-side.

Allan

akarshashok7

Hello @allan

Below is the updated part of my code which downloads the no of files assigned to 'totalNoFilesToDownload'. i have declaring the start and the end still both of the all the files are downloaded in as 0-50.
totally there are 3 ajax fired,

1)start : 0, length : 50,
2)start : 50, length : 50
3)start : 50, length : 50

Files downloaded 2
Both files starts at 0 and ends 50.

for (var i = 1; i <= totalNoFilesToDownload; i++) {
  splitBy = true;
  splitStart = (i - 1) * 50;
  splitlength = 50;
  // Just this once, load all data from the server...
  dt.settings()[0]._iDisplayStart = splitStart;
  dt.settings()[0]._iDisplayLength = splitlength;
  oldExportAction(self, e, dt, button, config);
  dt.ajax.reload();
}
splitBy = false;
dt.settings()[0]._iDisplayStart = oldStart;
dt.settings()[0]._iDisplayLength = oldLength;
// Requery the server with the new one-time export settings
dt.ajax.reload();