Export to Excel and format a numeric column as currency
Export to Excel and format a numeric column as currency
davidmenesesv
Posts: 2Questions: 2Answers: 0
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
This discussion has been closed.
Answers
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
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.
Hi @innerurge1
thank you for informing,
Which format should I use as 5,3316 in Turkish lira format?