You can create a custom filter that you can then include in the column filter definition collection. In order to create a custom filter, follow these steps:
For more details, refer to these members:
This example creates a custom filter that filters rows with values between 1000 and 5000 and sets the custom filter in the second column of the sheet.
C# |
Copy Code
|
---|---|
[Serializable()] public class CustomFilter : FarPoint.Win.Spread.BaseFilterItem { FarPoint.Win.Spread.SheetView sv = null; public CustomFilter() { } public override string DisplayName { // String to be displayed in the filter get { return "1000 to 5000"; } } public override FarPoint.Win.Spread.SheetView SheetView { set { sv = value; } } private bool IsNumeric(object ovalue) { System.Text.RegularExpressions.Regex _isNumber = new System.Text.RegularExpressions.Regex(@"^\-?\d+\.?\d*$"); System.Text.RegularExpressions.Match m = _isNumber.Match(Convert.ToString(ovalue)); return m.Success; } public bool IsFilteredIn(object ovalue) { bool ret = false; if (IsNumeric(ovalue)) { if (Double.Parse(Convert.ToString(ovalue)) >= 1000 && Double.Parse(Convert.ToString(ovalue)) <= 5000) ret = true; } return ret; } public override bool ShowInDropDown(int columnIndex, int[] filteredInRowList) { // filteredInRowList argument is displayed in the item list only when // there is data that meets the filtered in row list condition. if (filteredInRowList == null) { for (int i = 0; i < sv.RowCount; i++) { object value = sv.GetValue(i, columnIndex); if (value != null) { if (IsFilteredIn(value)) return true; } } } else { // Check if the current row list meets the condition for (int i = 0; i < filteredInRowList.Length; i++) { int row = filteredInRowList[i]; object value = sv.GetValue(row, columnIndex); if (value != null) { if (IsFilteredIn(value)) return true; } } } return false; } public override int[] Filter(int columnIndex) { System.Collections.ArrayList ar = new System.Collections.ArrayList(); object val; for (int i = 0; i < sv.RowCount; i++) { val = sv.GetValue(i, columnIndex); if (IsFilteredIn(val)) ar.Add(i); // Add row numbers to the list that match the conditions } return (Int32[])(ar.ToArray(typeof(Int32))); } public override bool Serialize(System.Xml.XmlTextWriter w) { w.WriteStartElement("CustomFilter"); base.Serialize(w); w.WriteEndElement(); return true; } public override bool Deserialize(System.Xml.XmlNodeReader r) { if (r.NodeType == System.Xml.XmlNodeType.Element) { if (r.Name.Equals("CustomFilter")) { base.Deserialize(r); } } return true; } } // Write the following in the code-behind. // Create a filter column definition in the second column. FarPoint.Win.Spread.FilterColumnDefinition fcd1 = new FarPoint.Win.Spread.FilterColumnDefinition(1, FarPoint.Win.Spread.FilterListBehavior.Custom | FarPoint.Win.Spread.FilterListBehavior.Default); // Add custom filter to column definition. fcd1.Filters.Add(new CustomFilter() { SheetView = fpSpread1.Sheets[0] }); // Create hidden filter. FarPoint.Win.Spread.HideRowFilter hideRowFilter = new FarPoint.Win.Spread.HideRowFilter(fpSpread1.Sheets[0]); hideRowFilter.AddColumn(fcd1); fpSpread1.Sheets[0].RowFilter = hideRowFilter; // Set test data. fpSpread1.Sheets[0].SetValue(0, 1, 999); fpSpread1.Sheets[0].SetValue(1, 1, 1000); fpSpread1.Sheets[0].SetValue(2, 1, 5000); |
Visual Basic |
Copy Code
|
---|---|
<Serializable> Public Class CustomFilter Inherits FarPoint.Win.Spread.BaseFilterItem Private sv As FarPoint.Win.Spread.SheetView = Nothing Public Sub New() End Sub Public Overrides ReadOnly Property DisplayName() As String ' String to be displayed in the filter Get Return "1000 to 5000" End Get End Property Public Overrides WriteOnly Property SheetView() As FarPoint.Win.Spread.SheetView Set sv = Value End Set End Property Private Function IsNumeric(ovalue As Object) As Boolean Dim _isNumber As New System.Text.RegularExpressions.Regex("^\-?\d+\.?\d*$") Dim m As System.Text.RegularExpressions.Match = _isNumber.Match(Convert.ToString(ovalue)) Return m.Success End Function Public Function IsFilteredIn(ovalue As Object) As Boolean Dim ret As Boolean = False If IsNumeric(ovalue) Then If [Double].Parse(Convert.ToString(ovalue)) >= 1000 AndAlso [Double].Parse(Convert.ToString(ovalue)) <= 5000 Then ret = True End If End If Return ret End Function Public Overrides Function ShowInDropDown(columnIndex As Integer, filteredInRowList As Integer()) As Boolean ' filteredInRowList argument is displayed in the item list only when ' there is data that meets the filtered in row list condition. If filteredInRowList Is Nothing Then For i As Integer = 0 To sv.RowCount - 1 Dim value As Object = sv.GetValue(i, columnIndex) If value IsNot Nothing Then If IsFilteredIn(value) Then Return True End If End If Next Else ' Check if the current row list meets the condition For i As Integer = 0 To filteredInRowList.Length - 1 Dim row As Integer = filteredInRowList(i) Dim value As Object = sv.GetValue(row, columnIndex) If value IsNot Nothing Then If IsFilteredIn(value) Then Return True End If End If Next End If Return False End Function Public Overrides Function Filter(columnIndex As Integer) As Integer() Dim ar As New System.Collections.ArrayList() Dim val As Object For i As Integer = 0 To sv.RowCount - 1 val = sv.GetValue(i, columnIndex) If IsFilteredIn(val) Then ar.Add(i) ' Add row numbers to the list that match the conditions End If Next Return DirectCast(ar.ToArray(GetType(Int32)), Int32()) End Function Public Overrides Function Serialize(w As System.Xml.XmlTextWriter) As Boolean w.WriteStartElement("CustomFilter") MyBase.Serialize(w) w.WriteEndElement() Return True End Function Public Overrides Function Deserialize(r As System.Xml.XmlNodeReader) As Boolean If r.NodeType = System.Xml.XmlNodeType.Element Then If r.Name.Equals("CustomFilter") Then MyBase.Deserialize(r) End If End If Return True End Function End Class ' Write the following in the code-behind. ' Create a filter column definition in the second column. Dim fcd1 As New FarPoint.Win.Spread.FilterColumnDefinition(1, FarPoint.Win.Spread.FilterListBehavior.Custom Or FarPoint.Win.Spread.FilterListBehavior.Default) ' Add custom filter to column definition. fcd1.Filters.Add(New CustomFilter() With {.SheetView = FpSpread1.Sheets(0)}) ' Create hidden filter. Dim hideRowFilter As New FarPoint.Win.Spread.HideRowFilter(FpSpread1.Sheets(0)) hideRowFilter.AddColumn(fcd1) FpSpread1.Sheets(0).RowFilter = hideRowFilter ' Set test data. FpSpread1.Sheets(0).SetValue(0, 1, 999) FpSpread1.Sheets(0).SetValue(1, 1, 1000) FpSpread1.Sheets(0).SetValue(2, 1, 5000) |