Buttons - Export data when using server-side ajax processing

cramblda · September 2015

I'm looking for information regarding exporting when server-side ajax processing is employed.

cramblda · September 2015

To be more specific, in the old "Table Tools" examples it appeared you could specify an ajax url to get the export data. When I look over the new "Buttons" configuration docs, I'm not seeing a similar option. Can I retrieve the export data from an ajax route?

cramblda · September 2015

So is there no way to do this currently?

tsmlp · October 2015

Did you ever figure this out? I'm working on the same issue - I moved from using locally loaded data to server side and cannot figure out a simple solution using the new buttons. Thanks!

cramblda · October 2015

No. I haven't worked on it since posting my question. I was hoping to get some confirmation that there was some type of facility built in to DataTables for this behavior before starting any work, but I can't find any indication that there is native support yet.

jLinux · October 2015

Have you googled it? Theres 3 or 4 threads on this exact issue..

In short.. if you have so few rows that you can export all of them, it voids the reason behind using serverSide processing

jLinux · October 2015

cramblda · October 2015

@jLinux, appreciate your response.

Yes, I googled the issue several different ways when I originally posted the question and found 0 results that weren't for the old TableTools API. My question was posted Sept 21, chronologically before each and every one of the thread links you included in your followup response.

I wouldn't have bothered with the setup time for server side if its use didn't make a substantial improvement in performance for my situation. As such, removing server side is not a reasonable option for this situation. However, the number of records is no where near the limit for a reasonable export.

It sounds as though writing my own server side export is the best option in this case.

For others that may stumble across this thread, server side export of all data is not available with the Buttons API. At this point, if you want to use server side and have export support for all data, you will need to write your own export process.

jLinux · October 2015

No no.. it is possible, just not recommended.

tsmlp · October 2015

@jLinux - thank you for your responses. Like @cramblda I have a situation where loading the data client-side takes a significant amount of time (5000+ rows with 25 columns [side note: I have argued to clean the data up but sales guys want it all in one place so that is what I have to deal with]) especially in Firefox which does full table re-draws on every cell. The actual query is quite quick but drawing the initial table is what takes up the time and I'm not experienced enough with front end to speed it up much more and I have to insert the data into a wordpress site, altogether a not very pleasant situation.

cramblda · October 2015

Yes I saw those comments. Loading all the data into the DOM client side, before exporting the data, isn't reasonable for performance in my situation. My best path forward is to keep the server side configuration for DataTables, and then create my own button that servers up an export file from the server, bypassing the need to load any data into the DOM.

park896 · November 2015

I had this same issue and i worked around it by extending the excel export button and providing my own implementation of the export to excel. Granted I copied most of the hard stuff from the buttons.flash.js file. Here is my implementation, granted you will need to modify it to suit your needs. What you need to know.

getExportColumns is a simple function that returns true if the column at that index is visible.
$.royalWebMethod is a simple wrapper around $.ajax that calls .NET WebMethods so you can use your ajax method here. This is the same method that I am passing to the ajax parameter of table initializer.
I've chosen to limit that maximum number of exported rows to 10000. This is somewhat arbitrary but suits my needs.
I found that I have to run the ajax synchronously because the flash button in datatables will not properly export/save the file if I write data to it after an asyncnronous ajax call. If anyone finds a solution for this I'd love to see it.
My tables dont have a footer so I did not copy the implementation for that. You should just be able to uncomment the section below, but I haven't ever tested it.
I added support for boolean data type export, but it still doesnt support dates.
getGlyphText is a simple function that gets a span containing a bootstrap glyph and some text. Feel free to replace this with just plain old text or any other function.
The export uses the last successful ajax parameters, clones them, and then sets the display start and length variables. I'm using the legacy ajax parameters format because I've been using the tables for a while, but these should be fairly straight forward to override with the new format. You may need to do a deep clone using $.extend(true, {}, blah) but that is up to the reader to determine.

