DataTables is a really cool jQuery plugin for adding LOTS of nifty features to data laid out in tabular format. Yes,
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 header of each column and filtering data by one column, adjusts the filtering options in other columns.
Getting started with DataTables.jsUnfortunately 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.
6. Add a CSS bundle to load the related css files.
7. Update the Index.cshtml by including the CSS at the top of the page
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
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 DatatablesDatatables 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.
So let’s see what it takes to implement this functionality.
The fnGetColumnData Method and extending the filter functionalityIn 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 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.
Download the entire source code (GitHub)