Metadata-Version: 2.4
Name: pyhandlexl
Version: 0.9.6
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 :: 4 - Beta
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: Programming Language :: Python :: 3.14
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<4,>=3.1.3
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Requires-Dist: mypy>=1.11; extra == "dev"
Requires-Dist: types-openpyxl; 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", "Budget")
t.read_cell(row="Alice", column="q2")   # 20  — by name, and typed
t.add_row("Carol", [1, 2])
t.write("budget.xlsx")                   # safe, atomic write
```

`pyhandlexl` splits cleanly in two, by how organised your data is:

- **Organised data → [`Table`](#organised-data-the-table-class).** Your sheet
  has column headers and row labels — a real table. Address everything by
  name (`t.read_cell(row="Revenue", column="North")`), not by cell position.
- **Unorganised data → [the grid layout](#unorganised-data-the-grid-layout).**
  Plain grids, exports, odd one-off dumps — no headers or labels to name.
  `read_sheet`/`write_sheet` give you a `list[list]`; `pyhandlexl.grid` edits
  it by position.

Have a `.csv` file instead of `.xlsx`? `read_sheet`/`write_sheet`/
`append_rows`/`grid` work directly on one too, as a raw grid — see
[CSV rules](#csv-rules-for-read_sheet-write_sheet-and-append_rows). To move
data *between* CSV and `.xlsx` instead, see
[Moving data between CSV and .xlsx](#moving-data-between-csv-and-xlsx).

> **Beta.** The features are in and tested; the API may still change in small ways
> before 1.0, and every change is recorded in the
> [changelog](https://github.com/LewyAmendi/pyhandlexl/blob/main/CHANGELOG.md).
> Pin a version (`pyhandlexl~=0.9.6`) if you need it not to move.

## Install

```bash
pip install pyhandlexl
```

Requires Python 3.10+ and [openpyxl](https://openpyxl.readthedocs.io/) 3.1.3 or newer
(below 4), which pip installs for you. Tested on Linux, Windows and macOS, on Python
3.10 to 3.14, against both the oldest and the newest openpyxl it allows. The package
is fully typed (`py.typed`) and passes `mypy --strict`.

## Quickstart

`pyhandlexl` never creates a file or a table implicitly, so a full lifecycle
looks like this:

```python
from pyhandlexl import Table, create_workbook, create_sheet

create_workbook("budget.xlsx")
create_sheet("budget.xlsx", "Data")

Table(
    data=[[10, 20], [30, 40]],
    column_headers=["q1", "q2"],
    row_labels=["Alice", "Bob"],
    name="Budget",
).create("budget.xlsx", sheet="Data")   # first placement — explicit
```

From then on, read and write it by name — no need to remember which sheet
it's on:

```python
t = Table.read("budget.xlsx", "Budget")

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

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

## Organised data: the `Table` class

