# SPDX-License-Identifier: Apache-2.0 OR MIT
# Copyright (C) 2023-2026 Sebastien Rousseau. All rights reserved.
"""Excel (.xlsx / .xlsm) loader plugin for pain001.
Reads payment rows from the first sheet of an OOXML spreadsheet,
treating row 1 as the header and rows 2..N as records. Cells are
read with ``data_only=True`` so formulas evaluate to their cached
last-saved value (the same value the user sees in Excel).
Implements the :class:`pain001.plugins.AbstractLoader` Protocol
without subclassing - structural typing means this plugin only
needs the four declared attributes (``meta``, ``extensions``,
``load``, ``load_streaming``) to satisfy the contract.
The loader is registered with pain001 via the ``pain001.loaders``
entry-point group in :file:`pyproject.toml`; users get .xlsx
dispatch automatically after ``pip install pain001-loader-xlsx``.
IBAN handling
-------------
Excel's General cell format silently strips leading zeros from
numeric-looking strings - a known data-corruption mode in SAP /
Oracle / Workday exports. To protect against this the loader
refuses any row whose ``debtor_account_IBAN`` /
``creditor_account_IBAN`` / ``charge_account_IBAN`` cell is typed
as a number (Excel ``General``) and asks the user to re-type the
column as ``Text`` first. Caught early, the warning saves the user
from wiring an IBAN with a missing digit to a bank.
"""
from __future__ import annotations
import datetime as _dt
from collections.abc import Iterable
from importlib import metadata
from typing import Any
import openpyxl
from pain001.plugins import (
PAIN001_API_VERSION,
LoaderResult,
PluginMeta,
)
from pain001_loader_xlsx._normalise import decode_escapes, not_xml, to_text
# Columns whose values are bank identifiers and must never be read
# from an Excel "General" cell (Excel strips leading zeros from
# numeric-looking strings). Lowercased for case-insensitive matching
# against the header row.
_IBAN_HEADERS = frozenset(
{
"debtor_account_iban",
"creditor_account_iban",
"charge_account_iban",
}
)
def _plugin_version() -> str:
"""Return the package version (or ``"0.0.0"`` for editable dev installs).
Returns:
SemVer string from importlib.metadata when the package is
installed; ``"0.0.0"`` when running from a checkout that hasn't
been pip-installed yet.
"""
try:
return metadata.version("pain001-loader-xlsx")
except metadata.PackageNotFoundError: # pragma: no cover - dev install
return "0.0.0"
#: Temporal types openpyxl produces from date/time-formatted cells.
_TEMPORAL_TYPES = (_dt.datetime, _dt.date, _dt.time, _dt.timedelta)
[docs]
class XlsxLoader:
"""Read flat-record payment data from an Excel ``.xlsx`` / ``.xlsm`` file.
Satisfies the structural :class:`pain001.plugins.AbstractLoader`
Protocol. Construction takes no arguments; the registry
instantiates it once per process at first lookup.
Sheet selection: the first sheet wins. Cross-sheet payment
batches are out of scope; users with that requirement should
consolidate to a single sheet (or open an issue describing the
layout).
"""
meta = PluginMeta(
name="xlsx",
version=_plugin_version(),
description=(
"Read flat-record payment data from Excel .xlsx / .xlsm files."
),
api_version=PAIN001_API_VERSION,
)
extensions = (".xlsx", ".xlsm")
[docs]
def load(self, path: str) -> LoaderResult:
"""Read every data row from ``path`` and return them all at once.
Args:
path: Filesystem path to the spreadsheet.
Returns:
A :class:`LoaderResult` carrying one dict per data row
(header-cell-value -> data-cell-value) and the original
path as source hint.
Raises:
ValueError: When the workbook has no sheets, no header
row, or when an IBAN column has been typed as a
numeric cell.
"""
rows = list(self._iter_rows(path))
return LoaderResult(rows=rows, source_hint=path)
[docs]
def load_streaming(
self, path: str, chunk_size: int
) -> Iterable[LoaderResult]:
"""Yield ``chunk_size``-row :class:`LoaderResult` chunks.
Args:
path: Filesystem path to the spreadsheet.
chunk_size: Maximum rows per yielded result.
Returns:
An iterable of :class:`LoaderResult`; the final chunk may
be smaller than ``chunk_size``.
"""
buf: list[dict[str, Any]] = []
for record in self._iter_rows(path):
buf.append(record)
if len(buf) >= chunk_size:
yield LoaderResult(rows=buf, source_hint=path)
buf = []
if buf:
yield LoaderResult(rows=buf, source_hint=path)
# ------------------------------------------------------------------
# Internal helpers
# ------------------------------------------------------------------
def _iter_rows(self, path: str) -> Iterable[dict[str, Any]]:
"""Stream rows from the first sheet, refusing IBAN-as-number cells.
Args:
path: Filesystem path to the spreadsheet.
Yields:
One dict per data row, keyed by the header-row values.
Raises:
ValueError: When the workbook has no sheets, no header
row, or when an IBAN column is numeric.
"""
workbook = openpyxl.load_workbook(path, read_only=True, data_only=True)
try:
yield from self._iter_workbook(workbook, path)
finally:
workbook.close()
def _iter_workbook(
self, workbook: Any, path: str
) -> Iterable[dict[str, Any]]:
"""Yield validated rows while the caller owns workbook cleanup."""
if not workbook.sheetnames:
raise ValueError(f"workbook {path!r} contains no sheets")
worksheet = workbook[workbook.sheetnames[0]]
rows_iter = worksheet.iter_rows()
try:
header_row = next(rows_iter)
except StopIteration as exc:
raise ValueError(
f"workbook {path!r} sheet 1 is empty (no header row)"
) from exc
headers = [
(cell.value if cell.value is not None else "")
for cell in header_row
]
iban_columns = {
idx
for idx, name in enumerate(headers)
if str(name).strip().lower() in _IBAN_HEADERS
}
for row_number, row in enumerate(rows_iter, start=2):
self._guard_iban_cells(row, iban_columns, headers, path)
self._guard_temporal_cells(row, headers, path, row_number)
self._guard_text_cells(row, headers, path, row_number)
yield dict(
zip(
headers,
(to_text(cell.value, cell.number_format) for cell in row),
strict=False,
)
)
def _guard_temporal_cells(
self,
row: tuple[Any, ...],
headers: list[Any],
path: str,
row_number: int,
) -> None:
"""Raise when a cell holds an Excel date, time, or duration.
Excel stores dates as offsets against a workbook epoch, and the
1900 and 1904 systems disagree by four years. Rendering one back
to ISO-8601 is a guess about a setting this loader cannot see,
and a payment execution date wrong by four years is worse than
a refusal.
Without this, a ``datetime`` object flows through untouched and
is stringified downstream as ``2026-03-01 00:00:00`` — a value
no ISO 20022 date field accepts.
Args:
row: The current data row's cells.
headers: The header-row values (for the error message).
path: The source file path (for the error message).
row_number: One-based worksheet row, matching Excel's own
numbering so the user can go straight to the cell.
Raises:
ValueError: When any cell carries a temporal type.
"""
for idx, cell in enumerate(row):
if isinstance(cell.value, _TEMPORAL_TYPES):
column_name = headers[idx] if idx < len(headers) else idx
raise ValueError(
f"workbook {path!r} column {column_name!r} row "
f"{row_number} is an Excel "
f"{type(cell.value).__name__} cell. Excel stores dates "
"against a workbook epoch (the 1900 and 1904 systems "
"differ by four years), which this loader will not "
"guess at. Re-type the column as 'Text' (in Excel: "
"select the column, Format Cells -> Number -> Text) "
"and write dates as ISO-8601 (YYYY-MM-DD)."
)
def _guard_text_cells(
self,
row: tuple[Any, ...],
headers: list[Any],
path: str,
row_number: int,
) -> None:
"""Raise when a text cell holds a character XML cannot carry.
OOXML stores control characters as ``_xHHHH_`` escapes, which
:func:`~pain001_loader_xlsx._normalise.to_text` decodes. Most
C0 controls, lone surrogates and U+FFFE/U+FFFF are outside XML
1.0's character set, so no pain.001 message can include them;
they usually arrive pasted in invisibly. Passing the escape text
on instead would put ``_x0007_`` into a payment field.
Args:
row: The current data row's cells.
headers: The header-row values (for the error message).
path: The source file path (for the error message).
row_number: One-based worksheet row.
Raises:
ValueError: When any text cell holds such a character.
"""
for idx, cell in enumerate(row):
if not isinstance(cell.value, str):
continue
bad = not_xml(decode_escapes(cell.value))
if bad is not None:
column_name = headers[idx] if idx < len(headers) else idx
raise ValueError(
f"workbook {path!r} column {column_name!r} row "
f"{row_number} holds the character U+{ord(bad):04X}, "
"which XML cannot carry, so no payment message can "
"include it. It is usually pasted in invisibly: retype "
"the cell and re-save."
)
def _guard_iban_cells(
self,
row: tuple[Any, ...],
iban_columns: set[int],
headers: list[Any],
path: str,
) -> None:
"""Raise when an IBAN column cell carries a numeric type.
Args:
row: The current data row's cells (openpyxl ``Cell``
instances in read-only mode).
iban_columns: Indices of header columns whose values are
IBANs.
headers: The header-row values (for the error message).
path: The source file path (for the error message).
Raises:
ValueError: When any IBAN column cell is numeric.
"""
for idx in iban_columns:
if idx >= len(row): # pragma: no cover - openpyxl pads rows
# Defensive: openpyxl's read_only mode auto-pads rows
# to the header width in practice, but the docs don't
# guarantee it. The guard protects against a hand-
# rolled iterator that strips trailing empty cells.
continue
value = row[idx].value
if isinstance(value, int | float):
column_name = headers[idx]
raise ValueError(
f"workbook {path!r} column {column_name!r} contains a "
f"numeric value ({value!r}) where an IBAN string is "
"expected. Excel's 'General' cell format silently "
"strips leading zeros from IBANs; re-type the column "
"as 'Text' (in Excel: select the column, "
"Format Cells -> Number -> Text) and re-export."
)