FormulaParser::parseToTokens() - Code Metrics - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

FormulaParser::parseToTokens() F
last analyzed 2025-06-01 16:52 UTC

↳ Parent: FormulaParser

Complexity

Conditions	99
Paths	> 20000

Size

Total Lines	494
Code Lines	289

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	247
CRAP Score	105.0616

Importance

Changes

Metric	Value
eloc	289
dl	0
loc	494
ccs	247
cts	270
cp	0.9148
rs	0
c	0
b	0
f	0
cc	99
nc	242551
nop	0
crap	105.0616

How to fix Long Method Complexity

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation;

/**
 * PARTLY BASED ON:
 * Copyright (c) 2007 E. W. Bachtal, Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy of this software
 * and associated documentation files (the "Software"), to deal in the Software without restriction,
 * including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
 * subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all copies or substantial
 * portions of the Software.
 *
 * The software is provided "as is", without warranty of any kind, express or implied, including but not
 * limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In
 * no event shall the authors or copyright holders be liable for any claim, damages or other liability,
 * whether in an action of contract, tort or otherwise, arising from, out of or in connection with the
 * software or the use or other dealings in the software.
 *
 * https://ewbi.blogs.com/develops/2007/03/excel_formula_p.html
 * https://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
 */
class FormulaParser
{
    // Character constants
    const QUOTE_DOUBLE = '"';
    const QUOTE_SINGLE = '\'';
    const BRACKET_CLOSE = ']';
    const BRACKET_OPEN = '[';
    const BRACE_OPEN = '{';
    const BRACE_CLOSE = '}';
    const PAREN_OPEN = '(';
    const PAREN_CLOSE = ')';
    const SEMICOLON = ';';
    const WHITESPACE = ' ';
    const COMMA = ',';
    const ERROR_START = '#';

    const OPERATORS_SN = '+-';
    const OPERATORS_INFIX = '+-*/^&=><';
    const OPERATORS_POSTFIX = '%';

    /**
     * Formula.
     */
    private string $formula;

    /**
     * Tokens.
     *
     * @var FormulaToken[]
     */
    private array $tokens = [];

    /**
     * Create a new FormulaParser.
     *
     * @param ?string $formula Formula to parse
     */
    public function __construct(?string $formula = '')
    {
        // Check parameters
        if ($formula === null) {
            throw new Exception('Invalid parameter passed: formula');
        }

        // Initialise values
        $this->formula = trim($formula);
        // Parse!
        $this->parseToTokens();
    }

    /**
     * Get Formula.
     */
    public function getFormula(): string
    {
        return $this->formula;
    }

    /**
     * Get Token.
     *
     * @param int $id Token id
     */
    public function getToken(int $id = 0): FormulaToken
    {
        if (isset($this->tokens[$id])) {
            return $this->tokens[$id];
        }

        throw new Exception("Token with id $id does not exist.");
    }

    /**
     * Get Token count.
     */
    public function getTokenCount(): int
    {
        return count($this->tokens);
    }

    /**
     * Get Tokens.
     *
     * @return FormulaToken[]
     */
    public function getTokens(): array
    {
        return $this->tokens;
    }

