# Spreadsheet practices (XLSX)

Extends `SKILL.md` with workflow rules that affect **how** models and reports should be built.

## Tool selection

| Task | Typical library |
|------|-----------------|
| Load / analyze tabular data | **pandas** (`read_excel`, `to_excel`) |
| Preserve formulas, styles, fine formatting | **openpyxl** |
| Raw OOXML surgery | `scripts/ooxml_kit/expand_ooxml_zip.py` → edit → `build_ooxml_zip.py` |

## Formulas vs Python numbers

Put **Excel formulas** in cells for anything that should update when inputs change. Avoid computing aggregates in Python and writing numeric literals into result cells unless the user explicitly wants static snapshots.

**Good:** `sheet["B10"] = "=SUM(B2:B9)"`
**Avoid:** `sheet["B10"] = sum(...)` computed in Python and written as a plain number.

## Mandatory recalc when formulas exist

After writing a workbook with formulas:

`python scripts/workbook_recalculate.py output.xlsx`

Read the JSON: if `errors_found`, use `error_summary` (`#REF!`, `#DIV/0!`, `#VALUE!`, etc.) to locate fixes, then save and recalc again.

LibreOffice must be available; the script wires a Basic macro via `libreoffice_runner` (see `ooxml_kit`).

## Cell formatting with openpyxl

```python
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, numbers

# Font
header_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF")

# Fill
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")

# Border
thin_border = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)

# Alignment
centered = Alignment(horizontal="center", vertical="center", wrap_text=True)

# Apply to a cell
cell = ws["A1"]
cell.font = header_font
cell.fill = header_fill
cell.border = thin_border
cell.alignment = centered

# Apply to a range (headers row)
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
```

### Number formats

```python
# Currency
cell.number_format = '"$"#,##0.00'

# Percentage (one decimal)
cell.number_format = '0.0%'

# Thousands separator
cell.number_format = '#,##0'

# Zeros as dashes (finance style)
cell.number_format = '#,##0;(#,##0);"-"'

# Date
cell.number_format = 'YYYY-MM-DD'

# Accounting with negatives in parentheses
cell.number_format = '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)'
```

### Column widths and row heights

```python
ws.column_dimensions["A"].width = 20
ws.row_dimensions[1].height = 30

# Auto-width approximation
for col in ws.columns:
    max_len = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 50)
```

## Conditional formatting

```python
from openpyxl.formatting.rule import (
    CellIsRule, ColorScaleRule, DataBarRule, IconSetRule, FormulaRule,
)
from openpyxl.styles import PatternFill, Font

# Highlight cells > 100 in green
ws.conditional_formatting.add(
    "B2:B100",
    CellIsRule(operator="greaterThan", formula=["100"],
               fill=PatternFill(bgColor="C6EFCE")),
)

# Red text for negative values
ws.conditional_formatting.add(
    "C2:C100",
    CellIsRule(operator="lessThan", formula=["0"],
               font=Font(color="FF0000")),
)

# Color scale (green → yellow → red)
ws.conditional_formatting.add(
    "D2:D100",
    ColorScaleRule(start_type="min", start_color="63BE7B",
                   mid_type="percentile", mid_value=50, mid_color="FFEB84",
                   end_type="max", end_color="F8696B"),
)

# Data bars
ws.conditional_formatting.add(
    "E2:E100",
    DataBarRule(start_type="min", end_type="max", color="4472C4"),
)

# Formula-based rule (highlight entire row if col A = "Total")
ws.conditional_formatting.add(
    "A2:F100",
    FormulaRule(formula=['$A2="Total"'],
                fill=PatternFill(bgColor="FFF2CC")),
)
```

## Data validation

```python
from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown list
dv = DataValidation(type="list", formula1='"Yes,No,N/A"', allow_blank=True)
dv.prompt = "Select an option"
dv.promptTitle = "Status"
ws.add_data_validation(dv)
dv.add("C2:C100")

# Numeric range
dv_num = DataValidation(type="whole", operator="between", formula1="1", formula2="100")
dv_num.error = "Value must be between 1 and 100"
ws.add_data_validation(dv_num)
dv_num.add("D2:D100")

# Dropdown from a named range or cell range
dv_ref = DataValidation(type="list", formula1="=Lookups!$A$1:$A$20")
ws.add_data_validation(dv_ref)
dv_ref.add("E2:E100")
```

## Multi-sheet workbook patterns

### Summary + detail sheets

```python
wb = openpyxl.Workbook()

# Detail sheets first
for dept in departments:
    ws = wb.create_sheet(title=dept["name"])
    # ... write dept data ...

# Summary sheet references detail sheets
summary = wb.create_sheet(title="Summary", index=0)
for i, dept in enumerate(departments, start=1):
    summary[f"A{i+1}"] = dept["name"]
    summary[f"B{i+1}"] = f"='{dept['name']}'!B10"   # inter-sheet reference
```

