Excel export removes comma from German decimal number

K3nguruh

Hi everyone,

I'm not quite sure how to best describe this issue, but I'll give it a try!

I wrote a custom renderer function for formatting German numbers because DataTable.render.number() doesn't yield the result I need.

For example: DataTable.render.number(null, null, 2) displays 100,00 correctly in the table itself, but when exported (PDF, Excel), it outputs 100—truncating the decimal places completely. However, I need the exported output to match what's visible in the table.

My custom renderer works fine for PDF exports. But for some reason, during the Excel export, the comma separator gets removed, resulting in 10000 instead of 100,00.

I logged the exported cell data to the console to inspect the values. Interestingly, instead of returning a string, the cell value arrives as a numeric type. Meanwhile, in the "Betrag" (Amount) column, everything works as expected.

I even tried appending a space to the formatted string to force string evaluation, but that didn't resolve the issue either.

allan

Can you show me how you are loading data into the DataTable (e.g. if by Ajax, an example of that data), or if by HTML, then a sample of that. And also the DataTable configuration please? A comma decimal place should work.

But for some reason, during the Excel export, the comma separator gets removed, resulting in 10000 instead of 100,00.

Because it is being exported as a number, not a string. But moreover, it isn't applying a formatter in the XSLX file, to have it styled as a comma decimal. How to do that is something that I'd need to research - I can't remember off the top of my head!

Allan

K3nguruh

Hi Allan,

I've stripped out several settings from language and buttons, as well as a few other non-essential configurations, to keep the test case focused on the issue.

Example:
https://live.datatables.net/jinozeva/1/edit?js,console,output

K3nguruh

Hi Allan,

Quick update: It looks like the live example wasn't saved properly earlier. Here is the updated link:

Example:
https://live.datatables.net/jinozeva/4/edit?js,console,output

rf1234

The truth is that it is a lot more complicated than you think. The trick is to transform the export values to US numeric formats in the export options. According to your regional Excel settings this will be transformed to German or other regional formats by Excel automatically. If you use csv-export as well it has to be opposite: you need to format the exported data according to the target regional Excel installation. E.g. the separator in German won't be a comma, but ";" etc.

You are using fairly inconsistent input data:

In the first record you have consistent American numbers. In the second record you don't. 100, doesn't make a lot of sense.
This is what my German Excel installation makes of it:

rf1234

Ok, I need to look at this:

The German thousand separators are ignored by Excel because they don't make any sense in American numbers: Just too many decimals! Hence Excel keeps "Ganzzahl" as it is. Probably it recognizes it as a string, not a number.

23,12 doesn't make a lot of sense either in the US: Hence Excel ignores the comma - and the German Excel installation adds the thousand separator!

Same applies to row 2: 42 is easy; 100,00 makes no sense in the US: A thousand separator after 100 is meaningless. It is being ignored.

The "Betrag" columns with the substring "EUR" in it are interpreted as strings anyway. Hence they are exported as is.

To be sure: This all about how Excel works, not about Data Tables! Data Tables just exports the values as is. You will need to modify them according to your regional requirements.

rf1234

Here is an old post. I tried to help Massimo from Italy who had a similar issue - like all continental Europeans

https://datatables.net/forums/discussion/comment/164990/#Comment_164990

rf1234

@allan
You could add something to the Excel export. Just a setting for the interpretation of European number and percentage formats. Could be just one variable to set, the default being: "English" and "Continental" as the second option. To filter out the "numeric" columns I would simply go for all columns that don't contain anything else but numbers, commas and periods. For percentage columns this would still need to be modified a little...