---
name: processing-xlsx
description: Creates, reads, and modifies Excel workbooks (.xlsx, .xlsm, .xltx) with openpyxl — cell values, formulas, formatting, multiple sheets. Use when the user asks to create, open, read, edit, update, or fix an Excel file or spreadsheet, mentions .xlsx/.xlsm/.xltx files, workbooks, worksheets, or Excel formulas. Do not use for .csv/.tsv files (plain-text tabular data) or for data-quality profiling.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Processing XLSX

## When to use / when NOT to use
- **Use for:** creating, reading, or modifying `.xlsx`, `.xlsm`, `.xltx` workbooks — values, formulas, formatting, sheets.
- **Do NOT use for:** `.csv`/`.tsv` files (handle as plain text), data-quality profiling, or Google Sheets (different API).

## Quick reference

Default library: **openpyxl**. Escape hatch: **pandas** (`read_excel`/`to_excel`) only for bulk data I/O with no formulas or formatting.

**Create:**
```python
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Revenue"])
ws.append(["East", 1200])
ws["B3"] = "=SUM(B2:B2)"   # formulas as strings, never hardcoded results
wb.save("sales.xlsx")
```

**Read — always two passes:**
```python
from openpyxl import load_workbook
wb_f = load_workbook("sales.xlsx")                  # pass 1: formula strings
wb_v = load_workbook("sales.xlsx", data_only=True)  # pass 2: cached values
```
`data_only=True` returns `None` for formulas if the file was never opened/recalculated by Excel or LibreOffice — report that, don't guess values.

**Modify:**
```python
wb = load_workbook("sales.xlsx")   # never data_only when re-saving: cached-only load discards formulas
wb["Sales"]["B2"] = 1500
wb.save("sales.xlsx")
```

## Workflow
1. Classify the task: create / read / modify. For bulk dataframe dumps with zero formatting, use pandas; otherwise openpyxl.
2. When modifying, first read the file (two passes) and match its existing conventions: sheet names, header row, number formats, fonts.
3. Write formulas as strings (`'=SUM(B2:B9)'`); never compute a result in Python and hardcode it where a formula belongs.
4. Save, then validate: `load_workbook(path)` on the output — if it raises, fix before delivering. List sheet names and dimensions to confirm expected structure.
5. Report the output path and what changed (sheets touched, ranges written).

## Edge cases & failure modes
- **openpyxl missing** → `pip install openpyxl` (pandas path additionally needs `pip install pandas`).
- **Corrupt / not a zip** → `load_workbook` raises `BadZipFile` or `InvalidFileException`; report the file is not a valid xlsx, stop.
- **Password-protected workbook** → openpyxl cannot decrypt; tell the user to remove the password (openpyxl has no decryption support).
- **Large file (>50 MB or >1M cells)** → read with `read_only=True`, write with `write_only=True`; both stream instead of loading everything in memory.
- **`.xlsm` macros** → open with `keep_vba=True` and save as `.xlsm`, otherwise macros are silently stripped.

## References
Deeper recipes (formatting, charts, merged cells, find-and-replace, conversion, gotchas): see [references/recipes.md](references/recipes.md).