RowGroup: Creating subtotal row is not compatible with excel and pdf function

fitri91

I try to create a subtotal row using this doc : https://datatables.net/extensions/rowgroup/examples/initialisation/customRow.html

But my code is not working if I include the function to create PDF & Excel. It works fine if I remove the PDF & Excel code.

Here is my code with the PDF & Excel function:

$(document).ready( function () {
    var table = $('#example').DataTable({
        'columnDefs': [
            {
                "className": "dt-center",
                "targets": [2,5]
            },
            {
                "className": "dt-head-center dt-body-right", 
                "targets": [3,4,6]
            }
        ],
        order: [[1, 'asc']],
        rowGroup:   {
            startRender:    null,
            endRender:  function (rows, group){

                var subtotalBilUsahawan = rows
                    .data()
                    .pluck(3)
                    .reduce(function (a,b) {
                        return a + b*1;
                    }, 0);
                subtotalBilUsahawan = $.fn.dataTable.render.number(',', '.', 0).display( subtotalBilUsahawan );

                return $('<tr/>')
                    .append( '<td colspan="3" bgcolor="#CDCDCD"><strong>' + group + ' TOTAL</strong></td>' )
                    .append( '<td bgcolor="#CDCDCD">' + subtotalBilUsahawan + '</td>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' );
                },
                dataSrc: 1
            }
    });
} );

$('.row-border').DataTable({
    "oLanguage": {
        "sSearch"     : "CARIAN: ",
        "sLengthMenu" : "Memapar _MENU_ rekod setiap mukasurat",
        "sZeroRecords": "Tiada rekod",
        "sInfo"       : "Memapar mukasurat _PAGE_ dari _PAGES_ (_MAX_ jumlah rekod)",
        "sInfoEmpty"  : "Tiada rekod untuk dipapar.",
        "sInfoFiltered": "(daripada _MAX_ jumlah rekod)",
        "oPaginate": {
            "sFirst":    "Pertama",
            "sLast":     "Akhir",
            "sNext":     ">>",
            "sPrevious": "<<"
            }
    },
    //scrollY:        'auto',
    //scrollX:        "100%",
    paging: true,
    destroy: true,
    "bLengthChange": false ,
    dom: 'Bfrtip',
    buttons: [
        //print properties for datatables----------------------------------------------------------

            {
                extend: 'pdfHtml5',
                text: 'Muat Turun PDF',
                orientation: 'potrait',
                pageSize: 'A4',
                header: true,
                title: 'emas.pdf',
                customize: function(doc) 
                    {
                        doc.defaultStyle.fontSize = 5; //<-- set fontsize to 16 instead of 10 
                        doc.styles.tableHeader.fontSize = 5;
                        doc.styles.tableHeader.fillColor = '#B8B8B8';
                        doc.styles.tableHeader.fillColor = '#000000';
                    } 
            },
            {
            extend: 'excelHtml5',
            text: 'Muat Turun Excel',
            customize: function( xlsx ) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var lastCol = sheet.getElementsByTagName('col').length - 1;
                var colRange = createCellPos( lastCol ) + '1';
                //Has to be done this way to avoid creation of unwanted namespace atributes.
                var afSerializer = new XMLSerializer();
                var xmlString = afSerializer.serializeToString(sheet);
                var parser = new DOMParser();
                var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
                var filterAttr = xmlDoc.createAttribute('ref');
                filterAttr.value = 'A1:' + colRange;
                xlsxFilter.setAttributeNode(filterAttr);
                sheet.getElementsByTagName('worksheet')[0].appendChild(xlsxFilter);
            }
        }
    ]
});

And here the working code without PDF & Excel Function:

$(document).ready( function () {
    var table = $('#example').DataTable({
        'columnDefs': [
            {
                "className": "dt-center",
                "targets": [2,5]
            },
            {
                "className": "dt-head-center dt-body-right", 
                "targets": [3,4,6]
            }
        ],
        order: [[1, 'asc']],
        rowGroup:   {
            startRender:    null,
            endRender:  function (rows, group){

                var subtotalBilUsahawan = rows
                    .data()
                    .pluck(3)
                    .reduce(function (a,b) {
                        return a + b*1;
                    }, 0);
                subtotalBilUsahawan = $.fn.dataTable.render.number(',', '.', 0).display( subtotalBilUsahawan );

                return $('<tr/>')
                    .append( '<td colspan="3" bgcolor="#CDCDCD"><strong>' + group + ' TOTAL</strong></td>' )
                    .append( '<td bgcolor="#CDCDCD">' + subtotalBilUsahawan + '</td>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' )
                    .append( '<td bgcolor="#CDCDCD"/>' );
                },
                dataSrc: 1
            }
    });
} );

allan

This is correct. I'm afraid that the grouping row is not included in the exported data. This is a known limitation.

Allan

fitri91

Do you have any workaround for this kind of issue?

allan

No sorry. I've been discussing with @colin about how we should approach this, but it isn't going to be a simple thing to develop, so there is no short term workaround.

Allan

jlock

Have you been able to get the autofilter function in your code to work?

customize: function( xlsx ) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var lastCol = sheet.getElementsByTagName('col').length - 1;
                var colRange = createCellPos( lastCol ) + '1';
                //Has to be done this way to avoid creation of unwanted namespace atributes.
                var afSerializer = new XMLSerializer();
                var xmlString = afSerializer.serializeToString(sheet);
                var parser = new DOMParser();
                var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
                var filterAttr = xmlDoc.createAttribute('ref');
                filterAttr.value = 'A1:' + colRange;
                xlsxFilter.setAttributeNode(filterAttr);
                sheet.getElementsByTagName('worksheet')[0].appendChild(xlsxFilter);
            }

ChrisGR

I have made some small changes in the source code of the autoFilter function in order to work for me and to suit my needs:

,customize: function(xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var lastCol = sheet.getElementsByTagName('col').length - 1;
    var colRange = createCellPos( lastCol ) + '2';//AutoFilter in the second row
    //Has to be done this way to avoid creation of unwanted namespace atributes.
    var afSerializer = new XMLSerializer();
    var xmlString = afSerializer.serializeToString(sheet);
    var parser = new DOMParser();
    var xmlDoc = parser.parseFromString(xmlString,'text/xml');
    var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
    var filterAttr = xmlDoc.createAttribute('ref');
    filterAttr.value = 'A2:' + colRange;
    xlsxFilter.setAttributeNode(filterAttr);
    var sheetDataNode = sheet.getElementsByTagName('worksheet')[0].getElementsByTagName("sheetData")[0];
    sheet.getElementsByTagName('worksheet')[0].insertBefore(xlsxFilter, sheetDataNode.nextSibling);
}

It may be helpful to someone....