Metadata-Version: 2.4
Name: excel_spec
Version: 0.1.0
Summary: Declarative JSON spec -> Excel with cell-locked images, template copy and match-back. Agent/LLM-friendly openpyxl wrapper.
Keywords: excel,xlsx,openpyxl,json,spec,agent,llm,ai-agent,tool-use,excel-image,excel-template,spreadsheet
Author: Flinn
Author-email: Flinn <flinnxie@outlook.com>
License-Expression: MIT
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
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
Requires-Dist: openpyxl>=3.0
Requires-Dist: pillow>=10 ; extra == 'images'
Requires-Python: >=3.10
Project-URL: Homepage, https://github.com/Flinn-X/excel_spec
Project-URL: Repository, https://github.com/Flinn-X/excel_spec
Project-URL: Issues, https://github.com/Flinn-X/excel_spec/issues
Project-URL: Changelog, https://github.com/Flinn-X/excel_spec/blob/main/CHANGELOG.md
Provides-Extra: images
Description-Content-Type: text/markdown

# excel_spec

Declarative JSON spec → Excel, with images **locked to cells** (so they move
and resize with the cells they sit on), template copy, and key-column
match-back. Aimed at AI agents / LLM tool use: agents emit a flat JSON spec,
the library turns it into a `.xlsx` with one `wb.save()` at the end.

**Status:** v0.1 (beta). API may move before v1.0.

---

## When to use this vs the alternatives

| You want…                                                       | Reach for                     |
|-----------------------------------------------------------------|-------------------------------|
| An agent to produce a workbook with images that **stay glued to cells** when columns are resized | **excel_spec** (this library) |
| A JSON spec that updates an existing template Excel by **primary key** (`match_config`) | **excel_spec**                |
| Full styles, charts, conditional formatting, pivot tables       | openpyxl / xlsxwriter         |
| `df.to_excel()` from a DataFrame                                | pandas + openpyxl            |
| Reading Excel back into Python                                  | openpyxl / pandas             |
| Templated fills with Jinja2-like substitution in `.xlsx` files   | xlsxtpl / xlsx-template (Node)|
| A CLI + project scaffold for batch-rendering JSON specs          | Herndon                       |

The unifying idea: **excel_spec** is for "the AI agent (or you) emits a JSON
spec — including pictures and key-based row matching — and the library
turns it into a `.xlsx` in one shot." It is intentionally not a general
openpyxl replacement.

---

## Install

```bash
pip install excel_spec                 # core: openpyxl only
pip install excel_spec[images]         # also pulls Pillow (required if any spec entry writes an image)
```

Pillow is openpyxl's optional dep, not this library's. If your spec contains
image entries and Pillow is missing, openpyxl raises
`ImportError: You must install Pillow to fetch image objects`. Installing
`excel_spec[images]` pulls Pillow from this side.

---

## Quick start

The fastest way to see what the library does — ship the demo script with
the repo; it auto-generates a tiny image so there's nothing to prepare:

```bash
git clone https://github.com/Flinn-X/excel_spec
cd excel_spec
uv sync --extra images          # or: pip install -e ".[images]"
python -m examples.demo         # writes examples/out/hello.xlsx
```

Open `hello.xlsx` and **drag column A wider** — the blue swatch in `A3`
expands with the column. That's the whole point of `TwoCellAnchor /
editAs="twoCell"`, the default image anchor in `excel_spec`.

If you already know the shape and just want to render a spec JSON:

```python
from excel_spec import to_excel

