Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Customizing Row or Column Interaction / Managing Filtering of Rows of User Data / Customizing Simple Filtering / Customizing the Filter List / Defining the Contents of the Filter Item List
In This Topic
    Defining the Contents of the Filter Item List
    In This Topic

    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:

    1. Define filter criteria
    2. Define filter result behavior (change styles of rows or hide rows)
    3. Define any custom filters
    4. Apply filter

    Define the filter criteria for each column, which are called the column filter definitions. This is the criteria that is used to filter rows based on the contents of the column and is assigned to an individual column. Combine these individual column criteria or column filter definitions into a collection.

    Define the appearance of the rows to be filtered, either by defining a filtered-in style and a filtered-out style or by deciding to hide the filtered out rows. For more information about styles and the appearance of rows of filtered data, refer to Setting the Appearance of Filtered Rows.

    You can customize the words that appear in the following choices in the drop-down list, using the corresponding properties in the DefaultRowFilter class.

    Apply the row filtering to all or specific columns in a sheet (which applies the column filter definition collection to the columns of that sheet).

    Using Code

    1. Define the column filter definitions.
    2. Group them into a collection.
    3. Define the styles.
    4. Apply the row filter.

    Example

    The following example sets up filtered rows, including filtered row styles.

    C#
    Copy Code 
    // Declare the row filter and column definitions.
FarPoint.Win.Spread.FilterColumnDefinitionCollection fcdc = new FarPoint.Win.Spread.FilterColumnDefinitionCollection();
FarPoint.Win.Spread.FilterColumnDefinition fcd1 = new FarPoint.Win.Spread.FilterColumnDefinition(2);
FarPoint.Win.Spread.FilterColumnDefinition fcd2 = new FarPoint.Win.Spread.FilterColumnDefinition(3);
FarPoint.Win.Spread.FilterColumnDefinition fcd3 = new FarPoint.Win.Spread.FilterColumnDefinition(1);
// Add column filter definitions to a collection.
fcdc.Add(fcd1);
fcdc.Add(fcd2);
fcdc.Add(fcd3);
FarPoint.Win.Spread.NamedStyle inStyle = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle outStyle = new FarPoint.Win.Spread.NamedStyle();
inStyle.BackColor = Color.Yellow;
outStyle.BackColor = Color.Aquamarine;
// Apply styles and column filter definitions to the row filter.
FarPoint.Win.Spread.StyleRowFilter rowFilter = new FarPoint.Win.Spread.StyleRowFilter(fpSpread1.ActiveSheet,inStyle,outStyle);
// Apply the column definition to the filter.
rowFilter.ColumnDefinitions = fcdc;
// Apply the row filter to the sheet.
fpSpread1.ActiveSheet.RowFilter = rowFilter;
fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterGadget;
    VB
    Copy Code 
    ' Declare the row filter and column definitions
Dim fcdc As New FarPoint.Win.Spread.FilterColumnDefinitionCollection()
Dim fcd1 As New FarPoint.Win.Spread.FilterColumnDefinition(2)
Dim fcd2 As New FarPoint.Win.Spread.FilterColumnDefinition(3)
Dim fcd3 As New FarPoint.Win.Spread.FilterColumnDefinition(1)
' Add column filter definitions to a collection.
fcdc.Add(fcd1)
fcdc.Add(fcd2)
fcdc.Add(fcd3)
Dim inStyle As New FarPoint.Win.Spread.NamedStyle()
Dim outStyle As New FarPoint.Win.Spread.NamedStyle()
inStyle.BackColor = Color.Yellow
outStyle.BackColor = Color.Aquamarine
' Apply styles and column filter definitions to the row filter.
Dim rowFilter As New FarPoint.Win.Spread.StyleRowFilter(fpSpread1.ActiveSheet, inStyle, outStyle)
' Apply the column definition to the filter.
rowFilter.ColumnDefinitions = fcdc
' Apply the row filter to the sheet.
fpSpread1.ActiveSheet.RowFilter = rowFilter
fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterGadget

    Example

    This example code creates row filters in a drop-down list that can be accessed by clicking on the drop-down arrow icon in the column header. Two filters are created (hide and style). Comment out the style filter to see the hide filter.

    This example defines filters based on criteria from the contents of columns 1 and 2 of the spreadsheet using the text of the items in the columns as the filter choices.

    C#
    Copy Code 
    // Set the rows to hide when they are filtered out.
FarPoint.Win.Spread.HideRowFilter hideRowFilter = new FarPoint.Win.Spread.HideRowFilter(fpSpread1.ActiveSheet);
hideRowFilter.AddColumn(1);
hideRowFilter.AddColumn(2);
fpSpread1.ActiveSheet.RowFilter = hideRowFilter;

// Set the styles for the filtered-in rows and filtered-out rows.
FarPoint.Win.Spread.NamedStyle inStyle = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle outStyle = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.StyleRowFilter styleRowFilter = new FarPoint.Win.Spread.StyleRowFilter(fpSpread1.ActiveSheet, inStyle, outStyle);
inStyle.BackColor = Color.Yellow;
outStyle.BackColor = Color.Aquamarine;
// Apply the row filter to the two columns.
styleRowFilter.AddColumn(1);
styleRowFilter.AddColumn(2);
fpSpread1.ActiveSheet.RowFilter = styleRowFilter;