    /**
     * Parse to tokens.
     */
    private function parseToTokens(): void
    {
        // No attempt is made to verify formulas; assumes formulas are derived from Excel, where
        // they can only exist if valid; stack overflows/underflows sunk as nulls without exceptions.

        // Check if the formula has a valid starting =
        $formulaLength = strlen($this->formula);
        if ($formulaLength < 2 || $this->formula[0] != '=') {
            return;
        }

        // Helper variables
        $tokens1 = $tokens2 = $stack = [];
        $inString = $inPath = $inRange = $inError = false;
        $nextToken = null;
        //$token = $previousToken = null;

        $index = 1;
        $value = '';

        $ERRORS = ['#NULL!', '#DIV/0!', '#VALUE!', '#REF!', '#NAME?', '#NUM!', '#N/A'];
        $COMPARATORS_MULTI = ['>=', '<=', '<>'];

        while ($index < $formulaLength) {
            // state-dependent character evaluation (order is important)

            // double-quoted strings
            // embeds are doubled
            // end marks token
            if ($inString) {
                if ($this->formula[$index] == self::QUOTE_DOUBLE) {
                    if ((($index + 2) <= $formulaLength) && ($this->formula[$index + 1] == self::QUOTE_DOUBLE)) {
                        $value .= self::QUOTE_DOUBLE;
                        ++$index;
                    } else {
                        $inString = false;
                        $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND, FormulaToken::TOKEN_SUBTYPE_TEXT);
                        $value = '';
                    }
                } else {
                    $value .= $this->formula[$index];
                }
                ++$index;

                continue;
            }

            // single-quoted strings (links)
            // embeds are double
            // end does not mark a token
            if ($inPath) {
                if ($this->formula[$index] == self::QUOTE_SINGLE) {
                    if ((($index + 2) <= $formulaLength) && ($this->formula[$index + 1] == self::QUOTE_SINGLE)) {
                        $value .= self::QUOTE_SINGLE;
                        ++$index;
                    } else {
                        $inPath = false;
                    }
                } else {
                    $value .= $this->formula[$index];
                }
                ++$index;

                continue;
            }

            // bracked strings (R1C1 range index or linked workbook name)
            // no embeds (changed to "()" by Excel)
            // end does not mark a token
            if ($inRange) {
                if ($this->formula[$index] == self::BRACKET_CLOSE) {
                    $inRange = false;
                }
                $value .= $this->formula[$index];
                ++$index;

                continue;
            }

            // error values
            // end marks a token, determined from absolute list of values
            if ($inError) {
                $value .= $this->formula[$index];
                ++$index;
                if (in_array($value, $ERRORS)) {
                    $inError = false;
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND, FormulaToken::TOKEN_SUBTYPE_ERROR);
                    $value = '';
                }

                continue;
            }

            // scientific notation check
            if (str_contains(self::OPERATORS_SN, $this->formula[$index])) {
                if (strlen($value) > 1) {
                    if (preg_match('/^[1-9]{1}(\.\d+)?E{1}$/', $this->formula[$index]) != 0) {
                        $value .= $this->formula[$index];
                        ++$index;

                        continue;
                    }
                }
            }

            // independent character evaluation (order not important)

            // establish state-dependent character evaluations
            if ($this->formula[$index] == self::QUOTE_DOUBLE) {
                if ($value !== '') {
                    // unexpected
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
                    $value = '';
                }
                $inString = true;
                ++$index;

                continue;
            }

            if ($this->formula[$index] == self::QUOTE_SINGLE) {
                if ($value !== '') {
                    // unexpected
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
                    $value = '';
                }
                $inPath = true;
                ++$index;

                continue;
            }

            if ($this->formula[$index] == self::BRACKET_OPEN) {
                $inRange = true;
                $value .= self::BRACKET_OPEN;
                ++$index;

                continue;
            }

            if ($this->formula[$index] == self::ERROR_START) {
                if ($value !== '') {
                    // unexpected
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
                    $value = '';
                }
                $inError = true;
                $value .= self::ERROR_START;
                ++$index;

                continue;
            }

            // mark start and end of arrays and array rows
            if ($this->formula[$index] == self::BRACE_OPEN) {
                if ($value !== '') {
                    // unexpected
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
                    $value = '';
                }

                $tmp = new FormulaToken('ARRAY', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
                $tokens1[] = $tmp;
                $stack[] = clone $tmp;

                $tmp = new FormulaToken('ARRAYROW', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
                $tokens1[] = $tmp;
                $stack[] = clone $tmp;

                ++$index;

                continue;
            }

            if ($this->formula[$index] == self::SEMICOLON) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }

                /** @var FormulaToken $tmp */
                $tmp = array_pop($stack);
                $tmp->setValue('');
                $tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
                $tokens1[] = $tmp;

                $tmp = new FormulaToken(',', FormulaToken::TOKEN_TYPE_ARGUMENT);
                $tokens1[] = $tmp;

                $tmp = new FormulaToken('ARRAYROW', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
                $tokens1[] = $tmp;
                $stack[] = clone $tmp;

                ++$index;

                continue;
            }

            if ($this->formula[$index] == self::BRACE_CLOSE) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }

                /** @var FormulaToken $tmp */
                $tmp = array_pop($stack);
                $tmp->setValue('');
                $tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
                $tokens1[] = $tmp;

