CODEWITHSUNDEEP
c#excelvstooffice-interop
marwaha.ks
marwaha.ks

Reputation: 549

Find which cells have a dropdown filter applied - Interop Excel

I'm trying to check whether a worksheet has any filters (whether applied or not), if so, I'd like to figure out which cells or row have a filter function.

I've tried: var filter = ((dynamic) range.AutoFilter(rowStart, j);

and this one to see if there are any in the whole worksheet

var range = workbookWorksheet.UsedRange; 
var sheet= application.ActiveWindow.ActiveSheet;
Range filteredRange = range.SpecialCells(XlCellType.xlCellTypeVisible, XlSpecialCellsValue.xlTextValues);
var sdsds = filteredRange.AutoFilter();

Trying to do this without Plus or another library other than Interop. Not sure if this is possible but any help will be greatly appreciated.

Upvotes: 1

Views: 430

Answers (1)

EylM
EylM

Reputation: 6103

You can use the the AutoFilter property to check if there are filters in the Worksheet.

In order to get the addresses, you need to iterate over the filters. Use the On property of the filter to check if it's on/off.

    // check if there are filters in the worksheet.
    if (workbookWorksheet.AutoFilter == null ||
        workbookWorksheet.AutoFilter.Filters.Count == 0)
    {
        // no filters
        return;
    }

    // go over the filters and get the addresses
    for (var i = 1; i < ws.AutoFilter.Filters.Count; i++)
    {
        var filter = ws.AutoFilter.Filters[i];
        Console.WriteLine(filter.On); // print/check if the filter is on.

        Range range = ws.AutoFilter.Range[1, i];
        Console.WriteLine(range.Address); // address of the filter cell
    }

Update: Not sure it's the most efficient way, but it does the job. This functions takes range as the parameter and check if it has a filter and returns true if the filter is on.

Note: If you prefer to work with absolute address, change the parameter to string address.

private bool HasFilterOn(Range range)
{       
    // check if there are filters in the worksheet.
    if (workbookWorksheet.AutoFilter == null ||
        workbookWorksheet.AutoFilter.Filters.Count == 0)
    {
        // no filters
        return false;
    }

    // go over the filters and get the addresses
    for (var i = 1; i < workbookWorksheet.AutoFilter.Filters.Count; i++)
    {
        var filter = workbookWorksheet.AutoFilter.Filters[i];

        if (workbookWorksheet.AutoFilter.Range[1, i].Address == range.Address)
            return filter.On;
    }

    return false;
}

Upvotes: 1

Related Questions