Number detection in excelHTML5

serio72

Hi guys,

I live in Italy and as you know here we use dot as thousand separator and comma for decimals.
Exporting datatables using excelHTML5 is making me crazy for number less than 1 thousand.

There is a way to disable number detection in button script? It may be usefull for me to have numbers as strings

Many thanks
Sergio

allan

Very good point.

I'm afraid that at the moment there is no way to disable externally. You'd need to modify Buttons a little bit - make this array empty and it won't do any special formatting.

Another option would be to use orthogonal export data and have the data requested for export return with a dot for the decimal place.

This is something I will look into improving. Thanks for pointing it out.

Allan

rf1234

Sergio,
same in that other country north of the alps ... This is my solution for Excel depending on the language. The key is that you format the numbers the English way without 1,000 separators and with decimal point. Then let the local Excel installation decide which way to format it for the end user. You would also tell Excel which columns to format with 1,000 separators. Then it works regardless of the local Excel installation. The code below also removes other text from the column, but requires to know which way the input data is formatted, either the Italian or the English way (but it doesn't matter whether input data has 1,000 separator or not).

For example if your input column is set to: 1.000,99 Euro and the language is Italian ('it') the result is 1000.99 which is subsequently formatted in Excel as
a) English Excel installation: 1,000.99 (or 1000.99 if you don't set the column attr below)
b) Italian Excel installation: 1.000,99 (or 1000,99 without attr( 's', '64' );)

This also works for numbers less than 1,000. In that case Excle would receive a value of 999.99 for example and format this as 999,99 (Italian) and 999.99 (English).

$.fn.dataTable.ext.buttons.YourExcelButton = {
    extend: 'excel',
    customize: function( xlsx ) {

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
//list of columns that should get a 1000 separator depending on local Excel installation
        $('row c[r^="F"]', sheet).attr( 's', '64' );
        $('row c[r^="G"]', sheet).attr( 's', '64' );
    },
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data !== null) {
            //whichever your numeric columns are:
                        if (column === 5 || column === 6 || column > 7) {
                            if (lang == 'it') { //use English number formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods (1.000 separators) with spaces
                                arr[0] = arr[0].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 { //number in English format; get rid of 1,000 separators too
                                data = data.replace( /[\,]/g, "" );
                            }
                            //result a number, still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            //with spaces
                            data = data.replace( /[^\d.-]/g, "" );
                            return data;                                
                        }
                    }
                }
                return data;
            }
        }
    }
};

serio72

Many Thanks guys!
Tomorrow I'll try rf1234 solution

I'm trying a more complex solution: export datatable as csv and send it to a servlet. This one make report using jasperreports libraries and sends back to caller...

I know it seems I'm a little crazy, but I'm new in jquery world...

garaggarag

Hi,

I have the same problem. I try to give a contribute.
I found out that the output of the extend copyHtml5 pasted into Excel running with Italian settings is perfect. I hope that this could help allan to address the issue.
Regards,
Antonio