Export child rows using excelHTML5

Khushi2005

Hi,
I was succefully able to add Child Rows using this link
https://datatables.net/blog/2014-10-02

I have added a nested table and would like to export all the new added children using Export function

What I noticed is formatted child rows do not get Exported.

How do we export child rows?

Thanks in advance.

kthorngren

According to Allan exporting child rows is not available:
https://datatables.net/forums/discussion/comment/103982/#Comment_103982

Kevin

Khushi2005

Oh..
So what is the best way to export the data?
Exporting nested data to Excel may not make sense but how about Print Preview or Copying the contents?

tangerine

A quick forum search finds this (for example):
https://datatables.net//forums/discussion/43133

Khushi2005

One question on Child Rows. Can we add events to Child Rows along with adding Child to Child Rows?
I am trying to created Nested table scenario. More like Drill Down Reports.

Thanks in advance

Khushi2005

/* Formatting function for row details - modify as you need */
      function formatNew(d) {
          // `d` is the original data object for the row
          return '<div class="slider">' +
          '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;width:80%">' +
          '<thead style="background-color:green"><tr><th>click</th><th>Year</th><th>Month</th><th>Savings</th></tr></thead>' + 
          '<tbody>'+
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr>      <td class="details-control">...</td>   <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '<tr style="color:red;"> <td class="details-control">...</td>     <td>2012</td>      <td>January</td>      <td>$100</td>    </tr>' +
          '</tbody>' +
          '</table>' +
          '</div>';
      }``

allan

Can we add events to Child Rows along with adding Child to Child Rows?

I'm afraid I don't really understand what you mean. Do you want to trigger an event when the child row is created? If so, DataTables itself doesn't currently trigger such an event, but there is no reason why you couldn't do so using $.trigger().

Allan

AndreNeves

Hi,
We managed to export the Child Rows by modifying the script "exportTableToCSV"