### Inter-sheet formula references

Use single quotes around sheet names containing spaces:
```python
cell.value = "='Q1 Revenue'!B10"      # sheet name with spaces
cell.value = "=Summary!B10"           # simple name, no quotes needed
```

### Table of contents sheet

```python
toc = wb.create_sheet(title="Contents", index=0)
toc["A1"] = "Sheet"
toc["B1"] = "Description"
for i, ws in enumerate(wb.worksheets[1:], start=2):
    toc[f"A{i}"] = ws.title
    toc[f"A{i}"].hyperlink = f"#'{ws.title}'!A1"
    toc[f"A{i}"].font = Font(color="0563C1", underline="single")
```

## Auto-filter and Excel tables

```python
# Auto-filter on a range
ws.auto_filter.ref = "A1:F100"

# Excel Table (ListObject)
from openpyxl.worksheet.table import Table, TableStyleInfo
table = Table(displayName="SalesData", ref="A1:F100")
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium9", showFirstColumn=False,
    showLastColumn=False, showRowStripes=True,
)
ws.add_table(table)
```

## Freeze panes and print setup

```python
# Freeze top row and first column
ws.freeze_panes = "B2"

# Print titles (repeat header row on every printed page)
ws.print_title_rows = "1:1"

# Page setup
ws.page_setup.orientation = "landscape"
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0    # auto height

# Print area
ws.print_area = "A1:F50"
```

## Data cleaning pipeline

### Detecting and removing junk rows

```python
import pandas as pd

df = pd.read_excel("messy.xlsx", header=None)   # read without headers

# Drop fully blank rows
df.dropna(how="all", inplace=True)

# Find the real header row (first row where most cells are non-null strings)
for i, row in df.iterrows():
    non_null = row.dropna()
    if len(non_null) > len(df.columns) * 0.5 and all(isinstance(v, str) for v in non_null):
        df.columns = df.iloc[i]
        df = df.iloc[i+1:].reset_index(drop=True)
        break
```

### Header normalization

```python
df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(r"[^\w]+", "_", regex=True)
    .str.strip("_")
)
# Deduplicate: "revenue", "revenue" → "revenue", "revenue_2"
seen, new_cols = {}, []
for c in df.columns:
    if c in seen:
        seen[c] += 1
        new_cols.append(f"{c}_{seen[c]}")
    else:
        seen[c] = 1
        new_cols.append(c)
df.columns = new_cols
```

### Type coercion

```python
# Dates stored as text
df["date"] = pd.to_datetime(df["date"], errors="coerce")

# Numbers stored as text (with commas, dollar signs)
df["amount"] = (
    df["amount"].astype(str)
    .str.replace(r"[$,]", "", regex=True)
    .pipe(pd.to_numeric, errors="coerce")
)
```

### Handling merged cells

```python
from openpyxl import load_workbook

wb = load_workbook("merged.xlsx")
ws = wb.active

# Unmerge and fill down
for merge_range in list(ws.merged_cells.ranges):
    min_col, min_row, max_col, max_row = merge_range.bounds
    value = ws.cell(row=min_row, column=min_col).value
    ws.unmerge_cells(str(merge_range))
    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            ws.cell(row=row, column=col, value=value)
```

## Pivot-like summaries

```python
import pandas as pd

df = pd.read_excel("sales.xlsx")
pivot = pd.pivot_table(
    df, values="revenue", index="region", columns="quarter",
    aggfunc="sum", margins=True, margins_name="Total",
)

# Write to a styled sheet
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active
ws.title = "Pivot"
for r in dataframe_to_rows(pivot, index=True, header=True):
    ws.append(r)

# Style the output
for cell in ws[1]:
    cell.font = Font(bold=True)
```

## Financial-style presentation (when modeling)

Unless a template dictates otherwise:

- **Inputs** often highlighted in **blue** RGB (0,0,255); **formulas** in **black**; **internal sheet links** in **green**; **external file links** in **red**; **key assumptions** may use **yellow** fill.
- **Years** as text like `"2024"` where comma thousands would confuse.
- **Currency** with explicit units in headers (e.g. `$mm`).
- **Zeros** display as `-` via number formats where appropriate.
- **Percentages** sensible precision (e.g. one decimal).
- **Negatives** in parentheses in finance-style sheets.

Keep assumptions in dedicated cells; reference them in formulas instead of embedding magic numbers.

## pandas snippets

```python
import pandas as pd

df = pd.read_excel("in.xlsx")
all_sheets = pd.read_excel("in.xlsx", sheet_name=None)
df.to_excel("out.xlsx", index=False)
```

Use `openpyxl` engine explicitly when you need to round-trip existing formatting.

---

For package-level XML validation, use `ooxml_kit/audit_ooxml_package.py` on the `.xlsx` or unpacked folder.
