Excel export and comma decimal

Excel export and comma decimal

zajczajc Posts: 67Questions: 10Answers: 2

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(',', '.');
                    }

                }
            }
        }

Replies

  • allanallan Posts: 63,597Questions: 1Answers: 10,485 Site admin

    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

  • sm1l3ysm1l3y Posts: 24Questions: 7Answers: 0

    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.

  • zajczajc Posts: 67Questions: 10Answers: 2
    edited February 2017

    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

  • sm1l3ysm1l3y Posts: 24Questions: 7Answers: 0
    edited February 2017

    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',
    },

  • rikerike Posts: 3Questions: 0Answers: 0
    edited February 2017

    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 :)

  • svenossvenos Posts: 17Questions: 2Answers: 0

    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

  • Massimo74Massimo74 Posts: 85Questions: 1Answers: 0

    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

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    @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

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited January 2020

    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;
            },
    
  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421
    edited January 2020

    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.

This discussion has been closed.