Complex AutoFilters

[blastdoman]

Hi!!

I have seen that there is a tutorial to filter a column according to a specific value but I have not found an example where the filter is of another type. For example, how do you filter for rows whose value in a column is greater than 50? Or, how do you do to make filters that involve more than one column?

Thanks.

[rachel.lei]

Hello,

Thanks for your inquiry.
Please refer to the code below. Attached are my test file and output file for your better reference. If this is not what you want, please provide us with a sample Excel file and the output file you expect.

Code: Select all

    Workbook wb = new Workbook();
    wb.LoadFromFile("test.xlsx");
    Worksheet sheet = wb.Worksheets[0];
    //Create two auto filters for column A and column B and specify the range to be filtered
    sheet.AutoFilters.Range = sheet.Range["A1:B8"];
    //Get the column to be filtered
    FilterColumn filtercolumn = (FilterColumn)sheet.AutoFilters[0];
    //Add number filters to filter cells with a value greater than 50
    sheet.AutoFilters.CustomFilter(filtercolumn, FilterOperatorType.GreaterThan, 50);
    sheet.AutoFilters.Filter();
    wb.SaveToFile("out.xlsx");

Sincerely,
Rachel
E-iceblue support team

[blastdoman]

Thank you very much Rachel.

With what you have answered, it is perfect for what I want to implement, but already out of curiosity, what would the OR filter option be like, that is, a filter of type column A> 50 or column B <75? I don't know if it can be done.

[rachel.lei]

Hello,

Thanks for your response.
I tried to use Microsoft Excel to add a number filter (> 50) for column A and add a number filter (< 75) for column B, but the final result is as follows (column A> 50 and column B <75). Our Spire.XLS is based on Microsoft Excel, I'm afraid that there is no way to achieve your requirement. Hope you can understand.

Filter results in MS Excel.png (3.34 KiB) Viewed 1214 times

Sincerely,
Rachel
E-iceblue support team

[blastdoman]

Thanks Rachel.

You're right. Excel does not have that option. I was testing with LibreOffice instead of Excel.

Thank you anyway.

[rachel.lei]

Hello,

Thanks for your response.
Feel free to contact us if you have further questions. Have a nice day!

Sincerely,
Rachel
E-iceblue support team