# How OfficeCLI Evaluates Excel Boolean Selectors: Deep Dive into Row Filtering Syntax

> Understand how OfficeCLI parses and evaluates Excel boolean selectors for row filtering. Learn the syntax and filtering logic for your iOfficeAI data.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: deep-dive
- Published: 2026-08-01

---

**OfficeCLI treats selectors like `row[Salary>5000 and Region=EMEA]` as boolean predicates that filter Excel table rows by parsing the expression, resolving column indexes against ListObject or header-row tables, and evaluating conditions using the `AttributeFilter` expression engine to return matching `DocumentNode` objects.**

The iOfficeAI/OfficeCLI repository provides command-line tools for programmatic Excel manipulation. When querying spreadsheet data, you can use Excel boolean selectors to filter rows using intuitive predicate syntax. Understanding how OfficeCLI parses and evaluates these expressions requires examining the three-stage resolution pipeline implemented in the Excel handler components.

## Understanding Boolean Selector Syntax

OfficeCLI supports a CSS-inspired selector syntax for targeting specific rows within Excel tables. The parser distinguishes between column-based filters and row-property filters while handling sheet scoping and logical operators.

### Selector Structure and Grammar

A valid row selector follows the pattern `row[<predicate>]`, where the predicate inside brackets can contain one or more conditions combined with logical operators. In [`src/officecli/Handlers/Excel/ExcelHandler.Selector.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Selector.cs), the parser extracts the element identifier (`row`) and analyzes each token inside the brackets to classify filters:

- **Column predicates** such as `Salary>5000` or `Region=EMEA` reference table data
- **Row attributes** including `height`, `hidden`, or other keys defined in the `RowAttributeKeys` set reference row properties
- **Logical operators** `and` and `or` combine multiple predicates into complex expressions

The parser also recognizes sheet prefixes in formats like `Sheet1!row[…]` or `/Sheet1/row[…]`, and supports pseudo-selectors such as `:contains()` and `:empty` for advanced filtering.

## How OfficeCLI Parses and Evaluates Boolean Selectors

The evaluation of boolean selectors occurs in three distinct phases across separate handler files, ensuring type-safe resolution against the actual Excel table structure.

### Stage 1: Parsing the Selector String

The initial parsing occurs in [`src/officecli/Handlers/Excel/ExcelHandler.Selector.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Selector.cs). This component normalizes the selector string by:

- Extracting the element type and bracketed predicate expression
- Identifying column references versus row-property keys by checking against the internal `RowAttributeKeys` collection
- Processing sheet scoping prefixes to narrow the search to specific worksheets
- Tokenizing pseudo-selectors like `:contains()` for specialized text matching

During this phase, the parser builds an intermediate representation of the filter criteria without yet resolving column positions.

### Stage 2: Resolving Column References

