excel conditionalformatting add

Excel: Conditional Formatting - add

Add conditional formatting rules for dynamic cell styling.

Path: /{SheetName}

Shared properties

All CF types accept these properties:

Property	Default	Description
stopIfTrue	false	Stop evaluating later CF rules on a matching cell. Honored on every CF type.

Data Bar

Property	Default	Description
sqref	(required)	Target range (e.g., B2:B100)
type	(required)	databar
color	638EC6	Bar fill color (hex RGB)
min	0	Minimum bound value
max	1	Maximum bound value
showValue	true	Show the numeric value under the bar. Set false to hide.
direction	context	Bar direction: context (default), leftToRight, rightToLeft
length		Length thresholds: min:max (e.g. 0:100)
colors		Negative-bar fill colors when used with positive: posColor:negColor

Get readback: dataBar Get exposes showValue, direction, length, colors; colorScale Get exposes midColor along with min/max/midpoint; cellIs Get exposes operator, formula, formula2, and dxfId.

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=B2:B100 \
  --prop type=databar \
  --prop color=63C384

Color Scale

Property	Default	Description
sqref	(required)	Target range
type	(required)	colorscale
minColor	F8696B	Color for minimum value (hex RGB)
midColor		Color for midpoint (hex RGB, omit for 2-color scale)
maxColor	63BE7B	Color for maximum value (hex RGB)
midpoint	50	Midpoint percentile for 3-color scale (only used when midColor is set)

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=C2:C100 \
  --prop type=colorscale \
  --prop minColor=FF0000 \
  --prop midColor=FFFF00 \
  --prop maxColor=00FF00

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=D2:D50 \
  --prop type=colorscale \
  --prop minColor=FFFFFF \
  --prop maxColor=0000FF

Icon Set

Property	Default	Description
sqref	(required)	Target range
type	(required)	iconset
iconSet	3TrafficLights1	Icon set name (see below)
reverse	false	Reverse icon order
showValue	true	Show cell value alongside icon

Icon sets: 3Arrows1, 3ArrowsGray1, 3Flags1, 3TrafficLights1, 3TrafficLights2, 3Signs1, 3Symbols1, 3Symbols2, 3Stars1, 3Triangles1, 4Arrows1, 4ArrowsGray1, 4RedToBlack1, 4Rating1, 4TrafficLights1, 5Arrows1, 5ArrowsGray1, 5Rating1, 5Quarters1

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=E2:E100 \
  --prop type=iconset \
  --prop iconSet=3Arrows

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=F2:F50 \
  --prop type=iconset \
  --prop iconSet=5Rating \
  --prop reverse=true \
  --prop showValue=false

Other supported CF types

These type= values are wired to their OOXML CF types:

Type	Behavior
cellIs	Compare cell value to formula (uses operator)
topN / topn	Top/bottom N values; rank=N (alias: topn) selects N, defaults to 10; bottom=true for bottom-N
aboveAverage / belowAverage	Above or below the range's average. Extra props: stdDev=N (standard-deviation band, ±N), equalAverage=true (include the average itself)
cellIs (with operators)	cellIs + operator=equal/notEqual/greaterThan/lessThan/greaterThanOrEqual/lessThanOrEqual/between/notBetween + formula=/formula2=, plus dxf style props (font.bold, font.color, fill, etc.)
containsBlanks / notContainsBlanks	Blank / non-blank cells
containsErrors / notContainsErrors	Error / non-error cells
contains / notContains	Substring match via text=...
beginsWith / endsWith	Prefix/suffix match via text=...
dateoccurring	Date range; timePeriod/timeperiod=today/yesterday/tomorrow/last7Days/lastWeek/thisWeek/nextWeek/lastMonth/thisMonth/nextMonth

Formula-based

Property	Default	Description
sqref	(required)	Target range
type	(required)	formulacf (use --type formulacf)
formula	(required)	Condition formula (e.g., =$A2>100)
font.bold / font.italic / font.strike		Font style on match
font.color		Font color on match (hex RGB)
font.size / font.name / font.underline		Full font sub-properties on the dxf
fill		Fill color on match (hex RGB)

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=A2:D100 \
  --prop type=formulacf \
  --prop formula='=$C2>1000' \
  --prop fill=C6EFCE \
  --prop font.color=006100

officecli add data.xlsx /Sheet1 --type cf \
  --prop sqref=A2:A100 \
  --prop type=formulacf \
  --prop formula='=ISBLANK(A2)' \
  --prop fill=FFC7CE

---

Based on OfficeCLI v1.0.64