Spread for Windows Forms Quick Start (11) --- Data Filtering

Spread allows developers to customize user experience for data filtering. Based on row data filtering, you can allow users to filter data by column to display only the data of rows that meet the conditions in the drop-down list, or change the appearance of rows based on the filtering results. You can use the default filtering method, or you can customize each aspect of the filter based on the actual situation.

Allow users to filter rows

By default, the form does not allow users to filter rows. You can enable this feature to allow users to filter all or specified columns. Use the HideRowFilter class or StyleRowFilter class to hide the filtered column or change the column appearance. Use the column AllowAutoFilter attribute to filter a given column.

After setting, you can select the option in the drop-down list to filter columns.

When filtering and hiding rows by value in a column), make sure the first column is visible. Use the HideRowFilter class to select the type of row filtering and use the AllowAutoFilter attribute to filter the selected columns. The following example assumes that the cell contains some data, either the specified data or the bound data source.

FpSpread1.ActiveSheet. ColumnHeaderVisible = true; FarPoint. Win. Spread. HideRowFilter hideRowFilter = new FarPoint. Win. Spread. HideRowFilter (fpSpread1.ActiveSheet ); FpSpread1.ActiveSheet. Columns [1, 3]. AllowAutoFilter = true;

Use row Filtering

Once you use row filtering in a column, an indicator is displayed at the beginning of the column. The following table summarizes the different appearances of the row filter indicator:

Row filter indicator	Description
650) this. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "title =" image "border =" 0 "alt =" image "width =" 145 "height =" 34 "src =" http://www.bkjia.com/uploads/allimg/131228/1T13UI6-0.png "/>	The appearance of the header cell that is not filtered by the row. This usually occurs when no filtering is performed or when the row filtering is invalid.
650) this. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "title =" image "border =" 0 "alt =" image "width =" 116 "height =" 32 "src =" http://www.bkjia.com/uploads/allimg/131228/1T13Sc4-1.png "/>	You can perform row filtering, but the appearance of the header cell is not filtered out. This usually happens when all the content meets the filtering conditions.
650) this. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "title =" image "border =" 0 "alt =" image "width =" 117 "height =" 33 "src =" http://www.bkjia.com/uploads/allimg/131228/1T13WU7-2.png "/>	You can perform row filtering and filter the appearance of some of the header cells. This usually happens when you filter some rows based on the row content.

A row filter indicator similar to the drop-down arrow symbol is displayed at the beginning of the column. Click this indicator to display a drop-down menu that contains various filter options. Select an item from the list, and the filtering will take effect. In this column, all the rows that match the filtering will be filtered out. The default drop-down list includes all non-duplicated text in cells in this column.

650) this. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "title =" image "border =" 0 "alt =" image "width =" 197 "height =" 161 "src =" http://www.bkjia.com/uploads/allimg/131228/1T13W625-3.png "/>

The following chart lists the entries in the drop-down list.

Filter list items	Description
All)	All rows in this column, regardless of the content
[Content]	Only rows that contain or allow specific cells in this column
Blank)	Rows that only contain or allow empty cells in this column)
Not empty)	Only contains or allows non-empty rows in this column to be non-empty cells). In other words, any cells that contain content.

In a given form, multiple columns may have filters. Based on the cell content in the column, different columns can have different filters. The filtering result is similar to sorting data based on the primary key and the slave key. These options in the filter list in the initial column will filter some rows, and the options in the remaining Filter list will be a subset of all possible rows. By selecting multiple filters, the results will be only those rows that meet all filtering criteria.

Set the appearance of the screened rows

You can customize the appearance of the row to show which rows are selected and which are excluded. Rows that meet the criteria are called "selected", and those that do not meet the criteria are called "excluded ". Filtering can hide excluded rows or change the appearance of selected and excluded rows. If you want to change the appearance, you can continue to display all data, highlight the rows that conform to certain standards. Then, you must define the style of the selected row and the style of the excluded row.

You can define a style by creating a NamedStyle object that contains all style settings. Then, when row filtering is applied to a column, you can reference the NamedStyle object effective for this filtering to make the specified style settings take effect.

In the following chart, based on the given code, the Gibson option in the filtering project sets the rows with filtering items as one appearance style, and the other rows as another appearance style.

