Excel Style Data Filtering in ASP.NET MVC using DataTables.js Plugin

DataTables is a really cool jQuery plugin for adding LOTS of nifty features to data laid out in tabular format. Yes, <table>s are looked down upon by hipsters but they are very useful for presenting – tabular data.

Today I’ll show how we can use DataTable to do Excel style filtering in an ASP.NET MVC application where we have filter-dropdowns on the footer of each column and filtering data by one column, adjusts the filtering options in other columns.

Getting started with DataTables.js

Unfortunately DataTables is not available as a Nuget package yet, so we do it the old fashioned way by downloading it from the source site at http://datatables.net/download/. The version at the time of this article is 1.9.4. Let’s get started

1. Start Visual Studio and create an MVC Project.

2. Download the zip file and extract it to a temp folder.

3. From the extracted files, navigate to the media\js folder and select the jquery.datatables.js and jquery.datatables.min.js file. Add it to the Scripts folder in the MVC project. By keeping both the minified and non-minified file, you let the ASP.NET Bundling and Minification system pick the appropriate file.

4. Next copy the media\css folder and paste the contents into MVC project’s ‘Contents’ folder.

5. Modify the BundleConfig.cs by adding a new bundle for Datatables.

bundles.Add(new ScriptBundle("~/bundles/jqueryPlugins").Include(
                "~/Scripts/jquery.dataTables.*"));

6. Add a CSS bundle to load the related css files.

bundles.Add(new StyleBundle("~/Content/datatables").Include("~/Content/css/*.css"));

7. Update the Index.cshtml by including the CSS at the top of the page

@Styles.Render("~/Content/datatables")

and the JavaScript at the bottom of the page

@Scripts.Render("~/bundles/datatables")

8. I’ve copied the sample data from DataTable’s example, it’s a list of Browser engines and the browsers in which they were used.

9. Finally initialize Datatables and apply it to our sample data

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function ()
        {
            /* Initialise the DataTable */
            var oTable = $('#example').dataTable({
                "oLanguage": {
                    "sSearch": "Search all columns:"
                }
            });
        });
    </script>
}

10. Once we are set, we run the application and if everything goes right we should see the following



11. As we can see, a vanilla table has been converted to a pretty functional ‘Grid’ of data with client-side pagination, filtering and searching. We want to add to this existing functionality by adding DropDowns to the header and filter data accordingly.

Plugging in functionality to Datatables

Datatables is hugely popular not only because of the rich functionality it adds but also because of its extensibility. A code snippet from Datatables site adds dropdowns to the footer and adds filtering functionality using select (combobox) elements.



However this code has one limitation, even though the data in the grid changes, the select options remain the same giving rise to filtering combinations that never have any data. For e.g.
Above we see that the Trident rendering engine was used in only a handful of browsers, but when we click on the Select in the Browsers column, we still get to see browsers that never used Trident. This is unlike Microsoft Excel where filters get auto updated with the reduced dataset as the filtering progresses.

So let’s see what it takes to implement this functionality.

The fnGetColumnData Method and extending the filter functionality

In the code we got from the Datatables plugin, we see the function fnGetColumnData essentially uses the data that’s currently visible in the Datatable and retrieves the column that we attached it to. This is pretty much what we want, but at a closer look we see that the data once attached to the column is never refreshed, hence irrespective of which filter we apply, all the filters selections have the same set of values in the select element. So to make it dynamic, we need to reload all the select elements after each filter is applied. We slightly adjust the code to update data for every select action. 


The code change is highlighted above and what it does is to essentially create the Select dropdown on every change. This does filter the data however if you note there are other side-effects like the First selection becomes invisible as it gets re-created too. So we need to ensure that the columns are already a part of the filter are not recreated in the filterSelectData method. So we manage a Map of columns currently in use and for those items we avoid refreshing the select lists.



The final code is as follows:



We’ve added code to check if the current selected value is empty or not. If empty, it means that the filter is being reset, else the filter is being applied. Once the filter is applied, we add it to the filterdColumnIndexMap. While filteringSelectData we check if the column is being used for filtering, if not we go ahead and re-filter the data.

Conclusion

We saw how to use the awesome Datatables.js plugin to spice up our ‘Tables’ and then with minimal code, how to add Excel like filtering. Caveat is that the filtering code is a little rudimentary and you will face challenges when the table data is showing hyperlinks or checkboxes for true/false etc. However all of that can be covered with incremental changes in the JavaScript to get a highly functional behavior.

Download the entire source code (GitHub)