Free Support Forum - aspose.com

Few questions for Autofilter command

Hi,

I have a few questions:

  1. 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.

  2. 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.

  1. 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.

Looking forward to hearing from you.

Thanks

@ServerSide527,

Thanks for the queries.

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.

Hi,

Please see below some examples:

1)
Aspose’s results: AsposeGreater500.png AND Excel Results: ExceLNumberGreater500.png
AsposeGreater500.PNG (5.2 KB)
ExceLNumberGreater500.PNG (1.7 KB)

Aspose Code:
activeworksheet.Autofilter.Custom(0,">",500)

2)
Aspose’s results: AsposeShowBlanksOrGreater500.png AND Excel Results: ExcelShowBlanksOrGreater500.png
AsposeShowBlanksOrGreater500.PNG (6.5 KB)
ExcelShowBlanksOrGreater500.PNG (2.3 KB)

Aspose Code:
activeworksheet.Autofilter.Custom(1,"=","",false,">",500)

3)
Aspose’s results: AsposeBottom5.png AND Excel Results: ExcelBottom5.png

AsposeBottom5.PNG (5.9 KB)
ExcelBottom5.PNG (3.6 KB)

Aspose Code:
activeworksheet.Autofilter.FilterTop10(1, false, false, 5);

4)

Aspose’s results: Aspose5TopItems.png AND Excel Results: Excel5TopItems.png

Aspose5TopItems.PNG (5.8 KB)
Excel5TopItems.PNG (3.0 KB)

Aspose Code:
activeworksheet.Autofilter.FilterTop10(1, true, false, 5);

In all above cases, the expected results should have been the results from all the Excel files.

Thanks,
Marios

@ServerSide527,

Thanks for the screenshots.

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

activeworksheet.AutoFilter.Refresh();

Hi,

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.

Template.zip (12.8 KB)

Thanks,
Marios

@ServerSide527,

Thanks for the template file.

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:

        1) 
        Workbook workbook = new Workbook("e:\\test2\\Template.xlsx"); 
        Worksheet worksheet = workbook.Worksheets[0]; 
        worksheet.AutoFilter.Range = "B6:K6"; 
        worksheet.AutoFilter.Custom(5, FilterOperatorType.GreaterThan, 500); 
        worksheet.AutoFilter.Refresh(); 

        workbook.Save("e:\\test2\\out1.xlsx");//Ok 
   

        2) 
        Workbook workbook = new Workbook("e:\\test2\\Template.xlsx"); 
        Worksheet worksheet = workbook.Worksheets[0]; 
        worksheet.AutoFilter.Range = "B6:K6"; 
        worksheet.AutoFilter.Custom(5, FilterOperatorType.Equal, "", false, FilterOperatorType.GreaterThan, 500); 
        worksheet.AutoFilter.Refresh(); 

        workbook.Save("e:\\test2\\out2.xlsx");//issue occurred 
      

        3) 
        Workbook workbook = new Workbook("e:\\test2\\Template.xlsx"); 
        Worksheet worksheet = workbook.Worksheets[0]; 
        worksheet.AutoFilter.Range = "B6:K6"; 
        worksheet.AutoFilter.FilterTop10(5, false, false, 5); 
        worksheet.AutoFilter.Refresh(); 

        workbook.Save("e:\\test2\\out3.xlsx");//issue occurred 
     

        4) 
        Workbook workbook = new Workbook("e:\\test2\\Template.xlsx"); 
        Worksheet worksheet = workbook.Worksheets[0]; 
        worksheet.AutoFilter.Range = "B6:K6"; 
        worksheet.AutoFilter.FilterTop10(5, true, false, 5); 
        worksheet.AutoFilter.Refresh(); 

        workbook.Save("e:\\test2\\out4.xlsx");//issue occurred  

I have logged a ticket with an id “CELLSNET-46029” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Hi,

Thanks for confirming the issues.

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,
Marios

@ServerSide527,

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.

@ServerSide527

Thanks for using Aspose APIs.

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.

Thank you for fixing the issue.

@ServerSide527,

Please try our latest version/fix: Aspose.Cells for .NET v18.3.8.

Your issue should be fixed in it.

Let us know your feedback.

Hi,

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.

Could you please review that again?

Thanks,
Marios

@ServerSide527,

You are right, The issue is fixed except for the last case, i.e., “4)”:
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.FilterTop10(5, true, false, 5); 
             worksheet.AutoFilter.Refresh(); 
    
             workbook.Save("e:\\test2\\out4.xlsx");//issue occurred 
    

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.

We are sorry for any inconvenience caused!

@ServerSide527,

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,

Thank you for sorting that out as well.

The issues you have found earlier (filed as CELLSNET-46029) have been fixed in Aspose.Cells for .NET 18.4. Please also check the document/article for your reference: https://docs.aspose.com/display/cellsnet/Installation

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:

ExpectedResult.PNG (13.7 KB)
AllSelected.PNG (45.9 KB)
Myresult.PNG (30.2 KB)

@ServerSide527,

Thanks for the screenshots.

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:


We will check it soon.

Could you please also check this rule?

80 or show blank cells. You fixed this in a previous version but it does not work now.

Test.zip (15.7 KB)

Also, if you apply the next rule on the column “Name”:

Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFilter.Range = “B6:K6”;
worksheet.AutoFilter.FilterTop10(5, true, false, 5);
worksheet.AutoFilter.Refresh();
worksheet.AutoFilter.FilterTop10(6, true, false, 2);

it should not return anything instead it returns all.