ColumnView.FilterPopupExcelData Event

Allows you to add, remove, and modify data values and customize predefined filters in the Excel style pop-up filter menus. Filter items added manually on this event must be unique and sorted.

Namespace: DevExpress.XtraGrid.Views.Base

Assembly: DevExpress.XtraGrid.v24.1.dll

NuGet Packages: DevExpress.Win.Grid, DevExpress.Win.Navigation

Declaration

C#

[DXCategory("Behavior")]
public event FilterPopupExcelDataEventHandler FilterPopupExcelData

VB.NET

<DXCategory("Behavior")>
Public Event FilterPopupExcelData As FilterPopupExcelDataEventHandler

Event Data

The FilterPopupExcelData event's data class is FilterPopupExcelDataEventArgs. The following properties provide information specific to this event:

Property	Description
Column	Gets the column being processed. Inherited from ExcelFilteringDataEventArgs<TColumn>.
DataItems	Provides access to the collection of data values by which the column being processed can be filtered, and the corresponding display texts. Inherited from ExcelFilteringDataEventArgs.
DisplayTexts	Provides acces to the collection of the texts to be displayed in the filter popup for the corresponding data values by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.
FilterItems	Provides access to the collection of custom filter conditions by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.
HtmlImages	Gets or sets a collection of images to be inserted into filter item captions using HTML tags. This property is in effect when the HTML formatting feature is enabled for filter item captions. Inherited from ExcelFilteringDataEventArgs.
ImageAlignment	Gets or sets the alignment of images fetched from the column’s image combo box editor to the filter menu. For internal use. Inherited from ExcelFilteringDataEventArgs.
Images	Provides access to the collection of images fetched form the column’s image combo box editor to the filter menu. For internal use. Inherited from ExcelFilteringDataEventArgs.
IsInitialized	Gets whether these event arguments contain data values. Inherited from ExcelFilteringDataEventArgs.
IsNotLoaded	Gets or sets whether the data is not yet loaded during asynchronous data loading. Inherited from ExcelFilteringDataEventArgs.
Values	Provides access to the collection of data values by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.

The event data class exposes the following methods:

Method	Description
AddData(Object, String, Boolean)	Adds the specified data value by which the column being processed can be filtered, and the corresponding text to be displayed in the filter popup. Inherited from ExcelFilteringDataEventArgs.
AddFilter(String, CriteriaOperator, Boolean)	Adds the specified filter condition by which the column being processed can be filtered, and the corresponding text to be displayed in the filter popup. Inherited from ExcelFilteringDataEventArgs.
AddFilter(String, String, Boolean)	Adds the specified filter condition by which the column being processed can be filtered, and the corresponding text to be displayed in the filter popup. Inherited from ExcelFilteringDataEventArgs.
ChangeText(Object, String)	Changes the display text in the filter popup for the specified data value. Inherited from ExcelFilteringDataEventArgs.
ClearData()	Removes all items from the collection of data values by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.
GetDisplayTexts()	Returns an array of strings representing captions for filters in the popup. Inherited from ExcelFilteringDataEventArgs.
GetFilterItems()	Returns the collection of custom filter conditions by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.
GetValues()	Returns an array of objects representing data values by which the column being processed can be filtered. Inherited from ExcelFilteringDataEventArgs.
RemoveData(Object)	Removes the specified data value by which the column being processed can be filtered from the filter popup. Inherited from ExcelFilteringDataEventArgs.

Examples

The following code snippet shows how to handle the FilterPopupExcelData event to add custom filters for particular columns.

C#

