Is there an equivalent to the "FilterMode" property of an Excel Worksheet object in Aspose.Cells?

Aspose.Cells Product Family
1

We are trying to determine if non-visible rows in a worksheet have been hidden directly by the user or because of filtering. We do different things based on why the rows are hidden.

In Excel, we’d check the “FilterMode” property of the Worksheet object to see if filtering is currently filtering anything - if nothing is hidden due to filtering, it returns false. However, I don’t see an equivalent in Aspose. Is there one?

On the Worksheet object in Aspose.Cells, there is the “HasAutofilter” property, which tells me if an auto-filter exists at all on the worksheet, but it doesn’t tell me if any items have been filtered, just that the auto-filter exists. There is also an “AutoFilter” property, where I can get the range affected by the filter, but I don’t see any way to indicate if anything has been filtered.

Is there a way to do what I’m trying to do with Aspose.Cells?

Thanks,
Michael Whalen

2

@mwhalen,

Thanks for the details.

I think you are talking about this Boolean property to be supported in Aspose.Cells APIs:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-filtermode-property-excel

I have logged a ticket (for the feature) for your requirements as following:

  • CELLSNET-46007 - Add an equivalent property to the “FilterMode” property of Excel Worksheet object (VBA)

We will look into it soon. Once we have an update on it, we will let you know here.

3

@mwhalen,

This is to inform you that we have fixed your issue “CELLSNET-46007” now. We will soon provide the fix after performing QA and including other enhancements and fixes.

4

@mwhalen

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

5

Thanks for the quick turnaround on this, but I can’t spot the new property in the object model. Is there supposed to be a Worksheet.FilterMode property now, or is the property accessed some other way?

Thanks again,
Michael Whalen

6

@mwhalen

Please provide the sample XLSX/XLSM file using the Worksheet.FilterMode property for our investigation. We will experiment it and help you asap.

7

Samples.zip (18.5 KB)
Attached is a zip file with two xlsx files included. If you open them in Excel, you’ll notice they’re almost identical - there are two columns and nine rows of data, with rows 3, 4, and 5 hidden, and both of the columns have auto-filters applied. The only visible difference should be the little filter icon in the drop down arrow of the filter on column B - Sample2 has that icon, and Sample1 doesn’t.

In Sample1, the rows were hidden directly - the rows were selected and “Hide” was clicked on the context menu. In Sample2, the rows were hidden by opening the filter and deselecting “Chocolate”, which filtered out the rows. The filter icon present in Sample2’s drop down arrow indicates that the filter is active.

Using the Excel object model, you can see that the value of ActiveWorkbook.Worksheets(1).AutoFilterMode is True for both workbooks - that corresponds to the Aspose “HasAutofilter” property of its Worksheet object. However, if you check ActiveWorkbook.Worksheets(1).FilterMode, for Sample1 it is False, while for Sample2 it is True. That’s the property we want to check using Aspose, and I don’t see a way to do that currently.

Thanks,
Michael Whalen

8

@mwhalen

Thanks for using Aspose APIs.

We have logged your comment in our database against this issue so that we could find what is fixed in 18.3.3 and provide you the correct sample code.

However, we checked the following code and it seems to work fine. Please try it at your end and let us know your feedback.

C#

Workbook wb = new Workbook("Sample1.xlsx");

Worksheet ws = wb.Worksheets[0];

if (ws.AutoFilter.FilterColumns.Count == 0)
{
    Console.WriteLine("It means FilterMode property is false.");
}
else
{
    Console.WriteLine("It means FilterMode property is true.");
}
9

@mwhalen

We have overloaded AutoFilter.Refresh() method, please try the following code.

C#

int[] ret = wb.Worksheets[0].AutoFilter.Refresh(false);

The returned int array represents all hidden rows by AutoFilter.

10

That’ll probably work for us - is there a place I can download the updated assembly to give it a try?

11

@mwhalen

It is already available in 18.3.3 which we shared with you earlier. Here is the post where you can download it from.

12

I’ve tried it out now, and this appears to suit our needs. To be sure - the calling of the Refresh method with a “false” parameter will not change anything in the document if it’s just been opened, right? I assume it’ll only make changes if there are new rows added that might need to be filtered - that won’t happen in our scenario.

Thanks,
Michael Whalen

13

@mwhalen,

Yes, using Worksheet.AutoFilter.Refresh(false) method will return an int array that represents the hidden rows by AutoFilter. If it returns null, it means the hidden rows (if any) are not hidden by auto-filter. Moreover, this method will not change anything in the workbook. And, your understanding is correct as you wrote:

14

The issues you have found earlier (filed as CELLSNET-46007) have been fixed in Aspose.Cells for .NET 18.4. Please also check the document/article for your reference: Install Aspose Cells through NuGet|Documentation

15

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