SpreadWinDemoVB app FilterAdvanced question

Posted by: echernyak on 8 September 2017, 2:28 pm EST

  • Posted 8 September 2017, 2:28 pm EST

    I am using custom filter based on the sample in SpreadWinDemoVB. It works fine except ShowInDropDown.  Code below adds two filters to the list: Yes and No. Spread has only one Row with value Yes. Code in ShowInDropDown() tries to eliminate a value from the filter list if it's not in the spread rows (call IsFilteredIn). My understanding is that No should not be in the filter list. Below is code to illustrate the problem:


      Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          Try
             PopulateSpread()


          Catch ex As Exception
             Throw ex
          End Try
       End Sub


    Private Sub PopulateSpread()
          Try
             Dim dtTest As New DataTable
             With dtTest
                .Columns.Add("Code", GetType(String))
                .Rows.Add("Yes")
             End With


             With FpSpread1.ActiveSheet
                .AutoGenerateColumns = False
                .DataAutoSizeColumns = False
                .DataAutoCellTypes = False


                .DataSource = dtTest.DefaultView


                .ColumnHeader.Cells(0, 0).Text = "Code"
                .BindDataColumn(0, dtTest.Columns("Code").ColumnName)


             End With


             SetTranslateFilter(FpSpread1.ActiveSheet, 0)


          Catch ex As Exception


          End Try
       End Sub
      
       Private Sub SetTranslateFilter(ByVal sheetView As FarPoint.Win.Spread.SheetView, _
                                   ByVal Col As Integer)
          Try
             Dim fcd As New FarPoint.Win.Spread.FilterColumnDefinition(Col, FarPoint.Win.Spread.FilterListBehavior.Custom)


             Dim rf As FarPoint.Win.Spread.IRowFilter = Nothing
             rf = New FarPoint.Win.Spread.HideRowFilter(sheetView)


             Dim fi As FarPoint.Win.Spread.BaseFilterItem = Nothing


             fi = New TranslateFilter(sheetView, "Yes")
             fcd.Filters.Add(fi)
             fi = New TranslateFilter(sheetView, "No")
             fcd.Filters.Add(fi)


             rf.AddColumn(fcd)


             sheetView.RowFilter = rf


          Catch ex As Exception


          End Try



       End Sub
      
       Public Class TranslateFilter
       Inherits FarPoint.Win.Spread.BaseFilterItem


       Dim sv As FarPoint.Win.Spread.SheetView = Nothing
       Dim Value As String = ""
       Dim objTradeLinksSupport As New Relius.Admin.Bus.Trade.TradeLinksSupport


       Public Sub New(ByVal sheetView As FarPoint.Win.Spread.SheetView, _
                      ByVal Value As String)
          Me.sv = sheetView
          Me.Value = Value
       End Sub


       Public Overrides ReadOnly Property DisplayName() As String
          Get
             Try
                Return Value
             Catch ex As Exception
                Return ""
             End Try


          End Get
       End Property


       Public Overrides WriteOnly Property SheetView() As FarPoint.Win.Spread.SheetView
          Set(ByVal Value As FarPoint.Win.Spread.SheetView)
             sv = Value
          End Set
       End Property


       Private Function IsFilteredIn(ByVal value As String) As Boolean
          If Not (value Is Nothing) And value = Me.Value Then
             Return True
          End If
          Return False
       End Function



       Public Overrides Function ShowInDropDown(ByVal columnIndex As Integer, ByVal filteredInRowList() As Integer) As Boolean
          If filteredInRowList Is Nothing Then
             Return True
          End If


          Try
             Dim i As Integer
             For i = 0 To filteredInRowList.Length - 1
                Dim row As Integer = filteredInRowList(i)
                Dim value As String = sv.GetText(row, columnIndex)
                If Not (value Is Nothing) And value.Length > 0 Then
                   If IsFilteredIn(value) Then
                      Return True
                   End If
                End If
             Next i
             Return False


          Catch ex As Exception
             Return False
          End Try
       End Function



       Public Overrides Function Filter(ByVal columnIndex As Integer) As Integer()
          Dim list As New ArrayList
          Dim returnList As Integer() = Nothing
          Try
             For row As Integer = 0 To sv.RowCount - 1
                Dim value As String = sv.GetText(row, columnIndex)
                If IsFilteredIn(value) Then
                   list.Add(row)
                End If
             Next row
             If list.Count > 0 Then
                returnList = New Integer(list.Count - 1) {}
                list.CopyTo(returnList)
                list.Clear()
             End If
             Return returnList


          Catch ex As Exception
             Return Nothing
          End Try
       End Function


       Public Overrides Function Serialize(ByVal w As System.Xml.XmlTextWriter) As Boolean
          Return True
       End Function


       Public Overrides Function Deserialize(ByVal r As System.Xml.XmlNodeReader) As Boolean
          Return True
       End Function
    End Class

  • Replied 8 September 2017, 2:28 pm EST

    I have tried your code and it seems to be working. The Filterlist in the combobox does not change as it shouldn't. Setting the filter effects what is displayed in the column you are filtering, ie. column 0 in your case. If you select "No" then nothing is displayed in the spread because the combobox defaults to displaying the top index "Yes" in your case so it is not displayed and there are no rowheaders displayed. If you select "Yes" then Yes is displayed and Rowheader 1 is displayed. You can see this more clearly if you add a few rows and a few yes' and No's in the column below the Combobox. If you selct Yes the the rows with Yes's are displayed including Row 1 if you select No then rows with No are displayed and row 1 is not because it defaults back to Yes.
  • Replied 8 September 2017, 2:28 pm EST

    I agree but this behavior is different from using default filter. If this code replaces using custom filter with (.AllowAutoFilter = True for this column) you will see that "No" would not be an option in the filter list since there is no such occurence in the datasource and there is no reason to place it there. At the same time, using custom filter doesn't eliminate this option in the filter list.


    Thank you


    EugC

  • Replied 8 September 2017, 2:28 pm EST

    I made the following changes to your ShowInDropDown Function and it no longer displays the No


    Public Overrides Function ShowInDropDown(ByVal columnIndex As Integer, ByVal filteredInRowList() As Integer) As Boolean


    'If filteredInRowList Is Nothing Then


    '    Return True


           'End If


     


            Try


            'Dim i As Integer


            'For i = 0 To filteredInRowList.Length - 1


            '    Dim row As Integer = filteredInRowList(i)


            '    Dim value As String = sv.GetText(row, columnIndex)


            '    If Not (value Is Nothing) And value.Length > 0 Then


            '        If IsFilteredIn(value) Then


            '            Return True


            '        End If


            '    End If


            'Next i


     


            For row As Integer = 0 To sv.RowCount - 1


                  Dim value As String = sv.GetText(row, columnIndex)


                  If IsFilteredIn(value) Then


                         Return True


                  End If


            Next row


     


            Return False


     


            Catch ex As Exception


                  Return False


            End Try


     


    End Function


     

  • Replied 8 September 2017, 2:28 pm EST

    Thank you, it works
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels