I would like to use autofilter to get the numbers > 500 from a column(column has with both numbers and strings in cells). However, autofilter.Custom() returns the numbers greater than 500 but also returns all cells with strings.
Also, AutoFilter.MatchBlanks() and AutoFilter.MatchNonBlanks() can only be applied seperately from the autofilter.custom(). For instance, I would like to return cells with >500 or blanks fields. If I use autofilter.custom() and then matchblanks() it will only return the blanks cells(because it’s the last command executed) and the other way around. I would like to combine autofilter.custom() with either Matchblanks() or MatchNonBlanks() methods.
3)FilterTop10() method for bottomitems return blank cells as well(that’s not the case). It should return only the total amount of bottomitems with not including the blank cells.
FilterTop10() method for topitems return blanks cells as well which it shouldn’t and also returns always one more element. Also, for instance, If you want the 3 top items it will return blank cells and 4 top items instead of returning only the 3 top items with no blank cells.
Well, Aspose.Cells follows MS Excel standards and specifications implementing auto filter (MS Excel) feature. I tried a few scenarios as per your points in MS Excel manually and found MS Excel itself works as per Aspose.Cells as you described. If you think Aspose.Cells does not follow MS Excel standards regarding data filtering, then please create sample demos (code segment(s) - runnable) with template file(s) to show the issue, we will check it soon.
I did notice the issue as you talked about via screenshots and details. We will try to evaluate your issues in details. It would be better if you could share your template Excel file to exercise different auto-filter options. So, kindly provide the template file.
By the way, did you try to refresh auto-filters after applying custom filtering of data if it makes any difference?
e.g
Thanks for your immediate response. I use the Refresh() method as well but still nothing.
Please also find attached the template. You can modify as well to check all the different cases.
I have tested your scenario/case using your template file and following test cases, it works only for the first case similar to MS Excel (using our latest version/fix: v18.3.6). All other test cases give wrong results:
e.g Sample code:
Now, for the first scenario, have you tested the case where the column has both strings and numbers?
Because in my case even that case does not work. It returns correctly the cells with numbers greater than 500 but also returns all the cells with strings. Could you please test it again?
Thanks for providing further details on first scenario.
I am able to reproduce the issue for case1 as you have mentioned by choosing a column having text and numbers in it. I used the following code with your template file and found the issue as you described.
e.g Sample code:
1)
Workbook workbook = new Workbook("e:\\test2\\Template.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFilter.Range = "B6:K6";
worksheet.AutoFilter.Custom(4, FilterOperatorType.GreaterThan, 500);
worksheet.AutoFilter.Refresh();
workbook.Save("e:\\test2\\out1.xlsx");//issue occurred - It returns correctly the cells with numbers greater than 500 but also returns all the cells with strings.
I have logged the findings and updated your existing issue “CELLSNET-46029”. We will try to figure out all your test cases soon.
This is to inform you that we have fixed your issue CELLSNET-46029 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
I just tested the bug fixes and it seems to be fixed except the one with filtering the top items. The blank items are not returned (fixed) but it returns plus one item when applying filtertop10() for top items.
I confirmed that the blank items are not returned but it returns plus one item when applying FilterTop10() for top items. I have reopened your issue “CELLSNET-46029” again. We will look into it soon.
Just to inform you that your recent finding/issue (regarding case “4)” ) has been sorted out. Hopefully we will share the Download link for the new fix in the next few days.
Hi, i found another issue:
Imagine you want to autofilter the Page column and show rows with >80 or <i (“i” is a character). Excel discards that character and shows only the cells with >80
Please see below example:
Could you provide us your template Excel file with your desired filters applied to the specified column. Also paste your sample code (that you are using) with Aspose.Cells for .NET APIs. The sample code should be executable and similar to what I provided in the post: