xlsx-official

name	xlsx-official
description	Spreadsheet toolkit. Reach for it whenever the artifact on either side of the conversation is a workbook file — .xlsx, .xlsm, .xltx, .csv, .tsv — and the user wants that artifact produced, changed, cleaned, or read. Typical triggers: 'build me a model', 'update this sheet', 'add a column', 'compute the totals as formulas', 'sanity-check this xlsx', 'export sheet 2 to CSV', 'render the workbook as PDF', 'the spreadsheet in ~/Downloads is a mess, fix it'. Applies equally to financial models, ops reports, data cleanups, and template fills. Skip it when the workbook is only source material and the real output is a Word doc, an HTML page, a Python script that runs standalone, a Google Sheets integration, or an ingestion pipeline into a database — in those cases the spreadsheet is a means, not the deliverable.
license	Apache-2.0 — see LICENSE for terms and third-party attributions

XLSX Skill

An Apache-2.0 toolkit for producing, editing, and reading Microsoft Excel (.xlsx) files. Written from scratch against the public ECMA-376 / ISO/IEC 29500 (SpreadsheetML) specification and built on permissively-licensed tooling (openpyxl MIT, pandas BSD-3-Clause, lxml BSD-3-Clause, optional xlsxwriter BSD-2-Clause, optional external binary soffice MPL 2.0) so it can be reused in commercial projects without restriction.

Decision matrix

Situation	Path	Read first
Build a workbook from a prompt / dataframe / raw values	Author with openpyxl (formulas + formatting) or pandas (bulk data)	create.md
Edit an existing .xlsx — add rows, patch cells, refresh formulas	Load with openpyxl, preserve formulas & styles	edit.md
Only need to read the data out (analysis, ETL, quick QA)	pandas.read_excel + openpyxl for structural inspection	read.md
Clean, aggregate, or transform tabular data before writing back	pandas pipeline, then hand back to openpyxl for final polish	analyze.md
Deep structural edits (custom XML parts, defined names, VBA-free surgery)	Unpack → edit XML → repack	edit.md → Raw XML workflow
Recompute formula values before shipping	scripts/bake.py via LibreOffice	see QA below

If the task mixes several of these, do them in this order: read → plan → edit/create → recalc → validate.

One-time environment setup

python3 -m pip install --upgrade openpyxl pandas lxml
# Optional but recommended:
python3 -m pip install --upgrade xlsxwriter
# For formula recalc and PDF export:
#   macOS       brew install --cask libreoffice
#   Debian/Ubuntu apt-get install -y libreoffice

Every script under scripts/ uses only the standard library plus openpyxl and pandas. No proprietary dependencies.

Common commands

# 1. Describe a workbook (sheets, dimensions, formula count, sample rows)
python scripts/overview.py input.xlsx

# 2. Recalculate every formula, then flag any residual #REF! / #DIV/0! / etc.
python scripts/bake.py output.xlsx               # default 30s LibreOffice timeout
python scripts/bake.py output.xlsx --timeout 60  # custom timeout

# 3. Validate ZIP integrity, XML well-formedness, and openpyxl load
python scripts/audit.py output.xlsx

# 4. Convert to CSV (one file per sheet, or a single sheet by name/index)
python scripts/csv_out.py input.xlsx out_dir/           # all sheets
python scripts/csv_out.py input.xlsx out.csv --sheet 0  # first sheet

# 5. Convert to PDF for visual QA (needs LibreOffice)
python scripts/pdf_out.py output.xlsx           # writes output.pdf next to it

# 6. Unpack an .xlsx into readable XML parts (for surgical edits)
python scripts/explode.py input.xlsx unpacked/

# 7. Repack an unpacked directory into a fresh .xlsx
python scripts/assemble.py unpacked/ output.xlsx

Every script is a small, self-contained Python file. Read the top of the file for full CLI options.

Authoring principles

Excel is a live calculation surface, not a static table renderer. Users open workbooks and expect to change numbers, watch the rest update, and trust what they see. Keep that in mind:

1. Use formulas, not hardcoded values. Compute totals with =SUM(...), not with a Python sum() written into the cell. When source data changes, the workbook must recompute itself.
2. Put assumptions in dedicated input cells. Reference them from formulas (=B5*(1+$B$6)), never inline (=B5*1.05). This is the single biggest determinant of whether a model is usable.
3. One sheet per idea. Inputs on one sheet, calculations on another, output on a third. Cross-sheet references (Inputs!B5) make dependencies explicit.
4. Named styles beat ad-hoc formatting. For anything reused (headers, totals, inputs, error markers), register a NamedStyle once and reapply.
5. Freeze headers. sheet.freeze_panes = "A2" (or "B2" if the first column is a row label) — every scrolling table needs this.
6. Format numbers per column, in one pass. Apply cell.number_format in a loop over the data range of each column (for row in ws.iter_rows(min_col=3, max_col=3): ...). Note: column_dimensions['C'].number_format does not reliably format cells you write afterwards — openpyxl-written cells carry their own style.
7. Never rely on openpyxl to evaluate formulas. It stores the string "=SUM(...)" and a cached previous value (if the file was opened before). Freshly written formulas have no cached value until LibreOffice or Excel recomputes.

Number-format cheatsheet

Kind	Format string	Renders
Plain integer with thousands	#,##0	1,234
Currency (USD, hide zeros)	$#,##0;($#,##0);"-"	$1,234 / ($1,234) / -
Currency (2 dp)	$#,##0.00	$1,234.56
Percentage (1 dp)	0.0%	12.3%
Multiplier	0.00"x"	1.35x
Year as text	0	2026 (no comma)
Short date	yyyy-mm-dd	2026-07-04
Long date	dddd, mmmm d, yyyy	Saturday, July 4, 2026
Scientific	0.00E+00	1.23E+04

Use parentheses for negatives in financial contexts; use a leading minus for scientific or engineering contexts.

Color and style conventions

There is no universal standard, but if the user does not specify one, this palette is safe for internal financial or operational models:

Purpose	Value	Rationale
Header text	#1F1F1F on #F2F2F2 fill	High contrast, print-safe
Input (user changes)	Blue #0033CC	Visually distinct from formulas
Formula (calculated)	Black #1F1F1F	Default reading color
Same-workbook link	Green #116611	"Comes from elsewhere in this file"
Cross-file link	Red #B22222	"Fragile — points outside this file"
Assumption to review	#FFF2CC fill	Yellow highlight, still readable in b/w
Error / warning	#FFC7CE fill, #9C0006 text	Excel's built-in "bad" style

Override these whenever the file has an existing template — match it exactly.

QA checklist — always run before declaring done

Assume something is wrong. Excel opens broken files quietly: a stray #REF!, an off-by-one range, a formula that quietly evaluates to 0. Verify explicitly.

1. Recalculate formulas. openpyxl does not evaluate them — LibreOffice does.

   python scripts/bake.py output.xlsx
   

   Read the JSON output. status: "clean" with error_count: 0 is the only acceptable result.

2. Structural validation.

   python scripts/audit.py output.xlsx
   

   Confirms the ZIP is well-formed, all XML parts parse, and openpyxl can round-trip the file.

3. Spot-check the values. Load with data_only=True after recalculation and read the cells you expect to be non-zero:

   from openpyxl import load_workbook
   wb = load_workbook('output.xlsx', data_only=True)
   assert wb['Summary']['B10'].value == expected_total
   

4. Visual sanity. Render a PDF and scan the first and last sheets for:

   • Columns clipped because widths were left at default.
   • Numbers displayed as ######## (column too narrow for the format).
   • Formulas showing as text (missing leading =, or a leading apostrophe).
   • Headers repeated per page, print area set for large sheets.

   python scripts/pdf_out.py output.xlsx
   

If any of these fail, fix and re-run. Do not paper over.

Common formula pitfalls

• #DIV/0! — wrap divisions defensively: =IF(B2=0,0,A2/B2) or =IFERROR(A2/B2, 0). Prefer IF so real zeros stay visible; use IFERROR only for values that must always be numeric.
• #REF! — a cell reference points to a deleted row/column. Rebuild the formula against current coordinates; do not just delete the offending cell.
• #VALUE! — text where a number is expected, usually from a stray label in a data column. Check the column dtype in pandas before writing.
• #NAME? — the formula uses a function name Excel does not recognize. Common causes: typos (=SUMM(...)), locale-specific separators (; vs ,), or dynamic-array functions like FILTER in older Excel versions.
• #N/A — usually from VLOOKUP / XLOOKUP / MATCH failing to find a key. Wrap in IFNA(..., default) when a miss is expected.
• Cross-sheet reference typos. Sheet1!A1 works; Sheet 1!A1 needs quoting: 'Sheet 1'!A1. openpyxl accepts either — Excel demands the quoting.

What is out of scope

• .xls (Excel 97-2003 binary). Convert to .xlsx first: soffice --headless --convert-to xlsx old.xls.
• VBA / macros / .xlsm. This skill does not emit or execute macros.
• Password-protected or encrypted workbooks. openpyxl cannot read encrypted files; strip protection through Excel/LibreOffice first.
• Live Excel automation. For COM (Windows) or AppleScript (macOS) integration, use a dedicated automation library — this toolkit is file-in / file-out.

Where each detail lives

• Creating from scratch: create.md — workbooks, sheets, formulas, formatting, named styles, charts, images, freeze panes, print setup.
• Editing / templating: edit.md — patching cells, appending rows, inserting columns, preserving formulas & styles, unpack/repack for deep XML surgery, defined names, data validation.
• Reading / extracting: read.md — pandas reads, structural inspection, formula extraction, conversion to CSV / TSV / PDF.
• Data analysis: analyze.md — pandas pipelines, reshaping, groupby, joins, then handing back to openpyxl for the final writeable artifact.
• Scripts: scripts/ — CLI utilities used throughout.