如何使用Ajax从数据表导出所有行?

2021/01/11 00:11 · jquery ·  · 0评论

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

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

问题在于它仅导出当前显示的页面。

我正在这样导出:

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

如何导出所有行?

根据DataTables文档,使用服务器端时无法导出所有行:

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

我通过在长度菜单中添加“ 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
});

您需要告诉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();
};

和:

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

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

在内部 $(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

对于按钮,如下定义

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

而已。现在您的下载已准备就绪。

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

您在初始化表之前将其覆盖。代码如下:

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

此按钮定义在滚动表(而不是分页)中为我工作:

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

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

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

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

变量:

var downloading = false,
    downloadTimestamp = null;

下载按钮定义:

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定义:

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'函数:

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

服务器端:

if(download == false),则服务器从WHERE rowNumber在firstRow和lastRow之间的表中执行SELECT列,并输出结果以在DataTable中正常显示

if(download == true),然后服务器从表中执行SELECT列,并将所有格式化为CSV文件(或任何其他文件格式,取决于您的服务器环境能够产生的文件格式)的行存储在服务器端,以便以后通过GET请求进行检索。

以下是我在服务器端使用过的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 = []

如果您使用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();
});

如果我们可以预先确定“ 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
}); 

我正在使用数据表版本:1.10.15,并得到了@kevenpo的答复才能工作。我不得不对其进行一些修改以处理我们的服务器端参数,但这是唯一的绊脚石。我改变了他的路线:data.length = 2147483647;data.params[2]= -1;,因为我们存储在PARAMS子阵列我们的服务器端的参数。我尚未使用非常大的数据集对其进行测试以查看性能,但这是一个非常聪明的解决方案。

只是想为挣扎于此的人们发布一个实际答案。

如果要使用excel按钮进行导出,则可以使用customizeDatabutton属性来格式化要导出为ex​​cel的数据,然后再导出。

我用它来对服务器进行同步api调用,以获取数据,返回数据,对其进行按摩,然后继续进行下去。下面的代码。

                           {
                extend: 'excel',
                customizeData: function (p)
                {
                    //get the params for the last datatables ajax call
                    var params = JSON.parse(options.dataTable.ajax.params());
                    //flag to tell the server to ignore paging info and get everything that matches the filter
                    params.export = true;
                    UC.Api(options.api.read.getHook(), params, function (data)
                    {
                        p.body = new Array();
                        $.each(data.data, function (i, d)
                        {
                            var item = [d.serial, UC.FormatDateToLocal(d.meta.Date), d.transmission.title, d.transmission.type, d.transmission.information];
                            p.body.push(item);
                        });
                    }, null, { async: false });
                }
            },

@diogenesgg的回答很好!

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

因此,我首先获取了数据。

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

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

第二次触发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', ...]
      }
    }
  });

您可以在页面中创建一个隐藏的额外表,然后单击一个按钮以下载所有数据,将此代码分配为将隐藏表作为datatable包含所有带有这些选项的行

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

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

本文地址:http://jquery.askforanswer.com/ruheshiyongajaxcongshujubiaodaochusuoyouxing.html
文章标签: ,   ,   ,   ,  
版权声明:本文为原创文章,版权归 admin 所有,欢迎分享本文,转载请保留出处!

文件下载

老薛主机终身7折优惠码boke112

上一篇:
下一篇:

评论已关闭!