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

| You have | Goal | Path |

|---|---|---|

| Existing .xlsx | Read sheets and cells | A. Inspect |

| Existing .xlsx | Modify specific cells | B. Edit-in-place |

| Nothing or a brief | Build a new workbook | C. Create from scratch |

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

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

Output:

{
  "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

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

ops.json:

[
  {"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

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

Spec:

{
  "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:

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](references/openpyxl.md) for styles, conditional

formatting, charts, and formula references.

---

Common pitfalls

| Symptom | Cause | Fix |

|---|---|---|

| Cell shows =SUM(...) as text, not the result | Wrote the string with as_text: true or workbook lacks cached values | Open in Excel and save once; or use a calc engine |

| Date renders as a serial number (45000) | Wrote int instead of datetime | Pass an ISO string and let the helper parse; or set cell.number_format |

| Merged range loses borders | Borders apply to the top-left cell only after merge | Apply border to the top-left cell post-merge |

| Workbook breaks Excel after edit | Removed a defined name without updating dependent formulas | Audit defined_names before delete |

| Pivot tables disappear | openpyxl drops pivot caches on save | Edit pivots in Excel; programmatic edit is not supported |

---

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.