void gridView_FilterPopupExcelData(object sender, FilterPopupExcelDataEventArgs e) {
    string fieldName = e.Column.FieldName;
    if(e.Column == bcModification) {
        // Add predefined filters to the Filters tab.
        e.AddFilter("<image=A><nbsp>Automatic Transmission (6-speed)", "Contains([" + fieldName + "], '6A')", true);
        e.AddFilter("<image=A><nbsp>Automatic Transmission (8-speed)", "Contains([" + fieldName + "], '8A')", true);
        e.AddFilter("<image=M><nbsp>Manual Transmission (6-speed)", "Contains([" + fieldName + "], '6M')", true);
        e.AddFilter("<image=M><nbsp>Manual Transmission (7-speed)", "Contains([" + fieldName + "], '7M')", true);
        e.AddFilter("<image=V><nbsp>Variomatic Transmission", "Contains([" + fieldName + "], 'VA')", true);
        e.AddFilter("<b>Limited Edition</b>", "Contains([" + fieldName + "], 'Limited')", true);
        // Customize filter values in the Values tab.
        foreach(var item in e.DataItems) {
            if(item.Text.Contains("V6"))
                item.HtmlText = item.Text.Replace("V6", "<b>V6</b>");
            if(item.Text.Contains("V8"))
                item.HtmlText = item.Text.Replace("V8", "<b>V8</b>");
            if(item.Text.Contains("Limited"))
                item.HtmlText = "<image=Ltd><nbsp>" + item.Text;
        }
    }
    if(e.Column == bcMPGCity) { // 12-28
        e.AddFilter("Fuel Economy (<color=@Information>High</color>)", "[" + fieldName + "]>=25", true);
        e.AddFilter("Fuel Economy (<color=@Warning>Medium</color>)", "[" + fieldName + "]>15 AND [" + fieldName + "]<25", true);
        e.AddFilter("Fuel Economy (<color=@Critical>Low</color>)", "[" + fieldName + "]<=15", true);
    }
    if(e.Column == bcMPGHighway) { // 15-36
        e.AddFilter("Fuel Economy (<color=@Information>High</color>)", "[" + fieldName + "]>=20", true);
        e.AddFilter("Fuel Economy (<color=@Warning>Medium</color>)", "[" + fieldName + "]>20 AND [" + fieldName + "]<30", true);
        e.AddFilter("Fuel Economy (<color=@Critical>Low</color>)", "[" + fieldName + "]>=20", true);
    }
    if (e.Column == bcName) {
        // Add a custom filter value and its caption to the Values tab.
        e.AddData("Q5", "<b>Q5</b>", true);
        // Change the caption of an existing filter value.               
        e.ChangeText("Beetle", "Coccinelle");
        // Remove a particular filter value.
        e.RemoveData("Touareg");
        // You can also change filter properties according to a custom logic.
        foreach (var item in e.DataItems) {
            item.Text = item.Text.ToUpper();
        }
    }
}

VB.NET

Private Sub gridView_FilterPopupExcelData(ByVal sender As Object, ByVal e As FilterPopupExcelDataEventArgs) Handles bandedGridView1.FilterPopupExcelData
    Dim fieldName As String = e.Column.FieldName
    If e.Column Is bcModification Then
        ' Add predefined filters to the Filters tab.
        e.AddFilter("<image=A><nbsp>Automatic Transmission (6-speed)", "Contains([" & fieldName & "], '6A')", True)
        e.AddFilter("<image=A><nbsp>Automatic Transmission (8-speed)", "Contains([" & fieldName & "], '8A')", True)
        e.AddFilter("<image=M><nbsp>Manual Transmission (6-speed)", "Contains([" & fieldName & "], '6M')", True)
        e.AddFilter("<image=M><nbsp>Manual Transmission (7-speed)", "Contains([" & fieldName & "], '7M')", True)
        e.AddFilter("<image=V><nbsp>Variomatic Transmission", "Contains([" & fieldName & "], 'VA')", True)
        e.AddFilter("<b>Limited Edition</b>", "Contains([" & fieldName & "], 'Limited')", True)
        ' Customize filter values in the Values tab.
        For Each item In e.DataItems
            If item.Text.Contains("V6") Then
                item.HtmlText = item.Text.Replace("V6", "<b>V6</b>")
            End If
            If item.Text.Contains("V8") Then
                item.HtmlText = item.Text.Replace("V8", "<b>V8</b>")
            End If
            If item.Text.Contains("Limited") Then
                item.HtmlText = "<image=Ltd><nbsp>" & item.Text
            End If
        Next item
    End If
    If e.Column Is bcMPGCity Then ' 12-28
        e.AddFilter("Fuel Economy (<color=@Information>High</color>)", "[" & fieldName & "]>=25", True)
        e.AddFilter("Fuel Economy (<color=@Warning>Medium</color>)", "[" & fieldName & "]>15 AND [" & fieldName & "]<25", True)
        e.AddFilter("Fuel Economy (<color=@Critical>Low</color>)", "[" & fieldName & "]<=15", True)
    End If
    If e.Column Is bcMPGHighway Then ' 15-36
        e.AddFilter("Fuel Economy (<color=@Information>High</color>)", "[" & fieldName & "]>=30", True)
        e.AddFilter("Fuel Economy (<color=@Warning>Medium</color>)", "[" & fieldName & "]>20 AND [" & fieldName & "]<30", True)
        e.AddFilter("Fuel Economy (<color=@Critical>Low</color>)", "[" & fieldName & "]<=20", True)
    End If
    If e.Column Is bcName Then
        ' Add a custom filter value and its caption to the Values tab.
        e.AddData("Q5", "<b>Q5</b>", True)
        ' Change the caption of an existing filter value.            
        e.ChangeText("Beetle", "Coccinelle")
        ' Remove a particular filter value.
        e.RemoveData("Touareg")
        ' You can also change filter properties according to a custom logic.
        For Each item In e.DataItems
            item.Text = item.Text.ToUpper()
        Next item
    End If
End Sub

Note

Run Excel Style Filtering module in the XtraGrid MainDemo to see the complete example.

