jQuery 如何使用 Ajax 从数据表中导出所有行？

How to export all rows from Datatables using Ajax?

提问by Fox

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

我正在使用数据表中的新功能：“HTML5 导出按钮”。我正在使用 Ajax 加载数据。

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

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?

如何导出所有行？

采纳答案by Selcuk

According to DataTables documentationthere is no way to export all rows when you are using server side:

根据DataTables 文档，当您使用服务器端时，无法导出所有行：

Special note on server-side processing: When using DataTables in server-side processing mode (serverSide) the selector-modifierhas very little effect on the rows selected since all processing (ordering, search etc) is performed at the server. Therefore, the only rows that exist on the client-side are those shown in the table at any one time, and the selector can only select those rows which are on the current page.

关于服务器端处理的特别说明：在服务器端处理模式 ( serverSide) 中使用 DataTables 时，selector-modifier对所选行的影响很小，因为所有处理（排序、搜索等）都是在服务器上执行的。因此，客户端唯一存在的行是表中任何时候显示的行，选择器只能选择当前页面上的那些行。

I worked this around by adding an 'ALL' parameter to the length menu and training end users to display all records before doing a PDF (or XLS) export:

我通过向长度菜单添加“ALL”参数并培训最终用户在执行 PDF（或 XLS）导出之前显示所有记录来解决此问题：

var table = $('#example').DataTable({
    serverSide: true,
    ajax: "/your_ajax_url/",
    lengthMenu: [[25, 100, -1], [25, 100, "All"]],
    pageLength: 25,
    buttons: [
        {
            extend: 'excel',
            text: '<span class="fa fa-file-excel-o"></span> Excel Export',
            exportOptions: {
                modifier: {
                    search: 'applied',
                    order: 'applied'
                }
            }
        }
    ],
    // other options
});

回答by kevinpo

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.

您需要告诉 AJAX 函数获取所有数据，然后进行导出但取消实际绘制，以便所有这些数据都不会加载到 DOM 中。但是，DataTables API 的完整数据仍将存在于内存中，因此您需要将其刷新为导出前的状态。

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
        },

回答by Chandan Kumar

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.

非常感谢用户“kevinpo”。他给出了当服务器端处理为 On时如何将 jquery 数据表中的所有记录下载为 excel 的方法。根据他的回答，我在这里实现了用于服务器端处理的完整导出功能（复制、excel、csv、pdf、打印）。

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

在里面 $(document).ready()定义下面的函数并在action每个导出按钮上调用这个函数，如下所示：

