Buttons html5 excel or printing numbers formatted problema

jvcunha

When exporting to excel or printing, columns containing numbers formatted as: 1.250,30 do not align to the right (the columns in excel are as string).
Previously I used tabletools and this problem did not occur.
Is there a way to pass to excel or to the impression that that formatted value is a number and not a string?
http://imgur.com/a/7OCwG
http://imgur.com/a/yFq5s
http://imgur.com/a/LdR2I

PS: send link in PM Allan

        var tableRbs = $('[id$=tblRBS]').DataTable({
            sDom: '<B>t<i>',
            order: [[2, 'asc']],
            pagingType: 'full_numbers',
            lengthMenu: [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, 'Todos']],
            displayLength: -1,
            searchHighlight: true,
            jQueryUI: true,
            orderClasses: true,
            bDeferRender: true,
            scrollY: '30vh',
            scrollCollapse: true,
            bDestroy: true,
            select: {
                style: 'multi'
            },
            buttons: [
                $.extend(true, {}, buttonCommon, {
                    extend: 'excelHtml5',
                    text: 'Excel',
                    titleAttr: 'Exportar para Excel',
                    title: 'Teste',
                    exportOptions: { columns: ':visible' }
                }),
                {
                    extend: 'print',
                    text: 'Imprimir',
                    titleAttr: 'Imprimir',
                    message: '  Teste de impressão ',
                    exportOptions: {
                        columns: ':visible',
                        orthogonal: 'export'
                    },
                    customize: function (win) {
                        var w = $(win.document.body);
 
                        w.css('font-size', '8pt');
 
                        w.find('table')
                            .addClass('compact')
                            .css('font-size', '8pt');
 
                        w.find('h1')
                            .html('');
 
                        w.find('th:nth-child(1)').addClass('text-right');
                        w.find('th:nth-child(2)').addClass('text-right');
                    }
                }
            ],
            columns: [
                { width: '60%' },
                { width: '20%' },
                { width: '20%' }
            ],
            columnDefs: [
                { type: 'formatted-num', targets: [1, 2], sClass: "text-right", mRender: function (data) { return formatNumber(parseFloat(data).toFixed(2), '.', ',', '.'); } }
            ]
        });
 
 
function formatNumber(nStr, inD, outD, sep) {
    nStr += "";
    var dpos = nStr.indexOf(inD);
    var nStrEnd = "";
    if (dpos != -1) {
        nStrEnd = outD + nStr.substring(dpos + 1, nStr.length);
        nStr = nStr.substring(0, dpos);
    }
    var rgx = /(\d+)(\d{3})/;
    while (rgx.test(nStr)) {
        nStr = nStr.replace(rgx, "$1" + sep + "$2");
    }
    if (nStrEnd == "") nStrEnd = ",00";
    if (nStrEnd.length < 3) nStrEnd += "0";
    return nStr + nStrEnd;
}

allan

Currently no - sorry. Numbers which are formatted as 1,234.00 would correctly be detected as numbers (at least with the nightly version), but using the thousand and decimal separator the other way around currently won't detect it as a number I'm afraid. That is an internationalisation limitation that I am aware of an plan to fix in future.

Allan

jvcunha

Hi Allan,
thanks for reply.
I do not know how I'm going to get around this. 80% of the datatables I use in the system have values in this format (PT-BR)

allan

I think there are a couple of options:

1. Just use it as is with string output.
2. Modify Buttons to match the formatting you are using. This is where to start. Also look through the code to see where that variable is being used to see how Buttons is creating the Excel XML.
3. Wait until I have a chance to fix it (the priority support options could be used to cover this work).

Allan

jvcunha

thanks Allan