Datatables Excel export money amounts in EU format

HarryHipster

I'm trying to format my amounts in the EU format ("," for decimal).

I got it working now using .toLocaleString(). But it's not really working when I export to Excel and I have no clue how to fix it.

In my view a number it is displayed as €1.234.56,78
But when I export to Excel it shows it as €12.345.678,00

Is there a way I can just /100 during the export?

colin

Hi @HarryHipster ,

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

HarryHipster

Here is a testcase: live.datatables.net/jogegexo/2/edit

rf1234

You need to make sure you export 123456.78 to Excel. Based on the local Excel installation and the style you apply Excel will format the value according to its regional settings. Using built in style "64 (Numbers with thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.3)" Excel will display this:
- English installation: 1,234,56.78
- Germany and many other European countries: 1.234.56,78

If you pass this: 1.234.56,78 Excel doesn't really know how to handle it because it contains multiple decimal points and a comma in the wrong spot ... All of the periods and the comma are therefore being ignored which makes Excel display 12.345.678,00 because the style has two decimals and Excel couldn't recognize any decimal places.

This is how I handle this in "exportOptions". For German formatted numbers I get rid of the thousand separator points and convert the decimal comma to a decimal point. For English formatted numbers I simply get rid of the thousand separator commas.

if (lang == 'de') { //this time we use the English formatting
    //data contain only one comma we need to split there
    var arr = data.split(',');
    //subsequently replace all the periods with spaces
    arr[0] = arr[0].toString().replace( /[\.]/g, "" );
    //join the pieces together with a period if not empty
    if (arr[0] > ''  || arr[1] > '') {
        data = arr[0] + '.' + arr[1];
    } else {
        return '';
    }
} else {
    data = data.toString().replace( /[\,]/g, "" );
}