---
name: xlsx
description: Create, read, and edit Microsoft Excel (.xlsx) spreadsheets. Use when the user wants an Excel file / .xlsx — tables, reports with formulas, styled sheets — or to read, aggregate, or edit an existing workbook. Uses the open-source openpyxl (and pandas for heavy data). Not for Word, PDF, or PowerPoint.
---

# Excel (.xlsx) spreadsheets

Build and edit `.xlsx` files with the open-source **openpyxl** library (use **pandas** for large data crunching). Run through Bash on absolute paths.

## Preflight — Python + the library (install on demand)
1. **Find Python.** `python3 --version`, else `python --version`. Use whichever works (`PY`).
2. **If neither exists, guide the user once** (in their language) — this needs a one-time Python install; then wait, don't fabricate:
   - macOS `brew install python` · Windows `winget install -e --id Python.Python.3.12` (or python.org, add to PATH) · Linux `sudo apt install -y python3 python3-pip`.
3. **Ensure openpyxl** (installs only if missing):
   ```bash
   PY -c "import openpyxl" 2>/dev/null || PY -m pip install --quiet --user openpyxl
   ```
   Add `pandas` the same way only if you need dataframes. If pip is offline/blocked, say so and stop.

## Create a workbook
```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

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

headers = ["Month", "Revenue", "Cost", "Margin"]
ws.append(headers)
for c in range(1, len(headers) + 1):                     # style header row
    cell = ws.cell(row=1, column=c)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1F4E79")
    cell.alignment = Alignment(horizontal="center")

ws.append(["Jan", 12000, 8000, "=B2-C2"])                # formulas as strings
ws.append(["Feb", 15000, 9000, "=B3-C3"])
ws["B2"].number_format = "#,##0"                          # or "$#,##0.00", "0.0%"
ws.column_dimensions["A"].width = 14
ws.freeze_panes = "A2"                                    # freeze header
wb.save("/abs/path/out.xlsx")
```
Formulas are written as text (`"=SUM(B2:B10)"`); Excel computes them on open. Cell address is 1-based `ws.cell(row, column)` or `ws["B2"]`.

## Read an existing workbook
```python
from openpyxl import load_workbook
wb = load_workbook("/abs/path/in.xlsx", data_only=True)   # data_only → last-saved formula VALUES
ws = wb["Sales"]
for row in ws.iter_rows(min_row=2, values_only=True):
    month, revenue, *_ = row
```
`data_only=True` gives cached values but returns `None` for cells whose values were never computed by Excel. Omit it to read the formula strings instead.

## Edit in place (keep styles / other sheets)
`load_workbook(...)` then mutate and `save()` — preserves untouched sheets, styles, and (with `keep_vba=True`) macros. Don't rebuild from scratch.

## Large data
Use pandas to compute, then write:
```python
import pandas as pd
df = pd.read_excel("/abs/path/in.xlsx", sheet_name="Sales")
out = df.groupby("Month")["Revenue"].sum().reset_index()
out.to_excel("/abs/path/summary.xlsx", index=False)       # engine=openpyxl by default
```
For very large writes use `Workbook(write_only=True)` and append rows to stream to disk.

## Charts
```python
from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True); chart.set_categories(cats)
ws.add_chart(chart, "F2")
```

## Conventions
- Save to an absolute path; report it.
- Show the numbers you computed and note the metric definition (per the session setup: numeric table / plain-language reading / both).
- Never invent values; read the real cells.
