Metadata-Version: 2.4
Name: excel-orm
Version: 0.2.0
Summary: A lightweight Excel ORM for generating templates and parsing typed row models.
Project-URL: Homepage, https://github.com/acdelrusso/excel-orm
Project-URL: Repository, https://github.com/acdelrusso/excel-orm
Project-URL: Issues, https://github.com/acdelrusso/excel-orm/issues
Author: Anthony Del Russo
License: MIT
Keywords: etl,excel,openpyxl,orm
Classifier: Development Status :: 3 - Alpha
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Requires-Python: >=3.12
Requires-Dist: openpyxl>=3.1.5
Description-Content-Type: text/markdown

# 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.

Or, going the other direction:

1. build a collection of model objects in Python,
2. write them directly to a structured `.xlsx` file in one call.

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/output) 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)
* **Two-way binding** — write Python objects back to Excel:

  * `excel_file.add_data(objects)` to pre-populate repos
  * `generate_template(filename)` writes data rows when repos are populated, or a blank template when they are empty
  * pivot sheets write a fully populated matrix from repo contents
* **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)
```

### 4) Write Python objects back to Excel

Instead of asking users to fill a template, you can populate an `ExcelFile` directly from Python objects and generate a pre-filled workbook.

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

cars = [
    Car(make="Toyota", model="Camry", year=2020),
    Car(make="Honda", model="Civic", year=2019),
]
plants = [
    ManufacturingPlant(name="Plant A", location="NJ"),
]

excel_file.add_data(cars)
excel_file.add_data(plants)

# generate_template detects populated repos and writes data rows automatically.
excel_file.generate_template("car_inventory_filled.xlsx")
```

You can also append directly to a repo attribute:

```python
excel_file.cars.append(Car(make="Ford", model="F-150", year=2022))
excel_file.generate_template("car_inventory_filled.xlsx")
```

---

## 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)
```

### 4) Write pivot data from Python objects

```python
xf = ExcelFile(sheets=[spec])

xf.add_data([
    Demand(region="NA", product="SKU-1", dt=date(2025, 6, 1), value=10),
    Demand(region="NA", product="SKU-1", dt=date(2025, 7, 1), value=20),
    Demand(region="EU", product="SKU-1", dt=date(2025, 6, 1), value=5),
])

# Writes title, row/pivot headers, and a filled matrix in one call.
xf.generate_template("demand_output.xlsx")
```

If `pivot_values` is not set on the spec, the pivot column order is inferred from the insertion order of pivot field values across the repo objects. Missing `(row_key, pivot_value)` combinations write a blank cell.

### `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]
```

You can populate repos in two ways before calling `generate_template()`:

**Via `add_data()`** (validated — raises `ValueError` for unregistered model types):

```python
excel_file.add_data([Car(make="Toyota", model="Camry", year=2020)])
```

**Via direct list operations** on the repo attribute:

```python
excel_file.cars.append(Car(make="Honda", model="Civic", year=2019))
excel_file.cars.extend([...])
```

Populating repos before calling `generate_template()` writes the data into the generated file. Empty repos produce a blank template.

### 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 .
```
