Footer Data Effecting Excel Export...

MFG

Is there any documentation, best practices or examples available on how to implement footers while maintaining the Column Auto Fit default associated with the ExcelHTML5 Export?

Most of our tables contain financial/numeric data and require footer information with totals and averages.

I am finding that the footer data is disrupting the Auto Fit sizing of columns when the table is exported to Excel.

Thank You.

Michael.

MFG

Apologies for not including an example. It can now be found here: http://live.datatables.net/ligiroyu/1/edit

Table 1 places the Page Totals & Grand Totals within a single <tr> inside <tfoot>. The table looks fine during display. When the table is exported to Excel, the contents of the footer cells are not being wrapped - mashing the content together & making the cell width larger. I did not realize this until I added footer: true to ExcelHtml5. I suspect the <br /> tags are the culprit, but don't know how to replace during Excel export.

Table 2 is my attempt at adding a 2nd <tr> to <tfoot>. However, I am not sure how to modify the footerCallback function in order to place each of the totals on their proper row. In addition the 2nd row is not being exported and it is not following the responsive behavior of the footer's first row. Resize the output window to see this.

Hope all this makes sense now.

Any suggestions or guidance would be greatly appreciated.

Thank You.

vaishnavkoka

Hi @MFG,

you can modify the below code according to your requirement.

