Export and Print for RowGroup Extension

daytonoutar · October 2017

I have taken note of the option of using the source code found at https://datatables.net/forums/discussion/42916/row-grouping-with-printing#latest. However, I'm hoping there is another simpler way to have the printing and exporting features of the Button extension able to print and export row grouping.

See below screenshot of RowGroup working on screen

See below screenshot of same datatable when Print is chosen

allan · October 2017

Sorry no. The RowGroup extension doesn't play nicely with the export options at the moment (see the compatibility chart). That is something I hope to address in future.

Nice styling on the row grouping btw!

Allan

daytonoutar · October 2017

Thanks.

I'm just going to make a print.css to print this much needed categorization.

Good work on the progress you have made on DataTables.

tducca · June 2019

Do you have news to print with group?

colin · June 2019

Nope, there's been no progress. It's in the backlog but isn't a priority right now, I'm afraid.

bubomike · June 2020

I couldn't get the code listed at https://datatables.net/forums/discussion/42916/row-grouping-with-printing to work properly, but with a little tweaking do have a working solution for exporting datatables with row grouping.

As in that thread:

Usage:
Define grouped_array_index in exportOptions

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

New logic in _exportData in datatable-buttons.js:

        var grouped_array_index = config.grouped_array_index;

        if ( !(grouped_array_index == undefined) ) { //don't run grouping logic if rows aren't grouped

            var row_array = dt.rows().nodes();
            var row_data_array = dt.rows().data();
            var iColspan = columns;
            var sLastGroup = "";
            var no_of_splices = 0;

            for (var i = 0, row_length = body.length; i < row_length; i++) {
                var sGroup = row_data_array[i][grouped_array_index];

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

                    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.splice(i + no_of_splices, 0, table_data);
                    no_of_splices++;
                    sLastGroup = sGroup;
                }
            }
        }

For completeness, the entire function _exportData in datatable-buttons.js can be replaced with:

    var _exportData = function ( dt, inOpts )
    {
        var config = $.extend( true, {}, {
            rows:           null,
            columns:        '',
            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 );
                }
            },
            customizeData: null
        }, inOpts );

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

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

            // Always remove comments
            str = str.replace( /<!\-\-.*?\-\->/g, '' );

            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;

        // If Select is available on this table, and any rows are selected, limit the export
        // to the selected rows. If no rows are selected, all rows will be exported. Specify
        // a `selected` modifier to control directly.
        var modifier = $.extend( {}, config.modifier );
        if ( dt.select && typeof dt.select.info === 'function' && modifier.selected === undefined ) {
            if ( dt.rows( config.rows, $.extend( { selected: true }, modifier ) ).any() ) {
                $.extend( modifier, { selected: true } )
            }
        }

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

        var columns = header.length;
        var rows = columns > 0 ? cells.length / columns : 0;
        var body = [];
        var cellCounter = 0;

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

            for ( var j=0 ; j<columns ; j++ ) {
                row[j] = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
                cellCounter++;
            }

            body[i] = row;
        }

        var grouped_array_index = config.grouped_array_index;

        if ( !(grouped_array_index == undefined) ) { //don't run grouping logic if rows aren't grouped

            var row_array = dt.rows().nodes();
            var row_data_array = dt.rows().data();
            var iColspan = columns;
            var sLastGroup = "";
            var no_of_splices = 0;

            for (var i = 0, row_length = body.length; i < row_length; i++) {
                var sGroup = row_data_array[i][grouped_array_index];

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

                    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.splice(i + no_of_splices, 0, table_data);
                    no_of_splices++;
                    sLastGroup = sGroup;
                }
            }
        }

        var data = {
            header: header,
            footer: footer,
            body:   body
        };

        if ( config.customizeData ) {
            config.customizeData( data );
        }

        return data;
    };

colin · June 2020

Nice, thanks for sharing.

Colin

aromero1911 · September 2020

bubomuke, you rock! Your code works great on my solution.

Just one thing, I'm trying to make the grouped header indent. I tried using the customize setting on the button, but it doesn't seem to take. Any suggestions?

