Excel Filtering in DataGridView
Have you ever faced the problem of getting desired rows from a huge collection of data? Is it a pain to manually place the cursor and scroll down the whole table? Do you need multiple records within an instance of a single click?
Have no fear, DataGridView AutoFilter is here
Filtering is one of the most common techniques that assists the user to segregate data easily according to the requirements. In C#, the DataGridView is used to represent the data in terms of rows and columns. By default, there’s no easy way to filter out such data. To achieve this goal, we go back to the year 2006…
Really Old is Gold!
Karl Erickson from Microsoft Corporation introduced the basic concept of Excel like filtering by using a DataGridView.
In the official documentation¹ “Building a Drop-Down Filter List for a DataGridView Column Header Cell”, he coined the term AutoFilter for the first time and presented a class library that would allow developers to integrate basic filtering features as shown in the image² below.
For this purpose, the target framework was Microsoft® .NET Framework 2.0 whilst C# & Visual Basic were the two dominant languages and the development process was carried out using Microsoft® Visual Studio® 2005. You can easily download the sample code compressed file from Microsoft Download Centre³.
By the year 2010, Microsoft launched the NuGet Packet Manager⁴ that allowed developers to install packages containing re-usable code fragments.
On 2nd July 2014, a Chinese programmer named zqb created the NuGet package⁵ of this class library with a customized form that allowed basic condition handling for each field such as
Sounds great , but unfortunately all the text string was hard coded in Chinese language which created a barrier for the users who were not familiar with this language.
What’s The Solution?
Finally after six years of waiting, presenting a general version of the DataGridView AutoFilter⁶ with some code modifications, bug fixes and support for following known languages:
How To Get Started?
You might be wondering what are the ingredient steps required to create such a basic form that allows the user to filter out data as per requirements. Is it a difficult job? Nope just follow the steps will guide you to complete this task.
Step 1 : Create a new project
- Open up Microsoft Visual Studio.
- Create a new .NET Framework Windows Form Project.
Step 2: Download NuGet Packages
- Navigate to Project/Manage NuGet Packages.
- Search & Install DataGridView-AutoFilter and MoreLINQ.
Note : Incase you are creating a .NET Framework 2.0 application, use this extension method available on Stack Overflow instead of MoreLINQ.
Step 3: Designing the User Interface
- Drag & Drop a DataGridView from the Toolbox onto the form.
- Choose the option “Dock in Parent Container” to fill up the whole form.
3. Drag & Drop a StatusStrip from the Toolbox.
4. Add two ToolStripStatusLabels by clicking the add button and change the names to FilterStatusLabel & ShowAllLabel accordingly.
5. Set the Properties of FilterStatusLabel as below:
- Clear out the Text.
- Change the Visible from True to False.
6. Set the Properties of ShowAllLabel as below:
- Change the Font to bold.
- Change the Text to Show &All.
- Change the LinkBehaviour from SystemDefault to HoverUnderline.
- Change the IsLink from False to True.
7. Press Ctrl+w then Ctrl+u to open up the Document Outline and place the StatusStrip beneath the DataGridView.
Step 4: Constructing the Person Model
- Right click the TestApp.csproj icon and add a class Person.
Consider a person having attributes such as Rank, Occupation, Job, Salary, Rate and a randomly generated Date. The method PopulateList takes a List of person and assigns the generated data to it.
2. Press F6 to Build the Solution.
Step 5: Assigning DataGridView Data Source
- Click on the small arrow besides the DataGridView.
- From Choose Data Source, select Add Project Data Source.
- Choose Object & click on Next.
- Choose TestApp/TestApp/Person & click on Finish.
Step 6: Manipulating the Columns
- Pin up the Document Outline & set the DataGridView’s AutoSizeColumnsMode property from None to Fill.
2. Simplify the name of each column by pressing F2.
3. Set the ColumnType of each column from DataGridViewTextBoxColumn to DataGridViewAutoFilterTextBoxColumn.
Step 7: Coding
1. Create a private object of the Person class and a private list of type Person.
2. Select the Load event from the Form1 Events List & paste the code.
3. Create the method EnableGridFilter taking a Boolean value to either enable or disable the data filtering. Assign this value to each column’s FilteringEnabled property.
4. Select the Click event from the ShowAllLabel Events list & paste the code.
5. Select the DataBindingComplete event from the DataGridView Events list & paste the code.
6. Select the KeyDown event from the DataGridView Events list & paste code.
7. Finally save and Press F5 to Debug the Project.
Hurrah! We're Done
Not at all! Join us now and make contributions to the library by visiting the DataGridViewAutoFilter⁷ GitHub repository. Demo forms with different look and feel have been generated using the same technique which can be seen below.
To get the source code of all these demo forms, visit the DataGridViewAutoFilterDemo⁸ GitHub repository.
: Building a Drop-Down Filter List for a DataGridView Column Header Cell
: Snapshot from Microsoft’s Official Documentation
: Visual Studio Project Download Link
: NuGet Packet Manager
: DataGridViewAutoFilter 1.0.0
: DataGridView-AutoFilter NuGet Package
: DataGridViewAutoFilter GitHub Repository
: DataGridViewAutoFilterDemo GitHub Repository