Retain decimal seperator on export excel in Datatables

Question

This is my values that I am getting in datatable.

Cuenta  Nombre Cuenta       Oct18   Dec18   Sep19   Oct19   M$      M%  A$      A%
11      Activos Liquidos    5732,12 6950,89 5879,21 5965,02 85,82   1.4 232,91  4.06

When exporting the excel the data's decimal place gets converted to 5732.12 6950.89 ..etc. I need to preserve the decimal I am using datatables.

I used this code:

{
      extend: 'excel',
      exportOptions: {
          columns: ':visible',
          format: {
              body: function(data, row, column, node) {
                  data = $('<p>' + data + '</p>').text();
                  return $.isNumeric(data.replace(',', '.')) ? data.replace(',', '.') : data;
              }
          }
      }
  }

But it changes my 4.06 to 4,06 which is incorrect. How to achieve this? Thank you.

Update :

This is the value I am getting in excel

isNumeric fails on the numbers with decimal comma. Then Excel will show whatever is in its locale setting. On my Excel it is decimal comma. What is the complete expected output? — mplungjan, Jun 10 '20 at 10:37
I have set the locale as Spanish but in excel it is getting converted to '.' — Amal, Jun 10 '20 at 10:41
Why is 4.06 wrong? I would have thought `573.212` was the wrong value — mplungjan, Jun 10 '20 at 10:51
I would consider this instead https://stackoverflow.com/questions/33159354/how-do-i-find-the-decimal-separator-for-current-locale-in-javascript — mplungjan, Jun 10 '20 at 10:53
@mplungjan to avoid strip errors and then strip all the HTML tags — Amal, Jun 10 '20 at 10:54
But YOU said that 4,06 is incorrect. 4,06 IS in my opinion correct and 573.212 is your problem — mplungjan, Jun 10 '20 at 11:06
Sorry it is same as the client is saying it is the correct value. — Amal, Jun 10 '20 at 11:06
I just retain the same values in datatable in excel as well. — Amal, Jun 10 '20 at 11:09

score 1 · Accepted Answer · answered Jun 10 '20 at 11:15

1

Does this help?

const fields = "11\tActivos Liquidos\t5732,12\t6950,89\t5879,21\t5965,02\t85,82\t1.4\t232,91\t4.06".trim().split("\t")
  .map(fld => /^\d/.test(fld) ? parseFloat(fld.replace(/,/,".")) : fld);
console.log(JSON.stringify(fields))

answered Jun 10 '20 at 11:15

mplungjan

169,008
28
173
236

Where should I paste this ? – Amal Jun 11 '20 at 13:04
You need to add `.map(fld => /^\d/.test(fld) ? parseFloat(fld.replace(/,/,".")) : fld);` to the array that holds the values. How you get the array is dependent on the strings. I guessed tab delimited – mplungjan Jun 11 '20 at 13:11

Retain decimal seperator on export excel in Datatables

1 Answers1