19356117742

Committed 14 Nov 2025 06:14AM UTC coverage: 95.959% (+0.005%) from 95.954%

Build # 19356117742

Build Type

Pull #4711

github

Committed by

web-flow

Commit Message

Merge 8108c12c3 into dcf475eec

Pull Request Pull Request #4711: Limited PrintArea Support for Html/Pdf

Run Details

56 of 57 new or added lines in 2 files covered. (98.25%)

8 existing lines in 1 file now uncovered.

45449 of 47363 relevant lines covered (95.96%)

373.76 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

91.76

/src/PhpSpreadsheet/Calculation/FormulaParser.php

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

PHPOffice / PhpSpreadsheet / 19356117742

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous