Need to review. Multi column filter

Question

PicHeader.png Save on D:\

Thanks for the review

Public Class Form6
    'Please add these objects with specific names
    'Me.ListBox1 = New System.Windows.Forms.ListBox()
    'Me.butLoad = New System.Windows.Forms.Button()
    'Me.dgv = New System.Windows.Forms.DataGridView()
    'Me.chkLstFilter = New System.Windows.Forms.CheckedListBox()
    'Me.chkSelectAll = New System.Windows.Forms.CheckBox()
    'Me.chkApendFilter = New System.Windows.Forms.CheckBox()
    'Me.butCls = New System.Windows.Forms.Button()
    'Me.txtFindFilter = New System.Windows.Forms.TextBox()
    'Me.butSortCls = New System.Windows.Forms.Button()
    'Me.butFilterCls = New System.Windows.Forms.Button()
    'Me.butApply = New System.Windows.Forms.Button()
    'Me.butCancel = New System.Windows.Forms.Button()
    'Me.butOk = New System.Windows.Forms.Button()
    'Me.lblTrueCount = New System.Windows.Forms.Label()
    'Me.chkLstId = New System.Windows.Forms.CheckedListBox()
    'Me.lblAdd = New System.Windows.Forms.Label()
    'Me.butSortAZ = New System.Windows.Forms.Button()
    'Me.butSortZA = New System.Windows.Forms.Button()

    Dim picHedearColumns As New Bitmap("d:\PicHeader.png") 'Need Header Picture Thank
    Public bitExit As Boolean
    Dim dtMain As New DataTable
    Dim VisibleFilterControls As Boolean
    Dim selectColumnsClick As Byte
    Structure sTags
        Dim SotrExpression As String
        Dim FilterExpression As String
        Dim SotrMode As Byte
        Dim Filters() As String
        Dim IdFilters() As Integer
        Dim Name As String
        Dim Type As String
        Dim CountRowsStart As Integer
        Dim iconHeaderStatus As Byte
        Dim ButtonPress As Boolean
    End Structure
    Structure sLevel
        Dim Filters() As Integer
        Dim FilterEx As String
        Dim Name As String
        Dim CountRowsStart As Integer
        Dim index As Byte
    End Structure
    Dim FilterLevel(-1) As sLevel
    Dim intLevel As Integer = -1

    Dim Arr1() As Integer
    Dim Arr2() As String
    Dim arrIds As String()
    Dim dt As New DataTable

    Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dgv.Location = New System.Drawing.Point(287, 2)
        chkLstFilter.Location = New System.Drawing.Point(12, 151)
        chkSelectAll.Location = New System.Drawing.Point(12, 128)
        chkApendFilter.Location = New System.Drawing.Point(88, 128)
        butCls.Location = New System.Drawing.Point(226, 102)
        txtFindFilter.Location = New System.Drawing.Point(12, 102)
        butSortCls.Location = New System.Drawing.Point(168, 15)
        butFilterCls.Location = New System.Drawing.Point(12, 72)
        butApply.Location = New System.Drawing.Point(99, 343)
        butCancel.Location = New System.Drawing.Point(12, 343)
        butOk.Location = New System.Drawing.Point(194, 343)
        lblTrueCount.Location = New System.Drawing.Point(12, 369)
        chkLstId.Location = New System.Drawing.Point(300, 273)
        lblAdd.Location = New System.Drawing.Point(85, 369)
        butSortAZ.Location = New System.Drawing.Point(12, 15)
        butSortZA.Location = New System.Drawing.Point(12, 42)

        dgv.Size = New System.Drawing.Size(628, 263)
        chkLstFilter.Size = New System.Drawing.Size(246, 184)
        chkSelectAll.Size = New System.Drawing.Size(70, 17)
        chkApendFilter.Size = New System.Drawing.Size(170, 17)
        butCls.Size = New System.Drawing.Size(24, 23)
        txtFindFilter.Size = New System.Drawing.Size(208, 23)
        butSortCls.Size = New System.Drawing.Size(90, 58)
        butFilterCls.Size = New System.Drawing.Size(121, 30)
        butApply.Size = New System.Drawing.Size(64, 23)
        butCancel.Size = New System.Drawing.Size(64, 23)
        butOk.Size = New System.Drawing.Size(64, 23)
        lblTrueCount.Size = New System.Drawing.Size(39, 13)
        chkLstId.Size = New System.Drawing.Size(108, 109)
        lblAdd.Size = New System.Drawing.Size(39, 13)
        butSortAZ.Size = New System.Drawing.Size(122, 28)
        butSortZA.Size = New System.Drawing.Size(122, 30)

        chkSelectAll.Text = "Select All"
        chkApendFilter.Text = "Add Current Selection To Filter"
        butFilterCls.Text = "Clear Filter"
        butCancel.Text = "Cancel"
        butOk.Text = "OK"
        butApply.Text = "Apply"
        butSortAZ.Text = "Sort By A-Z"
        butSortZA.Text = "Sort By Z-A"

        dtMain = Nothing
        dtMain = New DataTable
        dtMain.Columns.Clear()
        dtMain.Columns.Add("id", GetType(Integer))
        dtMain.PrimaryKey = New DataColumn() {dtMain.Columns("id")}
        dtMain.Columns.AddRange({New DataColumn("a"), New DataColumn("b"), New DataColumn("c"), New DataColumn("d")})
        Dim dr As DataRow = dtMain.NewRow
        dtMain.Rows.Clear()
        For a = 1 To 8
            dr = dtMain.NewRow
            Dim arrRow() = {a, IIf(a = 2 Or a = 4, Nothing, "a" & a), "b" & a, "c" & a, "d" & a}
            dr.ItemArray = arrRow
            dtMain.Rows.Add(dr)
        Next
        dgv.DataSource = dtMain
        For a = 0 To dgv.ColumnCount - 1
            Dim tags As New sTags
            dgv.Columns(a).SortMode = DataGridViewColumnSortMode.Programmatic
            tags.Name = dgv.Columns(a).Name
            dgv.Columns(a).Tag = tags
        Next
        dgv.Width = 600
    End Sub
    Private Sub drawHeaderColomns(statusFilterSort As Byte, e As DataGridViewCellPaintingEventArgs, Optional isReadOnly As Boolean = False, Optional isChildTable As Boolean = False, Optional ButtonPress As Boolean = False)

        Dim posi As Rectangle = e.CellBounds
        Dim srcRect As New RectangleF(17 * statusFilterSort, 0, 17, IIf(ButtonPress, 13, 17))
        Dim units As GraphicsUnit = GraphicsUnit.Pixel
        posi.X -= IIf(dgv.RightToLeft = RightToLeft.Yes, -1, 1)
        e.Paint(e.CellBounds, DataGridViewPaintParts.Background Or DataGridViewPaintParts.Border Or DataGridViewPaintParts.ContentForeground Or DataGridViewPaintParts.Focus)
        posi.Height = 17
        posi.Width = 17
        posi.X += IIf(dgv.RightToLeft = RightToLeft.Yes, 0, e.CellBounds.Width - 17)
        e.Graphics.DrawImage(picHedearColumns, posi, srcRect, units)

        posi.Height = 2
        posi.Y = 19
        srcRect.Height = 2
        srcRect.Y = IIf(isReadOnly, 19, 50)
        srcRect.X = 0
        e.Graphics.DrawImage(picHedearColumns, posi, srcRect, units)

        posi.Height = 2
        posi.Y = 17
        srcRect.Height = 2
        srcRect.Y = IIf(isChildTable, 17, 50)
        srcRect.X = 0
        e.Graphics.DrawImage(picHedearColumns, posi, srcRect, units)
    End Sub
    Private Sub dgv_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) Handles dgv.CellPainting
        If e.ColumnIndex >= 0 And e.RowIndex < 0 Then
            Dim tags As sTags = dgv.Columns(e.ColumnIndex).Tag
            drawHeaderColomns(tags.iconHeaderStatus, e, ButtonPress:=selectColumnsClick = e.ColumnIndex And VisibleFilterControls)
            e.Handled = True
        End If

    End Sub
    Private Sub dgv_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv.ColumnHeaderMouseClick
        Dim tags As sTags = dgv.Columns(selectColumnsClick).Tag
        selectColumnsClick = e.ColumnIndex
        Dim poCell As Rectangle = dgv.GetColumnDisplayRectangle(e.ColumnIndex, True)
        If (e.ColumnIndex = -1 And e.RowIndex = -1) Or IIf(dgv.RightToLeft = RightToLeft.No, ((poCell.Width - 17 > e.X) And (poCell.Width > e.X)), ((17 < e.X) And (poCell.Width > e.X))) Or (0 < e.Y And 17 < e.Y) Then
            ShowControlsFilterSort(False)
            Exit Sub
        End If
        Dim drSelect() As DataRow
        If FilterLevel Is Nothing OrElse FilterLevel.Length = 0 Then
            drSelect = dtMain.Select()
        ElseIf FilterLevel(FilterLevel.Length - 1).Name <> tags.Name Then
            drSelect = dtMain.Select(FilterLevel(FilterLevel.Length - 1).FilterEx)
        ElseIf FilterLevel.Length > 1 Then
            drSelect = dtMain.Select(FilterLevel(FilterLevel.Length - 2).FilterEx)
        Else
            drSelect = dtMain.Select()
        End If
        Dim bitHowBlank As Boolean = False
        Dim sttBlanks As String = ""
        Dim bitOnInFilter As Boolean = False
        Dim sttFilters As String = ""
        Dim dtM As New DataTable
        Dim columName As String = dtMain.Columns(e.ColumnIndex).ColumnName
        Dim distinctCounts As IEnumerable(Of String) = Nothing

        Dim drGroup = drSelect.GroupBy(Function(row) row(columName))
        Dim arrGroup = drGroup.ToArray
        Dim CountRows As Integer = arrGroup.Length - 1
        Dim arr(drSelect.Length - 1) As Integer
        Dim idxArr As Integer = -1
        Dim arrIds(CountRows) As String

        chkLstFilter.Items.Clear()
        chkLstId.Items.Clear()
        bitExit = True
        For a = 0 To CountRows
            Dim ss = arrGroup(a).ToArray
            Dim sttGetSub As String = ""
            For b = 0 To ss.Length - 1
                sttGetSub += IIf(sttGetSub = "", "", ",") & ss(b).ItemArray(0) '& " id:" & dd
                idxArr += 1
                arr(idxArr) = ss(b).ItemArray(0)
            Next
            arrIds(a) = sttGetSub

            If arrGroup(a).Key Is DBNull.Value Then
                bitHowBlank = True
                sttBlanks = sttGetSub
            Else
                'frmMenSortFilter.checkLstFilter.Items.Add(stt8 & " " & stt4)
                chkLstFilter.Items.Add(arrGroup(a).Key)
                chkLstId.Items.Add(sttGetSub)
                bitOnInFilter = (tags.Filters Is Nothing) OrElse Array.IndexOf(tags.Filters, arrGroup(a).Key) <> -1
                'bitOnInFilter = (tags.IdFilters Is Nothing) OrElse Array.IndexOf(tags.IdFilters, arrGroup(a).Key) <> -1
                chkLstFilter.SetItemChecked(chkLstFilter.Items.Count - 1, bitOnInFilter)
                chkLstId.SetItemChecked(chkLstId.Items.Count - 1, bitOnInFilter)

            End If

        Next
        If sttBlanks <> "" Then
            chkLstFilter.Items.Add("(Blanks)" & vbTab & vbTab)
            chkLstId.Items.Add(sttBlanks)
            bitHowBlank = (tags.Filters Is Nothing) OrElse Array.IndexOf(tags.Filters, "(Blanks)" & vbTab & vbTab) <> -1
            chkLstFilter.SetItemChecked(chkLstFilter.Items.Count - 1, bitHowBlank)
            chkLstId.SetItemChecked(chkLstId.Items.Count - 1, bitHowBlank)

        End If

        bitExit = False
        chkLstFilter_ItemCheck(Nothing, Nothing)
        arr.ToArray.Sort(arr)
        Arr1 = arr
        arrIds = arrIds
        ShowControlsFilterSort(Not VisibleFilterControls)
        tags.ButtonPress = VisibleFilterControls
        dgv.Columns(selectColumnsClick).Tag = tags

    End Sub
    Private Sub ShowControlsFilterSort(stateVisible As Boolean)
        VisibleFilterControls = stateVisible
        Dim FilterControls As Object() = {butApply, butCancel, butCls, butFilterCls,
            butOk, chkApendFilter, chkSelectAll, chkLstId, lblTrueCount, butSortAZ,
            butSortZA, butSortCls, txtFindFilter, chkLstFilter}
        For a = 0 To FilterControls.Length - 1
            FilterControls(a).Enabled = stateVisible
        Next
    End Sub
    Private Sub chkLstFilter_MouseUp(sender As Object, e As MouseEventArgs) Handles chkLstFilter.MouseUp
        chkLstFilter.CheckOnClick = True
    End Sub

    Private Sub chkLstFilter_MouseLeave(sender As Object, e As EventArgs) Handles chkLstFilter.MouseLeave
        chkLstFilter.CheckOnClick = False
    End Sub

    Private Sub chkLstFilter_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles chkLstFilter.ItemCheck
        If bitExit Then Exit Sub
        If e IsNot Nothing Then
            chkLstId.SetItemChecked(e.Index, e.NewValue)
            chkLstId.SelectedIndex = e.Index
        End If
        Dim chkState As CheckState
        Dim checkedItems As Integer = chkLstFilter.CheckedItems.Count
        If sender IsNot Nothing AndAlso e.NewValue = CheckState.Checked Then checkedItems += 1
        If sender IsNot Nothing AndAlso e.NewValue = CheckState.Unchecked Then checkedItems -= 1

        Select Case checkedItems
            Case 0
                chkState = CheckState.Unchecked
            Case chkLstFilter.Items.Count
                chkState = CheckState.Checked
            Case Else
                chkState = CheckState.Indeterminate
        End Select
        chkSelectAll.AutoCheck = False
        chkSelectAll.CheckState = chkState
        lblTrueCount.Text = IIf(lblTrueCount.BackColor = Color.LightGreen, checkedItems, chkLstFilter.Items.Count - checkedItems)
        butApply.Enabled = chkSelectAll.Checked
        butOk.Enabled = chkSelectAll.Checked
    End Sub

    Private Sub butApply_Click(sender As Object, e As EventArgs) Handles butApply.Click
        Dim tags As sTags = dgv.Columns(selectColumnsClick).Tag
        'checkListUpdate()
        Dim sttArray(chkLstId.CheckedItems.Count - 1)
        chkLstId.CheckedItems.CopyTo(sttArray, 0)
        Dim sttJoin As String = Join(sttArray, ",")
        ' Me.Text = Join(sss, ",")
        sttArray = Split(sttJoin, ",")
        Dim IntArray(sttArray.Length - 1) As Integer
        IntArray = Array.ConvertAll(sttArray, Function(stt) Integer.Parse(stt))
        Array.Sort(IntArray)

        Dim arrFilter(chkLstFilter.CheckedItems.Count - 1) As String
        chkLstFilter.CheckedItems.CopyTo(arrFilter, 0)
        tags.IdFilters = IntArray

        ReDim sttArray(chkLstId.Items.Count - 1)
        chkLstId.Items.CopyTo(sttArray, 0)
        sttArray = Split(Join(sttArray, ","), ",")
        tags.Filters = IIf(dt.Rows.Count = arrFilter.Length, Nothing, arrFilter)
        tags.CountRowsStart = dt.Rows.Count
        dgv.Columns(selectColumnsClick).Tag = tags
        SortFilter()
    End Sub
    Public Sub SortFilter()

        Dim tags As sTags = dgv.Columns(selectColumnsClick).Tag
        Text = ""
        If tags.Filters Is Nothing And FilterLevel.Length = 0 Then GoTo adame 'AndAlso FilterLevel IsNot Nothing AndAlso FilterLevel.Length = 0
        If FilterLevel.Length = 0 Then
            intLevel += 1
            ReDim Preserve FilterLevel(intLevel)
            FilterLevel(intLevel).Filters = tags.IdFilters
            FilterLevel(FilterLevel.Length - 1).Name = tags.Name
            FilterLevel(FilterLevel.Length - 1).CountRowsStart = tags.CountRowsStart
            FilterLevel(FilterLevel.Length - 1).index = selectColumnsClick
        ElseIf FilterLevel(FilterLevel.Length - 1).Name = tags.Name Then
            If FilterLevel(FilterLevel.Length - 1).CountRowsStart = tags.IdFilters.Length Then
                FilterLevel = FilterLevel.RemoveAt(FilterLevel.Length - 1)
                intLevel -= 1
                tags.iconHeaderStatus = 0
            Else
                FilterLevel(FilterLevel.Length - 1).Filters = tags.IdFilters
            End If


        ElseIf tags.Filters IsNot Nothing Then
            intLevel += 1
            ReDim Preserve FilterLevel(intLevel)
            FilterLevel(intLevel).Filters = tags.IdFilters
            FilterLevel(FilterLevel.Length - 1).Name = tags.Name
            FilterLevel(FilterLevel.Length - 1).CountRowsStart = tags.CountRowsStart
            FilterLevel(FilterLevel.Length - 1).index = selectColumnsClick


        End If
        For a = 0 To FilterLevel.Length - 1
            If FilterLevel(a).index = selectColumnsClick Then
                tags.iconHeaderStatus = 1
                Exit For
            End If
        Next
        If FilterLevel IsNot Nothing AndAlso FilterLevel.Length > 0 Then
            Text += FilterLevel(FilterLevel.Length - 1).Name & " Level: " & FilterLevel.Length & " "
        Else
            Text += " Level: " & FilterLevel.Length & " "
        End If

        ' Dim IntAllLenel As Integer = 0
        Dim ArrAll() As Integer = Nothing
adame:
        Dim sttFilterEx As String = ""
        Dim ienum As IEnumerable(Of Integer)
        If FilterLevel IsNot Nothing AndAlso FilterLevel.Length > 0 Then
            ienum = FilterLevel(FilterLevel.Length - 1).Filters.Distinct 'ArrAll.Distinct
            ArrAll = ienum.ToArray
            Array.Sort(ArrAll)
            sttFilterEx = "[id]>="
            sttFilterEx += ArrAll(0).ToString
            For a = 0 To ArrAll.Length - 1
                If a = ArrAll.Length - 1 Then
                    sttFilterEx += " And [Id]<=" & ArrAll(a).ToString
                Else
                    If Math.Abs(ArrAll(a) - ArrAll(a + 1)) = 1 Then
                    Else
                        sttFilterEx += " And [Id]<=" & ArrAll(a).ToString & " Or [id]>="
                        sttFilterEx += ArrAll(a + 1).ToString
                    End If
                End If
            Next
            tags.FilterExpression = sttFilterEx
            FilterLevel(FilterLevel.Length - 1).FilterEx = sttFilterEx
        End If
        Text += sttFilterEx
        dtMain.DefaultView.RowFilter = sttFilterEx
    End Sub

    Private Sub chkSelectAll_Click(sender As Object, e As EventArgs) Handles chkSelectAll.Click
        chkSelectAll.CheckState = If(chkSelectAll.CheckState = CheckState.Checked, CheckState.Unchecked, CheckState.Checked)
        bitExit = True
        ' butApply.Enabled = True
        For a = 0 To chkLstFilter.Items.Count - 1
            chkLstFilter.SetItemChecked(a, chkSelectAll.Checked)
            chkLstId.SetItemChecked(a, chkSelectAll.Checked)
        Next
        lblTrueCount.BackColor = IIf(chkSelectAll.CheckState = CheckState.Unchecked, Color.LightGreen, Color.LightBlue)
        ' lblTrueCount.BackColor = IIf(checkSelAll.CheckState = CheckState.Indeterminate, Me.BackColor, lblTrueCount.BackColor)
        lblTrueCount.Text = chkLstFilter.CheckedItems.Count
        bitExit = False
        butApply.Enabled = chkSelectAll.Checked
        butOk.Enabled = chkSelectAll.Checked
    End Sub

