Wrong format excel exporting

arranzropablo

Hello I'm trying to export to excel but the format for thousand separator is not behaving correctly... When it's a number under 1.000.000 it writes "," as separator, when it's higher than 1.000.000, it writes "." my purpose is having "." always as the thousand separator. This is my code in case it helps... Thank you!

`
this.initTable = function(tableId, options, filters) {
var filterEvents;

if (appname.tableGeneratorFilters && filters) {
  filterEvents = appname.tableGeneratorFilters.init(filters);
}

var defaultOptions = {
  dom: 'ri<"detail-actions"><"table-actions">Bf<"filters"><"live-filters">tlp',
  buttons: [
    {
        extend: 'csv',
        charset: 'UTF-16LE',
        fieldSeparator: ';',
        bom: true
    },
    {
        extend: 'excel',
      exportOptions: {
        columns: ':visible',
        format: {
          body: function(data, row, column, node) {
            var value = $('<span>' + data + '</span>').text();

            return value;
          }
        }
      },
        filename: function(){
            var d = new Date();
            // TODO ... dd/mm/yyyy hh:mm:ss.
            return document.title + ' ' +d.toISOString();
        }
    }
  ],

  scrollX: true,
  columnDefs: [
    {
      orderable: false ,
      targets: "no-sort"
    },
    {
      width: "1%",
      targets: 0 }
  ],
  order: [[ 1, 'asc' ]],
  language: {
    search: "_INPUT_",
    searchPlaceholder: '',
    url: ctx + "/js-i18n/tables-i18n.json"
  }
};
var opts = $.extend({}, defaultOptions, options);

var table = $(tableId).DataTable(opts);` 

arranzropablo

PD: This is how I have it now, I tried adding language.thousands and didn't work... with this code is currently printing:
-for "656.019" it's printing "656,019" (wrong)
-for "58.323.791" it's printing "58.323.791" (right)

rf1234

you need to use customization with the build-in style 63 for a number with thousand separators.
https://datatables.net/reference/button/excelHtml5

Like this for example if you would like an array of columns to be formatted like that:

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var thousandSepCols = ['F', 'I', 'J', 'K', 'L', 'M', 'N'];            
for ( i=0; i < thousandSepCols.length; i++ ) {
    $('row c[r^='+thousandSepCols[i]+']', sheet).attr( 's', '63' );
}

Excel will then decide which thousand separator to use. In an English excel installation it will use a comma, in a German or Spanish installation it will use a period. So if you do the Excel export you don't preformat the number yourself but all you do is tell Excel to use a thousand separator. If you did the same for csv it would be opposite: You would need to anticipate the language and hence the Excel installation of your user and preformat everything yourself. For example for English users the value separator would be a comma for German users it would need to be a ";" etc. etc. Same applies to number formatting: For English users 1,000,000.00 for German and Spanish users 1.000.000,00

allan

Yes - I'm afraid that at the moment, the Excel export only supports the British / American style of formatting numbers, not the European style.

That is something that I plan to address in a future version.

Allan

rf1234

@allan: I tend to disagree - the Excel export is better than you think. The only thing @arranzropablo needs to change is to use built-in style 63 to explicitly tell Excel that the field has no decimals but thousand separators instead.

I am pretty sure @arranzropablo is using a Spanish Excel installation. Hence Excel interprets "656.019" as a decimal number (only one separator!) and converts into Spanish "656,019". Since Excel "knows" that a number can only have one decimal sign it interprets the periods in "58.323.791" to be thousand separators and displays the number correctly in the Spanish installation. If @arranzropablo were using an English Excel installation it would be exactly opposite. The numbers would be displayed in Excel as "656.019" and "58,323,791".

I tested this with a US and a German Excel installation.

rf1234

I use this as a customization myself:

var twoDecPlacesCols = ['F', 'I', 'J', 'K', 'L', 'M', 'N'];            
            for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
            }

If you set my website to English numbers are passed to Excel as 1,000,000.00. Depending on what Excel installation you are using Excel displays them as 1,000,000.00 (English or US installation) or 1.000.000,00 (German installation).

Same applies if you set the site to German and 1.000.000,00 is passed to Excel. This does not matter at all. All that matters is the language setting in Excel - provided you use the built-in style.

allan

