0
I’m trying to convert a numerical column 8.5 in accounting currency (R$ 8,50) when I use export Excel of jQuery Datatable, but I was unsuccessful.
My spine is like this:
{
"data": "Valor2", "orderable": true, "render": function (data, type, full, meta) {
var valor = full.Valor.replace(',', '.');
return parseFloat(valor);;
}
},
0
For it to be recognized as currency you put to the parameter type with the value currency
{
"data": "Valor2", "orderable": true, "type": "currency"
},
This plug-in will provide numerical classification for coin columns (detected automatically with currency type detection plug-in or defined manually), taking into account the currency symbol ($ or £ by default).
Datatables 1.10+ has currency sorting skills embedded and will be detected automatically. As such, this plug-in is marked as obsolete, but can be useful when working with versions ancient Datatables.
Unfortunately it didn’t work for me.
Did you see this? a = (a==="-") ? 0 : a.replace( /[ d-.]/g, "" ); Return parseFloat( a ); in the documentation?
yes, I saw.... I did exactly as shown in the documentation, but in excel it continues as text
tried deleting the file to see if it is creating a new one?
yes, unfortunately I gave up datatable and decided to use a lib to generate excel, but thanks for the help
0
I know the post is old but as I have not seen any answer that I consider correct, here I leave my vision. From what I understand you want to take the format of "8.50" and go to "R$8,50", and then export the table to Excel. Let’s go by part then:
This type of procedure should be done using the property of the Datatable itself 'columnDefs' and not 'forcing' through an external rendering because otherwise vc will not be able for example to have the Data table do the sum of the values or export to excel or pdf with the correct mask. In your code look for the property 'columnDefs', if you have not created can create, it will be like this
-- ...codigo anterior do DataTable---
pageLength: 50,
responsive:true,
data: dataSet,
columnDefs: [
{ "width": "7%", "targets": 0 }, //defini manualmente o tamanho da coluna
{ "width": "5%", "targets": 1 }, //defini manualmente o tamanho da coluna
{ "width": "5%", "targets": 2 }, //defini manualmente o tamanho da coluna
{ "width": "7%", "targets": 3 }, //defini manualmente o tamanho da coluna
{ "width": "7%", "targets": 4 }, //defini manualmente o tamanho da coluna
{"render": function(data){ //responsavel por 'renderizar' a coluna que vc deseja aplicando o filtro R$
return parseFloat(data).toLocaleString('pt-br',{style: 'currency', currency: 'BRL'});
},"targets": 4
}
],
columns: [
{ title: "Tipo" },
{ title: "NºDoc" },
{ title: "Pedido" },
{ title: "Emissão" },
{ title: "Valor" }
]
--.... resto do código do seu DataTable ....--
Note that the part responsible for applying the filter is inside the property 'columnDefs', 'render'.
{"render": function(data){ //responsavel por 'renderizar' a coluna que vc deseja aplicando o filtro R$
return parseFloat(data).toLocaleString('pt-br',{style: 'currency', currency: 'BRL'});
},"targets": 4
}
Here you correctly apply the mask and use the 'targets' command to point to which column you want to be applying
*BS:*If you wanted to apply this formatting to multiple columns would you just put the columns q you want in array, like this
{"render": function(data){ //responsavel por 'renderizar' a coluna que vc deseja aplicando o filtro R$
return parseFloat(data).toLocaleString('pt-br',{style: 'currency', currency: 'BRL'});
},"targets": [4,5,8]
}
Remembering q the first column vc counts 0, because we are working with array here
I don’t know how the export button was made... but I do it this way:
--- ... código do dataTable ...--
columns: [
{ title: "Tipo" },
{ title: "NºDoc" },
{ title: "Pedido" },
{ title: "Emissão" },
{ title: "Valor" }
],
dom: 'Bfrtip',
altEditor: true,
buttons: [
{
text: 'EXCEL',
extend: 'excel',
exportOptions: {
modifier: {
page: 'current'
}
}
},
]
-- ...resto do código do DataTable...---
And last but not least, remember that you must import the relevant libraries if you do not have, if you decide to do this via Cdn will stay like this:
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css"/> <!-- dataTables.css buttons -->
<script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script> <!-- dataTables.js buttons -->
<script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script> <!-- dataTables.html5 -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> <!-- dataTables.pdf maker -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> <!-- dataTables.font maker -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script> <!-- dataTables.xlsx -->
Ready! it seems a lot but it is not. With this you applied the money mask as you send the documentation and is free to manipulate the values of the column without worrying about it.
0
Suppose the numbers in the source json are formatted in English;
For example:
Salary: 162700,00
A routine (fnTextoNumericoParaFloat) was used to replace:
To force American table data format when exporting to Excel.
In my case, the values were saved correctly as number;
<script>
function fnTextoNumericoParaFloat (sNumeroTexto, vErro) {
var resultado = 0;
try {
var aux = "";
var sFator = "";
var fator = sNumeroTexto;
sFator = fator.toString();
try {
aux = sFator;
aux = fnReplaceAll_Aux(aux, ".", "");
aux = fnReplaceAll_Aux(aux, ",", ".");
} catch (error) { aux = vErro; }
if (!$.isNumeric(aux)) { aux = vErro; }
resultado = parseFloat(aux);
}
catch (error) { resultado = vErro; }
return resultado;
}
function fnReplaceAll_Aux (str, de, para) {
str = str || ''
var pos = str.indexOf(de);
while (pos > -1) {
str = str.replace(de, para);
pos = str.indexOf(de);
}
return (str);
}
$(document).ready(function() {
var buttonCommon = {
exportOptions: {
format: {
body: function ( data, row, column, node ) {
var valor = data;
if (column === 5){
valor =fnTextoNumericoParaFloat(data,'N/D');
//console.log(data + ' : ' + valor);
}
return valor ;
//return valor column === 5 ? data.replace( data.replace( /[.]/g, '' ), '' ): data;
}
}
}
};
$('#example').DataTable( {
ajax: 'dados.json',
columns: [
{ data: 'name' },
{ data: 'position' },
{ data: 'office' },
{ data: 'extn' },
{ data: 'start_date' },
{ data: 'salary' }
],
dom: 'Bfrtip',
buttons: [
$.extend( true, {}, buttonCommon, {
extend: 'copyHtml5'
} ),
$.extend( true, {}, buttonCommon, {
extend: 'excelHtml5'
} ),
$.extend( true, {}, buttonCommon, {
extend: 'pdfHtml5'
} )
]
} );
} );
</script>
Browser other questions tagged jquery
You are not signed in. Login or sign up in order to post.
When you do replace, it generates an error, or simply does not get in the format you want?
– Wictor Chaves
It goes to excel as "General" format, so when I use the sum function, it cannot add up. I wanted to export with accounting format
– Elcio Santos
Because you have put a code, in which you use replace, does this replace work? Or does this modification not work?
– Wictor Chaves
This code I put from replace would be to convert the text 8.5 for example to 8.5, so it is possible to transform the text into a float. What I wanted now was to turn this float into a grind when generating excel.
– Elcio Santos