Export hidden columns, but exclude their rendered data on other columns

raenk

I have some tables that, for design purposes, some columns are hidden and their data is rendered (concatenated) in other columns and using <br> to separate the data in new lines.

However, while exporting these tables I ran into the known problem regarding line breaks in excel and you can imagine how it looks as a single string.

Right now I'm replacing <br> with " | " as a separator, but I think the best workaround would be exporting the tables as originally intended, meaning exporting hidden columns and instead hide the "extra" data rendered.

Exporting the hidden columns is not a problem, but I can't think of a way to hide the rendered data.

I will appreciate your advice.

Here's an snipped of the relevant code as an example:

    var bConf = [
        {
            extend: "copy",
            text: 'Copiar',
            className: "btn-xs btn-pink",
            exportOptions: {
                columns: ['th:not(.no-export):visible'],
                format: {
                    body: function ( data, column, row ) {
                        if (typeof data === 'string' || data instanceof String) {
                            data = data.replace( /<br\s*\/?>/ig, " | " ).replace( /<[^>]*>/g, '' ).replace( /^\s+|\s+$/g, '' ).replace( /\n/g, ' ' );
                        }
                        return data;
                    }
                }
            }                
        },
        {
            extend: "excel",
            text: 'Excel',
            className: "btn-xs btn-pink",
            exportOptions: {
                columns: ['th:not(.no-export):visible'],
                format: {
                    body: function ( data, column, row ) {
                        if (typeof data === 'string' || data instanceof String) {
                            data = data.replace( /<br\s*\/?>/ig, " | " ).replace( /<[^>]*>/g, '' ).replace( /^\s+|\s+$/g, '' ).replace( /\n/g, ' ' );
                        }
                        return data;
                    }
                }
            },
            customize: function( xlsx ) {
                $(xlsx.xl["styles.xml"]).find('numFmt[numFmtId="164"]').attr('formatCode', '[$$-80A]#,##0.00;[RED]-[$$-80A]#,##0.00');
            }                
        },            
        {
            extend: "print",
            text: 'Imprimir',
            className: "btn-xs btn-pink",
            exportOptions: {
                columns: ['th:not(.no-export):visible'],
                format: {
                    body: function ( data, column, row ) {
                        if (typeof data === 'string' || data instanceof String) {
                            data = data.replace( /<br\s*\/?>/ig, " | " ).replace( /<[^>]*>/g, '' ).replace( /^\s+|\s+$/g, '' ).replace( /\n/g, ' ' );
                        }
                        return data;
                    }
                }
            }
        }
    ];

        "order": [0, "asc"],
        "processing": true,
        "serverSide": true,
        responsive: false,
        stateSave: true,
        "ajax": {
            url : dataUrl,
            type : 'GET'
        },
        "columnDefs": [
            { "render": function ( data, type, row ) { return data + row[1] + row[2] + row[3]; }, "targets": 0 },
            { "render": function ( data, type, row ) { return data + row[5] + row[6]; }, "targets": 4 },
            { "visible": false,  "targets": [ 1, 2, 3, 5, 6 ] },
            { className: "text-center", "targets": [ 7, 8 ] }
        ],
        buttons: bConf

kthorngren

Right now I'm replacing <br> with " | " as a separator, but I think the best workaround would be exporting the tables as originally intended, meaning exporting hidden columns and instead hide the "extra" data rendered.

Thats probably a good choice. I needed something similar so worked out the solution in this thread. You are probably better off not doing this

Take a look at this column selector export example. Maybe you can add the classname no-export to the rendered columns and update the selectors to use an array to include the hidden columns.

Kevin

raenk

Hi Kevin.

Thanks for your input. Still trying to figure this out.

In a simple example, I have columns A | B | C | D

For design, I hide B and C and render their data in A (along with A data).

I want to export all columns, but if I do that, I get B and C by themselves, but their data will show again in column A.

A work around that I'm thinking, although maybe not optimal, is to add an extra column as buffer:

A | A2 | B | C | D

Now, in A2 repeat A data (alone) and hide it in screen. Then, when exporting, leave A behind and export only A2, B, C and D.

I'm sure this will work, but I think is overkill. Still hoping for some JS magic from datatables to solve this easier.

kthorngren

There is some Datatables magic that should work for you. Use orthogonal data like this example to return the data for A for the export operation.

Kevin

raenk

That's it Kevin! Many thanks!