Jaw XLSX

XLSX Skill

Use this skill for .xlsx, .xlsm, .csv, and .tsv work that ends in an Excel workbook.

OfficeCLI routing and consent rule:

• First check whether officecli is available with a probe that works in the agent's actual shell: command -v officecli on POSIX, Get-Command officecli -ErrorAction SilentlyContinue in PowerShell. command -v is not a PowerShell builtin or cmdlet — on Windows it prints nothing, sets no exit code, and raises no error, so it is indistinguishable from "not installed" and the agent asks to install a tool that is already there (#298). officecli --version is the portable fallback: a non-zero exit or no output means missing.
• If installed, recommend OfficeCLI first for high-fidelity XLSX mutations, inspection, validation, batch/resident flows, CJK/rhwp-aware work, and Office-compatible output.
• If missing, do not auto-install. Present choices before proceeding:
  1. Install forked OfficeCLI from https://github.com/lidge-jun/OfficeCLI via bash "$(npm root -g)/cli-jaw/scripts/install-officecli.sh".
  2. Continue with lightweight fallback tools for the current task, with limitations stated.
  3. Stop or cancel.
• Before taking a lightweight fallback path, ask the user again and state what fidelity/features may be lost.
• If the user chooses lightweight mode, save that preference to memory for future Office work.
• Use upstream/vanilla iOfficeAI/OfficeCLI only when the user explicitly asks for upstream behavior.

