Row grouping with printing

sciaschi · June 2017

I believe i solved the problem with printing with row grouping. I found a snippet of code on StackOverflow here and was able to fix it up to work with row grouping. This is placed in datatable-buttons.js on line 1564.

Heres some images of it working!
http://prntscr.com/fhx1vu
http://prntscr.com/fhx1ia

    var _exportData = function ( dt, inOpts )
    {

        var config = $.extend( true, {}, {
            rows:           null,
            columns:        '',
            grouped_array_index: [],
            modifier:       {
                search: 'applied',
                order:  'applied'
            },
            orthogonal:     'display',
            stripHtml:      true,
            stripNewlines:  true,
            decodeEntities: true,
            trim:           true,
            format:         {
                header: function ( d ) {
                    return strip( d );
                },
                footer: function ( d ) {
                    return strip( d );
                },
                body: function ( d ) {
                    return strip( d );
                }
            }

        }, inOpts );

        var strip = function ( str ) {
            if ( typeof str !== 'string' ) {
                return str;
            }

            // Always remove script tags
            str = str.replace( /<script\b[^<]*(?:(?!<\/script>)<[^<]*)*<\/script>/gi, '' );

            if ( config.stripHtml ) {
                str = str.replace( /<[^>]*>/g, '' );
            }

            if ( config.trim ) {
                str = str.replace( /^\s+|\s+$/g, '' );
            }

            if ( config.stripNewlines ) {
                str = str.replace( /\n/g, ' ' );
            }

            if ( config.decodeEntities ) {
                _exportTextarea.innerHTML = str;
                str = _exportTextarea.value;
            }

            return str;
        };


        var header = dt.columns( config.columns ).indexes().map( function (idx) {
            var el = dt.column( idx ).header();
            return config.format.header( el.innerHTML, idx, el );
        } ).toArray();

        var footer = dt.table().footer() ?
            dt.columns( config.columns ).indexes().map( function (idx) {
                var el = dt.column( idx ).footer();
                return config.format.footer( el ? el.innerHTML : '', idx, el );
            } ).toArray() :
            null;

        var rowIndexes = dt.rows( config.rows, config.modifier ).indexes().toArray();
        var selectedCells = dt.cells( rowIndexes, config.columns );
        var cells = selectedCells
            .render( config.orthogonal )
            .toArray();
        var cellNodes = selectedCells
            .nodes()
            .toArray();

        var grouped_array_index     = config.grouped_array_index;                     //customised
        var no_of_columns           = header.length;
        var no_of_rows              = no_of_columns > 0 ? cells.length / no_of_columns : 0;
        var body                    = new Array( no_of_rows );
        var body_data               = new Array( no_of_rows );                                //customised
        var body_with_nodes         = new Array( no_of_rows );                          //customised
        var body_with_nodes_static  = new Array( no_of_rows );                          //customised
        var cellCounter             = 0;

        for (var i = 0, ien = no_of_rows; i < ien; i++)
        {
            var rows            = new Array( no_of_columns );
            var rows_with_nodes = new Array( no_of_columns );

            for ( var j=0 ; j<no_of_columns ; j++ )
            {
                rows[j]             = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
                rows_with_nodes[j]  = config.format.body( cellNodes[ cellCounter ], i, j, cells[ cellCounter ] ).outerHTML;
                cellCounter++;
            }

            body[i]                     = rows;
            body_data[i]                = rows;
            body_with_nodes[i]          = $.parseHTML('<tr class="even">'+rows_with_nodes.join("")+'</tr>');
            body_with_nodes_static[i]   = $.parseHTML('<tr class="even">'+rows_with_nodes.join("")+'</tr>');
        }

        /******************************************** GROUP DATA *****************************************************/
        var row_array                       = dt.rows().nodes();
        var row_data_array                  = dt.rows().data();
        var iColspan                        = no_of_columns;
        var sLastGroup                      = "";
        var inner_html                      = '',
            grouped_index;
        var individual_group_array          = [],
            sub_group_array                 = [],
            total_group_array               = [];
        var no_of_splices                   = 0;  //to keep track of no of element insertion into the array as index changes after splicing one element

        for (var i = 0, row_length = body_with_nodes.length; i < row_length; i++)
        {
            sub_group_array[i]              = [];
            individual_group_array[i]       = [];

            var sGroup                      = '';

            for(var k = 0; k < grouped_array_index.length; k++)
            {
                sGroup                          = row_data_array[i][grouped_array_index[k]];
                inner_html                      = row_data_array[i][grouped_array_index[k]];
                grouped_index                   = k;
                individual_group_array[i][k]    = row_data_array[i][grouped_array_index[k]];
                sub_group_array[i][k]           = sGroup;
            }

            total_group_array[i] = sGroup;


            console.log("grouped_index",grouped_index);

            if ( sGroup !== sLastGroup )
            {
                var table_data              = [];
                var table_data_with_node    = '';

                for(var $column_index = 0;$column_index < iColspan;$column_index++)
                {
                    if($column_index === 0)
                    {
                        table_data_with_node        += '<td style="border-left:none;border-right:none">'+inner_html+'</td>';
                        table_data[$column_index]   = inner_html + " ";
                    }
                    else
                    {
                        table_data_with_node        += '<td style="border-left:none;border-right:none"></td>';
                        table_data[$column_index]   = '';
                    }
                }

                body_with_nodes.splice(i + no_of_splices, 0, $.parseHTML('<tr class="group group_'+grouped_index+' grouped-array-index_'+grouped_array_index[grouped_index]+'">'+table_data_with_node+'</tr>'));
                body_data.splice(i + no_of_splices, 0, table_data);
                no_of_splices++;
                sLastGroup = sGroup;
            }
        }

        return {
            header: header,
            footer: footer,
            body:   body_data
        };
    };