                /** @var FormulaToken $tmp */
                $tmp = array_pop($stack);
                $tmp->setValue('');
                $tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
                $tokens1[] = $tmp;

                ++$index;

                continue;
            }

            // trim white-space
            if ($this->formula[$index] == self::WHITESPACE) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }
                $tokens1[] = new FormulaToken('', FormulaToken::TOKEN_TYPE_WHITESPACE);
                ++$index;
                while (($this->formula[$index] == self::WHITESPACE) && ($index < $formulaLength)) {
                    ++$index;
                }

                continue;
            }

            // multi-character comparators
            if (($index + 2) <= $formulaLength) {
                if (in_array(substr($this->formula, $index, 2), $COMPARATORS_MULTI)) {
                    if ($value !== '') {
                        $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                        $value = '';
                    }
                    $tokens1[] = new FormulaToken(substr($this->formula, $index, 2), FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_LOGICAL);
                    $index += 2;

                    continue;
                }
            }

            // standard infix operators
            if (str_contains(self::OPERATORS_INFIX, $this->formula[$index])) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }
                $tokens1[] = new FormulaToken($this->formula[$index], FormulaToken::TOKEN_TYPE_OPERATORINFIX);
                ++$index;

                continue;
            }

            // standard postfix operators (only one)
            if (str_contains(self::OPERATORS_POSTFIX, $this->formula[$index])) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }
                $tokens1[] = new FormulaToken($this->formula[$index], FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX);
                ++$index;

                continue;
            }

            // start subexpression or function
            if ($this->formula[$index] == self::PAREN_OPEN) {
                if ($value !== '') {
                    $tmp = new FormulaToken($value, FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
                    $tokens1[] = $tmp;
                    $stack[] = clone $tmp;
                    $value = '';
                } else {
                    $tmp = new FormulaToken('', FormulaToken::TOKEN_TYPE_SUBEXPRESSION, FormulaToken::TOKEN_SUBTYPE_START);
                    $tokens1[] = $tmp;
                    $stack[] = clone $tmp;
                }
                ++$index;

                continue;
            }

            // function, subexpression, or array parameters, or operand unions
            if ($this->formula[$index] == self::COMMA) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }

                /** @var FormulaToken $tmp */
                $tmp = array_pop($stack);
                $tmp->setValue('');
                $tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
                $stack[] = $tmp;

                if ($tmp->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) {
                    $tokens1[] = new FormulaToken(',', FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_UNION);
                } else {
                    $tokens1[] = new FormulaToken(',', FormulaToken::TOKEN_TYPE_ARGUMENT);
                }
                ++$index;

                continue;
            }

            // stop subexpression
            if ($this->formula[$index] == self::PAREN_CLOSE) {
                if ($value !== '') {
                    $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
                    $value = '';
                }

                /** @var FormulaToken $tmp */
                $tmp = array_pop($stack);
                $tmp->setValue('');
                $tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
                $tokens1[] = $tmp;

                ++$index;

                continue;
            }

            // token accumulation
            $value .= $this->formula[$index];
            ++$index;
        }

        // dump remaining accumulation
        if ($value !== '') {
            $tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
        }

        // move tokenList to new set, excluding unnecessary white-space tokens and converting necessary ones to intersections
        $tokenCount = count($tokens1);
        for ($i = 0; $i < $tokenCount; ++$i) {
            $token = $tokens1[$i];
            if (isset($tokens1[$i - 1])) {
                $previousToken = $tokens1[$i - 1];
            } else {
                $previousToken = null;
            }
            if (isset($tokens1[$i + 1])) {
                $nextToken = $tokens1[$i + 1];
            } else {
                $nextToken = null;
            }

            if ($token->getTokenType() != FormulaToken::TOKEN_TYPE_WHITESPACE) {
                $tokens2[] = $token;

                continue;
            }

            if ($previousToken === null) {
                continue;
            }

            if (
                !(
                    (($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                || (($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                || ($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
                )
            ) {
                continue;
            }

            if ($nextToken === null) {
                continue;
            }

            if (
                !(
                    (($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) && ($nextToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_START))
                || (($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($nextToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_START))
                || ($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
                )
            ) {
                continue;
            }

            $tokens2[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_INTERSECTION);
        }

        // move tokens to final list, switching infix "-" operators to prefix when appropriate, switching infix "+" operators
        // to noop when appropriate, identifying operand and infix-operator subtypes, and pulling "@" from function names
        $this->tokens = [];

        $tokenCount = count($tokens2);
        for ($i = 0; $i < $tokenCount; ++$i) {
            $token = $tokens2[$i];
            if (isset($tokens2[$i - 1])) {
                $previousToken = $tokens2[$i - 1];
            } else {
                $previousToken = null;
            }

            if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == '-') {
                if ($i == 0) {
                    $token->setTokenType(FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
                } elseif (
                    (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION)
                        && ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                    || (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION)
                        && ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                    || ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX)
                    || ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
                ) {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
                } else {
                    $token->setTokenType(FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
                }

                $this->tokens[] = $token;

                continue;
            }

            if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == '+') {
                if ($i == 0) {
                    continue;
                } elseif (
                    (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION)
                        && ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                    || (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION)
                        && ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
                    || ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX)
                    || ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
                ) {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
                } else {
                    continue;
                }

                $this->tokens[] = $token;

                continue;
            }

            if (
                $token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX
                && $token->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_NOTHING
            ) {
                if (str_contains('<>=', substr($token->getValue(), 0, 1))) {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_LOGICAL);
                } elseif ($token->getValue() == '&') {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_CONCATENATION);
                } else {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
                }

                $this->tokens[] = $token;

                continue;
            }

            if (
                $token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND
                && $token->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_NOTHING
            ) {
                if (!is_numeric($token->getValue())) {
                    if (strtoupper($token->getValue()) == 'TRUE' || strtoupper($token->getValue()) == 'FALSE') {
                        $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_LOGICAL);
                    } else {
                        $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_RANGE);
                    }
                } else {
                    $token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_NUMBER);
                }

                $this->tokens[] = $token;

                continue;
            }

            if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) {
                if ($token->getValue() !== '') {
                    if (str_starts_with($token->getValue(), '@')) {
                        $token->setValue(substr($token->getValue(), 1));
                    }
                }
            }

            $this->tokens[] = $token;
        }
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Calculation;
4
5		/**
6		* PARTLY BASED ON:
7		* Copyright (c) 2007 E. W. Bachtal, Inc.
8		*
9		* Permission is hereby granted, free of charge, to any person obtaining a copy of this software
10		* and associated documentation files (the "Software"), to deal in the Software without restriction,
11		* including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
12		* and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
13		* subject to the following conditions:
14		*
15		* The above copyright notice and this permission notice shall be included in all copies or substantial
16		* portions of the Software.
17		*
18		* The software is provided "as is", without warranty of any kind, express or implied, including but not
19		* limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In
20		* no event shall the authors or copyright holders be liable for any claim, damages or other liability,
21		* whether in an action of contract, tort or otherwise, arising from, out of or in connection with the
22		* software or the use or other dealings in the software.
23		*
24		* https://ewbi.blogs.com/develops/2007/03/excel_formula_p.html
25		* https://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
26		*/
27		class FormulaParser
28		{
29		// Character constants
30		const QUOTE_DOUBLE = '"';
31		const QUOTE_SINGLE = '\'';
32		const BRACKET_CLOSE = ']';
33		const BRACKET_OPEN = '[';
34		const BRACE_OPEN = '{';
35		const BRACE_CLOSE = '}';
36		const PAREN_OPEN = '(';
37		const PAREN_CLOSE = ')';
38		const SEMICOLON = ';';
39		const WHITESPACE = ' ';
40		const COMMA = ',';
41		const ERROR_START = '#';
42
43		const OPERATORS_SN = '+-';
44		const OPERATORS_INFIX = '+-*/^&=><';
45		const OPERATORS_POSTFIX = '%';
46
47		/**
48		* Formula.
49		*/
50		private string $formula;
51
52		/**
53		* Tokens.
54		*
55		* @var FormulaToken[]
56		*/
57		private array $tokens = [];
58
59		/**
60		* Create a new FormulaParser.
61		*
62		* @param ?string $formula Formula to parse
63		*/
64	14	public function __construct(?string $formula = '')
65		{
66		// Check parameters
67	14	if ($formula === null) {
68	1	throw new Exception('Invalid parameter passed: formula');
69		}
70
71		// Initialise values
72	13	$this->formula = trim($formula);
73		// Parse!
74	13	$this->parseToTokens();
75		}
76
77		/**
78		* Get Formula.
79		*/
80	11	public function getFormula(): string
81		{
82	11	return $this->formula;
83		}
84
85		/**
86		* Get Token.
87		*
88		* @param int $id Token id
89		*/
90	12	public function getToken(int $id = 0): FormulaToken
91		{
92	12	if (isset($this->tokens[$id])) {
93	11	return $this->tokens[$id];
94		}
95
96	1	throw new Exception("Token with id $id does not exist.");
97		}
98
99		/**
100		* Get Token count.
101		*/
102	12	public function getTokenCount(): int
103		{
104	12	return count($this->tokens);
105		}
106
107		/**
108		* Get Tokens.
109		*
110		* @return FormulaToken[]
111		*/
112	11	public function getTokens(): array
113		{
114	11	return $this->tokens;
115		}
116
117		/**
118		* Parse to tokens.
119		*/
120	13	private function parseToTokens(): void
121		{
122		// No attempt is made to verify formulas; assumes formulas are derived from Excel, where
123		// they can only exist if valid; stack overflows/underflows sunk as nulls without exceptions.
124
125		// Check if the formula has a valid starting =
126	13	$formulaLength = strlen($this->formula);
127	13	if ($formulaLength < 2 \|\| $this->formula[0] != '=') {
128	1	return;
129		}
130
131		// Helper variables
132	12	$tokens1 = $tokens2 = $stack = [];
133	12	$inString = $inPath = $inRange = $inError = false;
134	12	$nextToken = null;
135		//$token = $previousToken = null;
136
137	12	$index = 1;
138	12	$value = '';
139
140	12	$ERRORS = ['#NULL!', '#DIV/0!', '#VALUE!', '#REF!', '#NAME?', '#NUM!', '#N/A'];
141	12	$COMPARATORS_MULTI = ['>=', '<=', '<>'];
142
143	12	while ($index < $formulaLength) {
144		// state-dependent character evaluation (order is important)
145
146		// double-quoted strings
147		// embeds are doubled
148		// end marks token
149	12	if ($inString) {
150	2	if ($this->formula[$index] == self::QUOTE_DOUBLE) {
151	2	if ((($index + 2) <= $formulaLength) && ($this->formula[$index + 1] == self::QUOTE_DOUBLE)) {
152	1	$value .= self::QUOTE_DOUBLE;
153	1	++$index;
154		} else {
155	2	$inString = false;
156	2	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND, FormulaToken::TOKEN_SUBTYPE_TEXT);
157	2	$value = '';
158		}
159		} else {
160	2	$value .= $this->formula[$index];
161		}
162	2	++$index;
163
164	2	continue;
165		}
166
167		// single-quoted strings (links)
168		// embeds are double
169		// end does not mark a token
170	12	if ($inPath) {
171	2	if ($this->formula[$index] == self::QUOTE_SINGLE) {
172	2	if ((($index + 2) <= $formulaLength) && ($this->formula[$index + 1] == self::QUOTE_SINGLE)) {
173	1	$value .= self::QUOTE_SINGLE;
174	1	++$index;
175		} else {
176	2	$inPath = false;
177		}
178		} else {
179	2	$value .= $this->formula[$index];
180		}
181	2	++$index;
182
183	2	continue;
184		}
185
186		// bracked strings (R1C1 range index or linked workbook name)
187		// no embeds (changed to "()" by Excel)
188		// end does not mark a token
189	12	if ($inRange) {
190	1	if ($this->formula[$index] == self::BRACKET_CLOSE) {
191	1	$inRange = false;
192		}
193	1	$value .= $this->formula[$index];
194	1	++$index;
195
196	1	continue;
197		}
198
199		// error values
200		// end marks a token, determined from absolute list of values
201	12	if ($inError) {
202	1	$value .= $this->formula[$index];
203	1	++$index;
204	1	if (in_array($value, $ERRORS)) {
205	1	$inError = false;
206	1	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND, FormulaToken::TOKEN_SUBTYPE_ERROR);
207	1	$value = '';
208		}
209
210	1	continue;
211		}
212
213		// scientific notation check
214	12	if (str_contains(self::OPERATORS_SN, $this->formula[$index])) {
215	2	if (strlen($value) > 1) {
216		if (preg_match('/^[1-9]{1}(\.\d+)?E{1}$/', $this->formula[$index]) != 0) {
217		$value .= $this->formula[$index];
218		++$index;
219
220		continue;
221		}
222		}
223		}
224
225		// independent character evaluation (order not important)
226
227		// establish state-dependent character evaluations
228	12	if ($this->formula[$index] == self::QUOTE_DOUBLE) {
229	2	if ($value !== '') {
230		// unexpected
231		$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
232		$value = '';
233		}
234	2	$inString = true;
235	2	++$index;
236
237	2	continue;
238		}
239
240	11	if ($this->formula[$index] == self::QUOTE_SINGLE) {
241	2	if ($value !== '') {
242		// unexpected
243		$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
244		$value = '';
245		}
246	2	$inPath = true;
247	2	++$index;
248
249	2	continue;
250		}
251
252	11	if ($this->formula[$index] == self::BRACKET_OPEN) {
253	1	$inRange = true;
254	1	$value .= self::BRACKET_OPEN;
255	1	++$index;
256
257	1	continue;
258		}
259
260	11	if ($this->formula[$index] == self::ERROR_START) {
261	1	if ($value !== '') {
262		// unexpected
263		$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
264		$value = '';
265		}
266	1	$inError = true;
267	1	$value .= self::ERROR_START;
268	1	++$index;
269
270	1	continue;
271		}
272
273		// mark start and end of arrays and array rows
274	10	if ($this->formula[$index] == self::BRACE_OPEN) {
275	1	if ($value !== '') {
276		// unexpected
277		$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_UNKNOWN);
278		$value = '';
279		}
280
281	1	$tmp = new FormulaToken('ARRAY', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
282	1	$tokens1[] = $tmp;
283	1	$stack[] = clone $tmp;
284
285	1	$tmp = new FormulaToken('ARRAYROW', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
286	1	$tokens1[] = $tmp;
287	1	$stack[] = clone $tmp;
288
289	1	++$index;
290
291	1	continue;
292		}
293
294	10	if ($this->formula[$index] == self::SEMICOLON) {
295	1	if ($value !== '') {
296	1	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
297	1	$value = '';
298		}
299
300		/** @var FormulaToken $tmp */
301	1	$tmp = array_pop($stack);
302	1	$tmp->setValue('');
303	1	$tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
304	1	$tokens1[] = $tmp;
305
306	1	$tmp = new FormulaToken(',', FormulaToken::TOKEN_TYPE_ARGUMENT);
307	1	$tokens1[] = $tmp;
308
309	1	$tmp = new FormulaToken('ARRAYROW', FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
310	1	$tokens1[] = $tmp;
311	1	$stack[] = clone $tmp;
312
313	1	++$index;
314
315	1	continue;
316		}
317
318	10	if ($this->formula[$index] == self::BRACE_CLOSE) {
319	1	if ($value !== '') {
320	1	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
321	1	$value = '';
322		}
323
324		/** @var FormulaToken $tmp */
325	1	$tmp = array_pop($stack);
326	1	$tmp->setValue('');
327	1	$tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
328	1	$tokens1[] = $tmp;
329
330		/** @var FormulaToken $tmp */
331	1	$tmp = array_pop($stack);
332	1	$tmp->setValue('');
333	1	$tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
334	1	$tokens1[] = $tmp;
335
336	1	++$index;
337
338	1	continue;
339		}
340
341		// trim white-space
342	10	if ($this->formula[$index] == self::WHITESPACE) {
343	1	if ($value !== '') {
344		$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
345		$value = '';
346		}
347	1	$tokens1[] = new FormulaToken('', FormulaToken::TOKEN_TYPE_WHITESPACE);
348	1	++$index;
349	1	while (($this->formula[$index] == self::WHITESPACE) && ($index < $formulaLength)) {
350	1	++$index;
351		}
352
353	1	continue;
354		}
355
356		// multi-character comparators
357	10	if (($index + 2) <= $formulaLength) {
358	9	if (in_array(substr($this->formula, $index, 2), $COMPARATORS_MULTI)) {
359	1	if ($value !== '') {
360	1	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
361	1	$value = '';
362		}
363	1	$tokens1[] = new FormulaToken(substr($this->formula, $index, 2), FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_LOGICAL);
364	1	$index += 2;
365
366	1	continue;
367		}
368		}
369
370		// standard infix operators
371	10	if (str_contains(self::OPERATORS_INFIX, $this->formula[$index])) {
372	4	if ($value !== '') {
373	2	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
374	2	$value = '';
375		}
376	4	$tokens1[] = new FormulaToken($this->formula[$index], FormulaToken::TOKEN_TYPE_OPERATORINFIX);
377	4	++$index;
378
379	4	continue;
380		}
381
382		// standard postfix operators (only one)
383	9	if (str_contains(self::OPERATORS_POSTFIX, $this->formula[$index])) {
384	1	if ($value !== '') {
385	1	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
386	1	$value = '';
387		}
388	1	$tokens1[] = new FormulaToken($this->formula[$index], FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX);
389	1	++$index;
390
391	1	continue;
392		}
393
394		// start subexpression or function
395	9	if ($this->formula[$index] == self::PAREN_OPEN) {
396	3	if ($value !== '') {
397	2	$tmp = new FormulaToken($value, FormulaToken::TOKEN_TYPE_FUNCTION, FormulaToken::TOKEN_SUBTYPE_START);
398	2	$tokens1[] = $tmp;
399	2	$stack[] = clone $tmp;
400	2	$value = '';
401		} else {
402	1	$tmp = new FormulaToken('', FormulaToken::TOKEN_TYPE_SUBEXPRESSION, FormulaToken::TOKEN_SUBTYPE_START);
403	1	$tokens1[] = $tmp;
404	1	$stack[] = clone $tmp;
405		}
406	3	++$index;
407
408	3	continue;
409		}
410
411		// function, subexpression, or array parameters, or operand unions
412	9	if ($this->formula[$index] == self::COMMA) {
413	2	if ($value !== '') {
414	2	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
415	2	$value = '';
416		}
417
418		/** @var FormulaToken $tmp */
419	2	$tmp = array_pop($stack);
420	2	$tmp->setValue('');
421	2	$tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
422	2	$stack[] = $tmp;
423
424	2	if ($tmp->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) {
425	2	$tokens1[] = new FormulaToken(',', FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_UNION);
426		} else {
427		$tokens1[] = new FormulaToken(',', FormulaToken::TOKEN_TYPE_ARGUMENT);
428		}
429	2	++$index;
430
431	2	continue;
432		}
433
434		// stop subexpression
435	9	if ($this->formula[$index] == self::PAREN_CLOSE) {
436	3	if ($value !== '') {
437	2	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
438	2	$value = '';
439		}
440
441		/** @var FormulaToken $tmp */
442	3	$tmp = array_pop($stack);
443	3	$tmp->setValue('');
444	3	$tmp->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_STOP);
445	3	$tokens1[] = $tmp;
446
447	3	++$index;
448
449	3	continue;
450		}
451
452		// token accumulation
453	9	$value .= $this->formula[$index];
454	9	++$index;
455		}
456
457		// dump remaining accumulation
458	12	if ($value !== '') {
459	7	$tokens1[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERAND);
460		}
461
462		// move tokenList to new set, excluding unnecessary white-space tokens and converting necessary ones to intersections
463	12	$tokenCount = count($tokens1);
464	12	for ($i = 0; $i < $tokenCount; ++$i) {
465	12	$token = $tokens1[$i];
466	12	if (isset($tokens1[$i - 1])) {
467	6	$previousToken = $tokens1[$i - 1];
468		} else {
469	12	$previousToken = null;
470		}
471	12	if (isset($tokens1[$i + 1])) {
472	6	$nextToken = $tokens1[$i + 1];
473		} else {
474	12	$nextToken = null;
475		}
476
477	12	if ($token->getTokenType() != FormulaToken::TOKEN_TYPE_WHITESPACE) {
478	12	$tokens2[] = $token;
479
480	12	continue;
481		}
482
483	1	if ($previousToken === null) {
484		continue;
485		}
486
487		if (
488		!(
489	1	(($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) && ($previousToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
490	1	\|\| (($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($previousToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
491	1	\|\| ($previousToken->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
492		)
493		) {
494	1	continue;
495		}
496
497	1	if ($nextToken === null) {
498		continue;
499		}
500
501		if (
502		!(
503	1	(($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) && ($nextToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_START))
504	1	\|\| (($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION) && ($nextToken->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_START))
505	1	\|\| ($nextToken->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
506		)
507		) {
508	1	continue;
509		}
510
511		$tokens2[] = new FormulaToken($value, FormulaToken::TOKEN_TYPE_OPERATORINFIX, FormulaToken::TOKEN_SUBTYPE_INTERSECTION);
512		}
513
514		// move tokens to final list, switching infix "-" operators to prefix when appropriate, switching infix "+" operators
515		// to noop when appropriate, identifying operand and infix-operator subtypes, and pulling "@" from function names
516	12	$this->tokens = [];
517
518	12	$tokenCount = count($tokens2);
519	12	for ($i = 0; $i < $tokenCount; ++$i) {
520	12	$token = $tokens2[$i];
521	12	if (isset($tokens2[$i - 1])) {
522	6	$previousToken = $tokens2[$i - 1];
523		} else {
524	12	$previousToken = null;
525		}
526
527	12	if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == '-') {
528	1	if ($i == 0) {
529		$token->setTokenType(FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
530		} elseif (
531	1	(($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION)
532	1	&& ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
533	1	\|\| (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION)
534	1	&& ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
535	1	\|\| ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX)
536	1	\|\| ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
537		) {
538		$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
539		} else {
540	1	$token->setTokenType(FormulaToken::TOKEN_TYPE_OPERATORPREFIX);
541		}
542
543	1	$this->tokens[] = $token;
544
545	1	continue;
546		}
547
548	12	if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX && $token->getValue() == '+') {
549	2	if ($i == 0) {
550	1	continue;
551		} elseif (
552	1	(($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION)
553	1	&& ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
554	1	\|\| (($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_SUBEXPRESSION)
555	1	&& ($previousToken?->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_STOP))
556	1	\|\| ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORPOSTFIX)
557	1	\|\| ($previousToken?->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND)
558		) {
559	1	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
560		} else {
561		continue;
562		}
563
564	1	$this->tokens[] = $token;
565
566	1	continue;
567		}
568
569		if (
570	12	$token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERATORINFIX
571	12	&& $token->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_NOTHING
572		) {
573	3	if (str_contains('<>=', substr($token->getValue(), 0, 1))) {
574		$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_LOGICAL);
575	3	} elseif ($token->getValue() == '&') {
576	1	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_CONCATENATION);
577		} else {
578	2	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_MATH);
579		}
580
581	3	$this->tokens[] = $token;
582
583	3	continue;
584		}
585
586		if (
587	12	$token->getTokenType() == FormulaToken::TOKEN_TYPE_OPERAND
588	12	&& $token->getTokenSubType() == FormulaToken::TOKEN_SUBTYPE_NOTHING
589		) {
590	9	if (!is_numeric($token->getValue())) {
591	6	if (strtoupper($token->getValue()) == 'TRUE' \|\| strtoupper($token->getValue()) == 'FALSE') {
592	1	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_LOGICAL);
593		} else {
594	5	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_RANGE);
595		}
596		} else {
597	6	$token->setTokenSubType(FormulaToken::TOKEN_SUBTYPE_NUMBER);
598		}
599
600	9	$this->tokens[] = $token;
601
602	9	continue;
603		}
604
605	6	if ($token->getTokenType() == FormulaToken::TOKEN_TYPE_FUNCTION) {
606	2	if ($token->getValue() !== '') {
607	2	if (str_starts_with($token->getValue(), '@')) {
608		$token->setValue(substr($token->getValue(), 1));
609		}
610		}
611		}
612
613	6	$this->tokens[] = $token;
614		}
615		}
616		}
617

PHPOffice / PhpSpreadsheet

FormulaParser::parseToTokens() F last analyzed 2025-06-01 16:52 UTC

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like

FormulaParser::parseToTokens() F
last analyzed 2025-06-01 16:52 UTC