Triggering export after table is redrawn to show all records

Triggering export after table is redrawn to show all records

wblakencwblakenc Posts: 77Questions: 17Answers: 1

Hello all,
I have datatables set up to use server side processing and as we all know, if you click export it will only export what it has in memory. This makes perfect sense, however I need the ability to export all rows. I know there are various other questions about this, and other ways to solve it, however I am interested in try to automatically change the page length to 'All' when the user clicks the export button.

i.e.:

 "buttons": 
            [
                { 
                    extend: "edit",   
                    editor: editor 
                },
                { 
                    extend: 'excel',
                    text: 'Export to Excel',
                    action: function ( e, dt, node, config ) {
                        theTable.page.len(-1).draw();
                        theTable.button('1').trigger();
                        
                    }
                }
            ]

The above throws a Stackoverflow (which I think makes some sense as it looks like an endless loop). Long story short how do I (or can I?) change the action of the excel export to first change the page length to 'All' then export the data? I can always instruct my users to manually select 'All' then export, but they won't listen.

I have also tried:

 "buttons": 
            [
                { 
                    extend: "edit",   
                    editor: editor 
                },
                { 
                    extend: 'excel',
                    text: 'Export to Excel',
                    action: function ( e, dt, node, config ) {
                        theTable.page.len(-1).draw();
                        $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
                        
                    }
                }
            ]

Which 'works' in that it does export the data but it doesn't wait for the draw to complete which means it only exports the first page. Is there anyway to have the $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config); wait until after the draw completes?

Thoughts?

This question has an accepted answers - jump to answer

