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

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, 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. 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, 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 (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 (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:

// 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, where the Query method processes the Selector property and returns filtered DocumentNode results.

Summary

• OfficeCLI parses boolean selectors in 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 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 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}