A default column filter menu contains data values available in the column. The code below shows how to populate a filter menu with custom values.

Note

The example uses the grid control. The vertical grid, tree list, and pivot grid controls provide a similar API.

In this example, the processed column contains comma-separated values that can be treated as individual tokens. The FilterPopupExcelData event allows you to populate the menu with custom tokens instead of the available data values.

C#

readonly static char[] separators = new char[] { ',', ' ' };

void OnFilterPopupExcelData(object sender, FilterPopupExcelDataEventArgs e) {
    // Create a collection of tokens based on data values.
    var tokens = new HashSet<string>();
    for(int i = 0; i < e.Values.Length; i++) {
        var parts = ((string)e.Values[i]).Split(separators, StringSplitOptions.RemoveEmptyEntries);
        for(int j = 0; j < parts.Length; j++)
            tokens.Add(parts[j]);
    }
    // Remove the default data values from the filter menu.
    e.ClearData();
    // Populate the menu with the created tokens.
    foreach(string t in tokens.OrderBy(x => x))
        e.AddData(t, t);
}

VB.NET

Private ReadOnly Shared separators() As Char = { ","c, " "c }

Private Sub OnFilterPopupExcelData(ByVal sender As Object, ByVal e As FilterPopupExcelDataEventArgs)
    ' Create a collection of tokens based on data values.
    Dim tokens = New HashSet(Of String)()
    For i As Integer = 0 To e.Values.Length - 1
        Dim parts = DirectCast(e.Values(i), String).Split(separators, StringSplitOptions.RemoveEmptyEntries)
        For j As Integer = 0 To parts.Length - 1
            tokens.Add(parts(j))
        Next j
    Next i
    ' Remove the default data values from the filter menu.
    e.ClearData()
    ' Populate the menu with the created tokens.
    For Each t As String In tokens.OrderBy(Function(x) x)
        e.AddData(t, t)
    Next t
End Sub

If you have populated the menu with custom tokens, you also must handle the following events:

• FilterPopupExcelQueryFilterCriteria — to convert the selected tokens to the corresponding filter criteria that should be applied to data (direct conversion). This conversion is processed when the user selects a token in the menu/applies the selected tokens/closes the menu.

  C#

  void OnFilterPopupExcelQueryFilterCriteria(object sender, FilterPopupExcelQueryFilterCriteriaEventArgs e) {
      var viewModel = e.Value as ICollectionValueViewModel<string>;
      var property = new OperandProperty(e.Path);
      var functions = viewModel.Values
          .Select(x => new FunctionOperator(FunctionOperatorType.Contains, property, x));
      e.FilterCriteria = CriteriaOperator.Or(functions);
  }
  

  VB.NET

  Private Sub OnFilterPopupExcelQueryFilterCriteria(ByVal sender As Object, ByVal e As FilterPopupExcelQueryFilterCriteriaEventArgs)
      Dim viewModel = TryCast(e.Value, ICollectionValueViewModel(Of String))
      Dim [property] = New OperandProperty(e.Path)
      Dim functions = viewModel.Values.Select(Function(x) New FunctionOperator(FunctionOperatorType.Contains, [property], x))
      e.FilterCriteria = CriteriaOperator.Or(functions)
  End Sub
  

• FilterPopupExcelParseFilterCriteria — to convert the applied filter criteria to the corresponding tokens that should be selected in the menu (reverse conversion). This conversion is processed when the user opens the menu.

  C#

  void OnFilterPopupExcelParseFilterCriteria(object sender, FilterPopupExcelParseFilterCriteriaEventArgs e) {
      var func = e.FilterCriteria as FunctionOperator;
      if(!ReferenceEquals(func, null))
          e.SetValue(((OperandValue)func.Operands[1]).Value);
      var group = e.FilterCriteria as GroupOperator;
      if(!ReferenceEquals(group, null)) {
          var values = group.Operands
              .OfType<FunctionOperator>()
              .Select(x => ((OperandValue)x.Operands[1]).Value);
          e.SetValues(values.ToArray());
      }
  }
  

  VB.NET

  Private Sub OnFilterPopupExcelParseFilterCriteria(ByVal sender As Object, ByVal e As FilterPopupExcelParseFilterCriteriaEventArgs)
      Dim func = TryCast(e.FilterCriteria, FunctionOperator)
      If Not ReferenceEquals(func, Nothing) Then
          e.SetValue(CType(func.Operands(1), OperandValue).Value)
      End If
      Dim group = TryCast(e.FilterCriteria, GroupOperator)
      If Not ReferenceEquals(group, Nothing) Then
          Dim values = group.Operands.OfType(Of FunctionOperator)().Select(Function(x) (CType(x.Operands(1), OperandValue)).Value)
          e.SetValues(values.ToArray())
      End If
  End Sub