How to use AND and OR operators in the filter?

14 Mar 20254 minutes to read

You can use AND and OR operators in the filter by using XlsIO. The following code snippet illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Filter
	//Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
	worksheet.AutoFilters.FilterRange = worksheet.Range["A1:A10"];

	//Column index to which AutoFilter must be applied
	IAutoFilter filter = worksheet.AutoFilters[0];
  // This property determines how multiple filter conditions are combined.
  // If set to false, the filter will use the OR operator, meaning any condition can be met.
  // If set to true, the filter will use the AND operator, meaning all conditions must be met.
  // By default, this property is set to true (AND operator).
  filter.IsAnd = false;
	//To apply Top10Number filter, IsTop and IsTop10 must be enabled
	filter.IsTop = true;
	filter.IsTop10 = true;

	//Setting Top10 filter with number of cell to be filtered from top
	filter.Top10Number = 5;
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Filter.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range. 
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied
  IAutoFilter filter = sheet.AutoFilters[0];
  // This property determines how multiple filter conditions are combined.
  // If set to false, the filter will use the OR operator, meaning any condition can be met.
  // If set to true, the filter will use the AND operator, meaning all conditions must be met.
  // By default, this property is set to true (AND operator).
  filter.IsAnd = false;
  //To apply Top10Number filter, IsTop and IsTop10 must be enabled
  filter.IsTop = true;
  filter.IsTop10 = true;

  //Setting Top10 filter with number of cell to be filtered from top
  filter.Top10Number = 5;

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
    Dim sheet As IWorksheet = workbook.Worksheets(0)

    ' Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
    sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

    ' Column index to which AutoFilter must be applied
    Dim filter As IAutoFilter = sheet.AutoFilters(0)

    ' This property determines how multiple filter conditions are combined.
    ' If set to false, the filter will use the OR operator, meaning any condition can be met.
    ' If set to true, the filter will use the AND operator, meaning all conditions must be met.
    ' By default, this property is set to true (AND operator).
    filter.IsAnd = False

    ' To apply Top10Number filter, IsTop and IsTop10 must be enabled
    filter.IsTop = True
    filter.IsTop10 = True

    ' Setting Top10 filter with number of cell to be filtered from top
    filter.Top10Number = 5

    workbook.Version = ExcelVersion.Xlsx
    workbook.SaveAs("Filter.xlsx")
End Using