Module refinery.lib.excel.formula

The formula substrate of the Excel library: the AST model for formulas, the text parser for OOXML formula sources, the synthesizer that prints the model back as formula text, and the decoders that turn the RPN token streams of BIFF and XLSB cells into the same model.

"""
The formula substrate of the Excel library: the AST model for formulas, the text parser for
OOXML formula sources, the synthesizer that prints the model back as formula text, and the
decoders that turn the RPN token streams of BIFF and XLSB cells into the same model.
"""
from __future__ import annotations

from refinery.lib.excel.formula.biff import BiffRpnContext, BiffRpnDecoder
from refinery.lib.excel.formula.model import (
    XlA1Reference,
    XlArrayConstant,
    XlBinaryExpression,
    XlBinaryOperator,
    XlBoolean,
    XlDefinedName,
    XlError,
    XlFunctionCall,
    XlLiteral,
    XlMissingArgument,
    XlNumber,
    XlParenExpression,
    XlR1C1Reference,
    XlString,
    XlUnaryExpression,
    XlUnaryOperator,
    XlUnparsedFormula,
)
from refinery.lib.excel.formula.parse import International, parse_formula
from refinery.lib.excel.formula.synth import FormulaSynthesizer, synthesize_formula
from refinery.lib.excel.formula.xlsb import XlsbRpnDecoder

__all__ = [
    'BiffRpnContext',
    'BiffRpnDecoder',
    'FormulaSynthesizer',
    'International',
    'XlArrayConstant',
    'XlA1Reference',
    'XlBinaryExpression',
    'XlBinaryOperator',
    'XlBoolean',
    'XlDefinedName',
    'XlError',
    'XlFunctionCall',
    'XlLiteral',
    'XlMissingArgument',
    'XlNumber',
    'XlParenExpression',
    'XlR1C1Reference',
    'XlString',
    'XlUnaryExpression',
    'XlUnaryOperator',
    'XlUnparsedFormula',
    'XlsbRpnDecoder',
    'parse_formula',
    'synthesize_formula',
]

Sub-modules

refinery.lib.excel.formula.biff

The BIFF operand layouts for the shared RPN stack machine: one reader per token whose byte layout the BIFF record format defines. Everything that only …

refinery.lib.excel.formula.model

The AST model for Excel formulas: a family of refinery.lib.scripts expression nodes that every formula source — the text an OOXML part stores, the …

refinery.lib.excel.formula.parse

The text parser for Excel formula sources: the <f> element text of an OOXML cell, the body of a defined name, or any other formula text with or …

refinery.lib.excel.formula.ptg

The shared substrate for the RPN formula streams that BIFF and XLSB cells store: the Ptg token table with the value-class bits masked away, the …

refinery.lib.excel.formula.synth

The synthesizer that prints the formula model back as one line of formula text, without the leading =. Parentheses are emitted only where the …

refinery.lib.excel.formula.xlsb

The MS-XLSB operand layouts for the shared RPN stack machine: one reader per token whose byte layout the XLSB formula grammar defines. The token …

Functions

def parse_formula(text, international=None)

Parse formula text into the AST model. The leading = of a stored formula is optional. Any input that does not read as one whole formula becomes an XlUnparsedFormula carrying the text, never an exception. The parse runs under TREE_RECURSION_DEPTH, so how deeply a formula may nest does not depend on the recursion limit of the caller.

Expand source code Browse git

def parse_formula(
    text: str,
    international: International | None = None,
) -> Expression:
    """
    Parse formula text into the AST model. The leading `=` of a stored formula is optional. Any
    input that does not read as one whole formula becomes an `XlUnparsedFormula` carrying the
    text, never an exception. The parse runs under `refinery.lib.scripts.TREE_RECURSION_DEPTH`,
    so how deeply a formula may nest does not depend on the recursion limit of the caller.
    """
    if not text or not text.strip():
        return XlUnparsedFormula(text=text)
    parser = _Parser(text.strip(), international or International())
    try:
        with RecursionDepth(TREE_RECURSION_DEPTH):
            expression = parser.parse()
    except (_ParseFailure, RecursionError, ValueError, IndexError):
        return XlUnparsedFormula(text=text)
    return expression

def synthesize_formula(expression, international=None)

Print a formula Expression as one line of formula text without the leading =.

Expand source code Browse git

def synthesize_formula(
    expression: Expression,
    international: International | None = None,
) -> str:
    """
    Print a formula `Expression` as one line of formula text without the leading `=`.
    """
    return FormulaSynthesizer(international).convert(expression)

Classes

class BiffRpnContext (*args, **kwargs)

What the BIFF decoder additionally asks of its workbook: the sheet span of a BIFF5 3-D reference, which that version stores directly instead of through the extern-sheet table.

Expand source code Browse git

class BiffRpnContext(RpnContext, Protocol):
    """
    What the BIFF decoder additionally asks of its workbook: the sheet span of a BIFF5 3-D
    reference, which that version stores directly instead of through the extern-sheet table.
    """

    def sheet_span(self, first: int, last: int) -> tuple[str, ...]:
        """
        The sheet names a BIFF5 3-D reference spans, given the first and last index into the
        workbook's sheet table.
        """
        ...

Ancestors

• RpnContext
• typing.Protocol
• typing.Generic

