excel-orm 0.1.8

A lightweight Excel ORM for generating templates and parsing typed row models.

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

pip install excel-orm

From source (uv)

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

---

Quick Start

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

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.

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

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.

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:

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:

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

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)

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

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

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:

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):

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

Via direct list operations on the repo attribute:

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

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

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

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

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

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.

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.

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

uv run pytest

Lint/format

If you use Ruff:

uv run ruff check .
uv run ruff format .