Row grouping with printing

Row grouping with printing

sciaschisciaschi Posts: 3Questions: 0Answers: 0
edited June 2017 in Free community support

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>],
 },

Replies

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

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

    Regards,
    Allan

  • cairoramos7cairoramos7 Posts: 1Questions: 0Answers: 0

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

    how to use this method to do the grouping?

  • rushidrautrushidraut Posts: 3Questions: 1Answers: 0

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

  • ckeggckegg Posts: 1Questions: 0Answers: 0

    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;
        }
    
    
  • craigncraign Posts: 1Questions: 0Answers: 0
    edited 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.

  • maotanWangmaotanWang Posts: 1Questions: 0Answers: 0

    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.

  • samlearn621samlearn621 Posts: 1Questions: 0Answers: 0

    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.

  • elalgarroelalgarro Posts: 2Questions: 0Answers: 0

    @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. 
          }
        }
      ]
    })
    
  • elalgarroelalgarro Posts: 2Questions: 0Answers: 0

    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
                };
    
  • EufragioEufragio Posts: 20Questions: 2Answers: 0
    edited 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 :

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    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

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • EufragioEufragio Posts: 20Questions: 2Answers: 0
    edited 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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

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

    Kevin

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    I can not find any difference

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

    Kevin

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

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

      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

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

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

    Not if someone knows how to manipulate the cells

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
  • smichaelxxsmichaelxx Posts: 4Questions: 1Answers: 0

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

  • smichaelxxsmichaelxx Posts: 4Questions: 1Answers: 0

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
    edited 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

  • PhaniDevisettyPhaniDevisetty Posts: 1Questions: 0Answers: 0

    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.

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

    Hi @PhaniDevisetty ,

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

    Cheers,

    Colin

This discussion has been closed.