Multiselect Filtering in Spread for WinForms

Filtering is a very basic functionality of grid controls and this feature comes in-built for WinForm Spread. You can refer to this link that describes how the user can implement filtering in Spread For Winforms. This built in functionality provides the basic type of filtering i.e clicking on the Filter icon displayed in the column header gives you a list of values from which you can select a single item, and based on the selected value, column would be filtered. However, Excel provides advanced filtering by providing the user an option to filter the column by selecting multiple items from the filtering criteria. This functionality is currently not provided by Spread. This blog discusses how we can implement the similar Excel Functionality in Spread for Winforms. The basic approach is to define and display a separate Listbox containing unique values for the filtering column. Select the desired items from the Listbox, and based on the selection you could show or hide the rows that match the filter items criteria. Here are the various code snippets that describe the implementation of the above approach.

Populating ListBox with Unique Values for Filtering

private void button1_Click(object sender, EventArgs e)  
{  
  FilterForm f1 = new FilterForm();  
  listBox1.Items.Clear();  

  if (fpSpread1.ActiveSheet.ActiveColumnIndex != lastcol)  
  {  
     templist.Items.Clear();  
     for (int r = 0; r < fpSpread1.ActiveSheet.RowCount; r++)  
     {  
       if (fpSpread1.ActiveSheet.Rows[r].Visible)  
        {  
          if (listBox1.Items.Count > 0)  
          {  
            if (!listBox1.Items.Contains(fpSpread1.ActiveSheet.Cells[r, fpSpread1.ActiveSheet.ActiveColumnIndex].Value))  
            {  
              listBox1.Items.Add(fpSpread1.ActiveSheet.Cells[r, fpSpread1.ActiveSheet.ActiveColumnIndex].Value);  
              templist.Items.Add(fpSpread1.ActiveSheet.Cells[r, fpSpread1.ActiveSheet.ActiveColumnIndex].Value);  
            }  
          }  
         else  
         {  
           listBox1.Items.Add(fpSpread1.ActiveSheet.Cells[r, fpSpread1.ActiveSheet.ActiveColumnIndex].Value);  
           templist.Items.Add(fpSpread1.ActiveSheet.Cells[r, fpSpread1.ActiveSheet.ActiveColumnIndex].Value);  
         }  
       }  
    }  
   listBox1.Items.Add("Select All");  
   templist.Items.Add("Select All");  
   f1 = new FilterForm(listBox1, fpSpread1, this);  
  }  
  else  
  {  
    f1 = new FilterForm(templist, fpSpread1, this);  
  }  
 f1.Show();  
 f1.FormClosed += new FormClosedEventHandler(f1_FormClosed);  
 lastcol = fpSpread1.ActiveSheet.ActiveColumnIndex;  
}  

Retrieving Row Indexes to Apply The Filter

Once the items are added to the Listbox, the list is displayed to the user. As the user selects the items in the listbox and clicks on the filter button, we would need to get the row indexes of all the rows that match the filter items and alter the filter lists for the remaining columns depending on the visible rows in them. Following code explains, it in details:

private void button1_Click(object sender, EventArgs e)  
{  
  ListBox.SelectedIndexCollection sindexes = this.listBox1.SelectedIndices;  
  if (sindexes.Count == 1 && this.listBox1.SelectedItem.Equals("Select All"))  
  {  
    al.Add("Select All");  
  }  
  else  
  {  
    ListBox sortlist = new ListBox();  
    for (int i = 0; i < listBox1.SelectedItems.Count; i++)  
    {  
      for (int r = 0; r < tempSpread.ActiveSheet.RowCount; r++)  
      {  
        if (sortlist.Items.Count > 0)  
        {  
          if (!sortlist.Items.Contains(r))  
          {  
            if (tempSpread.ActiveSheet.Rows[r].Visible)  
            {  
              if (tempSpread.ActiveSheet.Cells[r, tempSpread.ActiveSheet.ActiveColumnIndex].Value.Equals(listBox1.SelectedItems[i]))  
               {  
                 tempSpread.ActiveSheet.Rows[r].Visible = true;  
                 sortlist.Items.Add(r);  
               }  
             }  
           }  
         }  
         else  
         {  
           if (tempSpread.ActiveSheet.Rows[r].Visible)  
           {  
             if (tempSpread.ActiveSheet.Cells[r, tempSpread.ActiveSheet.ActiveColumnIndex].Value.Equals(listBox1.SelectedItems[i]))  
             {  
               tempSpread.ActiveSheet.Rows[r].Visible = true;  
               sortlist.Items.Add(r);  
             }  
           }  
         }  
       }  
     }  
   for (int j = 0; j < tempSpread.ActiveSheet.RowCount; j++)  
   {  
     if (!sortlist.Items.Contains(j))  
       tempSpread.ActiveSheet.Rows[j].Visible = false;  
   }  
   for (int r = 0; r < tempSpread.ActiveSheet.RowCount; r++)  
   {  
     if (tempSpread.ActiveSheet.Rows[r].Visible)  
     {  
       al.Add(r);  
     }  
   }  
 }  
 parent.SetRows(al);  
 this.Close();  
}  

With the above implementation, Spread will display the filtered rows based on the multiple values selected from the ListBox. Refer to the attached sample for complete implementation. MultiSelectFilterCSharp MultiSelectFilterVB