officecli-xlsx

# OfficeCLI XLSX Skill ## Runtime contract Wayland Desktop supplies a checksum-pinned native `officecli` runtime. Do **not** install, update, download, or replace it from this skill. Run `officecli --version` before authoring. The native low-level contract requires the exact verified `1.0.136` version; use `officecli --help` to verify the top-level commands. If the runtime is missing, incompatible, or unverified, stop native authoring and use Wayland's reinstall/update recovery path. Never curl an installer, invoke PowerShell bootstrap code, or fall back to the separately hosted-credit generator without explicit network and cost approval. ## ⚠️ Help-First Rule **This skill teaches what good xlsx looks like, not every command flag. When a property name, enum value, or alias is uncertain, consult help BEFORE guessing.** ```bash officecli help xlsx # List all xlsx elements officecli help xlsx <element> # Full element schema (e.g. pivottable, chart, cf) officecli help xlsx <verb> <element> # Verb-scoped (e.g. add chart, set cell) officecli help xlsx <element> --json # Machine-readable schema ``` Help reflects the installed CLI version. When this skill and help disagree, **help is authoritative**. ## Shell & Execution Discipline **Shell quoting (zsh / bash).** Excel paths contain `[]`, and number formats contain `$`. Both are shell metacharacters. Rules: - ALWAYS quote element paths: `"/Sheet1/row[1]"`, not `/Sheet1/row[1]`. - Use **single quotes** for any prop value containing `$`: `numFmt='$#,##0'`. - For formulas with cross-sheet `!` references, use `batch` with a `<<'EOF'` heredoc (see Known Issues). - NEVER hand-write `\$`, `\t`, `\n` inside executable examples. The CLI does not interpret backslash escapes; they will land in your file as literal characters. **Incremental execution.** Run commands one at a time and read each exit code. `officecli` mutates the file on every call; a 50-command script that fails at command 3 will cascade silently. One command → check output → continue. ## Requirements for Outputs Before reaching for a command, know what a good xlsx looks like. These are the deliverable standards every workbook MUST meet. ### All Excel files **Zero formula errors.** Every delivered workbook MUST have ZERO `#REF!`, `#DIV/0!`, `#VALUE!`, `#NAME?`, `#N/A`. No exceptions - guard denominators with `IFERROR` or `IF(x=0,...)`. **Formulas, not hardcoded values.** If a number can be computed from other cells, it is a formula. Hardcoding `5000` where `=SUM(B2:B9)` belongs breaks the contract that the workbook stays live when inputs change. This is the single most important rule in this skill. **Professional font.** Use one consistent, professional font across the workbook (Arial / Calibri / Times New Roman). Don't mix four fonts because one sheet came from CSV. **Explicit widths.** There is no auto-fit. Any column the user will read MUST have `width` set - default 8.43 chars clips everything. Sensible starts: labels 20-25, numbers 12-15, dates 12, short codes 8-10. **Preserve existing templates.** When editing a file that already has a look, match it. Existing conventions override these guidelines. ### Visual delivery floor (applies to EVERY workbook) Before you declare done, run `officecli view "$FILE" html` and Read the returned HTML path to confirm all of these: - **No `###` in any cell.** `###` means a column is too narrow for its widest value. Every column the user reads needs an explicit `width`. `###` in a delivered file is unfinished work, never "a small visual nit". - **No truncated titles.** Sheet titles, section headers, long labels must fit. Widen the column or apply `wrapText=true` on the cell. - **No placeholder tokens rendered as data.** `$fy$24`, `{var}`, `<TODO>`, `xxxx` must never appear in a cell, chart title, series name, or legend. These are build-time tokens that escaped replacement. - **Pie / doughnut slices have distinct fill colors.** If the slices render same-colored, switch to `bar` / `column` or set `colors=...` explicitly. - **No empty trailing pages / empty chart anchors.** `anchor=D2:J18` over empty source cells looks like a broken chart. If any of the above fails, STOP and fix before declaring done. **Print layout.** Any sheet the user may print or send as a board pack needs page setup. Default portrait + no fit-to-page splits wide tables and charts mid-way. Apply per sheet: ```bash officecli set "$FILE" "/Summary" --prop orientation=landscape --prop fitToPage=true ``` Trigger: sheet holds a chart, or > 8 columns, or the user's ask mentions print / board / investor. ### Financial models only - skip this section if you are building a template, tracker, CSV import, or operational sheet Scope: budgets, forecasts, 3-statement models, valuation, any `$`-heavy analytical workbook. A customer-support tracker or onboarding template does not need this section. **Color coding - industry standard.** Five core colors used as a language, not decoration. A reviewer should tell what a cell IS by color alone - before reading the formula. | Color | Role | Example | | -------------------- | -------------------------------------- | ------------------- | | Blue text `0000FF` | Hardcoded inputs, scenario variables | `font.color=0000FF` | | Black text `000000` | ALL formulas and calculations | default | | Green text `008000` | Cross-sheet links inside this workbook | `font.color=008000` | | Red text `FF0000` | Links to external files / workbooks | `font.color=FF0000` | | Yellow fill `FFFF00` | Key assumptions needing review | `fill=FFFF00` | A reviewer should tell what a cell IS just by its color - before reading the formula. This is a communication contract, not a cosmetic preference. **Number formatting - standards, not preferences.** - **Years** are text, not numbers. Format `2026` not `2,026` - use `numFmt="@"` or set `type=string`. - **Currency** carries its unit in the header (`Revenue ($mm)`), not in every cell. - **Zeros display as `-`**, not `0`. Use `$#,##0;($#,##0);"-"`. - **Percentages** default to one decimal: `0.0%`. - **Negatives use parentheses**: `(1,234)` not `-1,234`. - **Valuation multiples** use `0.0x` format (EV/EBITDA, P/E, etc.). **Assumptions live in cells, not inside formulas.** `=B5*(1+$B$6)` is correct; `=B5*1.05` is a bug. Document each blue hardcoded input with an adjacent source note in the next cell or a cell comment: ``` Source: Company 10-K, FY2024, Page 45, Revenue Note Source: Bloomberg, 2026-05-02, AAPL US Equity Source: Management guidance, Q2 2026 earnings call ``` Any hardcoded number without a source is an undocumented assumption - a reviewer cannot audit it. ## Common Workflow Six steps. Every non-trivial build follows this shape. 1. **Choose the mode.** Always use `officecli open <file>` at the start and `officecli close <file>` at the end. Resident mode is the default, not an optimization - it avoids re-parsing the file on every command. For many cells, use `batch`: **≤ 50 ops/block recommended; tested up to 80+ ops per block on pure value-set payloads with zero failures. Cross-sheet formula batches are the exception - run those non-resident, single heredoc (see Known Issues)**. 2. **Create or load.** `officecli create "$FILE"` (new) or `officecli view "$FILE" outline` (existing - get the lay of the land first). 3. **Build incrementally.** One command, read the output, continue. After any structural op (new sheet, chart, named range, pivot), run `get` on it to confirm shape before stacking more on top. 4. **Format.** Column widths, number formats, freeze panes, tab colors, header fills. Formatting is not optional polish - per "Requirements for Outputs" it is part of the deliverable. 5. **Close, then reckon with the cache.** `officecli close <file>` writes to disk. Newly-added formulas ship without cached values; when a human opens the file in a spreadsheet app, the app recalculates and populates them. **But your downstream `INDEX/MATCH`, `SUMPRODUCT`, or any formula that references an upstream formula will cache whatever the upstream cached at write-time - often `0` or a stale value - and that cached lie survives into non-recalculating readers.** After any multi-formula build involving array formulas (`SUMPRODUCT`, `SUMIFS` with dynamic criteria) or cross-sheet chains, **re-touch every downstream cell** (run `set` again with the same formula) so the engine recomputes its cache from the freshly-cached upstream. ⚠️ Re-touch on cross-sheet chains via resident is unreliable (see Batch / resident caveats) - prefer non-resident `set` for the re-touch pass. Then `officecli get` a few downstream cells and eyeball that their `cachedValue=` is plausible. **Array-formula fallback:** for `SUMPRODUCT(1/COUNTIF(range, range))` distinct-count patterns, the CLI engine treats the inner division as scalar and caches `1/N` (e.g. `0.001543`) rather than the true distinct count. Re-touching won't fix it. **Fallback: hardcode the correct value + an adjacent comment `"hardcoded distinct count; update if Data rows change"`, and tell the reader at delivery**. Better than shipping a cached lie. Do NOT run `validate` while a resident is open - it reports spurious drawing errors. 6. **QA - assume there are problems.** See the QA section. You are not done when your last command exited 0; you are done after one fix-and-verify cycle finds zero new issues. ## Quick Start Minimal viable xlsx: 3 months of revenue + a total formula + column widths + a currency format. Adapt, don't copy-paste - your file, your data. ```bash officecli create "$FILE" officecli open "$FILE" officecli set "$FILE" /Sheet1/A1 --prop value=Month --prop bold=true officecli set "$FILE" /Sheet1/B1 --prop value=Revenue --prop bold=true officecli set "$FILE" /Sheet1/A2 --prop value=Jan officecli set "$FILE" /Sheet1/A3 --prop value=Feb officecli set "$FILE" /Sheet1/A4 --prop value=Mar officecli set "$FILE" /Sheet1/B2 --prop value=42000 --prop numFmt='$#,##0' officecli set "$FILE" /Sheet1/B3 --prop value=45000 --prop numFmt='$#,##0' officecli set "$FILE" /Sheet1/B4 --prop value=48000 --prop numFmt='$#,##0' officecli set "$FILE" /Sheet1/A5 --prop value=Total --prop bold=true officecli set "$FILE" /Sheet1/B5 --prop formula="SUM(B2:B4)" --prop bold=true --prop numFmt='$#,##0' officecli set "$FILE" "/Sheet1/col[A]" --prop width=12 officecli set "$FILE" "/Sheet1/col[B]" --prop width=15 officecli close "$FILE" officecli validate "$FILE" ``` Verified: `validate` returns `no errors found`, `B5` resolves to `135000`. This is the shape of every build: open → set cells/formulas → format → close → validate. ## CSV / bulk import **Native `import` command (preferred for CSV/TSV).** Fastest path; loads a CSV into a sheet in one call. `--header` sets AutoFilter + freeze pane on row 1. Widths and `numFmt` still need a follow-up pass (per D-12 in Dashboard skill). ```bash officecli import "$FILE" /Sheet1 --file data.csv --header officecli import "$FILE" /Sheet1 --file data.tsv --format tsv --header officecli import "$FILE" /Sheet1 --stdin --start-cell B2 < data.csv ``` **Python + batch fallback** - use when you need custom type coercion, formula injection, or the CSV lives inside another data pipeline. Recipe for 600-6000+ cells: ```python # gen_batch.py - produces batch chunks of 80 value-set ops each import csv, json ops = [] with open("data.csv") as f: reader = csv.reader(f) for r, row in enumerate(reader, start=1): for c, val in enumerate(row): col = chr(ord('A') + c) ops.append({"command":"set","path":f"/Data/{col}{r}", "props":{"value": val}}) for i in range(0, len(ops), 80): print(json.dumps(ops[i:i+80])) ``` ```bash python gen_batch.py | while IFS= read -r chunk; do printf '%s\n' "$chunk" | officecli batch "$FILE" done ``` Outcome: 648-row retail CSV (6490 cells) loads in ~30s, zero failures. Tune: start at 80 ops/chunk, drop to 40 if any chunk fails. Numeric type inference and formulas come later via targeted `set` - batch in this recipe is pure value injection. ## Reading & Analysis Start wide, then narrow. `outline` first tells you what sheets exist and where the data is; jump into `view` / `get` / `query` only once you know where to look. **Open the rendered workbook to eyeball your own work.** - `officecli view $FILE html` - Read the returned HTML to audit the rendered output. Each sheet is addressable, charts render inline. Catches `###`, placeholder leakage, pivot layout, row-height clipping. - `officecli watch $FILE` keeps a live preview running for the human user - they open it at their own discretion. Use when the user wants to watch along; agent self-check uses `view html` above. Use `view html` as your **first visual check after a batch of edits** - fix at source. For final visual verification, the user opens the `.xlsx` in their Excel / WPS / Numbers viewer. **Orient.** Sheets, dimensions, formula counts. ```bash officecli view "$FILE" outline ``` **Extract.** Plain text dump for content QA or LLM context; scope with `--start` / `--end` / `--cols` for big files. ```bash officecli view "$FILE" text --start 1 --end 50 --cols A,B,C ``` Other `view` modes worth knowing: `annotated` (cell values + types/formulas + warnings), `stats` (numeric summaries), `issues` (broken formulas, empty sheets, missing refs). **Inspect one element.** Use XPath-style paths. Always quote - shells glob `[N]`. ```bash officecli get "$FILE" "/Sheet1/A1" # one cell officecli get "$FILE" "/Sheet1/A1:D10" # range officecli get "$FILE" "/Sheet1/chart[1]" # chart officecli get "$FILE" "/Sheet1/table[1]" # ListObject officecli get "$FILE" "/namedrange[1]" # workbook-level named range ``` Add `--depth N` to expand children; add `--json` for machine output. Full element list: `officecli help xlsx`. **Query across the workbook.** CSS-like selectors. Use for systematic checks (formula coverage, error cells, empty headers) rather than hand-walking. ```bash officecli query "$FILE" 'cell:has(formula)' # every formula cell officecli query "$FILE" 'cell:contains("#REF!")' # broken references officecli query "$FILE" 'cell[type=Number]' # typed filter officecli query "$FILE" 'Sheet1!B[value!=0]' # sheet-scoped ``` Operators: `=`, `!=`, `~=` (contains), `>=`, `<=`, `[attr]` (exists).