OfficeCLI XLSX Skill

BEFORE YOU START (CRITICAL)

Every time before using officecli, run this check:

CODEBLOCK0

---

Quick Reference

Task	Action
Read / analyze content	Use view and get commands below
Edit existing workbook

Read editing.md |
| Create from scratch | Read creating.md |

---

Reading & Analyzing

Text Extraction

CODEBLOCK1

Plain text dump, tab-separated per row, with [/Sheet1/row[N]] prefixes. Flags: --mode, --start N, --end N, --max-lines N, --cols A,B,C.

Structure Overview

CODEBLOCK2

Sheets with row/column counts and formula counts per sheet.

Detailed Inspection

CODEBLOCK3

Cell values with type/formula annotations, warnings for errors and empty cells.

Statistics

CODEBLOCK4

Summary statistics across all sheets.

Issue Detection

CODEBLOCK5

Empty sheets, broken formulas, missing references.

Element Inspection

CODEBLOCK6

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

CSS-like Queries

CODEBLOCK7

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

---

Design Principles

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

Use Formulas, Not Hardcoded Values (MANDATORY)

This is the single most important principle. The spreadsheet must remain dynamic -- when source data changes, formulas recalculate automatically. Hardcoded values break this contract.

CODEBLOCK8

Financial Model Color Coding

Convention	Color	Use For
Blue text	INLINECODE18	Hardcoded inputs, scenario-variable numbers
Black text

font.color=000000 | ALL formulas and calculations | | Green text | font.color=008000 | Cross-sheet links within same workbook | | Red text | font.color=FF0000 | External references | | Yellow background | fill=FFFF00 | Key assumptions needing attention |

These are industry-standard financial modeling conventions. Apply when building financial models. For non-financial workbooks, use project-appropriate styling.

Number Format Strings

Type	Format String	Example Output	Code
Currency	INLINECODE23	$1,234	INLINECODE24
Currency (neg parens)

$#,##0;($#,##0);"-" | ($1,234) | --prop numFmt='$#,##0;($#,##0);"-"' | | Percentage | 0.0% | 12.5% | --prop numFmt="0.0%" | | Decimal | #,##0.00 | 1,234.56 | --prop numFmt="#,##0.00" | | Accounting | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) | $ 1,234 | --prop numFmt='_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' | | Date | yyyy-mm-dd | 2026-03-27 | --prop numFmt="yyyy-mm-dd" | | Date (long) | mmmm d, yyyy | March 27, 2026 | --prop numFmt="mmmm d, yyyy" | | Year as text | @ | 2026 (not 2,026) | --prop type=string | | Multiples | 0.0x | 12.5x | --prop numFmt="0.0x" | | Zeros as dash | #,##0;-#,##0;"-" | - | --prop numFmt='#,##0;-#,##0;"-"' |

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

Column Width and Row Height

CODEBLOCK9

There is no auto-fit. Set column widths explicitly. Common widths: labels=20-25, numbers=12-15, dates=12, short codes=8-10.

Freeze Panes

CODEBLOCK10

Print Area

CODEBLOCK11

Data Validation for Input Cells

CODEBLOCK12

Always add data validation on input cells in financial models and trackers. It prevents data entry errors.

Print Area and Page Setup

For print-ready workbooks, set appropriate column widths and row heights. Consider which sheets need headers repeated on each page.

---

QA (Required)

Assume there are problems. Your job is to find them.

Your first spreadsheet build is almost never correct. Approach QA as a bug hunt, not a confirmation step. If you found zero issues on first inspection, you were not looking hard enough.

Content QA

CODEBLOCK13

When editing templates, check for leftover placeholders:

CODEBLOCK14

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 and confirm no \! in the formula string. If \! is present, the formula is broken -- delete and re-set using batch/heredoc.
• [ ] Named ranges: verify ref values match actual data locations
• [ ] Edge cases: test with zero values, negative numbers, empty cells
• [ ] Chart data vs formula results: for every chart with hardcoded/inline data, verify each data point matches the corresponding formula cell result. Use officecli get on the source cells and compare against chart series values. Mismatches here are silent data integrity bugs.

Validation

CODEBLOCK15

Pre-Delivery Checklist

• - [ ] Metadata set (title, author)
• [ ] All formula cells contain formulas (not hardcoded values)
• [ ] 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 -- charts with hardcoded/inline data can drift from formula results. For each chart, verify every data point against the corresponding cell value. Prefer cell-range references (series1.values="Sheet1!B2:B6") over inline data to avoid transcription errors.
• [ ] Named ranges defined for key assumptions
• [ ] Document validates with INLINECODE52
• [ ] No placeholder text remaining
• [ ] Comments on hardcoded assumption values documenting their source

