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 / Creating a Custom Filter
In This Topic
    Creating a Custom Filter
    In This Topic

    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:

    1. Create a class that inherits from FarPoint.Win.Spread.BaseFilterItem or FarPoint.Win.Spread.DefaultFilterItem.
    2. Override the DisplayName property to return the name to be displayed in the drop-down list of filter items.
    3. Override the ShowInDropDown method to specify if this filter item should be displayed in the drop-down list given the current filtered in rows.
    4. Override the Filter method to perform the filter action on the specified column.
    5. Override the Serialize and Deserialize methods. Make calls to the base.Serialize and base.Deserialize methods unless your methods handle persisting the default properties.
    6. Create a HideRowFilter or StyleRowFilter object.
    7. Add the custom filter to the custom filter’s list of the column filter definition in the row filtering object from the previous step.

    For more details, refer to these members:

    Example

    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)
    
    See Also