"""Cell values, formatting, and display helpers for Numbers documents."""
# ruff: noqa: SLF001
from __future__ import annotations
import logging
import math
import re
from dataclasses import asdict, dataclass, field, fields
from datetime import datetime, timedelta
from enum import IntEnum
from fractions import Fraction
from hashlib import sha1
from os.path import basename
from struct import pack, unpack
from typing import TYPE_CHECKING, Any, NamedTuple, cast
from warnings import warn
from sigfig import round as sigfig
from typing_extensions import Self
if TYPE_CHECKING:
from collections.abc import Sequence
from numbers_parser.model import NumbersModel
from numbers_parser.constants import (
CHECKBOX_FALSE_VALUE,
CHECKBOX_TRUE_VALUE,
CURRENCY_CELL_TYPE,
CUSTOM_TEXT_PLACEHOLDER,
DATETIME_FIELD_MAP,
DECIMAL128_BIAS,
DECIMAL_PLACES_AUTO,
DEFAULT_ALIGNMENT,
DEFAULT_BORDER_COLOR,
DEFAULT_BORDER_STYLE,
DEFAULT_BORDER_WIDTH,
DEFAULT_DATETIME_FORMAT,
DEFAULT_FONT,
DEFAULT_FONT_SIZE,
DEFAULT_TEXT_INSET,
DEFAULT_TEXT_WRAP,
EMPTY_STORAGE_BUFFER,
EPOCH,
FONT_FAMILY_DEFAULT,
FONT_TUPLE_MAP,
MAX_BASE,
MAX_SIGNIFICANT_DIGITS,
SECONDS_IN_DAY,
SECONDS_IN_HOUR,
SECONDS_IN_WEEK,
STAR_RATING_VALUE,
CellPadding,
CellType,
ControlFormattingType,
CustomFormattingType,
DurationStyle,
DurationUnits,
FormattingType,
FormatType,
FractionAccuracy,
NegativeNumberStyle,
NumberFormatConditionType,
PaddingType,
)
from numbers_parser.currencies import CURRENCIES, CURRENCY_SYMBOLS
from numbers_parser.exceptions import UnsupportedError, UnsupportedWarning
from numbers_parser.generated import TSKArchives_pb2 as TSKArchives
from numbers_parser.generated import TSPMessages_pb2 as TSPMessages
from numbers_parser.generated import TSTArchives_pb2 as TSTArchives
from numbers_parser.generated.TSWPArchives_pb2 import (
ParagraphStylePropertiesArchive as ParagraphStyle,
)
from numbers_parser.numbers_cache import Cacheable, cache
from numbers_parser.numbers_uuid import NumbersUUID
from numbers_parser.xrefs import xl_range
logger = logging.getLogger(__package__) # pylint: disable=invalid-name
debug = logger.debug
[docs]
class BackgroundImage:
"""
A named document style that can be applied to cells.
.. code-block:: python
fh = open("cat.jpg", mode="rb")
image_data = fh.read()
cat_bg = doc.add_style(
name="Cat",
bg_image=BackgroundImage(image_data, "cat.jpg")
)
table.write(0, 0, "❤️ cats", style=cat_bg)
Currently only standard image files and not 'advanced' image fills are
supported. Tiling and scaling is not reported back and cannot be changed
when saving new cells.
Parameters
----------
data: bytes
Raw image data for a cell background image.
filename: str
Path to the image file. Only the basename is stored.
"""
def __init__(self, data: bytes, filename: str) -> None:
"""Initialize the instance state."""
self._data = data
self._filename = basename(filename)
@property
def data(self) -> bytes:
"""bytes: The background image as bytes for a cell."""
return self._data
@property
def filename(self) -> str:
"""str: The image filename for a cell."""
return self._filename
class HorizontalJustification(IntEnum):
"""Horizontal alignment values accepted by :class:`Alignment`."""
LEFT = ParagraphStyle.TextAlignmentType.TATvalue0
RIGHT = ParagraphStyle.TextAlignmentType.TATvalue1
CENTER = ParagraphStyle.TextAlignmentType.TATvalue2
JUSTIFIED = ParagraphStyle.TextAlignmentType.TATvalue3
AUTO = ParagraphStyle.TextAlignmentType.TATvalue4
class VerticalJustification(IntEnum):
"""Vertical alignment values accepted by :class:`Alignment`."""
TOP = ParagraphStyle.DeprecatedParagraphBorderType.PBTvalue0
MIDDLE = ParagraphStyle.DeprecatedParagraphBorderType.PBTvalue1
BOTTOM = ParagraphStyle.DeprecatedParagraphBorderType.PBTvalue2
HORIZONTAL_MAP = {
"left": HorizontalJustification.LEFT,
"right": HorizontalJustification.RIGHT,
"center": HorizontalJustification.CENTER,
"justified": HorizontalJustification.JUSTIFIED,
"auto": HorizontalJustification.AUTO,
}
VERTICAL_MAP = {
"top": VerticalJustification.TOP,
"middle": VerticalJustification.MIDDLE,
"bottom": VerticalJustification.BOTTOM,
}
class _Alignment(NamedTuple):
"""Class for internal alignment type."""
horizontal: HorizontalJustification
vertical: VerticalJustification
class Alignment(_Alignment):
"""
Pair of horizontal and vertical cell alignment values.
Values may be supplied as the corresponding enum members or as the strings
accepted by Numbers, such as ``"center"`` and ``"middle"``.
"""
def __new__(
cls,
horizontal: str | HorizontalJustification = DEFAULT_ALIGNMENT[0],
vertical: str | VerticalJustification = DEFAULT_ALIGNMENT[1],
) -> Self:
"""Create a cell-related value with the requested attributes."""
if isinstance(horizontal, str):
horizontal = horizontal.lower()
if horizontal not in HORIZONTAL_MAP:
msg = "invalid horizontal alignment"
raise TypeError(msg)
horizontal = HORIZONTAL_MAP[horizontal]
if isinstance(vertical, str):
vertical = vertical.lower()
if vertical not in VERTICAL_MAP:
msg = "invalid vertical alignment"
raise TypeError(msg)
vertical = VERTICAL_MAP[vertical]
return tuple.__new__(cls, (horizontal, vertical))
def __repr__(self) -> str:
"""Return a developer-friendly representation."""
return f"[{self.horizontal.name.lower()},{self.vertical.name.lower()}]"
def __str__(self) -> str:
"""Return the string representation."""
return repr(self)
DEFAULT_ALIGNMENT_CLASS = Alignment(*DEFAULT_ALIGNMENT)
class RGB(NamedTuple):
"""A color represented by red, green, and blue integer components."""
r: int
g: int
b: int
def default_color() -> RGB:
"""Return the default color used for an unspecified value."""
return RGB(0, 0, 0)
[docs]
@dataclass
class Style:
"""
A named document style that can be applied to cells.
Parameters
----------
alignment: Alignment, optional, default: Alignment("auto", "top")
Horizontal and vertical alignment of the cell
bg_color: RGB | List[RGB], optional, default: None
Background color or list of colors for gradients
bold: bool, optional, default: False
``True`` if the cell font is bold
font_color: RGB, optional, default: RGB(0, 0, 0)
Font color
font_size: float, optional, default: DEFAULT_FONT_SIZE
Font size in points
font_name: str | tuple[str, str], optional, default: DEFAULT_FONT
Font name or a tuple of font family and style
italic: bool, optional, default: False
``True`` if the cell font is italic
name: str, optional
Style name
underline: bool, optional, default: False
``True`` if the cell font is underlined
strikethrough: bool, optional, default: False
``True`` if the cell font uses strikethrough
first_indent: float, optional, default: 0.0
First-line indent in points
left_indent: float, optional, default: 0.0
Left indent in points
right_indent: float, optional, default: 0.0
Right indent in points
text_inset: float, optional, default: DEFAULT_TEXT_INSET
Text inset in points
text_wrap: bool, optional, default: True
``True`` if text wrapping is enabled
Raises
------
TypeError:
If arguments do not match the specified type or for objects have invalid arguments
IndexError:
If an image filename already exists in document
ValueError:
If a font name is not known.
"""
alignment: Alignment = DEFAULT_ALIGNMENT_CLASS # : horizontal and vertical alignment
bg_image: BackgroundImage | None = None
bg_color: RGB | list[RGB] | None = None
font_color: RGB = field(default_factory=default_color)
font_size: float = DEFAULT_FONT_SIZE
font_name: str = DEFAULT_FONT
bold: bool = False
italic: bool = False
strikethrough: bool = False
underline: bool = False
first_indent: float = 0
left_indent: float = 0
right_indent: float = 0
text_inset: float = DEFAULT_TEXT_INSET
text_wrap: bool = DEFAULT_TEXT_WRAP
name: str | None = None
_font_details: dict[str, Any] | None = None
_text_style_obj_id: int | None = None
_cell_style_obj_id: int | None = None
_update_cell_style: bool = False
_update_text_style: bool = False
@staticmethod
def _text_attrs() -> list[str]:
"""Return the text-style attributes stored in the archive."""
return [
"alignment",
"bold",
"first_indent",
"font_color",
"font_name",
"font_size",
"italic",
"left_indent",
"name",
"right_indent",
"strikethrough",
"text_inset",
"underline",
]
@staticmethod
def _cell_attrs() -> list[str]:
"""Return the cell-style attributes stored in the archive."""
return [
"alignment",
"bg_color",
"bg_image",
"first_indent",
"left_indent",
"right_indent",
"text_inset",
"text_wrap",
]
[docs]
@classmethod
def from_storage(cls, cell: Cell, model: NumbersModel) -> Style:
"""Create a Style from the cell archive and model."""
bg_image = None
if cell._image_data is not None:
bg_image = BackgroundImage(*cell._image_data)
return Style(
alignment=model.cell_alignment(cell),
bg_image=bg_image,
bg_color=model.cell_bg_color(cell),
font_color=model.cell_font_color(cell),
font_size=model.cell_font_size(cell),
font_name=model.cell_font_family(cell),
bold=model.cell_is_bold(cell),
italic=model.cell_is_italic(cell),
strikethrough=model.cell_is_strikethrough(cell),
underline=model.cell_is_underline(cell),
name=model.cell_style_name(cell),
first_indent=model.cell_first_indent(cell),
left_indent=model.cell_left_indent(cell),
right_indent=model.cell_right_indent(cell),
text_inset=model.cell_text_inset(cell),
text_wrap=model.cell_text_wrap(cell),
_font_details=model.cell_font_details(cell),
_text_style_obj_id=model.text_style_object_id(cell),
_cell_style_obj_id=model.cell_style_object_id(cell),
)
def __post_init__(self) -> None:
"""Validate and initialize the instance state."""
self.bg_color = rgb_color(self.bg_color)
self.font_color = _single_rgb(self.font_color)
if not isinstance(self.font_size, (float, int)):
msg = "size must be a float number of points"
raise TypeError(msg)
if not isinstance(self.font_name, (str, tuple)):
msg = "font name must be a string or name/style tuple"
raise TypeError(msg)
if isinstance(self.font_name, str) and self.font_name in FONT_FAMILY_DEFAULT:
self._font_details = FONT_FAMILY_DEFAULT[self.font_name]
elif isinstance(self.font_name, tuple) and self.font_name in FONT_TUPLE_MAP:
self._font_details = FONT_TUPLE_MAP[self.font_name]
else:
msg = f"font '{self.font_name}' does not exist"
raise ValueError(msg)
for attr in ["bold", "italic", "underline", "strikethrough"]:
if not isinstance(getattr(self, attr), bool):
msg = f"{attr} argument must be boolean"
raise TypeError(msg)
def __setattr__(self, name: str, value: object) -> None:
"""
Detect cell-style changes and flag styles for updates.
Mark styles for writing when the document is saved.
"""
if name == "bg_color":
value = rgb_color(value)
elif name == "font_color":
value = _single_rgb(value)
if name == "alignment":
value = alignment(value)
if name in Style._text_attrs():
self.__dict__["_update_text_style"] = True
if name in Style._cell_attrs():
self.__dict__["_update_cell_style"] = True
if name not in ["_update_text_style", "_update_cell_style"]:
self.__dict__[name] = value
def _single_rgb(color: object) -> RGB:
"""Validate and return a single RGB color."""
if isinstance(color, RGB):
return color
if isinstance(color, tuple) and len(color) == 3 and all(isinstance(x, int) for x in color):
return RGB(color[0], color[1], color[2])
msg = "RGB color must be an RGB or a tuple of 3 integers"
raise TypeError(msg)
def rgb_color(color: object) -> RGB | list[RGB] | None:
"""Raise a TypeError if a color is not a valid RGB value."""
if color is None:
return None
if isinstance(color, list):
return [_single_rgb(c) for c in color]
return _single_rgb(color)
def alignment(value: object) -> Alignment:
"""Raise a TypeError if a alignment is not a valid."""
if value is None:
return Alignment()
if isinstance(value, Alignment):
return value
if isinstance(value, tuple):
if not (len(value) == 2 and all(isinstance(x, (int, str)) for x in value)):
msg = "Alignment must be an Alignment or a tuple of 2 integers/strings"
raise TypeError(msg)
return Alignment(*value)
msg = "Alignment must be an Alignment or a tuple of 2 integers/strings"
raise TypeError(msg)
BORDER_STYLE_MAP = {"solid": 0, "dashes": 1, "dots": 2, "none": 3}
class BorderType(IntEnum):
"""Enumerate the supported cell-border line styles."""
SOLID = BORDER_STYLE_MAP["solid"]
DASHES = BORDER_STYLE_MAP["dashes"]
DOTS = BORDER_STYLE_MAP["dots"]
NONE = BORDER_STYLE_MAP["none"]
[docs]
@dataclass(frozen=True, init=False)
class Border: # noqa: PLW1641
"""
Create a cell border for the :py:meth:`~numbers_parser.Table.set_cell_border` method.
.. code-block:: python
border_style = Border(8.0, RGB(29, 177, 0), "solid")
table.set_cell_border("B6", "left", border_style, 3)
table.set_cell_border(6, 1, "right", border_style)
Parameters
----------
width: float, optional, default: 0.35
Line width in points.
color: RGB, optional, default: RGB(0, 0, 0)
The line color for the border if present
style: BorderType, optional, default: ``None``
The type of border to create or ``None`` if there is no border defined. Valid
border types are:
* ``"solid"``: a solid line
* ``"dashes"``: a dashed line
* ``"dots"``: a dotted line
Raises
------
TypeError:
If the width is not a float, or the border type is invalid.
"""
width: float
color: RGB
style: BorderType
def __init__(
self,
width: float = DEFAULT_BORDER_WIDTH,
color: RGB | tuple[int, int, int] | None = None,
style: BorderType | str | None = None,
) -> None:
"""Validate and initialize the instance state."""
if not isinstance(width, float):
msg = "width must be a float number of points"
raise TypeError(msg)
object.__setattr__(self, "width", width)
if color is None:
object.__setattr__(self, "color", RGB(*DEFAULT_BORDER_COLOR))
else:
object.__setattr__(self, "color", _single_rgb(color))
current_style = style
if current_style is None:
# Default fallback
current_style = BorderType(BORDER_STYLE_MAP[DEFAULT_BORDER_STYLE])
elif isinstance(current_style, str):
# String conversion (e.g., "dashes" -> BorderType.DASHES)
current_style = current_style.lower()
if current_style not in BORDER_STYLE_MAP:
msg = f"invalid border style: '{current_style}'"
raise TypeError(msg)
current_style = BorderType(BORDER_STYLE_MAP[current_style])
elif not isinstance(current_style, BorderType):
try:
current_style = BorderType(current_style)
except ValueError as e:
msg = f"invalid border style value: {current_style}"
raise TypeError(msg) from e
object.__setattr__(self, "style", current_style)
def __repr__(self) -> str:
"""Return a developer-friendly representation."""
return str(self)
def __str__(self) -> str:
"""Return the string representation."""
style_name = BorderType(self.style).name.lower()
return f"Border(width={self.width}, color={self.color}, style={style_name})"
def __eq__(self, other: object) -> bool:
"""Compare this instance with another value."""
if not isinstance(other, Border):
return NotImplemented
return all(
[self.width == other.width, self.color == other.color, self.style == other.style],
)
class CellBorder:
"""
The four visible border segments associated with a cell.
A segment is ``None`` when it is unset or hidden by a merged-cell edge.
Border segments can be read directly; use :meth:`Table.set_cell_border`
to change them.
"""
def __init__(
self,
top_merged: bool = False,
right_merged: bool = False,
bottom_merged: bool = False,
left_merged: bool = False,
) -> None:
"""Initialize the instance state."""
self._top: Border | None = None
self._right: Border | None = None
self._bottom: Border | None = None
self._left: Border | None = None
self._top_merged = top_merged
self._right_merged = right_merged
self._bottom_merged = bottom_merged
self._left_merged = left_merged
@property
def top(self) -> Border | None:
"""Return or set the top border."""
if self._top_merged or self._top is None:
return None
return self._top
@top.setter
def top(self, value: Border | None) -> None:
"""Return or set the top border."""
self._top = value
@property
def right(self) -> Border | None:
"""Return or set the right border."""
if self._right_merged or self._right is None:
return None
return self._right
@right.setter
def right(self, value: Border | None) -> None:
"""Return or set the right border."""
self._right = value
@property
def bottom(self) -> Border | None:
"""Return or set the bottom border."""
if self._bottom_merged or self._bottom is None:
return None
return self._bottom
@bottom.setter
def bottom(self, value: Border | None) -> None:
"""Return or set the bottom border."""
self._bottom = value
@property
def left(self) -> Border | None:
"""Return or set the left border."""
if self._left_merged or self._left is None:
return None
return self._left
@left.setter
def left(self, value: Border | None) -> None:
"""Return or set the left border."""
self._left = value
class MergeReference:
"""Cell reference for cells eliminated by a merge."""
def __init__(self, row_start: int, col_start: int, row_end: int, col_end: int) -> None:
"""Initialize the instance state."""
self.rect = (row_start, col_start, row_end, col_end)
class MergeAnchor:
"""Cell reference for the merged cell."""
def __init__(self, size: tuple[int, int]) -> None:
"""Initialize the instance state."""
self.size = size
@dataclass
class Comment:
"""A comment associated with a cell."""
author: str
text: str
creation_date: datetime
replies: list[Comment] = field(default_factory=list)
@dataclass
class _CellStorageFlags:
"""Represent flags stored with a cell archive."""
_string_id: int | None = None
_rich_id: int | None = None
_cell_style_id: int | None = None
_text_style_id: int | None = None
_cond_style_id: int | None = None
_cond_rule_style_id: int | None = None
_formula_id: int | None = None
_control_id: int | None = None
_formula_error_id: int | None = None
_suggest_id: int | None = None
_num_format_id: int | None = None
_currency_format_id: int | None = None
_date_format_id: int | None = None
_duration_format_id: int | None = None
_text_format_id: int | None = None
_bool_format_id: int | None = None
_comment_id: int | None = None
_extra_bits: int = 0
def __repr__(self) -> str:
"""Return a developer-friendly representation."""
flag_fields = [f"{k[1:]}={v}" for k, v in asdict(self).items() if v is not None]
return ", ".join([x for x in flag_fields if x if not None])
def flags(self) -> list[str]:
"""Return the set of cell-storage flags."""
return [x.name for x in fields(self)]
[docs]
class Cell(_CellStorageFlags, Cacheable):
"""
Represent a cell in a Numbers table.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
"""
def __init__(
self,
row: int,
col: int,
value: str | float | bool | datetime | timedelta | None,
) -> None:
"""Initialize the instance state."""
self.row = row
self.col = col
self._value = value
self._is_bulleted = False
self._formula_id: int | None = None
self._storage: Any = None
self._style: Style | None = None
self._border: CellBorder = CellBorder()
self._d128: float | None = None
self._double: float | None = None
self._seconds: float | None = None
self._datetime: datetime | None = None
self._model: NumbersModel = None # type: ignore[assignment]
self._table_id: int = 0
self._buffer: bytes | None = None
self._flags: int = 0
super().__init__()
@property
def sheet_name(self) -> str:
"""str: The name of the sheet the cell resides in."""
return self._model.sheet_name(self._model.table_id_to_sheet_id(self._table_id))
@property
def table_name(self) -> str:
"""str: The name of the table the cell resides in."""
return self._model.table_name(self._table_id)
def __str__(self) -> str:
"""Return the string representation."""
return str(self.value)
def __repr__(self) -> str:
"""Return a developer-friendly representation."""
return (
f"{self.__class__.__module__}.{self.__class__.__name__}"
f"(sheet_name='{self.sheet_name}', "
f"table_name='{self.table_name}', value='{self.value}', "
f"row={self.row}, col={self.col})"
)
@property
def value(self) -> Any: # noqa: ANN401
"""Base class method."""
@property
def image_filename(self) -> str | None:
"""Return the image filename stored with the cell."""
warn(
"image_filename is deprecated and will be removed in the future. "
"Please use the style property",
DeprecationWarning,
stacklevel=2,
)
if self.style.bg_image is not None:
return self.style.bg_image.filename
return None
@property
def image_data(self) -> bytes | None:
"""Return the image data stored with the cell."""
warn(
"image_data is deprecated and will be removed in the future. "
"Please use the style property",
DeprecationWarning,
stacklevel=2,
)
if self.style.bg_image is not None:
return self.style.bg_image.data
return None
@property
def is_formula(self) -> bool:
"""bool: ``True`` if the cell contains a formula."""
return self._formula_id is not None
@property
def formula(self) -> str | None:
"""
str: The formula in a cell.
Formula evaluation relies on Numbers storing current values which should
usually be the case. In cells containing a formula, :py:meth:`numbers_parser.Cell.value`
returns computed value of the formula.
Returns
-------
str:
The text of the formula in a cell, or `None` if there is no formula
present in a cell.
"""
if self._formula_id is not None:
table_formulas = self._model.table_formulas(self._table_id)
return str(table_formulas.formula(self._formula_id, self.row, self.col))
return None
@property
def is_bulleted(self) -> bool:
"""bool: ``True`` if the cell contains text bullets."""
return self._is_bulleted
@property
def bullets(self) -> list[str] | None:
r"""
List[str] | None: The bullets in a cell, or ``None``.
Cells that contain bulleted or numbered lists are identified
by :py:attr:`numbers_parser.Cell.is_bulleted`. For these cells,
:py:attr:`numbers_parser.Cell.value` returns the whole cell contents.
Bullets can also be extracted into a list of paragraphs without the
bullet or numbering character. Newlines are not included in the
bullet list.
Example
-------
.. code-block:: python
doc = Document("mydoc.numbers")
table = doc.sheets[0].tables["Examples"]
if not table.cell(0, 1).is_bulleted:
print(table.cell(0, 1).value)
else:
bullets = ["* " + s for s in table.cell(0, 1).bullets]
print("\n".join(bullets))
"""
return None
@property
def formatted_value(self) -> str:
"""
str: The formatted value of the cell as it appears in Numbers.
Interactive elements are converted into a suitable text format where
supported, or as their number values where there is no suitable
visual representation. Currently supported mappings are:
* Checkboxes are U+2610 (Ballot Box) or U+2611 (Ballot Box with Check)
* Ratings are their star value represented using (U+2605) (Black Star)
.. code-block:: python
>>> table = doc.sheets[0].tables["Examples"]
>>> table.cell("B4").value
False
>>> table.cell("B4").formatted_value
'☐'
>>> table.cell("B5").value
True
>>> table.cell("B5").formatted_value
'☑'
>>> table.cell("B6").value
3.0
>>> table.cell("B6").formatted_value
'★★★'
"""
if self._duration_format_id is not None and self._double is not None:
return self._duration_format()
if self._date_format_id is not None and self._seconds is not None:
return self._date_format()
if (
self._text_format_id is not None
or self._num_format_id is not None
or self._currency_format_id is not None
or self._bool_format_id is not None
):
return self._custom_format()
return str(self.value).upper() if isinstance(self.value, bool) else str(self.value)
@property
def style(self) -> Style:
"""
Style: The :class:`Style` associated with the cell.
Warns
-----
UnsupportedWarning: On assignment; use
:py:meth:`numbers_parser.Table.set_cell_style` instead.
"""
if self._style is None:
self._style = Style.from_storage(self, self._model)
return self._style
@style.setter
def style(self, _: Style | None) -> None:
"""Return or set the style associated with the cell."""
warn(
"cell style cannot be set; use Table.set_cell_style() instead",
UnsupportedWarning,
stacklevel=2,
)
@property
def border(self) -> CellBorder:
"""
CellBorder: The :class:`CellBorder` associated with the cell.
Warns
-----
UnsupportedWarning: On assignment; use
:py:meth:`numbers_parser.Table.set_cell_border` instead.
"""
self._model.extract_strokes(self._table_id)
return self._border
@border.setter
def border(self, _: CellBorder | None) -> None:
"""Return the border configuration associated with the cell."""
warn(
"cell border values cannot be set; use Table.set_cell_border() instead",
UnsupportedWarning,
stacklevel=2,
)
@property
def comments(self) -> list[Comment]:
"""Return a list of (comment, author) tuples for the cell."""
if self._comment_id is None:
return []
return self._model.table_comments(self._table_id, self._comment_id)
@classmethod
def _empty_cell(cls, table_id: int, row: int, col: int, model: NumbersModel) -> EmptyCell:
"""Create an empty cell at the specified table coordinate."""
return cast(
"EmptyCell",
Cell._from_storage(table_id, row, col, EMPTY_STORAGE_BUFFER, model),
)
@classmethod
def _merged_cell(cls, table_id: int, row: int, col: int, model: NumbersModel) -> MergedCell:
"""Create a merged-cell reference at the specified coordinate."""
cell = MergedCell(row, col)
cell._model = model
cell._table_id = table_id
merge_cells = model.merge_cells(table_id)
cell._set_merge(merge_cells.get((row, col)))
return cell
@classmethod
def _from_value(
cls,
row: int,
col: int,
value: str | float | bool | datetime | timedelta | None,
) -> Cell:
# TODO: write needs to retain/init the border
"""Create a cell instance for the supplied value."""
if value is None:
cell: Cell = EmptyCell(row, col)
elif isinstance(value, str):
cell = TextCell(row, col, value)
elif isinstance(value, bool):
cell = BoolCell(row, col, value)
elif isinstance(value, int):
cell = NumberCell(row, col, float(value))
elif isinstance(value, float):
rounded_value = sigfig(value, sigfigs=MAX_SIGNIFICANT_DIGITS, warn=False)
if rounded_value != value:
warn(
f"'{value}' rounded to {MAX_SIGNIFICANT_DIGITS} significant digits",
RuntimeWarning,
stacklevel=2,
)
cell = NumberCell(row, col, rounded_value)
elif isinstance(value, datetime):
cell = DateCell(row, col, value)
elif isinstance(value, timedelta):
cell = DurationCell(row, col, value)
else:
msg = "Can't determine cell type from type " + type(value).__name__
raise ValueError(msg)
return cell
@classmethod
def _from_storage( # noqa: C901, PLR0912
cls,
table_id: int,
row: int,
col: int,
buffer: bytes,
model: NumbersModel,
) -> Cell:
"""Create a cell instance from its archive storage."""
def unpack_int(buffer: bytes, offset: int) -> tuple[int, int]:
"""Read an integer value from the cell storage buffer."""
flag = unpack("<i", buffer[offset : offset + 4])[0]
return (flag, offset + 4)
def unpack_double(buffer: bytes, offset: int) -> tuple[int, int]:
"""Read a floating-point value from the cell storage buffer."""
flag = unpack("<d", buffer[offset : offset + 8])[0]
return (flag, offset + 8)
d128 = None
double = None
seconds = None
version = buffer[0]
if version != 5: # noqa: PLR2004
msg = f"Cell storage version {version} is unsupported"
raise UnsupportedError(msg)
offset = 12
storage_flags = _CellStorageFlags()
flags = unpack("<i", buffer[8:12])[0]
if flags & 0x1:
d128 = _unpack_decimal128(buffer[offset : offset + 16])
offset += 16
if flags & 0x2:
double, offset = unpack_double(buffer, offset)
if flags & 0x4:
seconds, offset = unpack_double(buffer, offset)
if flags & 0x8:
storage_flags._string_id, offset = unpack_int(buffer, offset)
if flags & 0x10:
storage_flags._rich_id, offset = unpack_int(buffer, offset)
if flags & 0x20:
storage_flags._cell_style_id, offset = unpack_int(buffer, offset)
if flags & 0x40:
storage_flags._text_style_id, offset = unpack_int(buffer, offset)
if flags & 0x80:
storage_flags._cond_style_id, offset = unpack_int(buffer, offset)
if flags & 0x100:
storage_flags._cond_rule_style_id, offset = unpack_int(buffer, offset)
if flags & 0x200:
storage_flags._formula_id, offset = unpack_int(buffer, offset)
if flags & 0x400:
storage_flags._control_id, offset = unpack_int(buffer, offset)
if flags & 0x800:
storage_flags._formula_error_id, offset = unpack_int(buffer, offset)
if flags & 0x1000:
storage_flags._suggest_id, offset = unpack_int(buffer, offset)
if flags & 0x2000:
storage_flags._num_format_id, offset = unpack_int(buffer, offset)
if flags & 0x4000:
storage_flags._currency_format_id, offset = unpack_int(buffer, offset)
if flags & 0x8000:
storage_flags._date_format_id, offset = unpack_int(buffer, offset)
if flags & 0x10000:
storage_flags._duration_format_id, offset = unpack_int(buffer, offset)
if flags & 0x20000:
storage_flags._text_format_id, offset = unpack_int(buffer, offset)
if flags & 0x40000:
storage_flags._bool_format_id, offset = unpack_int(buffer, offset)
if flags & 0x80000:
storage_flags._comment_id, offset = unpack_int(buffer, offset)
# Skip 0x100000 (import_warning_id)
cell: Cell
cell_type = buffer[1]
if cell_type == TSTArchives.genericCellType:
cell = EmptyCell(row, col)
elif cell_type == TSTArchives.numberCellType:
cell = NumberCell(row, col, float(d128) if d128 is not None else 0.0)
elif cell_type == TSTArchives.textCellType:
cell = TextCell(row, col, model.table_string(table_id, storage_flags._string_id or 0))
elif cell_type == TSTArchives.dateCellType:
dt_val = EPOCH + timedelta(seconds=seconds or 0.0)
cell = DateCell(row, col, dt_val)
cell._datetime = dt_val
elif cell_type == TSTArchives.boolCellType:
cell = BoolCell(row, col, (double or 0.0) > 0.0)
elif cell_type == TSTArchives.durationCellType:
cell = DurationCell(row, col, timedelta(seconds=double or 0.0))
elif cell_type == TSTArchives.formulaErrorCellType:
cell = ErrorCell(row, col)
elif cell_type == TSTArchives.automaticCellType:
cell = RichTextCell(
row,
col,
model.table_rich_text(table_id, storage_flags._rich_id or 0) or {},
)
elif cell_type == CURRENCY_CELL_TYPE:
cell = NumberCell(
row,
col,
float(d128) if d128 is not None else 0.0,
cell_type=CellType.CURRENCY,
)
else:
msg = f"Cell type ID {cell_type} is not recognized"
raise UnsupportedError(msg)
cell._copy_flags(storage_flags)
cell._buffer = buffer
cell._model = model
cell._table_id = table_id
cell._d128 = d128
cell._double = double
cell._seconds = seconds
cell._extra_bits = unpack("<H", buffer[6:8])[0]
cell._flags = flags
merge_cells = model.merge_cells(table_id)
cell._set_merge(merge_cells.get((row, col)))
if logging.getLogger(__package__).level == logging.DEBUG:
# Guard to reduce expense of computing fields
debug(
"%s, cell_type=%d, flags=0x%0x, %s",
repr(cell),
cell_type,
flags,
repr(storage_flags),
)
return cell
def _copy_flags(self, storage_flags: _CellStorageFlags) -> None:
"""Copy flags."""
for flag in storage_flags.flags():
setattr(self, flag, getattr(storage_flags, flag))
def _set_merge(self, merge_ref: MergeAnchor | MergeReference | None) -> None:
"""Set merge."""
self.size: tuple[int, int] | None
if isinstance(merge_ref, MergeAnchor):
self.is_merged = True
self.size = merge_ref.size
self.merge_range = None
self.rect = None
self._border = CellBorder()
elif isinstance(merge_ref, MergeReference):
self.is_merged = False
self.size = None
self.row_start = merge_ref.rect[0]
self.col_start = merge_ref.rect[1]
self.row_end = merge_ref.rect[2]
self.col_end = merge_ref.rect[3]
self.merge_range = xl_range(*merge_ref.rect)
self.rect = merge_ref.rect
top_merged = self.row > self.row_start
right_merged = self.col < self.col_end
bottom_merged = self.row < self.row_end
left_merged = self.col > self.col_start
self._border = CellBorder(top_merged, right_merged, bottom_merged, left_merged)
else:
self.is_merged = False
self.size = (1, 1)
self.merge_range = None
self.rect = None
def _to_buffer(self) -> bytes | None: # noqa: C901, PLR0912, PLR0915
"""Create a storage buffer for a cell using v5 (modern) layout."""
if self._style is not None:
if self._style._text_style_obj_id is not None:
self._text_style_id = self._model.table_styles.lookup_key(
self._table_id,
TSPMessages.Reference(identifier=self._style._text_style_obj_id),
)
self._model.add_component_reference(
self._style._text_style_obj_id,
component_id=self._model.table_styles.id(self._table_id),
)
if self._style._cell_style_obj_id is not None:
self._cell_style_id = self._model.table_styles.lookup_key(
self._table_id,
TSPMessages.Reference(identifier=self._style._cell_style_obj_id),
)
self._model.add_component_reference(
self._style._cell_style_obj_id,
component_id=self._model.table_styles.id(self._table_id),
)
length = 12
if isinstance(self, NumberCell):
flags = 1
length += 16
if self._type == CellType.CURRENCY:
cell_type = CURRENCY_CELL_TYPE
else:
cell_type = TSTArchives.numberCellType
value = _pack_decimal128(self.value)
elif isinstance(self, TextCell):
flags = 8
length += 4
cell_type = TSTArchives.textCellType
value = pack("<i", self._model.table_string_key(self._table_id, self.value))
elif isinstance(self, DateCell):
flags = 4
length += 8
cell_type = TSTArchives.dateCellType
date_val = self.value
if date_val.tzinfo is None:
date_delta = date_val - EPOCH
else:
date_delta = date_val - EPOCH.astimezone(date_val.tzinfo)
value = pack("<d", float(date_delta.total_seconds()))
elif isinstance(self, BoolCell):
flags = 2
length += 8
cell_type = TSTArchives.boolCellType
value = pack("<d", float(self.value))
elif isinstance(self, DurationCell):
flags = 2
length += 8
cell_type = TSTArchives.durationCellType
duration_val = self.value
value = pack("<d", float(duration_val.total_seconds()))
elif isinstance(self, EmptyCell):
flags = 0
cell_type = TSTArchives.emptyCellValueType
value = b""
elif isinstance(self, MergedCell):
return None
elif isinstance(self, RichTextCell):
flags = 0
cell_type = TSTArchives.automaticCellType
value = b""
elif isinstance(self, ErrorCell):
flags = 0
cell_type = TSTArchives.formulaErrorCellType
value = b""
else:
data_type = type(self).__name__
table_name = self._model.table_name(self._table_id)
table_ref = f"@{table_name}:[{self.row},{self.col}]"
warn(
f"{table_ref}: unsupported data type {data_type} for save",
UnsupportedWarning,
stacklevel=1,
)
return b""
for field_id, bit in (
(self._num_format_id, 0),
(self._currency_format_id, 1),
(self._duration_format_id, 2),
(self._date_format_id, 3),
(self._bool_format_id, 5),
(self._text_format_id, 7),
(self._currency_format_id, 11),
(self._formula_id, 15),
):
if field_id is not None:
self._extra_bits |= 1 << bit
extensions = b""
if self._rich_id is not None:
flags |= 0x10
length += 4
extensions += pack("<i", self._rich_id)
if self._cell_style_id is not None:
flags |= 0x20
length += 4
extensions += pack("<i", self._cell_style_id)
if self._text_style_id is not None:
flags |= 0x40
length += 4
extensions += pack("<i", self._text_style_id)
if self._cond_style_id is not None:
flags |= 0x80
length += 4
extensions += pack("<i", self._cond_style_id)
if self._cond_rule_style_id is not None:
flags |= 0x100
length += 4
extensions += pack("<i", self._cond_rule_style_id)
if self._formula_id is not None:
flags |= 0x200
length += 4
extensions += pack("<i", self._formula_id)
if self._control_id is not None:
flags |= 0x400
length += 4
extensions += pack("<i", self._control_id)
if self._formula_error_id is not None:
flags |= 0x800
length += 4
extensions += pack("<i", self._formula_error_id)
if self._suggest_id is not None:
flags |= 0x1000
length += 4
extensions += pack("<i", self._suggest_id)
if self._num_format_id is not None:
flags |= 0x2000
length += 4
extensions += pack("<i", self._num_format_id)
if self._currency_format_id is not None:
flags |= 0x4000
length += 4
extensions += pack("<i", self._currency_format_id)
if self._date_format_id is not None:
flags |= 0x8000
length += 4
extensions += pack("<i", self._date_format_id)
if self._duration_format_id is not None:
flags |= 0x10000
length += 4
extensions += pack("<i", self._duration_format_id)
if self._text_format_id is not None:
flags |= 0x20000
length += 4
extensions += pack("<i", self._text_format_id)
if self._bool_format_id is not None:
flags |= 0x40000
length += 4
extensions += pack("<i", self._bool_format_id)
if self._comment_id is not None:
flags |= 0x80000
length += 4
extensions += pack("<i", self._comment_id)
header = pack("<BB4xHi", 5, cell_type, self._extra_bits, flags)
storage = header + value + extensions
if len(storage) < 32: # noqa: PLR2004
storage = storage.ljust(32, b"\x00")
return storage[0:length]
def _update_value(self, value: Any, cell: Cell) -> None: # noqa: ANN401
"""Update value."""
if cell._type == CellType.NUMBER:
self._d128 = value or 0.0
elif cell._type == CellType.DATE and isinstance(value, datetime):
self._datetime = value
self._value = value
@property
@cache(num_args=0)
def _image_data(self) -> tuple[bytes, str] | None:
"""Return the background image data for a cell or None if no image."""
if self._cell_style_id is None:
return None
style = self._model.table_style(self._table_id, self._cell_style_id)
if not style.cell_properties.cell_fill.HasField("image"):
return None
image_id = style.cell_properties.cell_fill.image.imagedata.identifier
datas = self._model.package_metadata.datas
stored_filename = next(x.file_name for x in datas if x.identifier == image_id)
preferred_filename = next(x.preferred_file_name for x in datas if x.identifier == image_id)
all_paths = self._model.objects.blob_store.keys()
image_pathnames = [x for x in all_paths if x == f"Data/{stored_filename}"]
if len(image_pathnames) == 0:
warn(
f"Cannot find file '{preferred_filename}' in Numbers archive",
RuntimeWarning,
stacklevel=3,
)
return None
image_data = self._model.objects.blob_store[image_pathnames[0]]
digest = sha1(image_data).digest() # noqa: S324
if digest not in self._model.images:
self._model.images[digest] = image_id
return (image_data, preferred_filename)
def _custom_format(self) -> str: # noqa: C901, PLR0911
"""Return the formatted value using its custom format."""
if self._text_format_id is not None and self._type == CellType.TEXT:
custom_format = self._model.table_format(self._table_id, self._text_format_id)
elif self._currency_format_id is not None:
custom_format = self._model.table_format(self._table_id, self._currency_format_id)
elif self._bool_format_id is not None and self._type == CellType.BOOL:
custom_format = self._model.table_format(self._table_id, self._bool_format_id)
elif self._num_format_id is not None:
custom_format = self._model.table_format(self._table_id, self._num_format_id)
else:
return str(self.value)
debug(
"custom_format: @[%d,%d]: format_type=%s, ",
self.row,
self.col,
custom_format.format_type,
)
d128 = self._d128 or 0.0
if custom_format.HasField("custom_uid"):
format_uuid = NumbersUUID(custom_format.custom_uid).hex
format_map = self._model.custom_format_map()
custom_format = format_map[format_uuid].default_format
if custom_format.requires_fraction_replacement:
formatted_value = _format_fraction(d128, custom_format)
elif custom_format.format_type == FormatType.CUSTOM_TEXT:
formatted_value = _decode_text_format(
custom_format,
self._model.table_string(self._table_id, self._string_id or 0),
)
else:
return _decode_number_format(
custom_format,
d128,
format_map[format_uuid].name,
format_map[format_uuid].conditions,
)
elif custom_format.format_type == FormatType.DECIMAL:
return _format_decimal(d128, custom_format)
elif custom_format.format_type == FormatType.CURRENCY:
return _format_currency(d128, custom_format)
elif custom_format.format_type == FormatType.BOOLEAN:
return "TRUE" if self.value else "FALSE"
elif custom_format.format_type == FormatType.PERCENT:
return _format_decimal(d128 * 100, custom_format, percent=True)
elif custom_format.format_type == FormatType.BASE:
return _format_base(d128, custom_format)
elif custom_format.format_type == FormatType.FRACTION:
return _format_fraction(d128, custom_format)
elif custom_format.format_type == FormatType.SCIENTIFIC:
return _format_scientific(d128, custom_format)
elif custom_format.format_type == FormatType.CHECKBOX:
return CHECKBOX_TRUE_VALUE if self.value else CHECKBOX_FALSE_VALUE
elif custom_format.format_type == FormatType.RATING:
return STAR_RATING_VALUE * int(d128)
else:
formatted_value = str(self.value)
return formatted_value
def _date_format(self) -> str:
"""Return the formatted date or time value."""
# Cannot be logically be None in valid documents
date_format_id = cast("int", self._date_format_id)
cell_datetime = cast("datetime", self._datetime)
date_format = self._model.table_format(self._table_id, date_format_id)
if date_format.HasField("custom_uid"):
format_uuid = NumbersUUID(date_format.custom_uid).hex
format_map = self._model.custom_format_map()
custom_format = format_map[format_uuid].default_format
custom_format_string = custom_format.custom_format_string
if custom_format.format_type == FormatType.CUSTOM_DATE:
formatted_value = _decode_date_format(
custom_format_string,
cell_datetime,
)
else:
warn(
f"Unexpected custom format type {custom_format.format_type}",
UnsupportedWarning,
stacklevel=3,
)
return ""
else:
formatted_value = _decode_date_format(
date_format.date_time_format,
cell_datetime,
)
return formatted_value
def _duration_format(self) -> str: # noqa: C901
"""Return the formatted duration value."""
# Cannot be logically be None in valid documents
duration_format_id = cast("int", self._duration_format_id)
duration_format = self._model.table_format(
self._table_id,
duration_format_id,
)
debug(
"duration_format: @[%d,%d]: table_id=%d, duration_format_id=%d, duration_style=%s",
self.row,
self.col,
self._table_id,
duration_format_id,
duration_format.duration_style,
)
duration_style = DurationStyle(duration_format.duration_style)
unit_largest = DurationUnits(duration_format.duration_unit_largest)
unit_smallest = DurationUnits(duration_format.duration_unit_smallest)
if duration_format.use_automatic_duration_units:
unit_smallest, unit_largest = _auto_units(self._double or 0.0, duration_format)
d = self._double or 0.0
dd = int(self._double or 0.0)
dstr = []
def unit_in_range(
largest: DurationUnits,
smallest: DurationUnits,
unit_type: DurationUnits,
) -> bool:
"""Return whether a duration unit is within the requested range."""
return largest <= unit_type <= smallest
def pad_digits(
d: float,
largest: DurationUnits,
smallest: DurationUnits,
unit_type: DurationUnits,
) -> bool:
"""Pad the number with the required leading digits."""
return (largest == unit_type and smallest == unit_type) or d >= 10 # noqa: PLR2004
if unit_largest == DurationUnits.WEEK:
dd = int(d / SECONDS_IN_WEEK)
if unit_smallest != DurationUnits.WEEK:
d -= SECONDS_IN_WEEK * dd
dstr.append(str(dd) + _unit_format("week", dd, duration_style))
if unit_in_range(unit_largest, unit_smallest, DurationUnits.DAY):
dd = int(d / SECONDS_IN_DAY)
if unit_smallest > DurationUnits.DAY:
d -= SECONDS_IN_DAY * dd
dstr.append(str(dd) + _unit_format("day", dd, duration_style))
if unit_in_range(unit_largest, unit_smallest, DurationUnits.HOUR):
dd = int(d / SECONDS_IN_HOUR)
if unit_smallest > DurationUnits.HOUR:
d -= SECONDS_IN_HOUR * dd
dstr.append(str(dd) + _unit_format("hour", dd, duration_style))
if unit_in_range(unit_largest, unit_smallest, DurationUnits.MINUTE):
dd = int(d / 60)
if unit_smallest > DurationUnits.MINUTE:
d -= 60 * dd
if duration_style == DurationStyle.COMPACT:
pad = pad_digits(dd, unit_smallest, unit_largest, DurationUnits.MINUTE)
dstr.append(("" if pad else "0") + str(dd))
else:
dstr.append(str(dd) + _unit_format("minute", dd, duration_style))
if unit_in_range(unit_largest, unit_smallest, DurationUnits.SECOND):
dd = int(d)
if unit_smallest > DurationUnits.SECOND:
d -= dd
if duration_style == DurationStyle.COMPACT:
pad = pad_digits(dd, unit_smallest, unit_largest, DurationUnits.SECOND)
dstr.append(("" if pad else "0") + str(dd))
else:
dstr.append(str(dd) + _unit_format("second", dd, duration_style))
if unit_smallest >= DurationUnits.MILLISECOND:
dd = round(1000 * d)
if duration_style == DurationStyle.COMPACT:
padding = "0" if dd >= 10 else "00" # noqa: PLR2004
padding = "" if dd >= 100 else padding # noqa: PLR2004
dstr.append(f"{padding}{dd}")
else:
dstr.append(str(dd) + _unit_format("millisecond", dd, duration_style, "ms"))
duration_str = (":" if duration_style == 0 else " ").join(dstr)
if duration_style == DurationStyle.COMPACT:
duration_str = re.sub(r":(\d\d\d)$", r".\1", duration_str)
return duration_str
def _set_formatting( # noqa: C901
self,
format_id: int,
format_type: FormattingType | CustomFormattingType,
control_id: int | None = None,
is_currency: bool = False,
) -> None:
"""Set formatting."""
self._is_currency = is_currency
if is_currency:
self._type = CellType.CURRENCY
if format_type == FormattingType.CURRENCY:
self._currency_format_id = format_id
elif format_type == FormattingType.TICKBOX:
self._bool_format_id = format_id
self._control_id = control_id
elif format_type == FormattingType.RATING:
self._num_format_id = format_id
self._control_id = control_id
elif format_type in [FormattingType.SLIDER, FormattingType.STEPPER]:
if is_currency:
self._currency_format_id = format_id
else:
self._num_format_id = format_id
self._control_id = control_id
elif format_type == FormattingType.POPUP:
self._text_format_id = format_id
self._control_id = control_id
elif format_type in [FormattingType.DATETIME, CustomFormattingType.DATETIME]:
self._date_format_id = format_id
elif format_type == FormattingType.DURATION:
self._duration_format_id = format_id
elif format_type in [FormattingType.TEXT, CustomFormattingType.TEXT]:
self._text_format_id = format_id
else:
self._num_format_id = format_id
class NumberCell(Cell):
"""
Represent a numeric cell.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
A numeric cell exposes its value as a ``float`` and may have a formula,
style, border, or number format.
"""
_value: float
def __init__(
self,
row: int,
col: int,
value: float,
cell_type: CellType = CellType.NUMBER,
) -> None:
"""Initialize the instance state."""
self._type = cell_type
super().__init__(row, col, value)
@property
def value(self) -> float:
"""Return or update the value represented by the cell."""
return self._value
class TextCell(Cell):
"""
Cell containing plain text.
Text cells are created while reading a document or by :meth:`Table.write`.
"""
_value: str
def __init__(self, row: int, col: int, value: str) -> None:
"""Initialize the instance state."""
self._type = CellType.TEXT
super().__init__(row, col, value)
@property
def value(self) -> str:
"""Return or update the value represented by the cell."""
return self._value
[docs]
class RichTextCell(Cell):
"""
Represent a rich-text cell.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
"""
_value: str
def __init__(self, row: int, col: int, value: dict[str, Any]) -> None:
"""Initialize the instance state."""
super().__init__(row, col, value["text"])
self._type = CellType.RICH_TEXT
self._bullets: list[str] = value["bullets"]
self._hyperlinks: list[tuple[str, str]] = value["hyperlinks"]
if value["bulleted"]:
self._formatted_bullets = [
(
str(value["bullet_chars"][i]) + " " + str(value["bullets"][i])
if value["bullet_chars"][i] is not None
else str(value["bullets"][i])
)
for i in range(len(self._bullets))
]
self._is_bulleted = True
@property
def value(self) -> str:
"""Return or update the value represented by the cell."""
return self._value
@property
def bullets(self) -> list[str]:
"""List[str]: A list of the text bullets in the cell."""
return self._bullets
@property
def formatted_bullets(self) -> list[str]:
"""list[str]: The bullet paragraphs including their bullet markers."""
return self._formatted_bullets
@property
def hyperlinks(self) -> list[tuple[str, str]]:
"""
List[Tuple[str, str]]: the hyperlinks in a cell.
Numbers does not support hyperlinks to cells within a spreadsheet, but does
allow embedding links in cells. When cells contain hyperlinks,
`numbers_parser` returns the text version of the cell. The `hyperlinks` property
of cells where :py:attr:`numbers_parser.Cell.is_bulleted` is ``True`` is a
list of text and URL tuples.
Example
-------
.. code-block:: python
>>> table.cell(1, 2).value
'example.com'
>>> table.cell(1, 2).hyperlinks
[('example.com', 'http://example.com')]
"""
return self._hyperlinks
# Backwards compatibility to earlier class names
class BulletedTextCell(RichTextCell):
"""Represent rich text cells containing bulleted paragraphs."""
class EmptyCell(Cell):
"""
Represent an empty cell.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
Empty cells have a value of ``None`` and an empty formatted value.
"""
def __init__(self, row: int, col: int) -> None:
"""Initialize the instance state."""
super().__init__(row, col, None)
self._type = CellType.EMPTY
@property
def value(self) -> None:
"""Return or update the value represented by the cell."""
return None
@property
def formatted_value(self) -> str:
"""Return the cell value formatted for display."""
return ""
class BoolCell(Cell):
"""
Represent a Boolean cell.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
Boolean cells expose a Python ``bool`` and can use tickbox formatting.
"""
_value: bool
def __init__(self, row: int, col: int, value: bool) -> None:
"""Initialize the instance state."""
super().__init__(row, col, value)
self._type = CellType.BOOL
self._value = value
@property
def value(self) -> bool:
"""Return or update the value represented by the cell."""
return self._value
class DateCell(Cell):
"""
Represent a date or time cell.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
Date cells expose a :class:`datetime.datetime` value.
"""
_value: datetime
def __init__(self, row: int, col: int, value: datetime) -> None:
"""Initialize the instance state."""
super().__init__(row, col, value)
self._type = CellType.DATE
@property
def value(self) -> datetime:
"""Return or update the value represented by the cell."""
return self._value
class DurationCell(Cell):
"""Cell containing a :class:`datetime.timedelta` value."""
_value: timedelta
def __init__(self, row: int, col: int, value: timedelta) -> None:
"""Initialize the instance state."""
super().__init__(row, col, value)
self._type = CellType.DURATION
@property
def value(self) -> timedelta:
"""Return or update the value represented by the cell."""
return self._value
class ErrorCell(Cell):
"""
Represent a cell containing a formula error.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
Error cells expose ``None`` as their value. Their formatted value preserves
the formatted error text when it is available.
"""
def __init__(self, row: int, col: int) -> None:
"""Initialize the instance state."""
super().__init__(row, col, None)
self._type = CellType.ERROR
@property
def value(self) -> None:
"""Return or update the value represented by the cell."""
return None
[docs]
class MergedCell(Cell):
"""
Represent a cell covered by a merged range.
.. NOTE::
Do not instantiate directly. Cells are created by :py:class:`~numbers_parser.Document`.
Merged cells represent non-anchor positions in a merged range. Use
:attr:`Cell.merge_range` and the row/column boundary properties to inspect
the range.
"""
def __init__(self, row: int, col: int) -> None:
"""Initialize the instance state."""
super().__init__(row, col, None)
self._type = CellType.MERGED
@property
def value(self) -> None:
"""Return or update the value represented by the cell."""
return None
def _pack_decimal128(value: float) -> bytes:
"""Encode a decimal value in the archive decimal128 format."""
buffer = bytearray(16)
exp = math.floor(math.log10(math.e) * math.log(abs(value))) if value != 0.0 else 0
exp += DECIMAL128_BIAS - 16
mantissa = abs(int(value / math.pow(10, exp - DECIMAL128_BIAS)))
buffer[15] |= exp >> 7
buffer[14] |= (exp & 0x7F) << 1
i = 0
while mantissa >= 1:
buffer[i] = mantissa & 0xFF
i += 1
mantissa = int(mantissa / 256)
if value < 0:
buffer[15] |= 0x80
return bytes(buffer)
def _unpack_decimal128(buffer: bytes) -> float:
"""Decode a decimal128 value from the archive bytes."""
exp = (((buffer[15] & 0x7F) << 7) | (buffer[14] >> 1)) - DECIMAL128_BIAS
mantissa = buffer[14] & 1
for i in range(13, -1, -1):
mantissa = mantissa * 256 + buffer[i]
sign = 1 if buffer[15] & 0x80 else 0
if sign == 1:
mantissa = -mantissa
value = mantissa * 10**exp
return float(value)
def _decode_date_format(date_format: str, value: datetime) -> str: # noqa: C901
"""Parse a custom date format string and return a formatted datetime value."""
chars = [*date_format]
index = 0
in_string = False
result = ""
while index < len(chars):
current_char = chars[index]
next_char = chars[index + 1] if index < len(chars) - 1 else None
if current_char == "'":
if next_char is None:
break
if chars[index + 1] == "'":
result += "'"
index += 2
elif in_string:
in_string = False
index += 1
else:
in_string = True
index += 1
elif in_string:
result += current_char
index += 1
elif current_char.isalpha():
remaining_str = date_format[index:]
matched_field = None
for date_field in DATETIME_FIELD_MAP:
# Greedily match the longest possible valid field
if remaining_str.startswith(date_field):
matched_field = date_field
break
if matched_field:
s = DATETIME_FIELD_MAP[matched_field]
if callable(s):
result += s(value)
else:
result += value.strftime(str(s))
index += len(matched_field)
else:
unknown_field = ""
while index < len(chars) and chars[index].isalpha():
unknown_field += chars[index]
index += 1
warn(f"Unsupported field code '{unknown_field}'", UnsupportedWarning, stacklevel=4)
else:
result += current_char
index += 1
return result
def _decode_text_format(
text_format: TSKArchives.FormatStructArchive,
value: str,
) -> str:
"""Apply a custom text format string and return the formatted text value."""
custom_format_string = text_format.custom_format_string
return custom_format_string.replace(CUSTOM_TEXT_PLACEHOLDER, value)
def _expand_quotes(value: str) -> str:
"""Expand quotes."""
chars = [*value]
index = 0
in_string = False
formatted_value = ""
while index < len(chars):
current_char = chars[index]
next_char = chars[index + 1] if index < len(chars) - 1 else None
if current_char == "'":
if next_char is None:
break
if chars[index + 1] == "'":
formatted_value += "'"
index += 2
elif in_string:
in_string = False
index += 1
else:
in_string = True
index += 1
else:
formatted_value += current_char
index += 1
return formatted_value
def _decode_number_format( # noqa: C901, PLR0912, PLR0915
number_format: TSKArchives.FormatStructArchive,
value: float,
_name: str,
conditions: Sequence[TSKArchives.CustomFormatArchive.Condition],
) -> str:
"""Parse a custom date format string and return a formatted number value."""
if value is None or number_format is None:
return None
custom_format_string = number_format.custom_format_string
sign = "-" if value < 0 else ""
for condition in conditions:
value_sigfig = sigfig(value, sigfigs=MAX_SIGNIFICANT_DIGITS, warn=False)
if (
(
condition.condition_type == NumberFormatConditionType.EQUAL
and value_sigfig == condition.condition_value_dbl
)
or (
condition.condition_type == NumberFormatConditionType.LESS_THAN
and value_sigfig < condition.condition_value_dbl
)
or (
condition.condition_type == NumberFormatConditionType.LESS_THAN_OR_EQUAL_TO
and value_sigfig <= condition.condition_value_dbl
)
or (
condition.condition_type == NumberFormatConditionType.GREATER_THAN
and value_sigfig > condition.condition_value_dbl
)
or (
condition.condition_type == NumberFormatConditionType.GREATER_THAN_OR_EQUAL_TO
and value_sigfig >= condition.condition_value_dbl
)
):
custom_format_string = condition.condition_format.custom_format_string
# Numbers discards sign when rules apply
sign = ""
break
value = abs(value)
value *= number_format.scale_factor
if "%" in custom_format_string and number_format.scale_factor == 1.0:
# Per cent scale has 100x but % does not
value *= 100.0
if number_format.currency_code != "":
# Replace currency code with symbol and no-break space
custom_format_string = custom_format_string.replace(
"\u00a4",
number_format.currency_code + "\u00a0",
)
if (match := re.search(r"([#0.,]+(E[+]\d+)?)", custom_format_string)) is None:
# Value is not part of the format
return _expand_quotes(custom_format_string)
format_spec = match.group(1)
scientific_spec = match.group(2)
if format_spec[0] == ".":
(int_part, dec_part) = ("", format_spec[1:])
elif "." in custom_format_string:
(int_part, dec_part) = format_spec.split(".")
else:
(int_part, dec_part) = (format_spec, "")
if scientific_spec is not None:
# Scientific notation
formatted_value = f"{value:.{len(dec_part) - 4}E}"
formatted_value = custom_format_string.replace(format_spec, formatted_value)
return _expand_quotes(formatted_value)
num_decimals = len(dec_part)
if num_decimals > 0:
if dec_part[0] == "#":
dec_pad = None
elif number_format.num_nonspace_decimal_digits > 0:
dec_pad = CellPadding.ZERO
else:
dec_pad = CellPadding.SPACE
else:
dec_pad = None
dec_width = num_decimals
value = round(float(value), num_decimals) if num_decimals > 0 else round(float(value))
# Custom formats have a maximum of 10 decimals
(integer_str, decimal_str) = f"{value:.10f}".split(".")
if num_decimals > 0:
integer = int(integer_str)
decimal = round(float(f"0.{decimal_str}"), num_decimals)
else:
integer = round(value)
decimal = float(f"0.{decimal_str}")
num_integers = len(int_part.replace(",", ""))
if not number_format.show_thousands_separator:
int_part = int_part.replace(",", "")
if num_integers > 0:
if int_part[0] == "#":
int_pad = None
int_width = len(int_part)
elif number_format.num_nonspace_integer_digits > 0:
int_pad = CellPadding.ZERO
if number_format.show_thousands_separator:
num_commas = int(math.floor(math.log10(integer)) / 3) if integer != 0 else 0
num_commas = max([num_commas, int((num_integers - 1) / 3)])
int_width = num_integers + num_commas
else:
int_width = num_integers
else:
int_pad = CellPadding.SPACE
int_width = len(int_part)
else:
int_pad = None
int_width = num_integers
int_pad_space_as_zero = False
# Formatting integer zero:
# Blank (padded if needed) if int_pad is SPACE and no decimals
# No leading zero if:
# int_pad is NONE, dec_pad is SPACE
# int_pad is SPACE, dec_pad is SPACE
# int_pad is SPACE, dec_pad is ZERO
# int_pad is SPACE, dec_pad is NONE if num decimals < decimals length
if integer == 0 and int_pad == CellPadding.SPACE and num_decimals == 0:
formatted_value = "".rjust(int_width)
elif integer == 0 and int_pad is None and dec_pad == CellPadding.SPACE:
formatted_value = ""
elif (integer == 0 and int_pad == CellPadding.SPACE and dec_pad is not None) or (
integer == 0
and int_pad == CellPadding.SPACE
and dec_pad is None
and len(str(decimal)) > num_decimals
):
formatted_value = "".rjust(int_width)
elif int_pad_space_as_zero or int_pad == CellPadding.ZERO:
if number_format.show_thousands_separator:
formatted_value = f"{integer:0{int_width},}"
else:
formatted_value = f"{integer:0{int_width}}"
elif int_pad == CellPadding.SPACE:
if number_format.show_thousands_separator:
formatted_value = f"{integer:,}".rjust(int_width)
else:
formatted_value = str(integer).rjust(int_width)
elif number_format.show_thousands_separator:
formatted_value = f"{integer:,}"
else:
formatted_value = str(integer)
if num_decimals:
if dec_pad == CellPadding.ZERO or (dec_pad == CellPadding.SPACE and num_integers == 0):
formatted_value += "." + f"{decimal:,.{dec_width}f}"[2:]
elif dec_pad == CellPadding.SPACE and decimal == 0 and num_integers > 0:
formatted_value += ".".ljust(dec_width + 1)
elif dec_pad == CellPadding.SPACE:
if "e" in str(decimal):
decimal_str = f"{decimal:.{num_decimals}f}"[2:]
else:
decimal_str = str(decimal)[2:]
formatted_value += "." + decimal_str.ljust(dec_width)
elif decimal or num_integers == 0:
if "e" in str(decimal):
formatted_value += "." + f"{decimal:.{num_decimals}f}"[2:]
else:
formatted_value += "." + str(decimal)[2:]
formatted_value = custom_format_string.replace(format_spec, formatted_value)
return sign + _expand_quotes(formatted_value)
def _format_decimal(
value: float,
number_format: TSKArchives.FormatStructArchive,
percent: bool = False,
) -> str:
"""Format decimal."""
if value is None or number_format is None:
return ""
if value < 0 and number_format.negative_style == 1:
accounting_style = False
value = -value
elif value < 0 and number_format.negative_style >= 2:
accounting_style = True
value = -value
else:
accounting_style = False
thousands = "," if number_format.show_thousands_separator else ""
if value.is_integer() and number_format.decimal_places >= DECIMAL_PLACES_AUTO:
formatted_value = f"{int(value):{thousands}}"
else:
if number_format.decimal_places >= DECIMAL_PLACES_AUTO:
formatted_value = str(sigfig(value, MAX_SIGNIFICANT_DIGITS, warn=False))
else:
formatted_value = sigfig(value, MAX_SIGNIFICANT_DIGITS, type=str, warn=False)
formatted_value = sigfig(
formatted_value,
decimals=number_format.decimal_places,
type=str,
)
if number_format.show_thousands_separator:
formatted_value = sigfig(formatted_value, spacer=",", spacing=3, type=str)
try:
(integer, decimal) = formatted_value.split(".")
formatted_value = integer + "." + decimal.replace(",", "")
except ValueError:
pass
if percent:
formatted_value += "%"
if accounting_style:
return f"({formatted_value})"
return formatted_value
def _format_currency(
value: float,
number_format: TSKArchives.FormatStructArchive,
) -> str:
"""Format currency."""
if number_format is None:
return None
formatted_value = _format_decimal(value, number_format)
if number_format.currency_code in CURRENCY_SYMBOLS:
symbol = CURRENCY_SYMBOLS[number_format.currency_code]
else:
symbol = number_format.currency_code + " "
if number_format.use_accounting_style and value < 0:
return f"{symbol}\t({formatted_value[1:]})"
if number_format.use_accounting_style:
return f"{symbol}\t{formatted_value}"
return symbol + formatted_value
INT_TO_BASE_CHAR = [str(x) for x in range(10)] + [chr(x) for x in range(ord("A"), ord("Z") + 1)]
def _invert_bit_str(value: str) -> str:
"""Invert a binary value."""
return "".join(["0" if b == "1" else "1" for b in value])
def _twos_complement(value: int, base: int) -> str:
"""Calculate the twos complement of a negative integer with minimum 32-bit precision."""
num_bits = max([32, math.ceil(math.log2(abs(value))) + 1])
bin_value = bin(abs(value))[2:] # noqa: FURB116
inverted_bin_value = _invert_bit_str(bin_value).rjust(num_bits, "1")
twos_complement_dec = int(inverted_bin_value, 2) + 1
if base == 2:
return f"{twos_complement_dec:b}".rjust(num_bits, "1")
if base == 8: # noqa: PLR2004
return f"{twos_complement_dec:o}"
return f"{twos_complement_dec:x}".upper()
def _format_base(value: float, number_format: TSKArchives.FormatStructArchive) -> str:
"""Format base."""
if value == 0:
return "0".zfill(number_format.base_places)
value = round(value)
is_negative = False
if not number_format.base_use_minus_sign and number_format.base in [2, 8, 16]:
if value < 0:
return _twos_complement(value, number_format.base)
value = abs(value)
elif value < 0:
is_negative = True
value = abs(value)
formatted_value = []
while value:
formatted_value.append(int(value % number_format.base))
value //= number_format.base
formatted_value_str = "".join([INT_TO_BASE_CHAR[x] for x in formatted_value[::-1]])
if is_negative:
return "-" + formatted_value_str.zfill(number_format.base_places)
return formatted_value_str.zfill(number_format.base_places)
def _format_fraction_parts(whole: int, numerator: int, denominator: int) -> str:
"""Return the simplest fraction for the given whole, numerator and denominator."""
whole, numerator = abs(whole), abs(numerator)
if not numerator:
return str(whole)
fraction = f"{numerator}/{denominator}"
return f"{whole} {fraction}" if whole else fraction
def _float_to_fraction(value: float, denominator: int) -> str:
"""Convert a float to the nearest fraction and return as a string."""
numerator = round(abs(value) * denominator)
whole, rem = divmod(numerator, denominator)
result = _format_fraction_parts(whole, rem, denominator)
return f"-{result}" if value < 0 and result != "0" else result
def _float_to_n_digit_fraction(value: float, max_digits: int) -> str:
"""Convert a float to a fraction of a maximum number of digits and return as a string."""
fraction = Fraction(abs(value)).limit_denominator(10**max_digits - 1)
whole, numerator = divmod(fraction.numerator, fraction.denominator)
result = _format_fraction_parts(whole, numerator, fraction.denominator)
return f"-{result}" if value < 0 and result != "0" else result
def _format_fraction(value: float, number_format: TSKArchives.FormatStructArchive) -> str:
"""Format fraction."""
accuracy = number_format.fraction_accuracy
if accuracy & 0xFF000000:
num_digits = 0x100000000 - accuracy
return _float_to_n_digit_fraction(value, num_digits)
return _float_to_fraction(value, accuracy)
def _format_scientific(value: float, number_format: TSKArchives.FormatStructArchive) -> str:
"""Format scientific."""
formatted_value = sigfig(value, sigfigs=MAX_SIGNIFICANT_DIGITS, warn=False)
return f"{formatted_value:.{number_format.decimal_places}E}"
def _unit_format(
unit: str,
value: int,
style: DurationStyle,
abbrev: str | None = None,
) -> str:
"""Format a duration value using its selected unit."""
plural = "" if value == 1 else "s"
if abbrev is None:
abbrev = unit[0]
if style == DurationStyle.COMPACT:
return ""
if style == DurationStyle.SHORT:
return f"{abbrev}"
return f" {unit}" + plural
def _auto_units( # noqa: C901
cell_value: float,
number_format: TSKArchives.FormatStructArchive,
) -> tuple[DurationUnits, DurationUnits]:
"""Select duration units that fit the value and display options."""
unit_largest = number_format.duration_unit_largest
unit_smallest = number_format.duration_unit_smallest
if cell_value == 0:
unit_largest = DurationUnits.DAY
unit_smallest = DurationUnits.DAY
else:
if cell_value >= SECONDS_IN_WEEK:
unit_largest = DurationUnits.WEEK
elif cell_value >= SECONDS_IN_DAY:
unit_largest = DurationUnits.DAY
elif cell_value >= SECONDS_IN_HOUR:
unit_largest = DurationUnits.HOUR
elif cell_value >= 60: # noqa: PLR2004
unit_largest = DurationUnits.MINUTE
elif cell_value >= 1:
unit_largest = DurationUnits.SECOND
else:
unit_largest = DurationUnits.MILLISECOND
if math.floor(cell_value) != cell_value:
unit_smallest = DurationUnits.MILLISECOND
elif cell_value % 60:
unit_smallest = DurationUnits.SECOND
elif cell_value % SECONDS_IN_HOUR:
unit_smallest = DurationUnits.MINUTE
elif cell_value % SECONDS_IN_DAY:
unit_smallest = DurationUnits.HOUR
elif cell_value % SECONDS_IN_WEEK:
unit_smallest = DurationUnits.DAY
unit_smallest = DurationUnits(max(unit_smallest, unit_largest))
return unit_smallest, unit_largest
@dataclass()
class Formatting:
"""Represent a cell data-format configuration."""
allow_none: bool = False
base_places: int = 0
base_use_minus_sign: bool = True
base: int = 10
control_format: ControlFormattingType = ControlFormattingType.NUMBER
currency_code: str = "GBP"
date_time_format: str = DEFAULT_DATETIME_FORMAT
duration_style: DurationStyle = DurationStyle.COMPACT
duration_unit_largest: DurationUnits | None = None
duration_unit_smallest: DurationUnits | None = None
decimal_places: int | None = None
fraction_accuracy: FractionAccuracy = FractionAccuracy.THREE
increment: float = 1.0
maximum: float = 100.0
minimum: float = 1.0
popup_values: list[str] = field(default_factory=lambda: ["Item 1"])
negative_style: NegativeNumberStyle = NegativeNumberStyle.MINUS
show_thousands_separator: bool = False
type: FormattingType = FormattingType.NUMBER
use_accounting_style: bool = False
_format_id = None
def __post_init__(self) -> None: # noqa: C901
"""Validate and initialize the instance state."""
if not isinstance(self.type, FormattingType):
type_name = type(self.type).__name__
msg = f"Invalid format type '{type_name}'"
raise TypeError(msg)
if self.use_accounting_style and self.negative_style != NegativeNumberStyle.MINUS:
warn(
"use_accounting_style overriding negative_style",
RuntimeWarning,
stacklevel=4,
)
if self.type == FormattingType.DATETIME:
formats = re.sub(r"[^a-zA-Z\s]", " ", self.date_time_format).split()
for el in formats:
if el not in DATETIME_FIELD_MAP:
msg = f"Invalid format specifier '{el}' in date/time format"
raise TypeError(msg)
if self.type == FormattingType.CURRENCY and self.currency_code not in CURRENCIES:
msg = f"Unsupported currency code '{self.currency_code}'"
raise TypeError(msg)
if self.decimal_places is None:
if self.type == FormattingType.CURRENCY:
self.decimal_places = 2
else:
self.decimal_places = DECIMAL_PLACES_AUTO
if (
self.type == FormattingType.BASE
and not self.base_use_minus_sign
and self.base not in (2, 8, 16)
):
msg = f"base_use_minus_sign must be True for base {self.base}"
raise TypeError(msg)
if self.type == FormattingType.BASE and (self.base < 2 or self.base > MAX_BASE):
msg = "base must be in range 2-36"
raise TypeError(msg)
@dataclass
class CustomFormatting:
"""Represent a named custom cell format."""
type: CustomFormattingType = CustomFormattingType.NUMBER
name: str | None = None
integer_format: PaddingType = PaddingType.NONE
decimal_format: PaddingType = PaddingType.NONE
num_integers: int = 0
num_decimals: int = 0
show_thousands_separator: bool = False
format: str = "%s"
def __post_init__(self) -> None:
"""Validate and initialize the instance state."""
if not isinstance(self.type, CustomFormattingType):
type_name = type(self.type).__name__
msg = f"Invalid format type '{type_name}'"
raise TypeError(msg)
if self.type == CustomFormattingType.TEXT and self.format.count("%s") > 1:
msg = "Custom formats only allow one text substitution"
raise TypeError(msg)
@classmethod
def from_archive(cls, archive: TSKArchives.CustomFormatArchive) -> CustomFormatting:
"""Create a Style from an archived style definition."""
if archive.format_type == FormatType.CUSTOM_DATE:
format_type = CustomFormattingType.DATETIME
elif archive.format_type == FormatType.CUSTOM_NUMBER:
format_type = CustomFormattingType.NUMBER
else:
format_type = CustomFormattingType.TEXT
return CustomFormatting(name=archive.name, type=format_type)