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.
• pandas.read_excel() picks its engine by file extension (openpyxl for .xlsx), not by what wrote the file. It never surfaces conditional formatting, data validation, protection, or charts — if the audit needs those, read the OOXML parts directly (see scripts/xlsx_audit.py) or use openpyxl directly instead of pandas.

Decision Tree

Excel Task: [What do you need?]
    ├─ New workbook export?
    │   ├─ Python data/report pipeline → pandas/Polars + XlsxWriter
    │   ├─ Edit-heavy workbook logic → openpyxl
    │   └─ Node/TypeScript service → ExcelJS
    │
    ├─ Existing workbook review?
    │   ├─ Read-only audit → scripts/xlsx_audit.py
    │   ├─ Data extraction → pandas or SheetJS
    │   └─ Structural edits → openpyxl
    │
    ├─ Native Excel features on a live workbook?
    │   ├─ Web / M365 workbook → Office Scripts or Graph Excel
    │   └─ Desktop Excel installed → xlwings
    │
    └─ Safe distribution?
        ├─ Sanitize text / strip links → scripts/xlsx_sanitize.py
        ├─ Accessibility review → Excel checker + accessibility reference
        └─ Sensitive data → encryption + platform access controls

Core Operations

Table-First Export (Python - XlsxWriter)

import pandas as pd

df = pd.DataFrame(
    [
        {"product": "Widget A", "qty": 100, "price": 10.0},
        {"product": "Widget B", "qty": 50, "price": 25.0},
    ]
)
df["total"] = df["qty"] * df["price"]

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Sales", index=False, startrow=1)

    workbook = writer.book
    worksheet = writer.sheets["Sales"]
    header_fmt = workbook.add_format({"bold": True, "bg_color": "#D9E2F3"})
    money_fmt = workbook.add_format({"num_format": "$#,##0.00"})

    worksheet.write("A1", "Sales report")
    worksheet.freeze_panes(2, 0)
    worksheet.autofilter(1, 0, len(df), len(df.columns) - 1)
    worksheet.set_column("C:D", 14, money_fmt)
    worksheet.add_table(
        1,
        0,
        len(df) + 1,
        len(df.columns) - 1,
        {
            "name": "SalesTable",
            "style": "Table Style Medium 2",
            "columns": [{"header": col, "header_format": header_fmt} for col in df.columns],
            "total_row": True,
        },
    )

Edit Existing Workbook Safely (Python - openpyxl)

from openpyxl import load_workbook

wb = load_workbook("input.xlsx", keep_vba=False, keep_links=False)
ws = wb["Sales"]

ws["A1"] = "Sales report for Q1 2026"
ws.freeze_panes = "A2"
ws.sheet_view.showGridLines = True

wb.save("output.xlsx")

Native Pivot Creation (Office Scripts)

function main(workbook: ExcelScript.Workbook) {
  const dataSheet = workbook.getWorksheet("Raw Data");
  const sourceRange = dataSheet.getUsedRange();
  const sourceTable = dataSheet.addTable(sourceRange, true);
  sourceTable.setName("SalesTable");

  const pivotSheet = workbook.addWorksheet("Pivot");
  const pivot = workbook.addPivotTable("SalesPivot", sourceTable, pivotSheet.getRange("A1"));
  pivot.addRowHierarchy(pivot.getHierarchy("Region"));
  pivot.addColumnHierarchy(pivot.getHierarchy("Product"));
  pivot.addDataHierarchy(pivot.getHierarchy("Revenue"));
}

Do / Avoid (July 2026)

Do

• Default to named tables, bounded ranges, and frozen headers.
• Keep assumptions explicit with value, unit, source, and date.
• Add control totals, duplicate checks, and fail-loud QA cells.
• Use descriptive sheet names and place workbook context in A1.
• Audit hidden sheets, external links, formulas, and named items before sharing.

Avoid

• Raw cell-block exports when a table would work.
• Hardcoded constants buried in formulas.
• Blank worksheets, merged header cells, or color-only meaning in delivered reports.
• Preserving external links by default on untrusted ingest.
• Sharing workbooks with PII or secrets without explicit approval and controls.

What Good Looks Like

• Structure: clear Inputs, Calculations, Outputs, and Instructions or Summary tabs as needed.
• Data model: named tables or ranges, no silent range drift, and no unexplained hidden sheets.
• Integrity: no #REF!, broken names, stale links, or silent formula inconsistencies.
• Accessibility: descriptive tabs, meaningful hyperlinks, proper table headers, alt text where applicable, and a clean Accessibility Checker run.
• Release hygiene: owner named, review loop completed, and workbook sanitized or justified before distribution.

Optional: AI / Automation

Use only when explicitly requested and policy-compliant.

• Generate first-pass formulas, charts, or summary tabs; humans verify results and edge cases.
• Produce a workbook audit summary from scripts/xlsx_audit.py; humans review the findings.
• Draft assumptions and glossary tabs from known source data; do not invent metrics or provenance.

Navigation

Resources

• references/excel-tables-structured-references.md - Excel Tables, totals rows, and structured formulas
• references/excel-cloud-automation.md - Office Scripts, Microsoft Graph Excel, xlwings
• references/excel-accessibility-compliance.md - Accessibility, Section 508, EN 301 549 considerations
• references/excel-formulas.md - Formula reference and patterns
• references/excel-formatting.md - Styling and conditional formatting
• references/excel-charts.md - Chart types and customization
• references/excel-data-validation.md - Dropdowns, input constraints, cascading validation
• references/excel-pivot-tables.md - Pivot workarounds and runtime-specific options
• references/excel-security-protection.md - Protection, links, injection prevention
• data/sources.json - Current vendor and standards links

Scripts

• python3 scripts/xlsx_audit.py workbook.xlsx --format md
• python3 scripts/xlsx_export_report.py input.csv output.xlsx
• python3 scripts/xlsx_sanitize.py input.xlsx output.xlsx --strip-external-links

Templates

• assets/financial-report.md - Financial statement template
• assets/data-dashboard.md - Dashboard with charts and KPIs
• assets/spreadsheet-model-review-checklist.md - Workbook QA checklist

Related Skills

• ../document-pdf/SKILL.md - PDF generation from spreadsheet data
• ../ai-ml-data-science/SKILL.md - Data analysis and dataframe workflows
• ../data-sql-optimization/SKILL.md - Database-to-workbook pipelines

Fact-Checking

• Use web search/web fetch to verify current external facts, versions, deadlines, regulations, or platform behavior before final answers.
• Prefer primary sources and stable vendor docs over blog posts.
• If a Microsoft Learn landing page is session-dependent, prefer a retrievable API/reference page for the source list.

Learnings Loop

Before applying this skill on a non-trivial task, read learnings.consolidated.md in this directory (and learnings.md if present).

After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to learnings.md via agents-skills-feedback-loop/scripts/append_learning.py. Do not modify SKILL.md itself.