excel-orm 0.1.7
A lightweight Excel ORM for generating templates and parsing typed row models.
License: MIT
Requires Python: >=3.12
Keywords: etl, excel, openpyxl, orm

# Excel ORM

A lightweight, typed “Excel ORM” for generating Excel templates and parsing Excel workbooks into Python objects using column descriptors.

This project is designed for the common enterprise pattern where you:

1. generate a structured `.xlsx` template for users,
2. let users fill it in,
3. load the workbook back into Python, producing typed objects grouped by model.

It uses `openpyxl` for reading/writing Excel files and supports:

* multiple model “tables” laid out horizontally on a worksheet, and
* **pivot-style sheets** (matrix input) that expand into row objects.

---

## Features

* **Typed column descriptors** (`text_column`, `int_column`, `bool_column`, `date_column`)
* **Template generation** for standard tables:

  * merged **table title cells** (pluralized model name)
  * bold headers
  * sensible column widths
  * multiple tables laid out horizontally on the same sheet with a configurable gap
* **Template generation** for pivot sheets:

  * merged title across the **row header block + pivot header span**
  * bold row-field headers in the left block (`row_fields`)
  * pivot column headers rendered across the top (`pivot_values`)
  * optional seeding of row keys down the left block (`row_values`)
* **Workbook parsing** into model-specific repositories:

  * `excel_file.cars.all()` → `list[Car]`
  * `excel_file.manufacturing_plants.all()` → `list[ManufacturingPlant]`
  * `excel_file.demands.all()` → `list[Demand]` (from pivot input)
* **Validation hooks**

  * column-level `not_null`
  * optional row exclusion rules via `excludes`
  * optional model-level `validate()` method

---

## Installation

### From PyPI

```bash
pip install excel-orm
```

### From source (uv)

```bash
git clone <your-repo-url>
cd excel-orm
uv sync
```

---

## Quick Start

### 1) Define models using `Column[...]` descriptors

```python
from excel_orm.column import Column, text_column, int_column
from excel_orm import ExcelFile, SheetSpec

class Car:
    make: Column[str] = text_column(header="Make", not_null=True)
    model: Column[str] = text_column(header="Model", not_null=True)
    year: Column[int] = int_column(header="Year", not_null=True)

class ManufacturingPlant:
    name: Column[str] = text_column(header="Factory Name", not_null=True)
    location: Column[str] = text_column(header="Location")
```

### 2) Declare a sheet containing multiple models (standard tables)

Each model becomes its own table on the same worksheet.

```python
sheet = SheetSpec(
    name="Cars",
    models=[Car, ManufacturingPlant],
    title_row=1,
    header_row=2,
    data_start_row=3,
    template_table_gap=2,
)
```

### 3) Create an `ExcelFile`, generate a template, then load data

```python
excel_file = ExcelFile(sheets=[sheet])

excel_file.generate_template("car_inventory_template.xlsx")
# Users fill in data in Excel...
excel_file.load_data("car_inventory_data.xlsx")

cars = excel_file.cars.all()
plants = excel_file.manufacturing_plants.all()

print(cars[0].make, cars[0].year)
print(plants[0].name, plants[0].location)
```

---

## Pivot Sheets

Pivot sheets are useful when users prefer matrix-style input (e.g., values by multiple row keys × time), but your application wants row objects.

### Pivot Layout Summary

For a pivot sheet:

* `row_fields` define the **leftmost columns** (starting at `row_header_col`)
* `pivot_values` are written across the top row (`header_row`) starting at `data_start_col`
* `data_start_col` is **computed** as:

```python
data_start_col = len(row_fields) + 1
```

Parsing behavior:

* pivot headers are read from `header_row` across the top until the first blank header cell
* parsing stops when the **row header block** (all `row_fields` columns) is blank for a row
* each non-blank pivot cell emits one object (unless `include_blanks=True`)

### 1) Define a “row” model

```python
from datetime import date
from excel_orm.column import Column, text_column, date_column, int_column

class Demand:
    region: Column[str] = text_column(header="Region", not_null=True)
    product: Column[str] = text_column(header="Product", not_null=True)
    dt: Column[date] = date_column(header="Date", not_null=True)
    value: Column[int] = int_column(header="Value", not_null=True)
```

### 2) Create a `PivotSheetSpec` (multi-row-fields)

```python
from datetime import date
from excel_orm import ExcelFile, PivotSheetSpec

spec = PivotSheetSpec(
    name="Demand",
    model=Demand,
    pivot_field="dt",
    row_fields=["region", "product"],
    value_field="value",
    pivot_values=[date(2025, 6, 1), date(2025, 7, 1), date(2025, 8, 1)],
    # optional: seed row keys (must match row_fields arity)
    row_values=[
        ("NA", "SKU-1"),
        ("NA", "SKU-2"),
        ("EU", "SKU-1"),
    ],
)
xf = ExcelFile(sheets=[spec])
xf.generate_template("demand_template.xlsx")
```

### 3) Load a filled pivot sheet

```python
xf.load_data("demand_filled.xlsx")
rows = xf.demands.all()

# each element is a Demand row:
# Demand(region="NA", product="SKU-1", dt=date(2025, 6, 1), value=10)
```

### `row_values` seeding rules

* If `len(row_fields) == 1`, each `row_values` element may be a **single scalar** value (backward compatible).
* If `len(row_fields) > 1`, each `row_values` element must be a **tuple/list with length == len(row_fields)**.

  * Otherwise template generation raises `ValueError`.

---

## How It Works

### Repositories

For each model you register, `ExcelFile` creates a repository attribute on the instance using a snake_case pluralized name:

* `Car` → `excel_file.cars`
* `ManufacturingPlant` → `excel_file.manufacturing_plants`
* `Demand` → `excel_file.demands`

Repositories are list-like containers with an `all()` helper:

```python
cars = excel_file.cars.all()  # list[Car]
```

### Multi-table Sheets (standard tables)

A single worksheet can host multiple model tables. During template generation:

* a merged title cell is written above each table (pluralized class name in title case)
* headers appear under the title
* data rows begin at `data_start_row`
* tables are placed horizontally with `template_table_gap` blank columns between them

During parsing:

* the library locates each model table by matching the expected header sequence
* it reads contiguous rows until a blank row is encountered

---

## Column Types

### Text

```python
from excel_orm.column import Column, text_column

class Example:
    name: Column[str] = text_column(header="Name", not_null=True, strip=True)
```

* `None` parses to `""` (empty string)
* `strip=True` trims whitespace

### Integer

```python
from excel_orm.column import Column, int_column

class Example:
    qty: Column[int] = int_column(header="Qty", not_null=True)
```

* `None` or `""` parses to `0`

### Boolean

```python
from excel_orm.column import Column, bool_column

class Example:
    active: Column[bool] = bool_column(header="Active")
```

Accepted values include:

* True: `true, t, yes, y, 1` (case-insensitive)
* False: `false, f, no, n, 0`
* `None` / empty parses to `False`

Invalid values raise `ValueError`.

### Date

```python
from datetime import date
from excel_orm.column import Column, date_column

class Example:
    start_date: Column[date] = date_column(header="Start Date")
```

The date parser supports:

* Excel-native `datetime`/`date` values from `openpyxl`
* ISO strings like `2025-06-01` and `2025-06-01T13:45:00`
* common business formats including `01-JUN-2025`

Invalid/empty values raise `ValueError`.

---

## Validation

### Column-level: `not_null`

```python
class Car:
    make: Column[str] = text_column(header="Make", not_null=True)
```

If a `not_null=True` column parses to `None` or `""`, a `ValueError` is raised.

### Row exclusion: `excludes`

If you set `excludes`, rows matching those raw values in that column will be skipped during parsing.

```python
status: Column[str] = text_column(header="Status")
status.spec.excludes = {"IGNORE", "SKIP"}
```

### Model-level: `validate()`

If your model defines a `validate(self)` method, it is called after a row is parsed.

```python
class Car:
    make: Column[str] = text_column(header="Make", not_null=True)
    year: Column[int] = int_column(header="Year", not_null=True)

    def validate(self) -> None:
        if self.year < 1886:
            raise ValueError("Invalid car year")
```

---

## Development

### Run tests

```bash
uv run pytest
```

### Lint/format

If you use Ruff:

```bash
uv run ruff check .
uv run ruff format .
```