iofficeai--officecli/examples/excel/conditional-formatting.md

Conditional Formatting Showcase

Exercises the full xlsx conditionalformatting rule family — the one major spreadsheet feature the other excel examples don't cover. Three files work together:

• conditional-formatting.py — builds the workbook via the officecli Python SDK.
• conditional-formatting.xlsx — the generated 7-sheet workbook.
• conditional-formatting.md — this file.

Built on the SDK (not subprocess)

Unlike the sibling *.py examples (which subprocess.run("officecli …") once per command), this script drives the officecli-sdk Python client. One resident process is started; every rule is shipped over the named pipe; all the rules for a sheet go in a single doc.batch(...) round-trip:

import officecli                      # pip install officecli-sdk

with officecli.create(FILE, "--force") as doc:
    doc.batch([
        {"command": "set", "path": "/Sheet1/A2", "props": {"value": "58"}},
        {"command": "add", "parent": "/Sheet1", "type": "conditionalformatting",
         "props": {"type": "cellIs", "ref": "A2:A11",
                   "operator": "greaterThan", "value": "80", "fill": "C6EFCE"}},
    ])

The dict shape is identical to an officecli batch list item — command, path/parent/type, and props. The script falls back to the in-repo SDK copy if officecli-sdk isn't pip-installed, so it runs straight from a checkout.

Regenerate

cd examples/excel
pip install officecli-sdk          # plus the `officecli` binary on PATH
python3 conditional-formatting.py
# → conditional-formatting.xlsx

A conditional-formatting rule

Every rule is one add against the sheet, with type= selecting the rule kind and ref= the target range. The match format (fill colour, bar, scale, icons) is carried by the remaining props:

officecli add file.xlsx /Sheet1 --type conditionalformatting \
  --prop type=cellIs --prop ref=A2:A11 --prop operator=greaterThan \
  --prop value=80 --prop fill=C6EFCE

The rule lands at /Sheet1/cf[N]; get/set/remove address it there. A rule's differential fill is stored once in the workbook-level <dxfs> table (styles.xml) and referenced by index.

Sheets

Sheet1 — CellIs (value comparison)

operator ∈ greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, equal, notEqual, between, notBetween. between/ notBetween use both value and value2.

officecli add file.xlsx /Sheet1 --type conditionalformatting --prop type=cellIs --prop ref=A2:A11 --prop operator=greaterThan --prop value=80 --prop fill=C6EFCE
officecli add file.xlsx /Sheet1 --type conditionalformatting --prop type=cellIs --prop ref=A2:A11 --prop operator=between --prop value=50 --prop value2=70 --prop fill=FFEB9C

Sheet2 — Text rules

containsText, notContainsText, beginsWith, endsWith. The needle is text=; the match fill is fill=.

officecli add file.xlsx /Text --type conditionalformatting --prop type=containsText --prop ref=A2:A9 --prop text=error --prop fill=FFC7CE
officecli add file.xlsx /Text --type conditionalformatting --prop type=beginsWith --prop ref=A2:A9 --prop text=Begins --prop fill=BDD7EE

Sheet3 — Top / Bottom / Average

top10/topN (count via rank=), topPercent (rank= + percent=true), bottom, aboveAverage/belowAverage (aboveAverage=true|false, optional stdDev= for an N-sigma band).

officecli add file.xlsx /TopBottom --type conditionalformatting --prop type=top10 --prop ref=A2:A13 --prop rank=3 --prop fill=C6EFCE
officecli add file.xlsx /TopBottom --type conditionalformatting --prop type=topPercent --prop ref=A2:A13 --prop rank=25 --prop percent=true --prop fill=63BE7B
officecli add file.xlsx /TopBottom --type conditionalformatting --prop type=aboveAverage --prop ref=A2:A13 --prop aboveAverage=true --prop stdDev=1 --prop fill=FFEB9C

Sheet4 — Data bars

color is the bar fill; min/max set the scale (auto = automatic bounds — the default). The 2010+ extension adds negativeColor, axisColor, and axisPosition (automatic/middle/none), so negative values render leftward in their own colour about a mid axis.

officecli add file.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

min=auto/max=auto is the automatic-bound sentinel — it serializes to <cfvo type="min"/>/<cfvo type="max"/> (and x14 autoMin/autoMax), the same as omitting the bound. (Passing a real number, e.g. min=0 max=100, pins the scale instead.)

Sheet5 — Color scales

2-colour (minColor/maxColor) or 3-colour (+ midColor, midpoint via midPoint=).

officecli add file.xlsx /ColorScales --type conditionalformatting --prop type=colorScale --prop ref=A2:A11 --prop minColor=FFFFFF --prop maxColor=63BE7B
officecli add file.xlsx /ColorScales --type conditionalformatting --prop type=colorScale --prop ref=B2:B11 --prop minColor=F8696B --prop midColor=FFEB84 --prop maxColor=63BE7B --prop midPoint=50

Sheet6 — Icon sets

iconset= names the set (3TrafficLights1, 3Arrows, 4Rating, 5Rating, …). reverse=true flips the order; showValue=false hides the cell value behind the icon.

officecli add file.xlsx /IconSets --type conditionalformatting --prop type=iconSet --prop ref=A2:A11 --prop iconset=3TrafficLights1
officecli add file.xlsx /IconSets --type conditionalformatting --prop type=iconSet --prop ref=D2:D11 --prop iconset=3TrafficLights1 --prop reverse=true

Sheet7 — Formula, date, duplicate / unique

formula (a boolean expression, no leading =), dateOccurring (period= token), duplicateValues, uniqueValues.

officecli add file.xlsx /FormulaEtc --type conditionalformatting --prop type=formula --prop ref=A2:A11 --prop formula="ISODD(A2)" --prop fill=BDD7EE
officecli add file.xlsx /FormulaEtc --type conditionalformatting --prop type=duplicateValues --prop ref=A2:A11 --prop fill=FFC7CE
officecli add file.xlsx /FormulaEtc --type conditionalformatting --prop type=dateOccurring --prop ref=B2:B11 --prop period=thisMonth --prop fill=FFEB9C

Complete feature coverage

Rule family	type=	Key props	Sheet
Comparison	cellIs	operator, value, value2, fill	Sheet1
Text	containsText / notContainsText / beginsWith / endsWith	text, fill	Sheet2
Top/Bottom	top10 / topN / topPercent / bottom	rank, percent, bottom, fill	Sheet3
Average	aboveAverage / belowAverage	aboveAverage, stdDev, equalAverage, fill	Sheet3
Data bar	dataBar	color, min, max, negativeColor, axisColor, axisPosition, showValue	Sheet4
Colour scale	colorScale	minColor, midColor, maxColor, midPoint	Sheet5
Icon set	iconSet	iconset, reverse, showValue	Sheet6
Formula	formula	formula, fill	Sheet7
Date	dateOccurring	period, fill	Sheet7
Dup/Unique	duplicateValues / uniqueValues	fill	Sheet7

Full property list: officecli help xlsx conditionalformatting (or schemas/help/xlsx/conditionalformatting.json).

Read a rule back

officecli query conditional-formatting.xlsx conditionalformatting
officecli get conditional-formatting.xlsx "/Sheet1/cf[1]" --json

get normalizes on read: colours gain a # prefix (#C6EFCE), and the rule type comes back as the canonical camelCase token.

Validating CF documents

A data-bar / colour-scale fill lives in the workbook <dxfs> table, so always validate the saved file from a fresh process:

officecli validate conditional-formatting.xlsx