Trying to export to clipboard selected rows with header in table form

mscroggi

I have been searching and trying to figure out how to process the selected data (multi rows) in datatables and have the data exported to the clipboard in an html table form. (to paste into a ticketing web app so that the data is readable)

I can get kind of close for the body data with this -

        {
            extend: 'copyHtml5',
            text: 'HTMLTable',
            //header:false,
            footer:false,
            title: null,
            messageTop: '<table><tr><td>',
            fieldSeparator: '</td><td>',
            messageBottom: '</table>',
        },

It actually generates the td's for the cells.. but the beginning and ending rows are missed as well as the header line

I could essentially do the same thing using a format:

    {   extend: 'copyHtml5', 
        exportOptions: htmlFormatter,
        text: 'Copy to HTML ',
    },

let htmlFormatter = {
    format: {
        body: function (data, row, column, node) {

            //data = "<td>" + data + "</td>";
            if (column === 0)  {
                data = "<tr><td>" + data;
            };

            if (column === 17)  {
                data = data + "</td></tr>";
            };

            //replace tabs between colus with html td tags
            data.replace(/\t/g, '</td><td>');

            //alert(data);

            //return the line as an html row
            return data;
        }
    }
};

I assume that the format feature is the best way to do this.

I found format options for the header and for the body.. but I never found how they are both used together.
trying to use two format definitions together didn't seem to work either..

I am missing something.. Unless I am willing to forego the column titles, I cant seem to get it working.

Suggestions anyone?

kthorngren

It seems the custom formatter is a good option. I built this example:
https://live.datatables.net/juruseso/1/edit

For the header and footer it uses the data passed into the function. The same can be used in the body function but I chose to show using the node parameter and removing the Datatables classes.

Kevin

mscroggi

Oh wow .. That got me so close!.. Your example worked perfectly! You are appreciated!!

I have a couple of issues to work around in my code. It appears there were some changes for DT 2.0, I am evidently using 1.13.1

I guess I have some studying / refactoring to do for DT 2.0 !

kthorngren

It works with 1.13.11 here:
https://live.datatables.net/petoxija/1/edit

It should work with 1.13.1. It is recommended to go to 2.0 as 1.13 won't get fixes anymore. Maybe something else is causing the issue you are having with 1.13.1. Can you update my example or provide a test case showing the issue?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

mscroggi

@kthorngren I figured out it does in fact work. but it doesnt like a couple of things I am doing..

The column counter:

var numCols = table.columns().count() - 1;

doesn't work when the table creation is wrapped in a doc ready function?

$(document).ready(function() {
var table = $('#datatable').DataTable( { ....

Maybe this isnt needed? I use PHP to pull in data for the table and then build the datatable.

The export is including all the rows. Previously it was only exporting the selected rows. I have the table defined with the 'select: true' setting.

I was researching how to only process the selected rows.

This is my table build code:

$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#datatable tfoot th').each( function (i) {
        var title = $('#datatable tfoot th').eq( $(this).index() ).text();
         $(this).html( '<input type="text" placeholder="'+title+'" data-index="'+i+'" />' );
    } );

    // DataTable
    var table = $('#datatable').DataTable( {
    // scrollY:        "300px",
    scrollX:        false,
    scrollCollapse: false,
    paging:         true,
    fixedColumns:   false,
    select: true,
    pageLength: 25,
    "pagingType": "full_numbers",
    "stripeClasses": [ 'evenrow', 'oddrow'],
    dom: 'lftBip',
    buttons: [
       {
            text: 'Clear Filters',
            action: function ( e, dt, node, config ) {
                ClearFilters();
            }
        },
        {
            extend: 'csvHtml5',
            text: 'Export to CSV'
        },
        {
            extend: 'copyHtml5',
            text: 'Copy to Clipboard',
            copyTitle: 'Data copied', 
        },
        {   
            extend: 'copyHtml5',
            exportOptions: htmlFormatter_nofooter,
            text: 'Copy to HTML',
            messageTop: '<table>',
            messageBottom: '</table>',
            title: '',
        },
    ],
        renderer: {
        "header": "datatables",
        "pageButton": "datatables"
    }
    } );

    // Filter event handler
    $( table.table().container() ).on( 'keyup', 'tfoot input', function () {
        table
            .column( $(this).data('index') )
            .search( this.value )
            .draw();
    } );

    //remove the loading message
    document.getElementById('message').style.display = 'none';

    //dont display the table until its done rendering
    $("#datatable").show();
} );

mscroggi

@kthorngren I discovered it actually does work as far as building the export table.. it doesnt like a couple of things I am doing.

I discovered the column counter -

