-
Notifications
You must be signed in to change notification settings - Fork 528
excel conditionalformatting add
Add conditional formatting rules for dynamic cell styling.
Path: /{SheetName} (range may also be appended to the path tail — e.g. /Sheet1/B2:B100)
Path-tail range (v1.0.105+): if the path includes a range suffix, it overrides the
sqref=property. Useful for selector-driven scripting:add data.xlsx /Sheet1/B2:B100 --type conditionalformatting --prop type=databar --prop color=638EC6is equivalent to passing--prop sqref=B2:B100.
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. |
| 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;colorScaleGet exposesmidColoralong with min/max/midpoint;cellIsGet exposesoperator,formula,formula2, anddxfId.
officecli add data.xlsx /Sheet1 --type cf \ --prop sqref=B2:B100 \ --prop type=databar \ --prop color=63C384
| 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
| 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
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
|
| 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.105