How to include row().child rows in downloads with dom: ‘Bfrtip’ using Datatables

Tags: ,



I am using the dom: 'Bfrtip' buttons to add a Copy and Excel button to my Datatable. However, my Datatable also contains hidden row().child which do not get copied when I click the buttons. How do I include the child rows with the Copy function?. Here is the code I am using:

function format ( d ) {
    // `d` is the original data object for the row
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:30px;">'+
        '<tr>'+
            '<td>col5:</td>'+
            '<td style="word-break:break-all;">'+d.col5+'</td>'+
        '</tr>'+
    '</table>';
}

$(document).ready(function() {
    var table = $('#myDataBase').DataTable( {
              dom: 'Bfrtip',
        buttons: [
            'copyHtml5',
            'excelHtml5',
            'pageLength'
        ],
        "pagingType": "full_numbers",
        "iDisplayLength": 25,
        "ajax": "./myDataBase_objects.json",
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "data": "col1" },
            { "data": "col2" },
            { "data": "col3" },
            { "data": "col4" }
        ],
        "order": [[1, 'asc']]
    } );

the source json is as follows wherein the first 4 columns are displayed in the DataTable and col5 is not:

{
    "data": [
        {
            "col1": "12",
            "col2": "98.2",
            "col3": "76",
            "col4": "376",
            "col5": "42,567"
        },
        {
            "col1": "11",
            "col2": "92.2",
            "col3": "70",
            "col4": "306",
            "col5": "22,567"
        },

    ]
}

Answer

I have based this answer on the demo provided here, but using the data sample from the question.

This is what the table looks like, with the first child row expanded, just to show the data in the child:

enter image description here

It doesn’t matter whether any child rows are visible or hidden – the copy/paste grabs all data, regardless of that.

Here is the DataTables script – note that I am hard-coding the data for this demo, rather than using a URL with ajax – but that does not change the copy/paste approach:

<script type="text/javascript">

var dataSet = [
        {
            "col1": "12",
            "col2": "98.2",
            "col3": "76",
            "col4": "376",
            "col5": "42,567"
        },
        {
            "col1": "11",
            "col2": "92.2",
            "col3": "70",
            "col4": "306",
            "col5": "22,567"
        }

];

function format ( d ) {
    return '<table cellpadding="5"><tr><td>' + d.col5 + '</td></tr></table>';
}
 
$(document).ready(function() {
    var table = $('#example').DataTable( {
        dom: 'Bfrtip',
        data: dataSet,
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "data": "col1" },
            { "data": "col2" },
            { "data": "col3" },
            { "data": "col4" }
        ],
        "order": [[1, 'asc']],

    buttons: [
      {
        text: 'Custom Copy',
        action: function ( e, dt, node, config ) {
          // the column headings are here:
          var copyString = 'col1tcol2tcol3tcol4tcol5n';

          table.rows().every( function () {
            // for each row, get the data items, separated by tabs:
            copyString = copyString + Object.values(this.data()).join('t') + 'n';

          } );

          if (copyString !== undefined) {
            // create a textarea, write the data to it, then 
            // select it for copy/paste:
            var dummy = document.createElement("textarea");
            document.body.appendChild(dummy);
            dummy.setAttribute("id", "dummy_id");
            document.getElementById("dummy_id").value = copyString;
            dummy.select();
            document.execCommand("copy");
            document.body.removeChild(dummy);
          }

        }
      }
    ]

    } );
     
    // Add event listener for opening and closing details
    $('#example tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row( tr );
 
        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    } );
} );

</script>

The result of clicking on the Custom Copy button is that the clipboard contains all the table’s data.

When pasted into an empty Excel worksheet, it looks like this:

enter image description here

How this works:

Because all data items are in the original JSON objects, each table row has access to all the data we need, regardless of how it is split between the main rows and child rows.

The button code can therefore use a custom function to access all that data using table.rows().every(...). We build a string (copyString) containing all this data, split by tabs (t) and newlines (n).

Finally, we create a new temporary DOM element – a textarea into which we place this text, so that we can copy it to the clipboard.

One downside of this: There is no notification to the user regarding the copy/paste event. You could add an alert for this if you wished.

But the upside is: This will find all data, unlike the out-of-the-box copy function.



Source: stackoverflow