var numCols = table.columns().count() - 1;

wont work because I am building the table inside a document.ready()

function$(document).ready(function() {

Maybe this isnt needed?

The other issue is that all the rows are being exported.. Previously only the selected rows were copied. I have the select:true option set.. but it doesn't seem to apply to the formatter. I am guessing the formatter needs to exclude any non selected rows itself?

This is how I am building my table

$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#datatable tfoot th').each( function (i) {
        var title = $('#datatable tfoot th').eq( $(this).index() ).text();
        $(this).html( '<input type="text" placeholder="'+title+'" data-index="'+i+'" />' );
    } );

    // DataTable
    var table = $('#datatable').DataTable( {
    // scrollY:        "300px",
    scrollX:        false,
    scrollCollapse: false,
    paging:         true,
    fixedColumns:   false,
    select: true,
    pageLength: 25,
    "pagingType": "full_numbers",
    "stripeClasses": [ 'evenrow', 'oddrow'],
    dom: 'lftBip',
    buttons: [
       {
            text: 'Clear Filters',
            action: function ( e, dt, node, config ) {
                ClearFilters();
            }
        },
        {
            extend: 'csvHtml5',
            text: 'Export to CSV'
        },
        {
            extend: 'copyHtml5',
            text: 'Copy to Clipboard',
            copyTitle: 'Data copied', 
        },
        {   
            extend: 'copyHtml5',
            exportOptions: htmlFormatter,
            text: 'Copy to HTML',
            messageTop: '<table>',
            messageBottom: '</table>',
            title: '',
        },
    ],
        renderer: {
        "header": "datatables",
        "pageButton": "datatables"
    }
    } );

    // Filter event handler
    $( table.table().container() ).on( 'keyup', 'tfoot input', function () {
        table
            .column( $(this).data('index') )
            .search( this.value )
            .draw();
    } );

    //remove the loading message
    document.getElementById('message').style.display = 'none';

    //dont display the table until its done rendering
    $("#datatable").show();
} );

kthorngren

wont work because I am building the table inside a document.ready()

Tthe htmlFormatter() function is outside the scope of the document.read() function resulting in the table variable being undefined - see your browser's console. You can either move the htmlFormatter() into the same document.read() function or get an instance of the API, ie, $('#example').DataTable().columns().count() - 1.

but it doesn't seem to apply to the formatter

That is unexpected as per. this example and this section of the selector-modifier docs indicate the selected rows should be the only rows exported. @allan can comment on this. As a workaround you can add this to the htmlFormatter() function:

    modifier: {
      selected: undefined
    },

Update 2.0 example but it should work with same with 1.x:
https://live.datatables.net/juruseso/2/edit

Kevin

mscroggi

@kthorngren
Thanks for the info on the column counter. That did indeed resolve the issue.

For the exporting of all the rows.. I discovered there was an issue with detecting any rows were selected at all.. I didn't notice since my styling was lost.. but clicking on a row wasn't actually selecting the row.

If I reverted to the same collection of js/css files I have been using.. selection is working as expected and only selected rows are exported.

perfect!

Thanks a ton!

kthorngren

I retested my last example. It doesn't need this added - which is the default setting:

modifier: {
  selected: undefined
},

Not sure what I was seeing yesterday to cause me to add it

https://live.datatables.net/juruseso/5/edit

Kevin

mscroggi

I would like to be able to define the formatter in an external js and include it -

dt_formatters.js:

let htmlFormatter_nofooter = {
    format: {
        header: function (data, column, node) {
          var tr = '<thead><tr>';
          var trClose = '</tr></thead><tbody>';

          // Used to know when the last column is processed for closing </tr>
          var numCols = $('#datatable').DataTable().columns().count() - 1;

          data = '<td>' + data + '</td>';

          return (column === 0 ? tr : '') + data + (column === numCols ? trClose : '');
        },
        body: function (data, row, column, node) {
          var tr = '<tr>';
          var trClose = '</tr>';

          var numCols = $('#datatable').DataTable().columns().count() - 1;

          // Remove DT classes from cell
          node.removeAttribute("class");

          return (column === 0 ? tr : '') + node.outerHTML + (column === numCols ? trClose : '');
        },
    }
};

In my testing this works great.

The issue I am trying to solve is how to not have a hardcoded table ID.

If I can figure out how to determine the tableID from inside the formatter, or pass it in.. I can simply add the js inclusion and button config to my existing pages with no worries of the table IDs changing etc..

I have been testing and searching and I do not believe there is a way to pass the name to the exportoptions formatter.

As far as obtaining it within the formatter itself.. I have yet to find any methods of doing this.

Any ideas?