How to Export Grouped rows

s.vinothkumar

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.

allan

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:

• RowGroup documentation
• Introduction blog post
• Compatibility chart

Allan

s.vinothkumar

Thank you very much for your reply

s.vinothkumar

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.vinothkumar

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.vinothkumar

 /*************************** 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.vinothkumar

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.

allan

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.vinothkumar

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

Eufragio

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

Eufragio

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

kthorngren

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

Eufragio

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 ?