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

# XLSX Recipes — openpyxl

## Contents
- Create with formatting (styles, header row, merged cells, column widths)
- Charts
- Read / extract (all sheets, one sheet, used range, tables)
- Modify (insert/delete rows and columns, find-and-replace, add a sheet)
- Convert (CSV ↔ xlsx, xlsx → pandas)
- Gotchas

## Create with formatting

```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 = "Report"

# Header row
headers = ["Region", "Q1", "Q2", "Total"]
ws.append(headers)
header_font = Font(name="Arial", bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="4472C4")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Data + formula per row
for row in [["East", 100, 150], ["West", 90, 120]]:
    ws.append(row)
for r in range(2, ws.max_row + 1):
    ws.cell(row=r, column=4).value = f"=SUM(B{r}:C{r})"

# Merged title above the table: insert row first, then merge
ws.insert_rows(1)
ws["A1"] = "Quarterly Sales"
ws.merge_cells("A1:D1")
ws["A1"].font = Font(size=14, bold=True)

# Column widths (openpyxl never auto-sizes)
for col in range(1, 5):
    ws.column_dimensions[get_column_letter(col)].width = 14

# Number format
for r in range(3, ws.max_row + 1):
    for c in range(2, 5):
        ws.cell(row=r, column=c).number_format = "#,##0"

wb.save("report.xlsx")
```

## Charts

```python
from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Revenue by Region"
data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=ws.max_row)  # includes header row for series names
cats = Reference(ws, min_col=1, min_row=3, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F3")   # anchor = top-left cell of the chart
wb.save("report.xlsx")
```

## Read / extract

```python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx", data_only=True)

# All sheets → list of rows
for name in wb.sheetnames:
    ws = wb[name]
    rows = [[c.value for c in row] for row in ws.iter_rows()]

# Used range only (skips trailing empty rows/cols)
ws = wb["Report"]
data = [[c.value for c in row] for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column)]

# Streaming read for large files
wb_big = load_workbook("big.xlsx", read_only=True, data_only=True)
for row in wb_big["Sheet1"].iter_rows(values_only=True):
    pass  # process row tuple
wb_big.close()  # read_only keeps the file handle open — always close
```

## Modify

```python
from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb["Report"]

# Insert / delete
ws.insert_rows(2)          # one row above row 2
ws.delete_cols(3)          # delete column C
ws.insert_cols(3, amount=2)

# Find-and-replace (string cells only)
for row in ws.iter_rows():
    for cell in row:
        if isinstance(cell.value, str) and "East" in cell.value:
            cell.value = cell.value.replace("East", "North-East")

# Add a sheet at a position
summary = wb.create_sheet("Summary", 0)  # index 0 = first tab
summary["A1"] = "=Report!D3"

wb.save("report.xlsx")
```

Insert/delete shifts cells but does **not** rewrite formulas that referenced the shifted range — check formulas after structural edits.

## Convert

```python
# CSV → xlsx
import csv
from openpyxl import Workbook
wb = Workbook(); ws = wb.active
with open("data.csv", newline="") as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save("data.xlsx")

# xlsx → CSV (one sheet)
import csv
from openpyxl import load_workbook
ws = load_workbook("data.xlsx", data_only=True).active
with open("out.csv", "w", newline="") as f:
    csv.writer(f).writerows([c if c is not None else "" for c in row]
                            for row in ws.iter_rows(values_only=True))

# Bulk I/O escape hatch
import pandas as pd
df = pd.read_excel("data.xlsx", sheet_name="Report")   # needs openpyxl installed
df.to_excel("out.xlsx", index=False)                   # loses all formulas/formatting
```

Formula recalculation without Excel: `soffice --headless --convert-to xlsx --outdir /tmp file.xlsx` (LibreOffice recalculates and writes cached values).

## Gotchas

- **openpyxl never computes formulas.** `data_only=True` returns the cached value from the last save by Excel/LibreOffice; a file created by openpyxl and never opened elsewhere has no cached values (`None`).
- **Loading with `data_only=True` and saving destroys all formulas** — they are replaced by their cached values. Never re-save a data_only load.
- **`ws.max_row`/`max_column` count formatted-but-empty cells**, so they can overshoot the real data; trim trailing `None` rows when extracting.
- **Merged cells:** only the top-left cell holds the value; the rest read `None`. Unmerge with `ws.unmerge_cells(...)` before editing the range.
- **Dates** come back as `datetime` objects; number formats (e.g. `"YYYY-MM-DD"`) control display only.
- **Colors are 8-digit ARGB hex** (`"FF4472C4"`) or 6-digit RGB — no `#` prefix.
- **`keep_vba=True`** is required to round-trip `.xlsm`; saving an `.xlsm` load as `.xlsx` drops macros without warning.
- **Styles are copied by assignment, not reference:** to reuse a style on many cells, assign `Font(...)`/`PatternFill(...)` objects per cell or use `NamedStyle`.