Altering exported table data

jLinux

I was looking for a way to alter the table data before exporting it, also would need to be able to determine which column it is to edit.

For example, if im exporting a table with the columns Status and Children, I wanted to know how I can change the boolean value of the Status column from 0/1 to Single/Married, and leave the children column as it is.

I used these two columns as example, because they can both contain a 0 and a 1, but only one should be altered.

Other columns also have epoch timestamps that I would like to parse into human readable dates, using a jQuery function I created

I looked in: print, pdf, button(), buttons.exportData() and a few other reference pages.

The funny/annoying part is, im pretty sure ive done this before somehow, but I cant find my old code, and I couldnt find anything about it on the website, and nothing when I consulted the Google Gods..

Thanks!

jLinux

The only thing I found that might be of some help, was this: https://www.datatables.net/extensions/tabletools/button_options

But the span at the top says its been retired, and I couldn't find anything new about it

jLinux

After a lot of Googling, I found this thread, In which @allan states:

If you want to custom format the text for the exported data, you need to use the orthogonal option of the buttons.exportData() method (which you can set using the exportOptions option for the button you are working with (e.g. excelHtml5).

That uses DataTables' orthogonal data abilities - so you would need to define a columns.render option for the column you want to customise the data export for, and give it a function that will handle the orthogonal data you want to export.

which seems like it might be what I need... Can I define a columns.render which will be used by the orthogonal option of the buttons.exportData(), but not assign it to a column in the table for the initial display?

btree

Hi,

not tested but you can use your own names for nested object, but dont know how it works with the export button.

//Data Source

{
    "Children":       "0",
    "Status": {
        "display": "0",
        "export": "Single"
    }
}

//Export

var data = table.buttons.exportData( {
    orthogonal: export;    //Default is "display"
} );

Cheers
Hannes

jLinux

Do those go inside the columnDefs ?

Heres what ive got so far, I havent even tried to use it in the export buttons yet though.. just working on defining how to render them

columnDefs: [
    {
        targets: [1,3], // Created and Modified timestamps
        data: null, 
        render: function ( data, type, row, meta ) {
            // If display or filter data is requested, format the date
            if ( type === 'display' || type === 'filter' ) {
                var d = new Date( data * 1000 );
                return d.getDate() +'-'+ (d.getMonth()+1) +'-'+ d.getFullYear();
            }

            // Otherwise the data type requested (`type`) is type detection or
            // sorting data, for which we want to use the integer, so just return
            // that, unaltered
            return data;
        }
    },
    {
        targets: [0,2], // Creator and Modifier usernames
        data: null,
        render: function ( data, type, row, meta ) {
            if( ! data || typeof data === 'undefined')
                return '';

            var link = {};

            if(type === 'display'){
                if(meta.col === 0){
                    link.href = '/account/details/'+full.creator;
                    link.text = full.creator_username;
                }
                else if(meta.col === 2) {
                    link.href = '/account/details/' + full.modifier;
                    link.text = full.modifier_username;
                }
                
                return $( '<a>', link).get(0);
            }
            else if(type === 'filter'){
                return data;
            }

            return data;
        }
    }
],

jLinux

Alrighty.. I think I got it...

Only part im stuck on now, is for the columnDef targets 0,2 (usernames), I want it to display a link, but export just text, when I print it, it displays the link URL right next to the text: http://d.pr/i/TtxO

Any way to get that so it just exports the username as text?

Edit: Looks like I was able to get this by setting the stripHtml to true

Thanks @btree

Looks like everything else is OK though!

columnDefs: [
    {
        targets: [1,3], // Created & Modified timestamps
        data: null, // Use the full data source object for the renderer's source
        render: function ( data, type, row, meta ) {
            if( ! data || typeof data === 'undefined' || data === null)
                return '';

            // If display or filter data is requested, format the date
            if ( type === 'display' || type === 'filter' ) {
                return tools.date(null, data * 1000);
            }
            else if(type === 'sort'){
                // If sorting, sort by the actual timestamp
                return data;
            }

            // Just in case
            return data;
        }
    },
    {
        targets: [0,2], // Creator & Modifier usernames
        data: null, // Use the full data source object for the renderer's source
        render: function ( data, type, row, meta ) {
            // if the data is empty, then just return nothing (should only
            // happen for empty modified dates)
            if( ! data || typeof data === 'undefined' || data === null)
                return '';

            // Create a link for the display
            if(type === 'display'){
                var link = {
                    href: '#',
                    text: '[error]'
                };

                // Creator
                if(meta.col === 0){
                    link.href = '/account/details/'+row.creator;
                    link.text = row.creator_username;
                }
                // Modifier
                else if(meta.col === 2){
                    link.href = '/account/details/'+row.modifier;
                    link.text = row.modifier_username;
                }

                return '<a href="'+link.href+'" target="_blank">'+link.text+'</a>';
            }
            // Filter/sort shouldnt see links
            else if(type === 'filter' || type === 'sort'){
                return data;
            }

            return data;
        }
    },
    {
        targets: 4, // Status
        data: null, // Use the full data source object for the renderer's source
        render: function ( data, type, row, meta ) {

            if( ! data || typeof data === 'undefined' || data === null)
                return 'Unlocked';
            else if(data === 'locked')
                return 'Locked';
            else
                return 'Secured';
        }
    }
],