Triggering export after table is redrawn to show all records

wblakenc

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?

wblakenc

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?

allan

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

wblakenc

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

wblakenc

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.

besnokta

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_tb

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?

allan

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

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

Thanks,
Allan

iscarface

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.

GerardoV

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

colin

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

GerardoV

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

GerardoV

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,

allan

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

ziakhan78

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

colin

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

iscarface

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