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

Cell Formatting Showcase

Exercises the full xlsx cell property surface — the single most-used Excel element. Three files work together:

• cell-formatting.py — Python script that drives officecli to build the workbook.
• cell-formatting.xlsx — The generated 6-sheet workbook.
• cell-formatting.md — This file.

Regenerate

cd examples/excel
python3 cell-formatting.py
# → cell-formatting.xlsx

set auto-creates the target cell, so no per-cell add is needed. The script uses resident mode (open … close) for speed and registers an atexit close so the resident process is never left dangling on error.

The cell() helper wraps each --prop k=v in shlex.quote. This matters: a currency format like numberformat=$#,##0.00 contains $#, which a shell would otherwise expand to the positional-arg count. Quoting keeps the code literal.

Sheets

Sheet1 — Fonts

Each row pairs a property label (column A) with a rendered sample (column B): font.name, font.size, font.bold, font.italic, font.color, underline=single, underline=double, strike, and a combined run.

officecli set file.xlsx /Sheet1/B11 \
  --prop value="Bold + italic + blue + 14pt" \
  --prop font.bold=true --prop font.italic=true \
  --prop font.color=2E75B6 --prop font.size=14

Sheet2 — Fills & alignment

Feature	Spec
Solid hex fill	fill=E63946
Named color	fill=gold
rgb() form	fill="rgb(46,157,182)"
Horizontal align	alignment.horizontal=left|center|right (alias halign)
Vertical align	alignment.vertical=top|center|bottom (alias valign)
Wrap text	alignment.wrapText=true (aliases wrap, wrapText)
Reading order	alignment.readingOrder=rtl

Vertical alignment only shows visibly when the row is taller than the text, so the script bumps row[6..8] height to 34pt via /Fills/row[6] --prop height=34.

The sheet also shows three alignment properties set directly (canonical keys):

Feature	Spec
Text rotation	alignment.textRotation=45 (0-90 up / 91-180 down / 255 stacked; alias rotation)
Indent	alignment.indent=3 (alias indent)
Shrink to fit	alignment.shrinkToFit=true (alias shrink)

Sheet3 — Borders

officecli set file.xlsx /Borders/B3  --prop border=thin           # shorthand: all four sides
officecli set file.xlsx /Borders/B5  --prop border.all=medium     # explicit "all" form
officecli set file.xlsx /Borders/B7  --prop border=thick --prop border.color=C00000
officecli set file.xlsx /Borders/B9  --prop border.bottom=double  # single side
officecli set file.xlsx /Borders/B13 --prop border.left=thick --prop border.top=thin \
                                     --prop border.right=medium --prop border.bottom=double
# Diagonal borders — direction via diagonalUp/Down; color requires a diagonal line.
officecli set file.xlsx /Borders/B15 --prop border.diagonal=thin --prop border.diagonalUp=true
officecli set file.xlsx /Borders/B17 --prop border.diagonal=medium --prop border.diagonalDown=true \
                                     --prop border.diagonal.color=C00000

Styles accepted: thin, medium, thick, double, dashed, … (full list in schemas/help/xlsx/cell.json → border.*). border.diagonal.color requires a border.diagonal line to attach to.

Sheet4 — Number formats

The label column is the format code; column B is the same kind of value with that numberformat applied:

numberformat=	Value → Display
#,##0	1234567 → 1,234,567
#,##0.00	1234.5 → 1,234.50
0.00%	0.1834 → 18.34%
$#,##0.00	29999.9 → $29,999.90
yyyy-mm-dd	45413 → 2024-05-01
0.00E+00	602214 → 6.02E+05
_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_)	-4250 → (4,250.00)

The 0.00E+00 label cell is written with type=string, otherwise Excel parses the literal text 0.00E+00 as the number 0.

Sheet5 — Values, formulas, links

