Few questions for Autofilter command

Aspose.Cells Product Family
21

@ServerSide527,

I tested your scenario/ case a bit using your new file and following sample code segments with v18.7.4 (please try it), it works fine. Please find attached the output files for your reference.
Aspose.Cells18.7.4 For .Net2_AuthenticodeSigned.Zip (3.8 MB)
Aspose.Cells18.7.4 For .Net4.0.Zip (3.8 MB)
e.g
Sample code:

//1)        
Workbook workbook = new Workbook("e:\\test2\\test.xlsx");
        Worksheet worksheet = workbook.Worksheets[0];
        worksheet.AutoFilter.Range = "B5:K5";
        worksheet.AutoFilter.Custom(5, FilterOperatorType.Equal, "", false, FilterOperatorType.GreaterThan, 80);
        worksheet.AutoFilter.Refresh();

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

      //1.1
            Workbook workbook = new Workbook("e:\\test2\\test.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.AutoFilter.Range = "B5:K5";
            worksheet.AutoFilter.Custom(5, FilterOperatorType.GreaterThan, 80);
            worksheet.AutoFilter.Refresh();

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

You may open the output files into MS Excel manually and confirm these are Ok.
files1.zip (33.3 KB)

22

@ServerSide527,

I tried the code segment with your file and it returns nothing. If you still find the issue with v18.7.4, kindly do provide output file (by Aspose.Cells APIs) and your expected file (that you may create/update manually in MS Excel), we will check it soon.

23

Hi,

I’m using an older version of aspose cells but still, you can see even with the newer version it does not work properly.

if you use this code:

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

you will see that still does not work. It returns nothing, but it should return top 5.

24

@ServerSide527,

Thanks for the code segment.

I am able to reproduce the issue as you mentioned. Using the following sample code with your template file gives nothing, no filtered rows are displayed whereas it should return top 5 rows:
e.g
Sample code:

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

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

I have logged a ticket with an id “CELLSNET-46267”. We will try to figure out your issue soon.

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

25

@ServerSide527,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46267”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

26

Thanks for your reply and for fixing it.

Also, could you please have a look at this one

Workbook workbook = new Workbook(“e:\test2\test.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFilter.Range = “B5:K10”;
worksheet.AutoFilter.Custom(5, FilterOperatorType.GreaterThan, “80”, false, FilterOperatorType.LessThan, “i”);
worksheet.AutoFilter.Refresh();

Pleas see below screenshots:

NotWorking.PNG (31.2 KB)
works.PNG (29.4 KB)

27

@ServerSide527,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46278 - AutoFilter.Refresh does not update custom filters

28

@ServerSide527,

Please change the line of code as following, it should fix your issue:
e.g
Sample code:

worksheet.AutoFilter.Custom(5, FilterOperatorType.GreaterThan, 80, false, FilterOperatorType.LessThan, “i”);

Let us know your feedback.

29

How does this one not work?

worksheet.AutoFilter.Custom(5, FilterOperatorType.LessThan, “i”, false, FilterOperatorType.GreaterThan, 500);

And the other one works?

Thanks

30

@ServerSide527,

I have tried the following sample code and found it working fine. Please try the following sample code using latest version Aspose.Cells for .NET 18.7.6.

Workbook workbook = new Workbook(path + "test.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFilter.Range = "B5:K10";
worksheet.AutoFilter.Custom(5, FilterOperatorType.LessThan, "i", false, FilterOperatorType.GreaterThan, 500);
worksheet.AutoFilter.Refresh();
workbook.Save(path + "out3.xlsx");
31

The issues you have found earlier (filed as CELLSNET-46267) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

32

With the newest version works fine.

Have you fixed this one, please? CELLSNET-46278 - AutoFilter.Refresh does not update custom filters

Also please check this one:

Workbook workbook = new Workbook(“e:\test2\test.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFilter.Range = “B5:K10”;
worksheet.AutoFilter.Custom(5, FilterOperatorType.LessThan, “i”, true, FilterOperatorType.GreaterThan,500);
worksheet.AutoFilter.Refresh();

I should return nothing.

Thanks

33

@ServerSide527,

  1. Regarding CELLSNET-46278, solution was already provided in the previous post, where it was suggested that you may remove quotes from the value 80. Hence the statement shall be as

    worksheet.AutoFilter.Custom(5, FilterOperatorType.GreaterThan, 80, false, FilterOperatorType.LessThan, “i”);

  2. Regarding the following issue:

I have checked this scenario using Aspose.Cells for .NET 18.8 and Excel 2016 both separately by setting the same custom filter. It is observed that both do not return any row. Could you please give it a try using the latest version and share the feedback if any difference is observed in the behavior of Apose.Cells and Excel 2016?
AsposeOutput.png (51.5 KB)
Excel2016Output.png (61.0 KB)