FarPoint's Spread for Windows Forms Product Tour

You can customize the user experience for filtering data on a sheet. With row filtering, you can allow the user to filter the data in columns on a sheet and display only the rows of data which meet criteria from a drop-down list or change the appearance of rows based on that filtering. You can use the basic default filtering or you can customize practically every aspect of the filtering.

Back to Top

Allowing Row Filtering

By default, the spreadsheet does not allow the user to filter the rows of a spreadsheet. You can turn on this feature and allow row filtering to all or specific columns on a sheet. The user can then click the items in the drop-down to filter the rows.

// C#

							fpSpread1.ActiveSheet.Columns[1,3].AllowAutoFilter = True;

							

							' VB

							fpSpread1.ActiveSheet.Columns(1, 3).AllowAutoFilter = True

play video

Using Row Filtering

Once you have row filtering applied to a column, an filter indicator appears in the column header. Clicking on this indicator provides a drop-down list of the filter choices. Picking an item from this list causes that filter to be applied and all the rows meeting that condition (in this column) are filtered. The default drop-down list contains all the unique text values in cells in this column. You can customize the way this list is displayed.

For a given sheet, multiple columns may have filtering set. The different columns may have different filters, depending on the contents of cells in that column. The results of filtering would be similar to what one would expect with primary and secondary keys when sorting data. The choice from the filter list from the initial column would filter some rows, leaving the choices in the subsequent filter list to be a subset of the total possible. By selecting choices from more than one filter, the results include only those rows that satisfy all the selected filtering conditions.

	Appearance of header cell with no row filtering.
	Appearance of header cell with row filtering allowed but no rows filtered.
	Appearance of header cell with row filtering allowed and some rows filtered.

Customizing Filter List Behavior
You can customize how the drop-down list of filter items is displayed. By default, the list shows the possible filter items alphabetically and includes all the options. By changing the value of the FilterListBehavior enumeration, you change how the filter list is displayed. For example you can set the filter list to display items in order of number of occurrences in that column.

Customizing Styles for Row Filtering
You can customize the appearance of filtered rows to allow you to see which rows are filtered in and which ones are filtered out. Rows that meet the criteria for the row filter are said to be "filtered in"; rows that do not meet the criteria are said to be "filtered out". Filtering may either hide the rows that are filtered out, or change the styles for both filtered-in and filtered out rows. If you want the styles to change, so that you can continue to display all the data but highlight rows that match some criteria, then you can define a filtered-in style and a filteredout style. You define styles by creating NamedStyle objects that contain all the style settings. Then when the row filtering is applied to a column, you specify those defined style settings by referring to the NamedStyle object for that filtered state. // apply styles to row filter FarPoint.Win.Spread.StyleInfo inStyle = new FarPoint.Win.Spread.NamedStyle(); FarPoint.Win.Spread.StyleInfo outStyle = new FarPoint.Win.Spread.NamedStyle(); inStyle.BackColor = Color.Lime; outStyle.BackColor = Color.Yellow; FarPoint.Win.Spread.StyleRowFilter rowFilter = new FarPoint.Win.Spread.StyleRowFilter(fpSpread1.ActiveSheet,inStyle,outStyle); rowFilter.AddColumn(1); // apply row filter to sheet fpSpread1.ActiveSheet.RowFilter = rowFilter; ' apply styles to row filter Dim inStyle as new FarPoint.Win.Spread.NamedStyle() dim outStyle as new FarPoint.Win.Spread.NamedStyle() inStyle.BackColor = Color.Lime outStyle.BackColor = Color.Yellow Dim rowFilter As New FarPoint.Win.Spread.StyleRowFilter(FpSpread1.ActiveSheet, inStyle, outStyle) rowFilter.AddColumn(1) ' apply row filter to sheet FpSpread1.ActiveSheet.RowFilter = rowFilter

Customizing the List of Filter Items
You can customize the contents and behavior of the drop-down list of filter items available to the user when they click on the row filter indicator. You can filter all rows in a sheet based on criteria of the contents of a particular cell in a column. To set up row filters, follow these basic steps: Define filter criteria Define filter result behavior (change styles of rows or hide rows) Define any custom filters Apply filter