How to add select filtering for column values in javascript DataTables

Question

I'm using javascript DataTables to display a csv file on a webpage. Below is my

javascript file:

var CsvToHtmlTable = CsvToHtmlTable || {};

CsvToHtmlTable = {
    init: function (options) {

      options = options || {};
      var csv_path = options.csv_path || "";
      var el = options.element || "table-container";
      var allow_download = options.allow_download || false;
      var csv_options = options.csv_options || {};
      var datatables_options = options.datatables_options || {};
      var custom_formatting = options.custom_formatting || [];

      $("#" + el).html("<table class='table table-striped table-condensed' id='" + el + "-table'></table>");

      $.when($.get(csv_path)).then(
        function(data){      
          var csv_data = $.csv.toArrays(data, csv_options);

          var table_head = "<thead><tr>";

          for (head_id = 0; head_id < csv_data[0].length; head_id++) { 
            table_head += "<th>" + csv_data[0][head_id] + "</th>";
          }

          table_head += "</tr></thead>";
          $('#' + el + '-table').append(table_head);
          $('#' + el + '-table').append("<tbody></tbody>");

          for (row_id = 1; row_id < csv_data.length; row_id++) { 
            var row_html = "<tr>";
          var color = "red";
            //takes in an array of column index and function pairs
            if (custom_formatting != []) {
              $.each(custom_formatting, function(i, v){
                var col_idx = v[0]
                var func = v[1];
                csv_data[row_id][col_idx]= func(csv_data[row_id][col_idx]);
              })
            }

              for (col_id = 0; col_id < csv_data[row_id].length; col_id++) {
          if (col_id === 2) {

              row_html += "<td>" + parseFloat(csv_data[row_id][col_id]) + "</td>";
          }
          else {
              row_html += "<td>" + csv_data[row_id][col_id] + "</td>";
          }

          if (parseFloat(csv_data[row_id][2]) <= 1 && parseFloat(csv_data[row_id][2]) > 0.7) {
              color = "red";

          }
          else if (parseFloat(csv_data[row_id][2]) <= 0.7 && parseFloat(csv_data[row_id][2]) >= 0.5) {
              color = "orange";
          }

          else {
              color = "yellow";
          }


            }

            row_html += "</tr>";
            $('#' + el + '-table tbody').append(row_html).css("background-color", color));
          }

          $('#' + el + '-table').DataTable(datatables_options);

          if (allow_download)
            $("#" + el).append("<p><a class='btn btn-info' href='" + csv_path + "'><i class='glyphicon glyphicon-download'></i> Download as CSV</a></p>");
        });
    }
}

And below is my index.html file:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
    <title>CSV to HTML Table</title>


    <!-- Bootstrap core CSS -->
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/dataTables.bootstrap.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!--[if lt IE 9]>
      <script type="text/javascript" src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script type="text/javascript" src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
  </head>

  <body>
    <div class="container-fluid">

      <h2>CSV to HTML Table</h2>

      <div id='table-container'></div>

    </div><!-- /.container -->


    <!-- Bootstrap core JavaScript
    ================================================== -->
    <!-- Placed at the end of the document so the pages load faster -->
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/bootstrap.min.js"></script>
    <script type="text/javascript" src="js/jquery.csv.min.js"></script>
    <script type="text/javascript" src="js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="js/dataTables.bootstrap.js"></script>
    <script type="text/javascript" src="js/csv_to_html_table.js"></script>


    <script type="text/javascript">
      function format_link(link){
        if (link)
          return "<a href='" + link + "' target='_blank'>" + link + "</a>";
        else
          return "";
      }
      CsvToHtmlTable.init({
        csv_path: 'data/fatty_acid_profiles.csv',
        element: 'table-container', 
        allow_download: true,
        csv_options: {separator: ','},
        datatables_options: {"paging": false},
        custom_formatting: [[4, format_link]]
      });
    </script>
  </body>
</html>

My webpage currently looks like this:

I want to know is it possible in DataTables that for 2nd & 3rd columns, I get a Filter along with the column name so that we can select for which specific values we want to view data for, something like what we have in Excel (using Sort & Filter)?? Please help!!

Emre Bolat · Accepted Answer · 2017-09-22 07:35:16Z

2

Yes, it is possible with a customized solution. You need to read all columns and add distinct members to dropdowns like this.

$(document).ready(function() {
    $('#example').DataTable( {
        initComplete: function () {
            this.api().columns().every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo($(column.header()).empty())
                    .on('change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );

                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    });

                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                });
            });
        }
    });
});

By using column().search() functionality, you will have a column based filter with dropdowns. You can move dropdowns from header to footer by changing .appendTo($(column.header()).empty()) to .appendTo($(column.footer()).empty()).

Examples:

jsFiddle (header dropdowns)
jsFiddle (footer dropdowns)

answered Sep 22, 2017 at 7:35

Emre Bolat

4,5626 gold badges31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Xavier Over a year ago

This is great! But can't we have multiple selections in the dropdown? Like we can do in Excel?

Emre Bolat Over a year ago

You need some more custom javascript or another js library like select2 to implement that feature. It is too complex to answer under this question. If this answer helped you, please mark it as selected.

Xavier Over a year ago

Hmm, sorry but I specifically asked in my question about filtering like Excel, which means multiple selection option, the above does not help me much.

bdalina Over a year ago

This has help me, this is great. I can filter the gender using this, which the simple search input can't , i search male, but both male and female shows, and instead of having multiple inputs in the search like "name gender address" once in the input field i can use this heading select filter with gender and address. then at the search box is the name. @Emre Bolat thanks!

Collectives™ on Stack Overflow

How to add select filtering for column values in javascript DataTables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related