Methods

def sheet_span(self, first, last)

The sheet names a BIFF5 3-D reference spans, given the first and last index into the workbook's sheet table.

Expand source code Browse git

def sheet_span(self, first: int, last: int) -> tuple[str, ...]:
    """
    The sheet names a BIFF5 3-D reference spans, given the first and last index into the
    workbook's sheet table.
    """
    ...

Inherited members

• RpnContext:
  • defined_name
  • extern_sheets

class BiffRpnDecoder (view, version, context, codepage)

The stack machine over the formula stream of a BIFF cell or NAME record. The version and the codepage of the workbook select the layouts; the context resolves defined names and 3-D sheet spans.

Expand source code Browse git

class BiffRpnDecoder(RpnDecoder):
    """
    The stack machine over the formula stream of a BIFF cell or NAME record. The version and
    the codepage of the workbook select the layouts; the context resolves defined names and
    3-D sheet spans.
    """

    _context: BiffRpnContext | None

    def __init__(
        self,
        view: memoryview,
        version: BiffVersion,
        context: BiffRpnContext | None,
        codepage: str,
    ):
        super().__init__(view, context)
        self._version = version
        self._codepage = codepage

    def _read_string(self) -> XlString:
        """
        The `ptgStr` token: a one-byte character count followed by the characters. Only BIFF8
        writes the option byte that selects the two-byte encoding; the earlier versions write
        plain codepage characters.
        """
        count = self._read_u8()
        if self._version >= BiffVersion.BIFF8:
            flags = self._read_u8()
            if flags & 0x01:
                return XlString(value=self._read_text(2 * count, 'utf_16_le'))
            return XlString(value=self._read_text(count, 'latin_1'))
        return XlString(value=self._read_text(count, self._codepage))

    def _read_function_id(self) -> int:
        """
        The identifier of a `ptgFunc` token, which grew from one byte to two with BIFF4.
        """
        if self._version < BiffVersion.BIFF4:
            return self._read_u8()
        return self._read_u16()

    def _read_function_variable(self) -> tuple[int, int, bool]:
        """
        The payload of a `ptgFuncVar` token: an argument count whose top bit only marks a
        pending prompt, then the identifier. An identifier of 255 — with or without the macro
        bit, which selects the macro-command half of the id space — names a user-defined call
        whose name operand sits below the arguments on the stack.
        """
        argc = self._read_u8() & 0x7F
        if self._version < BiffVersion.BIFF4:
            index = self._read_u8()
        else:
            index = self._read_u16()
        return index, argc, (index & 0x7FFF) == 0xFF

    def _read_defined_name(self) -> Expression:
        """
        The `ptgName` token: a one-based index into the name table, widened to four bytes in
        BIFF8 after earlier versions padded the two-byte index with unused bytes.
        """
        if self._version >= BiffVersion.BIFF8:
            index = self._read_u32()
        else:
            index = self._read_u16()
            if self._version < BiffVersion.BIFF3:
                self._read_bytes(5)
            elif self._version < BiffVersion.BIFF5:
                self._read_bytes(8)
            else:
                self._read_bytes(12)
        if self._context is None:
            raise RpnError('a name token has no workbook to resolve it')
        return self._context.defined_name(index)

    def _read_reference(self, relative: bool) -> Expression:
        """
        The `ptgRef` and `ptgRefN` tokens. A `ptgRef` carries absolute positions whose
        relative flags say which axes a copy of the formula adjusts; a `ptgRefN` carries an
        offset on every axis flagged relative, which only the containing cell resolves.
        """
        if self._version >= BiffVersion.BIFF8:
            row_word = self._read_u16()
            col_word = self._read_u16()
            return self._compose_reference(row_word, col_word & 0xFF, col_word, relative)
        row_word = self._read_u16()
        col = self._read_u8()
        return self._compose_reference(row_word, col, row_word, relative)

    def _read_area(self, relative: bool) -> Expression:
        """
        The `ptgArea` and `ptgAreaN` tokens: two corners of a rectangle, each carrying the
        reference layout of its version.
        """
        if self._version >= BiffVersion.BIFF8:
            first_row = self._read_u16()
            second_row = self._read_u16()
            first_col_word = self._read_u16()
            second_col_word = self._read_u16()
            first = self._compose_reference(
                first_row,
                first_col_word & 0xFF,
                first_col_word,
                relative,
            )
            second = self._compose_reference(
                second_row,
                second_col_word & 0xFF,
                second_col_word,
                relative,
            )
        else:
            first_row = self._read_u16()
            second_row = self._read_u16()
            first_col = self._read_u8()
            second_col = self._read_u8()
            first = self._compose_reference(first_row, first_col, first_row, relative)
            second = self._compose_reference(second_row, second_col, second_row, relative)
        return XlBinaryExpression(
            left=first,
            operator=XlBinaryOperator.RANGE,
            right=second,
        )

    def _read_3d_reference(self) -> Expression:
        """
        The `ptgRef3d` token: a sheet span followed by a reference of the version's layout.
        BIFF8 resolves the span through the extern-sheet table; BIFF5 stores the first and
        last sheet of the span directly.
        """
        sheets = self._read_3d_sheets()
        return self._qualify(self._read_reference(False), sheets)

    def _read_3d_area(self) -> Expression:
        """
        The `ptgArea3d` token: a sheet span followed by an area of the version's layout.
        """
        sheets = self._read_3d_sheets()
        return self._qualify(self._read_area(False), sheets)

    def _read_3d_sheets(self) -> tuple[str, ...]:
        if self._context is None:
            raise RpnError('a 3-D reference has no workbook to resolve its sheets')
        if self._version >= BiffVersion.BIFF8:
            return self._context.extern_sheets(self._read_u16())
        externsheet = int.from_bytes(self._read_bytes(2), 'little', signed=True)
        self._read_bytes(8)
        first, last = (
            int.from_bytes(self._read_bytes(2), 'little'),
            int.from_bytes(self._read_bytes(2), 'little'),
        )
        if externsheet >= 0:
            raise RpnError('a 3-D reference names a sheet of another document')
        if first == last == 0xFFFF or first == last == 0xFFFE:
            raise RpnError('a 3-D reference names a deleted or unspecified sheet')
        return self._context.sheet_span(first, last)

    def _compose_reference(
        self,
        row_word: int,
        col: int,
        flags_word: int,
        relative: bool,
    ) -> Expression:
        """
        Compose one cell reference from its raw words. BIFF8 keeps the relative flags in the
        column word and the column in its low byte, the six bits between them unused; the earlier
        versions keep the flags in the row word, fourteen row bits, and eight column bits. A
        relative column is a signed byte in every version.
        """
        if self._version >= BiffVersion.BIFF8:
            row = row_word
            row_relative = bool(flags_word & 0x8000)
            col_relative = bool(flags_word & 0x4000)
        else:
            row = row_word & 0x3FFF
            row_relative = bool(flags_word & 0x8000)
            col_relative = bool(flags_word & 0x4000)
        if not relative:
            return XlA1Reference(
                row=row + 1,
                col=col + 1,
                relative_row=row_relative,
                relative_col=col_relative,
            )
        row_bits = 0x8000 if self._version >= BiffVersion.BIFF8 else 0x2000
        if row_relative and row >= row_bits:
            row -= 2 * row_bits
        if col_relative and col >= 0x80:
            col -= 0x100
        return XlR1C1Reference(
            row=row if row_relative else row + 1,
            col=col if col_relative else col + 1,
            relative_row=row_relative,
            relative_col=col_relative,
        )

    def _read_attr_data(self) -> int:
        """
        The data word of a `ptgAttr` token, which is one byte wide in BIFF2 and two bytes wide
        in every later version, for both the choose counts and their jump distances.
        """
        if self._version < BiffVersion.BIFF3:
            return self._read_u8()
        return self._read_u16()

    def _skip_mem_token(self, token: Ptg):
        """
        The memory tokens of the reference subexpressions: their payload is a size the caller
        never consumes, because the subexpression itself follows in the stream.
        """
        if token == Ptg.MEMAREA:
            if self._version < BiffVersion.BIFF3:
                self._read_bytes(3)
                self._read_u8()
            else:
                self._read_bytes(4)
                self._read_u16()
        elif token == Ptg.MEMERR or token == Ptg.MEMNOMEM:
            self._read_bytes(4 if self._version < BiffVersion.BIFF3 else 6)
        else:
            if self._version < BiffVersion.BIFF3:
                self._read_u8()
            else:
                self._read_u16()

    def _skip_mem_func(self):
        if self._version < BiffVersion.BIFF3:
            self._read_u8()
        else:
            self._read_u16()

    def _skip_reference_error(self, token: Ptg):
        """
        The broken-reference tokens: their payload mirrors the reference token of the same
        version without any cell coordinates to decode.
        """
        biff8 = self._version >= BiffVersion.BIFF8
        if token == Ptg.REFERR:
            self._read_bytes(4 if biff8 else 3)
        elif token == Ptg.AREAERR:
            self._read_bytes(8 if biff8 else 6)
        elif token == Ptg.REFERR3D:
            self._read_bytes(6 if biff8 else 17)
        else:
            self._read_bytes(10 if biff8 else 20)

Ancestors

• RpnDecoder

class FormulaSynthesizer (international=None)

Print a formula Expression as formula text. The list separator of the output follows the International it is given, so a workbook stored under another locale round-trips too.

Expand source code Browse git

class FormulaSynthesizer(Synthesizer):
    """
    Print a formula `Expression` as formula text. The list separator of the output follows the
    `International` it is given, so a workbook stored under another locale round-trips too.
    """

    def __init__(self, international: International | None = None):
        international = international or International()
        super().__init__()
        self._sep = international.list_separator
        self._lb = international.left_bracket
        self._rb = international.right_bracket

    def convert(self, node: Node) -> str:
        if isinstance(node, XlBinaryExpression) and node.operator is XlBinaryOperator.UNION:
            node = XlParenExpression(operand=node)
        with RecursionDepth(TREE_RECURSION_DEPTH):
            return super().convert(node)

    def _emit_operand(self, operand: Expression | None, parent: int, left: bool):
        if operand is None:
            return
        if isinstance(operand, XlBinaryExpression):
            if operand.operator is XlBinaryOperator.UNION:
                self._write('(')
                self.visit(operand)
                self._write(')')
                return
            precedence = _PRECEDENCE[operand.operator]
            if precedence < parent or (precedence == parent and not left):
                self._write('(')
                self.visit(operand)
                self._write(')')
                return
        elif isinstance(operand, XlUnaryExpression):
            if _PRECEDENCE[operand.operator] < parent:
                self._write('(')
                self.visit(operand)
                self._write(')')
                return
        self.visit(operand)

    def visit_XlNumber(self, node: XlNumber):
        self._write(self._format_number(node))

    def visit_XlString(self, node: XlString):
        escaped = node.value.replace('"', '""')
        self._write(F'"{escaped}"')

    def visit_XlBoolean(self, node: XlBoolean):
        self._write('TRUE' if node.value else 'FALSE')

    def visit_XlError(self, node: XlError):
        self._write(node.value)

    def visit_XlBinaryExpression(self, node: XlBinaryExpression):
        precedence = _PRECEDENCE[node.operator]
        text = ' ' if node.operator is XlBinaryOperator.ISECT else node.operator.value
        self._emit_operand(node.left, precedence, left=True)
        self._write(text)
        self._emit_operand(node.right, precedence, left=False)

    def visit_XlUnaryExpression(self, node: XlUnaryExpression):
        if node.operator is XlUnaryOperator.PERCENT:
            self._emit_operand(node.operand, _PRECEDENCE[node.operator], left=False)
            self._write(node.operator.value)
            return
        self._write(node.operator.value)
        self._emit_operand(node.operand, _PRECEDENCE[node.operator], left=True)

    def visit_XlParenExpression(self, node: XlParenExpression):
        self._write('(')
        if node.operand is not None:
            self.visit(node.operand)
        self._write(')')

    def visit_XlMissingArgument(self, node: XlMissingArgument):
        pass

    def visit_XlUnparsedFormula(self, node: XlUnparsedFormula):
        self._write(node.text)

    def visit_XlA1Reference(self, node: XlA1Reference):
        if node.sheets is not None:
            self._write(_qualify(node.sheets))
        column = column_letters(node.col)
        row = F'${node.row}' if not node.relative_row else str(node.row)
        col = F'${column}' if not node.relative_col else column
        self._write(F'{col}{row}')

    def visit_XlR1C1Reference(self, node: XlR1C1Reference):
        if node.sheets is not None:
            self._write(_qualify(node.sheets))
        self._write(
            F'{self._axis("R", node.row, node.relative_row)}'
            F'{self._axis("C", node.col, node.relative_col)}'
        )

    def visit_XlDefinedName(self, node: XlDefinedName):
        if node.sheet is not None:
            self._write(_qualify((node.sheet,)))
        self._write(node.name)

    def visit_XlFunctionCall(self, node: XlFunctionCall):
        if isinstance(node.callee, str):
            self._write(node.callee)
        else:
            self.visit(node.callee)
        self._write('(')
        for index, argument in enumerate(node.arguments):
            if index:
                self._write(self._sep)
            # a call argument sits in a fully delimited position, so no precedence asks for
            # parentheses around it — only a union does, because bare it would read as two
            self._emit_operand(argument, 0, left=False)
        self._write(')')

    def visit_XlArrayConstant(self, node: XlArrayConstant):
        self._write('{')
        for rindex, row in enumerate(node.rows):
            if rindex:
                self._write(';')
            for cindex, item in enumerate(row):
                if cindex:
                    self._write(self._sep)
                self.visit(item)
        self._write('}')

    def _axis(self, letter: str, value: int, relative: bool) -> str:
        if not relative:
            return F'{letter}{value}'
        if value:
            return F'{letter}{self._lb}{value}{self._rb}'
        return letter

    def _format_number(self, node: XlNumber) -> str:
        try:
            if float(node.raw) == node.value:
                return node.raw
        except (TypeError, ValueError):
            pass
        if isinstance(node.value, int):
            return str(node.value)
        return repr(node.value)

Ancestors

• Synthesizer
• Visitor

Methods

def convert(self, node)

Expand source code Browse git

def convert(self, node: Node) -> str:
    if isinstance(node, XlBinaryExpression) and node.operator is XlBinaryOperator.UNION:
        node = XlParenExpression(operand=node)
    with RecursionDepth(TREE_RECURSION_DEPTH):
        return super().convert(node)

def visit_XlNumber(self, node)

Expand source code Browse git

def visit_XlNumber(self, node: XlNumber):
    self._write(self._format_number(node))

def visit_XlString(self, node)

Expand source code Browse git

def visit_XlString(self, node: XlString):
    escaped = node.value.replace('"', '""')
    self._write(F'"{escaped}"')

def visit_XlBoolean(self, node)

Expand source code Browse git

def visit_XlBoolean(self, node: XlBoolean):
    self._write('TRUE' if node.value else 'FALSE')

def visit_XlError(self, node)

Expand source code Browse git

def visit_XlError(self, node: XlError):
    self._write(node.value)

def visit_XlBinaryExpression(self, node)

Expand source code Browse git

def visit_XlBinaryExpression(self, node: XlBinaryExpression):
    precedence = _PRECEDENCE[node.operator]
    text = ' ' if node.operator is XlBinaryOperator.ISECT else node.operator.value
    self._emit_operand(node.left, precedence, left=True)
    self._write(text)
    self._emit_operand(node.right, precedence, left=False)

