Using Excel button customize() option to style text removes commas from comma separated data cells
Using Excel button customize() option to style text removes commas from comma separated data cells
Link to test case: https://jsfiddle.net/ynw36ftj/3/
Description of problem: Commas are removed from cells with comma separated data when formatting/styling text in the Excel export using the customize() option in the Excel button table initialisation config. For example, the first Classes value in the link above ("1,25,6") will be exported as "1256". We'd expect the data to be left as is without commas being stripped from the export.
To get around this we're using the exportOptions() button option method to format the data in that column with a replaceAll(',', ', '). We're using ', ' as the second replaceAll parameter because ',' does not work.
As a side note, the commas are left in if the numbers are separated with a space after the comma ("1, 25, 6") in the HTML, which is good to know but it would be good not to use replaceAll.
Answers
I don't quite understand your problem description but from your test case I can guess what the problem could be.
If you use comma separated values without spaces like this:
1,25,6 etc.
you can expect Excel to interpret them as numbers and part of those commas as decimal points or as thousand separators - depending on the regional settings of your MSOffice version. It is somewhat unpredictable I would say.
In your case the solution is very simple: Don't use comma separated values in your raw data! Render them as "comma+blank" separated values. Then you have no issues at all. e.g. 1, 25, 6
That will also improve legibility in your data table.
I use a German MS Office version:
This is what the German Excel does with your current data.
The German Excel obviously ignores all of your commas. Why? A comma in German is the same as a decimal point in English. Excel "thinks" your values are numbers, but multiple decimal points make no sense. Hence Excel ignores them as "crap". As you can see it even does this in cells that only contain one comma. Which is remarkable. If all of your values contained only one comma, Excel would keep the commas, I guess. Check with Microsoft if you want to fully understand their "disambiguation" algorithm of your local Excel version
This is what the German Excel does with "comma+blank" separated values:
Looks good because Excel interprets these as strings - and leaves them unchanged.
If you do the same with an English version of Excel it might look different in the first case. but it should be the same in the second case.