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

Define the filter criteria for each column, which is 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 simply 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.

All - AllString Property
Blanks - BlanksString Property
NonBlanks - NonBlanksString Property

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

Using Code

Here is how to set up filtered rows in code.

Define column filter definitions
Group them into a collection
Define styles
Apply row filter

Example

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;

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

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

Using Code

Set up row filters in code that define 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.

Define column filter definitions
Group them into a collection
Define styles
Apply row filter

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 in order to see the hide filter.

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";

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"

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"

Spread Windows Forms 7.0 Product Documentation
Defining the Contents of the Filter Item List
See Also Support Options

Using Code

Example

Using Code

Example

See Also