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.

officecli help xlsx                         # List all xlsx elements
officecli help xlsx                # Full element schema (e.g. pivottable, chart, cf)
officecli help xlsx          # Verb-scoped (e.g. add chart, set cell)
officecli help xlsx  --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 `, 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:

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 at the start and officecli close 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 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.

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).

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 =`, `` entries.

**When the data is big enough that a row-walk is useless**, reach for Excel's own analytical elements:

- Build a **pivot table** with `officecli add` (`--type pivottable`) to group/aggregate without writing 20 SUMIFs. Attach a **slicer** (`--type slicer`) to give the reader a filter UI.
- Drop a **sparkline** (`--type sparkline`) in a row to show per-row trends — cheaper than one line chart per row and they print inline. `type` is a strict enum: **`line | column | stacked`** (plus aliases `winloss` / `win-loss` → `stacked`). Invalid `type=` values hard-fail on 1.0.58+ — no silent fallback to `line` anymore.
- Run `officecli help xlsx pivottable`, `officecli help xlsx slicer`, `officecli help xlsx sparkline` for the exact prop names.

## Creating & Editing

Ninety percent of a build is cells, formulas, formatting, and one or two charts. The verbs: `add` (new element), `set` (change a prop), `remove`, `move`, `swap`, `batch`.

### Cells and formulas

Set a value and its format in one call. Never write `=` at the start of a formula — the CLI strips it.

```bash
officecli set "$FILE" /Sheet1/B5 --prop formula="SUM(B2:B4)" --prop numFmt='$#,##0'
officecli set "$FILE" /Sheet1/C5 --prop formula="B5/A5" --prop numFmt="0.0%"

Structural properties (width, height, freeze, tabColor) live on row / col / sheet nodes:

officecli set "$FILE" "/Sheet1/col[A]" --prop width=20
officecli set "$FILE" "/Sheet1/row[1]" --prop height=22
officecli set "$FILE" "/Sheet1" --prop freeze=A2 --prop tabColor=1F4E79

Named ranges

Prefer named ranges over $B$6 in formulas. They self-document (GrowthRate beats $B$6) and they let you move the assumption cell without breaking formulas. Because ref values contain both ! and $, add them through a batch heredoc:

cat `, `$VAR`, `{{placeholder}}` render **literally** in the legend — validate passes, but a CFO sees `$fy$24` where "FY2024" should be. Always bind to final text or a cell reference (`title="FY2024 Revenue"` or `series1.name="Sheet1!A1"`).

### Conditional formatting

Three common flavors, each with its own prop shape (consult `officecli help xlsx cf`):

- **Color scales**: cells shaded on a gradient by value — `type=colorscale` with `minColor` / `midColor` / `maxColor`.
- **Data bars**: in-cell bars showing magnitude — `type=databar`. ALWAYS set explicit `min` and `max`; defaults emit invalid XML (see Known Issues).
- **Formula rules**: highlight row when a condition is true — `type=formulacf` with `formula="$C2>1000"` and a fill/font.

Rule: apply CF sparingly. A workbook where every cell is colored tells the reader nothing.

### Data validation

Input cells in trackers and templates MUST carry data validation. It's cheap and it stops entire classes of downstream bugs. **Three list-source patterns** — pick based on where the allowed values live.

**(a) Inline list** — allowed values are short and fixed in the rule itself.

```bash
officecli add "$FILE" /Sheet1 --type validation \
  --prop sqref="C2:C100" --prop type=list \
  --prop formula1="Yes,No,Maybe" \
  --prop showError=true --prop errorTitle="Invalid" --prop error="Select from list"

(b) Named range (preferred for cross-sheet lookups) — allowed values live in another sheet and may grow. Define the named range first, then reference it. Use a batch heredoc because ref contains ! and $:

cat `), sliced charts, white-slice pie charts, empty chart anchors — **STOP and fix before declaring done**. "validate pass" is not delivery; "the preview looks like a real workbook" is delivery. For human preview, run `officecli watch "$FILE"` (user opens the live preview at their own discretion) or have them open the `.xlsx` directly in Excel / WPS / Numbers.
6. **Print layout fix (wide tables / multi-chart sheets).** When a sheet holds a chart or a wide table and the user will print it, set per-sheet page layout so it fits on one page:
   ```bash
   officecli set "$FILE" "/Summary" --prop orientation=landscape --prop fitToPage=true
   ```
   Outcome: each sheet's print layout is one page with no mid-chart splits. Apply to every sheet that holds a chart or a > 8-column table.
