How to Apply Excel Conditional Formatting Rules Like Data Bars and Color Scales with OfficeCLI

OfficeCLI generates Excel conditional formatting by manipulating Open XML structures through the ExcelHandler dispatcher, enabling data bars with automatic value scaling and color scales with multi-point gradients via the add command with --type conditionalformatting.

OfficeCLI from the iOfficeAI/OfficeCLI repository provides direct control over Excel's visual formatting by working with the underlying Open XML representation. When you need to apply Excel conditional formatting rules like data bars and color scales with OfficeCLI, the tool bypasses the Excel UI and writes the raw <conditionalFormatting> elements that Excel renders when the workbook opens.

How OfficeCLI Structures Conditional Formatting

OfficeCLI routes all conditional formatting requests through the ExcelHandler class in ExcelHandler.Add.Cf.cs. When you invoke the add command with --type conditionalformatting, the dispatcher at lines 20-34 examines the type property and forwards the request to specialized handlers. The CLI constructs ConditionalFormattingRule elements and populates the worksheet's <conditionalFormatting> collection, adding corresponding entries to the <dxfs> table for visual styling.

Applying Data Bars to Excel Ranges

Data bars provide visual length-based comparisons within cells. OfficeCLI supports both standard OOXML 2007 data bars and Office 2010 extensions for advanced negative-value rendering.

Command Syntax for Data Bars

Use the type=dataBar property to trigger the AddDataBar implementation (lines 84-86). The following command applies a blue data bar to column A with automatic min/max calculation:

officecli add book.xlsx /DataBars \
  --type conditionalformatting \
  --prop type=dataBar \
  --prop ref=A2:A11 \
  --prop color=638EC6 \
  --prop min=auto \
  --prop max=auto \
  --prop showValue=true

XML Implementation and Extensions

In ExcelHandler.Add.Cf.cs, the AddDataBar method builds a ConditionalFormattingRule containing a DataBar child element. For advanced features like negative value colors and axis positioning, the handler adds the x14:dataBar extension (lines 173-189). The ParseHelpers.NormalizeArgbColor helper converts color strings to the ARGB format required by Excel's XML schema.

Auto-Scaling Behavior

When you specify min=auto or max=auto, the handler omits the numeric cfvo value in the XML (lines 34-42), instructing Excel to calculate the bounds automatically based on the range's data. This matches Excel's native "Automatic" minimum and maximum settings.

Creating Color Scale Rules

Color scales apply gradient fills based on cell values, supporting both two-color and three-color configurations.

Two-Color and Three-Color Scales

For a simple gradient, specify minColor and maxColor. To add a midpoint, include midColor and midpoint properties. The generic Add path in ExcelHandler.Add.Cf.cs creates the <colorScale> element based on these parameters.

# Two-color scale (white to green)

officecli add book.xlsx /ColorScales \
  --type conditionalformatting \
  --prop type=colorScale \
  --prop ref=B2:B11 \
  --prop minColor=FFFFFF \
  --prop maxColor=63BE7B

# Three-color scale with 50% midpoint

officecli add book.xlsx /ColorScales \
  --type conditionalformatting \
  --prop type=colorScale \
  --prop ref=C2:C11 \
  --prop minColor=F8696B \
  --prop midColor=FFEB84 \
  --prop maxColor=63BE7B \
  --prop midPoint=50

Querying Existing Color Scales

When reading workbooks, the PopulateCfNodeFromRule method in ExcelHandler.Query.Cf.cs (lines 99-115) extracts color information from the ColorScale child element. It surfaces properties including minColor, midColor, maxColor, and midpoint, allowing you to inspect existing formatting with the officecli get command.

Validation Schemas and Properties

OfficeCLI validates all conditional formatting arguments against JSON schemas before processing. The schemas/help/xlsx/databar.json defines valid data bar properties including color, min, max, showValue, negativeColor, and axisPosition. For color scales, schemas/help/xlsx/colorscale.json specifies minColor, midColor, maxColor, and midpoint requirements. These schemas ensure that CLI arguments map correctly to the internal XML structures.

Complete Working Example

The following script from examples/excel/conditional-formatting.sh demonstrates the full workflow:

# Create and open workbook

officecli create book.xlsx
officecli open book.xlsx

# Data bar with negative value support

officecli add book.xlsx /DataBars \
  --type conditionalformatting \
  --prop type=dataBar \
  --prop ref=A2:A11 \
  --prop color=638EC6 \
  --prop min=auto \
  --prop max=auto \
  --prop negativeColor=FF0000 \
  --prop axisColor=000000 \
  --prop axisPosition=middle \
  --prop showValue=true

# Three-color scale

officecli add book.xlsx /ColorScales \
  --type conditionalformatting \
  --prop type=colorScale \
  --prop ref=C2:C11 \
  --prop minColor=F8696B \
  --prop midColor=FFEB84 \
  --prop maxColor=63BE7B \
  --prop midPoint=50

# Finalize

officecli close book.xlsx
officecli validate book.xlsx

Summary

• OfficeCLI manipulates raw Open XML to create conditional formatting rules without opening Excel.
• The ExcelHandler.Add.Cf.cs dispatcher routes type=dataBar to AddDataBar and type=colorScale to the generic color scale handler.
• Data bars support automatic scaling via omitted cfvo values and Office 2010 extensions for negative values.
• Color scales support both two-point and three-point gradients with configurable midpoint percentages.
• All properties are validated against JSON schemas (databar.json and colorscale.json) before XML generation.

Frequently Asked Questions

How does OfficeCLI handle automatic min/max values for data bars?

When you specify min=auto or max=auto, the CLI omits the numeric cfvo element in the Open XML (as seen in ExcelHandler.Add.Cf.cs lines 34-42). This signals Excel to calculate the minimum and maximum values dynamically based on the data range when the file opens.

Can I apply conditional formatting to multiple non-contiguous ranges?

The ref property accepts standard Excel range notation. While the examples show contiguous ranges like A2:A11, you can specify multiple areas using comma-separated references (e.g., A2:A11,C2:C11) provided the ranges are valid according to the Open XML specification.

What is the difference between the OOXML 2007 and 2010 data bar extensions?

OfficeCLI generates a standard DataBar element for basic functionality. When you specify advanced properties like negativeColor or axisPosition, the handler in ExcelHandler.Add.Cf.cs (lines 173-189) additionally creates an x14:dataBar extension element. This extension enables Excel 2010 and later to render negative bars, custom axis colors, and length constraints that the 2007 specification does not support.

How do I verify that my conditional formatting was applied correctly?

Use the officecli get command to query the worksheet's conditional formatting rules. The PopulateCfNodeFromRule method in ExcelHandler.Query.Cf.cs reads the <colorScale> or <dataBar> XML elements and returns human-readable properties like minColor, color, and midpoint, allowing you to verify the rule configuration without opening Excel.

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