Answers

  • wblakencwblakenc Posts: 77Questions: 17Answers: 1

    Also on a side note when I try:

     "buttons": 
                [
                    { 
                        extend: "edit",   
                        editor: editor 
                    },
                    { 
                        
                        text: 'Export to Excel',
                        action: function ( e, dt, node, config ) {
                            frppTable.page.len(-1).draw();
                            $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
                            
                        }
                    }
                ]
    

    I get the following error:

    datatables.min.js:250 Uncaught (in promise) TypeError: Cannot read property 'indexOf' of undefined
        at w (datatables.min.js:250)
        at datatables.min.js:265
        at <anonymous>
    

    Which is odd as it is almost exactly mirrors the example on https://datatables.net/reference/option/buttons.buttons.action

    Perhaps I am missing something?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Try this:

    action: function ( e, dt, node, config ) {
      dt.one( 'draw', function () {
        $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
      } );
      dt.page.len(-1).draw();
    }
    

    The issue you are running into is that the draw action when using server-side processing is async. So you have to wait for the next draw before the data is known to be loaded.

    Allan

  • wblakencwblakenc Posts: 77Questions: 17Answers: 1

    Thanks Allan! However, when enter the above it results in the following error:

    Uncaught TypeError: this.processing is not a function
        at HTMLTableElement.action (datatables.js:39288)
        at HTMLTableElement.<anonymous> (index.cfm?action=actUserLogin:549)
        at HTMLTableElement.fn (datatables.js:4508)
        at HTMLTableElement.dispatch (datatables.js:4749)
        at HTMLTableElement.elemData.handle (datatables.js:4561)
        at Object.trigger (datatables.js:7819)
        at HTMLTableElement.<anonymous> (datatables.js:7887)
        at Function.each (datatables.js:377)
        at jQuery.fn.init.each (datatables.js:149)
        at jQuery.fn.init.trigger (datatables.js:7886)
    action @ datatables.js:39288
    (anonymous) @ index.cfm?action=actUserLogin:549
    fn @ datatables.js:4508
    dispatch @ datatables.js:4749
    elemData.handle @ datatables.js:4561
    trigger @ datatables.js:7819
    (anonymous) @ datatables.js:7887
    each @ datatables.js:377
    each @ datatables.js:149
    trigger @ datatables.js:7886
    _fnCallbackFire @ datatables.js:27865
    _fnDraw @ datatables.js:24738
    _fnAjaxUpdateDraw @ datatables.js:25363
    (anonymous) @ datatables.js:25203
    callback @ datatables.js:25095
    success @ datatables.js:25125
    fire @ datatables.js:3199
    fireWith @ datatables.js:3329
    done @ datatables.js:8769
    (anonymous) @ datatables.js:9135
    XMLHttpRequest.send (async)
    send @ datatables.js:9187
    ajax @ datatables.js:8668
    _fnBuildAjax @ datatables.js:25179
    _fnAjaxUpdate @ datatables.js:25199
    _fnDraw @ datatables.js:24662
    _fnReDraw @ datatables.js:24781
    (anonymous) @ datatables.js:28669
    iterator @ datatables.js:28249
    (anonymous) @ datatables.js:28658
    (anonymous) @ datatables.js:28417
    action @ index.cfm?action=actUserLogin:551
    action @ datatables.js:37106
    (anonymous) @ datatables.js:37121
    dispatch @ datatables.js:4749
    elemData.handle @ datatables.js:4561
    

    Just to confirm my setup is correct here is my code:

     "buttons": 
                [
                    { 
                        extend: "edit",   
                        editor: editor 
                    },
                    { 
                        
                        extend: 'excel',
                        text: 'Export to Excel',
                        action: function ( e, dt, node, config ) {
                          dt.one( 'draw', function () {
                            $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
                          } );
                          dt.page.len(-1).draw();
                        }
                    }
                ]
    
  • wblakencwblakenc Posts: 77Questions: 17Answers: 1

    Allan,
    I changed my code a little bit to include the non minified version of buttons.html5.js which gave me the ability to debug the issue I was getting. I found that when I comment out: this.processing( true ); on line 989 and that.processing( false ); on line 1202 and I was able to refresh (draw) the table showing all records and export the results as a excel doc.

  • besnoktabesnokta Posts: 1Questions: 0Answers: 1
    Answer ✓

    You don't need make that.processing( false );
    Just call action button with $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config); after dt.one scopes.

  • powerwerker_tbpowerwerker_tb Posts: 2Questions: 1Answers: 0

    Allan,
    I tried using the code you offered in your last comment, but as a result the export dialogue appears every time the table is drawn now. Is there any way to unset the on draw event so that it only fires for the export buttons?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Did you use one() rather than on()?

    Can you link to a page showing the issue so I can offer some help.

    Thanks,
    Allan

  • iscarfaceiscarface Posts: 2Questions: 0Answers: 0

    I've experienced the same issue same with @wblakenc regarding this.processing is not a function. I just want to add some additional detail to help future viewer of this thread.

    action: function ( e, dt, node, config ) {
        dt.one( 'draw', function () {
            $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
        });
        dt.page.len(-1).draw();
    }
    

    As Allan pointed out on this SO' Answer, this is a scoping issue.

    Inside the dt.one draw scope, this should be the button object, which is not the case on the code above. so to fix this error, we should define this outside the draw scope, something like this:

    action: function ( e, dt, node, config ) {
        var myButton = this;
        dt.one( 'draw', function () {
            $.fn.dataTable.ext.buttons.excelHtml5.action.call(myButton, e, dt, node, config);
        });
        dt.page.len(-1).draw();
    }
    

    That should work now! Thanks Allan and everyone on this thread.

  • GerardoVGerardoV Posts: 3Questions: 0Answers: 0
    edited August 2020

    Hi Guys, the last example from @iscarface works great to export all the pages to excel on a server side scenario.

    Have some questions

    1) How can I revert back the page.len to some specific value or default once the excel was generated? On the top of my head I would chain a function to the excel export button to rest the page len to the default but have no clue on the name of that callback to code it.

    2) Is there any parameter to tweak the colums headers style, color, etc of the exported excel file?

    3) Any other fancy parameter to hide columns at export time?

    Thanks in advance,
    Gerardo

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    1. this thread should help, it's showing how to detect when the export is complete. you can then do whatever logic then to change the page length with page.len()
    2. this thread should help on that - it's changing the table body, but the same principle should apply
    3. This thread should help, it's asking the same thing.

    Cheers,

    Colin

  • GerardoVGerardoV Posts: 3Questions: 0Answers: 0

    Hi Colin, Thanks so much for your reply.

    Your suggestions for the points 2 and 3 are a light at the end of the tunnel. Thanks so much for point me on the right path!

    Regarding point 1 (The export all pages to Excel), I was mocking examples for two days with no success.

    For this, I land on the following thread where the user vbalsamello expose a pretty handy way to manage the switching of the data.length to a big number, export the Excel avoiding screen redraw and reverting back all to the original state.

    https://datatables.net/forums/discussion/comment/152343/#Comment_152343

    In in my case I've used -1 for the data.length parameter that's what my server is checking to list all the records.
    But the -1 never arrieve to the server and get lost in the roud.
    If I use, the Select on screen that's configured with a All and -1 it works ok so the server part is working ok.

    All the logic form vbalsamello looks correct but, I discovered that the preXhr function is receiving 'data' as string rather than object making impossible to add or change parameters like it is indicated in the manual at https://datatables.net/reference/event/preXhr.

    So this cannot be done this way as you cannot operate with data that's a json in string format as if it were an object.

    $('#example')
        .on('preXhr.dt', function ( e, settings, data ) {
            data.sessionId = $('#sessionId').val();
        } )
        .dataTable( {
            ajax: "data.json"
        } );
    

    Researching again, I discovered a response from You suggesting to: a) JSON.parse() data, b) Add what you need c) JSON.stringify() to revert data to it's original state

    https://datatables.net/forums/discussion/comment/132155/#Comment_132155

  • GerardoVGerardoV Posts: 3Questions: 0Answers: 0

    Well, before the long try an error with the preXhr, I performed the parse and stringify and all looks ok in the console.

    Data shows length = -1 but when the code continue, at the 2nd preXhr data shows back again the original size and the -1 is lost.

    The following is the code I'm using annd below this you can find the console output.

                    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...
        
                            var dataObj = JSON.parse(data);
                            dataObj.start = 0;
                            dataObj.length = -1;            
                            
                            data = JSON.stringify(dataObj);
        
                            console.log('data 1st preXhr: ', data);                 
        
                            // Antes de redibujar el dom interno del plugin, exporta el excel
                            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.
        
                                    console.log('data 2nd preXhr: ', data);     
        
                                    var dataObj2 = JSON.parse(data);
        
                                    settings._iDisplayStart = oldStart;
                                    dataObj2.start = oldStart;
                                    data = JSON.stringify(dataObj2);
        
                                });
                    
                                // 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();
                    };
    
    

    Console Output

    I'm totally with you regarding to produce the Excel from the server, specially for a large datasets but, for now I need to release this with the excelHtml5 option and code all the server export part at a later phase.

    Does you know the reason why data has changed to string inside preXhr?
    Is there any way to change the properties of data inside preXhr but in a permanent way to do the Ajax call with those changes?

    Sorry for the long explanation. Hope this also help others too.

    cheers,

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The second preXhr is happening on your next draw request and since you aren't changing the page length on the client-side in the first preXhr the second one is taking the value from the client-side.

    Isn't that the intention of that setup though? Make one request with the length at -1 and export that data. Then make another request to get things back to normal?

    Thanks,
    Allan

  • ziakhan78ziakhan78 Posts: 1Questions: 0Answers: 0

    Hi Allan,
    Can you please help me how to do that? means after export the data how to get back to normal?

    Thanks & Regards,
    Zia

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    As Allan said, length of -1 means "everything" - so when you want to return to the standard page length, just send the page length as it was before,

    Colin

  • iscarfaceiscarface Posts: 2Questions: 0Answers: 0

    @GerardoV sorry for the late reply, I haven't check datatables forum for a while until I've seen this thread again.

    Regarding your question:

    Have some questions

    1) How can I revert back the page.len to some specific value or default once the excel was generated? On the top of my head I would chain a function to the excel export button to rest the page len to the default but have no clue on the name of that callback to code it.

    This is what I am doing it in my apps:

    action: function(e, dt, node, config) {
        var dtButton= this; //we need this as param for action.call()
        var currentPageLen = dt.page.len();
        var currentPage = dt.page.info().page;
    
        dt.one( 'draw', function () {
            $.fn.DataTable.ext.buttons.excelHtml5.action.call(dtButton, e, dt, node, config); //trigger export
    
            //setTimeout is needed here because action.call is async call
            //without setTimeout, pageLength will show all 
            setTimeout(function() {
                dt.page.len(currentPageLen).draw(); //set page length
                dt.page(currentPage).draw('page'); //set current page
            }), 500;
        });
    
        //draw all before export
        dt.page.len(-1).draw();
    }
    
This discussion has been closed.