Incorrect number format when exporting to Excel

artyco

I am using the button scripts and the functionality to export the table to excel. Everything works perfect, except the way it interprets the numerical data.

I have a value in the table, for example, 1,310 (one thousand three hundred and ten) but Excel exports 1.31 (one comma thirty-one). In the table I show data with the point as a thousands separator but in Excel it arrives as a decimal point.

I have reviewed the documentation, especially this one:
https://datatables.net/reference/button/excelHtml5

Alli says about the built-in styles:
" 63 - Numbers with thousand separators (automatically detected and used by buttons - since 1.2.3)
64 - Numbers with thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.3)
65 - Numbers without thousand separators (automatically detected and used by buttons - since 1.2.4)
66 - Numbers without thousand separators - 2 decimal places (automatically detected and used by buttons - since 1.2.4)"

I have updated all the scripts in my project, including Buttons for DataTables 2.3.3 (I think the latest one); still the problem has not been solved.

Any idea how to fix it?. I need to use the point in the table to separate thousands, and that when going to Excel it ignores that point, or at least not to confuse it with a decimal point.

Thank you very much. Cheers

allan

Can you give me a link to a test case showing the issue and I'll take a look into it.

Thanks,
Allan

rf1234

I have a value in the table, for example, 1,310 (one thousand three hundred and ten) but Excel exports 1.31 (one comma thirty-one).

Excel has some built-in logic to disambiguate imported number formats. And it goes like this ( sort of ):
- if you just have one separator in your number (comma or period) it is interpreted to be a decimal separator (i.e. if your Excel version is English it is going to be a decimal point, if your Excel version is German for example it is going to be a decimal comma).
- If you have two separators in your number and the number of digits between those separators is kind of "ok" the first separator is interpreted to be a thousand separator and the last one is interpreted to be the decimal separator.

The above is my "guesstimate" of what Excel is actually doing. The only way to be sure you get the export right is to get rid of all thousand separators and to export the number this way: 9999 or this way 9999.99 using a decimal point. Excel will always understand that.

Use "exportOptions" just like in this thread to get rid of your thousand separator when exporting. If you want to have your target Excel column formatted in a certain way then you would need to use "customize" as well.

https://datatables.net/forums/discussion/comment/213908#Comment_213908

artyco

Thank you very much rf1234 for the solution. I used this example: https://datatables.net/extensions/buttons/examples/html5/outputFormat-orthogonal.html

Greetings

rf1234

Thanks artyco. Now I learned something too Orthogonal data make it simpler to use exportOptions. Wasn't aware of that because I have never really looked into orthogonal data. I think the name "orthogonal data" is a bit deterring ...

Roland

rf1234

@allan
Looking at the documentation of orthogonal data here:
https://datatables.net/manual/data/orthogonal-data
and the example here:
https://datatables.net/extensions/buttons/examples/html5/outputFormat-orthogonal.html
I don't see that "export" is descriebed in the documentation. Is there a complete list of all orthogonal data types somewhere?

allan

Its entirely custom. You can use any token you want to trigger different actions in your data functions. DataTables uses the four described here and I'm thinking that a fifth called export should be perhaps added by default.

Allan

rf1234

Thanks Allan and sorry: I didn't get that "export" is defined in "columns.render" ...

Roland