sum columns

horus1613

Good afternoon.
I use the template https://datatables.net/extensions/buttons/examples/html5/excelCellShading

$(document).ready(function() {
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [{
extend: 'excelHtml5',
customize: function(xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];

            // Loop over the cells in column `C`
            $('row c[r^="C"]', sheet).each( function () {
                // Get the value
                if ( $('is t', this).text() == 'New York' ) {
                    $(this).attr( 's', '20' );
                }
            });
        }
    }]
});

});

I need to read the sum of the visible values in several columns (suppose 5 and 6) and put these values in TFOOT.
Tell me, please, how to do this in relation to this code. The options found in the forum do not work.

allan

This should work assuming you want the sum visible in the browser as well.

Allan

horus1613

I'm dumb. Wherever I put this code, it refuses to work.

            $(document).ready(function() {
                $('#example').DataTable({
                    dom: 'Bfrtip',
                    buttons: [{
                        extend: 'excelHtml5',
                        customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];

                            // Loop over the cells in column `E`
                            $('row c[r^="E"]', sheet).each( function () {
                                // Get the value
                                if ( $('is t', this).text() == 'отключен' ) {
                                    $(this).attr( 's', '20' );
                                }
                            });
                        }
                    }];
                            "footerCallback": function ( row, data, start, end, display ) {
                        var api = this.api(), data;

                        // Remove the formatting to get integer data for summation
                        var intVal = function ( i ) {
                            return typeof i === 'string' ?
                                i.replace(/[\$,]/g, '')*1 :
                                typeof i === 'number' ?
                                    i : 0;
                        };

                        // Total over all pages
                        total = api
                            .column( 8 )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        // Total over this page
                        pageTotal = api
                            .column( 8, { page: 'current'} )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        // Update footer
                        $( api.column( 8 ).footer() ).html(
                            '$'+pageTotal +' ( $'+ total +' total)'
                        );
                    }
                });
            });

horus1613

I managed. Allan, thanks!

gregraven

I prefer to format the totals thusly:

$(document).ready(function() {
    $("#example").DataTable({
        dom: "Bfrtip",
        buttons: [{
            extend: "excelHtml5",
            customize: function(xlsx) {
                var sheet = xlsx.xl.worksheets["sheet1.xml"];

                // Loop over the cells in column `E`
                $("row c[r^="E"]", sheet).each(function() {
                    // Get the value
                    if ($("is t", this).text() == "отключен") {
                        $(this).attr("s", "20");
                    }
                });
            }
        }];
        "footerCallback": function(row, data, start, end, display) {
            var api = this.api(),
                data;

            // Remove the formatting to get integer data for summation
            var intVal = function(i) {
                return typeof i === "string" ?
                    i.replace(/[\$,]/g, "") * 1 :
                    typeof i === "number" ?
                    i : 0;
            };

            // Total over all pages
            total = api
                .column(8)
                .data()
                .reduce(function(a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

            // Total over this page
            pageTotal = api
                .column(8, {
                    page: "current"
                })
                .data()
                .reduce(function(a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

            // Update footer
            // Create our number formatter.
            var formatter = new Intl.NumberFormat("en-US", {
                style: "currency",
                currency: "USD",
                minimumFractionDigits: 2,
                // the default value for minimumFractionDigits
                // depends on the currency
            });

            $(api.column(8).footer()).html(
                formatter.format(pageTotal) + " ("
                formatter.format(total) + " total)";
            );
        }
    });
});

horus1613

Thanks!