def visit_XlUnaryExpression(self, node)

Expand source code Browse git

def visit_XlUnaryExpression(self, node: XlUnaryExpression):
    if node.operator is XlUnaryOperator.PERCENT:
        self._emit_operand(node.operand, _PRECEDENCE[node.operator], left=False)
        self._write(node.operator.value)
        return
    self._write(node.operator.value)
    self._emit_operand(node.operand, _PRECEDENCE[node.operator], left=True)

def visit_XlParenExpression(self, node)

Expand source code Browse git

def visit_XlParenExpression(self, node: XlParenExpression):
    self._write('(')
    if node.operand is not None:
        self.visit(node.operand)
    self._write(')')

def visit_XlMissingArgument(self, node)

Expand source code Browse git

def visit_XlMissingArgument(self, node: XlMissingArgument):
    pass

def visit_XlUnparsedFormula(self, node)

Expand source code Browse git

def visit_XlUnparsedFormula(self, node: XlUnparsedFormula):
    self._write(node.text)

def visit_XlA1Reference(self, node)

Expand source code Browse git

def visit_XlA1Reference(self, node: XlA1Reference):
    if node.sheets is not None:
        self._write(_qualify(node.sheets))
    column = column_letters(node.col)
    row = F'${node.row}' if not node.relative_row else str(node.row)
    col = F'${column}' if not node.relative_col else column
    self._write(F'{col}{row}')

def visit_XlR1C1Reference(self, node)

Expand source code Browse git

def visit_XlR1C1Reference(self, node: XlR1C1Reference):
    if node.sheets is not None:
        self._write(_qualify(node.sheets))
    self._write(
        F'{self._axis("R", node.row, node.relative_row)}'
        F'{self._axis("C", node.col, node.relative_col)}'
    )

def visit_XlDefinedName(self, node)

Expand source code Browse git

def visit_XlDefinedName(self, node: XlDefinedName):
    if node.sheet is not None:
        self._write(_qualify((node.sheet,)))
    self._write(node.name)

def visit_XlFunctionCall(self, node)

Expand source code Browse git

def visit_XlFunctionCall(self, node: XlFunctionCall):
    if isinstance(node.callee, str):
        self._write(node.callee)
    else:
        self.visit(node.callee)
    self._write('(')
    for index, argument in enumerate(node.arguments):
        if index:
            self._write(self._sep)
        # a call argument sits in a fully delimited position, so no precedence asks for
        # parentheses around it — only a union does, because bare it would read as two
        self._emit_operand(argument, 0, left=False)
    self._write(')')

def visit_XlArrayConstant(self, node)

Expand source code Browse git

def visit_XlArrayConstant(self, node: XlArrayConstant):
    self._write('{')
    for rindex, row in enumerate(node.rows):
        if rindex:
            self._write(';')
        for cindex, item in enumerate(row):
            if cindex:
                self._write(self._sep)
            self.visit(item)
    self._write('}')

Inherited members

• Synthesizer:
  • visit

class International (list_separator=',', left_bracket='[', right_bracket=']')

The locale-dependent characters of formula text: the separator between the arguments of a call and between the columns of an array constant, and the brackets around a relative R1C1 offset.

Expand source code Browse git

class International(NamedTuple):
    """
    The locale-dependent characters of formula text: the separator between the arguments of a
    call and between the columns of an array constant, and the brackets around a relative R1C1
    offset.
    """

    list_separator: str = ','
    left_bracket: str = '['
    right_bracket: str = ']'

Ancestors

• builtins.tuple

Instance variables

var list_separator

Alias for field number 0

Expand source code Browse git

class International(NamedTuple):
    """
    The locale-dependent characters of formula text: the separator between the arguments of a
    call and between the columns of an array constant, and the brackets around a relative R1C1
    offset.
    """

    list_separator: str = ','
    left_bracket: str = '['
    right_bracket: str = ']'

var left_bracket

Alias for field number 1

Expand source code Browse git

class International(NamedTuple):
    """
    The locale-dependent characters of formula text: the separator between the arguments of a
    call and between the columns of an array constant, and the brackets around a relative R1C1
    offset.
    """

    list_separator: str = ','
    left_bracket: str = '['
    right_bracket: str = ']'

var right_bracket

Alias for field number 2

Expand source code Browse git

class International(NamedTuple):
    """
    The locale-dependent characters of formula text: the separator between the arguments of a
    call and between the columns of an array constant, and the brackets around a relative R1C1
    offset.
    """

    list_separator: str = ','
    left_bracket: str = '['
    right_bracket: str = ']'

class XlArrayConstant (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, rows=<factory>)

A constant array literal such as {1,2;3,4}. Each row is a tuple of literals; the list separator divides columns and the semicolon divides rows. The rows are tuples rather than lists so that the node framework recognizes the nesting as children.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlArrayConstant(Expression):
    """
    A constant array literal such as `{1,2;3,4}`. Each row is a tuple of literals; the list
    separator divides columns and the semicolon divides rows. The rows are tuples rather than
    lists so that the node framework recognizes the nesting as children.
    """

    rows: list[tuple[XlLiteral, ...]] = field(default_factory=list)

Ancestors

• Expression
• Node

Instance variables

