Javascript number sorting on column table with commas as thousand separator-CodePudding

i have data from my db that used in table. And I use some sorting javascript code to sort each column. But sorting result for number was not right (unordered number output).

function sortTable(f, n) {
  var rows = $('#Product tbody  tr').get();

  rows.sort(function(a, b) {

    var A = getVal(a);
    var B = getVal(b);

    if (A < B) {
      return -1 * f;
    }
    if (A > B) {
      return 1 * f;
    }
    return 0;
  });

  function getVal(elm) {
    var v = $(elm).children('td').eq(n).text().toUpperCase();
    if ($.isNumeric(v)) {
      v = parseInt(v, 10);
    }
    return v;
  }

  $.each(rows, function(index, row) {
    $('#Product').children('tbody').append(row);
  });
}

var f_sort_price = 1;
var f_sort_quantity = 1;

$("#sort_price").click(function() {
  f_sort_price *= -1;
  var n = $(this).prevAll().length;
  sortTable(f_sort_price, n);
});

$("#sort_quantity").click(function() {
  f_sort_quantity *= -1;
  var n = $(this).prevAll().length;
  sortTable(f_sort_quantity, n);
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="Product">
  <thead>
    <tr>
      <th>Product</th>
      <th id="sort_price">Price</th>
      <th id="sort_quantity">Quantity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Graphic Card</td>
      <td>2,040</td>
      <td>1,493</td>
    </tr>
    <tr>
      <td>Wireless Router</td>
      <td>8,420</td>
      <td>2,272</td>
    </tr>
    <tr>
      <td>Mouse</td>
      <td>420</td>
      <td>493</td>
    </tr>
    <tr>
      <td>Monitor</td>
      <td>4.420</td>
      <td>874</td>
    </tr>
  </tbody>
</table>

and this is my fiddle https://jsfiddle.net/imawof/bgkneo81/18/

i want to sort the price and quantity, with formated number in td. But i have no clue how to do right sorting it with formated number (commas thousand separator, dot decimal).

CodePudding user response：

I would first grab a copy of the data in the grid so that we can return to the default order when we toggle sorting off. See the tableData assignment below.

Now, to keep track of sorting, it would be better to assign data attributes to the table headers to keep track of sorting state.

For sorting, I wrote custom sorting logic as a jQuery plugin called $.fn.sort. This can be called on a table to change the sort order of the rows. The tableData is optional, but it helps give us back the original order.

(function($) {
  $.parseNumeric = function(value) {
    return  value.replace(/[^-0-9.]/g, '').replace(/[,]/g, '')
  };
  
  $.isNumeric2 = function(value) {
    return !isNaN( value.replace(/[$,]/g, ''));
  };
  
  $.tableDataToRows = function(data) {
    return data.map(row => $('<tr>')
      .append(row.map(col => $('<td>').text(col))));
  };
  
  $.fn.extractTableData = function() {
    return this.find('tbody tr').toArray()
      .map(tr => $(tr).find('td').toArray()
        .map(td => $(td).text()));
  };

  $.fn.sort = function(colIndex, direction, tableData) {
    var $tbody = this.find('tbody');
    var $rows = $tbody.find('tr');
    var sortDir = direction === 'asc' ? 1 : direction === 'desc' ? -1 : 0;
    var data = tableData ? $.tableDataToRows(tableData) : $rows.toArray();
    
    var sorted = data.sort((rowA, rowB) => {
      const a = $(rowA).find(`td:eq(${colIndex})`).text();
      const b = $(rowB).find(`td:eq(${colIndex})`).text();
      if (!b === '') return -1;
      if (!a === '') return 1;
      if ($.isNumeric2(a) && $.isNumeric2(b)) {
        return ($.parseNumeric(a) - $.parseNumeric(b)) * sortDir;
      }
      return a.localeCompare(b) * sortDir;
    });

    $tbody.empty().append(sorted);
  }
})(jQuery);

const nextSortDirection = (sortDirection) =>
  sortDirection
    ? sortDirection === 'asc' ? 'desc' : ''
    : 'asc';

const toggleSort = ($header) => {
  const sortDirection = nextSortDirection($header.data('sort-direction'));
  // Clear the existing sort
  $header.siblings().each(function() {
    $(this).attr('data-sort-direction', '');
    $(this).data('sort-direction', '');
  });
  // Change the sort for the current column
  $header.attr('data-sort-direction', sortDirection);
  $header.data('sort-direction', sortDirection);
  return sortDirection;
};

const $table = $('#Products');
const tableData = $table.extractTableData();

const onHeaderSort = (e) => {
  const $header = $(e.target);
  const $table = $header.closest('table');
  const field = $header.data('field');
  const colIndex = $header.prevAll().length; 
  const sortDirection = toggleSort($header);
  $table.sort(colIndex, sortDirection, tableData);
};

$table.find('th[data-sortable="true"]').on('click', onHeaderSort);

:root {
  --header-color: #BBB;
  --row-color-even: #DDD;
  --row-color-odd: #FFF;
}

.styled-table {
  border-collapse: collapse;
}

.styled-table {
  border: thin solid grey;
}

.styled-table th, .styled-table td {
  border: none;
}

.styled-table th, .styled-table td {
  padding: 0.25rem;
}

.styled-table th {
  position: relative;
  padding-right: 1.5rem;
  background: var(--header-color);
}

.styled-table tbody tr:nth-child(even) {
  background: var(--row-color-even);
}

.styled-table tbody tr:nth-child(odd) {
  background: var(--row-color-odd);
}

th[data-sortable]:after {
  position: absolute;
  content: "\21D5";
  cursor: pointer;
  right: 0.25rem;
}
th[data-sort-direction="asc"]:after {
  content: "\21D1";
}
th[data-sort-direction="desc"]:after {
  content: "\21D3";
}

tbody td:nth-child(3),
tbody td:nth-child(4) {
  text-align: right;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="Products" >
  <thead>
    <tr>
      <th data-field="id" data-sortable="true" data-sort-direction="">ID</th>
      <th data-field="product" data-sortable="true" data-sort-direction="">Product</th>
      <th data-field="price" data-sortable="true" data-sort-direction="">Price</th>
      <th data-field="quantity" data-sortable="true" data-sort-direction="">Quantity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>Graphic Card</td>
      <td>$2,040.00</td>
      <td>1,493</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Wireless Router</td>
      <td>$8,420.00</td>
      <td>2,272</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Mouse</td>
      <td>$420.00</td>
      <td>493</td>
    </tr>
    <tr>
      <td>4</td>
      <td>Monitor</td>
      <td>$4.42</td>
      <td>874</td>
    </tr>
  </tbody>
</table>

CodePudding user response：

You are mixing commas(8,420) and decimal points (4.420).

If you intend to keep the ',' as visual seperators, you have to remove them in your sort function before converting them. I also recommend parseFloat to keep the decimal points for your Prices.

function getVal(elm) {
           var v = $(elm).children('td').eq(n).text().toUpperCase();
           v = v.replace(",","");
            if ($.isNumeric(v)) {
                v = parseFloat(v);
            }
            return v;
        }