officecli-xlsx

# OfficeCLI XLSX Skill ## Setup If `officecli` is missing: - **macOS / Linux**: `curl -fsSL https://d.officecli.ai/install.sh | bash` - **Windows (PowerShell)**: `irm https://d.officecli.ai/install.ps1 | iex` Verify with `officecli --version` (open a new terminal if PATH hasn't picked up). If install fails, download a binary from https://github.com/iOfficeAI/OfficeCLI/releases. ## ⚠️ 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). - `\n` and `\t` in a prop value ARE interpreted by the CLI — `\n` is a real in-cell line break (pair with `--prop wrapText=true`), `\t` a tab — consistent across xlsx / docx / pptx. Double them (`\\n`) for a literal backslash-n (rarely wanted). (`$` is the shell layer above — single-quote it.) **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. Pick the fit mode by sheet shape: ```bash # Summary / chart / dashboard sheet (small, ≤ ~40 rows): fit to a single page. officecli set "$FILE" "/Summary" --prop orientation=landscape --prop fitToPage=true # Tall data table (dozens+ rows): fit WIDTH only, let height paginate naturally. # fitToPage=true here crushes every row onto one page → unreadable (### dates, 5px rows). officecli set "$FILE" "/Data" --prop orientation=landscape --prop fitToPage=1x0 ``` `fitToPage=true` == `1x1` == fit both axes to one page — correct only when the sheet is already short. `1x0` = fit 1 page wide, unlimited pages tall. 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. **Open/save lifecycle.** Use `officecli open <file>` at the start and `officecli save <file>` at the end to flush to disk — `save` only writes and leaves the resident warm for follow-up edits; reach for `officecli close <file>` only to release the resident on a one-shot handoff. Both are always safe (never error or lose work). 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)**. **Flush only at the non-officecli boundary:** officecli's own reads always see your edits; run `save`/`close` only before a non-officecli program reads the file (openpyxl/pandas, Excel, a renderer, delivery). 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. **Save, then reckon with the cache.** `officecli save <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. `validate` is safe with a resident open and itself flushes pending edits to disk (same as docx / pptx). 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). **Round-trip dump.** `officecli dump "$FILE" [path]` serializes the workbook — or one worksheet (`/Sheet1`, `/sheet[N]`) — into a replayable batch JSON; `officecli batch new.xlsx --input dump.json` replays it. Use it to learn from an existing workbook's structure or clone/adapt a template instead of reading raw OOXML. Coverage per `dump --help`; subtree dumps don't carry workbook-level resources (settings, named ranges) — the replay target must already define them. ```bash officecli dump "$FILE" -o blueprint.json # whole workbook officecli dump "$FILE" /Sheet1 -o sheet.json # one worksheet officecli batch new.xlsx --input blueprint.json ``` **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