650) this. width = 650; "style =" border-right-0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "title =" image "border =" 0 "alt =" image "width =" 611 "height =" 409 "src =" http://www.bkjia.com/uploads/allimg/131228/1T13UC3-4.png "/>

This shows how to use the code to start row filtering.

// Defines the style applied to the row sieve. FarPoint. Win. Spread. NamedStyle inStyle = new FarPoint. Win. Spread. NamedStyle (); FarPoint. Win. Spread. NamedStyle outStyle = new FarPoint. Win. Spread. NamedStyle (); InStyle. BackColor = Color. LightCyan; InStyle. ForeColor = Color. DarkRed; OutStyle. BackColor = Color. LemonChiffon; OutStyle. ForeColor = Color. Green; // Create a new filtering setting for the first column (use the default setting ). FarPoint. Win. Spread. FilterColumnDefinition fcdef = new FarPoint. Win. Spread. FilterColumnDefinition (0 ); // Create a StyleRowFilter object (style filter) and add it to the preceding filter settings. FarPoint. Win. Spread. StyleRowFilter styleFilter = new FarPoint. Win. Spread. StyleRowFilter (fpSpread1.ActiveSheet, inStyle, outStyle ); StyleFilter. AddColumn (fcdef ); // Set the created row filter object for the form. FpSpread1.ActiveSheet. RowFilter = styleFilter; // Filter data areas by text. FpSpread1.ActiveSheet. DefaultStyle. CellType = new FarPoint. Win. Spread. CellType. TextCellType (); FpSpread1.ActiveSheet. SetText (0, 0, "Fender "); FpSpread1.ActiveSheet. SetText (1, 0, "Gibson "); FpSpread1.ActiveSheet. SetText (2, 0, "Fender "); FpSpread1.ActiveSheet. SetText (3, 0, "Ibanez "); FpSpread1.ActiveSheet. SetText (4, 0, "Gibson "); FpSpread1.ActiveSheet. SetText (5, 0, "Yamaha "); FpSpread1.ActiveSheet. SetText (0, 1, "AST-100 DMC "); FpSpread1.ActiveSheet. SetText (1, 1, "Les Paul Standard Double Cut Plus "); FpSpread1.ActiveSheet. SetText (2, 1, "ST58-70TX "); FpSpread1.ActiveSheet. SetText (3, 1, "AGS83B "); FpSpread1.ActiveSheet. SetText (4, 1, "Les Paul Supreme "); FpSpread1.ActiveSheet. SetColumnWidth (0, 90 ); FpSpread1.ActiveSheet. SetColumnWidth (1,210 );

Define the content of the filtering project list

You can filter all rows in the form based on the content of the specified cell in the column. To filter such rows, you only need to complete the following simple steps:

Define filter conditions

Define the filtered result behavior to change the appearance or hide the row)

Define any custom filter

Apply this filter

Defines the filtering criteria for each column. This step is called the definition of column filtering. This filter condition is allocated to a single column based on the column content. Combine the conditions or filtering settings of these single columns into a collection.

If you want to define the appearance of the rows to be filtered, you can either define a selected style and an excluded style, or directly hide the excluded rows.

By setting related attributes in the DefaultRowFilter class, you can customize the display words of the following options in the drop-down list,

All-AllString Property

Balanks-balancer string Property

NonBlanks-NonBlanksString Property

Filters all or specific columns in a form and applies the Filter setting set to columns in the form ).

This shows how to use the code for row filtering.

1. Column Filtering Definition

2. Combine these definitions into a set

3. Define a style

4. Complete row Filtering

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

 

Appendix: Spread for Windows Forms Quick Start Series

Spread for Windows Forms Quick Start (1) --- getting started with Spread

Spread for Windows Forms Quick Start (2) --- setting Spread Forms

Spread for Windows Forms Quick Start (3) --- row and column operations

Spread for Windows Forms Quick Start (4) --- common cell types)

Spread for Windows Forms Quick Start (5) --- under common cell types)

Spread for Windows Forms Quick Start (6) --- define the appearance of cells

Spread for Windows Forms Quick Start (7) --- interactive operations on cells

Spread for Windows Forms Quick Start (8) --- events triggered by user actions in Cells

Spread for Windows Forms Quick Start (9) --- use Formulas

Spread for Windows Forms Quick Start (10) --- bind to database

This article from the "grape city control blog" blog, please be sure to keep this source http://powertoolsteam.blog.51cto.com/2369428/636549