Sorry I wasn't clear. Yes you are absolutely correct, Excel is excellent at internationalisation. What I was meaning was that Button's auto detection of the export will correctly detect US formatted numbers and then setup the spreadsheet for them, but not Euro formatted numbers.

DataTables does have a language.thousands and language.decimal option which will be used in future versions of Buttons to automatically support Euro styled numbers.

This is the part of the Buttons code that does its type detection on output.

But yes, if you have a plain number, use Excel's built in styling options.

Allan

arranzropablo

Thank you so much @rf1234 for your answer, but I'm afraid I can't apply that solution to the code I pasted... As I understand it, my code just tells the library to create two buttons with certain properties, but I can't obtain the var sheet at any moment as you do, to apply the .attr('s','63')... if you could tell me where I should fit that, it would be a great help, thank you!

arranzropablo

sry I posted this accidentally

arranzropablo

@allan so language.thousands and language.decimal are not working already? I'm still confused about how should I send my numbers... plain? "," separator? "." separator?

arranzropablo

nevermind guys! I made it, thank you so much to both!

rf1234

great you got it working! and also great that you will enhance auto detection, Allan.
Unfortunately my post was wrong. I overlooked that I use export options to strip everything out of the numbers passed to Excel. So I always pass 1000000.00 and not 1,000,000.00 or 1.000.000,00. This is the complete code of my custom Excel button if it helps at all. Everything I do for lang === 'de' would be the same for lang === 'es'.

The code has a couple of new styles because if you want to combine built-in styles with each other you need to define a proprietary style yourself. Combining by using two built-in styles does not work because one style overwrites the other one.

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
    customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at "buttons.html5.js", search for "xl/styles.xml"
//styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
//styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
//styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
//styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
//styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
//styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
//on the last line we have the 67 currently built in styles (0 - 66), see link above

        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;
//n1, n2 ... are number formats; s1, s2, ... are styles
        var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
        var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
        var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';
//s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
//just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
        var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
        var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center" wrapText="1"/></xf>'
        sSh.childNodes[0].childNodes[0].innerHTML += n1;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;

        var fourDecPlaces    = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
        var twoDecPlacesBold = lastXfIndex + 3;
        var greyBoldWrapText = lastXfIndex + 4;

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
    //create array of all columns (0 - N)
        var cols = $('col', sheet);
    //set lenght of some columns: col number: length (excl. first column)
        var colsLength = ['01:18', '02:12', '03:12', '04:12', '05:16', '06:10',
                          '07:12', '08:16', '09:16', '10:16', '11:16', '12:16',
                          '13:16', '14:16', '15:16'];
        for ( i=0; i < colsLength.length; i++ ) {
            $( cols [ parseInt( colsLength[i].substring(0,2) ) ] )
                    .attr('width', parseInt( colsLength[i].substring(3) ) );                
        }

//two decimal places columns          
        var twoDecPlacesCols = ['F', 'I', 'J', 'K', 'L', 'M', 'N'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="G"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
        $('row:last c', sheet).attr( 's', '2' );  //bold
//row with totals   
        var boldCols = ['A', 'C', 'D', 'E'];         
        for ( i=0; i < boldCols.length; i++ ) {
            $('row:eq(-2) c[r^='+boldCols[i]+']', sheet).attr( 's', '2' );  //bold
        }
//move text from column B to column A and empty columns B through E
        var copyPaste = $('row:eq(-2) c[r^="B"] t', sheet).text();
        $('row:eq(-2) c[r^="A"] t', sheet).text(copyPaste);
        var emptyCellCols = ['B', 'C', 'D', 'E'];
        for ( i=0; i < emptyCellCols.length; i++ ) {
            $('row:eq(-2) c[r^='+emptyCellCols[i]+']', sheet).text('');
        }

        var twoDecPlacesBoldCols = ['I', 'J', 'K', 'M', 'N', 'O', 'P'];   
        for ( i=0; i < twoDecPlacesBoldCols.length; i++ ) {
            $('row:eq(-2) c[r^='+twoDecPlacesBoldCols[i]+']', sheet).attr( 's', twoDecPlacesBold );
        }
    },
    exportOptions: {
//            columns: ':visible',
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {  
                        if ( column > 15 ) {
                            return '';  //get rid of the changed manually column
                        }
                        if (column === 5 || column === 6 || column > 7) {
                            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 == 6) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {
                        if ( column > 15 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};