Commas and $ in data columns

Question

I have a datatable with 4 columns in which there is a currency. I am currently treating them like regular columns and manually adding "$" to each value. Now I need to format the column to have commas. Is there a plugin for this? I also want to remove the manual addition of the value $. I checked several sites, but I really did not understand how they work.

Answer 1

[Updating the response to use DataTables 1.9+ and for a better answer to rynop. The original answer is kept below the horizontal rule, but it is outdated and less effective than it should be.]

Since this is actually the data you want to change, not the whole cell, you should use the "render" property inside the column definition. To create clean code, you can save the actual modification method in another place and simply call it:

var myTable = $('#mytable').DataTable({ ... "columns": [ { "data" : "key_of_column", "render" : function( data, type, full ) { // you could prepend a dollar sign before returning, or do it // in the formatNumber method itself return formatNumber(data); } } ] ... }); // elsewhere... probably more likely in a utility namespace or module function formatNumber(n) { return n.replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,"); } 

formatNumber uses the regular expression from this accepted answer:

Add a comma for numbers every three digits

---

[original answer]

I would not add dollar values ​​to each cell myself, a solution that has a side effect that reduces the complexity of what you need to do. ;-) In any table or table of currency values, you only need to put the currency symbol in the header or first line. Putting it in the header will make your life easier, putting it on the first line will actually add complexity to your problem.

So, back to the DataTables themselves, you have several ways to trick this cat, but here are two:

• Display the entire table without commas (that is, the default behavior of DataTables), but adding a class to these specific cells with the sClass parameter. Then use fnDrawCallback to launch the comma creation function, as described in the link above.

• Use only the regular expression from the link above to change the cell when entering data.

Something like this (where 3 is the zero column of the zero index):

 "aoColumnDefs": [ { "aTargets": [3], "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) { var $currencyCell = $(nTd); var commaValue = $currencyCell.text().replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,"); $currencyCell.text(commaValue); } }] 

( aoColumnDefs is part of your initialization object passed to dataTable() );

If you absolutely SHOULD add a dollar sign, you simply add it to the same function before the text function.

Answer 2

I would use the "mRender" method in "aoColumns". Its cleaner and probably more efficient than the current decision.

Example:

 oTable = $('#mytable').dataTable( { ... "aoColumns": [ { "sTitle": "MyCol With Number", "mRender": function( data, type, full ) { return formatNumber(data); }, ... 

Where formatNumber is defined as follows:

 function formatNumber(n) { return n.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ","); } 

Using fnRender also allows you to have full control over the cell - for example, if you want to wrap data in some HTML.

See http://www.datatables.net/usage/columns#mRender for a full specification

Answer 3

DataTables 1.10 has a new Number Helper that is used with the render option to easily format numbers, for example:

 { data: 'price', render: $.fn.dataTable.render.number( ',', '.', 2, '$' ) } 

This displays the price value, for example 1000.006, as "1000.01".