`$(document).ready(function() {
var currentDate = new Date()
var day = currentDate.getDate()
var month = currentDate.getMonth() + 1
var year = currentDate.getFullYear()
var d = day + "-" + month + "-" + year;
function getBase64FromImageUrl(url) {
var img = new Image();
img.crossOrigin = "anonymous";
img.onload = function () {
var canvas = document.createElement("canvas");
canvas.width =this.width;
canvas.height =this.height;
var ctx = canvas.getContext("2d");
ctx.drawImage(this, 0, 0);
var dataURL = canvas.toDataURL("image/png");
return dataURL.replace(/^data:image\/(png|jpg);base64,/, "");
};
img.src = url;
}
var table=$('#statistics').dataTable( {
"fnDrawCallback": function( oSettings ){
console.log("in");
},

dom: 'lBfrtip',
// "dom": '<"dt-buttons"Bf><"clear">lirtp',
"lengthMenu": [[4, 8, 12, -1], [4, 8, 12, "All"]],
autoWidth:true,
paging: true,
"pagingType": "full_numbers",

    buttons: [
        {   
            text         : '----PDF----',
            extend       : 'pdfHtml5',
            className    : " btn-success",
            filename     :d+'stats',
            footer       : true,
            messageBottom:'reached end',
            title        : 'koka',
            orientation  : 'portrait', //portrait
            pageSize     : 'A4', //A3 , A5 , A6 , legal , letter
            exportOptions:{
                //   columns: ':visible', //     columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
                   columns: [0,1,2,3],
                   search: 'applied',
                    order: 'applied'
                },
            customize: function (doc) {
                    //Remove the title created by datatTables
                    doc.content.splice(0,1);
                    // doc.content[1].table.widths = [ '30%', '30%','40%'];
                    //Create a date string that we use in the footer. Format is dd-mm-yyyy
                    //var now = new Date();
                    //var jsDate = now.getDate()+'-'+(now.getMonth()+1)+'-'+now.getFullYear();
                     console.dir(doc)
                    //doc.content[1].margin = [ 10, 0, 10, 0 ] //left, top, right, bottom set page width
                    var now = new Date(); 
                    var days = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
                    var months = ["January","February","March","April","May","June","July","August","September","October","November","December"];
                    var hour=( now.getHours() < 10 ? "0" : "") + now.getHours()-12;
                    var jsDate =now.getDate() +"("+days[now.getDay()]+")"+ "/" + (now.getMonth()+1)+"("+months[now.getMonth()]+")"  + "/" + now.getFullYear() + " Time: "  +hour+ ":" + (now.getMinutes() < 10 ? "0" : "") + now.getMinutes() + ":" + (now.getSeconds() < 10 ? "0" : "") + now.getSeconds() ;

                    // Logo converted to base64
                    // var logo = getBase64FromImageUrl('https://datatables.net/media/images/logo.png');
                    // The above call should work, but not when called from codepen.io
                    // So we use a online converter and paste the string in.
                    // Done on http://codebeautify.org/image-to-base64-converter
                    // It's a LONG string scroll down to see the rest of the code !!!   
                    var logo=;//base64 image link
                    // A documentation reference can be found at
                    // https://github.com/bpampuch/pdfmake#getting-started
                    // Set page margins [left,top,right,bottom] or [horizontal,vertical]
                    // or one number for equal spread
                    // It's important to create enough space at the top for a header !!!
                    //doc.pageMargins = [20,60,10,50];
                    doc.pageMargins = [20,60,10,30];
                    // Set the font size fot the entire document
                    doc.defaultStyle.fontSize = 9;
                    // Set the fontsize for the table header
                    doc.styles.tableHeader.fontSize = 12;
                    // Create a header object with 3 columns
                    // Left side: Logo
                    // Middle: brandname
                    // Right side: A document title
                    doc['header']=(function() {
                        return {
                            columns: [
                                {
                                    image: logo,
                                    alignment:'right',
                                    width: 35
                                },
                                {
                                    alignment: 'center',
                                    italics: true,
                                    text: 'text1whereareyoubuddyget ack',
                                    fontSize: 18,
                                    margin: [10,0]
                                },
                                {
                                    alignment: 'left',
                                    fontSize: 14,
                                    text: 'text2'
                                },
                                {
                                    image: logo2,
                                    alignment:'left',
                                    width: 24
                                },
                                {
                                    image: logo,
                                    alignment:'right',
                                    width: 35
                                }

                            ],
                            margin: 20
                        }
                    });
                    // Create a footer object with 2 columns
                    // Left side: report creation date
                    // Right side: current page and total pages
                    doc['footer']=(function(page, pages) {
                        return {
                            columns: [
                                {
                                    alignment: 'left',
                                    text: ['Created on: ', { text: jsDate.toString() }]
                                },
                                {
                                    alignment: 'right',
                                    text: ['page ', { text: page.toString() },  ' of ', { text: pages.toString() }]
                                }
                            ],
                            margin: 20
                        }
                    });
                    // Change dataTable layout (Table styling)
                    // To use predefined layouts uncomment the line below and comment the custom lines below
                    // doc.content[0].layout = 'lightHorizontalLines'; // noBorders , headerLineOnly
                    var objLayout = {};
                    objLayout['hLineWidth'] = function(i) { return .5; };
                    objLayout['vLineWidth'] = function(i) { return .5; };
                    objLayout['hLineColor'] = function(i) { return '#aaa'; };
                    objLayout['vLineColor'] = function(i) { return '#aaa'; };
                    objLayout['paddingLeft'] = function(i) { return 4; };
                    objLayout['paddingRight'] = function(i) { return 4; };
                    doc.content[0].layout = objLayout;
            },

            repeatingHead: {
                logo: 'http://127.0.0.1/abc.svg',
                logoPosition: 'right',
                logoStyle: '',
                title: '<h3>Sample Heading</h3>'
            }

            /*customize: function ( win ) {
                $(win.document.body)
                    //console.dir(win)
                    //win.content[1].margin = [ 10, 0, 10, 0 ] //left, top, right, bottom
                    .css( 'font-size', '10pt' )
                    .prepend(
                        '<img src="http://127.0.0.1/deg_login/images/isro.svg" style="position:absolute; top:1; left:1; right:1; bottom:1;" />'
                    );


                $(win.document.body).find( 'table' )
                    .addClass( 'compact' )
                    .css( 'font-size', 'inherit' );
                      }

        */
        }
    ],  

    "footerCallback": function ( row, data, start, end, display ) {
        var api = this.api();

        // Remove the formatting to get integer data for summation
        var intVal = function ( i ) {
            return typeof i === 'string' ?

                i.replace(/[\MB,KB,GB,TB,]/g, '')*1 :
                typeof i === 'number' ?
                numeral(i).value() : i;
        };

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

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

        total = numeral(total).format('0.0a');
        pageTotal = numeral(pageTotal).format('0.0a');
      $( api.column( 3 ).footer() ).html(
         '--'+pageTotal +' ( --'+ total +' total)'
        );
    }

} );

} );
`

Thanks
Koka