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.

Solution 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.

Solution 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

Solution 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".

Comments

[user1165952]

Thanks a lot for the answer. I took the 2nd approach, since I must add a dollar sign. I used this function money_format('%(#10n', $cost) which adds the dollar sign as well.

[user1165952]

I also added - setlocale(LC_MONETARY, 'en_US.UTF-8'); at the beginning of the php.

[Greg Pettit]

Glad it worked for you! These are only two of the many ways you could skin this cat. Another very similar one would be to do it in the fnRowCallback. I only mention this because you may find a reason to move it later and you should be aware it's there!

[Greg Pettit]

I should get around to updating this for API 1.9+ syntax; also, instead of modifying the "created cell", a lot of this can be done in the "render" callback. The difference is really about whether you need to modify the cell itself or just the contents.

[Greg Pettit]

Just rediscovered this Q, and saw rynop's answer. Maybe years later, but not to late to mention that he's right. mRender, now called simply "render" is the better place to do this, and calling to an external renderer is the better way. Since my answer is still the accepted one, I will update it with due credit to rynop.