how to mix footerCallback and columnDefs?

francians

Hi!
I'm struggling with this question: how can I mix footerCallback and columnDefs?
I did this basic example:

http://jsfiddle.net/bv5qk06k/8/

but I want the column with amount be formatted, footer included, during rendering.
I CANNOT simply format the amount in footerCallback, my goal is to turn on the "Buttons" extension without breaking Excel...
I think the problem is that footer is wrote using .html(), but I haven't found a way to avoid it.
Any suggestion?
Many thanks
Cheers,
Francesco

kthorngren

Maybe Javascript's toLocaleString will help. Try this:

$( api.column( 1 ).footer() ).html(  total.toLocaleString('de-DE', {minimumFractionDigits: 2, maximumFractionDigits: 2})  );

Updated your example here:
http://jsfiddle.net/pcm5aurf/6/

Kevin

francians

Hi Kevin,
Thanks for the prompt reply.. I fear it will fix the rendering, but will break the Excel... I'll give it a try anyway later today.
BR
Francesco

francians

I tested the solution and I can confirm the Excel is broken...
175,00 will be converted in 17500

francians

I've found a workaround that I post here for future reference...
Using kthorngren suggestion (or "$.fn.dataTable.render.number( '', ',', 2, '' ).display()" or a replace of "." with ",") we display right data in table then we can do the opposite in Excel footer export:

var excelButton = {
    exportOptions: {
        columns: ':visible',
        orthogonal: 'export',
        format: {
            footer: function ( data, columnIdx, idx ) {
                return columnIdx===1 ? data.replace(",",".") : data;
            }
        }
    }
};

and of course:

        buttons: [
            $.extend( true, {footer:true}, excelButton, {extend: 'excelHtml5'} ),
        ]

as I told this is not a proper fix or solution to the problem!

kthorngren

Have you tried creating a buttons formatting function for the footer?
https://datatables.net/extensions/buttons/examples/html5/outputFormat-function.html

Kevin

francians

It's what I did in the end.. but it's a kind of gross IMHO

alexela8882

@kthorngren I have a column which has a data like 3 days, 14hrs & 30 minutes. I converted it to seconds and called it in the footerCallback. However, I can't get it working converting it back to the above format. My code looks like this:

"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(10)
          .data()
          .reduce( function (a, b) {
                return intVal(a) + intVal(b);
          }, 0 );

      // Total over this page
      pageTotal = api
          .column(10, { page: 'current'} )
          .data()
          .reduce( function (a, b) {
          var seconds = intVal(a) + intVal(b);
          var seconds = parseInt(seconds);
          return seconds + 1; // it adds 10 which is my current page like it
                  always on loop;
                  tried to reserve it but it only makes it a complicated
          // var days = Math.floor(seconds / (3600*24));
              // seconds  -= days*3600*24;
          // var hrs   = Math.floor(seconds / 3600);
          // seconds  -= hrs*3600;
          // var mnts = Math.floor(seconds / 60);
          // seconds  -= mnts*60;
          // var jaa0324 = days+" days, "+hrs+" hours & "+mnts+" minutes";
              // return days;
          }, 0 );

      // Update footer
      $( api.column(1).footer() ).html(
        pageTotal
      );
  },

Converting seconds into desired format will be inaccurate (see the comment in the code). And so I tried doing it in the footer update:

      // Update footer
      $( api.column(1).footer() ).html(
        pageTotal // even something like Math.floor(pageTotal) won't work
      );

colin

Hi @alexela8882 ,

This sounds like a different to the main thread, so it would be worth raising separately. What I would say though, is any thing time based, just use Moment.js, as it's excellent for all time/date based operations.

If that doesn't help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin