Currency formatting

doug_hale_jr

Is anyone aware of a way to format a column as currency with commas and $ and still allow for totalling in the footer?

allan

Hi,

What you will probably need to do is parse the your currency information on each callback. As you will be able to see on the footer callback demo ( http://www.datatables.net/examples/example_footer_callback.html ) you will that I loop over the visible data (with the line: "iPageMarket += aaData[ aiDisplay[i] ][4]*1;") summing it up. What you could do is something like:

iTotal += aaData[ aiDisplay[i] ][4].replace(/[$,]/g, "") * 1;

This will take the information from the table (change the column as you require), remove the $ and commas and then "cast" the string as a number (adding it to the result).

Hope this helps!

Allan

doug_hale_jr

Hey Allan!

Nice re-write of the site and thanks for the forums!!

For the moment, I formatted one column with $ and commas and used a second column (hidden) that I used for getting the total. But your solution sounds better so I'll be changing my code.

Thanks again!!!
Doug

allan

Hi Doug,

Glad to hear that has done the trick! Thanks for the complements on the site!

Allan

naspinski

One method I use it to render the table with straight integers (could use doubles, etc.) and use the "fnDrawCallback" to format the currency clientside each time:

"fnDrawCallback": function() {
$("td[id*='class_that_has_currrency_in_it']").each(function() {
$(this).html(formatCurrency($(this).html()));
});
}

and I grabbed this formatCurrency funciton from somehwere?

function formatCurrency(num) {
num = num.toString().replace(/\$|\,/g, '');
if (isNaN(num))
num = "0";
sign = (num == (num = Math.abs(num)));
num = Math.floor(num * 100 + 0.50000000001);
cents = num % 100;
num = Math.floor(num / 100).toString();
if (cents < 10)
cents = "0" + cents;
for (var i = 0; i < Math.floor((num.length - (1 + i)) / 3); i++)
num = num.substring(0, num.length - (4 * i + 3)) + ',' + num.substring(num.length - (4 * i + 3));
return ((sign) ? '' : '-') + '$' + num + '.' + cents;
}

allan

Worth nothing that fnRender can also be used to do this:

[code]
$(document).ready(function() {
$('#example').dataTable( {
aoColumns: [
null,
null,
null,
null,
{
fnRender: function ( o ) {
return "$"+o.aData[ o.iDataColumn ];
},
bUseRendered: false
]
} );
} );
[/code]

Allan