Error on Excel-Export on render.number
Error on Excel-Export on render.number
![Rappi](https://secure.gravatar.com/avatar/a766342e452633ef067cdb953aee7d80/?default=https%3A%2F%2Fvanillicon.com%2Fa766342e452633ef067cdb953aee7d80_200.png&rating=g&size=120)
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
This question has an accepted answers - jump to answer
Answers
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.
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!
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
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?
Sounds to me like your browser might be detecting your locale as en-US. Try this in your browser's console:
what does it show?
Allan
Hi Allan,
it show '100.000,1'
It's Chrome on Windows 11 in german.
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
Sorry. It's a closed system. I just tried removing the user and permissions queries, but it's too much work.
shows a comma in the table but the export does not work.
shows a point in the table but the export works.
shows a comma in the table but the export does not work.
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:
@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?
@rf1234 Great solution! Many thanks!
@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 beexport
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
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
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![:) :)](https://datatables.net/forums/resources/emoji/smile.png)
Allan