Excel Export all records with ajax and scroller true

hpegmslicensemgmt

Dear all,

I have a pretty big datatable which is using ajax for the data retrieval from the Database and the scroller extension to load the data only when it's needed.

When I export the data to Excel, I only get the records that have been in the "cursor" around 300 ish...

I see in the Chrome Dev Tools, that the ajax is not fired prior to the export - so I assume datatables just uses the data from it's cache....

Is there a simple way that datatables would read all records, prior to export ?

Best regards,
David

    var oTable = $('#myTable').DataTable({
            scrollX: true,
            scrollY: sHeight,
            serverSide: true,
            ordering: true,
            dom: 'B<"#dateselect">fr<t>ip',
            stateSave: false,
            deferRender: true,
            ajax: {
                    url: "ajax.php",
                    data: function ( d ) {
                            d.AjaxAction = 'datatables';
                            d.SQLWhere = $('#SQLWhere').val();
                    },
                    type: 'GET'
            },
            order: [[ 0, "asc"]],
            scroller: {
                    loadingIndicator: true
            },
            search: {
                    return: true
            },
            columnDefs: [
                    {
                            targets: [<?php echo $_SESSION['numtargets'] ?>],
                            className: 'dt-body-right'
                    }
            ],
            buttons: [
                    {
                    extend: 'copy',
                    text:      '<img src="images/copy.png" border="0">',
                    titleAttr: 'Copy'
                    },
                    {
                    extend: 'csv',
                    text:      '<img src="images/csv.png" border="0">',
                    titleAttr: 'CSV',
                    filename: '<?php echo $_SESSION['title']; ?>'
                    },
                    {
                    extend: 'excel',
                    text:      '<img src="images/excel.gif" border="0">',
                    titleAttr: 'XLS',
                    className: 'ExcelExport',
                    filename: '<?php echo $_SESSION['title']; ?>',
                    title: 'null',
                    exportOptions: {
                            modifier: {
                                    order: 'current',
                                    page: 'all'
                            }
                    }
                    },
                    {
                    extend: 'print',
                    autoPrint: false,
                    text:      '<img src="images/print.png" border="0">',
                    titleAttr: 'Print'
                    },
                    {
                    extend: 'pdf',
                    orientation: 'landscape',
                    pageSize: 'A4',
                    text:      '<img src="images/pdf.png" border="0">',
                    titleAttr: 'PDF',
                                    title: '<?php echo $_SESSION['title']; ?>'
                    },
                    {
                    text:   '<img src=images/filter.gif border=0>',
                                    className: 'FilterButton',
                                    action: function ( e, dt, node, config ) {
                                            $('#builderfilter').dialog('open');
                                    }
                    }
            ]
    });

kthorngren

See this FAQ about using Server Side Processing with the export buttons.

Kevin

hpegmslicensemgmt

Dear Kevin, thank you very much for your answer !

Do you have an idea if there is a way to execute a command prior to the export when clicking on the Excel button ?

In this case I could trigger an ajax refresh and tell the php script to send all records ...

Best regards,
David

kthorngren

You can create a Custom button like this example. The buttons.buttons.action docs show how to call one of the export functions like Excel.

Kevin

hpegmslicensemgmt

Dear Kevin,

The following Button works perfectly and reloads the complete dataset prior to export.
A huge THANKS for your help :-)

Just one last small thing, the generated XLSX File has no headers. Any idea how I can tell the export to include the headers ?

Best regards,
David

                    text: 'My button',
                    action: function ( e, dt, node, config ) {
                            $('#LoadAll').val('True');
                            var myTable = this;
                            oTable.ajax.reload( function ( json ) {
                                    $.fn.dataTable.ext.buttons.excelHtml5.action.call(myTable, e, dt, node, config);
                            });
                            }
                    },

colin

It should export the headers by default. Is the issue that you have multiple rows in your header? If not, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

hpegmslicensemgmt

Dear all,
I found a small work around for my excel header issue:

text: 'My button',
action: function ( e, dt, node, config ) {
        $('#LoadAll').val('True');
        var myTable = this;
        oTable.ajax.reload( function ( json ) {
                $('.buttons-excel').click();
        });
        }
},

Now I get the perfect Excel export with all records and the headers :-)

Best regards,
David

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