In [`src/officecli/Handlers/Excel/ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Query.RowWhere.cs), the `ResolveColumns` method maps each column name (or letter) to its absolute zero-based index within the target table. This resolution process:

1. Searches first for **ListObject** instances (formal Excel tables)
2. Falls back to **detected header-row tables** when no formal table structure exists
3. Validates that all referenced columns exist within the resolved table scope

If a column name appears in multiple tables (for example, a "Name" column exists on both Sheet1 and Sheet2), OfficeCLI raises a `Core.CliException` with specific disambiguation suggestions, such as scoping the query to `/SheetName/row[…]`.

### Stage 3: Evaluating the Boolean Expression Tree

Once columns resolve to indexes, OfficeCLI constructs an `AttributeFilter.FilterExpr` tree to represent the boolean logic. This evaluation happens through:

- **Relational operators** (`>`, `<`, `>=`, `<=`): Compare raw stored cell values against the predicate value
- **Equality operators** (`=`): Match either the raw stored value or the display value (e.g., accepting "50%" for a percentage cell)
- **Logical combinators**: The `AttributeFilter.MatchesExpr` engine walks the expression tree, applying `and`/`or` logic to combine leaf conditions

For each row in the target range, OfficeCLI creates a probe `DocumentNode` containing the cell values for the resolved columns. Rows satisfying the entire expression return as `DocumentNode` objects with paths formatted as `/Sheet1/row[12]`.

## Handling Ambiguity and Edge Cases

OfficeCLI implements strict disambiguation rules when selector keys could reference either table columns or row properties.

### Disambiguating Row Properties vs Column Names

When a key like `height` exists both as a column header and a row attribute (controlling row height), you must use explicit prefixes:

- `row[col.height>180]` forces evaluation against the **column** named "height"
- `row[@height>20]` forces evaluation against the **row property** "height"

According to the source in [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs) (lines 1661-1664), omitting these prefixes when ambiguity exists triggers a `CliException` requiring explicit clarification.

### Error Handling and Suggestions

The error handling in [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs) (lines 1661-1700) provides actionable feedback:

- Suggests `row[col.Salary>…]` syntax when column names conflict with attributes
- Recommends sheet scoping (`/SheetName/row[…]`) when columns exist across multiple tables
- Validates operator compatibility with cell data types before evaluation

## Practical Code Examples for Excel Boolean Selectors

The following C# examples demonstrate selector usage against the OfficeCLI query API:

```csharp
// Simple column filter using greater-than operator
var highEarners = excelHandler.Query(new QueryCommand {
    Selector = "row[Salary>5000]"
});

// Combining conditions with AND logic (implicit or explicit)
var emeaHighEarners = excelHandler.Query(new QueryCommand {
    Selector = "row[Salary>5000 and Region=EMEA]"
});

// Using OR to match alternative conditions
var targetRegions = excelHandler.Query(new QueryCommand {
    Selector = "row[Region=EMEA or Region=APAC]"
});

// Disambiguating column vs row property with col. prefix
var tallRows = excelHandler.Query(new QueryCommand {
    Selector = "row[col.height>180]"
});

// Scoping selector to specific sheet with absolute path
var financeRows = excelHandler.Query(new QueryCommand {
    Selector = "/FinanceDept/row[Salary>5000 and Region=EMEA]"
});

```

These examples reference the actual implementation in [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs), where the `Query` method processes the `Selector` property and returns filtered `DocumentNode` results.

## Summary

- OfficeCLI parses boolean selectors in [`ExcelHandler.Selector.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Selector.cs) by extracting column predicates and row attributes from bracketed expressions like `row[Salary>5000 and Region=EMEA]`.
- Column resolution in [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs) maps names to indexes using ListObject or header-row detection, with strict ambiguity checking.
- The `AttributeFilter` engine evaluates expressions as trees, supporting relational operators on raw values and equality on display values, combined with `and`/`or` logic.
- Disambiguation requires `col.` prefixes for columns or `@` prefixes for row properties when names conflict.
- Results return as `DocumentNode` objects with paths like `/Sheet1/row[N]`, enabling precise programmatic access to filtered Excel data.

## Frequently Asked Questions

### What is the syntax for combining multiple conditions in OfficeCLI row selectors?

Use the logical operators `and` or `or` inside the brackets. For example, `row[Salary>5000 and Region=EMEA]` requires both conditions to be true, while `row[Salary>5000 or Region=EMEA]` matches rows satisfying either condition. You can group complex logic with parentheses, and the evaluation engine in `AttributeFilter.MatchesExpr` processes these as a binary expression tree.

### How does OfficeCLI handle column names that conflict with row attributes?

When a name like `height` exists as both a column header and a row property (controlling Excel row height), OfficeCLI throws a `CliException` and requires explicit disambiguation. According to [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs) lines 1661-1664, prefix the key with `col.` for column references (e.g., `col.height`) or `@` for row attributes (e.g., `@height`).

### What types of operators are supported in Excel boolean selectors?

OfficeCLI supports **relational operators** (`>`, `<`, `>=`, `<=`) that compare numeric or date values, and **equality operators** (`=`) that match string, numeric, or boolean values. Relational comparisons use the raw stored cell value, while equality checks accept either the raw value or the formatted display value (such as percentage strings).

### How are rows returned when using boolean selectors?

Matching rows return as `DocumentNode` objects with hierarchical paths formatted as `/SheetName/row[Index]`. The [`ExcelHandler.Query.RowWhere.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.RowWhere.cs) implementation evaluates each row against the `FilterExpr` tree, and only rows satisfying the complete boolean expression are included in the result set, preserving the original Excel table structure for downstream processing.