to_excel("spec.json")                  # path mode
to_excel(spec_dict, output="out.xlsx")  # dict mode (typical for AI agents)
```

---

## Spec

> This section is a summary. The authoritative document is
> [`docs/SPEC.md`](docs/SPEC.md) — all field names, defaults, validation
> codes (`E_*` / `W_*`), and edge cases are listed there in full. If you
> build tools (agents, IDE plugins, validators) that emit or read
> `excel_spec` JSON, read that file.

### Top level

- `excel_template_path` (string, optional): if present, copied into the
  output workbook's starting state. Resolved relative to `base_dir`.
  Useful for "fill this template's cells".
- `output_excel_path` (string, required): the `.xlsx` to write. Resolved
  relative to the current working directory. If `to_excel(output=...)` is
  also given, that argument wins; if both are given and differ,
  `validate()` emits `E_OUTPUT_CONFLICT`. **The output file is written
  atomically — running the same spec twice overwrites the previous output,
  it does not append.**
- `sheets` (array): see below. **Unknown top-level fields and any field
  starting with `_` are silently ignored (lenient by default)** — the
  `_`-prefix convention is reserved for reverse-tooling meta in v0.2.

### Per sheet

- `sheet_name` (string, required). If the same name appears more than
  once in `sheets`, the rows merge into one sheet (first occurrence wins
  for `column_widths`) and a `W_DUPLICATE_SHEET` warning is emitted.
- `column_widths` (`{"A":15, "B":25}`, optional): sheet-level, applied
  regardless of whether `data_list` touches those columns. Letters
  must satisfy `^[A-Za-z]{1,3}$` and stay within XLSX's 16,384-column
  limit (`"XFD"`).
- `row_height` (number, optional): applied to every row written by this
  sheet's `data_list`.
- `start_row` (int, default `1`): the row to start at. `start_row == -1`
  means "auto": empty sheet → `1`; only row 1 has data → `2`; otherwise
  `max_row + 1`.
- `match_config` (object, optional): enables key-column match-back.
  The row's target row is determined by looking up a value in an existing
  sheet row, rather than incrementing from `start_row`.
  - `mode`: only `"col_equal"` is supported in v0.1.
  - `col`: upper-case column letter (`"A"`, `"B"`, …, `"XFD"`); case is
    ignored on input.
  - Setting `start_row != 1` alongside `match_config` is an
    `E_STARTROW_USELESS` validation error.
- `data_list` (array of arrays of cell items): each inner array is one
  row. Each cell item is one of:

  - `{"col": "A", "value": ... }` — a value cell. `value` may be
    `str`, `int`, `float`, `bool`, `datetime`, `date`, `time`,
    `Decimal`, or `null` (null = leave cell empty). `datetime` /
    `Decimal`: openpyxl writes them but `number_format` is the caller's
    responsibility — `excel_spec` does not format them on your behalf.
  - `{"col": "B", "type": "image", "path": "pic.png", ...}` — an
    image cell.
    - `path` (string, required): resolved relative to `base_dir`
      (which defaults to the spec file's directory when you pass a
      file path, or CWD when you pass a dict).
    - `anchor_kind` (`"twoCell" | "oneCell"`, default `"twoCell"`):
      `twoCell` (default) creates a `TwoCellAnchor(editAs="twoCell")`
      so the picture moves AND resizes with the cells it overlays.
      `oneCell` falls back to openpyxl's default one-anchor
      fixed-size behaviour.
    - For `twoCell`: `cols` (int, default `1`) and `rows` (int,
      default `1`) span the picture over a rectangular range starting
      at the cell column/row.
    - For `oneCell`: use `w` and `h` (inches) to set the picture
      size; `cols` / `rows` are ignored and trigger a
      `W_ONECELL_IGNORES_SPAN` warning.

### Side-by-side: openpyxl vs excel_spec

Same "put a blue 1×1 picture in B2 that resizes with the cell":

```python
# openpyxl (the long way)
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import TwoCellAnchor

wb = Workbook(); ws = wb.active
img = Image("logo.png")
anchor = TwoCellAnchor()
anchor._from.col = 1; anchor._from.row = 1
anchor.to.col = 2;   anchor.to.row = 2
anchor.editAs = "twoCell"
img.anchor = anchor
ws.add_image(img)
wb.save("out.xlsx")
```

```python
# excel_spec
from excel_spec import to_excel
to_excel({
    "output_excel_path": "out.xlsx",
    "sheets": [{"sheet_name": "Sheet",
      "data_list": [[{"col": "B", "type": "image",
                       "path": "logo.png",
                       "anchor_kind": "twoCell", "cols": 1, "rows": 1}]]}],
})
```

---

## Public API

```python
def to_excel(spec, output=None, base_dir=None, verbose=False) -> Path:
    """Render spec to .xlsx. Single wb.save() at the end; raises
    SpecValidationError on spec errors, MatchNotFoundError on data
    lookup misses, TemplateError on template copy failures."""

