Source code for pain001_loader_xlsx._normalise

# SPDX-License-Identifier: Apache-2.0 OR MIT
# Copyright (C) 2023-2026 Sebastien Rousseau. All rights reserved.

"""Turn Excel cell values into the strings a CSV would have carried.

pain001's CSV loader is :class:`csv.DictReader`, so every value it
produces is a ``str``. The generated XML is rendered from those
strings. For an ``.xlsx`` file to produce *byte-identical* XML to the
equivalent CSV — the acceptance criterion for this package — the values
this loader emits have to be the same strings.

That is harder than it sounds, because Excel does not store what it
displays. A cell showing ``100.00`` is the float ``100.0``; a cell
showing ``0023456`` may be the int ``23456`` with the leading zeros
gone for good. Recovering the displayed text needs the cell's
``number_format``, and where the information is genuinely destroyed
this module's job is to say so rather than guess.

The rules, in order:

* ``str`` — used as Excel displays it: OOXML's ``_xHHHH_`` escapes
  (ECMA-376 Part 1, ST_Xstring) are decoded, so a cell holding a
  carriage return yields one rather than the six characters
  ``_x000D_``. This is the good case, and the reason the documentation
  tells users to format columns as Text.
* ``None`` — the empty string, matching how ``DictReader`` reports an
  empty field.
* ``bool`` — ``"TRUE"`` / ``"FALSE"``, which is what Excel shows.
* ``int`` / ``float`` — rendered through the cell's ``number_format``
  when that format pins the number of decimals (``0.00`` -> two), and
  otherwise with the shortest representation that round-trips.
* ``datetime`` / ``date`` / ``time`` — rejected. See
  :mod:`pain001_loader_xlsx.errors`.

No quote is added in front of a value starting with ``=``, ``+``,
``-`` or ``@``. pain001 writes ISO 20022 XML, which nothing evaluates,
and since 0.0.74 its CSV loader keeps such values raw too (pain001
ADR-0008), so the same sheet renders the same XML either way.
"""

from __future__ import annotations

import re
from decimal import ROUND_HALF_UP, Decimal, InvalidOperation
from typing import Any

#: Matches the decimal places a numeric ``number_format`` pins, e.g.
#: ``0.00``, ``#,##0.000``, ``0.00;[Red]-0.00``. Only the first section
#: matters: Excel's positive-form section governs an ordinary value.
_DECIMALS = re.compile(r"[0#]\.([0#]+)")

#: Formats that carry no decimal information worth honouring.
_UNFORMATTED = frozenset({"General", "@", ""})

#: ECMA-376 Part 1, ST_Xstring: ``_xHHHH_`` stands for the UTF-16 code
#: unit ``HHHH``. A literal ``_xHHHH_`` is stored with its underscore
#: escaped (``_x005F_xHHHH_``), which a single left-to-right pass decodes.
_ESCAPE = re.compile(r"_x([0-9A-Fa-f]{4})_")

#: What XML 1.0's ``Char`` production excludes: no payment message can
#: carry these, so a cell holding one is refused rather than passed on.
_NOT_XML = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f\ud800-\udfff\ufffe\uffff]")


[docs] def decimals_in(number_format: str | None) -> int | None: """Return the decimal places pinned by ``number_format``, if any. Args: number_format: The cell's Excel number format, as openpyxl reports it. ``None`` for cells that carry none. Returns: The count of digits after the decimal point that the format pins, or ``None`` when the format leaves it open (``General``, text, or a format with no decimal section). Example: >>> decimals_in("0.00") 2 >>> decimals_in("#,##0.000") 3 >>> decimals_in("General") is None True """ if not number_format or number_format in _UNFORMATTED: return None first_section = number_format.split(";", 1)[0] match = _DECIMALS.search(first_section) if match is None: return None return len(match.group(1))
[docs] def decode_escapes(text: str) -> str: """Decode OOXML ``_xHHHH_`` escapes into the characters Excel shows. openpyxl returns shared and inline strings with the escapes intact. An escaped surrogate pair decodes to the one character it encodes; a lone surrogate stays, for :func:`not_xml` to report. Args: text: A string cell's value as openpyxl read it. Returns: The text with every escape decoded. Example: >>> decode_escapes("Line1_x000D_\\nLine2") 'Line1\\r\\nLine2' >>> decode_escapes("_x005F_x0041_") '_x0041_' """ if "_x" not in text: return text decoded = _ESCAPE.sub(lambda match: chr(int(match[1], 16)), text) return decoded.encode("utf-16-le", "surrogatepass").decode( "utf-16-le", "surrogatepass" )
[docs] def not_xml(text: str) -> str | None: """Return the first character in ``text`` that XML 1.0 cannot carry. Args: text: Decoded cell text. Returns: The offending character, or ``None`` when every character is allowed. Example: >>> not_xml("ACME\\x07Ltd") '\\x07' >>> not_xml("Line1\\r\\nLine2") is None True """ match = _NOT_XML.search(text) return None if match is None else match[0]
def _number_to_text(value: int | float, number_format: str | None) -> str: """Render a numeric cell the way Excel displays it. Args: value: The numeric cell value openpyxl produced. number_format: The cell's Excel number format. Returns: The number as text. """ places = decimals_in(number_format) if places is not None: try: quantum = Decimal(1).scaleb(-places) return str( Decimal(str(value)).quantize(quantum, rounding=ROUND_HALF_UP) ) except (InvalidOperation, ValueError): # pragma: no cover - guard return str(value) if isinstance(value, int): return str(value) # An unformatted float. `100.0` came from a cell showing `100`, so # drop the artefact zero; anything else keeps its shortest # round-tripping form, which is what `str` already gives. if value.is_integer(): return str(int(value)) return str(value)
[docs] def to_text(value: Any, number_format: str | None) -> str: """Convert one cell value to the string a CSV would have held. Args: value: The cell value openpyxl produced. number_format: The cell's Excel number format, used to recover the displayed precision of numeric cells. Returns: The cell as text. Example: >>> to_text("DE89370400440532013000", "@") 'DE89370400440532013000' >>> to_text(100.0, "0.00") '100.00' >>> to_text(None, None) '' >>> to_text("+44 20 7946 0000", None) '+44 20 7946 0000' """ if value is None: return "" if isinstance(value, str): return decode_escapes(value) if isinstance(value, bool): return "TRUE" if value else "FALSE" if isinstance(value, (int, float)): return _number_to_text(value, number_format) return str(value)