Excel Export all records with ajax and scroller true

Excel Export all records with ajax and scroller true

hpegmslicensemgmthpegmslicensemgmt Posts: 21Questions: 6Answers: 0

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');
                                    }
                    }
            ]
    });

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    Answer ✓

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

    Kevin

  • hpegmslicensemgmthpegmslicensemgmt Posts: 21Questions: 6Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    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

  • hpegmslicensemgmthpegmslicensemgmt Posts: 21Questions: 6Answers: 0

    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);
                                });
                                }
                        },
    
  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • hpegmslicensemgmthpegmslicensemgmt Posts: 21Questions: 6Answers: 0
    edited April 2022

    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.

Sign In or Register to comment.