var rows

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlA1Reference (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, sheets=None, row=1, col=1, relative_row=True, relative_col=True)

A cell reference in A1 notation. The row and column are one-based absolute numbers, and the relative flags say which axes Excel copies the reference along; sheets holds one name for a qualified reference, two for the 3-D span that Sheet1:Sheet2!A1 spells, and nothing for a reference local to the sheet the formula sits on.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlA1Reference(Expression):
    """
    A cell reference in A1 notation. The row and column are one-based absolute numbers, and the
    relative flags say which axes Excel copies the reference along; `sheets` holds one name for
    a qualified reference, two for the 3-D span that `Sheet1:Sheet2!A1` spells, and nothing for
    a reference local to the sheet the formula sits on.
    """

    sheets: tuple[str, ...] | None = None
    row: int = 1
    col: int = 1
    relative_row: bool = True
    relative_col: bool = True

Ancestors

• Expression
• Node

Instance variables

var sheets

The type of the None singleton.

var row

The type of the None singleton.

var col

The type of the None singleton.

var relative_row

The type of the None singleton.

var relative_col

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlBinaryExpression (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, left=None, operator=XlBinaryOperator.ADD, right=None)

XlBinaryExpression(offset: 'int' = -1, parent: 'Node | None' = None, leading_comments: 'list[str]' = , trailing_comments: 'list[str]' = , left: 'Expression | None' = None, operator: 'XlBinaryOperator' = <XlBinaryOperator.ADD: '+'>, right: 'Expression | None' = None)

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlBinaryExpression(Expression):
    left: Expression | None = None
    operator: XlBinaryOperator = XlBinaryOperator.ADD
    right: Expression | None = None

Ancestors

• Expression
• Node

Instance variables

var left

The type of the None singleton.

var operator

The type of the None singleton.

var right

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlBinaryOperator (*args, **kwds)

An operator written between two operands. The three reference operators are members of the same family: a range spans, an intersection narrows to shared cells, and a union gathers.

Expand source code Browse git

class XlBinaryOperator(enum.Enum):
    """
    An operator written between two operands. The three reference operators are members of the
    same family: a range spans, an intersection narrows to shared cells, and a union gathers.
    """

    ADD = '+'
    SUB = '-'
    MUL = '*'
    DIV = '/'
    POW = '^'
    CONCAT = '&'
    RANGE = ':'
    ISECT = ' '
    UNION = ','
    EQ = '='
    NE = '<>'
    LT = '<'
    LE = '<='
    GT = '>'
    GE = '>='

Ancestors

• enum.Enum

Class variables

var ADD

The type of the None singleton.

var SUB

The type of the None singleton.

var MUL

The type of the None singleton.

var DIV

The type of the None singleton.

var POW

The type of the None singleton.

var CONCAT

The type of the None singleton.

var RANGE

The type of the None singleton.

var ISECT

The type of the None singleton.

var UNION

The type of the None singleton.

var EQ

The type of the None singleton.

var NE

The type of the None singleton.

var LT

The type of the None singleton.

var LE

The type of the None singleton.

var GT

The type of the None singleton.

var GE

The type of the None singleton.

class XlBoolean (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, value=False)

XlBoolean(offset: 'int' = -1, parent: 'Node | None' = None, leading_comments: 'list[str]' = , trailing_comments: 'list[str]' = , value: 'bool' = False)

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlBoolean(Expression):
    value: bool = False

Ancestors

• Expression
• Node

Instance variables

var value

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlDefinedName (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, name='', sheet=None)

A reference to a defined name, qualified by the sheet that scoped it when it is not global. The _xlfn. and _xlnm. prefixes Excel writes for future and built-in names are part of the name and preserved verbatim.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlDefinedName(Expression):
    """
    A reference to a defined name, qualified by the sheet that scoped it when it is not global.
    The `_xlfn.` and `_xlnm.` prefixes Excel writes for future and built-in names are part of
    the name and preserved verbatim.
    """

    name: str = ''
    sheet: str | None = None

Ancestors

• Expression
• Node

Instance variables

var name

The type of the None singleton.

var sheet

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlError (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, value='#VALUE!')

An error literal such as #N/A; the text domain is the one ERROR_TEXT decodes cell errors into.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlError(Expression):
    """
    An error literal such as `#N/A`; the text domain is the one `ERROR_TEXT` decodes cell
    errors into.
    """

    value: str = '#VALUE!'

Ancestors

• Expression
• Node

Instance variables

var value

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlFunctionCall (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, callee='', arguments=<factory>)

A call to a function or macro. The callee is normally the function name; a defined name or a reference can also be called, because a macro is a cell and R1C1() is the call to the macro in the cell that reference points at.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlFunctionCall(Expression):
    """
    A call to a function or macro. The callee is normally the function name; a defined name or
    a reference can also be called, because a macro is a cell and `R1C1()` is the call to the
    macro in the cell that reference points at.
    """

    callee: str | XlDefinedName | XlA1Reference | XlR1C1Reference = ''
    arguments: list[Expression] = field(default_factory=list)

    def canonical_form(self) -> Expression | None:
        # a defined name in the callee position spells no differently than a function name, so
        # text cannot tell the two apart and comparing programs must not either
        if isinstance(self.callee, XlDefinedName):
            return XlFunctionCall(callee=self.callee.name, arguments=self.arguments)
        return None

