How to Handle Excel Conditional Formatting with Formula-Based Rules in OfficeCLI

OfficeCLI handles Excel conditional formatting with formula-based rules by mapping the OOXML Expression type to CLI properties, allowing you to read and write formula expressions via officecli get and officecli set commands while validating inputs against the Open XML SDK schema.

Managing formula-based conditional formatting in Excel programmatically requires precise OOXML manipulation. The OfficeCLI open-source tool provides a command-line interface that abstracts these complexities, enabling developers to handle Excel conditional formatting with formula-based rules through simple path selectors. The implementation leverages the Open XML SDK to mirror Excel's internal representation of <cfRule> elements with Type = Expression.

Understanding Formula-Based Rules in OfficeCLI

OfficeCLI implements Excel conditional formatting through the ExcelHandler class, which mirrors Excel’s OOXML representation. A conditional formatting rule is stored as a <cfRule> element inside a <conditionalFormatting> container on a worksheet.

When the rule’s type is Expression (the OOXML enum value ConditionalFormatValues.Expression), the rule is formula‑based. The handler surfaces this rule in two main phases: Query for reading existing rules and Set for creating or modifying them.

Querying Existing Formula Rules

The query phase extracts formula-based rules from existing Excel files through the PopulateCfNodeFromRule method located in src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs.

When reading a rule, the handler performs the following steps:

• Extracts rule.Type and checks for Expression
• Reads the <formula> child element containing the raw expression
• Retrieves the style reference via rule.FormatId (mapped as dxfId)
• Returns a DocumentNode containing type = "formula", formula = "…", and dxfId

This allows the command officecli get /SheetName/cf[N] to display the formula text and applied style (DXF) for any formula-based conditional formatting rule.

Creating and Modifying Formula Rules

The Set phase handles creation and modification of rules in src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs (around line 830). When you supply a property named formula (or the alias ref), the handler creates a new ConditionalFormattingRule with Type = ConditionalFormatValues.Expression and inserts a <formula> child containing the supplied text.

The workflow follows this sequence:

1. Parse the selector – /SheetName/cf[N] selects the N‑th <conditionalFormatting> on the given sheet
2. Locate the rule – Within that container, the N‑th <cfRule> is targeted
3. Read/write the formula – For Expression rules, the <formula> child holds the user‑supplied expression
4. Apply style – The dxfId links the rule to a <dxf> (differential format) defined elsewhere in the stylesheet

Validation and Input Requirements

The handler validates inputs through shared helpers in ExcelHandler.Set.Tables.cs to ensure OOXML compliance:

• Formula syntax: The supplied formula must not start with an extra = (Excel expects the raw expression)
• Range validation: Referenced ranges must be valid within the worksheet context
• Index bounds: The rule index must be within the valid range of existing rules

These checks guarantee that the generated OOXML file will be accepted by Excel without corruption.

Complete Working Examples

CLI Workflow for Formula-Based Rules

First, create a differential style (dxf) that the rule will reference:

officecli set /styles/dxf[1] fillColor=ffeb3b fontColor=000000

Add a formula‑based conditional formatting rule to Sheet1 that highlights cells where A1 > 5:

officecli set /Sheet1/cf[1] \
    range=A2:A10 \
    formula="=$A$1>5" \
    dxfId=1 \
    stopIfTrue=true

Retrieve the rule to verify its contents:

officecli get /Sheet1/cf[1]

Expected output structure:

{
  "type": "formula",
  "formula": "=$A$1>5",
  "dxfId": 1,
  "stopIfTrue": true,
  "range": "A2:A10"
}

Update an existing rule by changing only the formula:

officecli set /Sheet1/cf[1] formula="=$A$1<3"

C# SDK Implementation

Using the Open XML SDK directly through OfficeCLI's handler:

var handler = new ExcelHandler(@"C:\books\sample.xlsx", editable: true);
var ws = handler.FindWorksheet("Sheet1");

// Create a new <conditionalFormatting> element
var cf = new ConditionalFormatting { 
    SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2:A10" } 
};
ws.Worksheet.Append(cf);

// Create the formula-based rule
var rule = new ConditionalFormattingRule
{
    Type = ConditionalFormatValues.Expression,
    FormatId = 1U,
    StopIfTrue = true
};
rule.Append(new Formula { Text = "=$A$1>5" });
cf.Append(rule);

handler.Flush();

Key Implementation Files

File	Role
src/officecli/Handlers/Excel/ExcelHandler.cs	Core class that opens the workbook, caches worksheets, and exposes high‑level operations
src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs	Implements PopulateCfNodeFromRule for reading conditional format rules
src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs	Handles creation/modification of rules, validates inputs, writes <formula> nodes
schemas/help/xlsx/cfextended.json	Schema definition for extended CF properties validating allowed keys

Summary

• OfficeCLI represents formula-based conditional formatting as OOXML Expression type rules, mapping them to CLI-friendly formula properties.
• Query operations use PopulateCfNodeFromRule in ExcelHandler.Query.Cf.cs to extract formula text and style references from <cfRule> elements.
• Set operations in ExcelHandler.Set.Tables.cs create ConditionalFormattingRule objects with Type = ConditionalFormatValues.Expression and append <formula> children.
• Validation ensures formulas omit the leading = character and reference valid ranges, preventing corrupt Excel files.
• The dxfId parameter links rules to differential formats defined in the stylesheet, enabling visual formatting like colors and borders.

Frequently Asked Questions

What is the correct syntax for formula expressions in OfficeCLI?

OfficeCLI expects the raw Excel expression without the leading = character. For example, use formula="=$A$1>5" in the CLI command, but note that the actual OOXML stores =$A$1>5 (the handler strips the extra = during validation if accidentally provided). According to the implementation in ExcelHandler.Set.Tables.cs, the formula text is inserted directly into the <formula> element as-is.

How does OfficeCLI validate formula-based conditional formatting rules?

The handler validates three critical aspects: the formula must not contain syntax errors that would invalidate the OOXML, any referenced cell ranges must exist within the target worksheet, and the rule index must not exceed the bounds of the conditional formatting collection. These checks occur in the shared validation helpers within src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs before any modifications are written to disk.

Can I update existing formula rules without recreating the entire conditional format?

Yes. OfficeCLI supports partial updates through the set command with specific selectors. By targeting /SheetName/cf[N] with only the properties you want to change (such as formula="=$A$1<3"), the handler modifies the existing <cfRule> element while preserving other attributes like dxfId and stopIfTrue. This follows the standard CRUD pattern implemented across all OfficeCLI handlers.

What is the relationship between dxfId and conditional formatting rules?

The dxfId (Differential Format ID) links a conditional formatting rule to a specific style definition stored in the workbook's <dxfs> collection. When you specify dxfId=1, the rule references the first differential format defined in styles.xml, which contains formatting instructions for fill color, font color, borders, or number formats. The schemas/help/xlsx/cfextended.json schema documents this relationship, requiring that referenced dxfId values must exist in the stylesheet.

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