Excel export and comma decimal

zajc

We are using commas for decimal. If we export numbers with commas, for example 1,59 the number exports as 159.

The example works but it also format columns with strings. How can we format only columns with numbers?

{
            extend: 'excel',
            exportOptions: {
                columns: ':visible',
                format: {
                    body: function(data, row, column, node) {
                        return data.replace(',', '.');
                    }

                }
            }
        }

allan

You would currently need to modify the source. There is no option for Buttons to export an Excel document with numbers formatted as commas. It is something I plan to look at in future.

Allan

sm1l3y

I am having a similar problem with ONLY the export to excel option. The footer is not retaining comma formatting and some of the body loses decimal places.

zajc

This workaround works.

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

1.) Add additional <p> HTML tag to data to avoid strip errors and then strip all the HTML tags
2.) Check if the text is number. If the text is number, replace the comma with the dot, if not, leave it as the text

sm1l3y

I found a work around that works perfect for us, simply change it from excel to csv and all commas, decimals, and percents are retained. If you want the button to still say excel just do as below...

{
extend: 'csv',
footer: true,
text: 'Excel',
},

rike

Note that the "format" option of https://datatables.net/forums/profile/123909/zajc needs to go into the "exportOptions" part and then it works Thanks for posting this workaround

svenos

We had the same issue.

Our data gets correctly displayed in DataTables ("1,59") but after the excel export the comma got stripped ("159"). Funny thing is that it only happened to some columns.

The solution from zajc worked perfectly and we had to change nothing else (neither the file format nor the DataTables Code itself).

=> Just replace "excel" (in your button definition) with the code from zajc

Massimo74

I have a problem whit zajc function

if I have this status
export result is Good

if I have this status

export result is Error

colin

@Massimo74 We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

rf1234

Hi Massimo,

that is an Excel-problem. If you export: 9.520,00 Excel interprets the first period to be a thousand separator because there is a comma following it which cannot happen in English numbers which may contain commas like in 9,520.00 but a comma can NEVER follow a period.
If you export 9.520 Excel has no chance to detect that you mean it to be a thousand separator.

So you need to help Excel by making it all very explicit ...

This is an example that modifies the exported values to work in German and English. The German numbering is the same as the Italian - it'll work for you too.
In this code I get rid of all the thousand separators because Excel puts them back in anyway if the field is formatted the right way.
So if the user language is English and I get 9,520.00 I return 9520.00.
If the user language is German and I get 9.520,00 I return 9520.00 as well.
I also have percentage treatment in there because Excel doesn't want 10% but 0.1 ... if it is a percentage field in Excel.

exportOptions: {    
    format: {
        body: function ( data, row, column, node ) {
            if (typeof data !== 'undefined') {
                if (data != null) {  
                    if (column === 3 || column === 4) { //col with number or percentage
                        if (lang == 'de') { //this time we use the English formatting
                            //data contain only one comma we need to split there
                            var arr = data.split(',');
                            //subsequently replace all the periods with spaces
                            arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                            //join the pieces together with a period if not empty
                            if (arr[0] > ''  || arr[1] > '') {
                                data = arr[0] + '.' + arr[1];
                            } else {
                                return '';
                            }
                        } else {
                            data = data.toString().replace( /[\,]/g, "" );
                        }
                        //result a number still as a string with decimal . and
                        //no thousand separators
                        //replace everything except numbers, decimal point and minus
                        data = data.toString().replace( /[^\d.-]/g, "" ); 
                        //percent must be adjusted to fraction to work ok
                        if (column == 4) { //column with percent
                            if (data !== '') {
                                data = data / 100;
                            }
                        }
                        return data;                                
                    }
                }
            }
            return data;
        },

rf1234

Now you might have the question: How do I format the Excel fields the right way without using a template?

Well here is another link on that: https://datatables.net/forums/discussion/comment/164929

What I forgot to mention:
For the Excel export everything must be formatted as if your language were English. Depending on the client version of Excel that you use on your computer the numbers will be displayed in your local style.
For the csv-Export it is opposite: You must export everything in the target language. So you need to anticipate which local Excel version the user will use because the field separator for English csv-files is a comma which you can't use in Italian or German because you'll kill all numbers with decimal commas. So at least in German you need to export ";" as field separator. The same applies to number formatting. If you don't do this the user will see everything in the wrong local style in Excel when opening a csv-file.
So there are two completely different approaches for Excel and csv exports.