Ancestors

• Expression
• Node

Instance variables

var arguments

The type of the None singleton.

var callee

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlMissingArgument (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>)

An argument a call omits: IF(a,,b) passes nothing in the middle. It prints no text.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlMissingArgument(Expression):
    """
    An argument a call omits: `IF(a,,b)` passes nothing in the middle. It prints no text.
    """

Ancestors

• Expression
• Node

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlNumber (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, value=0, raw='')

XlNumber(offset: 'int' = -1, parent: 'Node | None' = None, leading_comments: 'list[str]' = , trailing_comments: 'list[str]' = , value: 'int | float' = 0, raw: 'str' = '')

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlNumber(Expression, spelling='raw'):
    value: int | float = 0
    raw: str = ''

Ancestors

• Expression
• Node

Instance variables

var value

The type of the None singleton.

var raw

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlParenExpression (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, operand=None)

A parenthesis the source wrote around an operand. It spells nothing of its own, so comparing programs identifies it with what it holds.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlParenExpression(Expression):
    """
    A parenthesis the source wrote around an operand. It spells nothing of its own, so comparing
    programs identifies it with what it holds.
    """

    operand: Expression | None = None

    def canonical_form(self) -> Expression | None:
        return self.operand

Ancestors

• Expression
• Node

Instance variables

var operand

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlR1C1Reference (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, sheets=None, row=1, col=1, relative_row=True, relative_col=True)

A cell reference in R1C1 notation. When a relative flag is set, the number on that axis is an offset from the cell that contains the formula rather than an absolute position, so only the macrosheet layer that knows the containing cell can resolve it. The sheets field carries the same qualification a XlA1Reference does.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlR1C1Reference(Expression):
    """
    A cell reference in R1C1 notation. When a relative flag is set, the number on that axis is
    an offset from the cell that contains the formula rather than an absolute position, so only
    the macrosheet layer that knows the containing cell can resolve it. The `sheets` field
    carries the same qualification a `XlA1Reference` does.
    """

    sheets: tuple[str, ...] | None = None
    row: int = 1
    col: int = 1
    relative_row: bool = True
    relative_col: bool = True

Ancestors

• Expression
• Node

Instance variables

var sheets

The type of the None singleton.

var row

The type of the None singleton.

var col

The type of the None singleton.

var relative_row

The type of the None singleton.

var relative_col

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlString (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, value='')

XlString(offset: 'int' = -1, parent: 'Node | None' = None, leading_comments: 'list[str]' = , trailing_comments: 'list[str]' = , value: 'str' = '')

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlString(Expression):
    value: str = ''

Ancestors

• Expression
• Node

Instance variables

var value

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlUnaryExpression (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, operator=XlUnaryOperator.NEG, operand=None)

XlUnaryExpression(offset: 'int' = -1, parent: 'Node | None' = None, leading_comments: 'list[str]' = , trailing_comments: 'list[str]' = , operator: 'XlUnaryOperator' = <XlUnaryOperator.NEG: '-'>, operand: 'Expression | None' = None)

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlUnaryExpression(Expression):
    operator: XlUnaryOperator = XlUnaryOperator.NEG
    operand: Expression | None = None

Ancestors

• Expression
• Node

Instance variables

var operator

The type of the None singleton.

var operand

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlUnaryOperator (*args, **kwds)

An operator written before or after one operand.

Expand source code Browse git

class XlUnaryOperator(enum.Enum):
    """
    An operator written before or after one operand.
    """

    NEG = '-'
    POS = '+'
    PERCENT = '%'

Ancestors

• enum.Enum

Class variables

var NEG

The type of the None singleton.

var POS

The type of the None singleton.

var PERCENT

The type of the None singleton.

class XlUnparsedFormula (offset=-1, parent=None, leading_comments=<factory>, trailing_comments=<factory>, text='')

The carrier for a formula source the decoders or the parser could not read: hostile or truncated text, a token stream that does not decode. It prints the text it was handed.

Expand source code Browse git

@dataclass(repr=False, eq=False)
class XlUnparsedFormula(Expression, unparsed=True):
    """
    The carrier for a formula source the decoders or the parser could not read: hostile or
    truncated text, a token stream that does not decode. It prints the text it was handed.
    """

    text: str = ''

Ancestors

• Expression
• Node

Instance variables

var text

The type of the None singleton.

Inherited members

• Expression:
  • canonical_form
  • canonical_type
  • children
  • early_errors
  • has_spelling
  • is_recovered
  • leading_comments
  • offset
  • parent
  • spelling_fields
  • trailing_comments
  • unparsed
  • walk_in_order

class XlsbRpnDecoder (view, context=None)

The stack machine over the formula stream of an MS-XLSB cell or name record. A reference token stores absolute coordinates whatever its relative flags say, because the flags only tell which axes Excel copies the reference along; the context resolves defined names and the extern-sheet indexes of 3-D references.

Expand source code Browse git

