Excel Like Filtering in Silverlight FlexGrid

You can easily filter columns in C1FlexGrid for Silverlight by using the C1FlexGridFilterService. This blog explains how you can implement Excel-like filtering in C1FlexGrid i.e show only those values in the Filter which are currently visible.

Setting up FlexGrid Filter Service

Setting up the FlexGrid filter is easy. You can do it either is xaml or in code. XAML :

<c1:C1FlexGrid x:Name="_flex" Grid.Row="2" >  

<!-- use C1FlexGridFilterService to add the filter -->  
<c1:C1FlexGridFilterService.FlexGridFilter>  
<c1:C1FlexGridFilter FilterApplied="C1FlexGridFilter_FilterApplied" />  
</c1:C1FlexGridFilterService.FlexGridFilter>  
</c1:C1FlexGrid>

Code :

var f = new C1FlexGridFilter(_flex);  
f.FilterApplied += C1FlexGridFilter_FilterApplied;

Set Filter Values

First of all, we need to handle the EditorOpened event of the filter. In this event, we would fetch the column where filtering is to be applied. Then we would fetch only the currently visible rows using C1FlexGrid.CollectionView property and get distinct values from the collection view for the current column.

C1FlexGridFilterService.GetFlexGridFilter(\_flex).EditorOpened += new EventHandler(MainPage\_EditorOpened);

Now, we would match the content of the checkboxes in the filter to the list of distinct values and remove the list items containing those checkboxes which do not match.

protected void SetFilterValues(string col)  
{  
    PagedCollectionView pagedView = (PagedCollectionView)_flex.CollectionView;  
    ObservableCollection<object> collection = new ObservableCollection<object>();  
    foreach (Product citem in pagedView)  
    {  
        collection.Add(citem.GetType().GetProperty(col).GetValue(citem, null));  
    }  

    var distinctValues = collection.Distinct();  
    ObservableCollection<double> doubleDistinctValues = new ObservableCollection<Double>();  
    ObservableCollection<string> intDistinctValues = new ObservableCollection<string>();  

    var firstValue = distinctValues.Where(a => a != null).FirstOrDefault();  
    if (firstValue.GetType() == typeof(Double))  
    {  
        foreach (double value in distinctValues)  
        {  
            doubleDistinctValues.Add((double)(Math.Round(value, 2)));  
        }  
    }  

    if (firstValue.GetType() == typeof(int))  
    {  
        foreach (int value in distinctValues)  
        {  
            intDistinctValues.Add(value.ToString());  
        }  
    }  

    int count = VisualTreeHelper.GetChildrenCount(C1FlexGridFilterService.GetFlexGridFilter(_flex).Editor);  
    if (count != 0)  
    {  
        var editor = C1FlexGridFilterService.GetFlexGridFilter(_flex).Editor;  
        var obj = VisualTreeHelper.GetChild(editor, 0);  
        var listbox = (System.Windows.Controls.ListBox)(((System.Windows.Controls.Grid)(((System.Windows.Controls.Grid)(((System.Windows.Controls.Grid)(((System.Windows.Controls.Border)obj).Child)).Children[0])).Children[0])).Children[1]);  

        listbox.Loaded += ((s, e1) =>  
        {  
            ObservableCollection<CheckBox> listboxItems = new ObservableCollection<CheckBox>();  
            for (int i = 0; i <= (listbox.Items.Count - 1); i++)  
            {  
                var chkbox = (CheckBox)listbox.Items[i];  
                if (firstValue.GetType() == typeof(double))  
                {  
                    if (doubleDistinctValues.Contains(Convert.ToDouble(chkbox.Content)))  
                        chkbox.Visibility = System.Windows.Visibility.Visible;  
                    else  
                        listboxItems.Add((CheckBox)(listbox.Items[listbox.Items.IndexOf(chkbox)]));  
                }  
                else if (firstValue.GetType() == typeof(int))  
                {  
                    if (intDistinctValues.Contains(chkbox.Content.ToString()))  
                        chkbox.Visibility = System.Windows.Visibility.Visible;  
                    else  
                        listboxItems.Add((CheckBox)listbox.Items[listbox.Items.IndexOf(chkbox)]);  
                }  
                else if (distinctValues.Contains(chkbox.Content))  
                {  
                    chkbox.Visibility = System.Windows.Visibility.Visible;  
                }  
                else  
                {  
                    listboxItems.Add((CheckBox)listbox.Items[listbox.Items.IndexOf(chkbox)]);  
                }  
            }  

            foreach (CheckBox item in listboxItems)  
            {  
                listbox.Items.Remove(item);  
            }  
        });  
    }  
}

Excel-Like Filtering

To make sure filtering occurs just like MS Excel, we would have to maintain a list of the filtered columns, adding/removing columns from it depending on the column's filter state in the FilterApplied event of C1FlexGridFilter. If filtering is applied to only one column, it's filter should display all the checkboxes with only those checkboxes checked which are currently visible in the grid, the rest should be unchecked. Now, if a second column is filtered, then the first column's filter should contain only those checkboxes which are currently visible in the grid and the rest should be hidden.

void C1FlexGridFilter_FilterApplied(object sender, EventArgs e)  
{  
    if (_flex != null)  
    {  
        string filter = C1FlexGridFilterService.GetFlexGridFilter(_flex).FilterDefinition;  
        string col = C1FlexGridFilterService.GetFlexGridFilter(_flex).Editor.Filter.Column.ColumnName;  
        int index = filter.Split('\\"').ToList().IndexOf(col);  
        if (index != -1)  
        {      
            if (!filteredColumns.Contains(col))  
                filteredColumns.Add(col);  
        }  
        else  
        {  
            filteredColumns.Remove(col);  
        }  
    }  
    UpdateStatusBar();  
}  

void MainPage_EditorOpened(object sender, EventArgs e)  
{  
    var flexEditor = C1FlexGridFilterService.GetFlexGridFilter(_flex).Editor;  
    string firstCol = null;  
    if (filteredColumns.Count > 0)  
    {  
        firstCol = filteredColumns[0];  
        string col = flexEditor.Filter.Column.ColumnName;  

        if (firstCol != null && firstCol != col)  
            SetFilterValues(col);  
        else if (filteredColumns.Count > 1)  
            SetFilterValues(col);  
    }  
}

Please download the sample for complete implementation. Download Sample C#