FormulaParser::getToken() - Code Metrics - Inspection of "Drop dead code" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 07c60b...7328e1 )

by Adrien

created 2023-09-26 09:56 UTC

FormulaParser::getToken() A

↳ Parent: FormulaParser

Complexity

Conditions	2
Paths	2

Size

Total Lines	7
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	2

Importance

Changes

Metric	Value
eloc	3
c	0
b	0
f	0
dl	0
loc	7
ccs	4
cts	4
cp	1
rs	10
cc	2
nc	2
nop	1
crap	2

<?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 $tokens = [];

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

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

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

PHPOffice / PhpSpreadsheet

Push — master ( 07c60b...7328e1 )

FormulaParser::getToken() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like