-
Notifications
You must be signed in to change notification settings - Fork 530
excel validation add
zmworm edited this page Apr 29, 2026
·
51 revisions
Add data validation rules to cells and ranges.
Path: /{SheetName}
| Property | Default | Description |
|---|---|---|
sqref |
(required) | Target cell/range (e.g., A1, B2:B100) |
type |
list, whole, decimal, date, time, textLength, custom
|
|
formula1 |
First value/formula (list items, min value, start date, etc.) | |
formula2 |
Second value/formula (max value, end date, etc.) | |
operator |
between |
between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual
|
allowBlank |
true |
Allow blank cells |
showError |
true |
Show error alert on invalid input |
errorTitle |
Error dialog title | |
error |
Error dialog message | |
showInput |
true |
Show input prompt when cell is selected |
promptTitle |
Input prompt title | |
prompt |
Input prompt message | |
errorStyle |
stop |
Error severity: stop, warning, information
|
inCellDropdown |
true |
Show the in-cell dropdown arrow for type=list. Set false to hide the arrow but keep validation. Raw OOXML showDropDown (inverted) also accepted. |
Validation formulas are normalized per type:
-
type=time—formula1=HH:MMconverts to the time serial fraction (e.g.09:00→0.375). -
type=date—formula1=YYYY-MM-DDconverts to the date serial. -
type=custom— leading=is stripped (OOXML<x:formula1>expects the body without one). -
type=list— cell references (e.g.$A1ドル:$A10ドルorSheet2!$A1ドル:$A10ドル) pass through unchanged.
officecli add data.xlsx /Sheet1 --type validation \ --prop sqref=B2:B100 \ --prop type=list \ --prop formula1="Yes,No,Maybe" \ --prop promptTitle="Selection" \ --prop prompt="Choose from the list"
officecli add data.xlsx /Sheet1 --type validation \ --prop sqref=C2:C100 \ --prop type=whole \ --prop operator=between \ --prop formula1=1 \ --prop formula2=100 \ --prop showError=true \ --prop errorTitle="Invalid" \ --prop error="Enter a number between 1 and 100"
officecli add data.xlsx /Sheet1 --type validation \ --prop sqref=D2:D100 \ --prop type=date \ --prop operator=greaterThan \ --prop formula1=2024年01月01日 \ --prop showError=true \ --prop errorTitle="Invalid Date" \ --prop error="Date must be after 2024年01月01日"
Based on OfficeCLI v1.0.64