Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Jul 02, 2020 7:17 am

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.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Thu Jul 02, 2020 8:59 am

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
Attachments
docs.zip
(12.4 KiB) Downloaded 157 times
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Thu Jul 02, 2020 9:31 am

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.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Thu Jul 02, 2020 10:54 am

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
Filter results in MS Excel.png (3.34 KiB) Viewed 1162 times

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Thu Jul 02, 2020 12:28 pm

Thanks Rachel.

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

Thank you anyway.

blastdoman
 
Posts: 27
Joined: Thu Jul 02, 2020 7:06 am

Fri Jul 03, 2020 1:06 am

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
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS