spb/ultra-sharp-agent-skills Public

1---2name: processing-xlsx3description: 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.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Processing XLSX1213## When to use / when NOT to use14- **Use for:** creating, reading, or modifying `.xlsx`, `.xlsm`, `.xltx` workbooks — values, formulas, formatting, sheets.15- **Do NOT use for:** `.csv`/`.tsv` files (handle as plain text), data-quality profiling, or Google Sheets (different API).1617## Quick reference1819Default library: **openpyxl**. Escape hatch: **pandas** (`read_excel`/`to_excel`) only for bulk data I/O with no formulas or formatting.2021**Create:**22```python23from openpyxl import Workbook24wb = Workbook()25ws = wb.active26ws.title = "Sales"27ws.append(["Region", "Revenue"])28ws.append(["East", 1200])29ws["B3"] = "=SUM(B2:B2)"   # formulas as strings, never hardcoded results30wb.save("sales.xlsx")31```3233**Read — always two passes:**34```python35from openpyxl import load_workbook36wb_f = load_workbook("sales.xlsx")                  # pass 1: formula strings37wb_v = load_workbook("sales.xlsx", data_only=True)  # pass 2: cached values38```39`data_only=True` returns `None` for formulas if the file was never opened/recalculated by Excel or LibreOffice — report that, don't guess values.4041**Modify:**42```python43wb = load_workbook("sales.xlsx")   # never data_only when re-saving: cached-only load discards formulas44wb["Sales"]["B2"] = 150045wb.save("sales.xlsx")46```4748## Workflow491. Classify the task: create / read / modify. For bulk dataframe dumps with zero formatting, use pandas; otherwise openpyxl.502. When modifying, first read the file (two passes) and match its existing conventions: sheet names, header row, number formats, fonts.513. Write formulas as strings (`'=SUM(B2:B9)'`); never compute a result in Python and hardcode it where a formula belongs.524. Save, then validate: `load_workbook(path)` on the output — if it raises, fix before delivering. List sheet names and dimensions to confirm expected structure.535. Report the output path and what changed (sheets touched, ranges written).5455## Edge cases & failure modes56- **openpyxl missing** → `pip install openpyxl` (pandas path additionally needs `pip install pandas`).57- **Corrupt / not a zip** → `load_workbook` raises `BadZipFile` or `InvalidFileException`; report the file is not a valid xlsx, stop.58- **Password-protected workbook** → openpyxl cannot decrypt; tell the user to remove the password (openpyxl has no decryption support).59- **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.60- **`.xlsm` macros** → open with `keep_vba=True` and save as `.xlsm`, otherwise macros are silently stripped.6162## References63Deeper recipes (formatting, charts, merged cells, find-and-replace, conversion, gotchas): see [references/recipes.md](references/recipes.md).64