officecli set file.xlsx /Data/B5 --prop formula="B3*B4" --prop numberformat="$#,##0.00"   # 12 × 4.50 = $54.00
officecli set file.xlsx /Data/B7 --prop value=007 --prop type=string                       # keep leading zeros
officecli set file.xlsx /Data/A9 --prop value="OfficeCLI on GitHub" \
  --prop link="https://github.com/iOfficeAI/OfficeCLI" --prop tooltip="Open the repo"
officecli set file.xlsx /Data/A11 --prop value="locked cell" --prop locked=true            # effective once sheet is protected
officecli set file.xlsx /Data/A13 --prop value="Merged title" --prop merge="A13:C13" \
  --prop alignment.horizontal=center
officecli set file.xlsx /Data/B15 --prop arrayformula="B3*2"                                # dynamic-array spill

Sheet6 — Rich-text runs

runs is an add-time property (requires --type cell and type=richtext). Each run is a JSON object with "text" plus optional font props (bold, italic, color, size, underline, strike, superscript, subscript). set does not support rich-text; use add.

# Bold+red / italic+blue / normal run in one cell
officecli add file.xlsx /RichText --type cell --prop ref=A3 \
  --prop type=richtext \
  --prop 'runs=[{"text":"Bold + Red  ","bold":true,"color":"C00000"},{"text":"Italic + Blue","italic":true,"color":"2E75B6"},{"text":"  Normal"}]'

# Chemical formula with superscript: H₂O
officecli add file.xlsx /RichText --type cell --prop ref=A5 \
  --prop type=richtext \
  --prop 'runs=[{"text":"H","bold":true,"color":"1F4E79","size":18},{"text":"2","superscript":true,"size":10},{"text":"O water formula","color":"1F4E79"}]'

# strike / underline / different size in one cell
officecli add file.xlsx /RichText --type cell --prop ref=A7 \
  --prop type=richtext \
  --prop 'runs=[{"text":"Strike","strike":true},{"text":" | "},{"text":"underline","underline":"single"},{"text":" | "},{"text":"size 14pt","size":14}]'

Features: type=richtext, runs (JSON array of run objects), per-run: text, bold, italic, color, size, underline, strike, superscript, subscript

Complete Feature Coverage

Feature	Sheet
font.name, font.size, font.bold, font.italic, font.color	Sheet1
underline=single, underline=double	Sheet1
strike=true	Sheet1
superscript=true, subscript=true	Sheet1
fill (hex, named, rgb)	Sheet2
alignment.horizontal (left/center/right)	Sheet2
alignment.vertical (top/center/bottom)	Sheet2
alignment.wrapText	Sheet2
alignment.readingOrder (rtl)	Sheet2
alignment.textRotation (0-255)	Sheet2
alignment.indent	Sheet2
alignment.shrinkToFit	Sheet2
border (shorthand all sides)	Sheet3
border.all, border.top/bottom/left/right	Sheet3
border.color, border.diagonal, border.diagonalUp, border.diagonalDown, border.diagonal.color	Sheet3
numberformat (thousands, %, currency, date, scientific, accounting)	Sheet4
value, type=string	Sheet5
formula, arrayformula	Sheet5
link, tooltip	Sheet5
locked	Sheet5
merge	Sheet5
type=richtext, runs (per-run: bold/italic/color/size/strike/underline/superscript/subscript)	Sheet6

Set → Get round-trip

The script ends by reading three cells back with get … --json and printing the canonical keys, proving the values survive the write and normalize on read:

/Sheet1/B11: {'font.bold': True, 'font.italic': True, 'font.color': '#2E75B6', 'font.size': '14pt'}
/Numbers/B6: {'numberformat': '$#,##0.00'}
/Borders/B9: {'border.bottom': 'double'}

Note the normalization on get: colors gain a # prefix (#2E75B6) and font sizes become unit-qualified (14pt) — the canonical output forms.

Inspect the Generated File

officecli query cell-formatting.xlsx sheet
officecli get cell-formatting.xlsx "/RichText/A3"