OfficeCLI is the recommended advanced backend for workbook mutation and inspection. Primary data pipeline: pandas for DataFrame transforms, joins, aggregations. Lightweight fallback: Python OOXML scripts (scripts/*.py) and openpyxl for tasks OfficeCLI/pandas cannot cover, or when the user chooses lightweight mode: formula recalc, raw OOXML inspection, complex openpyxl styling. See §3.

Do NOT use this skill for Word, HTML dashboards, or external database orchestration.

OfficeCLI discovery rule: use officecli --help for workflow entry points and officecli help xlsx ... --json for exact workbook element/property schema. Do not rely on stale property names from memory.

Same-file execution rule: run OfficeCLI commands against the same .xlsx/.xlsm sequentially. Do not run officecli view, officecli validate, officecli query, or officecli get in parallel against one package. If a file lock occurs, stop and report the exact command and path before making a copy or retrying.

1. Quick Decision

2. Reference-Based Editing (Edit > Create from Scratch)

When the user says "format like X.xlsx", "match existing style", "based on template", or provides a source file — start from the source. Don't rebuild from scratch.

Core Rule: Preserve Existing Templates

When modifying files, match existing format, style, and conventions exactly. Excel workbooks often have:

• Named ranges that formulas depend on
• Conditional formatting rules on specific ranges
• Data validation on input cells
• Custom number formats per column
• Sheet protection / locked cells
• Hidden/very-hidden sheets

Rebuilding these from scratch silently breaks formulas, validation, and visual consistency.

Workflow

1. Copy the source: cp source.xlsx target.xlsx — inherits sheets, named ranges, styles, validation, CF rules
2. Open with officecli open target.xlsx — daemon returns immediately (do NOT run as run_in_background)
3. Clear data cells only — keep sheet structure, named ranges, validation, conditional formatting
4. Write new values into the preserved structure — formulas auto-recalculate

Template Sources (priority order)

1. User-provided source file — first-class template
2. tests/fixtures/*.xlsx — pre-built examples shipped with this skill
3. officecli-financial-model/ templates — 3-statement, DCF, budget starting points
4. officecli-data-dashboard/ templates — chart/pivot/CF starting points
5. officecli create blank — only when nothing else applies

Example — Budget Template Reuse

# CORRECT: preserve named ranges + CF
cp Q3Budget.xlsx Q4Budget.xlsx
officecli open Q4Budget.xlsx
# Clear only data cells (keep headers, formulas, named ranges)
officecli set Q4Budget.xlsx "/Inputs/B2:B20" --prop value=""
# Write new Q4 inputs
officecli set Q4Budget.xlsx "/Inputs/B2" --prop value=15000 --prop type=number
officecli close Q4Budget.xlsx
python3 scripts/recalc.py Q4Budget.xlsx   # recalc formulas

# WRONG: rebuild from scratch, loses named ranges + CF
officecli create Q4Budget.xlsx
# ... all formulas break ...

3. Reference Materials & Script Map

officecli covers most XLSX tasks. pandas handles analysis. For formatting, recalc, and raw OOXML work, use these references + scripts.

References (references/)

Scripts (scripts/) — Python OOXML Toolkit

Editing Escalation Ladder

When officecli can't do the job, escalate in this order:

Escalation signals:

• Wrote formulas via officecli → L3 scripts/recalc.py (non-negotiable)
• Need data transforms (groupby, pivot, merge) → L4 pandas
• Need complex CF / data validation / named-range math → L4 openpyxl (see references/openpyxl_guide.md)
• Need financial convention (blue input, black formula, source annotation) → Read references/financial_conventions.md
• Need raw OOXML tweak (shared strings, theme XML) → L2 officecli raw-set

4. Subskill References

Read only the subskill relevant to the current task. Do not preload all.

5. Design Principles for Spreadsheets

Professional spreadsheets need clear structure, correct formulas, and intentional formatting.

Intent discovery (before building): ask audience (board / analyst / ops), print-vs-screen, and brand palette — then match number formats, column widths, and conditional formatting.

Named spreadsheet anti-slop (AI-tells — pattern → fix):

1. Default 8.43 widths — unsized columns → fit to content (CJK ~2× width).
2. Rainbow conditional formatting — competing CF colors → one color-scale / data-bar per metric.
3. No number formatting — raw 1234.5/0.4 → currency/accounting/percent (§12); negatives in parens, zero as dash.
4. 3D / pie / dual-axis overload — decorative chart types → column/line/bar by data shape; pie only ≤5 parts; no 3D.
5. Hardcoded results — typed-in totals → live formulas + recalc.py verification.
6. Merged-cell abuse + gridlines-as-structure — merged headers break sort/filter; raw gridlines → center-across-selection; deliberate fills/borders.
7. Fake names / placeholder data — invented samples in delivered output → real data only.

Named palettes + formatting conventions + personality→workbook-type map: references/design-system.md.

Core Rule: Preserve Existing Templates (MANDATORY)

When modifying files, match existing format, style, and conventions exactly. Rebuilding from scratch silently breaks named ranges, CF rules, and validation. See §2.

Alignment

• Numbers = right-aligned (default in Excel; do not override)
• Labels / text = left-aligned
• Headers = center or left, bold, with fill color

Color Coding Convention

Apply via: officecli set model.xlsx /Inputs/B2:B20 --prop font.color=0000FF

Full financial convention details: read references/financial_conventions.md.

3-Sheet Separation

Source Annotations

Every hard-coded input cell MUST have a comment documenting its origin:

officecli add model.xlsx /Inputs --type comment \
  --prop ref=B1 --prop text='Source: Company 10-K FY2025 p.45'

Use Formulas, Not Hardcoded Values (MANDATORY)

The spreadsheet must remain dynamic -- when source data changes, formulas recalculate automatically. Hardcoded values break this contract.

# WRONG -- hardcoded calculation result
officecli set data.xlsx "/Sheet1/B10" --prop value=5000

# CORRECT -- let Excel calculate
officecli set data.xlsx "/Sheet1/B10" --prop formula="SUM(B2:B9)"

6. Mandatory Verification (NEVER SKIP)

Delivery Gate — treat verification as a gate, not a confirmation. Any failure (validate error, formula/recalc mismatch, placeholder leak) = REJECT, do not deliver. Fix → re-run the checks (incl. recalc.py) → repeat until a pass finds zero new issues. After 3 rounds without convergence, STOP and report the likely root cause.

After ANY XLSX edit, ALWAYS execute both steps:

# Step 1: structural validation
officecli validate output.xlsx

# Step 2: visual PDF proof
soffice --headless --convert-to pdf --outdir /tmp output.xlsx
# Check: formula results, cell formatting, chart rendering, merged cells

If formulas were written, ADD:

# Step 3: recalc pass (formulas do NOT calculate until this runs)
python3 scripts/recalc.py output.xlsx

Skip none. validate catches structural errors; the PDF catches rendering issues (truncated CJK, broken charts, invisible text); recalc.py updates cached values for all formulas.

Formula correctness requires recalculation evidence. If LibreOffice/Excel recalculation is unavailable, report formulas as structurally written but not runtime-verified.

7. Prerequisite Check

# Required
python3 -c "import pandas, openpyxl" || echo "MISSING: pip install pandas openpyxl"

# LibreOffice: check only; ask before installing when PDF/recalc is needed.
which soffice >/dev/null 2>&1 || echo "ASK USER: LibreOffice is not installed; install it for PDF conversion/recalculation or skip that output."

# OfficeCLI: check only; do not auto-install from a skill.
if ! command -v officecli >/dev/null 2>&1; then
  echo "ASK USER: install forked OfficeCLI from https://github.com/lidge-jun/OfficeCLI, continue lightweight, or stop."
  echo "Install command after approval: bash \"\$(npm root -g)/cli-jaw/scripts/install-officecli.sh\""
fi

8. Tool Discovery

When unsure about property names, value formats, or command syntax, run help instead of guessing. One help query is faster than guess-fail-retry loops.

officecli --help
officecli help xlsx
officecli help xlsx set                 # All settable elements
officecli help xlsx set cell --json     # Cell properties in detail
officecli help xlsx add --json          # Addable element types
officecli view --help                   # View modes
officecli help xlsx query
officecli help all --jsonl | grep '"format":"xlsx"'

9. Core Workflows

Execution Model

Run commands one at a time. Do not write all commands into a shell script and execute it as a single block.

OfficeCLI is incremental: every add, set, and remove immediately modifies the file and returns output. Use this to catch errors early:

1. One command at a time, then read the output. Check the exit code before proceeding.
2. Non-zero exit = stop and fix immediately. Do not continue building on a broken state.
3. Verify after structural operations. After adding a sheet, chart, pivot table, or named range, run get or validate before building on top of it.

Reading & Analyzing

officecli view data.xlsx text                              # Plain text dump
officecli view data.xlsx text --start 1 --end 50 --cols A,B,C  # Filtered
officecli view data.xlsx outline                           # Structure overview
officecli view data.xlsx annotated                         # Type/formula annotations
officecli view data.xlsx stats                             # Summary statistics
officecli view data.xlsx issues                            # Empty sheets, broken formulas

Element Inspection (PATH Syntax)

officecli get data.xlsx /                       # Workbook root (all sheets, doc props)
officecli get data.xlsx "/Sheet1"               # Sheet overview
officecli get data.xlsx "/Sheet1/A1"            # Single cell (value, type, formula, font, fill)
officecli get data.xlsx "/Sheet1/A1:D10"        # Cell range
officecli get data.xlsx "/Sheet1/row[1]"        # Row properties
officecli get data.xlsx "/Sheet1/col[A]"        # Column properties
officecli get data.xlsx "/Sheet1/chart[1]"      # Chart
officecli get data.xlsx "/Sheet1/table[1]"      # Table (ListObject)
officecli get data.xlsx "/Sheet1/validation[1]" # Data validation rule
officecli get data.xlsx "/Sheet1/cf[1]"         # Conditional formatting rule
officecli get data.xlsx "/Sheet1/comment[1]"    # Comment
officecli get data.xlsx "/namedrange[1]"        # Named range

Add --depth N to expand children, --json for structured output. Excel-native notation also supported: Sheet1!A1, Sheet1!A1:D10.

CSS-like Queries

officecli query data.xlsx 'cell:has(formula)'           # Cells with formulas
officecli query data.xlsx 'cell:contains("Revenue")'    # Cells containing text
officecli query data.xlsx 'cell:empty'                  # Empty cells
officecli query data.xlsx 'cell[type=Number]'           # Cells by type
officecli query data.xlsx 'cell[font.bold=true]'        # Cells by formatting
officecli query data.xlsx 'B[value!=0]'                 # Column B non-zero
officecli query data.xlsx 'Sheet1!cell[value="100"]'    # Sheet-scoped
officecli query data.xlsx 'chart'                       # Find all charts
officecli query data.xlsx 'table'                       # Find all tables
officecli query data.xlsx 'pivottable'                  # Find all pivot tables

Operators: =, !=, ~= (contains), >=, <=, [attr] (exists).

Cell Formatting

# Column width (character units, ~1 char = 7px) -- no auto-fit available
officecli set data.xlsx "/Sheet1/col[A]" --prop width=15

# Row height (points)
officecli set data.xlsx "/Sheet1/row[1]" --prop height=20

# Freeze panes (headers)
officecli set data.xlsx "/Sheet1" --prop freeze=A2

# Print area
officecli set data.xlsx "/Sheet1" --prop printArea="A1:F20"

Common widths: labels=20-25, numbers=12-15, dates=12, short codes=8-10.

Data Validation

# Dropdown list
officecli add data.xlsx /Sheet1 --type validation \
  --prop sqref="C2:C100" --prop type=list \
  --prop formula1="Yes,No,Maybe" --prop showError=true

# Number range
officecli add data.xlsx /Sheet1 --type validation \
  --prop sqref="D2:D100" --prop type=decimal \
  --prop operator=between --prop formula1=0 --prop formula2=100

Batch Mode

cat <<'EOF' | officecli batch data.xlsx
[
  {"command":"set","path":"/Sheet1/A1","props":{"value":"Revenue","bold":"true","fill":"1F4E79","font.color":"FFFFFF"}},
  {"command":"set","path":"/Sheet1/B1","props":{"value":"Q1","bold":"true","fill":"1F4E79","font.color":"FFFFFF"}}
]
EOF

Batch supports: add, set, get, query, remove, move, swap, view, raw, raw-set, validate. Batch fields: command, path, parent, type, from, to, index, after, before, props (dict), selector, mode, depth, part, xpath, action, xml.

Error decoding: 'X' is an invalid start of a value = shell syntax leaked into JSON. Use heredoc cat <<'EOF' | officecli batch FILE with single-quoted delimiter.

Resident Mode

officecli open data.xlsx        # Returns IMMEDIATELY; daemon in bg
officecli add data.xlsx ...     # All commands run in memory -- fast
officecli set data.xlsx ...
officecli close data.xlsx       # Write once to disk

Do NOT run officecli open as a background shell job. It returns immediately and the daemon lives in the background automatically. Running it as a monitored shell creates zombies and file locks.

CSV / TSV Import

officecli import f.xlsx /Sheet1 data.csv --header           # CSV
officecli import f.xlsx /Sheet1 data.tsv --header --format tsv  # TSV
cat data.csv | officecli import f.xlsx /Sheet1 --stdin --header  # stdin

Chart Creation

# Add a column chart from data range
officecli add doc.xlsx /Sheet1 --type chart --prop chartType=column --prop dataRange=Sheet1!A1:D10 --prop title="Revenue by Quarter"
# NOTE: a worksheet RANGE uses --prop dataRange=. --prop data= is for an INLINE series only ('Name:1,2,3') and rejects a range.

# For detailed chart customization (series, axes, legends), read ./officecli-data-dashboard/SKILL.md

Three ways to feed chart data — pick ONE per chart (mixing at add-time is a trap):

• Single-column trap: dataRange="Sheet1!B2:B13" is rejected ("Chart requires data") — widen to include the category column (A2:B13) or use per-series with explicit categories.
• Always prefix dataRange with the sheet (Summary!A17:C22, not A17:C22) — the sheet-less form is unreliable.
• Series + anchor are immutable after create — to resize/move/add a series, remove the chart then add again (remove shifts indices and re-add appends; rebuild in order to preserve chart order).

10. pandas Pipeline

pandas is the PRIMARY analysis layer, NOT legacy. It is the first-choice tool for data transforms that officecli should not reimplement.

Standard Flow: pandas -> CSV -> officecli

pandas DataFrame
    |  .to_csv("data.csv", index=False)
    v
officecli create output.xlsx
officecli import output.xlsx /Sheet1 data.csv --header
officecli batch output.xlsx --commands '[formatting...]'
officecli validate output.xlsx
python3 scripts/recalc.py output.xlsx   # if formulas added

This path keeps pandas focused on transforms and lets officecli own the OOXML package. One import command replaces dozens of set cell calls.

For complex openpyxl-based styling (CF gradients, data bars, custom chart XML), see references/openpyxl_guide.md.

11. Formula Recalculation (CRITICAL)

officecli writes formulas but does NOT recalculate them. openpyxl does not recalc either. Excel displays the cached value until recalc runs.

Always run a recalc pass after formula generation:

python3 scripts/recalc.py output.xlsx
# or
soffice --headless --calc --convert-to xlsx output.xlsx

Recalc Checklist

• Sample formulas use correct sheet/range references
• No off-by-one row mapping mistakes
• No circular references
• recalc.py returns success with zero errors
• Final cached values match expected outputs

12. Number Format Reference

Standard Formats

Korean Number Formats

Shell quoting: Number formats containing $ must use single quotes ('$#,##0') or heredoc in batch mode. Double quotes cause shell variable expansion.

13. Common Pitfalls

Formula Verification Checklist

• Test 2-3 sample cell references: verify they pull correct values
• Column mapping: confirm cell references point to intended columns
• Row offsets: check formula ranges include all data rows
• Division by zero: verify denominators are non-zero or wrapped in IFERROR
• Cross-sheet references: use correct Sheet1!A1 format
• Cross-sheet formula escaping: run officecli get on 2-3 cross-sheet formula cells -- confirm no \! in the formula string
• Named ranges: verify ref values match actual data locations
• Edge cases: test with zero values, negative numbers, empty cells
• Chart data vs formula results: verify each chart data point matches the source cell

Pre-Delivery Checklist

• Metadata set (title, author)
• All formula cells contain formulas (not hardcoded values)
• python3 scripts/recalc.py executed after formula writes
• No formula error values (#REF!, #DIV/0!, #VALUE!, #NAME?, #N/A)
• Number formats applied (currency, percentage, dates)
• Column widths set explicitly (no default 8.43)
• Header row styled (bold, fill, freeze panes)
• Data validation on input cells
• Charts have titles and readable axis labels
• Chart data matches source cells (prefer cell-range refs over inline data)
• Named ranges defined for key assumptions
• Document validates with officecli validate
• No placeholder text remaining
• Comments on hardcoded assumption values documenting their source (see references/financial_conventions.md)

QA Error Scan

Approach QA as a bug hunt, not a confirmation step. If you found zero issues on first inspection, you were not looking hard enough.

officecli view data.xlsx issues                         # Broken formulas, missing refs
officecli query data.xlsx 'cell:has(formula)'           # Verify formulas exist
officecli query data.xlsx 'cell:contains("#REF!")'      # Formula error scan
officecli query data.xlsx 'cell:contains("#DIV/0!")'
officecli query data.xlsx 'cell:contains("#VALUE!")'
officecli query data.xlsx 'cell:contains("#NAME?")'
officecli query data.xlsx 'cell:contains("#N/A")'
officecli validate data.xlsx                            # Structural validation

Verification Loop

1. Generate workbook
2. Run view issues + view annotated (sample ranges) + validate
3. python3 scripts/recalc.py if formulas present
4. Run formula error queries (all 5 error types)
5. List issues found (if none found, look again more critically)
6. Fix issues
7. Re-verify affected areas -- one fix often creates another problem
8. Repeat until a full pass reveals no new issues

Do not declare success until you have completed at least one fix-and-verify cycle.

14. Anti-Patterns (NEVER DO)

Formula results hardcoded as values -- STRICTLY FORBIDDEN. The workbook must remain recalculable when inputs change.

Fictional example data leaking into output -- FORBIDDEN. Never use placeholder names in deliverable workbooks.

Merged cell abuse -- FORBIDDEN. Merged cells break sorting, filtering, screen readers, and programmatic access. Use center-across-selection or column width adjustments instead. Exception: a single title row.

Sheet names containing ! -- ESCAPE WARNING. Excel uses ! as the sheet-range delimiter.

Rebuilding existing template from scratch -- STRICTLY FORBIDDEN when user provides a source file. cp first; match existing conventions. Named ranges, CF, validation, locked cells cannot be trivially recreated. See §2.

Skipping recalc.py after formula writes -- FORBIDDEN. Cached formula values stay stale until recalc runs. Deliverables show wrong numbers.

Ignoring reference materials -- FORBIDDEN. For financial conventions, read references/financial_conventions.md. For complex openpyxl styling, read references/openpyxl_guide.md. The Pre-officecli openpyxl/pandas workflow is still available.

15. Known Issues

16. Dependencies