Here I set as described the data table thousand separator, but it doesn’t work the way I expected.
Can anybody help me?
$('#example').dataTable( {
"language": {
"thousands": "'"
}
} );table.dataTable thead th {
border-bottom: 0;
}
table.dataTable tfoot th {
border-top: 0;
}<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link href="http://cdn.datatables.net/rowreorder/1.0.0/css/rowReorder.dataTables.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.js"></script>
<script src="https://cdn.datatables.net/rowreorder/1.0.0/js/dataTables.rowReorder.js"></script>
<link href="http://cdn.datatables.net/1.10.0/css/jquery.dataTables.css" rel="stylesheet"/>
<script src="http://cdn.datatables.net/plug-ins/1.10.24/sorting/formatted-numbers.js"></script>
<table id="example">
<thead>
<tr>
<th>Seq.</th>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>2011/04/25</td>
<td>320800</td>
</tr>
<tr>
<td>22</td>
<td>Garrett Winters</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>2011/07/25</td>
<td>170750</td>
</tr>
<tr>
<td>6</td>
<td>Ashton Cox</td>
<td>Junior Technical Author</td>
<td>San Francisco</td>
<td>2009/01/12</td>
<td>86000</td>
</tr>
<tr>
<td>41</td>
<td>Cedric Kelly</td>
<td>Senior Javascript Developer</td>
<td>Edinburgh</td>
<td>2012/03/29</td>
<td>433060</td>
</tr>
<tr>
<td>55</td>
<td>Airi Satou</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>2008/11/28</td>
<td>162700</td>
</tr>
<tr>
<td>21</td>
<td>Brielle Williamson</td>
<td>Integration Specialist</td>
<td>New York</td>
<td>2012/12/02</td>
<td>372000</td>
</tr>
<tr>
<td>46</td>
<td>Herrod Chandler</td>
<td>Sales Assistant</td>
<td>San Francisco</td>
<td>2012/08/06</td>
<td>137500</td>
</tr>
</tbody>
</table>Thanks
Advertisement
Answer
You can use a column render function to convert your source data from numbers without thousands separators to the format you want.
$(document).ready(function() {
var table = $('#example').DataTable( {
"lengthMenu": [ 5, 10, 50, 100 ], // just for testing!
columnDefs: [
{
targets: [5],
render: function ( data, type, row, meta ) {
return '$' + parseInt(data).toLocaleString('en-US');
}
}
]
} );
} );<head>
<meta charset="UTF-8">
<title>Demo</title>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
</head>
<body>
<div style="margin: 20px;">
<table id="example" class="display dataTable cell-border" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office in Country</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>2011/04/25</td>
<td>320800</td>
</tr>
<tr>
<td>22</td>
<td>Garrett Winters</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>2011/07/25</td>
<td>170750</td>
</tr>
<tr>
<td>6</td>
<td>Ashton Cox</td>
<td>Junior Technical Author</td>
<td>San Francisco</td>
<td>2009/01/12</td>
<td>86000</td>
</tr>
<tr>
<td>41</td>
<td>Cedric Kelly</td>
<td>Senior Javascript Developer</td>
<td>Edinburgh</td>
<td>2012/03/29</td>
<td>433060</td>
</tr>
<tr>
<td>55</td>
<td>Airi Satou</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>2008/11/28</td>
<td>162700</td>
</tr>
<tr>
<td>21</td>
<td>Brielle Williamson</td>
<td>Integration Specialist</td>
<td>New York</td>
<td>2012/12/02</td>
<td>372000</td>
</tr>
<tr>
<td>46</td>
<td>Herrod Chandler</td>
<td>Sales Assistant</td>
<td>San Francisco</td>
<td>2012/08/06</td>
<td>137500</td>
</tr>
</tbody>
</table>
</div>
</body>This has the following features:
It will work for every record in the table, not just for those which are displayed on the first page.
It does not require a regular expression such as
data.replace(/B(?=(d{3})+(?!d))/g, ",");– and is therefore easier to understand.It uses JavaScript’s built-in support for number formatting using
toLocaleString. This means it is also possible to change the thousands separator by applying a different locale (the language tag). For example, if you replace'en-US'withfr-FR, then you will get the type of thousands separator used in France, which is a space – so$320 800instead of$320,800.
The above code assumes the source data is provided as number without a currency symbol:
<td>320800</td>
If the source data already has a currency symbol at the start of the string, for example, like this:
<td>$320800</td>
then you would need to adjust the render function as follows:
render: function ( data, type, row, meta ) {
return data.substring(0, 1) + parseInt(data.substring(1)).toLocaleString('en-US');
}