// Fill the cells with test data.
fpSpread1.ActiveSheet.Cells[0,1].Value = "aaa";
fpSpread1.ActiveSheet.Cells[1,1].Value = "aaa";
fpSpread1.ActiveSheet.Cells[2,1].Value = "bbb";
fpSpread1.ActiveSheet.Cells[3,1].Value = "ccc";
fpSpread1.ActiveSheet.Cells[4,1].Value = "ddd";
fpSpread1.ActiveSheet.Cells[5,1].Value = "bbb";
fpSpread1.ActiveSheet.Cells[6,1].Value = "aaa";
fpSpread1.ActiveSheet.Cells[7,1].Value = "eee";
fpSpread1.ActiveSheet.Cells[8,1].Value = "jjj";
fpSpread1.ActiveSheet.Cells[9,1].Value = "jjj";
fpSpread1.ActiveSheet.Cells[10,1].Value = "fff";
fpSpread1.ActiveSheet.Cells[11,1].Value = "fff";
fpSpread1.ActiveSheet.Cells[12,1].Value = "eee";
fpSpread1.ActiveSheet.Cells[13,1].Value = "jjj";
fpSpread1.ActiveSheet.Cells[14,1].Value = "eee";
fpSpread1.ActiveSheet.Cells[15,1].Value = "jjj";
fpSpread1.ActiveSheet.Cells[16,1].Value = "fff";
fpSpread1.ActiveSheet.Cells[0,2].Value = "111";
fpSpread1.ActiveSheet.Cells[1,2].Value = "222";
fpSpread1.ActiveSheet.Cells[2,2].Value = "333";
fpSpread1.ActiveSheet.Cells[3,2].Value = "222";
fpSpread1.ActiveSheet.Cells[4,2].Value = "555";
fpSpread1.ActiveSheet.Cells[5,2].Value = "444";
fpSpread1.ActiveSheet.Cells[6,2].Value = "444";
fpSpread1.ActiveSheet.Cells[0,3].Value = "North";
fpSpread1.ActiveSheet.Cells[1,3].Value = "South";
fpSpread1.ActiveSheet.Cells[2,3].Value = "East";
fpSpread1.ActiveSheet.Cells[3,3].Value = "South";
fpSpread1.ActiveSheet.Cells[4,3].Value = "North";
fpSpread1.ActiveSheet.Cells[5,3].Value = "North";
fpSpread1.ActiveSheet.Cells[6,3].Value = "West";
    VB
    Copy Code 
    ' Set the rows to hide when they are filtered out.
Dim hRowFilter As New FarPoint.Win.Spread.HideRowFilter(fpSpread1.ActiveSheet)
hRowFilter.AddColumn(1)
hRowFilter.AddColumn(2)
fpSpread1.ActiveSheet.RowFilter = hRowFilter

' Set the styles for the filtered-in rows and filtered-out rows.
Dim inStyle As New FarPoint.Win.Spread.NamedStyle()
Dim outStyle As New FarPoint.Win.Spread.NamedStyle()
Dim styleRowFilter As New FarPoint.Win.Spread.StyleRowFilter(fpSpread1.ActiveSheet, inStyle, outStyle)
inStyle.BackColor = Color.Yellow
outStyle.BackColor = Color.Aquamarine
' Apply the row filter to the two columns.
styleRowFilter.AddColumn(1)
styleRowFilter.AddColumn(2)
fpSpread1.ActiveSheet.RowFilter = styleRowFilter

' Fill the cells with test data.
fpSpread1.ActiveSheet.Cells(0, 1).Value = "aaa"
fpSpread1.ActiveSheet.Cells(1, 1).Value = "aaa"
fpSpread1.ActiveSheet.Cells(2, 1).Value = "bbb"
fpSpread1.ActiveSheet.Cells(3, 1).Value = "ccc"
fpSpread1.ActiveSheet.Cells(4, 1).Value = "ddd"
fpSpread1.ActiveSheet.Cells(5, 1).Value = "bbb"
fpSpread1.ActiveSheet.Cells(6, 1).Value = "aaa"
fpSpread1.ActiveSheet.Cells(7, 1).Value = "eee"
fpSpread1.ActiveSheet.Cells(8, 1).Value = "jjj"
fpSpread1.ActiveSheet.Cells(9, 1).Value = "jjj"
fpSpread1.ActiveSheet.Cells(10, 1).Value = "fff"
fpSpread1.ActiveSheet.Cells(11, 1).Value = "fff"
fpSpread1.ActiveSheet.Cells(12, 1).Value = "eee"
fpSpread1.ActiveSheet.Cells(13, 1).Value = "jjj"
fpSpread1.ActiveSheet.Cells(14, 1).Value = "eee"
fpSpread1.ActiveSheet.Cells(15, 1).Value = "jjj"
fpSpread1.ActiveSheet.Cells(16, 1).Value = "fff"
fpSpread1.ActiveSheet.Cells(0, 2).Value = "111"
fpSpread1.ActiveSheet.Cells(1, 2).Value = "222"
fpSpread1.ActiveSheet.Cells(2, 2).Value = "333"
fpSpread1.ActiveSheet.Cells(3, 2).Value = "222"
fpSpread1.ActiveSheet.Cells(4, 2).Value = "555"
fpSpread1.ActiveSheet.Cells(5, 2).Value = "444"
fpSpread1.ActiveSheet.Cells(6, 2).Value = "444"
fpSpread1.ActiveSheet.Cells(0, 3).Value = "North"
fpSpread1.ActiveSheet.Cells(1, 3).Value = "South"
fpSpread1.ActiveSheet.Cells(2, 3).Value = "East"
fpSpread1.ActiveSheet.Cells(3, 3).Value = "South"
fpSpread1.ActiveSheet.Cells(4, 3).Value = "North"
fpSpread1.ActiveSheet.Cells(5, 3).Value = "North"
fpSpread1.ActiveSheet.Cells(6, 3).Value = "West"
    See Also
    Defining the Order of the Items in the Filter Item List
    Setting the Appearance of the Display of the Filter Item List