tablepress / libraries / vendor / PhpSpreadsheet / Calculation / FormulaParser.php

FormulaParser.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/FormulaParser.php

608 lines 17.4 KB

Raw Download Zip

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