Excel XLSX Spreadsheets skill for Codex

Done well means: numbers that depend on other numbers are formulas, inputs are separated from calculations, every column has an explicit number format, the workbook opens with no repair prompt and no #REF!/#NAME?, and the file was audited after it was written.

Choose the library

Task	Default	Notes
Read values from a workbook	openpyxl.load_workbook(p, data_only=True, read_only=True)	Returns cached values; None where never calculated
Edit an existing workbook	openpyxl	Loses charts, images in some cases, shapes and slicers it cannot parse: check first
New formatted report, Python	openpyxl, or XlsxWriter for very large/fast writes and native charts	XlsxWriter cannot read or edit
DataFrame dump with light formatting	pandas.DataFrame.to_excel via pd.ExcelWriter(..., engine="openpyxl")	Format afterwards through writer.sheets[name]
Node / TypeScript	exceljs	Reads and writes; streaming writer for big exports
Pivot tables and charts	Use $excel-charts-pivots
Writing or debugging formulas themselves	Use $excel-formulas

Read AGENTS.md and the manifest first; reuse the library already in the project. Installs need network; ask for approval if the sandbox blocks it.

Workflow

- [ ] 1. Inspect inputs: sheets, used ranges, formulas vs values, macros (.xlsm)
- [ ] 2. Lay out the workbook: Inputs / Data / Calc / Summary sheets
- [ ] 3. Write data with types (numbers as numbers, dates as datetime)
- [ ] 4. Write formulas that reference cells, never hard-coded results
- [ ] 5. Apply formats, widths, freeze panes, tables, validation
- [ ] 6. Save to a new file; recalculate if values must be readable by code
- [ ] 7. Audit: python3 scripts/xlsx_audit.py out.xlsx

Inspect quickly:

from openpyxl import load_workbook

wb = load_workbook("in.xlsx")                       # formulas as strings
for ws in wb.worksheets:
    formulas = sum(1 for row in ws.iter_rows() for c in row if c.data_type == "f")
    print(ws.title, ws.dimensions, "formulas:", formulas, "merged:", len(ws.merged_cells.ranges))
print("defined names:", list(wb.defined_names.keys()))

Build a report with openpyxl

from datetime import date
from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
inputs = wb.active
inputs.title = "Inputs"
inputs["A1"], inputs["B1"] = "Tax rate", 0.2
inputs["B1"].number_format = "0.0%"
inputs["B1"].fill = PatternFill("solid", fgColor="FFF2CC")       # input cells look different

ws = wb.create_sheet("Sales")
headers = ["Date", "Region", "Units", "Unit price", "Net", "Tax", "Gross"]
ws.append(headers)
rows = [(date(2025, 9, 1), "North", 120, 9.5), (date(2025, 9, 1), "South", 80, 10.25)]
for r, (d, region, units, price) in enumerate(rows, start=2):
    ws.append([d, region, units, price,
               f"=C{r}*D{r}",
               f"=E{r}*Inputs!$B$1",
               f"=E{r}+F{r}"])
last = ws.max_row

formats = {"A": "yyyy-mm-dd", "C": "#,##0", "D": "#,##0.00", "E": "#,##0.00",
           "F": "#,##0.00", "G": "#,##0.00"}
for col, fmt in formats.items():
    for cell in ws[col][1:]:
        cell.number_format = fmt
for i, h in enumerate(headers, start=1):
    ws.column_dimensions[get_column_letter(i)].width = max(12, len(h) + 4)
    ws.cell(row=1, column=i).font = Font(bold=True)
    ws.cell(row=1, column=i).alignment = Alignment(horizontal="center")

ws.freeze_panes = "A2"
table = Table(displayName="SalesTable", ref=f"A1:G{last}")
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
ws.add_table(table)

dv = DataValidation(type="list", formula1='"North,South,East,West"', allow_blank=False)
ws.add_data_validation(dv)
dv.add(f"B2:B{last + 500}")
ws.conditional_formatting.add(f"G2:G{last}", ColorScaleRule(start_type="min", start_color="F8696B",
                                                           end_type="max", end_color="63BE7B"))
ws.conditional_formatting.add(f"C2:C{last}", CellIsRule(operator="lessThan", formula=["0"],
                                                        font=Font(color="9C0006")))

