Can't format last column as currency

pedrocsa

Hey guys!

There are 2 days that I'm trying to format the last column of my datatable as currency, with no success. I have already looked a bunch of articles, try a million solutions, but nothing had worked.

The picture of my datatable is attached to this message.

The code that I'm using is here:

<script> 
    $(document).ready(function(){
        $('.dataTables-example').DataTable({
            dom: '<"html5buttons"B>lTfgitp',
            columnDefs: [
                { type: "num-fmt", symbols:"R$" , targets: 4 }
            ],
            buttons: [
                { extend: 'copy'},
                {extend: 'csv'},
                {extend: 'excel', title: 'ExampleFile'},
                {extend: 'pdf', title: 'ExampleFile'},
                {extend: 'print',
                 customize: function (win){
                    $(win.document.body).addClass('white-bg');
                    $(win.document.body).css('font-size', '10px');
                    $(win.document.body).find('table')
                        .addClass('compact')
                        .css('font-size', 'inherit');
                    }
                }
            ]

        });

    });
</script>

Here is the debbuger of my datatable: http://debug.datatables.net/ilasew

In addition, I added the following cdn to my project:

<link href="//cdn.datatables.net/plug-ins/1.10.15/sorting/currency.js" rel="stylesheet">

What I'm doing wrong?

Please, someone can help me?

pedrocsa

No help? Anybody?

allan

You need to use the built in Number renderer. The documentation for it is available here.

Remove the columns.type option - you don't need to set that yourself. But you do need a renderer.

Allan

pedrocsa

allan, I changed my function the way that you told, but it still not working, what am I doing wrong now?

<script>
    $(document).ready(function(){
        $('.dataTables-example').DataTable({
            dom: '<"html5buttons"B>lTfgitp',
            columnDefs: [
                {
                    data: 'Valor',
                    render: $.fn.dataTable.render.number( ',', '.', 2, 'R$' )
                }
            ],
            buttons: [
                { extend: 'copy'},
                {extend: 'csv'},
                {extend: 'excel', title: 'ExampleFile'},
                {extend: 'pdf', title: 'ExampleFile'},
                {extend: 'print',
                 customize: function (win){
                    $(win.document.body).addClass('white-bg');
                    $(win.document.body).css('font-size', '10px');
                    $(win.document.body).find('table')
                        .addClass('compact')
                        .css('font-size', 'inherit');
                    }
                }
            ]

        });

    });
</script>

allan

That looks like it should work. Could you give me a link to a page showing the issue so I can help to debug it please?

Thanks,
Allan

pedrocsa

Allan,

You can check it on http://cursosites-com-br.stackstaging.com/login.php make the login with the following:

Matricula: 471910
Password: 111111

This will take you straight to the table!

Thank you so much!

Pedro

allan

Got it - thanks. You need to add targets: -1 to the object you are using in the columnDefs array.

I'm slightly surprised DataTables doesn't give a warning about that. I thought it would there - I'll look into that!

Allan

pedrocsa

Allan,

Almost done! Now it shows the currency but it is rounding the cents to 00.

You can check it on the same website.

By the way, what is the "targets" function and why "-1"?

Thanks!

tangerine

https://datatables.net/reference/option/columnDefs.targets

pedrocsa

Thanks for the explanation tangerine!

But I'm still having the problem that my numbers are being rounded. The cents are always "00". And that's not what is happen in database.

Any help?

allan

Yes, I see the issue. Its because a comma is used a the decimal place in the original HTML - e.g.: 14,4. If you have it as a dot (14.4) it would be correctly displayed.

The reason for this is that Javascript will see the dot as a decimal character, but it won't see the comma as one (e.g. parseFloat('14,4') === 14).

Are you able to change the HTML? If not, then you'd need a custom formatter that can handle the comma decimal place.

Allan