Export to Excel and format a numeric column as currency

davidmenesesv

I'm trying to export numeric data to Excel. The numeric formatting is as follows:

• Thousands grouping separator: "."
• Decimal point indicator: ","
• Number of decimal points to show: "0"
• Prefix: "$"

And the header and footer texts are formatted as bold.

This is the table:

<table id="idtablainforme_ventaporfamilia" class="table table-striped table-striped table-bordered nowrap dataTable">
    <thead>
        <tr>
            <th>
                <b>FAMILIA</b>
            </th>
            <th>
                <b>VENTA</b>
            </th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th><b>TOTAL</b></th>
            <th><b>$12.925.150</b></th>
        </tr>
    </tfoot>
    <tbody>
        <tr><td>CHEQUERA MUJER</td><td>$5.231.760</td></tr>
        <tr><td>CARTERA</td><td>$3.487.630</td></tr>
        <tr><td>BILLETERA MUJER</td><td>$2.155.120</td></tr>
        <tr><td>NECESER</td><td>$21.980</td></tr>
        <tr><td>COSMETIQUERA</td><td>$10.990</td></tr>
        <tr><td>SET DE VIAJE</td><td>$10.990</td></tr>
    </tbody>
</table>

This is my try:

<script>
    $('#idtablainforme_ventaporfamilia').DataTable({
        destroy: true,
        "searching": false,
        "paging": false,
        "ordering": false,
        "info": false,
        "autowidth": false,
        columns: [
           { data: "1", render: $.fn.dataTable.render.text() },
           { data: "2", render: $.fn.dataTable.render.number('.', ',', 0, '$') }
        ],
        footerCallback: function (tfoot, data, start, end, display) {
            var $th = $(tfoot).find('th').eq(1);
            $th.text($.fn.dataTable.render.number('.', ',', 0, '$').display($th.text()))
        },
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excel',
                footer: true,
                title: 'INFORME DE VENTAS POR FAMILIA',
                text: '<i class="fa fa-file-excel-o"></i>',
                titleAttr: 'Exporta a EXCEL',
            }
        ]
    });
</script>

But it doesn't convert the numeric data, and the header and footer texts are not formatted as bold. They are exported in Excel as string, except for the values 21.98 $, 10.99 $ and 10.99 $ (although they should be $21.980, $10.990 and $10.990) as follows:

FAMILIA         VENTA
CHEQUERA MUJER  $5.494.310
CARTERA         $5.231.760
BILLETERA MUJER $2.155.120
NECESER         21.98  $
COSMETIQUERA    10.99  $
SET DE VIAJE    10.99  $
TOTAL           $12.925.150

innerurge1

The datatables buttons.html5.js file has a regex that matches $ amounts and applies a dollar format to them (which is unfortunately after the number, but that's a different topic). That regex doesn't look for dot as a thousand separator or comma for the decimal, it looks for the opposite. So you'll either have to manually apply the $ formating to the excel sheet using the documentation here https://datatables.net/reference/button/excelHtml5

and that would something like

buttons: [
    {
     extend: 'excelHtml5',
     customize: function( xlsx, row ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
             $('row c[r^="G"], row c[r^="H"]', sheet).attr( 's', 57);
     }
}]

where H would be the column you wanted to be $ and 57 is the style number for $ formatting.

If you wanted to extend things further, there is some really good info here:

https://www.datatables.net/forums/discussion/comment/121231/

But both only help on a table by table basis. If you want something to always catch your different format of comma as a decimal and dot as a thousand separator you might want to edit the buttons.html5.js (not ideal, I know, but it works and it's what I'm leaning toward after a week of searching). For more details about that I have a stackoverflow answer out there that might help with that:

https://stackoverflow.com/questions/47675528/datatables-export-to-excel-and-format-a-numeric-column-as-currency/48571827#48571827

It specifically speaks to putting the $ on the left, but the method works for any new style you want to create.

Hope this helps.

ibrahimayhan

Hi @innerurge1
thank you for informing,
Which format should I use as 5,3316 in Turkish lira format?