spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# XLSX Recipes — openpyxl78## Contents9- Create with formatting (styles, header row, merged cells, column widths)10- Charts11- Read / extract (all sheets, one sheet, used range, tables)12- Modify (insert/delete rows and columns, find-and-replace, add a sheet)13- Convert (CSV ↔ xlsx, xlsx → pandas)14- Gotchas1516## Create with formatting1718```python19from openpyxl import Workbook20from openpyxl.styles import Font, PatternFill, Alignment, Border, Side21from openpyxl.utils import get_column_letter2223wb = Workbook()24ws = wb.active25ws.title = "Report"2627# Header row28headers = ["Region", "Q1", "Q2", "Total"]29ws.append(headers)30header_font = Font(name="Arial", bold=True, color="FFFFFF")31header_fill = PatternFill("solid", fgColor="4472C4")32for cell in ws[1]:33    cell.font = header_font34    cell.fill = header_fill35    cell.alignment = Alignment(horizontal="center")3637# Data + formula per row38for row in [["East", 100, 150], ["West", 90, 120]]:39    ws.append(row)40for r in range(2, ws.max_row + 1):41    ws.cell(row=r, column=4).value = f"=SUM(B{r}:C{r})"4243# Merged title above the table: insert row first, then merge44ws.insert_rows(1)45ws["A1"] = "Quarterly Sales"46ws.merge_cells("A1:D1")47ws["A1"].font = Font(size=14, bold=True)4849# Column widths (openpyxl never auto-sizes)50for col in range(1, 5):51    ws.column_dimensions[get_column_letter(col)].width = 145253# Number format54for r in range(3, ws.max_row + 1):55    for c in range(2, 5):56        ws.cell(row=r, column=c).number_format = "#,##0"5758wb.save("report.xlsx")59```6061## Charts6263```python64from openpyxl.chart import BarChart, Reference6566chart = BarChart()67chart.title = "Revenue by Region"68data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=ws.max_row)  # includes header row for series names69cats = Reference(ws, min_col=1, min_row=3, max_row=ws.max_row)70chart.add_data(data, titles_from_data=True)71chart.set_categories(cats)72ws.add_chart(chart, "F3")   # anchor = top-left cell of the chart73wb.save("report.xlsx")74```7576## Read / extract7778```python79from openpyxl import load_workbook8081wb = load_workbook("report.xlsx", data_only=True)8283# All sheets → list of rows84for name in wb.sheetnames:85    ws = wb[name]86    rows = [[c.value for c in row] for row in ws.iter_rows()]8788# Used range only (skips trailing empty rows/cols)89ws = wb["Report"]90data = [[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)]9192# Streaming read for large files93wb_big = load_workbook("big.xlsx", read_only=True, data_only=True)94for row in wb_big["Sheet1"].iter_rows(values_only=True):95    pass  # process row tuple96wb_big.close()  # read_only keeps the file handle open — always close97```9899## Modify100101```python102from openpyxl import load_workbook103wb = load_workbook("report.xlsx")104ws = wb["Report"]105106# Insert / delete107ws.insert_rows(2)          # one row above row 2108ws.delete_cols(3)          # delete column C109ws.insert_cols(3, amount=2)110111# Find-and-replace (string cells only)112for row in ws.iter_rows():113    for cell in row:114        if isinstance(cell.value, str) and "East" in cell.value:115            cell.value = cell.value.replace("East", "North-East")116117# Add a sheet at a position118summary = wb.create_sheet("Summary", 0)  # index 0 = first tab119summary["A1"] = "=Report!D3"120121wb.save("report.xlsx")122```123124Insert/delete shifts cells but does **not** rewrite formulas that referenced the shifted range — check formulas after structural edits.125126## Convert127128```python129# CSV → xlsx130import csv131from openpyxl import Workbook132wb = Workbook(); ws = wb.active133with open("data.csv", newline="") as f:134    for row in csv.reader(f):135        ws.append(row)136wb.save("data.xlsx")137138# xlsx → CSV (one sheet)139import csv140from openpyxl import load_workbook141ws = load_workbook("data.xlsx", data_only=True).active142with open("out.csv", "w", newline="") as f:143    csv.writer(f).writerows([c if c is not None else "" for c in row]144                            for row in ws.iter_rows(values_only=True))145146# Bulk I/O escape hatch147import pandas as pd148df = pd.read_excel("data.xlsx", sheet_name="Report")   # needs openpyxl installed149df.to_excel("out.xlsx", index=False)                   # loses all formulas/formatting150```151152Formula recalculation without Excel: `soffice --headless --convert-to xlsx --outdir /tmp file.xlsx` (LibreOffice recalculates and writes cached values).153154## Gotchas155156- **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`).157- **Loading with `data_only=True` and saving destroys all formulas** — they are replaced by their cached values. Never re-save a data_only load.158- **`ws.max_row`/`max_column` count formatted-but-empty cells**, so they can overshoot the real data; trim trailing `None` rows when extracting.159- **Merged cells:** only the top-left cell holds the value; the rest read `None`. Unmerge with `ws.unmerge_cells(...)` before editing the range.160- **Dates** come back as `datetime` objects; number formats (e.g. `"YYYY-MM-DD"`) control display only.161- **Colors are 8-digit ARGB hex** (`"FF4472C4"`) or 6-digit RGB — no `#` prefix.162- **`keep_vba=True`** is required to round-trip `.xlsm`; saving an `.xlsm` load as `.xlsx` drops macros without warning.163- **Styles are copied by assignment, not reference:** to reuse a style on many cells, assign `Font(...)`/`PatternFill(...)` objects per cell or use `NamedStyle`.164