First we will change the natural function of the button xls and csv

   buttons: [

                             {
                                 extend:    'excelHtml5',
                                 text:      '<i class="fa fa-file-excel-o"></i>',
                                 titleAttr: 'Excel',
                                 "oSelectorOpts": { filter: 'applied', order: 'current' },
                                 "sFileName": "report.xls",
                                 action : function( e, dt, button, config ) {
                                     exportTableToCSV.apply(this, [$('#myTable'), 'export.xls']);

                                 } 

                             },
                             {
                                 extend:    'csvHtml5',
                                 text:      '<i class="fa fa-file-text-o"></i>',
                                 titleAttr: 'CSV',
                                 exportOptions: {
                                     columns: ':visible'
                                 },
                                 action : function( e, dt, button, config ) {
                                     exportTableToCSV.apply(this, [$('#myTable'), 'export.csv']);

                                 } 

                             }

Then we create the functions that creates the files and the one that downloads them:

```

    function exportTableToCSV($table, filename) {

        //rescato los títulos y las filas
        var $Tabla_Nueva = $table.find('tr:has(td,th)');
        // elimino la tabla interior.
        var Tabla_Nueva2= $Tabla_Nueva.filter(function() {
             return (this.childElementCount != 1 );
        });

        var $rows = Tabla_Nueva2,
            // Temporary delimiter characters unlikely to be typed by keyboard
            // This is to avoid accidentally splitting the actual contents
            tmpColDelim = String.fromCharCode(11), // vertical tab character
            tmpRowDelim = String.fromCharCode(0), // null character

            // Solo Dios Sabe por que puse esta linea 
            colDelim = (filename.indexOf("xls") !=-1)? '"\t"': '","',
            rowDelim = '"\r\n"',


            // Grab text from table into CSV formatted string
            csv = '"' + $rows.map(function (i, row) {
                var $row = $(row);
                var   $cols = $row.find('td:not(.hidden),th:not(.hidden)');

                return $cols.map(function (j, col) {
                    var $col = $(col);
                    var text = $col.text().replace(/\./g, '');
                    return text.replace('"', '""'); // escape double quotes

                }).get().join(tmpColDelim);
                csv =csv +'"\r\n"' +'fin '+'"\r\n"';
            }).get().join(tmpRowDelim)
                .split(tmpRowDelim).join(rowDelim)
                .split(tmpColDelim).join(colDelim) + '"';


         download_csv(csv, filename);


    }



   function download_csv(csv, filename) {
        var csvFile;
        var downloadLink;

        // CSV FILE
        csvFile = new Blob([csv], {type: "text/csv"});

        // Download link
        downloadLink = document.createElement("a");

        // File name
        downloadLink.download = filename;

        // We have to create a link to the file
        downloadLink.href = window.URL.createObjectURL(csvFile);

        // Make sure that the link is not displayed
        downloadLink.style.display = "none";

        // Add the link to your DOM
        document.body.appendChild(downloadLink);

        // Lanzamos
        downloadLink.click();
    }

```

With this method you can export the child rows of many Datatables:

English provided by Google

allan

Brilliant! Thanks for sharing your code with us.

Allan

IT_Learner

is there a way to use this function to export nested rows in pdf

allan

The PDF export is entirely different from the Excel export I'm afraid (since they use different document definition formats). To have this working with the PDF export it would need to be rewritten for the pdfmake library.

Allan

IT_Learner

allan
if u can do this i will be really thankful to your.

Regards

allan

Child row export is not currently something I'm working on I'm afraid. It is something we hope to introduce longer term, but there are other things we are working on at the moment.

Allan

sant

@allan
Hi I have used your code to export child row in CSV and EXCEL and I can say it is very optimized code and apart from this I want to do the same in the case of Pdf and Print so can you please provide the code for that also it will be very thankful to you.
Anyone knows about this please tell me.

Thank you
Santosh Chaurasiya (onhand.us)

allan

Hi,

I'm afraid exporting of child rows is not something that Buttons currently supports as I note above.

Allan

islamelshobokshy

Any updates on this subject?

islamelshobokshy

The answer is already here... Thanks @AndreNeves !

Gurupriyan

@AndreNeves Thanks for your answer. But that export only current page data. Have to export all data. Kindly Help me out ASAP

kthorngren

@Gurupriyan Are you using server side processing? If so then this FAQ will answer your question:
https://datatables.net/faqs/index#buttons

If not then please post more information about what you have. A link to your page or a test case would help greatly to see what you have and help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

kanikamehra

@AndreNeves Can we create table inside table and have common column search for both tables or common excel, pdf and copy options.

kthorngren

The child detail rows are independent of the row data and won't share common search or export functions. If the data is part of the row data you can place them in hidden columns using columns.visible then the would be searchable and exportable. If the data is not part of the row data then you will need to create custom functions for each.

Kevin

kuz1toro

guys, this is really simple.
just include the child data but hide the coloum and it will print

colin

@kuz1toro If you're able to provide an example/fiddle, that would help explain that.

Thanks,

Colin

Pandalex

A bit late but it tried kuz1toro exemple like this :

        var table = $('#tableau').DataTable({           
            ajax: {
                    dataType: 'json',
                    type: 'GET',
                    url: '/AppliHabit/ChargeHabits/',
                    dataSrc : ''
                },
            columns: [
                {
                    "className":      'details-control',
                    "orderable":      false,
                    "searchable":     false,
                    "data":           null,
                    "defaultContent": ''
                },
                {"data": 'nom', "title": 'Nom'},
                {"data": 'taille', "title": 'Taille'},
                {"data": 'couleur', "title": 'Couleur'},
                {"data": 'teinte', "title": 'Teinte', "visible":false},
                {"data": 'ton', "title": 'Ton', "visible":false},
                { 
                   'mRender': function (data, type, full) {
                        return '<input type="button" name="edit" value="Modifier" onclick="editRecord(' + full[1] + ');" class="buttonGreen">';                             
                    },
                    "orderable":      false,
                    "searchable":     false
                },
                { 
                   'mRender': function (data, type, full) {
                        return '<input type="button" name="delete" value="Supprimer" onclick="deleteRecord(' + full[1] + ');" class="buttonRed">';                          
                    },
                    "orderable":      false,
                    "searchable":     false
                }
            ],          
            dom: 'Blfrtip',         

Here my fieds 'Teinte' and 'ton' are hidden by default but printed in the excel export.
Then my detail control can just duplicate the content like this:

    function format ( d ) {
        // `d` is the original data object for the row
        return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
            '<tr>'+
                '<td>Ton:</td>'+
                '<td>'+d.ton+'</td>'+
            '</tr>'+
            '<tr>'+
                '<td>Teinte:</td>'+
                '<td>'+d.teinte+'</td>'+
            '</tr>'+
        '</table>';
    }