sarooptrivedi · May 2021

Hey @bubomike ,

I tried your code for exporting the group row but it is not working as you explain. I replaced the _exportData in datatable-buttons.js with your code. but I am still confuse with this code
exportOptions: {
// Any other settings used
grouped_array_index: <place row to use here>,
},

how to pass value grouped_array_index: 2 or grouped_array_index:'Office'. I am binding data with Ajax.

Can you show the steps to replace the code with demo?

Thank you in advance.

LeksVelo · July 2021

@sarooptrivedi

I paste this code in datatables.js (_exportData function) and
use grouped_array_index: "office"

var _exportData = function ( dt, inOpts )
{
    var config = $.extend( true, {}, {
        grouped_array_index: false,
        rows:           null,
        columns:        '',
        modifier:       {
            search: 'applied',
            order:  'applied'
        },
        orthogonal:     'display',
        stripHtml:      true,
        stripNewlines:  true,
        decodeEntities: true,
        trim:           true,
        format:         {
            header: function ( d ) {
                return Buttons.stripData( d, config );
            },
            footer: function ( d ) {
                return Buttons.stripData( d, config );
            },
            body: function ( d ) {
                return Buttons.stripData( d, config );
            }
        },
        customizeData: null
    }, inOpts );

    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;
    
    // If Select is available on this table, and any rows are selected, limit the export
    // to the selected rows. If no rows are selected, all rows will be exported. Specify
    // a `selected` modifier to control directly.
    var modifier = $.extend( {}, config.modifier );
    if ( dt.select && typeof dt.select.info === 'function' && modifier.selected === undefined ) {
        if ( dt.rows( config.rows, $.extend( { selected: true }, modifier ) ).any() ) {
            $.extend( modifier, { selected: true } )
        }
    }

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

    var columns = header.length;
    var rows = columns > 0 ? cells.length / columns : 0;
    var body = [];
    var cellCounter = 0;

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

        for ( var j=0 ; j<columns ; j++ ) {
            row[j] = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
            cellCounter++;
        }

        body[i] = row;
    }
    
    var grouped_array_index = config.grouped_array_index;
    if ( grouped_array_index !== false ) { //don't run grouping logic if rows aren't grouped
 
        var row_array = dt.rows().nodes();       
        var row_data_array = dt.rows().data();
        var iColspan = columns;
        var sLastGroup = "";
        var no_of_splices = 0;
 
        for (var i = 0, row_length = body.length; i < row_length; i++) {
            var sGroup = row_data_array[i][grouped_array_index];
            if ( sGroup !== sLastGroup ) {
                var table_data = [];
 
                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.splice(i + no_of_splices, 0, table_data);
                no_of_splices++;
                sLastGroup = sGroup;
            }
        }
    }

    var data = {
        header: header,
        footer: footer,
        body:   body
    };

    if ( config.customizeData ) {
        config.customizeData( data );
    }

    return data;
};

BhavinBhatt · September 2021

This will export group rows only.
what about sums and avgs, whatever we used with rowGroup to display summarized data??

colin · September 2021

There are a few threads discussing exporting the RowGroup headers, such as here and here. Hopefully that'll help,

Colin

pmconsulting · January 17

I'm loading datatables.js using the download builder to get the documents from the CDN. Does this solution work if I include the new function in a different file or do I have to replace the specific lines in datatables.js?

colin · January 17

You would need to replace the file with that change, I'm afraid.

Colin

pmconsulting · January 17

Answering myself: I downloaded the combined file from this site & made the changes per this post & it is working as described. Can anyone direct me to documentation that iterates through the settings available for customizing the pdf file?

kthorngren · January 17

The pdfhtml5 docs provide the details. You will want. to refer to the PDFMake docs also.

Kevin

Export and Print for RowGroup Extension

Export and Print for RowGroup Extension

Answers

Howdy, Stranger!

Categories

DataTables

Export and Print for RowGroup Extension

Export and Print for RowGroup Extension

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables