Custom Parser for jQuery Tablesorter

Question

I'm using the jQuery Tablesorter and have an issue with the order in which parsers are applied against table columns. I'm adding a custom parser to handle currency of the form $-3.33.

$.tablesorter.addParser({
    id: "fancyCurrency",
    is: function(s) {
      return /^\$[\-]?[0-9,\.]*$/.test(s);
    },
    format: function(s) {
      s = s.replace(/[$,]/g,'');
      return $.tablesorter.formatFloat( s );
    },
    type: "numeric"
});

The problem seems to be that the built-in currency parser takes precedence over my custom parser. I could put the parser in the tablesorter code itself (before the currency parser) and it works properly, but this isn't very maintainable. I can't specify the sorter manually using something like:

headers: {
    3: { sorter: "fancyNumber" },
    11: { sorter: "fancyCurrency" }
}

since the table columns are generated dynamically from user inputs. I guess one option would be to specify the sorter to use as a css class and use some JQuery to explicitly specify a sorter like this question suggests, but I'd prefer to stick with dynamic detection if possible.

Aubarta

Does it fail when there's only positive currency values, or has there always been negative values? – Barraza

@Tim : please check my answer and hoping it will be useful. Thanks friend. – Gumwood

Answer 1

The first option is to give the columns that have currency values the additional class "{'sorter':'currency'}". Also make sure you're including the plugin metadata, which tablesorter supports. This will pull in the per element options and explicitly tell tablesorter how to sort.

<link rel="stylesheet" href="/css/jquery/tablesorter/green/style.css" />
<script src="/js/jquery-1.3.2.js"></script>
<script src="/js/jquery.ui.core.js"></script>
<script src="/js/jquery.metadata.js"></script>
<script src="/js/jquery.tablesorter.js"></script>

<script>
    $(document).ready(function()
    {
    $("#table").tablesorter();
    }
    );
    </script>
</head>
<table id="table" class="tablesorter">
<thead>
  <tr>
    <th class="{'sorter':'currency'}">Currency</th>
    <th>Integer</th>
    <th>String</th>
  </tr>
</thead>
<tbody>
<tr>
  <td class="{'sorter':'currency'}">$3</td>
  <td>3</td>
  <td>three</td>
</tr>
<tr>
  <td class="{'sorter':'currency'}">-$3</td>
  <td>-3</td>
  <td>negative three</td>
</tr>
<tr>
  <td class="{'sorter':'currency'}">$1</td>
  <td>1</td>
  <td>one</td>
</tr>
</tbody>
</table>

Also, there's a bug in the function to format currency: it doesn't handle negative numbers.

I've filed a bug, and am working on landing a patch.

The second option is to apply this fix to your copy of tablesorter. Once you've applied the fix, you won't need to specify the currency sorter in the th or td's(specifying in the td's is over-kill anyway).

Edit line 724 of jquery.tablesorter.js:

return $.tablesorter.formatFloat(s.replace(new RegExp(/[^0-9.]/g),""));

change to:

return $.tablesorter.formatFloat(s.replace(new RegExp(/[^-0-9.]/g),""));

case:

• values: $-3,$1,$3

• current asc order: $1,$3,$-3

• expected asc order $-3,$1,$3

case:

• values: -$3,$1,$3

• current asc order: $1,$3,-$3

• expected asc order $-3,$1,$3

Barraza

I do not work with regex's at all, so I'm not positive my solution is the most correct one; I just know the current code does not handle negative currency at all. – Barraza

Nice writeup. I ended up using some jQuery to add the sorter metadata for my custom parser based on the slug of the header field name. – Aubarta

Answer 2

I had a similar issue and discovered the textExtraction option which is recommended when your cells contain markup. However, it works perfectly well as a pre-format formatter!

$('table').tablesorter({
  textExtraction: function(s) {
    var text = $(s).text();
    if (text.charAt(0) === '$') {
      return text.replace(/[^0-9-.]/g, '');
    } else {
      return text;
    }
  }
});

Acrylic

Answer 3

I used something like this and it worked for me.

Use this class in header < th class="{'sorter':'digit'}"> and in column < td class="{'sorter':'digit'}">.

Once its done , make changes in javascript code for getting all the currency in the form of digits.

Its done, enjoy sorting !!!

Here is the code:

HEADER :

  <th style='width: 98px;' class="{'sorter':'digit'}">
            Amount
   </th>

COLUMN (td):

 <td align="left" style='width: 98px;' class="{'sorter':'digit'}">
            <%= Convert.ToDouble( a.AMOUNT ?? 0.0).ToString("C3") %>
 </td>

JAVASCRIPT :

<script language="javascript" type="text/javascript">
  jQuery("#rewardtable").tablesorter({
    textExtraction: function (data) {
        var text = $(data).text();
        if (text.toString().indexOf("-$") != -1) {
            return ("-" + text.replace(new RegExp(/[^0-9.]/g), ""));
        }
        else if (text.toString().indexOf("$") != -1) {
            return (text.replace(new RegExp(/[^-0-9.]/g), ""));
        }
        else {
            return text;
        }
    }
  });

Gumwood