NOTE: Unlike pptx (SVG/HTML), xlsx has no visual preview mode. Verification relies on view text, view annotated, view stats, view issues, validate, and formula queries. For visual verification, the user must open the file in Excel.

Verification Loop

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

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

---

Common Pitfalls

Pitfall	Correct Approach
INLINECODE61	Use --prop name="foo" -- all attributes go through INLINECODE63
Guessing property names

Run officecli xlsx set cell to see exact names |
| \n in shell strings | Use \\n for newlines in --prop text="line1\\nline2" |
| Modifying an open file | Close the file in Excel first |
| Hex colors with # | Use FF0000 not #FF0000 -- no hash prefix |
| Paths are 1-based | "/Sheet1/row[1]", "/Sheet1/col[1]" -- XPath convention |
| --index is 0-based | --index 0 = first position -- array convention |
| Unquoted [N] in zsh/bash | Shell glob-expands /Sheet1/row[1] -- always quote paths: "/Sheet1/row[1]" |
| Sheet names with spaces | Quote the full path: "/My Sheet/A1" |
| Formula prefix = | OfficeCLI strips the = -- use formula="SUM(A1:A10)" not formula="=SUM(A1:A10)" |
| Cross-sheet ! in formulas | CRITICAL: The ! in Sheet1!A1 can be corrupted by shell quoting. Use batch/heredoc for cross-sheet formulas, or double quotes: --prop "formula==Sheet1!A1". NEVER use single quotes for formulas containing !. After setting, verify with officecli get that the formula shows Sheet1!A1 (no backslash before !). |
| Hardcoded calculated values | Use --prop formula="SUM(B2:B9)" not --prop value=5000 |
| $ and ' in batch JSON | Use heredoc: cat <<'EOF' \| officecli batch -- single-quoted delimiter prevents shell expansion |
| Number format with $ | Shell interprets $ -- use single quotes: numFmt='$#,##0' |
| Year displayed as "2,026" | Set cell type to string: --prop type=string or use numFmt="@" |

---

Performance: Resident Mode

CODEBLOCK16

For multi-step workflows (3+ commands on the same file), use open/close.

Performance: Batch Mode

CODEBLOCK17

Batch supports: add, set, get, query, remove, move, view, raw, raw-set, validate.

Batch fields: command, path, parent, type, from, to, index, props (dict), selector, mode, depth, part, xpath, action, xml.

INLINECODE126 = container to add into (for add). path = element to modify (for set, get, remove).

Batch mode is the default for multi-cell operations. A financial model with 50+ cells MUST use batch, not individual commands.

---

Known Issues

Issue	Workaround
Chart series cannot be added after creation	INLINECODE132 and set --prop seriesN= on an existing chart can only update existing series. To add series, delete and recreate: officecli remove data.xlsx "/Sheet1/chart[1]" then officecli add with all series.
No visual preview

Unlike pptx (SVG/HTML), xlsx has no built-in rendering. Use view text/view annotated/view stats/view issues for verification. Users must open in Excel for visual check. |
| Formula cached values for new formulas | OfficeCLI writes formula strings natively. For newly added formulas, the cached value may not update until the file is opened in Excel/LibreOffice. Existing formula cached values are preserved. |
| No auto-fit column width | No "auto-fit" column width based on content. Set width explicitly on each column. |
| Shell quoting in batch with echo | echo '...' \| officecli batch fails when JSON values contain apostrophes or $. Use heredoc: cat <<'EOF' \| officecli batch data.xlsx. |
| Batch intermittent failure | Batch+resident mode has a high failure rate (up to 1-in-3 in some sessions). For maximum reliability: (1) prefer batch WITHOUT resident mode, (2) keep batches to 8-12 operations, (3) always check batch output for failures, (4) retry failed operations individually. For critical formulas (especially cross-sheet), consider using individual set commands which have 100% reliability. |
| Data bar default min/max invalid | Creating a data bar without --prop min=N --prop max=N produces empty val attributes in cfvo elements, which may be rejected by strict XML validators or Excel. Always specify explicit min and max values. |
| Cell protection requires sheet protection | locked and formulahidden properties only take effect when the sheet itself is protected. |

---

Help System

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.

CODEBLOCK18