Excel Spreadsheets (.xlsx) skill for Claude Code

Done well means: numbers are stored as numbers with number formats (not strings), totals and derived values are live formulas a user can audit, the sheet is readable on open (header styling, widths, frozen header, filters), nothing the user did not ask to change was lost, and you verified the workbook has no error values and no uncalculated formulas before claiming results.

Setup and tool choice

pip install openpyxl          # read/write .xlsx (no calculation engine)
npm i exceljs                 # Node alternative
# optional: LibreOffice (soffice) to recalculate and render

Task	Use
Create or edit with formatting, formulas, validation, charts	openpyxl
Analysis of tabular data, then export	pandas (df.to_excel(..., engine="openpyxl")), then style with openpyxl
Very large output (100k+ rows)	openpyxl Workbook(write_only=True) or xlsxwriter (write-only, fast, rich formatting)
Node/TypeScript services	exceljs (streaming writer available)
Read .xls (legacy)	xlrd (xls only) or convert with LibreOffice first
CSV in, messy encodings	Clean first (see csv-cleaning), then write xlsx

Step 1: audit before and after

python3 scripts/xlsx_audit.py model.xlsx
python3 scripts/xlsx_audit.py model.xlsx --json > audit.json

Reports every sheet (visibility, used range, formula count, merged ranges, validations, conditional formats, tables, freeze panes), cells holding error values (#REF!, #DIV/0!, #NAME?, #VALUE!, #N/A, #SPILL!), formulas with no cached value, external workbook references, and defined names. Exit code 1 if any errors or uncalculated formulas exist.

The recalculation caveat

openpyxl and exceljs write formula text; they do not calculate. Consequences:

• A file you just wrote has formulas with no cached values. Excel calculates them on open; but anything reading the file with data_only=True (or pandas) sees None.
• Loading with data_only=True and saving replaces formulas with their last values permanently. Never save a workbook loaded with data_only=True over the original.
• To get computed values without Excel: recalculate with LibreOffice headless (open and re-save via a conversion such as soffice --headless --convert-to xlsx --outdir recalculated/ model.xlsx), then re-run the audit to confirm cached values exist. Alternatively compute the numbers in Python and write both the value-based check and the formula.
• Tell the user explicitly when the delivered file has not been recalculated.

Create a well-formed sheet

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active
ws.title = "Sales"

headers = ["Date", "Region", "Units", "Unit price", "Revenue"]
ws.append(headers)
for r in rows:                                     # r = (date_obj, "EMEA", 12, 19.99)
    ws.append([*r, None])
last = ws.max_row
for i in range(2, last + 1):
    ws[f"E{i}"] = f"=C{i}*D{i}"                    # live formula per row

ws[f"D{last + 2}"] = "Total"
ws[f"E{last + 2}"] = f"=SUM(E2:E{last})"
ws[f"D{last + 2}"].font = Font(bold=True)

# Formats: store real dates and numbers; format them
for i in range(2, last + 3):
    ws[f"A{i}"].number_format = "yyyy-mm-dd"
    ws[f"D{i}"].number_format = "#,##0.00"
    ws[f"E{i}"].number_format = "#,##0.00"

# Header style, widths, freeze, filter
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="305496")
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
for idx, width in enumerate([12, 12, 8, 12, 14], start=1):
    ws.column_dimensions[get_column_letter(idx)].width = width
ws.freeze_panes = "A2"

# An Excel Table gives filters, banding, and structured references
table = Table(displayName="SalesTable", ref=f"A1:E{last}")
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
ws.add_table(table)

wb.save("sales.xlsx")

Rules:

• Formulas use English function names and comma separators in the file format regardless of the user’s locale (=SUM(A1:A3), not =SUMME(A1;A3)).
• Newer dynamic-array functions (FILTER, UNIQUE, XLOOKUP, LET) may need the _xlfn. prefix when written by libraries (=_xlfn.XLOOKUP(...)), or Excel shows #NAME?. Prefer classic functions when compatibility matters.
• Column widths are in character units; there is no auto-fit. Estimate from the longest value: min(max(len(str(v)) for v in column) + 2, 60).
• Do not use an auto filter on a range that is also a Table; the Table has its own.
• Dates: write datetime.date/datetime objects, not strings. Excel has no time zones — convert to the intended local time and drop tzinfo first (openpyxl rejects aware datetimes).
• Leading zeros (ZIP codes, IDs): write as strings and set number_format = "@".
• Money: keep Decimal or integer cents in your code; write floats only at the end.

Validation, conditional formatting, charts

from openpyxl.chart import BarChart, Reference
from openpyxl.formatting.rule import CellIsRule
from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"EMEA,APAC,AMER"', allow_blank=False,
                    showErrorMessage=True, errorTitle="Invalid region", error="Pick from the list")
ws.add_data_validation(dv)
dv.add(f"B2:B{last}")
# Lists longer than 255 characters must reference a range: formula1="=Lists!$A$1:$A$40"

ws.conditional_formatting.add(f"E2:E{last}",
    CellIsRule(operator="lessThan", formula=["0"], font=Font(color="9C0006"),
               fill=PatternFill("solid", fgColor="FFC7CE")))

chart = BarChart()
chart.title = "Revenue by row"
chart.add_data(Reference(ws, min_col=5, min_row=1, max_row=last), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=2, min_row=2, max_row=last))
ws.add_chart(chart, "G2")

Edit an existing workbook

from openpyxl import load_workbook
wb = load_workbook("model.xlsx")                 # formulas preserved; NOT data_only
ws = wb["Inputs"]
ws["B3"] = 0.07                                   # change an input, keep formulas downstream
wb.save("model-updated.xlsx")                     # new name; keep the original

What openpyxl loses or may alter on load/save — check before editing a user’s file:

• Shapes, text boxes, SmartArt, form controls, slicers, and pivot table caches may be dropped or degraded; images and charts are partially supported.
• VBA is removed unless you load with keep_vba=True and save as .xlsm.
• External links and complex conditional formats can change. If the workbook has these, edit a copy, compare, and tell the user what was not preserved. For heavy-feature workbooks, consider writing a separate output sheet/file instead.

Read values for analysis:

wb = load_workbook("report.xlsx", data_only=True, read_only=True)   # cached values, fast
for row in wb["Data"].iter_rows(min_row=2, values_only=True):
    ...
wb.close()

exceljs (Node)

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: 12 },
  { header: "Revenue", key: "revenue", width: 14, style: { numFmt: "#,##0.00" } },
];
ws.addRows(data);
ws.getCell(`B${data.length + 2}`).value = { formula: `SUM(B2:B${data.length + 1})` };
ws.getRow(1).font = { bold: true };
await wb.xlsx.writeFile("sales.xlsx");

Supplying result in a formula cell ({ formula, result }) sets a cached value; only do it when you computed that result correctly yourself.

Large files

• Write: Workbook(write_only=True); append rows; styles via WriteOnlyCell.
• Read: load_workbook(path, read_only=True); iterate with values_only=True; call wb.close().
• Excel’s limit is 1,048,576 rows × 16,384 columns per sheet; split or use CSV/Parquet beyond that.

Verification

• scripts/xlsx_audit.py exits 0, or every uncached formula is explained by “not yet recalculated” and the user is told; zero error values.
• Spot-check key numbers: compute the expected total in Python and compare with the formula’s cached value after recalculation.
• Open-check: render to PDF with LibreOffice (soffice --headless --convert-to pdf) or ask the user to open it; confirm headers, formats, and frozen panes look right.
• For edits, compare the audit of the original and the output: same sheets, formulas, and validations except for intended changes.