How to export all records into excel in Serverside datatables?

How to export all records into excel in Serverside datatables?

KeithMaxKeithMax Posts: 2Questions: 1Answers: 0
edited January 2022 in DataTables 1.10

I'm calling below actions method in

  "buttons": [
                             {

                                "extend": 'excel',
                                "text": '<button class="btn"><i class="fa fa-file-excel-o" style="color: green;"></i>Export</button>',
                                "titleAttr": 'Excel',
                                "action": newexportaction,
                                "exportOptions": {
                                    columns: ':not(:last-child)',
                                },
                                "filename": function () {
                                    var d = new Date();
                                    var l = d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate();
                                    var n = d.getHours() + "-" + d.getMinutes() + "-" + d.getSeconds();
                                    return 'List_' + l + ' ' + n;
                                },
                            },
                        ],
`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();
};`

Once action method is called data is start fetching but after sometime I'm getting JSON serialize error.
Like data length is exceed the defined value.
please give some advice how to proceed further.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,142Questions: 1Answers: 2,586
    Answer ✓

    There are a few threads discussing this, such as here and here, that should get you going,

    Colin

  • KeithMaxKeithMax Posts: 2Questions: 1Answers: 0

    Thank you colin

Sign In or Register to comment.