Error on Excel-Export on render.number

Rappi

Hello,

I have found a bug on Excel-Export.

When I use the render.number function, the values x 100 are entered into Excel.
Example: I have the value 10,-. Then in the export is 1000,-
In the CSV export the value is correct. Likewise with the print and PDF export. So it is only about the Excel export.

I'am using:

...
data: "Betrag", render: $.fn.dataTable.render.number( '.', ',', 2, '€' )
...

My version:

DataTables 1.12.11.12.1
Buttons 2.2.32.2.3
ColReorder 1.5.61.5.6
Editor 2.0.82.0.10
FixedColumns 4.1.04.1.0
FixedHeader 3.2.43.2.4
Responsive 2.3.02.3.0
RowGroup 1.2.01.2.0
RowReorder 1.2.81.2.8
Scroller 2.0.72.0.7
SearchBuilder 1.3.41.3.4
SearchPanes 2.0.22.0.2
Select 1.4.01.4.0

Greetings
Rappi

rf1234

Yep, you are using German number formatting which is kind of toxic for Excel. Excel expects your number in English US format.

10,- is interpreted to be a 10 followed by a thousand separator and then nothing which is interpreted to be 00. Hence you get 10,00 which might be converted into 10.00 in case you are using a German Excel version.

There is tons of posts on this in this forum. Many by myself. Feel free to search. I would convert your formatted number to an unformatted one. Then it should work.

rf1234

I have found a bug on Excel-Export.

No, not a bug. Smart design by microsoft I'd rather say. They can't anticipate what culture you are from if you provide formatted numbers which you shouldn't. When exporting to Excel you are using a generic interface, not a German version of Excel.

If I recall it correctly:
In case you export a csv file you need to anticipate what field separator your users will use in their Excel: German users will have a ";" preset as the separator while English users will have a comma preset.

For Excel it is opposite:
You need to export every number in English US format, preferably without thousand separators and have Excel convert it into the respective national view.

1 Million:
Export format to Excel should be 1000000.00
Display in an English Excel version with the column properly formatted: 1,000,000.00
Display in a German Excel version: 1.000.000,00

Good luck!

allan

Excellent points. To add to that, consider using the number renderer in DataTables. In 1.12 I added the ability for it to automatically detect the user's locale and format the number accordingly. That then allows the wire format to be unformatted (xxxxx.xx for example).

That also allows the export to export numbers rather than strings.

Allan

Rappi

Thank you for your answers.
I can not get it to work.
When I use

data: "Betrag", render: $.fn.dataTable.render.number( null, null, 2, '€' )

then I get the same result as with

data: "Betrag", render: $.fn.dataTable.render.number( '.', ',', 2, '€' )

At

data: "Betrag", render: $.fn.dataTable.render.number( null, null, 0, '€' )

is rounded up or down and in the table the decimal places are not shown.

Only

data: "Betrag", render: $.fn.dataTable.render.number( ',', '.', 2, '€' )

works. However, then in the table as a decimal separator a dot is displayed instead of a comma. Not nice, but at least the export works. There is indeed a comma.

How can I make it so that the decimal separator are displayed as comma in the table and the ecel export still works?

allan

Sounds to me like your browser might be detecting your locale as en-US. Try this in your browser's console:

new Intl.NumberFormat().format(100000.1); 

what does it show?

Allan

Rappi

Hi Allan,

it show '100.000,1'

It's Chrome on Windows 11 in german.

allan

I'm really surprised that $.fn.dataTable.render.number( null, null, 2, '€' ) would result in a decimal dot in that case. Can you give me a link to the page in question please?

Allan

Rappi

Sorry. It's a closed system. I just tried removing the user and permissions queries, but it's too much work.

data: "Betrag", render: $.fn.dataTable.render.number( null, null, 2, '€' )

shows a comma in the table but the export does not work.

data: "Betrag", render: $.fn.dataTable.render.number( ',', '.', 2, '€' )

shows a point in the table but the export works.

data: "Betrag", render: $.fn.dataTable.render.number( '.', ',', 2, '€' )

shows a comma in the table but the export does not work.

rf1234

Maybe I am missing something here but it should be clear that you can't export the values you render in your data table to Excel in case you are using German formatting.

In that case you must convert them when exporting them to excel. Alternatively you can change your data table rendering to English numbers as you did above in your second example. But your users might not appreciate that.

This is something from my own coding. Old but should still work. It converts a German formatted number that may even have text around it into an exportable number stripping off everything else.

Here is the button:

{   extend: "excel",
        exportOptions: {
            format: {
                body: function ( data, row, column, node ) {
                    if (typeof data === 'undefined') {
                        return;
                    }
                    if (data == null) {
                        return data;
                    }
                    if ( column === 2) { //assuming column two is your German formatted amount                        
                        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 '';
                        }
                        //result a number still as a string with decimal . and
                        //no thousand separators
                        //replace everything except numbers, decimal point and minus
                        return data.toString().replace( /[^\d.-]/g, "" );     
                    }
                    return data;
                }
            }
        }
    }

rf1234

In 1.12 I added the ability for it to automatically detect the user's locale and format the number accordingly. That then allows the wire format to be unformatted (xxxxx.xx for example).

That also allows the export to export numbers rather than strings.

@allan
Does that mean that data tables will automatically export the wire format mentioned above to Excel? That of course would be great, but I haven't seen anything like this yet. Would you have an example?

Rappi

@rf1234 Great solution! Many thanks!

allan

@rf1234 - Actually, thinking about it more, no it doesn't (by default) - apologies. I was forgetting how it works!

When exporting, by default Buttons will use the display data type - which in this case is a formatted number. What we want is for it to use the wire data (unformatted). That can be done with the orthogonal data options for export. Set it to be export and it then should automatically work (although the Excel format then won't automatically add any formatting itself - it would just be whatever Excel shows a plain number as).

Allan

rf1234

Sounds good, Allan. To be honest I've never looked into "orthogonal" data at all. The name is somewhat deterring for me...

I do all the date and amount rendering on the server depending on user language. So this won't work for me I guess. Maybe for the next project?!

Roland

allan

Yes - if the rendering is done server-side, then this approach wouldn't work. It would need the DataTable to have access to the unformatted data.

I like the orthogonal data stuff - it can got complex quickly though

Allan