Hi
We are trying to apply filtering to spreadsheet columns. The filter is configured via Excel file (attached), which is loaded into the spreadsheet control as template. Most of cells are using the validation list but some not. Using applied filter results in incorrect found results on all sheets.
When we enter some word into the “Contains”, "Equals" or “Begins with” or any other conditions, we get a lot of non-matching values (or sometimes even all original values). However, after selecting the Cell and changing filter to "Filter by Selected Cell's value" all filtering starts working correctly. This additional filtering type should not be set every time when opening control with multiple work sheets and should be default.
EqDictTemplate_v2.6.xlsx
Hello Pachilova,
Suggestion did not worked as we expected since adding new row caused problems. However, we found a better solution to use SetRegions() as shown in example: https://es.infragistics.com/help/wpf/excelengine-filtering-worksheet-regions
Hello Tomas,
Thank you for the provided images. I have been looking into the described issue and I was able to reproduce this behavior on my side. Since Filtering in the XamSpreadsheet control is performed on tables, I added some test data and set the region of the headers and rows as a table in Excel (Insert -> Table). On my side, this solves the issue when the workbook is being loaded into the spreadsheet control and the filters work correctly.
Setting a region as a Table can be done in code-behind as well. For example, the following snippet sets the table for the specified region in the second worksheet of your sample excel file:
Workbook workbook1 = Workbook.Load("..\\..\\EqDictTemplate_v2.6.xlsx"); this.xamSpreadsheet1.Workbook = workbook1; workbook1.Worksheets[1].Tables.Add("A1:H6", true);
However, in this case, you would have to loop through all worksheets and set a correct region for each one.
My suggestion is to make sure that the required regions are specified as tables. Please, test this approach on your side and if you require any further assistance on the matter, please let me know.