Xlsx

xlsx skill

Purpose: Give the agent a deterministic, script-first path for creating and sanity-checking .xlsx workbooks. The core operations (CSV → styled .xlsx, force formula recalculation, scan for formula errors, structural OOXML validation) are wrapped in small CLIs so the agent does not have to rewrite openpyxl boilerplate on every task and is never surprised by "formulas are stored but not calculated" (the single most common xlsx bug).

1. Red Flags (Anti-Rationalization)

STOP and READ THIS if you are thinking:

• "I'll just call DataFrame.to_excel and ship it." → WRONG. to_excel writes no styles, no frozen header, and no auto-filter. The result looks amateur. Use csv2xlsx.py.
• "I'll just call pd.DataFrame.from_records(rows).to_excel(out) on my LLM JSON output." → WRONG. Same to_excel styling gap, plus pandas' infer_objects heuristics silently promote mixed-type columns to object/float64 (an int column with one null becomes float64). Use json2xlsx.py — preserves native JSON types, ISO-date auto-coercion, csv2xlsx-style header.
• "I wrote formulas with openpyxl, so the numbers are there." → WRONG. openpyxl stores formulas as strings with no cached value. Every downstream consumer (pandas, charts, external apps) sees None. Run xlsx_recalc.py before shipping.
• "Validation says OK, the formulas must be fine." → WRONG. xlsx_validate.py scans for cached error values. If there are no cached values at all (fresh openpyxl output), the scan is meaningless. Use --fail-empty or recalc first.
• "Leading zeros in my phone-number column vanished; it's fine." → WRONG. It is almost never fine. Excel and pandas both coerce "007" to 7 by default. csv2xlsx.py detects leading-zero columns and keeps them as text; inline code should either pass dtype=str to pandas or set cell.number_format = "@" in openpyxl.
• "I'll just regex-replace \| to extract markdown tables." → WRONG. GFM pipe parsing has 5+ edge cases (escaped pipes \|, optional trailing pipes, separator-row alignment markers :---: / ---: / :---, column-count validation, blockquote skip, fenced-code-block skip) that regex hand-rolls always fail. Use md_tables2xlsx.py. For HTML ` blocks: same tool — lxml.html is locked with nonetwork=True, hugetree=False`.

2. Capabilities