Usage:
Define grouped_array_index in exportOptions

 exportOptions: {
        // Any other settings used
        grouped_array_index: [<place row to use here>],
 },

allan · June 2017

Thanks for this! There is also a pull request with this feature.

Regards,
Allan

cairoramos7 · October 2017

exportOptions: {
// Any other settings used
grouped_array_index: [<place row to use here>],
},

how to use this method to do the grouping?

rushidraut · November 2017

My grouped Row is dynamically generated.
Then how to use grouped_array_index:[<place row to use here>]

ckegg · January 2018

This is what I did to print with rowGroup header.

dataTables.buttons.js
Line 1797
to
Line 1828

    var columns = header.length;
    var rows = columns > 0 ? cells.length / columns : 0;
    // var body = [ rows ]; // use body.push() method instead
    var body = [];
    var rowGroupHeader = null;
    var cellCounter = 0;

    // read rowGroup dataSrc
    var rowGroup_index = null;
    if (dt.settings()[0].rowGroup.dataSrc)
        rowGroup_index = dt.settings()[0].rowGroup.dataSrc();

    for ( var i=0, ien=rows ; i<ien ; i++ ) {
        var row = [ columns ];

        for ( var j=0 ; j<columns ; j++ ) {

            // Add group header
            if (rowGroup_index != null && j == rowGroup_index) {
                if (rowGroupHeader != cells[ cellCounter ]) {
                    body.push( ['<td colspan="'+(columns-1)+'"><b>'+cells[ cellCounter ]+'</b></td>'] );
                    rowGroupHeader = cells[ cellCounter ];
                }
            }

            row[j] = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
            cellCounter++;
        }

        body.push(row);
        // body[i] = row;
    }

craign · January 2018

Hi there, thanks for posting this, I'm current integrating it into my own solution. Just a quick question though, should this work for nested groups? I noticed a few variables like sub_group_array which would imply such functionality, although am not too sure. Any light shed would be much appreciated.

maotanWang · January 2018

Have anybody can explain how to use grouped_array_index:[<place row to use here>] correctly, I place the datatable.buttons.js and add exportOptions, but it didn't work.

samlearn621 · November 2018

Hi, I have tried to export with subheading (with rowGroup). But still no luck. Could someone point how to incorporate above changes clearly? as some other people said, I am not sure how to use " grouped_array_index:[<place row to use here>] ". Thanks.

elalgarro · December 2018

@samelearn621 it's a custom attribute you add when initializing your table

for example:

$('#table-id').DataTable({
  buttons: [
    {
      extend: 'pdfHtml5',
      exportOptions: {
        //n is the index of the column you want to group by. 
        //for example if you want to group by the first col, you would use 0 for n
        grouped_array_index: [ n ] //note the brackets, i think this is so you can group by multiple columns. 
      }
    }
  ]
})

elalgarro · December 2018

spent some time working on this, cleaned up the code a bit. It seems like there's some additional functionality in this post that is either not used or not shown in this post's code snippet

you should be able to paste this into the bottom of the _exportData function in datatables.buttons.js

note, i changed the grouped_array_index to take an integer instead of an array.

                var grouped_array_index     = config.grouped_array_index;
                        
                if (grouped_array_index == undefined) { //don't run grouping logic if rows aren't grouped
                    return data
                }
                var no_of_columns           = header.length;
            var no_of_rows              = no_of_columns > 0 ? cells.length / no_of_columns : 0;

            var body_data               = new Array( no_of_rows );                                
            var body_with_nodes         = new Array( no_of_rows );                          
            var cellCounter             = 0;
         
            for (var i = 0, ien = no_of_rows; i < ien; i++)
            {
                var rows            = new Array( no_of_columns );
                var rows_with_nodes = new Array( no_of_columns );
         
                for ( var j=0 ; j<no_of_columns ; j++ )
                {
                    rows[j]             = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
                    rows_with_nodes[j]  = config.format.body( cellNodes[ cellCounter ], i, j, cells[ cellCounter ] ).outerHTML;
                    cellCounter++;
                }
                body_data[i]                = rows;
                body_with_nodes[i]          = $.parseHTML('<tr class="even">'+rows_with_nodes.join("")+'</tr>');
            }
         
            /******************************************** GROUP DATA *****************************************************/
            var row_array                       = dt.rows().nodes();
            var row_data_array                  = dt.rows().data();
            var iColspan                        = no_of_columns;
            var sLastGroup                      = "";
         
            var no_of_splices                   = 0;  //to keep track of no of element insertion into the array as index changes after splicing one element
         
            for (var i = 0, row_length = body_with_nodes.length; i < row_length; i++)
            {
                var sGroup                      = row_data_array[i][grouped_array_index];
                if ( sGroup !== sLastGroup )
                {
                    var table_data              = [];
                    var table_data_with_node    = '';
         
                    for(var $column_index = 0;$column_index < iColspan;$column_index++)
                    {
                        if($column_index === 0)
                        {
                                            // strips anything inside < > tags. hoping this won't be an issue in the future. 
                            table_data[$column_index]   = sGroup.replace( /<[^>]*>/gi, '' ); + " ";
                        }
                        else
                        {
                            table_data[$column_index]   = '';
                        }
                    }
                    body_data.splice(i + no_of_splices, 0, table_data);
                    no_of_splices++;
                    sLastGroup = sGroup;
                }
            }
         
            return {
                header: header,
                footer: footer,
                body:   body_data
            };