summary = wb.create_sheet("Summary", 0)
summary["A1"], summary["B1"] = "Total gross", "=SUM(SalesTable[Gross])"
summary["B1"].number_format = "#,##0.00"
wb.save("sales.xlsx")

Big files

• Reading: load_workbook(p, read_only=True, data_only=True) and iterate ws.iter_rows(values_only=True); call wb.close() at the end (read-only keeps the file open).
• Writing millions of cells: Workbook(write_only=True) and ws.append(row), or XlsxWriter with Workbook(path, {"constant_memory": True}) (rows must be written in order).
• Excel limits: 1,048,576 rows and 16,384 columns per sheet, 32,767 characters per cell, sheet names at most 31 characters without []:*?/\. Split across sheets or ship CSV beyond that.

pandas and ExcelJS

import pandas as pd

with pd.ExcelWriter("export.xlsx", engine="openpyxl") as xw:
    df.to_excel(xw, sheet_name="Orders", index=False)
    ws = xw.sheets["Orders"]
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions

import ExcelJS from "exceljs";

const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("Sales", { views: [{ state: "frozen", ySplit: 1 }] });
ws.columns = [
  { header: "Region", key: "region", width: 14 },
  { header: "Units", key: "units", width: 10, style: { numFmt: "#,##0" } },
  { header: "Price", key: "price", width: 12, style: { numFmt: "#,##0.00" } },
  { header: "Net", key: "net", width: 14, style: { numFmt: "#,##0.00" } },
];
rows.forEach((r, i) => {
  const n = i + 2;
  ws.addRow({ ...r, net: { formula: `B${n}*C${n}` } });
});
ws.getRow(1).font = { bold: true };
await wb.xlsx.writeFile("sales.xlsx");

For very large Node exports use new ExcelJS.stream.xlsx.WorkbookWriter({ filename }), call row.commit() per row and await wb.commit() at the end.

Hard rules and gotchas

• Formulas are stored, not calculated. openpyxl and ExcelJS write the formula with no cached result; Excel computes on open, but data_only=True readers, pandas and many previewers see empty cells. If code must read results, recalculate (next section).
• Never load_workbook(..., data_only=True) and then save() over the original: every formula is replaced by its last cached value (or None).
• Formula text uses English function names and comma separators. Functions newer than Excel 2010 need the _xlfn. prefix when written by code (=_xlfn.XLOOKUP(...)), or Excel shows #NAME?. See $excel-formulas for dynamic arrays.
• openpyxl treats any string starting with = as a formula. For untrusted text, set cell.value = text then cell.data_type = "s" so it is stored as a string; never let user data become a formula (=HYPERLINK(...) injection).
• Timezone-aware datetimes raise in openpyxl; convert to naive local or UTC first and say which.
• Store money as numbers with a format, not strings like "$1,200". Use Decimal-rounded values when cents matter, and round in formulas (=ROUND(x,2)) where totals are shown.
• Macros: open .xlsm with load_workbook(p, keep_vba=True) and save with the .xlsm extension, or the macros are dropped.
• Editing a workbook that has charts, pivot caches, images or form controls: openpyxl may drop or degrade them. Test on a copy, compare the parts list (unzip -l) before and after, and warn the user if anything disappeared.

Recalculate and audit

python3 scripts/xlsx_audit.py sales.xlsx                  # errors, injection-risk text, uncached formulas
soffice --headless --calc --convert-to xlsx --outdir recalculated sales.xlsx
python3 scripts/xlsx_audit.py recalculated/sales.xlsx --require-cached

The LibreOffice step (needs LibreOffice installed) rewrites the file with computed values. Re-run the audit with --require-cached; if formulas still lack values, the converter did not recalculate, so verify the numbers in Excel instead of trusting the file. The script exits 1 on #REF!, #DIV/0!, #NAME? and other error values, and on defined names that point at #REF!.

Then check meaning, not just syntax: total one column in Python from the source data and compare it with the workbook’s total cell.

Delivery note

**File:** out/sales.xlsx (sheets: Summary, Inputs, Sales)
**Live formulas:** Net, Tax, Gross per row; Summary!B1 = SUM(SalesTable[Gross])
**Inputs:** Inputs!B1 tax rate (yellow cells are editable)
**Checks:** xlsx_audit clean; recalculated total 2,187.60 matches source data