7. If anything failed, fix, then **rerun the full cycle**. One fix commonly creates another problem.

`officecli view issues` + `view html` are the structural QA pair: `issues` catches broken formulas and empty sheets; `view html` (Read the returned HTML path) catches `###`, truncation, and token leakage. Chart fill colors / theme tints can vary across viewers — spot-check in the user's target viewer when color fidelity matters.

### Formula verification checklist

- [ ] Pick 2-3 formulas at random. Run `officecli get` on each. Confirm the formula string is what you intended **and** `cachedValue=` is what you expect — arithmetic in your head.
- [ ] **Cached value sanity on every summary cell.** Any cell that aggregates (COUNTA / COUNTIF / SUMPRODUCT / INDEX&MATCH) must have a plausible `cachedValue`. If a progress tracker shows `199 / 199 / 100%` on a blank template, the cache is lying — re-touch the formula via `set` (forces recompute) or manually set a correct cached value. Do NOT ship "validate passes but the numbers are fiction".
- [ ] **Spot-check one cell per numeric column.** `%` columns showing integer `0.0%` throughout means the denominator is wrong or the numerator is cached stale — investigate one cell, fix the pattern.
- [ ] Ranges include every row: off-by-one on `SUM(B2:B12)` when data goes to `B13` is the most common bug.
- [ ] Cross-sheet formulas (`Sheet1!A1`) contain no `\!`. If `officecli get` shows `Sheet1\!A1`, the `!` was shell-corrupted — delete and re-enter via batch/heredoc.
- [ ] Named ranges (`officecli get "$FILE" "/namedrange[1]"`) point at what their names claim.
- [ ] Every `/` denominator is guarded — `IFERROR(x/y, 0)` or `IF(y=0, 0, x/y)`.
- [ ] Chart data vs source cells: for every chart with inline data, spot-check data points against `officecli get` of the source cells.
- [ ] Chart title / series name / legend contain **no** unreplaced tokens (`$...$`, `{var}`, ``). Grep the chart via `officecli get /Sheet1/chart[N]`.

### Template QA

When editing a template, check for leftover placeholders — they look like content and slip past `validate`:

```bash
officecli query "$FILE" 'cell:contains("{{")'
officecli query "$FILE" 'cell:contains("xxxx")'
officecli query "$FILE" 'cell:contains("TBD")'

Fresh eyes

When you finish a workbook, open it fresh. Read view text / HTML preview top-to-bottom as if you are a new reviewer — look for formulas, numbers that look off, formatting inconsistency, missing data.

Honest limit

validate catches schema errors, not design errors. A workbook can pass validate with every number wrong. The checklist above — especially spot-checking formulas against source cells — is how you catch what validation can't.

Known Issues & Pitfalls

The cross-sheet ! trap (short)

Shells (bash history expansion, zsh splitting) and CLI arg parsing mangle ! in Sheet1!A1 into \!. A formula containing \! is silently broken — it renders as literal text and references nothing.

Fix. Use a batch heredoc with single-quoted delimiter (` — don't improvise | | --prop color=... on a cell | Ambiguous — use font.color (text) or fill` (bg). Also applies inside batch JSON: always use full dotted nam

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: zhu1090093659
• Source: zhu1090093659/deepseek-pp
• License: Apache-2.0
• Homepage: https://chromewebstore.google.com/detail/deepseek++/kdmpkkahkhdmdhfkdihkopikgcocbpbf?hl=zh-CN&authuser=0

Install and usage instructions live in the source repository linked above.