Spread Windows Forms 10.0 Product Documentation
Filter Method (BaseFilterItem)
Example 


Column index
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 
Requirements

Target Platforms: Windows 2000 Professional (SP4), Windows 2000 Server, Windows 2003 Server (SP1), Windows Server 2012 R2, Windows 2008, Windows XP (SP2), Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows 10

See Also

Reference

BaseFilterItem Class
BaseFilterItem Members

 

 


Copyright © GrapeCity, inc. All rights reserved.