xlsx

Files (4)

• SKILL.md
• reference.md
• extract_xlsx.py · scripts/
• inspect_xlsx.py · scripts/

SKILL.md (first lines)

Run the scripts in `scripts/` rather than rewriting them inline. `reference.md`
covers number formats, conditional formatting, data validation, print setup and
the things that are not round-tripped.

## Inspect before reading or editing

```
python scripts/inspect_xlsx.py book.xlsx
python scripts/inspect_xlsx.py book.xlsx --sheet Revenue --rows 20
```

Shows sheets, dimensions, merged ranges, frozen panes, column types, a preview —
and, most importantly, every formula cell **with whether it has a cached value**.

## The trap: formulas vs values

openpyxl gives you one or the other, never both:

```python
load_workbook(path)                    # '=C2/B2-1'  the formula
load_workbook(path, data_only=True)    # 0.056       Excel's cached result
```

The cached result exists **only if Excel itself opened and saved the file**. A
workbook produced by a script has none, so `data_only=True` returns `None` for
every formula cell — turning a report into a column of blanks with no error.

If you need the numbers, compute them in Python and write values. Use formulas
when the user will open the file in Excel and wants it live. `inspect_xlsx.py`
tells you which situation you are in.

## Reading data

```
python scripts/extract_xlsx.py book.xlsx                    # CSV to stdout
python scripts/extract_xlsx.py book.xlsx --sheet Revenue --json
python scripts/extract_xlsx.py book.xlsx --header-row 3     # force it
```

It handles what breaks naive reads: **merged cells** (only the top-left holds
the value — the rest read as `None`, so a report with merged category labels
comes out full of blanks), a **header that is not row 1** (real files start with
a title and a blank row), and `max_row` **over-reporting** because rows once
carried formatting. Row numbers it reports are the real ones in the file.

For heavy analysis load with pandas instead; for anything with an awkward
layout, this script first.

## Writing

```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
ws.title = 'Revenue'
ws.append(['Region', 'Q3', 'Q4', 'Growth'])
for c in ws[1]:
    c.font = Font(bold=True, color='FFFFFF')
    c.fill = PatternFill('solid', fgColor='4472C4')
    c.alignment = Alignment(horizontal='center')

ws.append(['North', 23771, 25104, '=C2/B2-1'])
ws['D2'].number_format = '0.0%'
for col, w in zip('ABCD', (14, 12, 12, 10)):
    ws.column_dimensions[col].width = w
ws.freeze_panes = 'A2'
wb.save('report.xlsx')
```

## Number formats decide whether the file is usable

A date written as a string sorts alphabetically and breaks every filter — write
real `datetime` objects and set a format. Money: `'#,##0.00'`. Percent:
`'0.0%'` **and store `0.12`, not `12`**, or Excel displays 1200%.

## Charts

```python
from openpyxl.chart import BarChart, Reference
ch = BarChart()
ch.title = 'Revenue by region'
ch.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row),
            titles_from_data=True)
ch.set_categories(Reference(ws, min_col=1, min_row=2, max_row=ws.max_row))
ws.add_chart(ch, 'F2')
```

## Large files

Above ~50k rows write with `Workbook(write_only=True)` and read with
`load_workbook(read_only=True)` — normal mode holds every cell in memory.

## Limits — say these instead of pretending

- **openpyxl does not calculate.** A formula you write has no value until Excel
  opens the file.
- **Pivot tables, macros and some conditional formatting are not round-tripped**
  — opening and re-saving a workbook can drop them. If the source has pivots or
  VBA, say so before editing it in place.
- **Column widths are approximate**; Excel's units are not pixels.

## Verify before claiming success

```python
import openpyxl
wb = openpyxl.load_workbook('report.xlsx')
ws = wb['Revenue']
print(ws.max_row, 'rows,', ws.max_column, 'cols')
assert ws['A1'].value == 'Region', 'header missing'
```

From iaa2005/monet-directory, MIT.

More from iaa2005/monet-directory