How to Export Grouped rows

How to Export Grouped rows

s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0
edited April 2017 in Free community support

I cannot export the grouped rows using button exports. I used my own functionality using basic row grouping code for grouping rows in data tables and when i exported the table using "buttons" the grouped rows are not exported. Also I am using static serial number which is also not exported, instead it is found empty. Is there a way to export the contents which are added later. Any idea would be helpful.
Thanks in advance.

This question has an accepted answers - jump to answer

Answers

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

    Hi,

    I cannot export the grouped rows using button exports.

    No, that is not a supported feature at this time I'm afraid. I've tried to make that clear:

    Allan

  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0

    Thank you very much for your reply

  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0
    edited April 2017

    Your plugin helped me a lot.

    I wish to share my code which supports any number of nested groping

    var dataTable;
    var total_menu_items=[];
    grouped_array_index=[2,3,4];
    function bindDataTable(table_id){
        try{
            "fnDrawCallback": function( oSettings ) {
                        /************************ GROUP DATATABLE *******************/
                        var nTrs = $('#'+table_id+' tbody tr');
                        //var iColspan = nTrs[0].getElementsByTagName('td').length;
                        var iColspan =$('.dataTables_scrollHead table thead tr th').length;
                        var sLastGroup = "";
                        var inner_html='',grouped_index;
                        var individual_group_array= new Array(),sub_group_array= new Array(),total_group_array= new Array();
                        for ( var i=0 ; i<nTrs.length ; i++ ){
                            sub_group_array[i]= new Array();
                            individual_group_array[i]= new Array();
                            //iDisplayIndex                         --> index of rows in a page
                            //oSettings.aiDisplay[iDisplayIndex]    --> s no of the rows
                            //oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]] --> Table row object
                            //_aData --> table row data array
                            //console.log($('#'+table_id).dataTable().fnSettings()._iDisplayStart+" "+oSettings._iDisplayStart);
                            var iDisplayIndex = oSettings._iDisplayStart + i;
                            var sGroup='';
                            
                            for(var k=0;k<grouped_array_index.length;k++){
                                sGroup = sGroup+''+oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]]._aData[grouped_array_index[k]]+' ';
                              //  inner_html=inner_html+"<span style='width:500px;margin-right:10px'>"+oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]]._aData[grouped_array_index[k]]+"</span>";
                                inner_html=oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]]._aData[grouped_array_index[k]];
                                grouped_index=k;
                                individual_group_array[i][k]=oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]]._aData[grouped_array_index[k]];
                                sub_group_array[i][k]=sGroup;
                                //console.log(i+" "+k+" "+sub_group_array[i][k]);
                            }
                            total_group_array[i]=sGroup;
                            if ( sGroup !== sLastGroup ){
                                var table_data='';
                                for(var $column_index=0;$column_index<iColspan-1;$column_index++){
                                    if($column_index===0)
                                        table_data+='<td colspan="2" style="border-left:none;border-right:none">'+inner_html+'</td>';
                                    else
                                        table_data+='<td style="border-left:none;border-right:none"></td>';
                                }
                                if(iColspan===1){
                                    table_data+='<td>'+inner_html+'</td>';
                                }
                                $(nTrs[i]).before('<tr class="group group_'+grouped_index+' grouped-array-index_'+grouped_array_index[grouped_index]+'">'+table_data+'</tr>');
                                sLastGroup = sGroup;
                            }
                        }
                        
                        //For Multi grouping
                            for(var k=grouped_index-1;k>=0;k--){
                                var i=0;
                                //var sGroup='';
                                sLastGroup='';
                                nTrs.each(function () {
                                    var $current_row=$(this);
                                    var font_weight=400;
                                    if(font_weight<800)
                                        font_weight=font_weight+200;
                                    else
                                        font_weight=900;
                                    var sGroup='';
                                    if (total_group_array[i].indexOf(sub_group_array[i][k]) !== -1) { 
                                        var inner_html=individual_group_array[i][k];
                                        var sGroup=sub_group_array[i][k];
                                        //console.log("SubGroup:"+sub_group_array[i][k]+" TotalGroup:"+total_group_array[i]+" | "+sGroup+" | "+sLastGroup);
                                        if ( sGroup !== sLastGroup ){
                                            while($current_row.prev().attr('class')!==undefined && $current_row.prev().attr('class').indexOf('grouped-array-index')!==-1 && $current_row.prev().attr('class').split(" ")[1].split("_")[1]>k){
                                                $current_row=$current_row.prev();
                                            }
                                            //console.log($current_row.attr('class'));
                                            sLastGroup = sGroup;
                                            var table_data='';
                                            for(var $column_index=0;$column_index<iColspan-1;$column_index++){
                                                if($column_index===0)
                                                    table_data+='<td style="font-weight:'+font_weight+';border-left:none;border-right:none" colspan="2">'+inner_html+'</td>';
                                                else
                                                    table_data+='<td style="border-left:none;border-right:none"></td>';
                                            }
                                            if(iColspan===1){
                                                table_data+='<td>'+inner_html+'</td>';
                                            }
                                            $current_row.before('<tr class="group group_'+k+' grouped-array-index_'+grouped_array_index[k]+'">'+table_data+'</tr>');
                                        }
                                    }
                                    i++;
                                });
                            }
                            
                        if(total_menu_items.length!==0){ 
                            showSubTotalForGroup(oSettings,table_id);
                        }
                        /************************ GROUP DATATABLE *******************/
                        
                    }
                    
                }); 
              
            }
        }
        catch(e){
            console.log(e);
        }
    }   
    
    
  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0
    var is_group_sub_divided=false;                                     //For hiding subtotal for divided group at only last column 
    function showSubTotalForGroup(oSettings,table_id){
        try {
            var font_weight=400;
            var row_array=$('#'+table_id).DataTable().rows().nodes();
            var row_data_array=$('#'+table_id).DataTable().rows().data();
            var sub_total=[];
            for(var i=(grouped_array_index.length-1);i>=0;i--){
                $('#'+table_id+' tbody tr').each(function () {
                    var group_class="group_"+i;
                    if($(this).attr('class').indexOf(group_class) !== -1){
                        //console.log($(this).attr('class')+" "+group_class);
                        var rowCount = $(this).nextUntil('.'+group_class).length;
                        var $last_row_in_group=$(this);
                        var $group_header='';
                        for(var $item=0;$item<total_menu_items.length;$item++){
                            sub_total[total_menu_items[$item]]=0;
                        }
                        $group_header=$last_row_in_group;
                        for(var k=1;k<=rowCount;k++){
                            $last_row_in_group=$last_row_in_group.next();
                            
                            if(($last_row_in_group.attr('class').indexOf('group') === -1) && ($last_row_in_group.attr('class').indexOf('group-footer') === -1)){
                                var current_index=$(row_array).index($last_row_in_group);
                                //var row_data_array=$('#'+table_id).DataTable().row(current_index).data();
                                for(var $item=0;$item<total_menu_items.length;$item++){
                                    sub_total[total_menu_items[$item]] += parseFloat($(row_data_array)[current_index][total_menu_items[$item]]);  
                                }
                            }
                        }
                        while($last_row_in_group.attr('class').indexOf("group")!==-1){
                            $last_row_in_group=$last_row_in_group.prev();
                        }
                           
                        while($last_row_in_group.next().attr('class')!==undefined && $last_row_in_group.next().attr('class').indexOf('group-footer')!==-1 && $last_row_in_group.next().attr('class').split(" ")[1].split("_")[1]>i){
                            //console.log("group-footer group_"+prev_group_footer_index);
                            $last_row_in_group=$last_row_in_group.next();
                        }
                        
                       /****** Correcting group sub division***********/
                       // The below code should be added here
                     /****** END Correcting group sub division***********/
                        /***************************For Adding subtotal row *********************************/
                        var is_sub_total=false;
                        var label_index='';
                        var total_index_array=[];
                        for(var $item=0;$item<total_menu_items.length;$item++){
                            var current_header=$('#'+table_id).DataTable().column(total_menu_items[$item]).header();
                            var current_index=$('.dataTables_scrollHead table thead tr th').index($(current_header));      
                            if(current_index!==-1){
                                is_sub_total=true;
                                if(label_index==='')
                                    label_index=current_index-1;
                                total_index_array.push(current_index);
                            }
                            sub_total[total_menu_items[$item]]=sub_total[total_menu_items[$item]].toFixed(2);
                        }
                        
                        if(is_sub_total && !is_group_sub_divided){
                            var sub_total_row=$('<tr class="group-footer group_'+i+'"></tr>');
                            var length=$('.dataTables_scrollHead table thead tr th').length;
                            //To display sub totals in group header if show only groups is clicked
                            if(show_only_groups==='checked'){ 
                                var header_array=$('#'+table_id).DataTable().columns().header();
                                for(var j=0;j<length;j++){
                                    if(total_index_array.indexOf(j)!==-1){
                                        if(length===2){
                                            $group_header.children().eq(0).attr("colspan","1");
                                            $group_header.children().eq(0).after('<td style="text-align:right,border:1px solid #eee">'+sub_total[total_menu_items[total_index_array.indexOf(j)]]+'</td>');
                                            //$group_header.children().eq(1).html(sub_total[total_menu_items[total_index_array.indexOf(j)]]);
                                            //$group_header.children().eq(1).css({"text-align":"right","border":"1px solid #eee"});
                                        }
                                        else{
                                            var column_index=$('.dataTables_scrollHead table thead tr th').index($(header_array[total_menu_items[total_index_array.indexOf(j)]]));
                                            $group_header.children().eq(column_index-1).html(sub_total[total_menu_items[total_index_array.indexOf(j)]]);
                                            $group_header.children().eq(column_index-1).css({"text-align":"right","border":"1px solid #eee"});
                                        }
                                    }
                                }
                            }
                            //To display sub totals in group footer 
                            else{
                                for(var j=0;j<length;j++){
                                    if(j===label_index)
                                        sub_total_row.append('<td style="border-right:0;border-left:0;font-weight:'+font_weight+'">Sub Total</td>');
                                    else if(total_index_array.indexOf(j)!==-1)
                                        sub_total_row.append('<td>'+sub_total[total_menu_items[total_index_array.indexOf(j)]]+'</td>');
                                    else
                                        sub_total_row.append('<td style="border-right:0;border-left:0"></td>');
                                }
                                $last_row_in_group.after(sub_total_row);
                            }
                        }
                        /*End*/
                    }    
                });
                if(font_weight<800)
                    font_weight=font_weight+200;
                else
                    font_weight=900;
            }
        }
        catch(e){
            console.log(e);
        }
    }
    
  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0
     /*************************** Correcting GROUP SUB DIVISION ***************************/
                        var $first_row_in_group_data=$(this);
                        while($first_row_in_group_data.attr('class').indexOf("group") !== -1){
                            $first_row_in_group_data=$first_row_in_group_data.next();
                        }
                        var $last_row_in_group_data=$last_row_in_group;
                        while($last_row_in_group_data.attr('class').indexOf("group-footer") !== -1){
                            $last_row_in_group_data=$last_row_in_group_data.prev();
                        }
                        
                        var page_length=$('select[name="'+table_id+'_length"]').val();
                        //var row_index=$(row_id_array).index(parseInt($last_row_in_group_data.attr('row-id')));
                        var row_index=$(row_array).index($last_row_in_group_data);
                        var first_row_index=$(row_array).index($first_row_in_group_data);
                        var current_page = Math.ceil(oSettings._iDisplayStart / oSettings._iDisplayLength) + 1;
                        
                        //Check if group is subdivided or not in first row for calculating sub total
                        if(parseInt(page_length)===first_row_index/(current_page-1)){
                            var group_content_in_current_page_first_row='';
                            var group_content_in_previous_page_last_row='';
                            for(var z=0;z<=i;z++){
                                group_content_in_current_page_first_row+=$(row_data_array)[first_row_index][grouped_array_index[z]];
                                group_content_in_previous_page_last_row+=$(row_data_array)[first_row_index-1][grouped_array_index[z]];
                            }
                            var sub_total_group_sub_divided=[];
                            for(var $item=0;$item<total_menu_items.length;$item++){
                                sub_total_group_sub_divided[total_menu_items[$item]]=0.00;
                            }
                           while(group_content_in_current_page_first_row!=='' && group_content_in_current_page_first_row===group_content_in_previous_page_last_row){
                                //for calculating subtotal
                                for(var $item=0;$item<total_menu_items.length;$item++){
                                    sub_total_group_sub_divided[total_menu_items[$item]] += parseFloat($(row_data_array)[first_row_index-1][total_menu_items[$item]]);
                                }
                                first_row_index--;
                                group_content_in_previous_page_last_row='';
                                for(var z=0;z<=i;z++){
                                    group_content_in_previous_page_last_row+=$(row_data_array)[first_row_index-1][grouped_array_index[z]];
                                }
                            }
                            for(var $item=0;$item<total_menu_items.length;$item++){
                                sub_total[total_menu_items[$item]]=parseFloat(sub_total[total_menu_items[$item]])+parseFloat(sub_total_group_sub_divided[total_menu_items[$item]]);
                            }
                        }
                        
                        //Check if group is subdivided or not in last row for hiding sub total
                        is_group_sub_divided=false;
                        var sub_total_only_group_sub_divided=[];
                        var sub_total_only_group=[];
                        if(parseInt(page_length)===(row_index+1)/current_page){
                            var group_content_in_current_page_last_row='';
                            var group_content_in_next_page_first_row='';
                            var last_row_index=row_index;
                            for(var z=0;z<=i;z++){
                                group_content_in_current_page_last_row+=$(row_data_array)[row_index][grouped_array_index[z]];
                                group_content_in_next_page_first_row+=$(row_data_array)[row_index+1][grouped_array_index[z]];
                            }
                            if(group_content_in_current_page_last_row!=='' && group_content_in_current_page_last_row===group_content_in_next_page_first_row){
                                is_group_sub_divided=true;
                            }
                            /*******To calculate sub total in previous group header which is subdivided  if show only groups is clicked***/
                            for(var $item=0;$item<total_menu_items.length;$item++){
                                sub_total_only_group_sub_divided[total_menu_items[$item]]=0.00;
                            }
                            while(group_content_in_current_page_last_row!=='' && group_content_in_current_page_last_row===group_content_in_next_page_first_row){
                                //for calculating subtotal
                                for(var $item=0;$item<total_menu_items.length;$item++){
                                    sub_total_only_group_sub_divided[total_menu_items[$item]] += parseFloat($(row_data_array)[last_row_index+1][total_menu_items[$item]]);
                                }
                                last_row_index++;
                                group_content_in_next_page_first_row='';
                                for(var z=0;z<=i;z++){
                                    group_content_in_next_page_first_row+=$(row_data_array)[last_row_index+1][grouped_array_index[z]];
                                }
                            }
                            for(var $item=0;$item<total_menu_items.length;$item++){
                                sub_total_only_group[total_menu_items[$item]]=parseFloat(sub_total[total_menu_items[$item]])+parseFloat(sub_total_only_group_sub_divided[total_menu_items[$item]]);
                            }
                            /*End*/
                        }
                        /*************************** Correcting GROUP SUB DIVISION ***************************/
    
  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0
    edited April 2017

    I exported the grouped contents to excel, pdf and print by adding same functionality in _exportData function in buttons.js file. But i am not able to give colspan in excel for grouped row. The pdf and print exports works fine. Also is there a plugin in jquery for execute the group and sub total functions in excel.

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

    There isn't a jQuery plug-in for it. You would need to modify the Buttons code that the export functions use to add the colspan functionality into them. I don't know what the correct XML for that is in an XLSX file. You would probably need to refer to the open spreadsheet standard documents.

    Allan

  • s.vinothkumars.vinothkumar Posts: 7Questions: 1Answers: 0

    Thank you Allan,
    I exported to excel using PHPExcel by passing the table data through ajax and the groping, column merge and cell formats works perfectly.
    Thank you very much for your plugin

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

    Hi, I cannot export the grouped rows using button exports. @allan

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

    Hello @allan I cannot export the grouped rows using button exports. I can use the copy button

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

    Did you look at the links Allan provided in the second post?

    https://datatables.net/download/compatibility
    https://datatables.net/extensions/rowgroup/#Limitations-and-future-work

    The row group info is not included using the export buttons.

    Kevin

  • EufragioEufragio Posts: 20Questions: 2Answers: 0
    edited December 2018

    You could group the rows and export, because really if I have to do that. How can I solve this problem. What else can I do ?

This discussion has been closed.