• Convert a CSV / TSV to a styled .xlsx with bold header, freeze-first-row, auto-filter, auto column widths, and leading-zero preservation.
• Convert an .xlsx workbook back to CSV (per-sheet / per-region) or JSON (flat / dict-of-arrays / nested tables) (xlsx2csv.py, xlsx2json.py). Thin shims on top of the xlsx_read/ foundation library (xlsx-10.A). Supports --sheet NAME|all, --include-hidden, --header-rows N|auto, --tables whole|listobjects|gap|auto, --merge-policy anchor-only|fill|blank, --include-hyperlinks (JSON: dict-form {"value", "href"}; CSV: markdown-link-as-text [text](url)), --include-formulas, --datetime-format ISO|excel-serial|raw. JSON shapes: single-sheet single-region flat array; multi-sheet dict-of-arrays; multi-sheet multi-region nested {Sheet: {tables: {Name: [...]}}}; single-sheet multi-region flat {Name: [...]}. CSV multi-region requires --output-dir → subdirectory schema //.csv (sheet names with __ are NOT split — L4 lock). Round-trip with json2xlsx.py (xlsx-2) for Shape 1 / Shape 2 is byte-identical (live in tests/test_json2xlsx.py::TestRoundTripXlsx8::test_live_roundtrip); Shape 3 / Shape 4 are lossy on xlsx-2 v1 consume (full restoration deferred to xlsx-2 v2 --write-listobjects). Cross-cutting parity: cross-3 / cross-4 / cross-5 / cross-7 envelopes; path-traversal guard on --output-dir. Public helpers convert_xlsx_to_csv(input, output=None, **kwargs) and convert_xlsx_to_json(input, output=None, **kwargs) in xlsx2csv2json/__init__.py.
• Convert a JSON / JSONL document (file or stdin -) to a styled .xlsx with the SAME visual contract as csv2xlsx (json2xlsx.py). Three input shapes auto-detected: array-of-objects (single sheet), dict-of-arrays-of-objects (multi-sheet), JSONL (one JSON object per line — .jsonl extension). Preserves native JSON types (int / float / bool / null / str); ISO-8601 date strings auto-coerced to Excel datetime cells; --strict-dates makes timezone-aware datetimes a hard fail. Cross-cutting parity: cross-5 --json-errors envelope, cross-7 H1 same-path guard (exit 6 SelfOverwriteRefused), stdin pipe -. Round-trip contract with xlsx2json.py (xlsx-8) is frozen at [skills/xlsx/references/json-shapes.md](references/json-shapes.md) and is LIVE as of xlsx-8 merge.
• Convert an .xlsx workbook back to Markdown (GFM pipe tables, HTML ` blocks, or per-table hybrid auto-select) (xlsx2md.py). Thin shim on top of the xlsxread/ foundation (xlsx-10.A) — symmetric pair to mdtables2xlsx.py closing the xlsx → md → edits → xlsx round-trip. Supports --sheet NAME|all, --include-hidden, --format gfm|html|hybrid (default hybrid), --header-rows N|auto|smart, --memory-mode auto|streaming|full, --hyperlink-scheme-allowlist http,https,mailto (Sec-MED-2 default-enabled), --no-table-autodetect, --no-split, --gap-rows N --gap-cols N, --gfm-merge-policy fail|duplicate|blank, --datetime-format ISO|excel-serial|raw, --include-formulas (HTML only — M7 lock: GFM + formulas → exit 2). Hybrid promotion rules (per-table, first match wins): body merges → HTML; multi-row header → HTML; --include-formulas + formula cell → HTML; headerRowCount=0 (synthetic headers) → HTML (D13). Otherwise GFM. Multi-row reconstruction from xlsx_read's flat › -joined headers (D-A11). Hyperlinks via Path C′ (parallel pass through openpyxl) preserve display text — emit as [text](url) GFM or text HTML; blocked schemes emit text-only + warning. Round-trip contract frozen at [references/xlsx-md-shapes.md](references/xlsx-md-shapes.md); LIVE round-trip via TestRoundTripXlsx9::testliveroundtripxlsxmd (cell-content byte-identical for non-merged plain tables; sheet-name asymmetry History → History is xlsx-3's documented sanitisation, NOT a regression). Cross-cutting parity: cross-3/4/5/7 envelopes; terminal InternalError redaction (R23f). Public helper convertxlsxtomd(input, output=None, **kwargs) in xlsx2md/__init__.py`.
• Extract markdown tables from a .md document (file or stdin -) into a styled multi-sheet .xlsx (md_tables2xlsx.py). Two table flavors auto-detected: GFM pipe tables (with per-column alignment carried to Excel cell alignment) and HTML ` blocks (with colspan / rowspan honoured as Excel merged cells). Sheet names derive from the nearest preceding markdown or HTML heading; fallback Table-N; UTF-16-aware truncate to Excel's 31-char limit; case-insensitive workbook-wide dedup via -2..-99 suffix. Default-on numeric + ISO-date coercion (csv2xlsx + json2xlsx parity; leading-zero preservation; aware datetimes → UTC-naive). Cross-cutting parity: --json-errors cross-5 envelope, cross-7 H1 same-path guard (exit 6), stdin pipe -. Pre-scan strips fenced code blocks, HTML comments, indented code blocks, / blocks, and blockquoted tables — so tables inside those regions never reach the parser. HTML parser is lxml.html.HTMLParser(nonetwork=True, hugetree=False, recover=True) singleton (defense-in-depth against XXE + libxml2 huge-tree expansion). Public helper convertmdtablestoxlsx(inputpath, outputpath, **kwargs) -> int lives in mdtables2xlsx/_init__.py (mirrors xlsx-2 convertjsonto_xlsx`; VDD-multi atomic-token protection inherited).
• Force formula recalculation in an .xlsx via headless LibreOffice, then optionally scan for error cells.
• Scan an .xlsx for formula errors (#REF!, #DIV/0!, #VALUE!, #NAME?, #N/A, #NUM!, #NULL!) without recomputing.
• Add a bar / line / pie chart on a value range with optional categories, title, anchor; stays editable in Excel / LibreOffice.
• Insert an Excel comment (legacy `, optionally with the threaded-comment + personList Excel-365 modern layer) into a target cell, with cross-sheet --cell syntax and a batch mode that auto-detects the xlsx-7 findings envelope. Closes the "validation-агент расставляет замечания" pipeline together with xlsxcheckrules.py` (xlsx-7).
• Declarative business-rule validation — xlsx_check_rules.py reads a YAML/JSON rules file alongside an .xlsx and emits a machine-readable findings envelope ({ok, summary, findings}) on stdout; pipes directly into xlsx_add_comment.py --batch - for cell-comment placement. Optional --output writes a workbook copy with a Remarks column + per-severity PatternFill. Hardened DSL (closed AST, no eval/exec), ReDoS-lint reject-list, billion-laughs YAML alias rejection, and a 100K-row × 10-rule perf contract (≤ 30 s wall-clock).
• Unpack and repack .xlsx archives for raw OOXML editing (shared office/ module with the docx skill).
• Structurally validate an .xlsx (relationships, content types, required parts, package-layout allow-list — every ZIP entry must live under [Content_Types].xml, _rels/, xl/, docProps/, or customXml/ per ECMA-376 §11.3.10; catches scratch-file leaks that Excel refuses to open. With --strict, the package-layout warning is promoted to exit 1).
• Reject password-protected and legacy .xls (CFB-container) inputs early in the reader scripts (xlsx_recalc.py, xlsx_validate.py, xlsx_add_chart.py, office/validate.py, office/unpack.py, preview.py) with a clear remediation message (exit 3) instead of a BadZipFile traceback. csv2xlsx.py and office_passwd.py are not gated — the former takes CSV/TSV input (no encryption to detect), the latter is the encryption tool itself.
• Detect macro-enabled inputs (.xlsm, with xl/vbaProject.bin) and warn when the chosen output extension would silently drop the macros (xlsm → xlsx).
• Render any .xlsx/.xlsm/.pdf (or peer-skill .docx/.pptx) into a single PNG-grid preview via preview.py (LibreOffice + Poppler).
• Emit failures as machine-readable JSON to stderr with --json-errors (uniform across all four office skills).
• Set or remove a password on a .xlsx/.docx/.pptx (MS-OFB Agile, Office 2010+) via office_passwd.py — three modes: --encrypt PASSWORD, --decrypt PASSWORD, --check (exit 0 encrypted / 10 clean / 11 missing).

3. Execution Mode

• Mode: script-first.
• Why this mode: Spreadsheet tasks have well-defined inputs and outputs and benefit heavily from deterministic CLIs. Writing the styling code inline produces ugly workbooks every time; delegating to scripts frees the agent to focus on the user's intent.

4. Script Contract

• Commands:
• python3 scripts/csv2xlsx.py INPUT.csv OUTPUT.xlsx [--delimiter auto|,|;|\t] [--encoding utf-8] [--no-freeze] [--no-filter]
• python3 scripts/xlsx2csv.py INPUT.xlsx [OUTPUT.csv|-] [--output-dir DIR] [--sheet NAME|all] [--include-hidden] [--header-rows N|auto] [--merge-policy anchor-only|fill|blank] [--tables whole|listobjects|gap|auto] [--gap-rows N] [--gap-cols N] [--include-hyperlinks] [--include-formulas] [--datetime-format ISO|excel-serial|raw] [--memory-mode auto|streaming|full] [--encoding utf-8|utf-8-sig] [--delimiter ,|;|tab|\\t|pipe] [--drop-empty-rows] [--json-errors] — convert .xlsx → CSV. Multi-region requires --output-dir. Output - or omitted → stdout. --drop-empty-rows filters out rows where every value is None/"" (default off; same semantics as the JSON path). For full Excel-RU/EU double-click compatibility pass BOTH --encoding utf-8-sig AND --delimiter ';': the BOM lets Excel detect UTF-8 encoding, and ; is the field separator on RU/EU locales (where , is the decimal separator). Defaults (utf-8 + ,) stay pandas-/jq-friendly. Stdout always plain UTF-8 regardless of flags. The tab alias (or 2-char \\t escape) maps to a real tab; pipe maps to |. Python-helper equivalent: convert_xlsx_to_csv(input, output, delimiter=';', encoding='utf-8-sig') (kwarg names map to CLI flags 1:1).
• python3 scripts/xlsx2json.py INPUT.xlsx [OUTPUT.json|-] [--sheet NAME|all] [--include-hidden] [--header-rows N|auto|leaf|smart] [--header-flatten-style string|array] [--merge-policy anchor-only|fill|blank] [--tables whole|listobjects|gap|auto] [--gap-rows N] [--gap-cols N] [--include-hyperlinks] [--include-formulas] [--datetime-format ISO|excel-serial|raw] [--memory-mode auto|streaming|full] [--drop-empty-rows] [--json-errors] — convert .xlsx → JSON. Output - or omitted → stdout. --drop-empty-rows filters rows where every value is None/"". --header-rows leaf auto-detects the header band but keeps ONLY the deepest non-empty level per column as the JSON key — solves the layout-heavy-report key bloat where rows 1..K-1 are merged metadata banners (title / customer / contract / project / period) and the real column names sit on row K. Example recipe: xlsx2json.py FILE.xlsx out.json --sheet all --tables auto --header-rows leaf --drop-empty-rows → keys become ["Date", "Hours", "Days", "Specialist", "Position", "Task #", "Description"] instead of 7-level › -concatenated metadata-prefixed strings. --header-rows smart (xlsx-8a-09 / R11; iter-3 2026-05-13) is the "find-the-data-table" recipe — locates the real header row whether or not merges are present. Uses a type-pattern heuristic: scores each top row by string-ratio + column-coverage + downstream type-stability + depth (max 5.0); when a candidate scores ≥ 3.5 AND len(sample_below) ≥ 2, the library shifts the region past the metadata and treats the candidate row as a 1-row header. smart does NOT defer to merge-based detection — it competes purely on score. On merged-banner fixtures (e.g. A1:C1 "2026 plan" over Q1/Q2/Q3), smart shifts to the sub-header row and produces leaf-like keys ["Q1", "Q2", "Q3"]. Callers needing the multi-level concatenated form ("2026 plan › Q1") must use --header-rows auto (or leaf to take the deepest level only) — smart is non-overlapping with auto/leaf at the OUTPUT shape level. Use smart when auto produces {"": ..., "От": ..., "До": ..., "__2": ..., ..., "__N": ...}-style keys on a workbook whose real headers are buried below a config section (typical: financial-modeling sheets with От/До parameter ranges on rows 1-K + a real data table starting at row K+1; or per-row banner + per-row metadata header rows above the column-name row, e.g. masterdata Timesheet pattern). --memory-mode {auto,streaming,full} (xlsx-8a-11 / R13) exposes the read-mode selection: auto (default) lets the library pick based on file size (_DEFAULT_READ_ONLY_THRESHOLD = 100 MiB); streaming minimises RAM — measured 7.6× RAM reduction PLUS 2.6× wall-clock speedup on 15 MB multi-sheet workbooks (1188 MB / 32 s → 156 MB / 13 s, live subprocess measurement 2026-05-13) at the cost of merge-aware features (overlap detection, multi-row header band via merges, merge-policy fill) becoming no-ops — use on workbooks without merges OR where merge fidelity does not matter; full forces non-streaming so all merge features work but RAM scales ~10× with file size. --include-hyperlinks is incompatible with streaming (hyperlink extraction needs the non-streaming cell object); the combination emits a stderr warning and auto-overrides to full. Practical recipe for large multi-sheet workbooks without critical merges: xlsx2json.py BIG.xlsx out.json --sheet all --tables whole --header-rows smart --memory-mode streaming --drop-empty-rows.
• python3 scripts/md_tables2xlsx.py INPUT.md OUTPUT.xlsx [--no-coerce] [--no-freeze] [--no-filter] [--allow-empty] [--sheet-prefix STR] [--encoding utf-8] [--json-errors] — markdown tables → multi-sheet xlsx. INPUT is a path or - for stdin.
• `python3 scripts/xlsx2md.py INPUT.xlsx [OUTPUT.md|-] [--sheet NAME|

…

Source & license

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

• Author: MatrixFounder
• Source: MatrixFounder/Universal-skills
• License: Apache-2.0

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