Spread Windows Forms 13.0 Product Documentation
FarPoint.Win.Spread Assembly / FarPoint.Win.Spread Namespace / BaseFilterItem Class / Filter Method
Column index
Example


In This Topic
    Filter Method (BaseFilterItem)
    In This Topic
    Filters the specified column of the sheet.
    Syntax
    'Declaration
     
    Public Overridable Function Filter( _
       ByVal columnIndex As Integer _
    ) As Integer()
    'Usage
     
    Dim instance As BaseFilterItem
    Dim columnIndex As Integer
    Dim value() As Integer
     
    value = instance.Filter(columnIndex)
    public virtual int[] Filter( 
       int columnIndex
    )

    Parameters

    columnIndex
    Column index

    Return Value

    Integer array of filter item indexes
    Example
    This example creates a custom filter.
    [Serializable()]
    
            //Create a subclass inheriting BaseFilterItem class.
            public class CustomFilter : FarPoint.Win.Spread.BaseFilterItem
            {
                
                FarPoint.Win.Spread.SheetView sv = null;
                public CustomFilter()
                {
                }
    
                public CustomFilter(FarPoint.Win.Spread.SheetView sheetView)
                {
                    this.sv = sheetView;
                }
    
                //---------------------------------------------------------
                //Return names to be displayed in the drop-down list.
                //---------------------------------------------------------
                public override string DisplayName
                {
                    get { return "Extract any values that are greater than 10 and less than 50."; }
                }
    
                //-----------------------------------------------------------------------------------------
                //Set sheets to filters.
                //-----------------------------------------------------------------------------------------
                public override FarPoint.Win.Spread.SheetView SheetView
                {
                    set { sv = value; }
                }
    
                //-----------------------------------------------------------------------------------------
                //Evaluate specified values by conditions.
                //-----------------------------------------------------------------------------------------
                public bool IsFilteredIn(int value)
                {
                    if (!(value == null) & (value >= 10) & (value <= 50))
                    {
                        //Return True only when the following conditions are satisfied.
                        //(1)Values are entered.
                        //(2)Values are not lower than 10.
                        //(3)Values are not greater than 50.
                        return true;
                    }
                    return false;
                }
    
                //-----------------------------------------------------------------------------------------
                //Display names returned by DisplayName property.
                //-----------------------------------------------------------------------------------------
                public override bool ShowInDropDown(int columnIndex, int[] filteredInRowList)
                {
                    return true;
                }
    
                //-----------------------------------------------------------------------------------------
                //Execute filtering in the column specified in a sheet.
                //-----------------------------------------------------------------------------------------
                public override int[] Filter(int columnIndex)
                {
    
                    System.Collections.ArrayList list = new System.Collections.ArrayList();
                    int[] returnList = null;
                    int row = 0;
    
                    if ((sv == null))
                    {
                        return returnList;
                    }
    
                    for (row = 0; row <= sv.RowCount - 1; row++)
                    {
                        if (!(sv.GetValue(row, columnIndex) == null))
                        {
                            int value = (int)sv.GetValue(row, columnIndex);
                            if (IsFilteredIn(value))
                            {
                                //Add row indexes that meet conditions sequentially.
                                list.Add(row);
                            }
                        }
                    }
    
                    //When there are any rows that meet conditions, copy them to arrays.
                    if (list.Count > 0)
                    {
                        returnList = new int[list.Count];
                        list.CopyTo(returnList);
                        list.Clear();
                    }
    
                    //Return all row indexes that meet conditions.
                    return returnList;
    
                }
    
                //-----------------------------------------------------------------------------------------
                //When any data that has to be serialized is included in a custom filter class, override it.
                //-----------------------------------------------------------------------------------------
                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;
    
                }
    
            }
    
    
    private void Form1_Load(object sender, EventArgs e)
            {           
        //Display only custom filters created in Column1.
       FarPoint.Win.Spread.FilterColumnDefinition fcd = new FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom);
       FarPoint.Win.Spread.HideRowFilter hf = new FarPoint.Win.Spread.HideRowFilter(fpSpread1.ActiveSheet);
       hf.AddColumn(fcd);
       fpSpread1.ActiveSheet.RowFilter = hf;
    
       //Add the custom filter created for Column1.
       CustomFilter cfi = new CustomFilter(fpSpread1.ActiveSheet);
       FarPoint.Win.Spread.FilterColumnDefinition ccd = fpSpread1.ActiveSheet.RowFilter.GetFilterColumnDefinition(0);
       ccd.Filters.Add(cfi);
    
       fpSpread1.ActiveSheet.DefaultStyle.CellType = new FarPoint.Win.Spread.CellType.NumberCellType();
       fpSpread1.ActiveSheet.SetValue(0, 0, 10);
       fpSpread1.ActiveSheet.SetValue(1, 0, 100);
       fpSpread1.ActiveSheet.SetValue(2, 0, 50);
       fpSpread1.ActiveSheet.SetValue(3, 0, 40);
       fpSpread1.ActiveSheet.SetValue(4, 0, 80);
       fpSpread1.ActiveSheet.SetValue(5, 0, 1);
       fpSpread1.ActiveSheet.SetValue(6, 0, 65);
       fpSpread1.ActiveSheet.SetValue(7, 0, 20);
       fpSpread1.ActiveSheet.SetValue(8, 0, 30);
       fpSpread1.ActiveSheet.SetValue(9, 0, 35);   
            }
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
       'Display only custom filters created in Column1.
       Dim fcd As New FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom)
       Dim hf As New FarPoint.Win.Spread.HideRowFilter(FpSpread1.ActiveSheet)
       hf.AddColumn(fcd)
       FpSpread1.ActiveSheet.RowFilter = hf
    
       'Add the custom filter created for Column1.
       Dim cfi As New CustomFilter(FpSpread1.ActiveSheet)
       Dim ccd As FarPoint.Win.Spread.FilterColumnDefinition = FpSpread1.ActiveSheet.RowFilter.GetFilterColumnDefinition(0)
       ccd.Filters.Add(cfi)
    
       FpSpread1.ActiveSheet.DefaultStyle.CellType = New FarPoint.Win.Spread.CellType.NumberCellType
       FpSpread1.ActiveSheet.SetValue(0, 0, 10)
       FpSpread1.ActiveSheet.SetValue(1, 0, 100)
       FpSpread1.ActiveSheet.SetValue(2, 0, 50)
       FpSpread1.ActiveSheet.SetValue(3, 0, 40)
       FpSpread1.ActiveSheet.SetValue(4, 0, 80)
       FpSpread1.ActiveSheet.SetValue(5, 0, 1)
       FpSpread1.ActiveSheet.SetValue(6, 0, 65)
       FpSpread1.ActiveSheet.SetValue(7, 0, 20)
       FpSpread1.ActiveSheet.SetValue(8, 0, 30)
       FpSpread1.ActiveSheet.SetValue(9, 0, 35)
     End Sub 
    
    
    'Custom filter class
    
     <Serializable()> Public Class CustomFilter
    
       'Create a sub-class inheriting BaseFilterItem class.
       Inherits FarPoint.Win.Spread.BaseFilterItem
    
       Dim sv As FarPoint.Win.Spread.SheetView = Nothing
    
       Public Sub New()
       End Sub
    
       Public Sub New(ByVal sheetView As FarPoint.Win.Spread.SheetView)
          Me.sv = sheetView
       End Sub
    
       '---------------------------------------------------------
       'Return names to be displayed in the drop-down list.
       '---------------------------------------------------------
       Public Overrides ReadOnly Property DisplayName() As String
          Get
             Return "Extract any values that are greater than 10 and less than 50."
          End Get
       End Property
    
       '-----------------------------------------------------------------------------------------
       'Set sheets to filters.
       '-----------------------------------------------------------------------------------------
       Public Overrides WriteOnly Property SheetView() As FarPoint.Win.Spread.SheetView
          Set(ByVal Value As FarPoint.Win.Spread.SheetView)
             sv = Value
          End Set
       End Property
    
       '-----------------------------------------------------------------------------------------
       'Evaluate specified values by particular conditions.
       '-----------------------------------------------------------------------------------------
       Public Function IsFilteredIn(ByVal value As Integer) As Boolean
          If Not (value = Nothing) And (value >= 10) And (value <= 50) Then
             'Return True only when the following conditions are satisfied.
             '(1)Values are entered.
             '(2)Values are not lower than 10.
             '(3)Values are not greater than 50.
             Return True
          End If
          Return False
       End Function
    
       '-----------------------------------------------------------------------------------------
       'Display names returned by DisplayName property.
       '-----------------------------------------------------------------------------------------
       Public Overrides Function ShowInDropDown(ByVal columnIndex As Integer, ByVal filteredInRowList() As Integer) As Boolean
          Return True
       End Function
    
       '-----------------------------------------------------------------------------------------
       'Execute filtering in the column specified in a sheet.
       '-----------------------------------------------------------------------------------------
       Public Overrides Function Filter(ByVal columnIndex As Integer) As Integer()
    
          Dim list As New ArrayList
          Dim returnList As Integer() = Nothing
          Dim row As Integer
    
          If (sv Is Nothing) Then
             Return returnList
          End If
    
          For row = 0 To sv.RowCount - 1
             If Not (sv.GetValue(row, columnIndex) = Nothing) Then
                Dim value As Integer = sv.GetValue(row, columnIndex)
                If IsFilteredIn(value) Then
                   'Add row indexes that meet conditions sequentially.
                   list.Add(row)
                End If
             End If
          Next row
    
          'When there are any rows that meet conditions, copy them to arrays.
          If list.Count > 0 Then
             returnList = New Integer(list.Count - 1) {}
             list.CopyTo(returnList)
             list.Clear()
          End If
    
          'Return all row indexes that meet conditions.
          Return returnList
    
       End Function
    
       '-----------------------------------------------------------------------------------------
       'When any data that has to be serialized is included in a custom filter class, override it.
       '-----------------------------------------------------------------------------------------
       Public Overrides Function Serialize(ByVal w As System.Xml.XmlTextWriter) As Boolean
    
          w.WriteStartElement("CustomFilter")
          MyBase.Serialize(w)
          w.WriteEndElement()
          Return True
    
       End Function
    
       Public Overrides Function Deserialize(ByVal 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 
    See Also