Eufragio · January 2019

I understand that you can not export the grouping of rows, but how could you take the result of each enlargement at the bottom of the page <tfoot> :

the result that I hope is this :

allan · January 2019

If that information is being written into the footer, then it should be included in export. If that isn't the case, please link to a test case showing the issue.

Allan

Eufragio · January 2019

This is an example of a current project: https://jsfiddle.net/Eufragio/b1ju4r0d/1/

kthorngren · January 2019

It looks like your example is using this example:
https://datatables.net/examples/advanced_init/row_grouping.html

You may want to consider using the RowGroup Extension as it has builtin options you may want to use:
https://datatables.net/extensions/rowgroup/

However this won't help with your current question. One option would be to use a global object variable to keep track of the column totals for each column using the group_assoc as the key. Similar to your total variable but it would look something like this:

{2: {edinburgh: 100, London: 500,.....},
 5: {edinburgh: 38473.00, London: 99283.00,.....}
}

Then in the FooterCallback loop through column 2 and 5 and build your HTML output as desired.

The other option might be to simply adjust your FooterCallback to use a total variable similar to what you are doing in the DrawCallback with the row grouping.

Kevin

Eufragio · January 2019

@kthorngren You could do a demonstration with the ideas you have. https://jsfiddle.net/Eufragio/b1ju4r0d/4/

Because what I need is something like this :

Since I can not export row grouping, I want to at least export the results of those groupings in the footer

kthorngren · January 2019

Here you go:
https://jsfiddle.net/kLj1ebsp/

I commented out the FooterCallback code that is summing column 5. Added code in the row group to update the footer with the totals of each group. I'll leave any formatting of the footer for you to define.

Kevin

kthorngren · January 2019

I don't think I saved the fiddle. Try this:
https://jsfiddle.net/qk9tac51/

Kevin

Eufragio · January 2019

I can not find any difference between me: https://jsfiddle.net/Eufragio/b1ju4r0d/4/

with yours: https://jsfiddle.net/kLj1ebsp/

I do not know if you still understand what I need, but as I show in the footer page that general total, I would like to show the totals of each group. I do not know if that is possible, because in the footnote those totals could be exported. Thanks for your time

kthorngren · January 2019

I can not find any difference

Yes, that is why I posted an updated link:
https://jsfiddle.net/qk9tac51/

Kevin

Eufragio · January 2019

Thank you very much, that's what I needed.

Now what I do not know if it is possible to export all those results that are in the footer. It does not matter that you do not export the grouping, but if those results

Eufragio · January 2019

This was my result: https://jsfiddle.net/Eufragio/u342qgoz/1/

Not if someone knows how to manipulate the cells

kthorngren · January 2019

Answered question in your other post:
https://datatables.net/forums/discussion/53919/line-break-when-exporting-to-excel#latest

Kevin

smichaelxx · March 2019

Is it possible to add subtotals to PDF and print?
Regards
M

smichaelxx · March 2019

If ServerSide is used, grouping will not work. Any idea to solve this?
Thanks
Regards
M

kthorngren · March 2019

Is it possible to add subtotals to PDF and print?

If you are referring to the subtotals in RowGroup the answer is no. The RowGroup docs state this:

No support for the export options of the Buttons extension - the grouping information is just ignored

If ServerSide is used, grouping will not work. Any idea to solve this?

RowGroup builds the groups from the data in the client. With Server Side processing the only data in the client is the page being shown. RowGrouping does work with server side processing, see this example. However totaling, like this example will only work with the data in the client. It would be possible to perform the data sums in the server and return the results in the ajax response. The response data totals can be desiplay in the RowGroup.

Kevin

PhaniDevisetty · May 2019

Hi All, I need to export the Data table data with RowGroup in Excel,
I am able to show the data in table, but I couldn't download as same format,
Can anyone assist me,

Regards,
Phani.

colin · May 2019

Hi @PhaniDevisetty ,

This thread should help, it's asking the same thing.

Cheers,

Colin

Row grouping with printing

Row grouping with printing

Replies

Howdy, Stranger!

Categories

DataTables

Row grouping with printing

Row grouping with printing

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables