Commas and $ in datatable columns

Question

I have a datatable with 4 columns that hold currency. Currently I'm treating them as normal columns and manually appending '$' to each value. Now I need to format the column to have commas as well. Is there any plug-in to do this? I also want to remove the manual addition of '$' value. I checked few sites, but I really didn't understand how they work.

Answer 1

[Updating answer to use DataTables 1.9+ and to honor rynop's better answer. Original answer preserved below the horizontal rule, but it's both out of date and less efficient than it should be.]

Since it is really the data that you want to modify, not the entire cell, you should be using the "render" property inside of the columns definition. To produce clean code, you could store the actual modification method elsewhere and just call over to 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 regex from this accepted answer:

Add comma to numbers every three digits

---

[original answer]

I wouldn't add dollar values to each cell myself, a decision that has the side-effect of reducing the complexity of what you need to do. ;-) In any spreadsheet or table of currency values, you only need to put the currency symbol in the header or the first row. Putting it in the header will make your life easier, putting it in the first row will actually add complexity to your problem.

So, back to DataTables itself, you have multiple ways to skin this cat but here are two:

1. Render the whole table without the commas (ie. default DataTables behaviour) but adding a class to those particular cells with the sClass parameter. Then use fnDrawCallback to fire the comma-creating function as described in the above link.

2. Use only the regex from the above link to modify the cell as data comes in.

Something like this (where 3 is the zero-index column that you're modifying):

"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 MUST add the dollar sign, you would just prepend it in the same function before the text function.

Answer 2

I would leverage the 'mRender' method in 'aoColumns'. Its cleaner and probably more efficient than the currently accepted solution.

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 complete control over the cell - for example if you want wrap the data with some HTML.

See http://www.datatables.net/usage/columns#mRender for complete specifications

Answer 3

DataTables 1.10 has a new Number Helper which is used in conjunction with the render option to easily format numbers, like this:

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

This will display a price value like 1000.006 as "$1,000.01".