Export child rows using excelHTML5

Export child rows using excelHTML5

Khushi2005Khushi2005 Posts: 6Questions: 2Answers: 0
edited July 2017 in Free community support

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.

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

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

    Kevin

  • Khushi2005Khushi2005 Posts: 6Questions: 2Answers: 0
    edited July 2017

    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?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

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

  • Khushi2005Khushi2005 Posts: 6Questions: 2Answers: 0

    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

  • Khushi2005Khushi2005 Posts: 6Questions: 2Answers: 0
    edited July 2017
    /* 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>';
          }``
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • AndreNevesAndreNeves Posts: 1Questions: 0Answers: 0

    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 :)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Brilliant! Thanks for sharing your code with us.

    Allan

  • IT_LearnerIT_Learner Posts: 2Questions: 0Answers: 0

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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_LearnerIT_Learner Posts: 2Questions: 0Answers: 0

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

    Regards

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • santsant Posts: 1Questions: 0Answers: 0

    @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)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

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

    Allan

  • islamelshobokshyislamelshobokshy Posts: 99Questions: 20Answers: 1

    Any updates on this subject?

  • islamelshobokshyislamelshobokshy Posts: 99Questions: 20Answers: 1
    edited February 2019

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

  • GurupriyanGurupriyan Posts: 15Questions: 6Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    @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

  • kanikamehrakanikamehra Posts: 1Questions: 0Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • kuz1torokuz1toro Posts: 1Questions: 0Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Thanks,

    Colin

  • PandalexPandalex Posts: 18Questions: 6Answers: 1

    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>';
        }   
    
This discussion has been closed.