Xlsx

xlsx

Work with .xlsx workbooks. The format is OOXML SpreadsheetML — a zip container of XML parts. Treat each cell as a typed value: a number, a string, a datetime, or a formula. Mixing the four causes Excel to flag the workbook or compute incorrect totals.

Decide the path first

If the user provides a workbook to update, default to path B and treat the input as the formatting baseline. Choose path C only when the user says "start fresh".

Path A: Inspect

bashCopy code
python {baseDir}/scripts/inspect_xlsx.py /path/to/book.xlsx

Output:

jsonCopy code
{
  "sheets": [
    {
      "name": "Q3",
      "max_row": 10,
      "max_col": 5,
      "rows": [
        [
          {"value": "Metric", "type": "s"},
          {"value": "Value", "type": "s"}
        ],
        [
          {"value": "Revenue", "type": "s"},
          {"value": 2100000, "type": "n"}
        ]
      ]
    }
  ]
}

type follows openpyxl conventions: n (number), s (string), d (datetime), f (formula), b (bool), e (error), inlineStr (inline string). The helper script reads with data_only=False so formula expressions are returned literally; pass --data-only to get the cached computed result instead.

Path B: Edit in place

bashCopy code
python {baseDir}/scripts/edit_xlsx.py book.xlsx ops.json --out edited.xlsx

ops.json:

jsonCopy code
[
  {"op": "set_cell", "sheet": "Q3", "row": 2, "col": 2, "value": "=SUM(B3:B10)"},
  {"op": "set_cell", "sheet": "Q3", "row": 5, "col": 1, "value": "Net margin"},
  {"op": "rename_sheet", "old": "Sheet1", "new": "Summary"}
]

Rules:

• Rows and columns are 1-based (Excel convention).
• Strings starting with = are written as formulas (cell.value = "=..."), matching openpyxl behavior. To write a literal =hello use '=hello (Excel's leading-apostrophe escape) or pass an explicit as_text: true.
• Datetimes go in as ISO 8601 strings ("2026-05-06T09:00:00"); the helper parses them back to datetime objects so Excel renders the cell with date format.
• Editing a cell does not recalculate dependent formulas. Excel and LibreOffice recalculate on open. If you need cached values immediately, use a calculation engine (out of scope here).

Path C: Create from scratch

bashCopy code
python {baseDir}/scripts/create_xlsx.py spec.json --out out.xlsx

Spec:

jsonCopy code
{
  "sheets": [
    {
      "name": "Sales",
      "rows": [
        ["Region", "Revenue", "Growth"],
        ["NA", 1200000, "=B2/SUM($B$2:$B$4)"],
        ["EU", 850000, "=B3/SUM($B$2:$B$4)"]
      ],
      "merged": [{"range": "A1:C1"}],
      "freeze": "A2"
    }
  ]
}

For programmatic use:

pythonCopy code
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Revenue"])
ws.append(["NA", 1_200_000])
ws["C2"] = "=B2*1.05"          # formula
ws.merge_cells("A1:B1")
ws.freeze_panes = "A2"
wb.save("out.xlsx")

See references/openpyxl.md for styles, conditional formatting, charts, and formula references.

Common pitfalls

Boundaries

• This skill handles .xlsx (OOXML SpreadsheetML). It does not handle .xls (legacy binary), .xlsm (macro-enabled), or Google Sheets. Convert via Excel or LibreOffice export first.
• Pivot tables, slicers, and pivot caches are read-only here.
• For datasets larger than ~100k rows or 50MB workbooks, prefer pandas + to_excel with the xlsxwriter engine; openpyxl loads the whole workbook into memory.