class XlsbRpnDecoder(RpnDecoder):
    """
    The stack machine over the formula stream of an MS-XLSB cell or name record. A reference
    token stores absolute coordinates whatever its relative flags say, because the flags only
    tell which axes Excel copies the reference along; the context resolves defined names and
    the extern-sheet indexes of 3-D references.
    """

    _context: RpnContext | None

    def _read_string(self) -> XlString:
        """
        The `ptgStr` token: a two-byte character count followed by UTF-16 characters.
        """
        count = self._read_u16()
        return XlString(value=self._read_text(2 * count, 'utf_16_le'))

    def _read_function_id(self) -> int:
        return self._read_u16()

    def _read_function_variable(self) -> tuple[int, int, bool]:
        """
        The payload of a `ptgFuncVar` token: an argument count whose top bit only marks a
        pending prompt, then a two-byte identifier. An identifier of 255 — with or without the
        macro bit, which selects the macro-command half of the id space — names a user-defined
        call whose name operand sits below the arguments on the stack.
        """
        argc = self._read_u8() & 0x7F
        index = self._read_u16()
        return index, argc, (index & 0x7FFF) == 0xFF

    def _read_defined_name(self) -> Expression:
        """
        The `ptgName` token: a one-based index into the name table, padded with two unused
        bytes.
        """
        index = self._read_u16()
        self._read_bytes(2)
        if self._context is None:
            raise RpnError('a name token has no workbook to resolve it')
        return self._context.defined_name(index)

    def _read_reference(self, relative: bool) -> Expression:
        """
        The `ptgRef` and `ptgRefN` tokens: a four-byte row and a two-byte column whose top
        bits carry the relative flags. The grammar stores the absolute position in both
        spellings; the containing cell of a copied formula is what the flags select for.
        """
        return self._compose(self._read_u32(), self._read_u16())

    def _read_area(self, relative: bool) -> Expression:
        """
        The `ptgArea` and `ptgAreaN` tokens: two corners of a rectangle, each carrying the
        reference layout of the grammar.
        """
        first_row = self._read_u32()
        second_row = self._read_u32()
        first_col_word = self._read_u16()
        second_col_word = self._read_u16()
        return XlBinaryExpression(
            left=self._compose(first_row, first_col_word),
            operator=XlBinaryOperator.RANGE,
            right=self._compose(second_row, second_col_word),
        )

    def _read_3d_reference(self) -> Expression:
        """
        The `ptgRef3d` token: an extern-sheet index followed by a reference.
        """
        sheets = self._read_3d_sheets()
        return self._qualify(self._read_reference(False), sheets)

    def _read_3d_area(self) -> Expression:
        """
        The `ptgArea3d` token: an extern-sheet index followed by an area.
        """
        sheets = self._read_3d_sheets()
        return self._qualify(self._read_area(False), sheets)

    def _read_3d_sheets(self) -> tuple[str, ...]:
        if self._context is None:
            raise RpnError('a 3-D reference has no workbook to resolve its sheets')
        return self._context.extern_sheets(self._read_u16())

    @staticmethod
    def _compose(row: int, col_word: int) -> XlA1Reference:
        return XlA1Reference(
            row=row + 1,
            col=(col_word & 0x3FFF) + 1,
            relative_row=bool(col_word & 0x8000),
            relative_col=bool(col_word & 0x4000),
        )

    def _read_array(self) -> Expression:
        """
        The `ptgArray` token: a column count in which zero spells two hundred fifty-six, a row
        count, and then the constants in row-major order, each announced by a type byte.
        """
        cols = self._read_u8()
        if cols == 0:
            cols = 256
        rows = self._read_u16()
        constants = []
        for _ in range(cols * rows):
            kind = self._read_u8()
            if kind == 0x01:
                number = self._read_double()
                # a double that holds an integer is an integer literal: the synthesizer prints
                # it without the decimal point, as Excel writes it
                if number.is_integer():
                    number = int(number)
                constants.append(XlNumber(value=number))
            elif kind == 0x02:
                count = self._read_u16()
                constants.append(XlString(value=self._read_text(2 * count, 'utf_16_le')))
            else:
                raise RpnError(F'an array constant of type {kind:#x} holds no literal')
        return XlArrayConstant(
            rows=[tuple(constants[index:index + cols]) for index in range(0, len(constants), cols)]
        )

    def _skip_mem_token(self, token: Ptg) -> None:
        """
        The memory tokens of the reference subexpressions: a length word every one of them
        carries, with the memory-area tokens additionally announcing the rectangles of the
        subexpression that follows, which this reader only has to step over.
        """
        if token == Ptg.MEMAREA:
            self._read_bytes(4)
            size = self._read_u16()
            if size:
                count = self._read_u16()
                self._read_bytes(12 * count)
        elif token == Ptg.MEMERR or token == Ptg.MEMNOMEM:
            self._read_bytes(4)
            self._read_bytes(self._read_u16())
        else:
            self._read_bytes(self._read_u16())

    def _skip_mem_func(self) -> None:
        self._read_bytes(self._read_u16())

    def _skip_reference_error(self, token: Ptg) -> None:
        """
        The broken-reference tokens: reserved words as wide as the reference token of the same
        kind, which is one reference layout wider than the BIFF8 pair.
        """
        if token == Ptg.REFERR:
            self._read_bytes(6)
        elif token == Ptg.AREAERR:
            self._read_bytes(12)
        elif token == Ptg.REFERR3D:
            self._read_bytes(8)
        else:
            self._read_bytes(14)

Ancestors

• RpnDecoder