To view a printable version of this article in a PDF viewer, click here for PDF.
To see the example code that goes along with this article, download either the C# code or the VB .NET code.
Introduction
When your application has a tons of data, and your users only need to see a subset to perform their task, you can simplify their work by letting them remove from the display the data they don’t need to see. In version 2 of FarPoint Spread for Windows Forms, row filtering gives you that ability. With row filters, you can let your users tailor their data views and perform customized runtime queries on the provided data. This article introduces row filtering and describes how to use it in a typical application.
Row filtering allows the end user to filter rows of data based on certain criteria or filter items. You define the filter items, which the user can select from a drop-down list for a specific column. By selecting a filter item for a specific column, the user is telling Spread that any row satisfying that filter item is to be included, and all other rows are to be either excluded or changed in the display. Spread for Windows Forms version 2 has two row filtering modes: filtering by hiding rows and filtering by changing styles. Both row filter modes have similar functionality and usability, but their display characteristics are different.
The filter items are displayed in a drop-down list when the end user clicks on the filter gadget that is displayed in the upper-right corner of the column header. By default, the filter items are a list of unique string values that exist within a specific column. Spread makes this list based on the contents of all the rows in that column. Choices for "All", "Blanks", and "NonBlanks" are also added by default to provide some generic functionality. By selecting one of these items from the drop-down list the user is telling Spread to filter the rows. After filtering, a column can have its filtering reset by selecting "All". This does not clear all the column filtering settings, only the settings for the specified column. Beyond that, Spread lets you define custom criteria or filter items.
Filtering by Hiding Rows
The default filtering mode is the hide row mode. A hide row filter can be set up as follows:
HideRowFilter hideRowFilter = new HideRowFilter(fpSpread1.ActiveSheet);
hideRowFilter.AddColumn(0);
fpSpread1.ActiveSheet.RowFilter = hideRowFilter;or, since hide row filter is the default filter mode, by using the shortcut:
fpSpread1.ActiveSheet.Columns[0].AllowAutoFilter = true;Here is a Spread containing car information in which the user filters the rows by manufacturer.
Before Filtering
After Filtering (user selected "Ford")
Filtering by Changing Styles
The other filtering mode in FarPoint Spread is filtering by changing styles. In this mode, Spread displays rows that satisfy the selected filter item criteria in the filtered-in style, and rows that fail the filter item criteria are displayed with the filtered-out style. When setting up a style filter you specify these styles. In most cases, display attributes such as background color, text color, and font are used to indicate a row's filtered status; however, since the filtered-in and filtered-out styles are StyleInfo objects customizability at this level is possible.
A style row filter can be set up as follows:
NamedStyle filteredInStyle = new FarPoint.Win.Spread.NamedStyle();
NamedStyle filteredOutStyle = new FarPoint.Win.Spread.NamedStyle();
filteredInStyle.BackColor = Color.Red;filteredInStyle.ForeColor = Color.White;
filteredOutStyle.BackColor = Color.Gray;
StyleRowFilter styleRowFilter = new StyleRowFilter(fpSpread1.ActiveSheet, filteredInStyle, filteredOutStyle);
styleRowFilter.AddColumn(0);
fpSpread1.ActiveSheet.RowFilter = styleRowFilter;When the end user selects "Ford", the display changes highlighting the Ford vehicles:
Ford Items Highlighted
Multiple columns may have row filters assigned to them. In that case, as the user selects filter items on multiple columns, a dynamic virtual query is created. This query contains the intersection of the rows that satisfy all of the chosen filter items.
As a result, the number of rows of interest can be greatly reduced. After filtering the first column on "Ford", there were four rows that satisfied that filter item (contained the string "Ford" in the first column). Notice below that the fourth column, Closest Location, also contains a filter definition. There are Ford vehicles available in Cary, Morrisville, and Garner. If the user is only interested in Ford vehicles in Cary, they can select the appropriate filter item ("Cary") from the fourth column and the further narrowed result is:
Further Narrowed by Location
In our example we have shown how to reduce the rows of interest using filter item choices, and shown the power of combining filter item choices across multiple columns. The multiple filters also apply to the hiding of rows in the same way.
Custom Filter Items
The filtering shown so far does provide some assistance to the end user in simplifying the search for relevant data, but these solutions alone do not provide enough granularity or flexibility to truly be considered virtual queries. But custom filter items take the row filter idea to the next level. Spread allows you to write custom filter items and assign them to filter column definitions so that rows can be filtered by any criteria you decide.
In our example we have a list of vehicles made by Ford located in Cary. What if a user is only interested in a Mustang? Since the text in the cells of the Model column contains "Mustang V6 Convertible", "Mustang Shelby Cobra GT500", and "Mustang V8 GT" there is no way for a default filter item to include these but distinguish them from other Fords. The solution is to create a custom filter item by extending the DefaultFilterItem class. Adding to the form a text box to accept a filter substring provides even more runtime flexibility. The images below show the custom filter item "Contains: Mustang", followed by the results after selecting the custom filter item.
Selecting Choice "Contains: Mustang"
Result Leaves Only Three
To extend this virtual query, another custom filter item could be written that allows the user to enter the maximum price they are willing to pay. Again, this can be achieved by extending the DefaultFilterItem class, and adding a text box to accept the maximum price. The images below show the custom filter item "Priced Less or Equal to $25,000", followed by the results after selecting the custom filter item.
The images below show the filter item that allows filtering by price. With the choice of "Price Less or Equal to $25,000", the results are shown here.
Selecting Choice "Price"
Final Result
Conclusion
Whether you use hide row filtering or style row filtering, with FarPoint Spread for Wi