**Use this when your data has structure — column headers and row labels
that name what's in each cell.** A `Table` treats a worksheet region as
exactly that: a header row, a label column, a corner, and data — and it
stays in sync as you add, drop, or rename rows and columns. Every `Table`
has a **name**, which is how you find it again; a worksheet can hold more
than one, side by side — see
[Multiple named tables on one sheet](#multiple-named-tables-on-one-sheet).

If your sheet is just a plain grid with nothing to name, skip ahead to
[Unorganised data: the grid layout](#unorganised-data-the-grid-layout) instead.

### Reading

```python
Table.read(path, name)
```

Finds the table called `name` anywhere in the workbook — no need to know
which sheet it's on. `TableNotFoundError` if there's no table by that name.
The table remembers what it read, so a later `write()` can merge with changes
another writer made in between — see
[Two people editing the same table](#two-people-editing-the-same-table).

Row labels, column headers, and the corner are always `str`. A numeric header
cell (`2024`) is read back as `"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.set_corner("name")  # changes the value of cell A1
t.data                 # a TableData snapshot — read the corner via t.data.corner
```

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

```python
t.to_dict()
# {'Alice': {'q1': 10, 'q2': 20}, 'Bob': {'q1': 30, 'q2': 40}}
```

`to_dict` keys the outer dict by row label and the inner one by column
header, so both axes survive in a single structure. Build a table the other
way with `Table.from_dict`:

```python
Table.from_dict(table, corner="", *, name)
```

```python
from pyhandlexl import Table

t = Table.from_dict(
    {"Alice": {"q1": 10, "q2": 20}, "Bob": {"q1": 30, "q2": 40}},
    name="Budget",
)
```

The outer keys become the row labels, in order. Every inner dict must have
the same keys, in the same order — that order becomes the column headers
(`ValueError` otherwise); `table` itself, and every row in it, must be a
mapping (`TypeError` otherwise). `table` must not be empty — with no rows
there's nothing to infer the column headers from. A duplicate row label or
column header (only possible on a table read from a file) collapses to its
last value on `to_dict`, since dict keys must be unique.

### 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. A malformed `ref`
(`""`, `"not a ref"`) raises `ValueError`.

### 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.insert_row(1, "Carol", [1, 2])       # insert as the new first row
t.insert_column(2, "q0", [5, 6])       # insert as the new 2nd column

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

t.rename_row("Alice", "ALICE")
t.rename_column("q1", "Q1")
t.set_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
`set_corner` 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`. A row
label or column header can never be `""` — that raises `ValueError` too (the
corner has no such restriction; it may be empty). Data values may be any
type; a value Excel can't store is caught on `.write()` (`CellTypeError`),
not when it's set. `drop_column` refuses to remove a table's only remaining
column (`ValueError`) — `column_headers` is required and can never end up
empty.

`insert_row`/`insert_column` take a 1-based position among existing data rows/
columns: `1` inserts as the new first one; `len(...) + 1` inserts as the last
— the same result as `add_row`/`add_column`, which are exactly that special
case. Same constraints as `add_row`/`add_column` otherwise; a non-`int`
position raises `TypeError`, an out-of-range one raises `IndexError`.

The `values` you give `add_row`, `insert_row`, `set_row`, `set_column`,
`add_column`, and `insert_column` must be a sequence: a bare string raises
`TypeError` rather than being quietly split into one cell per character
(`add_row("x", "ab")` is a mistake, not two cells). The same goes for the rows you
hand to `write_sheet`/`append_rows` and the `pyhandlexl.grid` functions.

You can also build a table up from nothing before placing it — `create()` is
only needed once, for that first placement. `column_headers=` and `name=` are
the only required arguments; data and row labels can start empty and grow
with `add_row`:

```python
from pyhandlexl import create_workbook, Table

t = Table(column_headers=["q1", "q2"], name="Budget")
t.add_row("Alice", [10, 20])
create_workbook("new.xlsx")
t.create("new.xlsx", sheet="Sheet")
```

### Writing

```python
t.create(path, sheet)   # first placement on a sheet
t.write(path)            # every write after that
```

`create` places a brand-new named table on `sheet`: `TableExistsError` if the
name is already taken, `SheetNotFoundError` if the sheet doesn't exist,
`SheetKindError` if `sheet` already holds plain grid data (see
[One kind of data per sheet](#one-kind-of-data-per-sheet)). Once a table has
been created, `write` reassembles its headers, labels, and data and writes it
back to its tracked location — no `sheet=` needed, and `TableNotFoundError` if
the table was never created (or has since been deleted). The file must
already exist for either call — see [Files](#files). If another writer changed
the table since you read it, `write` merges their changes with yours instead of
overwriting them — see the next section.

### Two people editing the same table

More than one script — or person — can work on the same table without losing
each other's changes, as long as they aren't writing at the very same instant.
A `Table` remembers what it last saw on disk. If someone else changed the
table since, `write()` reads what is on disk *now* and **merges** it with your
edits, rather than replacing it with your stale copy:

```python
alice = Table.read("log.xlsx", "Log")
bob   = Table.read("log.xlsx", "Log")

alice.add_row("alice-1", [1, 2])
bob.add_row("bob-1", [3, 4])

alice.write("log.xlsx")
bob.write("log.xlsx")        # merges: the file now has BOTH rows

bob.data.row_labels          # [..., 'alice-1', 'bob-1'] — bob's object is updated too
bob.last_merge               # MergeReport(rows_added=('alice-1',), ...)
```

Rows and columns are matched by label/header, and the merge is decided per
row, column, and cell:

| You and the other writer… | Result |
|---|---|
| added different rows/columns | both appear (yours after theirs when you both appended) |
| edited different cells | both edits survive |
| added the same label with the same values | one row, no conflict |
| deleted a row/column the other left alone | it's gone |
| renamed a row/column the other edited | your new name, their edit |
| changed the corner, style, or a column type (only one side did) | that change is kept |
| **changed the very same thing differently** | **yours wins**, with a warning |
| deleted a row/column the other **edited** | your delete wins, with a warning |
| edited a row/column the other **deleted** | your edit restores it, with a warning |

Where yours wins over something the other writer did, `write()` still succeeds
and emits a `MergeConflictWarning` naming each place it overwrote (up to ten,
then a count). It isn't an error — use `warnings.filterwarnings("error",
category=MergeConflictWarning)` if you'd rather stop instead. After any
merge, `t.last_merge` is a `MergeReport` with `rows_added`, `rows_removed`,
`columns_added`, `columns_removed`, `cells_updated` (your cells that took the
other writer's value), and `conflicts`; it is `None` if nothing had changed on
disk. Values are compared the way Excel stores them, so `5` vs `5.0`, or a
`date` vs its midnight `datetime`, is not mistaken for an edit — while `True`
vs `1` is.

Details worth knowing:

- Column types are checked **after** merging, so a restriction the other writer
  added can reject a value you set: `ColumnTypeError`, nothing is written, and
  your `Table` object is left exactly as it was.
- A `Table` that was built in memory and never read or created has nothing to
  merge against, so `write()` simply replaces the table, as before.
- Merging needs unique labels. A table with duplicate row labels or column
  headers (only possible from a hand-edited file) can't be matched row by row,
  so yours replaces the disk version, with a warning.

**What this does not do: it is not a lock.** The merge closes the gap between
your `read` and your `write` — the long part, where you're editing. It does
*not* stop two writes that land at the very same instant: each checks for
changes and then saves as separate steps, so a write that sneaks in between them
can still be overwritten. Writes to *different* tables in one workbook can also
overwrite each other for the same reason (every write re-saves the whole
workbook). If your writers can genuinely collide, serialise them yourself
(a queue, a single writer process, or a lock of your own around the write).
pyhandlexl deliberately does not take file locks itself, because OS file
locking behaves differently across platforms and filesystems.

### Styling a table

Every table is visually styled when it's created or written: bold, filled
headers and row labels; alternating row colors; a thick border around the
whole table with a heavier line separating headers/labels from data. This
is a real property of the table, not a one-time paint job — adding a row or
column extends the same look to cover it, and a table shifted right to make
room for a growing neighbor (see
[Multiple named tables on one sheet](#multiple-named-tables-on-one-sheet))
keeps its look at the new position too.

```python
from pyhandlexl import Table, TableStyle

t = Table(data=[[10, 20]], column_headers=["q1", "q2"], row_labels=["Alice"], name="Budget")
t.style                       # TableStyle.DEFAULT, unless you passed style=
t.style = TableStyle.MINIMAL  # takes effect on the next create()/write()
```

Three ready-made looks:

| | header/labels | data banding | border |
|---|---|---|---|
| `TableStyle.DEFAULT` | bold, black on olive green | light gray stripe | thick outer, medium header line |
| `TableStyle.MINIMAL` | bold, plain colors | none | thick outer, medium header line |
| `TableStyle.NONE` | plain | none | none |

For anything else, build one — every field is a plain 6-digit hex color
(`""` means "none," for `header_fill`/`band_fill`/`border_color` only —
font colors are always required):

```python
TableStyle(
    header_font_name="Calibri", header_font_size=11, header_font_color="000000",
    header_bold=True, header_fill="76933C",
    data_font_name="Calibri", data_font_size=11, data_font_color="000000",
    band_fill="F2F2F2", border_color="000000",
)
```

Styling never touches `t.data` or equality — `t1 == t2` compares data,
headers, labels, corner, and column types only, however differently the two
are styled. It has no effect on `.csv` files — there's nothing there to style.

### Restricting a column's type

A column defaults to accepting any type `check_cell_value` allows — restrict
one to a single Excel-native type instead, and a value that doesn't match
raises `ColumnTypeError` on the next `create()`/`write()`:

```python
from pyhandlexl import Table, ColumnType

t = Table(
    data=[[1000, "Alice"]],
    column_headers=["amount", "name"],
    row_labels=["r1"],
    name="Orders",
    column_types={"amount": ColumnType.NUMBER},
)
t.column_types              # {'amount': ColumnType.NUMBER, 'name': ColumnType.ANY}
t.set_column_type("name", ColumnType.TEXT)
```

`ColumnType.NUMBER`, `.TEXT`, `.BOOLEAN`, `.DATE`, `.TIME`, `.DURATION`, and
`.ANY` (the default) follow *Excel's* type model, not Python's exactly:
`NUMBER` covers both `int` and `float` (Excel stores every number as a
float and doesn't distinguish them — see
[Round-trip notes](#round-trip-notes)), and `DATE` covers both
`datetime.date` and `datetime.datetime` (Excel has no date-only type). A
blank cell (`None`) is always allowed regardless of a column's type — the
restriction governs what a real value may be, not whether the cell has been
filled in yet.

Checked the same time as `CellTypeError` — a value can be a type Excel can
store at all, and still fail this because it isn't the type *this* column
was restricted to. Restricting a column doesn't touch data already in it
until the next `create()`/`write()`; renaming, inserting, or dropping a
column moves or drops its restriction along with it, and a newly inserted
column always starts as `ColumnType.ANY`. If the reserved schema sheet is
deleted and rebuilt from markers (see
[Multiple named tables on one sheet](#multiple-named-tables-on-one-sheet)),
restrictions are **inferred** from what each column currently holds rather
than restored — nothing in a cell records that a column was restricted.
That can go either way: an empty or mixed column comes back `ColumnType.ANY`
(a restriction lost), and a column you'd deliberately left unrestricted, but
which happens to hold a single type, comes back restricted to it (one
invented). Check `t.column_types` after a rebuild and `set_column_type` back
anything that's wrong.

### Table metadata

```python
t.info   # a TableInfo snapshot — not data, not style: when, where, how big
```

```python
i = t.info
i.created_at       # when create() first placed it (UTC datetime, or None)
i.modified_at      # when write()/create() last touched it (UTC, or None)
i.n_rows           # data row count
i.n_cols           # data column count
i.sheet            # which worksheet it's on (str, or None)
i.style            # same object as t.style
i.column_types     # same mapping as t.column_types
```

`sheet`, `created_at`, and `modified_at` are `None` until the table has
actually been placed with `create()` (or loaded with `read()`) — a table
you're still building up in memory has no sheet or history yet.
`modified_at` only reflects *this* table's own `create()`/`write()` calls —
a table shifted right to make room for a growing neighbour (see
[Multiple named tables on one sheet](#multiple-named-tables-on-one-sheet))
is not itself modified, so its `modified_at` is untouched by that.

For checking a table's metadata without reading (and type-converting) its
actual row data — say, listing every table's size and dates in a workbook
— there's a standalone lookup that skips all of that:

```python
from pyhandlexl import table_info

table_info(path, "Sales")   # same TableInfo, without an existing Table object
```

The dates are persisted in the reserved schema sheet, and unlike size and
sheet they can't be reconstructed from the table itself: if that sheet is
deleted and rebuilt from markers, nothing in a cell records when a table was
created or last modified — and, unlike column types, there's no data to
infer them from — so a rebuilt table reports `created_at`/`modified_at` as
`None` rather than a guessed time. Size and sheet still come back exact,
same as always.

### Equality

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

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

### Multiple named tables on one sheet

A worksheet isn't limited to one table — a "Sales" table and an "Inventory"
table can live side by side. Tables stack **left to right** with exactly one
empty column between them, and always start at row 1.

```python
from pyhandlexl import Table, create_workbook, create_sheet

create_workbook("shop.xlsx")
create_sheet("shop.xlsx", "Data")

sales = Table(
    data=[[100, 200], [150, 250]],
    column_headers=["North", "South"],
    row_labels=["Q1", "Q2"],
    name="Sales",
)
sales.create("shop.xlsx", sheet="Data")          # first placement — explicit

inventory = Table(data=[[10], [20]], column_headers=["Units"], row_labels=["A", "B"], name="Inventory")
inventory.create("shop.xlsx", sheet="Data")       # lands to the right of Sales

sales = Table.read("shop.xlsx", "Sales")          # no sheet= needed — the name finds it
sales.add_column("East", [300, 350])
sales.write("shop.xlsx")                          # Inventory shifts right automatically
```

- **`list_tables(path)`** — every named table in the workbook, across all sheets.
- **`table_info(path, name)`** — a table's [metadata](#table-metadata) (dates,
  size, sheet, style, column types) without reading its row data.
- **`delete_table(path, name)`** — removes a named table; `TableNotFoundError`
  if it doesn't exist. Leaves the space empty — other tables on the sheet are
  not shifted to close the gap, and the name is free to reuse afterwards.
- Table names are **unique per workbook**, not per sheet.
- **Growing a table's columns** (`add_column`, or writing back wider data)
  automatically shifts every table to its right, on the same sheet, further
  right to make room — this can rewrite more than one table's position in a
  single `.write()`. **Growing rows never shifts anything**, since nothing sits
  below a table. A shifted table keeps its
  [style](#styling-a-table) at the new position.
- **Duplicate row labels/column headers within one table** are blocked the
  same way regardless of how many tables share the sheet.

**How it stays correct if you edit the sheet by hand.** Every named table's
first cell literally contains the text `"TABLE NAME"`, with the table's name
in the cell beside it. Before trusting its tracked position, `pyhandlexl`
checks that marker is still there. If someone has inserted or deleted columns
by hand and it's moved, `pyhandlexl` scans that sheet, finds it by its marker,
and repairs the tracked position — this can make `Table.read` write to the
file even though it looks like a pure read. If the marker is gone entirely
(the table was deleted, or the name cell was overwritten), you get
`TableNotFoundError` rather than silently wrong data. This self-heal only
recovers a *moved* table on its *original* sheet — if a table was deleted, cut
to a different sheet, or resized by hand, `pyhandlexl` reports it missing
rather than guessing further.

The tracking data itself lives in a reserved worksheet, `_pyhandlexl_tables`
— `list_sheets()` never shows it, since it isn't a sheet you created or can
write to. Leave it alone: it's marked with a red sheet tab and a warning
comment on its first cell, every time it's written, so it's hard to miss
even for someone opening the workbook by hand without having read this.

**If that reserved sheet is deleted entirely**, self-heal can't help — it
only relocates a table it already has a schema entry for. Instead, every
operation that reads the schema (`Table.read`, `Table.write`, `Table.create`,
`table_info`, `list_tables`, `delete_table`, `sheet_kind`, `write_sheet` (to a
sheet that already exists), `append_rows`, `clear_all_sheet_data`,
`delete_sheet`, `rename_sheet`) **rebuilds it** the moment it finds it missing:
it scans every sheet for `"TABLE NAME"` markers, emits a
`SchemaRebuiltWarning`, and — once the call
succeeds — saves the rebuilt sheet (re-marked with the same tab color and
comment) so the scan isn't repeated next time. The warning names the tables
found and points at the line of *your* code that made the call. A workbook
with no tables has nothing to rebuild, so it neither warns nor gains a
schema sheet.

**What a rebuild restores:**

- **Which tables exist, and their names** — every marker found, on any sheet.
- **Each table's exact position and size** — sheet, top-left corner, rows and
  columns. Markers bound each other directly, and there's nowhere legitimate
  for real content to sit past a table's true edge.
- **All of the table's contents** — data, row labels, column headers, and
  corner. Those live in the cells, never in the schema, so they can't be lost
  by deleting it.
- **A style and column types, as a best-effort guess** (next list).

**What a rebuild does not — and cannot — restore:**

- **Creation and modification times.** Nothing in a cell records them, and
  there's no data to infer them from, so `t.info.created_at`/`.modified_at`
  come back `None` rather than a guessed time. (See [Table metadata](#table-metadata).)
- **The exact style.** It is read back from the cells themselves and can be
  imperfect: a table with exactly one data row, for instance, can never have
  its row-banding detected (there's no second row to compare against), so it
  always comes back reporting no banding even if it originally had some.
- **Your declared column types.** They are *inferred* from the values each
  column currently holds — a column whose values all share one Excel-native
  type comes back restricted to it, and an empty or mixed column comes back
  `ColumnType.ANY`. That can go either way: a restriction can be **lost**, or
  one **invented** for a column you'd left unrestricted. See
  [Restricting a column's type](#restricting-a-columns-type).
- **A table whose marker is gone.** If the `"TABLE NAME"` cell was deleted or
  overwritten, there is nothing to find: the table is not rebuilt, and
  reading it raises `TableNotFoundError`.
- **A table name claimed by two markers.** It can't be safely resolved, so that
  table is left out of the rebuilt schema (named in the warning) rather than
  guessing which one is real; it isn't reachable by name until you resolve it
  by hand. Every unambiguous table is unaffected.

After a rebuild, check `t.style`, `t.column_types`, and `t.info` on the tables
you care about, set back anything that's wrong, and `write()` it.

**Workbooks written by older versions** keep working: a reserved sheet with
fewer columns than today's (written before column types, or before creation and
modification times, existed) is read with sensible defaults — the default
style, unrestricted columns, `None` dates — and upgraded to the current layout
by the next write.

**Editing a table by hand.** Numeric and date headers or labels are read as text.
A blank data cell is `None`. Duplicate labels or headers are tolerated on read.
A *blank* header or label can't be read as a table, and the error says which cell
(`... the column header in cell C2 is blank ...`) so you can fix it in Excel. Rows or
columns inserted above or left of a table are healed; deleting the marker cell (or a
column through it) makes the table unfindable. Formulas and error values (`#DIV/0!`)
read as their text.

A table may be any width Excel allows (16,383 data columns), and its column types
are always recorded in full — for very wide tables in a compact form, since a cell
holds at most 32,767 characters.

**One limit of the rebuild:** a cell holding the words `TABLE NAME` is only mistaken
for a table's marker when it sits at the very top of a sheet with text beside it — for
example a *grid* sheet whose first row starts `TABLE NAME | hello`. Inside a real
table (a label or data value that says it) it is recognised for what it is.

### One kind of data per sheet

A worksheet holds **either** named tables **or** plain [grid data](#unorganised-data-the-grid-layout),
never both — mixing them would let one silently corrupt the other (a grid
write landing across a table's marker, say). Whichever kind writes to a
sheet first claims it:

```python
from pyhandlexl import sheet_kind, clear_all_sheet_data

sheet_kind(path, "Data")   # "empty", "grid", or "table"
```

- A fresh or freshly-cleared sheet is `"empty"` — either kind can claim it next.
- `write_sheet`/`append_rows` claim it as `"grid"`; `Table.create` claims it as `"table"`.
- Writing the other kind to an already-claimed sheet raises `SheetKindError`
  rather than writing something that would corrupt what's already there:
  `write_sheet`/`append_rows` refuse a `"table"` sheet, and `Table.create`
  refuses a `"grid"` sheet. The reserved `_pyhandlexl_tables` schema sheet
  can't be targeted directly by any of them either, or by `sheet_kind`
  itself — it isn't a sheet with a kind of its own. Neither can
  `delete_sheet` or `rename_sheet` — deleting it directly, or renaming it
  away (or renaming some *other* sheet onto its reserved name), is refused
  (`SheetKindError`); it's only ever meant to disappear as a side effect of
  deleting every table that lives on it.
- **`clear_all_sheet_data(path, sheet)`** wipes a sheet's cells, styles, and
  any tables tracked on it, back to `"empty"` — the one way to reverse a
  claim and let the sheet be reused as the other kind.

`delete_sheet` and `rename_sheet` keep the schema in sync with reality:
deleting a sheet forgets any tables that lived on it (instead of leaving
stale, unreachable schema entries), and renaming one moves its tables'
tracked location along with it — they stay readable under their same names.

### Displaying a table

```python
t.show()                     # default: first 5 and last 5 rows, "..." between
t.show(rows=10)               # only the first 10 data rows
t.show(head=2, tail=2)        # first 2 and last 2 rows
t.show(head=None, tail=None)  # every row, no truncation
```

The default (`head=5, tail=5`) shows everything with no divider if the table
has 10 rows or fewer — truncation only kicks in past that. `rows=` overrides
the head/tail defaults outright. Prints a plain, aligned, whitespace-padded
grid to the console — a debug convenience, unrelated to cell formatting in the
`.xlsx` (still out of scope; see [Not in scope](#not-in-scope)). `rows`,
`head`, and `tail` must each be a non-negative `int` or `None` — a negative
value raises `ValueError` and a non-`int` raises `TypeError`, rather than
silently doing something confusing with Python's slice semantics.

## Unorganised data: the grid layout

**Use this when your data has no headers or labels to name — a plain grid,
an export, an odd one-off layout.** There's nothing here to address by name,
so everything works by position: a `list[list]` you read, edit by row/column
index, and write back. If your sheet *does* have column headers and row
labels, use [`Table`](#organised-data-the-table-class) instead — it'll save
you from re-inventing header/label handling by hand.

**These same functions work directly on a `.csv` file too** — pass a path
ending in `.csv` instead of `.xlsx` and they read/write the file itself
rather than a worksheet inside a workbook. A `.csv` file is treated purely as
a raw grid, never as a table: no types, no sheets, no size limit — see
[CSV rules](#csv-rules-for-read_sheet-write_sheet-and-append_rows) below.
`pyhandlexl.grid`'s editing functions need no changes to work either way,
since they only ever touch the `list[list]` these return, never a file.

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

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

For an **.xlsx** file: 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. `orientation="columns"`
returns each worksheet column as an inner list instead — the transpose of
`"rows"`, with the same trimming/`pad` rules applied down each column.

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

For an **.xlsx** file: 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. `sheet`
must not already hold table data (`SheetKindError`) — see
[One kind of data per sheet](#one-kind-of-data-per-sheet).

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

For an **.xlsx** file: appends after the last row. Empty input is a no-op.
Same `SheetKindError` restriction as `write_sheet`.

### CSV rules for read_sheet, write_sheet, and append_rows

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

create_csv("data.csv")                    # files are never created implicitly
write_sheet("data.csv", [["a", "b"], [1, 2]])
g = read_sheet("data.csv")                # [['a', 'b'], ['1', '2']]

g = grid.insert_row(g, 1, ["h1", "h2"])   # the grid toolkit works unchanged
write_sheet("data.csv", g)

append_rows("data.csv", [["x", "y"]])
```

- **Every value becomes/comes back as a plain `str`** (`None` becomes `""`
  on write) — a CSV field has no other type, so nothing is inferred as a
  number, date, or boolean. That is a promise, not a stopgap: a CSV stays
  all text. `read_sheet`'s trimming/`pad` behavior still applies, but pads
  with `""` instead of `None`.
- **`sheet` must be `None`** — a `.csv` file has no sheets. Passing anything
  else raises `ValueError`.
- **No size limit** — `DimensionError`/`CellTypeError` never apply to a
  `.csv` write; it isn't bound by the `.xlsx` grid.
- **The file must already exist**, exactly like `.xlsx` — call `create_csv`
  first. `write_sheet` replaces the whole file's content (there's no sheet
  to isolate a change to) and writes atomically, the same as every other
  write in this library.

**Why `append_rows` is a separate function, not just
`write_sheet(path, grid.append_row(read_sheet(path), values))`:**
performance. That composition would read *every* existing row into Python
first, just to add a few more at the end — expensive once a sheet or file
is large. `append_rows` never does: it costs only what the *new* rows cost,
regardless of how much is already there. The two formats earn that
differently, because they can differently:

- For **.xlsx**, it loads the workbook (unavoidable — that's how you open
  one at all) but calls the equivalent of "append one row" only for the new
  rows; the existing ones are never walked or turned into Python values.
  Still written atomically, like every other `.xlsx` write.
- For **.csv**, it does better still: unlike a zip-based `.xlsx`, a plain
  text file can be modified without rewriting it, so `append_rows` opens it
  in append mode and writes only the new rows, touching none of the
  existing bytes. That's cheaper than the `.xlsx` case, but it does mean a
  `.csv` append isn't wrapped in the temp-file-then-replace safety
  `write_sheet` gets: a crash mid-write could leave a malformed trailing
  row, but — unlike a failed *replace* — can never lose or corrupt a byte
  that was already there.

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

```python
grid.show(g)                     # default: first 5 and last 5 rows, "..." between
grid.show(g, rows=10)             # only the first 10 rows
grid.show(g, head=2, tail=2)      # first 2 and last 2 rows
grid.show(g, head=None, tail=None)  # every row, no truncation
```

Prints a plain, aligned, whitespace-padded block to the console — a debug
convenience, with the same truncation rules (and the same `rows`/`head`/`tail`
validation) as [`Table.show`](#displaying-a-table). Ragged rows are padded
with `""` for display only; the grid itself is untouched.

## Moving data between CSV and .xlsx

There are two different things "CSV" might mean here, and two different
tools for them:

- **Editing a `.csv` file as itself** — `read_sheet`/`write_sheet`/
  `append_rows`/`grid` all work directly on a `.csv` path now, treating it
  as a raw grid (never a table). See
  [CSV rules](#csv-rules-for-read_sheet-write_sheet-and-append_rows) above.
  Nothing ever crosses into `.xlsx` here.
- **Moving data *between* a `.csv` file and an `.xlsx` worksheet** —
  `import_csv_to_xl`/`export_xl_to_csv`, below. These are one-shot
  **conversions**, not a live link and not editing in place.

```python
from pyhandlexl import import_csv_to_xl, export_xl_to_csv

import_csv_to_xl(csv_path, path, *, sheet=None, encoding="utf-8-sig")
export_xl_to_csv(path, csv_path, *, sheet=None, encoding="utf-8")
```

```python
import_csv_to_xl("results.csv", "experiments.xlsx", sheet="Log")
export_xl_to_csv("experiments.xlsx", "backup.csv", sheet="Log")
```

**`import_csv_to_xl`** reads `csv_path` and writes it into `sheet` of the
existing `.xlsx` file at `path` — same target semantics as `write_sheet`
(replaced if `sheet` already exists, created if it doesn't). Every field
becomes a `str` cell: CSV has no other type, so nothing is inferred as a
number, date, or boolean. `encoding` defaults to `"utf-8-sig"`, which also
strips a leading byte-order mark transparently (common in CSVs saved by
Excel on Windows).

**`export_xl_to_csv`** reads `sheet` from `path` and writes a brand-new CSV
file at `csv_path` — refuses to overwrite an existing file there
(`FileExistsError`), the same caution as `create_workbook`. Every value is
stringified with `str()` (`None` becomes an empty field): a lossy, one-way
conversion — re-importing the result gives back text, not the original
types. Written atomically, like every other write in this library (see
[Safe writes](#safe-writes)).

Both require the `.xlsx` file to already exist (`FileNotFoundError`
otherwise — see [Files](#files)), and both check the path they were given —
`import_csv_to_xl`'s `path` must be a workbook and `csv_path` must be
`.csv`, `export_xl_to_csv` the other way around (`ValueError` otherwise).
`import_csv_to_xl` also raises `DimensionError` if the CSV has more rows or
columns than an `.xlsx` worksheet can hold, checked *before* anything is
written.

## Files

Applies whether you're working with organised or unorganised data.
`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_csv(path)                           # the .csv equivalent of create_workbook
```

`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.
`create_csv` makes a new empty `.csv` file — same guarantee, same
`FileExistsError` if something's already there, so a typo can't silently
overwrite or produce a stray file either way.

Every write operation — `write_sheet`, `append_rows`, `create_sheet`,
`Table.create`, `Table.write` — raises `FileNotFoundError` if the file does
not exist yet. This applies to a `.csv` target exactly the same as `.xlsx`.

A read-only file is never overwritten: a write to one raises `FileReadOnlyError`
(a kind of `FileLockedError`) before anything happens — on every platform, not just
Windows. A save keeps the file's permissions, and if the path is a symlink the file it
points to is the one updated.

`create_workbook` only makes **`.xlsx`** files (`ValueError` for any other name, so a
typo can't produce, say, an `.xlsx` inside `data.csv` that Excel refuses to open), and
the directory must already exist. Existing **`.xlsm`/`.xltm`** workbooks can be
read and written and keep their macros; an `.xltx` template stays a template.
Charts, merged cells, column widths, frozen panes, data validation, comments,
hyperlinks, formulas and defined names elsewhere in a workbook are preserved when
you write to it — but anything openpyxl itself can't keep (images without
Pillow installed, form controls, slicers) is not.

## Sheet management

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

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, or the schema sheet; forgets its tables too
rename_sheet(path, "Old", "New")  # moves its tables' tracked location along with it; refuses the schema sheet either way
list_tables(path)                 # every named table in the workbook (all sheets)
sheet_kind(path, "Data")          # "empty", "grid", or "table" — see below
clear_all_sheet_data(path, "Data")  # wipes it back to "empty"
```

`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. See
[One kind of data per sheet](#one-kind-of-data-per-sheet) for `sheet_kind`
and `clear_all_sheet_data`.

Excel treats sheet names as **case-insensitive**, so `create_sheet`,
`rename_sheet`, `write_sheet`, and `append_rows` refuse a name that differs from
an existing sheet's only by capitalisation (`ValueError`) instead of letting the
file quietly end up with `data1` when you asked for `data`. (Changing just a
sheet's own capitalisation — `rename_sheet(path, "log", "LOG")` — is fine.) The
same goes for the reserved schema sheet's name, `_pyhandlexl_tables`, in *any*
capitalisation, or with stray spaces around it: no call can create, rename to,
write to, or otherwise use it (`SheetKindError`). A sheet name also can't contain a
control character or a character XML can't hold, or begin or end with an
apostrophe (`SheetNameError`).

## Safe writes

Every write goes through the same steps:

1. Refuse if the file is read-only (`FileReadOnlyError`), and follow it if it is a
   symlink so the real file is the one updated.
2. Save to a temporary file in the same directory.
3. Verify it is a readable `.xlsx` **and that every XML part in it parses** — a
   file that merely opens as a zip is not enough.
4. Give it the original's permissions and 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 |
| `datetime`/`time`/`timedelta` with microseconds | rounded to the millisecond | Excel keeps time to a millisecond |
| `"a\r\nb"` or a lone `"\r"` in a data cell | `"a\nb"` | line breaks are stored as `\n`, the same on every platform |
| `"=1+1"` (a `str` starting with `=`) | `"=1+1"`, but Excel treats it as a **formula** | untested, unsupported: see below |

Rejected outright (`CellTypeError`), because a silent change or loss is worse than
an error: `Decimal` (would silently become `float`), timezone-aware
`datetime`/`time` (Excel has no timezone), and any non-cell type (`list`,
`dict`, `bytes`, `complex`, …). Also refused, since Excel can't hold them:

- a string containing a character an `.xlsx` file cannot contain — control
  characters such as NUL, lone surrogates, `U+FFFE`/`U+FFFF`. A single one of
  these makes the *whole workbook* unreadable, not just its cell;
- a string longer than **32,767 characters**, which Excel would silently cut short;
- `nan` and the infinities, which would silently become a blank cell;
- an `int` too large to be a float;
- a date before **1900-01-01** or after 9999-12-31 23:59:59.999;
- a carriage return in a table's **name, row label, column header, or corner** —
  those are found by their exact text, and Excel stores line breaks as `\n`
  (a data cell just has its line breaks normalised, as the table above says).

A `str` that looks numeric (`"007"`) stays a `str` in both directions.

**A string that starts with `=` is stored as a formula — but formulas are not a
supported feature.** That is how openpyxl treats it, and pyhandlexl neither evaluates,
validates nor tests formulas: `"=1+1"` reads back as the text `"=1+1"`, but Excel will
calculate it. It also means a table read from a workbook that contains formulas writes
them back as formulas, unchanged. There is currently no way to store literal text that
begins with `=`.

> **Don't build on this.** Formula handling is untested behaviour that happens to fall
> out of openpyxl, and it **may change or be removed in a future release** — for
> example, strings starting with `=` might be stored as literal text instead. If you
> need formulas, use openpyxl directly.

`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 (open in Excel) through every retry |
| `FileReadOnlyError` | `FileLockedError` | file is read-only, so nothing was written — retrying can't help; catching `FileLockedError` still covers it |
| `CellTypeError` | `TypeError` | a value is not something Excel can store faithfully (a foreign type, an XML-illegal or over-long string, `nan`/`inf`, an out-of-range date, …) |
| `ColumnTypeError` | `TypeError` | a value doesn't match its column's `ColumnType` restriction |
| `TableNotFoundError` | `KeyError` | no named table with that name, or its marker is gone |
| `TableExistsError` | `ValueError` | a named table with that name already exists |
| `SheetKindError` | `ValueError` | the sheet already holds the other kind of data (table vs. grid), or its name is the reserved schema sheet's (in any capitalisation) |
| `InvalidFileError` | — | file is missing or not a readable `.xlsx` |

`SchemaRebuiltWarning` and `MergeConflictWarning` are not in this table on
purpose — they're `Warning`s (via Python's `warnings` module), not
`PyhandlexlError`s. The operation that triggers them still succeeds; see
[Multiple named tables on one sheet](#multiple-named-tables-on-one-sheet) for
when the first fires and [Two people editing the same table](#two-people-editing-the-same-table)
for the second.

## Not in scope

`pyhandlexl` deliberately does **not** handle: arbitrary cell-level
formatting (`Table`'s own [styling](#styling-a-table) is a curated set of
choices, not a general "format any cell" API), conditional formatting,
formulas, charts, images, merged cells, `.xls` (old format), or password
protection / encryption. For any of that, use openpyxl directly. (Charts,
merged cells and the like that are already in a workbook are left alone when
you write to it — see [Files](#files) — but pyhandlexl has no way to create or
edit them.)

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

## License

MIT — see [LICENSE](https://github.com/LewyAmendi/pyhandlexl/blob/main/LICENSE).
