XLSX

# XLSX

Manipulate Excel spreadsheets and tabular data with production-grade reliability.
Spreadsheets are where most business data lives, and small mistakes (wrong
formula reference, mismatched types) cause expensive errors.

## Keywords
excel, xlsx, xlsm, csv, tsv, spreadsheet, workbook, worksheet, formula, vlookup,
xlookup, pivot table, chart, conditional formatting, data validation, named range,
sheet protection, freeze panes, autofilter, sumif, countif, query, get & transform

## Core Truth

Three forces fight over a spreadsheet: data, formulas, and formatting. Get the
order right or everything breaks:
1. Load and validate data structure FIRST
2. Compute derived values SECOND
3. Apply formatting LAST

If you format first, data writes wipe styles. If you formula first, data writes
wipe formulas. Order matters.

Use openpyxl for .xlsx with formulas/formatting. Use pandas for pure data work,
then write through openpyxl if you need styles.

---

## 1. When To Use

- Reading/editing an existing .xlsx, .xlsm, .csv
- Building a financial model or dashboard
- Cleaning messy tabular data
- Creating reports with charts
- Bulk operations on rows/columns
- Converting between formats (csv ↔ xlsx)

## 2. When NOT To Use

- Web dashboards → use HTML/React
- Database operations → use postgres skill
- Documents → use docx
- Quick calculations → just compute and answer

## 3. The Right Library

| Need | Use |
|------|-----|
| Read/write with formulas + styles | openpyxl |
| Heavy data manipulation | pandas |
| Pure data, no styles | pandas |
| Read VBA-enabled .xlsm | openpyxl (data only — VBA not preserved) |
| Macros required | Not supportable — explain to user |
| Convert CSV→Excel | pandas + openpyxl engine |
| Excel with charts | openpyxl chart module |

## 4. Reading Messy Spreadsheets

Real spreadsheets have:
- Headers in row 5, not row 1
- Merged cells across category labels
- "Total" rows mixed with data rows
- Empty rows between sections
- Notes in random cells

Defensive reading checklist:
- Read with no header first, look at structure
- Identify header row by content match
- Skip rows that match exclusion patterns
- Validate column count per row
- Coerce types explicitly — don't trust inference

## 5. Formulas — Write or Compute?

Two paths:
- **Compute in Python, write values** — fastest, deterministic
- **Write Excel formulas** — lets user audit and modify

Default to writing formulas when the user will open the file. Default to computed
values when the file is a one-time export.

Common formula patterns:
- `=VLOOKUP(...)` — keep for backward compatibility
- `=XLOOKUP(...)` — preferred for Excel 365+ users
- `=SUMIFS(...)` — multi-condition sums
- `=INDEX(MATCH(...))` — when XLOOKUP isn't available

## 6. Charts

openpyxl supports BarChart, LineChart, PieChart, ScatterChart, AreaChart. Common
issues:
- Chart data references must be `Reference()` objects, not strings
- Add category data with `set_categories()`
- Style with `chart.style` (1-48) — most look outdated
- For modern look, customize colors via `chart.series[i].graphicalProperties`

## 7. Common Pitfalls

| Problem | Cause | Fix |
|---------|-------|-----|
| Numbers stored as text | CSV import default | Explicit type conversion |
| Dates display as numbers | No date format applied | Set number_format = 'YYYY-MM-DD' |
| Formulas show as text | Cell type wrong | Use `cell.value = "=SUM(...)"` not data_only |
| Merged cells lose data on read | Only top-left has value | Unmerge, fill down |
| Large file very slow | Using openpyxl on 100k+ rows | Switch to pandas with read_only mode |

## 8. Key Questions Before Starting

- Does the user need formulas or just values?
- Will they open it in Excel, Google Sheets, or Numbers?
- Is there an existing template to fill?
- How many rows? (>50k = use pandas)
- Are there merged cells, formulas, or charts to preserve?

## References
- `references/openpyxl-patterns.md` — Working examples for every op
- `references/pandas-to-excel.md` — When and how to bridge
- `references/financial-model-template.md` — Standard model structure