def build_workbook(spec, base_dir=None) -> Workbook:
    """Build the workbook without saving. Mainly for testing / embedding.
    The returned openpyxl.Workbook's API is not part of excel_spec's
    stability contract (it follows openpyxl)."""

def validate(spec, base_dir=None, *, strict=False) -> list[Issue]:
    """Lint a spec. Returns Issue objects. v0.1 emits warnings for
    lenient cases and errors for hard failures. `strict=True` is a
    v0.2 hook (currently a no-op)."""

@dataclass(frozen=True, slots=True)
class Issue:
    severity: "error" | "warning"
    code: str
    path: str
    message: str
```

### Error classes

- `SpecParseError` — file missing or JSON malformed.
- `SpecValidationError` — `validate()` returned >=1 error-severity `Issue`.
- `MatchNotFoundError` — runtime data miss: `match_config` published but
  the target sheet has no row whose key column equals the expected value.
- `TemplateError` — `excel_template_path` resolved at validation time
  but copy failed at runtime (race, permissions, etc.).

---

## Performance notes

- `build_workbook` does no `wb.save()` work; `to_excel` saves exactly
  once. Ad-hoc openpyxl scripts that save inside a write loop have
  quadratic cost on big sheets because each save re-serialises the
  entire workbook. `excel_spec` matches the cost of `wb.save()` once.
- No benchmark numbers are claimed here.
  `_local_dev/bench/` ships an empty placeholder for users to drop their
  own real-world sample.
- Even with single-save, openpyxl calls `PIL.open()` twice per image at
  save time (once for size metadata at construction, once for byte
  payload at save). v0.2 will cache the byte payload to avoid this;
  v0.1 does not.

---

## Known limitations

- `write_only` streaming mode is incompatible with pictures + merged
  cells; v0.1 does not use it. Do not expect streaming output.
- `datetime` / `Decimal` are passed through; caller controls
  `number_format`.
- `bool` is written as `TRUE`/`FALSE` (openpyxl default), not `1`/`0`.
- No styles, fonts, borders, fills, charts, or conditional formatting.
  Use openpyxl or xlsxwriter for those — see the decision table above.
- No CLI. Use `python -c "from excel_spec import to_excel; to_excel('spec.json')"`
  or build your own wrapper.

---

## Differences from the seed `excel_handler.py`

`excel_spec` is intentionally tighter than the seed in two spots:

- Row-level errors are written to the cell **after the row's last data
  cell** (`max_column + 1`), never to column A. The seed's behaviour of
  overwriting column A with the error string was a footgun.
- When building a brand-new workbook (no template), `excel_spec` removes
  openpyxl's default `Sheet`. The seed accidentally left a phantom empty
  `Sheet1` behind because `ExcelHandler.__init__` defaulted
  `sheet_name="Sheet1"` and `JsonExcelWriter.write_excel_from_json`
  never closed it. `excel_spec` produces only the sheets named in your
  spec. If your first sheet's name is literally `"Sheet"`, it is
  **reused** in place (not deleted then re-created).

Both differences are deliberate; if you need to migrate, expect these
two behaviour changes.

---

## Roadmap

- v0.2: `read_workbook(path)` → spec (round-trip + diff); PIL.open
  byte cache; `strict=True` validation mode; more `match_config.modes`.
- v0.3+: additional `match_config` modes; potential CLI; **Agent Skill
  package** (`SKILL.md` + bundled spec schema) — teach agents how to
  emit excel_spec specs without standing up an MCP server. excel_spec is
  an unauthenticated, stateless transformation library, which is precisely
  the shape Agent Skills (per Anthropic's 2026 framing) are designed for;
  standing up an MCP server for it would be the kind of "Markdown in a
  trench coat" anti-pattern multiple 2026 articles warn against.

---

## License

MIT. See `LICENSE`.