Metadata-Version: 2.4
Name: pyhandlexl
Version: 0.4.0
Summary: An organised, labelled-table API for reading and writing data in Excel .xlsx files.
Author-email: Lewis Wainaina <lewyamendi@gmail.com>
License-Expression: MIT
Project-URL: Homepage, https://github.com/LewyAmendi/pyhandlexl
Project-URL: Repository, https://github.com/LewyAmendi/pyhandlexl
Project-URL: Issues, https://github.com/LewyAmendi/pyhandlexl/issues
Project-URL: Changelog, https://github.com/LewyAmendi/pyhandlexl/blob/main/CHANGELOG.md
Keywords: excel,xlsx,openpyxl,spreadsheet,xl
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Office/Business :: Financial :: Spreadsheet
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: openpyxl>=3.1
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Dynamic: license-file

# pyhandlexl

[![CI](https://github.com/LewyAmendi/pyhandlexl/actions/workflows/ci.yml/badge.svg)](https://github.com/LewyAmendi/pyhandlexl/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/pyhandlexl)](https://pypi.org/project/pyhandlexl/)

**Use an Excel file as a lightweight database for a small project.**

For internal tools, prototypes, and anything where people need to read or edit
the data themselves, a spreadsheet is often enough — portable, familiar, no
server to run. The friction is in the code: [openpyxl](https://openpyxl.readthedocs.io/)
gives you full control of the workbook, which means working in worksheets, cell
coordinates, ranges, and dimensions.

`pyhandlexl` is a simpler layer over openpyxl for the common case — read
organised data from a sheet, change it, write it back. It treats a worksheet as
a structured table, so your code can focus on what the data *represents* rather
than where it sits.

```python
from pyhandlexl import Table

t = Table.read("budget.xlsx")
t.read_cell(row="Revenue", column="North")   # 4200  — by name, and typed
t.add_row("Q3", [120, 90, 60])
t.write("budget.xlsx")                        # safe, atomic write
```

- **`Table`** — a worksheet as a labelled table: column headers, row labels, and
  a data grid you address by name.
- **`read_sheet` / `write_sheet`** — raw grid access for sheets that are not a
  clean table.

> **Under active development.** Usable today — expect new capabilities with each
> release, and some API changes before it stabilises. See the
> [changelog](CHANGELOG.md).

## Install

```bash
pip install pyhandlexl
```

Requires Python 3.10+.

## Quickstart

Given `budget.xlsx`:

|        | q1 | q2 |
|--------|----|----|
| **Alice** | 10 | 20 |
| **Bob**   | 30 | 40 |

```python
from pyhandlexl import Table

t = Table.read("budget.xlsx")

t.read_cell(row="Alice", column="q2")   # 20     (int)
t.read_row("Bob")                       # [30, 40]
t.read_column("q1")                     # [10, 30]

t.set_cell(row="Alice", column="q1", value=99)   # edit in place
t.add_row("Carol", [1, 2])
t.write("budget.xlsx")     # one safe, atomic write
```

Starting from scratch? Create the file first — `pyhandlexl` never creates one
implicitly:

```python
from pyhandlexl import create_workbook, Table

create_workbook("new.xlsx")
Table(data=[[10, 20]], column_headers=["q1", "q2"], row_labels=["Alice"]).write("new.xlsx")
```

> **Values keep their type.** `read_sheet` and `Table` return each cell as its
> native Python type — `str`, `int`, `float`, `bool`, `datetime`, `date`,
> `time`, `timedelta` — and an empty cell as `None`. Writing preserves type
> too; a value that isn't one of those raises `CellTypeError`. **Row labels,
> column headers, and the corner are the exception** — they are always coerced
> to `str`, since you address rows and columns by name. See
> [round-trip notes](#round-trip-notes) for the small type changes Excel forces.

## The `Table` class

A `Table` treats a worksheet as structured data: row 1 is the column headers,
column A is the row labels, `A1` is the corner, and everything from `B2` on is
the data. You work with it by name — rows, columns, and cells are addressed by
their label, not their position — and it stays in sync as you add, drop, or
rename rows and columns.

### Reading

```python
Table.read(path, sheet=None, *, column_headers=True, row_labels=True)
```

- `sheet=None` reads the active sheet; pass a name for a specific one.
- `column_headers=False` — row 1 is ordinary data, `column_headers` is empty.
- `row_labels=False` — column A is ordinary data, `row_labels` is empty.

Row labels, column headers, and the corner are always `str`. Reading a sheet
with a numeric header (`2024`) coerces it to `"2024"`; building a `Table` by
hand with a non-`str` label/header/corner raises `TypeError`. Data values keep
their type.

### The whole table at once

```python
t.corner       # value of cell A1 (settable: t.corner = "name")
t.data         # a TableData snapshot
```

```python
d = t.data
d.rows              # [[10, 20], [30, 40]]   (B2 onward, by row — typed)
d.columns           # [[10, 30], [20, 40]]   (same data, by column)
d.row_labels        # ['Alice', 'Bob']       (column A, from A2 — str)
d.column_headers    # ['q1', 'q2']           (row 1, from B1 — str)
d.corner            # value of cell A1       (str)
```

Every field is a fresh copy — mutating `t.data.rows` does not change the table.

### Access by label

```python
t.read_row("Bob")            # [30, 40]   — a data row (no label), typed
t.read_column("q1")          # [10, 30]   — a data column (no header)
```

Unknown labels raise `KeyError`. `add_row` / `add_column` / `rename_row` /
`rename_column` refuse to create a duplicate label (`ValueError`); a table read
from a file may still contain duplicates, in which case reads take the first
match.

### `read_cell` / `set_cell` — a single value, by position or by label

Both take the same addressing: a ref like `"B2"`, or `row=`/`column=` as a
matching pair — **both ints** for a 1-based Excel position (row 1 is the
header row, column 1 is the label column), or **both strings** for a row
label / column header pair. Mixing types raises `TypeError`.

```python
t.read_cell("B2")                       # 10 — first data cell, by position
t.read_cell(row=2, column=2)            # 10 — same thing, spelled out
t.read_cell(row="Alice", column="q1")   # 10 — same value, by label

t.read_cell(row=1, column=2)   # 'q1'    — a column header (str)
t.read_cell(row=2, column=1)   # 'Alice' — a row label (str)
t.read_cell("A1")               # the corner (str)
```

By position, `read_cell` can reach *any* cell — header, label, corner, or
data. By label it always reads data, wherever that row/column intersection
actually lives.

A string given through `row=`/`column=` is always a label lookup — it does
**not** accept a column letter like `"B"` for a position. Use a plain number
(`column=2`) or `ref="B2"` for letter-based positions.

### Editing (in place, returns `None`)

```python
t.set_cell("B2", value=99)                       # by position — ref
t.set_cell(row=2, column=2, value=99)            # by position — row=/column= as ints
t.set_cell(row="Alice", column="q1", value=99)   # by label    — row=/column= as strings
t.set_row("Bob", [50, 60])          # replace a row   (length must match)
t.set_column("q1", [1, 2])          # replace a column (length must match)

t.add_row("Carol", [1, 2])          # append a labelled row
t.add_column("q3", [5, 6])          # append a labelled column

t.drop_row("Bob")
t.drop_column("q2")

t.rename_row("Alice", "ALICE")
t.rename_column("q1", "Q1")
t.corner = "name"
```

`set_cell` only ever touches **data** — addressing a header, row label, or the
corner by position raises `ValueError`; use `rename_row`, `rename_column`, or
`t.corner = value` for those. Wrong-length values, and a name that would
duplicate an existing label/header, raise `ValueError`; unknown labels raise
`KeyError`; a non-`str` row label, column header, or corner raises `TypeError`.
Data values may be any type; a value Excel can't store is caught on `.write()`
(`CellTypeError`), not when it's set.

You can also build a table from nothing:

```python
from pyhandlexl import create_workbook, Table

t = Table([], column_headers=["q1", "q2"])
t.add_row("Alice", [10, 20])
create_workbook("new.xlsx")
t.write("new.xlsx")
```

### Writing

```python
t.write(path, sheet=None)
```

Reassembles headers into row 1 and labels into column A, then writes the whole
sheet. Other sheets in the file are left untouched. The file must already
exist — see [Files](#files).

### Equality

```python
t1 == t2            # compares data, headers, labels, corner
```

Row count and row-label membership go through `t.data` instead of `len()`/`in`,
so the call site says what's being checked: `len(t.data.rows)`,
`"Bob" in t.data.row_labels`.

## Files

`pyhandlexl` **never creates a file implicitly** — this is deliberate, so a
typo in a path can't silently produce a stray workbook.

```python
create_workbook(path, *, sheet="Sheet")   # FileExistsError if the path is taken
delete_workbook(path)                      # FileNotFoundError if it isn't there
```

`create_workbook` makes a new empty `.xlsx` with one worksheet. `delete_workbook`
removes a workbook file, retrying while it is locked (open in Excel) before
raising `FileLockedError`, and refuses a path that isn't an Excel extension.

Every write operation — `write_sheet`, `append_rows`, `create_sheet`,
`Table.write` — raises `FileNotFoundError` if the file does not exist yet.

## The raw layer

For sheets that are not a labelled table — plain grids, exports, odd layouts.

```python
from pyhandlexl import read_sheet, write_sheet, append_rows

read_sheet(path, sheet=None, *, pad=False)
```

Returns `list[list[object]]` — each cell as its native type (`str`, `int`,
`float`, `bool`, `datetime`, `date`, `time`, `timedelta`), an empty cell as
`None`. Trailing `None` values are trimmed from each row (a fully empty row
becomes `[]`); `pad=True` right-pads every row with `None` to the widest row's
length instead.

```python
write_sheet(path, rows, sheet=None, *, orientation="rows")
```

Replaces the target sheet with `rows` (other sheets untouched); adds `sheet` if
it does not exist. Values are written with their type preserved — no conversion;
`None` leaves the cell empty. A value that isn't a type Excel can store raises
`CellTypeError`. `orientation="columns"` writes each inner list *down a column*
instead of across a row.

```python
append_rows(path, rows, sheet=None)
```

Appends after the last row. Empty input is a no-op.

### Editing a grid: `pyhandlexl.grid`

Helpers for the `list[list[object]]` that `read_sheet` returns. Each takes a
grid and returns a **new** grid, so they compose in a pipeline. Rows and
columns are **1-based** (row 1 is the first row), matching `Table`.

```python
from pyhandlexl import read_sheet, write_sheet, grid

g = read_sheet("data.xlsx")

g = grid.set_value(g, 2, 3, "changed")   # one cell
g = grid.set_row(g, 1, ["a", "b", "c"])  # replace a row
g = grid.set_column(g, 2, [1, 2, 3])     # replace a column
g = grid.insert_row(g, 2, [...])         # insert before row 2
g = grid.insert_column(g, 1, [...])      # insert as the new first column
g = grid.append_row(g, [...])
g = grid.append_column(g, [...])
g = grid.delete_row(g, 3)
g = grid.delete_column(g, 2)

g = grid.transpose(g)   # rows <-> columns (ragged rows padded with None)
g = grid.pad(g)         # rectangularise

write_sheet("data.xlsx", g)
```

`get_row(g, i)` / `get_column(g, j)` read a single line. Column operations that
take `values` require `len(values)` to equal the row count; out-of-range
indices raise `IndexError`. Gaps introduced by any of these (padding a short
row, `pad`, `transpose`) are filled with `None`.

## Sheet management

```python
from pyhandlexl import (
    list_sheets, sheet_exists, create_sheet, delete_sheet, rename_sheet,
)

list_sheets(path)                 # ['Sheet', 'Data']
sheet_exists(path, "Data")        # True
create_sheet(path, "Results")     # ValueError if it already exists
delete_sheet(path, "Old")         # refuses to delete the last sheet
rename_sheet(path, "Old", "New")
```

`create_sheet` needs an existing file (`create_workbook` first). Sheet names are
validated everywhere: max 31 characters, none of `\ / ? * [ ] :`, and
`"History"` is reserved by Excel.

## Safe writes

Every write goes through the same steps:

1. Save to a temporary file in the same directory.
2. Verify it is a readable `.xlsx`.
3. Atomically replace the original (`os.replace`).

If any step fails the temporary file is removed and the original is left exactly
as it was. If the target is locked (open in Excel), writes retry briefly before
raising `FileLockedError`.

## Round-trip notes

Excel forces a few small type changes on `write` → `read`:

| You write | You read back | Why |
|---|---|---|
| `5.0` (float) | `5` (int) | Excel stores all numbers as float; openpyxl returns `int` when the value is whole |
| `date(2026, 1, 1)` | `datetime(2026, 1, 1, 0, 0)` | Excel has no date-only type |
| `""` (empty string) | `None` | Excel doesn't distinguish an empty string from a blank cell |
| int larger than 2⁵³ | loses precision | float limit |

Rejected outright (`CellTypeError`): `Decimal` (would silently become `float`),
timezone-aware `datetime`/`time` (Excel has no timezone), and any non-cell type
(`list`, `dict`, `bytes`, `complex`, …). A `str` that looks numeric (`"007"`)
stays a `str` in both directions.

`check_cell_value(value)` runs this check on a single value if you want to
validate before writing.

## Errors

All raised exceptions derive from `PyhandlexlError`:

| Exception | Also a | Meaning |
|---|---|---|
| `SheetNameError` | `ValueError` | invalid worksheet name |
| `DimensionError` | `ValueError` | data exceeds Excel's 1,048,576 × 16,384 grid |
| `SheetNotFoundError` | `KeyError` | no worksheet with that name |
| `FileLockedError` | `OSError` | file stayed locked through every retry |
| `CellTypeError` | `TypeError` | a value is not a type Excel can store |
| `InvalidFileError` | — | file is missing or not a readable `.xlsx` |

## Not in scope

`pyhandlexl` deliberately does **not** handle: cell formatting, styles, fonts,
formulas, charts, images, merged cells, `.xls` (old format), or password
protection / encryption. For any of that, use openpyxl directly.

## Development

```bash
python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\Activate.ps1
pip install -e ".[dev]"
pytest
ruff check . && ruff format --check .
```

## License

MIT — see [LICENSE](LICENSE).