/* 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
                           }
],

回答by diogenesgg

Yes, it's totally possible to make this work. Internally, DataTables has a function called buttons.exportData(). When you press a button, this function is called and returns the current page content. You can overwrite that function so it pulls all server side results based on current filters. And calling the same url used for ajax pagination.

是的，完全有可能完成这项工作。在内部，DataTables 有一个名为 buttons.exportData() 的函数。当您按下按钮时，将调用此函数并返回当前页面内容。您可以覆盖该函数，以便它根据当前过滤器提取所有服务器端结果。并调用用于 ajax 分页的相同 url。

You overwrite it before initializing your table. The code is as follows:

在初始化表之前覆盖它。代码如下：

$(document).ready(function() {

    jQuery.fn.DataTable.Api.register( 'buttons.exportData()', function ( options ) {
            if ( this.context.length ) {
                var jsonResult = $.ajax({
                    url: 'myServerSide.json?page=all',
                    data: {search: $(#search).val()},
                    success: function (result) {
                        //Do nothing
                    },
                    async: false
                });

                return {body: jsonResult.responseJSON.data, header: $("#myTable thead tr th").map(function() { return this.innerHTML; }).get()};
            }
        } );

    $("#myTable ").DataTable(
        {
            "dom": 'lBrtip',
            "pageLength": 5, 
            "buttons": ['csv','print', 'excel', 'pdf'],
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "myServerSide.json",
                "type": 'GET',
                "data": {search: $(#search).val()} 
            }
        }
});

回答by haui

This button definition worked for me in a scrolled table (instead of paging):

这个按钮定义在滚动表（而不是分页）中对我有用：

{
  text: 'PDF',
  action: function(e, dt, button, config) {
    dt.one('preXhr', function(e, s, data) {
      data.length = -1;
    }).one('draw', function(e, settings, json, xhr) {
      var pdfButtonConfig = $.fn.DataTable.ext.buttons.pdfHtml5;
      var addOptions = { exportOptions: { "columns" : ":visible" }};

      $.extend(true,pdfButtonConfig,addOptions);
      pdfButtonConfig.action(e, dt, button, pdfButtonConfig);
    }).draw();
  }
}

It will force the DataTable to request all rows for the current filtering for one request. Then it calls the desired action of the Export button directly. The variable addOptionscan be used to alter the standard configuration of the export button.

它将强制 DataTable 为一个请求请求当前过滤的所有行。然后它直接调用导出按钮的所需操作。该变量addOptions可用于更改导出按钮的标准配置。

You might run into problems though if you have a lot of rows as they are all loaded into the DOM.

如果您有很多行，因为它们都加载到 DOM 中，您可能会遇到问题。

回答by Ma?vydas Tadaravi?ius

I know this is an old question, however for anyone struggling with this, here's my solution.

我知道这是一个老问题，但是对于任何为此而苦苦挣扎的人，这是我的解决方案。

Variables:

变量：

var downloading = false,
    downloadTimestamp = null;

---

---

Download button definition:

下载按钮定义：

buttons: [{
    text: '<span class="glyphicon glyphicon-save-file" aria-hidden="true"></span>',
    titleAttr: 'CSV',
    className: 'downloadCSV',
    action: function(e, dt, node, config) {
        if (downloading === false) { //if download is in progress, do nothing, else
            node.attr('disabled', 'disabled'); //disable download button to prevent multi-click, probably some sort of *busy* indicator is a good idea

            downloading = true; //set downloading status to *true*

            dt.ajax.reload(); //re-run *DataTables* AJAX query with current filter and sort applied
        }
    }
}]

---

---

Ajax definition:

阿贾克斯定义：

ajax: {
    url: ajaxURL,
    type: 'POST',
    data: function(data) {
        data.timestamp = new Date().getTime(); //add timestamp to data to be sent, it's going to be useful when retrieving produced file server-side

        downloadTimestamp = data.timestamp; //save timestamp in local variable for use with GET request when retrieving produced file client-side

        if (downloading === true) { //if download button was clicked
            data.download = true; //tell server to prepare data for download
            downloading = data.draw; //set which *DataTable* draw is actually a request to produce file for download
        }

        return { data: JSON.stringify(data) }; //pass data to server for processing
    }
}

---

---

'preDrawCallback' function:

'preDrawCallback' 函数：

preDrawCallback: function(settings) {
    if (settings.iDraw === downloading) { //if returned *DataTable* draw matches file request draw value
        downloading = false; //set downloading flag to false

        $('.downloadCSV').removeAttr('disabled'); //enable download button

        window.location.href = ajaxURL + '?' + $.param({ ts: downloadTimestamp }); //navigate to AJAX URL with timestamp as parameter to trigger file download. Or You can have hidden IFrame and set its *src* attribute to the address above.

        return false; //as it is file request, table should not be re-drawn
    }
}

---

---

Server-side:

服务器端：

if(download == false), then server executes SELECT columns FROM tables WHERE rowNumber BETWEEN firstRow AND lastRowand outputs result for normal display within DataTable.

if(download == false)，然后服务器执行SELECT columns FROM tables WHERE rowNumber BETWEEN firstRow AND lastRow并输出结果以在DataTable 中正常显示。

if(download == true), then server executes SELECT columns FROM tablesand stores all rows formatted as CSV file (or any other file format depending on what Your server environment is capable to produce) server-side for later retrieval by GET request.

if(download == true)，然后服务器执行SELECT 列 FROM 表并将所有行存储为 CSV 文件（或任何其他文件格式，取决于您的服务器环境能够生成的内容）服务器端，以便稍后通过 GET 请求检索。

Following is ASP JScript code that I've used server-side:

以下是我在服务器端使用的 ASP JScript 代码：

    var timestamp = Number(Request.QueryString('ts')), //if it's a GET request, get timestamp
        tableData = {
            draw: data.draw,
            recordsTotal: 100, //some number static or dynamic
            recordsFiltered: 10, //some number static or dynamic
            data: []
        };
        jsonData = String(Request.Form('data')), //if it's POST request, get data sent by *DataTable* AJAX
        data = jsonData === 'undefined' || jsonData.length === 0 ? null : JSON.parse(jsonData); //do some error checking (optional)

    if(!isNaN(timestamp)) { //check timestamp is valid
        var csvTextKey = 'download-' + timestamp, //this is where timestamp value is used (can be any other unique value)
            csvText = Session(csvTextKey); //obtain saved CSV text from local server-side storage

        if(typeof csvText === 'undefined') { //if CSV text does not exist in local storage, return nothing (or throw error is You wish)
            Response.End();
        }

        //if CSV exists:
        Response.ContentType = 'text/csv'; //set response mime type
        Response.AddHeader('Content-Disposition', 'attachment; filename=test.csv'); //add header to tell browser that content should be downloaded as file and not displayed

        Response.Write(csvText); //send all content to browser

        Response.End(); //stop further server-side code execution
    }

    //if timestamp is not valid then we assume this is POST request, hence data should be either prepared for display or stored for file creation

    if(typeof data !== 'object' || data === null) { //do some more clever error checking
        throw 'data is not an object or is null';
    }

        var recordset = data.download === true ? sqlConnection.Execute('SELECT * FROM #FinalTable') : Utilities.prepAndRunSQLQuery('SELECT * FROM #FinalTable WHERE rowId BETWEEN ? AND ?', [data.start, data.start + data.length], //execute SELECT either for display or for file creation
            headerRow = [],
            sqlHeaderRow = [],
            exportData = [];; 

        if(data.download === true) { //create CSV file (or any other file)
            if(!Array.isArray(data.columns)) {
                throw 'data.columns is not an array';
            }

            for(var i = 0, dataColumnsCount = data.columns.length; i < dataColumnsCount; ++i) {
                var dataColumn = data.columns[i], //get columns data object sent by client
                    title = dataColumn.title, //this is custom property set on client-side (not shown in code above)
                    sqlColumnName = typeof dataColumn.data === 'string' ? dataColumn.data : (typeof dataColumn.data.display === 'string' ? dataColumn.data.display : dataColumn.data['_']); //set SQL table column name variable

                if(typeof title === 'string' && typeof sqlColumnName === 'string' && columnNames.indexOf(sqlColumnName) > -1) { //some more error checking
                    headerRow.push(title);
                    sqlHeaderRow.push(sqlColumnName);
                }
            }

            exportData.push('"' + headerRow.join('","') + '"'); //add table header row to in CSV file format
        }

        while(recordset.EOF === false) { //iterate through recordset
            if(data.download === true) { //if download flag is set build string containing CSV content
                var row = [];

                for(var i = 0, count = sqlHeaderRow.length; i < count; ++i) {
                    row.push(String(recordset.Fields(sqlHeaderRow[i]).Value).replace('"', '""'));
                }

                exportData.push('"' + row.join('","') + '"');
            }

            else { //else format data for display
                var row = {};

                for(var i = 1, fieldsCount = recordset.Fields.Count; i < fieldsCount; ++i) {
                    var field = recordset.Fields(i),
                        name = field.Name,
                        value = field.Value;

                    row[name] = value;
                }

                tableData.data.push(row);
            }

            recordset.MoveNext();
        }

if(data.download === true) { //save CSV content in server-side storage
    Session('download-' + data.timestamp) = exportData.join('\r\n'); //this is where timestamp value is used (can be any other unique value)
}

Response.Write(JSON.stringify(tableData)); //return data for display, if download flag is set, tableData.data = []

回答by Jagatheesh

If you use Laravel framework, you can use this....

如果你使用 Laravel 框架，你可以使用这个......

$.fn.DataTable.Api.register( 'buttons.exportData()', function( options ) {
  if(this.context.length) {

    var src_keyword = $('.dataTables_filter input').val();

    // make columns for sorting
    var columns = [];
    $.each(this.context[0].aoColumns, function(key, value) {
      columns.push({
        'data' : value.data, 
        'name' : value.name, 
        'searchable' : value.bSearchable, 
        'orderable' : value.bSortable
      });
    });

    // make option for sorting
    var order = [];
    $.each(this.context[0].aaSorting, function(key, value) {
      order.push({
        'column' : value[0], 
        'dir' : value[1]
      });
    });

    // make value for search
    var search = {
      'value' : this.context[0].oPreviousSearch.sSearch, 
      'regex' : this.context[0].oPreviousSearch.bRegex
    };

    var items = [];
    var status = $('#status').val();
    $.ajax({
      url: "server_side_url",
      data: { columns: columns, order: order, search: search, status: status, page: 'all' }
      success: function (result) {

        $.each(result.data, function(key, value) {

          var item = [];

          item.push(key+1);
          item.push(value.username);
          item.push(value.email);
          item.push(value.created_at);
          item.push(value.status);

          items.push(item);
        });
      },
      async: false
    });

    return {
      body: items, 
      // skip actions header
      header: $("#user_table thead tr th").map(function() { 
        if(this.innerHTML!='Actions')
          return this.innerHTML; 
      }).get()
    };
  }
});

var user_table = $('#user_table').DataTable({
  dom: 'Bfrtip',
  buttons: [
  'copy', 'csv', 'excel', 'pdf', 'print'
  ],
  "oSearch": {"bSmart": false},
  processing: true,
  serverSide: true,
  ajax: {
    url: "server_side_url",
    type: 'GET',
    data: function (d) {
      d.status = ""; // when onload make status as empty to get all users
    }
  },
  columns: [
  {data: 'DT_RowIndex', name: 'DT_RowIndex'},
  {data: 'username', name: 'username'},
  {data: 'email', name: 'email'},
  {data: 'created_at', name: 'created_at'},
  {data: 'status', name: 'status'},
  {data: 'actions', name: 'actions', orderable: false, searchable: false},
  ],
});

// filter users with status
$('#status').change(function() {
  user_table.draw();
});

回答by Juneho Nam

@diogenesgg answer is good!

@diogenesgg 答案很好！

but I checked $.fn.DataTable.Api.registerdont support Promise

但我检查了$.fn.DataTable.Api.register不支持Promise

So, I fetched data first.

所以，我先获取了数据。

    const {data} = await selectDailyConnectStatistics({
      page: 1,
      limit: 99999999
    }))
    excelDatas = data.list

    $("#table").DataTable().button('.buttons-excel').trigger();

Second trigger excel export.

第二次触发excel导出。

  let excelDatas = []
  $.fn.DataTable.Api.register('buttons.exportData()', function(options) {
    if (this.context.length ) {
      return {
        body: _.map(excelDatas, v=> [v.data, ...]), 
        header: ['colum header name', ...]
      }
    }
  });

回答by Mohammad Taheri

you can make a hidden extra table in your page then make a button for download all data , assign this code for make hidden table as datatable with all rows with these options

您可以在您的页面中创建一个隐藏的额外表格，然后制作一个下载所有数据的按钮，将此代码分配为使隐藏表格成为具有这些选项的所有行的数据表

var options = {
            "processing": true,
            "serverSide": true,
            "ajax": fullbase,
            "language": {
                "search": "Buscar: ",
                "zeroRecords": "Datos no encontrados."
            },
            "initComplete": function(settings, json) {
                $(".buttons-excel").click();
            },
            "iDisplayLength": 100000,
            lengthMenu: [[10,25,50,100, 100000], [10,25,50, 100, "All"]],
            "buttons": [{
                extend : 'excel',
                exportOptions : {
                        order : 'current',  
                        page : 'all',    
                        search : 'none' 
                }
            }],
            "dom": "Blfrtip",
        };

you can see a trigger on export excel button in complete table event and will run automatically for user when user click on that button then get an excel with all data

您可以在完整表格事件中看到导出 excel 按钮上的触发器，当用户单击该按钮时将自动为用户运行，然后获取包含所有数据的 excel

回答by Mrityunjoy Choudhury

The answer of Selcuk will work absolutely fine if we can fix the value of "All" beforehand. Suppose the number of rows is stored in variable row_count. Then

如果我们可以事先修复“All”的值，Selcuk 的答案将绝对有效。假设行数存储在变量 row_count 中。然后

var row_count = $("#row_count").val();
var table = $('#example').DataTable({
    serverSide: true,
    ajax: "/your_ajax_url/",
    lengthMenu: [[25, 100, row_count], [25, 100, "All"]],
    pageLength: 25,
    buttons: [
        {
            extend: 'excel',
            text: '<span class="fa fa-file-excel-o"></span> Excel Export',
            exportOptions: {
                modifier: {
                    search: 'applied',
                    order: 'applied'
                }
            }
        }
    ],
    // other options
});