End Class

Answer 1

Hi @Mansour_Dalir ,

I don't know if I understand your question correctly.

Here is a code example that uses two CheckedListBoxes as filters, which you can refer to.

    Private dataTable As DataTable = New DataTable()

    Private Sub ApplyFilters()
        Dim filteredData As DataTable = dataTable.Clone()

        Dim selectedFilters1 As List(Of String) = GetSelectedFilters(CheckedListBox1)
        If selectedFilters1.Count > 0 Then
            For Each row As DataRow In dataTable.Rows
                Dim value As String = row("ID").ToString()
                If selectedFilters1.Contains(value) Then
                    filteredData.ImportRow(row)
                End If
            Next
        Else
            filteredData = dataTable.Copy()
        End If

        Dim selectedFilters2 As List(Of String) = GetSelectedFilters(CheckedListBox2)
        If selectedFilters2.Count > 0 Then
            Dim tempData As DataTable = filteredData.Clone()
            For Each row As DataRow In filteredData.Rows
                Dim value As String = row("Name").ToString()
                If selectedFilters2.Contains(value) Then
                    tempData.ImportRow(row)
                End If
            Next
            filteredData = tempData
        End If

        DataGridView1.DataSource = filteredData
        DataGridView1.Refresh()
    End Sub

    Private Function GetSelectedFilters(checkedListBox As CheckedListBox) As List(Of String)
        Dim selectedFilters As New List(Of String)()
        For i As Integer = 0 To checkedListBox.CheckedItems.Count - 1
            selectedFilters.Add(checkedListBox.CheckedItems(i).ToString())
        Next
        Return selectedFilters
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ApplyFilters()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dataTable.Columns.Add("ID")
        dataTable.Columns.Add("Name")
        dataTable.Rows.Add("1", "a")
        dataTable.Rows.Add("2", "b")
        dataTable.Rows.Add("3", "c")
        dataTable.Rows.Add("4", "d")
        dataTable.Rows.Add("5", "e")
        dataTable.Rows.Add("6", "f")
        DataGridView1.DataSource = dataTable
    End Sub

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.