xlsx · diff
v1.0 to git:20261104.fc13d00
64 added, 132 removed. Audit A to A.
---
name: xlsx
- description: "Open, create, read, analyze, edit, or validate Excel/spreadsheet files (.xlsx, .xlsm, .csv, .tsv). Use when the user asks to create, build, modify, analyze, read, validate, or format any Excel spreadsheet, financial model, pivot table, or tabular data file. Covers: creating new xlsx from scratch, reading and analyzing existing files, editing existing xlsx with zero format loss, formula recalculation and validation, and applying professional financial formatting standards. Triggers on 'spreadsheet', 'Excel', '.xlsx', '.csv', 'pivot table', 'financial model', 'formula', or any request to produce tabular data in Excel format."
+ description: Create, read, edit, format, and validate Excel spreadsheets. Use for XLSX files, spreadsheet formulas, tables, and financial models, budgets, forecasts, scenarios, or management reports delivered in Excel. Select both the file operation and the business scenario; financial workbooks also load the finance reference. Read CSV/TSV data without changing the source.
license: MIT
- metadata:
- version: "1.0"
- category: productivity
- sources:
- - ECMA-376 Office Open XML File Formats
- - Microsoft Open XML SDK documentation
---
- # Excel Skill
-
- Handle the request directly. Do NOT spawn sub-agents. Always write the output file the user requests.
-
- ## Environment and recalculation
-
- Set `SKILL_DIR` to the absolute directory containing this file (the `xlsx/`
- subdirectory of the Office package). Resolve reference paths relative to it.
-
- XML packing/editing and static formula checks use Python's standard library.
- Install `pandas openpyxl` only when the chosen read/analysis path needs them.
- For dynamic formula evaluation, install LibreOffice **Calc** (for example
- `sudo apt-get install libreoffice-calc` on Debian/Ubuntu); `soffice --version`
- does not prove Calc is available. Recalculate into a separate output and check
- its cached results with `formula_check.py`. A successful process exit without
- a newly written workbook is a failure, not a successful recalculation.
- Keep the source workbook intact and use the selected template's formatting.
-
- ## Task Routing
-
- | Task | Method | Guide |
- |------|--------|-------|
- | **READ** — analyze existing data | `xlsx_reader.py` + pandas | `references/read-analyze.md` |
- | **CREATE** — new xlsx from scratch | XML template | `references/create.md` + `references/format.md` |
- | **EDIT** — modify existing xlsx | XML unpack→edit→pack | `references/edit.md` (+ `format.md` if styling needed) |
- | **FIX** — repair broken formulas in existing xlsx | XML unpack→fix `<f>` nodes→pack | `references/fix.md` |
- | **VALIDATE** — check formulas | `formula_check.py` | `references/validate.md` |
-
- ## READ — Analyze data (read `references/read-analyze.md` first)
-
- Start with `xlsx_reader.py` for structure discovery, then pandas for custom analysis. Never modify the source file.
-
- **Formatting rule**: When the user specifies decimal places (e.g. "2 decimal places"), apply that format to ALL numeric values — use `f'{v:.2f}'` on every number. Never output `12875` when `12875.00` is required.
-
- **Aggregation rule**: Always compute sums/means/counts directly from the DataFrame column — e.g. `df['Revenue'].sum()`. Never re-derive column values before aggregation.
-
- ## CREATE — XML template (read `references/create.md` + `references/format.md`)
-
- Copy `templates/minimal_xlsx/` → edit XML directly → pack with `xlsx_pack.py`. Every derived value MUST be an Excel formula (`<f>SUM(B2:B9)</f>`), never a hardcoded number. Apply font colors per `format.md`.
-
- ## EDIT — XML direct-edit (read `references/edit.md` first)
-
- **CRITICAL — EDIT INTEGRITY RULES:**
- 1. **NEVER create a new `Workbook()`** for edit tasks. Always load the original file.
- 2. The output MUST contain the **same sheets** as the input (same names, same data).
- 3. Only modify the specific cells the task asks for — everything else must be untouched.
- 4. **After saving output.xlsx, verify it**: open with `xlsx_reader.py` or `pandas` and confirm the original sheet names and a sample of original data are present. If verification fails, you wrote the wrong file — fix it before delivering.
-
- Never use openpyxl round-trip on existing files (corrupts VBA, pivots, sparklines). Instead: unpack → use helper scripts → repack.
+ # Excel workbooks
- **"Fill cells" / "Add formulas to existing cells" = EDIT task.** If the input file already exists and you are told to fill, update, or add formulas to specific cells, you MUST use the XML edit path. Never create a new `Workbook()`. Example — fill B3 with a cross-sheet SUM formula:
- ```bash
- python3 "$SKILL_DIR/scripts/xlsx_unpack.py" input.xlsx /tmp/xlsx_work/
- # Find the target sheet's XML via xl/workbook.xml → xl/_rels/workbook.xml.rels
- # Then use the Edit tool to add <f> inside the target <c> element:
- # <c r="B3"><f>SUM('Sales Data'!D2:D13)</f><v></v></c>
- python3 "$SKILL_DIR/scripts/xlsx_pack.py" /tmp/xlsx_work/ output.xlsx
- ```
+ Select **both an operation and a business scenario** before authoring. Choosing
+ CREATE or EDIT does not finish scenario selection. User requirements and an
+ existing template or house style take precedence over these defaults.
- **Add a column** (formulas, numfmt, styles auto-copied from adjacent column):
- ```bash
- python3 "$SKILL_DIR/scripts/xlsx_unpack.py" input.xlsx /tmp/xlsx_work/
- python3 "$SKILL_DIR/scripts/xlsx_add_column.py" /tmp/xlsx_work/ --col G \
- --sheet "Sheet1" --header "% of Total" \
- --formula '=F{row}/$F$10' --formula-rows 2:9 \
- --total-row 10 --total-formula '=SUM(G2:G9)' --numfmt '0.0%' \
- --border-row 10 --border-style medium
- python3 "$SKILL_DIR/scripts/xlsx_pack.py" /tmp/xlsx_work/ output.xlsx
- ```
- The `--border-row` flag applies a top border to ALL cells in that row (not just the new column). Use it when the task requires accounting-style borders on total rows.
+ Set `SKILL_DIR` to the absolute directory containing this file. Read only the
+ common guidance, applicable scenario, and operation needed for the task.
- **Insert a row** (shifts existing rows, updates SUM formulas, fixes circular refs):
- ```bash
- python3 "$SKILL_DIR/scripts/xlsx_unpack.py" input.xlsx /tmp/xlsx_work/
- # IMPORTANT: Find the correct --at row by searching for the label text
- # in the worksheet XML, NOT by using the row number from the prompt.
- # The prompt may say "row 5 (Office Rent)" but Office Rent might actually
- # be at row 4. Always locate the row by its text label first.
- python3 "$SKILL_DIR/scripts/xlsx_insert_row.py" /tmp/xlsx_work/ --at 5 \
- --sheet "Budget FY2025" --text A=Utilities \
- --values B=3000 C=3000 D=3500 E=3500 \
- --formula 'F=SUM(B{row}:E{row})' --copy-style-from 4
- python3 "$SKILL_DIR/scripts/xlsx_pack.py" /tmp/xlsx_work/ output.xlsx
- ```
- **Row lookup rule**: When the task says "after row N (Label)", always find the row by searching for "Label" in the worksheet XML (`grep -n "Label" /tmp/xlsx_work/xl/worksheets/sheet*.xml` or check sharedStrings.xml). Use the actual row number + 1 for `--at`. Do NOT call `xlsx_shift_rows.py` separately — `xlsx_insert_row.py` calls it internally.
+ ## Layer 1 — Common workbook rules
- **Apply row-wide borders** (e.g. accounting line on a TOTAL row):
- After running helper scripts, apply borders to ALL cells in the target row, not just newly added cells. In `xl/styles.xml`, append a new `<border>` with the desired style, then append a new `<xf>` in `<cellXfs>` that clones each cell's existing `<xf>` but sets the new `borderId`. Apply the new style index to every `<c>` in the row via the `s` attribute:
- ```xml
- <!-- In xl/styles.xml, append to <borders>: -->
- <border>
- <left/><right/><top style="medium"/><bottom/><diagonal/>
- </border>
- <!-- Then append to <cellXfs> an xf clone with the new borderId for each existing style -->
- ```
- **Key rule**: When a task says "add a border to row N", iterate over ALL cells A through the last column, not just newly added cells.
+ - Preserve the source. Read-only analysis must not save, restyle, or recalculate
+ the original. Perform edits and application checks on separate copies.
+ - Use live Excel formulas for derived workbook outputs; keep source records and
+ adjustable inputs identifiable. Do not replace formulas with cached answers.
+ - Preserve unrelated sheets, formulas, styles, controls, and package features.
+ A narrow edit is not permission to rebuild or redesign the workbook.
+ - For new workbooks or requested formatting, read
+ [common presentation](references/common/presentation.md). For narrow edits, apply it only
+ to changed cells without overriding the supplied layout.
+ - For imports, new editable inputs or authorized input changes, read
+ [source data and input contracts](references/common/data-and-inputs.md) before
+ populating cells. Keep data types, source assumptions and current narrative coherent.
+ - For new reader-facing analytical reports/dashboards or authorized chart changes,
+ read [analytical charts](references/common/charts.md) during planning. Choose charts for
+ the question, not a fixed count; preserve data-only and narrow-edit scope.
- **Manual XML edit** (for anything the helper scripts don't cover):
- ```bash
- python3 "$SKILL_DIR/scripts/xlsx_unpack.py" input.xlsx /tmp/xlsx_work/
- # ... edit XML with the Edit tool ...
- python3 "$SKILL_DIR/scripts/xlsx_pack.py" /tmp/xlsx_work/ output.xlsx
- ```
+ ## Layer 2 — Business scenario
- ## FIX — Repair broken formulas (read `references/fix.md` first)
+ | Requested work product | Read before planning the workbook |
+ | --- | --- |
+ | Financial model, revenue/cost budget, forecast, valuation, financial statements, scenario/sensitivity analysis, or financial management report | [Finance](references/domains/finance.md), even if the request never says "finance" |
+ | Operational tracker, inventory, roster, ordinary data table, or other non-financial spreadsheet | Common presentation only; do not impose financial colors or extra model tabs |
+ | One-cell update, formula repair, or template filling in an existing financial file | Preserve the existing structure and house style; use only the finance rules relevant to the requested change |
+ | Read-only extraction or analysis | [Read and analyze](references/operations/read-analyze.md); do not redesign the source |
- This is an EDIT task. Unpack → fix broken `<f>` nodes → pack. Preserve all original sheets and data.
+ For mixed workbooks, apply the finance layer to the financial model/report
+ areas, not every unrelated table. Finance guidance is bundled here: do not stop
+ at a generic spreadsheet route or require an external skill search to find it.
- ## VALIDATE — Check formulas (read `references/validate.md` first)
+ Examples:
+ - "Build next quarter's revenue, costs and hiring scenarios" → finance + CREATE.
+ - "Make a stock-count tracker" → common presentation + CREATE, not finance.
+ - "Change the tax-rate input in this workbook" → EDIT; keep its other tabs and
+ layout, rather than adding an executive-summary sheet.
- Run `formula_check.py` for static validation. Use `libreoffice_recalc.py` for dynamic recalculation when available.
+ ## Layer 3 — Operation and implementation
- ## Financial Color Standard
+ | Operation | Read | Tools |
+ | --- | --- | --- |
+ | Create a new XLSX | [Create](references/operations/create.md) | Prefer a suitable high-level library; raw OOXML remains available |
+ | Edit, fill a template, or repair formulas | [Edit](references/operations/edit.md) | Unpack → targeted XML changes → pack |
+ | Read or analyze XLSX/XLSM/CSV/TSV | [Read and analyze](references/operations/read-analyze.md) | `xlsx_reader.py`, then analysis tools as needed |
+ | Apply or change formatting | [Common presentation](references/common/presentation.md) and [Edit](references/operations/edit.md) for existing files | Append/reuse styles; change only intended cells |
+ | Check a created or edited workbook | [Validate](references/operations/validate.md) | Static checks, application checks, and task/scenario review |
- | Cell Role | Font Color | Hex Code |
- |-----------|-----------|----------|
- | Hard-coded input / assumption | Blue | `0000FF` |
- | Formula / computed result | Black | `000000` |
- | Cross-sheet reference formula | Green | `00B050` |
+ For new financial work using openpyxl, read [high-level primitives](references/implementation/authoring.md)
+ when choosing construction helpers. Check the calculation environment before
+ substantial formula authoring, then build, view and revise inside the same attempt.
+ Read [OOXML details](references/implementation/ooxml.md) only when directly changing
+ package relationships, cell types, strings, or style definitions. Style indices
+ are workbook-local; never copy an index from an example into an unrelated file.
- ## Key Rules
+ ## Package organization
- 1. **Formula-First**: Every calculated cell MUST use an Excel formula, not a hardcoded number
- 2. **CREATE → XML template**: Copy minimal template, edit XML directly, pack with `xlsx_pack.py`
- 3. **EDIT → XML**: Never openpyxl round-trip. Use unpack/edit/pack scripts
- 4. **Always produce the output file** — this is the #1 priority
- 5. **Validate before delivery**: `formula_check.py` exit code 0 = safe
+ `references/common/` owns reusable data and presentation rules; `domains/` owns
+ applicable business conventions; `operations/` owns the requested workflow;
+ `implementation/` owns library/package details. The links above are direct entry
+ points; load only applicable files. Keep `scripts/`, `templates/` and dependency
+ paths unchanged when following these guides.
- ## Utility Scripts
+ ## Finish
- ```bash
- python3 "$SKILL_DIR/scripts/xlsx_reader.py" input.xlsx # structure discovery
- python3 "$SKILL_DIR/scripts/formula_check.py" file.xlsx --json # formula validation
- python3 "$SKILL_DIR/scripts/formula_check.py" file.xlsx --report # standardized report
- python3 "$SKILL_DIR/scripts/xlsx_unpack.py" in.xlsx /tmp/work/ # unpack for XML editing
- python3 "$SKILL_DIR/scripts/xlsx_pack.py" /tmp/work/ out.xlsx # repack after editing
- python3 "$SKILL_DIR/scripts/xlsx_shift_rows.py" /tmp/work/ insert 5 1 # shift rows for insertion
- python3 "$SKILL_DIR/scripts/xlsx_add_column.py" /tmp/work/ --col G ... # add column with formulas
- python3 "$SKILL_DIR/scripts/xlsx_insert_row.py" /tmp/work/ --at 6 ... # insert row with data
- ```
+ Use the selected scenario's requirements while building, not as optional
+ polish after the workbook is complete. Run the applicable checks in VALIDATE
+ and fix discovered defects. A formula scanner's exit code is not a certificate
+ of financial accuracy, presentation quality, or usability. State unavailable
+ checks honestly and deliver only the files the user requested.