Hi,
we try to implement Excel-Style Rowfiltering to a WinGrid.
When in Excel a column filter is applied (1) the other column filters only show visible values. In WinGrid (2) all possible values can be selected:
Is there a way to apply the excel filter Behaviour to WinGrid?
Thanks
Michael
Hello,
I far as I can see from your screen shot you are using UltraGridFilterUIProvider in order to display filter in UltraGrid. UltraFilterUiProvider evaluate its filter condition based on the column content not based on filtered row. I have research your scenario and couldn’t find a property that you could set to make UltraGridFilterUIProvider to evaluate its filter condition base on filtered row. But I have made some test with CreationFilter and handling some UltraGrid and UltraGridFilterUIProvider events, just to verify if your scenario is achievable with our components and I think that the result is good. Please look at the attached sample and let me know if this is what you are looking for.
Let me know if you have any further questions.
Hello Mike, hello Hristo,
thank you for yor replies.
Meanwhile I wrote following code which will work for me and provide the same filter style as excel does.
using System; using System.Linq; using Infragistics.Win; using Infragistics.Win.SupportDialogs.FilterUIProvider; using Infragistics.Win.UltraWinGrid; namespace IG.Shared.ExtensionMethods.InfragisticControls { public static class UltraGridExtension { #region Methods (4) // Public Static Methods (2) /// <summary> /// Applies the excel filter. /// </summary> /// <param name="source">The source.</param> /// <param name="filterProvider">The filter provider.</param> public static void ApplyExcelFilter(this UltraGrid source, UltraGridFilterUIProvider filterProvider) { source.DisplayLayout.Override.FilterUIProvider = filterProvider; source.BeforeRowFilterDropDown += ultraGridBeforeRowFilterDropDown; source.AfterRowFilterChanged += ultraGridAfterRowFilterChanged; } /// <summary> /// Removes the excel filter. /// </summary> /// <param name="source">The source.</param> public static void RemoveExcelFilter(this UltraGrid source) { source.DisplayLayout.Override.FilterUIProvider = null; source.BeforeRowFilterDropDown -= ultraGridBeforeRowFilterDropDown; source.AfterRowFilterChanged -= ultraGridAfterRowFilterChanged; } // Private Static Methods (2) /// <summary> /// Claims the list ultra grid after row filter changed. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The <see cref="Infragistics.Win.UltraWinGrid.AfterRowFilterChangedEventArgs"/> instance containing the event data.</param> private static void ultraGridAfterRowFilterChanged(object sender, AfterRowFilterChangedEventArgs e) { // sets id for filter colunm var grid = sender as UltraGrid; if (grid == null) { return; } // get id var filterId = grid.Rows.ColumnFilters.Cast<ColumnFilter>().Max(f => Int32.Parse(f.Tag == null ? "0" : f.Tag.ToString())); var currentColumn = grid.Rows.ColumnFilters[e.Column.Key]; // clear if (String.IsNullOrEmpty(currentColumn.ToString())) { currentColumn.Tag = null; } // set new if needed if (!String.IsNullOrEmpty(currentColumn.ToString()) && currentColumn.Tag == null) { currentColumn.Tag = filterId + 1; } } /// <summary> /// Claims the list ultra grid before row filter drop down. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The <see cref="Infragistics.Win.UltraWinGrid.BeforeRowFilterDropDownEventArgs"/> instance containing the event data.</param> private static void ultraGridBeforeRowFilterDropDown(object sender, BeforeRowFilterDropDownEventArgs e) { // implements excel 2007 style filtering var grid = sender as UltraGrid; if (grid == null) { return; } // as in excel still keep all options for the last added filtercondition var filterId = grid.Rows.ColumnFilters.Cast<ColumnFilter>().Max(f => Int32.Parse(f.Tag == null ? "0" : f.Tag.ToString())); var filterConditions = null as FilterCondition[]; if ((grid.Rows.ColumnFilters[e.Column.Key].Tag != null && grid.Rows.ColumnFilters[e.Column.Key].Tag.ToString() == filterId.ToString())) { filterConditions = new FilterCondition[grid.Rows.ColumnFilters[e.Column.Key].FilterConditions.Count]; grid.Rows.ColumnFilters[e.Column.Key].FilterConditions.CopyTo(filterConditions, 0); grid.Rows.ColumnFilters[e.Column.Key].ClearFilterConditions(); } // get default values to keep them and remove only databound not in grid var query = from i in e.ValueList.ValueListItems.All.OfType<ValueListItem>() where !(from r in grid.Rows where !r.IsFilteredOut select r).Any(r => r.Cells[e.Column].Text == i.DisplayText) // default values e.g. (All), (Blanks), ... && !(from iSub in e.ValueList.ValueListItems.All.OfType<ValueListItem>() where !(from r in grid.Rows select r.Cells[e.Column].Text).Contains(iSub.DisplayText) // databound values select iSub.DisplayText).Contains(i.DisplayText) select i; // now remove items foreach (var listItem in query.Distinct()) { e.ValueList.ValueListItems.Remove(listItem); } // put filter back if (filterConditions != null && filterConditions.Length > 0) { foreach (var filterCondition in filterConditions) { grid.Rows.ColumnFilters[e.Column.Key].FilterConditions.Add(filterCondition); } } } #endregion Methods } }
As Excel it keeps "all" possible values for the last added filter condition -> see attached screenshot.
Hi team
Im trying to use/understand the code, Im using it on VB ,net but having issues on the part of the query.can some one help me on the matter:Thanks
I have translated my sample to VB.NET, I hope that this will help you.
Thank you for using Infragistics Components.
Hi Hristo !!Thaks for the code, more than I expected, I will test the code.cheers