C1FlexGrid: Excel Style Filtering

ComponentOne FlexGrid for Winforms has a rich object model that allows you to configure and control its various functionalities programmatically. C1FlexGrid has a built-in column filtering feature that is enabled using the AllowFiltering property. Once filtering is enabled, users can edit filters for each column by clicking the filter icon on the column headers. The Filter Editor allows users to set the filter based on values present in the column or by specifying conditions that must be met in order for the rows to be visible. Default behavior of the grid displays values in the DropDown Filter list from all the rows including those rows which have been filtered and removed from the display. In this blog, we'll customize this behavior to achieve Excel style Filtering. That is, if filter is being applied on a column, then Filter DropDown list will contain values from that column only for the visible rows in the grid. Binding the Grid To begin with refer to this Documentation link for binding a C1FlexGrid to a DataSource. Custom Filtering For customizing the filtering behavior in the Grid, we have to handle the button click events for the Filter Editor. To do so, capture the FilterEditorForm used for filtering and then handle the behavior of Apply & Clear buttons on that Form. The basic logic behind the implementation requires resetting the DataSource of the grid with fresh set of rows using the filters applied on the columns.

private void c1FlexGrid1_MouseDown(object sender, MouseEventArgs e)  
{  
    if (c1FlexGrid1.HitTest(e.X,e.Y).Type == C1.Win.C1FlexGrid.HitTestTypeEnum.FilterIcon)  
    {  
       // start the timer if the FilterIcon has been hit  
       tm.Start();  
    }  
}  

void tm_Tick(object sender, EventArgs e)  
{  
    tm.Stop();  
    foreach (Form f in Application.OpenForms)  
    {  
       if (f.Name == "FilterEditorForm" && f.GetType().ToString() == "C1.Win.C1FlexGrid.FilterEditorForm")  
       {  
           // Capture the Apply & Clear buttons on the filterdropdown form & assign Handlers to their Click Events  
           System.Windows.Forms.ToolStripButton clr_btn = ((ToolStripButton)((ToolStrip)f.Controls[0]).Items[1]);  
           System.Windows.Forms.ToolStripButton app_btn = ((ToolStripButton)((ToolStrip)f.Controls[0]).Items[2]);  
           clr\_btn.Click += new EventHandler(clr\_btn_Click);  
           app\_btn.Click += new EventHandler(app\_btn_Click);  
       }  
    }  
}  

Now, once the Apply or the Clear button is clicked, apply Custom Filtering :

// Handling Apply button's Click Event  
void app\_btn\_Click(object sender, EventArgs e)  
{  
    string filter_text = null;  
    for (int i = 0; i < c1FlexGrid1.Cols.Count; i++)  
    {  
        // check for which column the filter is currently active  
        if (c1FlexGrid1.Cols[ i ].Filter.IsActive == true)  
        {  
            object[] filter_values = ((C1.Win.C1FlexGrid.ColumnFilter)(this.c1FlexGrid1.Cols[ i ].Filter)).ValueFilter.ShowValues;  
            foreach (object value in filter_values)  
            {  
               // if the Column is Integer  
               if (c1FlexGrid1.Cols[ i ].DataType.ToString() == "System.Int32")  
               {  
                  // check for null values in the column  
                  if (value.ToString() == "")  
                      filter_text += c1FlexGrid1.Cols[ i ].Caption + " Is Null or ";  
                  else  
                      filter_text += c1FlexGrid1.Cols[ i ].Caption + "=" + value + " or ";  
               }  
               else        // columns with Datatype not as Integer  
               {  
                  // check for null values in the column  
                  if (value.ToString() == "")  
                     filter_text += c1FlexGrid1.Cols[ i ].Caption + " Is Null or ";  
                  else  
                     filter_text += c1FlexGrid1.Cols[ i ].Caption + " like '" + value + "' or ";  
               }  
            }  
            dv.RowFilter = filter\_text.Remove(filter\_text.Length - 3);    // removing the extra appended or from the filter_text  
            // Assign the filtered datasource to the Grid  
            c1FlexGrid1.DataSource = dv;  
         }  
     }  
}  

// Handling Clear button's Click Event  
void clr\_btn\_Click(object sender, EventArgs e)  
{  
     // Assign the default datasource to the Grid  
     c1FlexGrid1.DataSource = c1NWINDDataSet.Employees;  
}  

Refer to the attached samples for the detailed implementation : Download C# Sample Download VB Sample