Feel free to use this if it helps you and please let me know if you find any issues with it.

        var getExcelCell = function (v) {
            if ($.isNumeric(v)) {
                return '<c t="n"><v>' + v + '</v></c>';
            }
            if (v === true || v === false) {
                v = v ? '1' : '0';
                return '<c t="b"><v>' + v + '</v></c>';
            }
            if (!v) {
                v = '';
            } else {
                v = v.replace(/<.*?>/g, '');
                v = v.replace(/&(?!amp;)/g, '&amp;');
            }
            return '<c t="inlineStr"><is><t>' + v + '</t></is></c>';
        };

    var tts = [];
    tts.push({
            extend: 'excel',
            text: getGlyphText('list', 'Excel'),
            action: function (e, dt, button, config) {
                button.prop('disabled', true);
                var cols = dt.columns(getExportColumns).indexes();
                
                var settings = dt.settings()[0];
                var colmap = cols.map(function (idx, i) {
                    return settings.aoColumns[idx];
                }).toArray();

                var sp = $.extend({}, dt.ajax.params());
                sp.iDisplayStart = 0;
                sp.iDisplayLength = 10000;

                $.royalWebMethod({
                    method: 'GetData',
                    dto: {
                        settings: sp
                    },
                    json: true,
                    sync: true
                }).done(function (d) {
                    var data = d.aaData;

                    var body = [];
                    if (config.header) {
                        var header = cols.map(function (idx, i) {
                            return getExcelCell(dt.column(idx).header().innerHTML);
                        }).toArray();
                        body.push('<row>' + header.join('') + '</row>');
                    }
                    $.each(data, function (r, record) {
                        var row = [];
                        row.push('<row>');
                        $.each(colmap, function (c, col) {
                            var v = col.fnGetData(record, 'display');
                            row.push(getExcelCell(v));
                        });
                        row.push('</row>');
                        body.push(row.join(''));
                    });

                    //if (config.footer) {
                    //var footer = cols.map(function (idx, i) {
                    //    return getExcelCell(dt.column(idx).footer().innerHTML);
                    //}).toArray();
                    //    xml += addRow(footer);
                    //}

                    var xml = body.join('');

                    var flash = config._flash;
                    flash.setAction('excel');

                    //set the default filename
                    var title = config.title;
                    if (title.indexOf('*') !== -1) {
                        title = title.replace('*', $('title').text());
                    }
                    // Strip characters which the OS will object to
                    title = title.replace(/[^a-zA-Z0-9_\u00A1-\uFFFF\.,\-_ !\(\)]/g, "");
                    title = title + config.extension;
                    flash.setFileName(title);

                    //This streams text to flash which according to datatables docs has limits
                    //thus the regex to split up into 8k chunks
                    var parts = xml.match(/[\s\S]{1,8192}/g) || [];
                    flash.clearText();
                    for (var i = 0, len = parts.length ; i < len ; i++) {
                        flash.appendText(parts[i]);
                    }
                }).always(function() {
                    button.prop('disabled', false);
                });

            }
        });

jLinux · November 2015

I see $.royalWebMethod, does that mean you're using ASP.NET?

I found that I have to run the ajax synchronously because the flash button in datatables will not properly export/save the file if I write data to it after an asynchronous ajax call. If anyone finds a solution for this I'd love to see it.

That kinda seems like an issue... @allan, would you know if its possible to accomplish this with an asynchronous request?

park896 · November 2015

Yes, I am using ASP.NET with page level WebMethod's to retrieve the data.

jLinux · November 2015

EEK!

Well glad you got your issue resolved.

allan · November 2015

I found that I have to run the ajax synchronously because the flash button in datatables will not properly export/save the file if I write data to it after an asynchronous ajax call

This is correct - there is no callback or promise in the default export Buttons to write data to. You could do it by using the ZeroClipboard API and writing your own custom button, but I can't emphasis enough how much I think using client-side creation of files with server-side processing is a bad idea. You loose all benefit of server-side processing and introduce the client-side limitations on file exporting.

Allan

musinik · January 2016

Hi everyone!
Is there an out-of-box solution now for the server-side data export?
Or we still have retired TableTools and custom scripts?

cramblda · January 2016

No change that I'm aware of.

The gist of the problem is most people switch to server side because there is too much data to load in the client all at once. As such, sending all the data to the client for export purposes (when your using server side) would cause the same performance issue. Due to this consideration, no builtin server side export API hook is built in.

However, someone did say you could override the page number in the URL of the button to 0 (zero), and then it will export all the data, instead of just the specific page the user is on.

musinik · January 2016

Okay. Thank for the information.
The tool is great anyway. It saved so much time with it's functionality.

My solution for this is the folllowing:
I decided to privide user ability to show all rows if required and called the button "Export visible rows".

These are my colums

columns: [ 1,12,13,15,16,17,18,19,20,6,7,8,9,10,11,22 ]  (16 rows in total)

But I think the usual upper limit of exported data would be 1000 rows.
Even if it was 5000 I think the modern machines will handle that normally, but haven't tested that.

Buttons - Export data when using server-side ajax processing

Buttons - Export data when using server-side ajax processing

Answers

Howdy, Stranger!

Categories

DataTables

Buttons - Export data when using server-side ajax processing

Buttons - Export data when using server-side ajax processing

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables