Document Xlsx

Document XLSX Skill - Quick Reference

This skill enables creation, editing, inspection, and safe distribution of .xlsx workbooks. Use it for report exports, spreadsheet models, spreadsheet QA, workbook automation, and Excel-compatible deliverables.

Modern best practices (July 2026):

• Prefer Excel Tables over loose ranges.
• Separate inputs, calculations, and outputs.
• Treat spreadsheets as software: checks, owners, change control, and review loops.
• Treat untrusted workbooks as hostile: formulas, hyperlinks, external links, hidden content, and macros all need review.
• If workbooks are shared externally, include accessibility hygiene and run Excel's Accessibility Checker.

Core Decision Rules (2026)

• First decide the runtime: local file generation, cloud workbook automation, or workbook audit/sanitization.
• Default to table-first exports: headers in row 1, frozen header row, autofilter, named table, bounded ranges.
• For native pivots: use Office Scripts or Excel automation; for headless exports prefer pre-computed summary tables.
• Libraries usually write formulas, but Excel calculates them when the file opens. If server-side computed values are required, calculate them in code and write values.
• XlsxWriter is write-only: it cannot open, read, or edit an existing .xlsx file. If the task is "edit this workbook" rather than "create a new one," reach for openpyxl (or ExcelJS in Node) instead — choosing XlsxWriter for an edit task is a common non-expert mistake that fails immediately.
• A formula written by openpyxl or XlsxWriter has no cached result until some calculation engine (Excel, LibreOffice headless, or a session-based tool such as xlwings) opens and recalculates the file. Reading that same file back with openpyxl(..., data_only=True) before any recalculation returns None, not the computed value — this looks like a bug but is expected behavior. If a downstream step (pandas, another script, an LLM) needs the number immediately, compute it in Python and write the literal value, or write both the formula and a plausible cached value only if you can guarantee it matches.
• ExcelJS is strong for workbook structure and styling, but it does not provide native chart generation; ExcelJS pivot-table support shipped as an experimental, limited feature only in recent 4.x releases — treat it as unstable and verify round-trip fidelity before relying on it in production.
• openpyxl can preserve VBA with keep_vba=True, but this skill does not author or execute macros.
• If ingesting untrusted workbooks with openpyxl, default to keep_links=False unless external links must be preserved.
• For very large exports (hundreds of thousands of rows or more), default openpyxl usage can balloon memory (a ~150MB source DataFrame has been observed using 2GB+ RAM with the default XML parser). Install lxml and use Workbook(write_only=True) for writing or load_workbook(read_only=True) for reading — both stream rather than build a full in-memory tree, and lxml alone materially cuts memory even outside those modes. Write-only workbooks can be saved exactly once; a second save() call raises WorkbookAlreadySaved, so batch all writes before saving.
• Row/column ceilings are fixed by the file format, not the library: 1,048,576 rows and 16,384 columns per worksheet. Exports approaching this need a pagination or multi-sheet strategy decided up front, not discovered at write time.

Quick Reference

When To Use This Skill

Invoke this skill when a user requests:

• Generate .xlsx reports, dashboards, models, or exports
• Add formulas, validation, tables, conditional formatting, or protection
• Audit an existing workbook for formulas, links, hidden sheets, or risky content
• Prepare a workbook for distribution, accessibility review, or safer ingestion
• Automate Excel features that depend on Microsoft 365 or desktop Excel

Default Workflow

• Create: pick local generation (XlsxWriter, openpyxl, ExcelJS) or cloud automation (Office Scripts, Graph, xlwings), then start from a table-first layout.
• Review: run python3 scripts/xlsx_audit.py workbook.xlsx --format md and compare the results against assets/spreadsheet-model-review-checklist.md.
• Ship: sanitize exported text, review external links, run Accessibility Checker, and verify behavior in Excel plus the target secondary viewer if interoperability matters.

ASCII Flow

XLSX request
  |
  v
Classify workbook task
  |-- new export / report
  |-- edit existing workbook
  |-- audit / sanitize
  |-- cloud or desktop automation
  |
  v
Choose runtime
  |-- Python data pipeline -----> pandas / Polars + XlsxWriter
  |-- Python workbook edits ----> openpyxl
  |-- Node / TS service --------> ExcelJS
  |-- M365 live workbook -------> Office Scripts or Graph Excel
  |-- desktop Excel ------------> xlwings
  |
  v
Apply table-first structure
  |-- inputs
  |-- calculations
  |-- outputs
  |-- instructions / summary
  |
  v
Review formulas, links, hidden content, and accessibility
  |
  v
Sanitize and verify in target viewers

Known Limits And Caveats

• Native pivots remain runtime-specific. openpyxl and XlsxWriter still do not create native pivot tables.
• Google Sheets and LibreOffice do not perfectly preserve all Excel features. Validate if you rely on pivots, formulas, protection, or advanced formatting.
• Data validation is UI metadata, not a full security boundary. Users can paste around it unless protection and process controls are in place.
• Workbook and sheet protection passwords are deterrents, not encryption. Use file-level encryption or platform controls for sensitive data.
• External links and hyperlinks can be both a security and reproducibility problem. Strip or document them before distribution.
• Dynamic-array and modern lookup formulas (XLOOKUP, FILTER, UNIQUE, SORT, IFS, SEQUENCE) require Microsoft 365 / current Excel. Writing them into a workbook targeted at Excel 2019/2016, Google Sheets (partial support), or older LibreOffice will show #NAME? for recipients on those versions — confirm the audience's Excel channel before defaulting to these over VLOOKUP/INDEX-MATCH/nested IF.