PluginProbe
Visualizer – Tables & Charts Manager with Built-in AI Generator / 3.7.1
Visualizer – Tables & Charts Manager with Built-in AI Generator v3.7.1
4.0.8 4.0.7 4.0.6 4.0.5 4.0.4 4.0.3 3.0.5 3.0.6 3.0.7 3.0.8 3.0.9 3.1.0 3.1.1 3.1.2 3.1.3 3.10.0 3.10.1 3.10.10 3.10.11 3.10.12 3.10.13 3.10.14 3.10.15 3.10.2 3.10.3 All 149 releases
visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Calculation / Calculation.php

Calculation.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.7.1, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php

4,487 lines 180.7 KB
No matching file
Up and down to move Enter to open Esc to close
Raw Download Zip
1 <?php
2
3 namespace PhpOffice\PhpSpreadsheet\Calculation;
4
5 use PhpOffice\PhpSpreadsheet\Calculation\Engine\CyclicReferenceStack;
6 use PhpOffice\PhpSpreadsheet\Calculation\Engine\Logger;
7 use PhpOffice\PhpSpreadsheet\Calculation\Token\Stack;
8 use PhpOffice\PhpSpreadsheet\Cell\Cell;
9 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
10 use PhpOffice\PhpSpreadsheet\NamedRange;
11 use PhpOffice\PhpSpreadsheet\Shared;
12 use PhpOffice\PhpSpreadsheet\Spreadsheet;
13 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
14
15 class Calculation
16 {
17 /** Constants */
18 /** Regular Expressions */
19 // Numeric operand
20 const CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?';
21 // String operand
22 const CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"';
23 // Opening bracket
24 const CALCULATION_REGEXP_OPENBRACE = '\(';
25 // Function (allow for the old @ symbol that could be used to prefix a function, but we'll ignore it)
26 const CALCULATION_REGEXP_FUNCTION = '@?(?:_xlfn\.)?([A-Z][A-Z0-9\.]*)[\s]*\(';
27 // Cell reference (cell or range of cells, with or without a sheet reference)
28 const CALCULATION_REGEXP_CELLREF = '((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d{1,7})';
29 // Named Range of cells
30 const CALCULATION_REGEXP_NAMEDRANGE = '((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_A-Z][_A-Z0-9\.]*)';
31 // Error
32 const CALCULATION_REGEXP_ERROR = '\#[A-Z][A-Z0_\/]*[!\?]?';
33
34 /** constants */
35 const RETURN_ARRAY_AS_ERROR = 'error';
36 const RETURN_ARRAY_AS_VALUE = 'value';
37 const RETURN_ARRAY_AS_ARRAY = 'array';
38
39 private static $returnArrayAsType = self::RETURN_ARRAY_AS_VALUE;
40
41 /**
42 * Instance of this class.
43 *
44 * @var Calculation
45 */
46 private static $instance;
47
48 /**
49 * Instance of the spreadsheet this Calculation Engine is using.
50 *
51 * @var Spreadsheet
52 */
53 private $spreadsheet;
54
55 /**
56 * Calculation cache.
57 *
58 * @var array
59 */
60 private $calculationCache = [];
61
62 /**
63 * Calculation cache enabled.
64 *
65 * @var bool
66 */
67 private $calculationCacheEnabled = true;
68
69 /**
70 * List of operators that can be used within formulae
71 * The true/false value indicates whether it is a binary operator or a unary operator.
72 *
73 * @var array
74 */
75 private static $operators = [
76 '+' => true, '-' => true, '*' => true, '/' => true,
77 '^' => true, '&' => true, '%' => false, '~' => false,
78 '>' => true, '<' => true, '=' => true, '>=' => true,
79 '<=' => true, '<>' => true, '|' => true, ':' => true,
80 ];
81
82 /**
83 * List of binary operators (those that expect two operands).
84 *
85 * @var array
86 */
87 private static $binaryOperators = [
88 '+' => true, '-' => true, '*' => true, '/' => true,
89 '^' => true, '&' => true, '>' => true, '<' => true,
90 '=' => true, '>=' => true, '<=' => true, '<>' => true,
91 '|' => true, ':' => true,
92 ];
93
94 /**
95 * The debug log generated by the calculation engine.
96 *
97 * @var Logger
98 */
99 private $debugLog;
100
101 /**
102 * Flag to determine how formula errors should be handled
103 * If true, then a user error will be triggered
104 * If false, then an exception will be thrown.
105 *
106 * @var bool
107 */
108 public $suppressFormulaErrors = false;
109
110 /**
111 * Error message for any error that was raised/thrown by the calculation engine.
112 *
113 * @var string
114 */
115 public $formulaError;
116
117 /**
118 * An array of the nested cell references accessed by the calculation engine, used for the debug log.
119 *
120 * @var CyclicReferenceStack
121 */
122 private $cyclicReferenceStack;
123
124 private $cellStack = [];
125
126 /**
127 * Current iteration counter for cyclic formulae
128 * If the value is 0 (or less) then cyclic formulae will throw an exception,
129 * otherwise they will iterate to the limit defined here before returning a result.
130 *
131 * @var int
132 */
133 private $cyclicFormulaCounter = 1;
134
135 private $cyclicFormulaCell = '';
136
137 /**
138 * Number of iterations for cyclic formulae.
139 *
140 * @var int
141 */
142 public $cyclicFormulaCount = 1;
143
144 /**
145 * Epsilon Precision used for comparisons in calculations.
146 *
147 * @var float
148 */
149 private $delta = 0.1e-12;
150
151 /**
152 * The current locale setting.
153 *
154 * @var string
155 */
156 private static $localeLanguage = 'en_us'; // US English (default locale)
157
158 /**
159 * List of available locale settings
160 * Note that this is read for the locale subdirectory only when requested.
161 *
162 * @var string[]
163 */
164 private static $validLocaleLanguages = [
165 'en', // English (default language)
166 ];
167
168 /**
169 * Locale-specific argument separator for function arguments.
170 *
171 * @var string
172 */
173 private static $localeArgumentSeparator = ',';
174
175 private static $localeFunctions = [];
176
177 /**
178 * Locale-specific translations for Excel constants (True, False and Null).
179 *
180 * @var string[]
181 */
182 public static $localeBoolean = [
183 'TRUE' => 'TRUE',
184 'FALSE' => 'FALSE',
185 'NULL' => 'NULL',
186 ];
187
188 /**
189 * Excel constant string translations to their PHP equivalents
190 * Constant conversion from text name/value to actual (datatyped) value.
191 *
192 * @var string[]
193 */
194 private static $excelConstants = [
195 'TRUE' => true,
196 'FALSE' => false,
197 'NULL' => null,
198 ];
199
200 // PhpSpreadsheet functions
201 private static $phpSpreadsheetFunctions = [
202 'ABS' => [
203 'category' => Category::CATEGORY_MATH_AND_TRIG,
204 'functionCall' => 'abs',
205 'argumentCount' => '1',
206 ],
207 'ACCRINT' => [
208 'category' => Category::CATEGORY_FINANCIAL,
209 'functionCall' => [Financial::class, 'ACCRINT'],
210 'argumentCount' => '4-7',
211 ],
212 'ACCRINTM' => [
213 'category' => Category::CATEGORY_FINANCIAL,
214 'functionCall' => [Financial::class, 'ACCRINTM'],
215 'argumentCount' => '3-5',
216 ],
217 'ACOS' => [
218 'category' => Category::CATEGORY_MATH_AND_TRIG,
219 'functionCall' => 'acos',
220 'argumentCount' => '1',
221 ],
222 'ACOSH' => [
223 'category' => Category::CATEGORY_MATH_AND_TRIG,
224 'functionCall' => 'acosh',
225 'argumentCount' => '1',
226 ],
227 'ACOT' => [
228 'category' => Category::CATEGORY_MATH_AND_TRIG,
229 'functionCall' => [MathTrig::class, 'ACOT'],
230 'argumentCount' => '1',
231 ],
232 'ACOTH' => [
233 'category' => Category::CATEGORY_MATH_AND_TRIG,
234 'functionCall' => [MathTrig::class, 'ACOTH'],
235 'argumentCount' => '1',
236 ],
237 'ADDRESS' => [
238 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
239 'functionCall' => [LookupRef::class, 'cellAddress'],
240 'argumentCount' => '2-5',
241 ],
242 'AMORDEGRC' => [
243 'category' => Category::CATEGORY_FINANCIAL,
244 'functionCall' => [Financial::class, 'AMORDEGRC'],
245 'argumentCount' => '6,7',
246 ],
247 'AMORLINC' => [
248 'category' => Category::CATEGORY_FINANCIAL,
249 'functionCall' => [Financial::class, 'AMORLINC'],
250 'argumentCount' => '6,7',
251 ],
252 'AND' => [
253 'category' => Category::CATEGORY_LOGICAL,
254 'functionCall' => [Logical::class, 'logicalAnd'],
255 'argumentCount' => '1+',
256 ],
257 'AREAS' => [
258 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
259 'functionCall' => [Functions::class, 'DUMMY'],
260 'argumentCount' => '1',
261 ],
262 'ASC' => [
263 'category' => Category::CATEGORY_TEXT_AND_DATA,
264 'functionCall' => [Functions::class, 'DUMMY'],
265 'argumentCount' => '1',
266 ],
267 'ASIN' => [
268 'category' => Category::CATEGORY_MATH_AND_TRIG,
269 'functionCall' => 'asin',
270 'argumentCount' => '1',
271 ],
272 'ASINH' => [
273 'category' => Category::CATEGORY_MATH_AND_TRIG,
274 'functionCall' => 'asinh',
275 'argumentCount' => '1',
276 ],
277 'ATAN' => [
278 'category' => Category::CATEGORY_MATH_AND_TRIG,
279 'functionCall' => 'atan',
280 'argumentCount' => '1',
281 ],
282 'ATAN2' => [
283 'category' => Category::CATEGORY_MATH_AND_TRIG,
284 'functionCall' => [MathTrig::class, 'ATAN2'],
285 'argumentCount' => '2',
286 ],
287 'ATANH' => [
288 'category' => Category::CATEGORY_MATH_AND_TRIG,
289 'functionCall' => 'atanh',
290 'argumentCount' => '1',
291 ],
292 'AVEDEV' => [
293 'category' => Category::CATEGORY_STATISTICAL,
294 'functionCall' => [Statistical::class, 'AVEDEV'],
295 'argumentCount' => '1+',
296 ],
297 'AVERAGE' => [
298 'category' => Category::CATEGORY_STATISTICAL,
299 'functionCall' => [Statistical::class, 'AVERAGE'],
300 'argumentCount' => '1+',
301 ],
302 'AVERAGEA' => [
303 'category' => Category::CATEGORY_STATISTICAL,
304 'functionCall' => [Statistical::class, 'AVERAGEA'],
305 'argumentCount' => '1+',
306 ],
307 'AVERAGEIF' => [
308 'category' => Category::CATEGORY_STATISTICAL,
309 'functionCall' => [Statistical::class, 'AVERAGEIF'],
310 'argumentCount' => '2,3',
311 ],
312 'AVERAGEIFS' => [
313 'category' => Category::CATEGORY_STATISTICAL,
314 'functionCall' => [Functions::class, 'DUMMY'],
315 'argumentCount' => '3+',
316 ],
317 'BAHTTEXT' => [
318 'category' => Category::CATEGORY_TEXT_AND_DATA,
319 'functionCall' => [Functions::class, 'DUMMY'],
320 'argumentCount' => '1',
321 ],
322 'BESSELI' => [
323 'category' => Category::CATEGORY_ENGINEERING,
324 'functionCall' => [Engineering::class, 'BESSELI'],
325 'argumentCount' => '2',
326 ],
327 'BESSELJ' => [
328 'category' => Category::CATEGORY_ENGINEERING,
329 'functionCall' => [Engineering::class, 'BESSELJ'],
330 'argumentCount' => '2',
331 ],
332 'BESSELK' => [
333 'category' => Category::CATEGORY_ENGINEERING,
334 'functionCall' => [Engineering::class, 'BESSELK'],
335 'argumentCount' => '2',
336 ],
337 'BESSELY' => [
338 'category' => Category::CATEGORY_ENGINEERING,
339 'functionCall' => [Engineering::class, 'BESSELY'],
340 'argumentCount' => '2',
341 ],
342 'BETADIST' => [
343 'category' => Category::CATEGORY_STATISTICAL,
344 'functionCall' => [Statistical::class, 'BETADIST'],
345 'argumentCount' => '3-5',
346 ],
347 'BETAINV' => [
348 'category' => Category::CATEGORY_STATISTICAL,
349 'functionCall' => [Statistical::class, 'BETAINV'],
350 'argumentCount' => '3-5',
351 ],
352 'BIN2DEC' => [
353 'category' => Category::CATEGORY_ENGINEERING,
354 'functionCall' => [Engineering::class, 'BINTODEC'],
355 'argumentCount' => '1',
356 ],
357 'BIN2HEX' => [
358 'category' => Category::CATEGORY_ENGINEERING,
359 'functionCall' => [Engineering::class, 'BINTOHEX'],
360 'argumentCount' => '1,2',
361 ],
362 'BIN2OCT' => [
363 'category' => Category::CATEGORY_ENGINEERING,
364 'functionCall' => [Engineering::class, 'BINTOOCT'],
365 'argumentCount' => '1,2',
366 ],
367 'BINOMDIST' => [
368 'category' => Category::CATEGORY_STATISTICAL,
369 'functionCall' => [Statistical::class, 'BINOMDIST'],
370 'argumentCount' => '4',
371 ],
372 'BITAND' => [
373 'category' => Category::CATEGORY_ENGINEERING,
374 'functionCall' => [Engineering::class, 'BITAND'],
375 'argumentCount' => '2',
376 ],
377 'BITOR' => [
378 'category' => Category::CATEGORY_ENGINEERING,
379 'functionCall' => [Engineering::class, 'BITOR'],
380 'argumentCount' => '2',
381 ],
382 'BITXOR' => [
383 'category' => Category::CATEGORY_ENGINEERING,
384 'functionCall' => [Engineering::class, 'BITOR'],
385 'argumentCount' => '2',
386 ],
387 'BITLSHIFT' => [
388 'category' => Category::CATEGORY_ENGINEERING,
389 'functionCall' => [Engineering::class, 'BITLSHIFT'],
390 'argumentCount' => '2',
391 ],
392 'BITRSHIFT' => [
393 'category' => Category::CATEGORY_ENGINEERING,
394 'functionCall' => [Engineering::class, 'BITRSHIFT'],
395 'argumentCount' => '2',
396 ],
397 'CEILING' => [
398 'category' => Category::CATEGORY_MATH_AND_TRIG,
399 'functionCall' => [MathTrig::class, 'CEILING'],
400 'argumentCount' => '2',
401 ],
402 'CELL' => [
403 'category' => Category::CATEGORY_INFORMATION,
404 'functionCall' => [Functions::class, 'DUMMY'],
405 'argumentCount' => '1,2',
406 ],
407 'CHAR' => [
408 'category' => Category::CATEGORY_TEXT_AND_DATA,
409 'functionCall' => [TextData::class, 'CHARACTER'],
410 'argumentCount' => '1',
411 ],
412 'CHIDIST' => [
413 'category' => Category::CATEGORY_STATISTICAL,
414 'functionCall' => [Statistical::class, 'CHIDIST'],
415 'argumentCount' => '2',
416 ],
417 'CHIINV' => [
418 'category' => Category::CATEGORY_STATISTICAL,
419 'functionCall' => [Statistical::class, 'CHIINV'],
420 'argumentCount' => '2',
421 ],
422 'CHITEST' => [
423 'category' => Category::CATEGORY_STATISTICAL,
424 'functionCall' => [Functions::class, 'DUMMY'],
425 'argumentCount' => '2',
426 ],
427 'CHOOSE' => [
428 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
429 'functionCall' => [LookupRef::class, 'CHOOSE'],
430 'argumentCount' => '2+',
431 ],
432 'CLEAN' => [
433 'category' => Category::CATEGORY_TEXT_AND_DATA,
434 'functionCall' => [TextData::class, 'TRIMNONPRINTABLE'],
435 'argumentCount' => '1',
436 ],
437 'CODE' => [
438 'category' => Category::CATEGORY_TEXT_AND_DATA,
439 'functionCall' => [TextData::class, 'ASCIICODE'],
440 'argumentCount' => '1',
441 ],
442 'COLUMN' => [
443 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
444 'functionCall' => [LookupRef::class, 'COLUMN'],
445 'argumentCount' => '-1',
446 'passByReference' => [true],
447 ],
448 'COLUMNS' => [
449 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
450 'functionCall' => [LookupRef::class, 'COLUMNS'],
451 'argumentCount' => '1',
452 ],
453 'COMBIN' => [
454 'category' => Category::CATEGORY_MATH_AND_TRIG,
455 'functionCall' => [MathTrig::class, 'COMBIN'],
456 'argumentCount' => '2',
457 ],
458 'COMPLEX' => [
459 'category' => Category::CATEGORY_ENGINEERING,
460 'functionCall' => [Engineering::class, 'COMPLEX'],
461 'argumentCount' => '2,3',
462 ],
463 'CONCAT' => [
464 'category' => Category::CATEGORY_TEXT_AND_DATA,
465 'functionCall' => [TextData::class, 'CONCATENATE'],
466 'argumentCount' => '1+',
467 ],
468 'CONCATENATE' => [
469 'category' => Category::CATEGORY_TEXT_AND_DATA,
470 'functionCall' => [TextData::class, 'CONCATENATE'],
471 'argumentCount' => '1+',
472 ],
473 'CONFIDENCE' => [
474 'category' => Category::CATEGORY_STATISTICAL,
475 'functionCall' => [Statistical::class, 'CONFIDENCE'],
476 'argumentCount' => '3',
477 ],
478 'CONVERT' => [
479 'category' => Category::CATEGORY_ENGINEERING,
480 'functionCall' => [Engineering::class, 'CONVERTUOM'],
481 'argumentCount' => '3',
482 ],
483 'CORREL' => [
484 'category' => Category::CATEGORY_STATISTICAL,
485 'functionCall' => [Statistical::class, 'CORREL'],
486 'argumentCount' => '2',
487 ],
488 'COS' => [
489 'category' => Category::CATEGORY_MATH_AND_TRIG,
490 'functionCall' => 'cos',
491 'argumentCount' => '1',
492 ],
493 'COSH' => [
494 'category' => Category::CATEGORY_MATH_AND_TRIG,
495 'functionCall' => 'cosh',
496 'argumentCount' => '1',
497 ],
498 'COT' => [
499 'category' => Category::CATEGORY_MATH_AND_TRIG,
500 'functionCall' => [MathTrig::class, 'COT'],
501 'argumentCount' => '1',
502 ],
503 'COTH' => [
504 'category' => Category::CATEGORY_MATH_AND_TRIG,
505 'functionCall' => [MathTrig::class, 'COTH'],
506 'argumentCount' => '1',
507 ],
508 'COUNT' => [
509 'category' => Category::CATEGORY_STATISTICAL,
510 'functionCall' => [Statistical::class, 'COUNT'],
511 'argumentCount' => '1+',
512 ],
513 'COUNTA' => [
514 'category' => Category::CATEGORY_STATISTICAL,
515 'functionCall' => [Statistical::class, 'COUNTA'],
516 'argumentCount' => '1+',
517 ],
518 'COUNTBLANK' => [
519 'category' => Category::CATEGORY_STATISTICAL,
520 'functionCall' => [Statistical::class, 'COUNTBLANK'],
521 'argumentCount' => '1',
522 ],
523 'COUNTIF' => [
524 'category' => Category::CATEGORY_STATISTICAL,
525 'functionCall' => [Statistical::class, 'COUNTIF'],
526 'argumentCount' => '2',
527 ],
528 'COUNTIFS' => [
529 'category' => Category::CATEGORY_STATISTICAL,
530 'functionCall' => [Functions::class, 'DUMMY'],
531 'argumentCount' => '2+',
532 ],
533 'COUPDAYBS' => [
534 'category' => Category::CATEGORY_FINANCIAL,
535 'functionCall' => [Financial::class, 'COUPDAYBS'],
536 'argumentCount' => '3,4',
537 ],
538 'COUPDAYS' => [
539 'category' => Category::CATEGORY_FINANCIAL,
540 'functionCall' => [Financial::class, 'COUPDAYS'],
541 'argumentCount' => '3,4',
542 ],
543 'COUPDAYSNC' => [
544 'category' => Category::CATEGORY_FINANCIAL,
545 'functionCall' => [Financial::class, 'COUPDAYSNC'],
546 'argumentCount' => '3,4',
547 ],
548 'COUPNCD' => [
549 'category' => Category::CATEGORY_FINANCIAL,
550 'functionCall' => [Financial::class, 'COUPNCD'],
551 'argumentCount' => '3,4',
552 ],
553 'COUPNUM' => [
554 'category' => Category::CATEGORY_FINANCIAL,
555 'functionCall' => [Financial::class, 'COUPNUM'],
556 'argumentCount' => '3,4',
557 ],
558 'COUPPCD' => [
559 'category' => Category::CATEGORY_FINANCIAL,
560 'functionCall' => [Financial::class, 'COUPPCD'],
561 'argumentCount' => '3,4',
562 ],
563 'COVAR' => [
564 'category' => Category::CATEGORY_STATISTICAL,
565 'functionCall' => [Statistical::class, 'COVAR'],
566 'argumentCount' => '2',
567 ],
568 'CRITBINOM' => [
569 'category' => Category::CATEGORY_STATISTICAL,
570 'functionCall' => [Statistical::class, 'CRITBINOM'],
571 'argumentCount' => '3',
572 ],
573 'CSC' => [
574 'category' => Category::CATEGORY_MATH_AND_TRIG,
575 'functionCall' => [MathTrig::class, 'CSC'],
576 'argumentCount' => '1',
577 ],
578 'CSCH' => [
579 'category' => Category::CATEGORY_MATH_AND_TRIG,
580 'functionCall' => [MathTrig::class, 'CSCH'],
581 'argumentCount' => '1',
582 ],
583 'CUBEKPIMEMBER' => [
584 'category' => Category::CATEGORY_CUBE,
585 'functionCall' => [Functions::class, 'DUMMY'],
586 'argumentCount' => '?',
587 ],
588 'CUBEMEMBER' => [
589 'category' => Category::CATEGORY_CUBE,
590 'functionCall' => [Functions::class, 'DUMMY'],
591 'argumentCount' => '?',
592 ],
593 'CUBEMEMBERPROPERTY' => [
594 'category' => Category::CATEGORY_CUBE,
595 'functionCall' => [Functions::class, 'DUMMY'],
596 'argumentCount' => '?',
597 ],
598 'CUBERANKEDMEMBER' => [
599 'category' => Category::CATEGORY_CUBE,
600 'functionCall' => [Functions::class, 'DUMMY'],
601 'argumentCount' => '?',
602 ],
603 'CUBESET' => [
604 'category' => Category::CATEGORY_CUBE,
605 'functionCall' => [Functions::class, 'DUMMY'],
606 'argumentCount' => '?',
607 ],
608 'CUBESETCOUNT' => [
609 'category' => Category::CATEGORY_CUBE,
610 'functionCall' => [Functions::class, 'DUMMY'],
611 'argumentCount' => '?',
612 ],
613 'CUBEVALUE' => [
614 'category' => Category::CATEGORY_CUBE,
615 'functionCall' => [Functions::class, 'DUMMY'],
616 'argumentCount' => '?',
617 ],
618 'CUMIPMT' => [
619 'category' => Category::CATEGORY_FINANCIAL,
620 'functionCall' => [Financial::class, 'CUMIPMT'],
621 'argumentCount' => '6',
622 ],
623 'CUMPRINC' => [
624 'category' => Category::CATEGORY_FINANCIAL,
625 'functionCall' => [Financial::class, 'CUMPRINC'],
626 'argumentCount' => '6',
627 ],
628 'DATE' => [
629 'category' => Category::CATEGORY_DATE_AND_TIME,
630 'functionCall' => [DateTime::class, 'DATE'],
631 'argumentCount' => '3',
632 ],
633 'DATEDIF' => [
634 'category' => Category::CATEGORY_DATE_AND_TIME,
635 'functionCall' => [DateTime::class, 'DATEDIF'],
636 'argumentCount' => '2,3',
637 ],
638 'DATEVALUE' => [
639 'category' => Category::CATEGORY_DATE_AND_TIME,
640 'functionCall' => [DateTime::class, 'DATEVALUE'],
641 'argumentCount' => '1',
642 ],
643 'DAVERAGE' => [
644 'category' => Category::CATEGORY_DATABASE,
645 'functionCall' => [Database::class, 'DAVERAGE'],
646 'argumentCount' => '3',
647 ],
648 'DAY' => [
649 'category' => Category::CATEGORY_DATE_AND_TIME,
650 'functionCall' => [DateTime::class, 'DAYOFMONTH'],
651 'argumentCount' => '1',
652 ],
653 'DAYS' => [
654 'category' => Category::CATEGORY_DATE_AND_TIME,
655 'functionCall' => [DateTime::class, 'DAYS'],
656 'argumentCount' => '2',
657 ],
658 'DAYS360' => [
659 'category' => Category::CATEGORY_DATE_AND_TIME,
660 'functionCall' => [DateTime::class, 'DAYS360'],
661 'argumentCount' => '2,3',
662 ],
663 'DB' => [
664 'category' => Category::CATEGORY_FINANCIAL,
665 'functionCall' => [Financial::class, 'DB'],
666 'argumentCount' => '4,5',
667 ],
668 'DCOUNT' => [
669 'category' => Category::CATEGORY_DATABASE,
670 'functionCall' => [Database::class, 'DCOUNT'],
671 'argumentCount' => '3',
672 ],
673 'DCOUNTA' => [
674 'category' => Category::CATEGORY_DATABASE,
675 'functionCall' => [Database::class, 'DCOUNTA'],
676 'argumentCount' => '3',
677 ],
678 'DDB' => [
679 'category' => Category::CATEGORY_FINANCIAL,
680 'functionCall' => [Financial::class, 'DDB'],
681 'argumentCount' => '4,5',
682 ],
683 'DEC2BIN' => [
684 'category' => Category::CATEGORY_ENGINEERING,
685 'functionCall' => [Engineering::class, 'DECTOBIN'],
686 'argumentCount' => '1,2',
687 ],
688 'DEC2HEX' => [
689 'category' => Category::CATEGORY_ENGINEERING,
690 'functionCall' => [Engineering::class, 'DECTOHEX'],
691 'argumentCount' => '1,2',
692 ],
693 'DEC2OCT' => [
694 'category' => Category::CATEGORY_ENGINEERING,
695 'functionCall' => [Engineering::class, 'DECTOOCT'],
696 'argumentCount' => '1,2',
697 ],
698 'DEGREES' => [
699 'category' => Category::CATEGORY_MATH_AND_TRIG,
700 'functionCall' => 'rad2deg',
701 'argumentCount' => '1',
702 ],
703 'DELTA' => [
704 'category' => Category::CATEGORY_ENGINEERING,
705 'functionCall' => [Engineering::class, 'DELTA'],
706 'argumentCount' => '1,2',
707 ],
708 'DEVSQ' => [
709 'category' => Category::CATEGORY_STATISTICAL,
710 'functionCall' => [Statistical::class, 'DEVSQ'],
711 'argumentCount' => '1+',
712 ],
713 'DGET' => [
714 'category' => Category::CATEGORY_DATABASE,
715 'functionCall' => [Database::class, 'DGET'],
716 'argumentCount' => '3',
717 ],
718 'DISC' => [
719 'category' => Category::CATEGORY_FINANCIAL,
720 'functionCall' => [Financial::class, 'DISC'],
721 'argumentCount' => '4,5',
722 ],
723 'DMAX' => [
724 'category' => Category::CATEGORY_DATABASE,
725 'functionCall' => [Database::class, 'DMAX'],
726 'argumentCount' => '3',
727 ],
728 'DMIN' => [
729 'category' => Category::CATEGORY_DATABASE,
730 'functionCall' => [Database::class, 'DMIN'],
731 'argumentCount' => '3',
732 ],
733 'DOLLAR' => [
734 'category' => Category::CATEGORY_TEXT_AND_DATA,
735 'functionCall' => [TextData::class, 'DOLLAR'],
736 'argumentCount' => '1,2',
737 ],
738 'DOLLARDE' => [
739 'category' => Category::CATEGORY_FINANCIAL,
740 'functionCall' => [Financial::class, 'DOLLARDE'],
741 'argumentCount' => '2',
742 ],
743 'DOLLARFR' => [
744 'category' => Category::CATEGORY_FINANCIAL,
745 'functionCall' => [Financial::class, 'DOLLARFR'],
746 'argumentCount' => '2',
747 ],
748 'DPRODUCT' => [
749 'category' => Category::CATEGORY_DATABASE,
750 'functionCall' => [Database::class, 'DPRODUCT'],
751 'argumentCount' => '3',
752 ],
753 'DSTDEV' => [
754 'category' => Category::CATEGORY_DATABASE,
755 'functionCall' => [Database::class, 'DSTDEV'],
756 'argumentCount' => '3',
757 ],
758 'DSTDEVP' => [
759 'category' => Category::CATEGORY_DATABASE,
760 'functionCall' => [Database::class, 'DSTDEVP'],
761 'argumentCount' => '3',
762 ],
763 'DSUM' => [
764 'category' => Category::CATEGORY_DATABASE,
765 'functionCall' => [Database::class, 'DSUM'],
766 'argumentCount' => '3',
767 ],
768 'DURATION' => [
769 'category' => Category::CATEGORY_FINANCIAL,
770 'functionCall' => [Functions::class, 'DUMMY'],
771 'argumentCount' => '5,6',
772 ],
773 'DVAR' => [
774 'category' => Category::CATEGORY_DATABASE,
775 'functionCall' => [Database::class, 'DVAR'],
776 'argumentCount' => '3',
777 ],
778 'DVARP' => [
779 'category' => Category::CATEGORY_DATABASE,
780 'functionCall' => [Database::class, 'DVARP'],
781 'argumentCount' => '3',
782 ],
783 'EDATE' => [
784 'category' => Category::CATEGORY_DATE_AND_TIME,
785 'functionCall' => [DateTime::class, 'EDATE'],
786 'argumentCount' => '2',
787 ],
788 'EFFECT' => [
789 'category' => Category::CATEGORY_FINANCIAL,
790 'functionCall' => [Financial::class, 'EFFECT'],
791 'argumentCount' => '2',
792 ],
793 'EOMONTH' => [
794 'category' => Category::CATEGORY_DATE_AND_TIME,
795 'functionCall' => [DateTime::class, 'EOMONTH'],
796 'argumentCount' => '2',
797 ],
798 'ERF' => [
799 'category' => Category::CATEGORY_ENGINEERING,
800 'functionCall' => [Engineering::class, 'ERF'],
801 'argumentCount' => '1,2',
802 ],
803 'ERF.PRECISE' => [
804 'category' => Category::CATEGORY_ENGINEERING,
805 'functionCall' => [Engineering::class, 'ERFPRECISE'],
806 'argumentCount' => '1',
807 ],
808 'ERFC' => [
809 'category' => Category::CATEGORY_ENGINEERING,
810 'functionCall' => [Engineering::class, 'ERFC'],
811 'argumentCount' => '1',
812 ],
813 'ERFC.PRECISE' => [
814 'category' => Category::CATEGORY_ENGINEERING,
815 'functionCall' => [Engineering::class, 'ERFC'],
816 'argumentCount' => '1',
817 ],
818 'ERROR.TYPE' => [
819 'category' => Category::CATEGORY_INFORMATION,
820 'functionCall' => [Functions::class, 'errorType'],
821 'argumentCount' => '1',
822 ],
823 'EVEN' => [
824 'category' => Category::CATEGORY_MATH_AND_TRIG,
825 'functionCall' => [MathTrig::class, 'EVEN'],
826 'argumentCount' => '1',
827 ],
828 'EXACT' => [
829 'category' => Category::CATEGORY_TEXT_AND_DATA,
830 'functionCall' => [TextData::class, 'EXACT'],
831 'argumentCount' => '2',
832 ],
833 'EXP' => [
834 'category' => Category::CATEGORY_MATH_AND_TRIG,
835 'functionCall' => 'exp',
836 'argumentCount' => '1',
837 ],
838 'EXPONDIST' => [
839 'category' => Category::CATEGORY_STATISTICAL,
840 'functionCall' => [Statistical::class, 'EXPONDIST'],
841 'argumentCount' => '3',
842 ],
843 'FACT' => [
844 'category' => Category::CATEGORY_MATH_AND_TRIG,
845 'functionCall' => [MathTrig::class, 'FACT'],
846 'argumentCount' => '1',
847 ],
848 'FACTDOUBLE' => [
849 'category' => Category::CATEGORY_MATH_AND_TRIG,
850 'functionCall' => [MathTrig::class, 'FACTDOUBLE'],
851 'argumentCount' => '1',
852 ],
853 'FALSE' => [
854 'category' => Category::CATEGORY_LOGICAL,
855 'functionCall' => [Logical::class, 'FALSE'],
856 'argumentCount' => '0',
857 ],
858 'FDIST' => [
859 'category' => Category::CATEGORY_STATISTICAL,
860 'functionCall' => [Functions::class, 'DUMMY'],
861 'argumentCount' => '3',
862 ],
863 'FIND' => [
864 'category' => Category::CATEGORY_TEXT_AND_DATA,
865 'functionCall' => [TextData::class, 'SEARCHSENSITIVE'],
866 'argumentCount' => '2,3',
867 ],
868 'FINDB' => [
869 'category' => Category::CATEGORY_TEXT_AND_DATA,
870 'functionCall' => [TextData::class, 'SEARCHSENSITIVE'],
871 'argumentCount' => '2,3',
872 ],
873 'FINV' => [
874 'category' => Category::CATEGORY_STATISTICAL,
875 'functionCall' => [Functions::class, 'DUMMY'],
876 'argumentCount' => '3',
877 ],
878 'FISHER' => [
879 'category' => Category::CATEGORY_STATISTICAL,
880 'functionCall' => [Statistical::class, 'FISHER'],
881 'argumentCount' => '1',
882 ],
883 'FISHERINV' => [
884 'category' => Category::CATEGORY_STATISTICAL,
885 'functionCall' => [Statistical::class, 'FISHERINV'],
886 'argumentCount' => '1',
887 ],
888 'FIXED' => [
889 'category' => Category::CATEGORY_TEXT_AND_DATA,
890 'functionCall' => [TextData::class, 'FIXEDFORMAT'],
891 'argumentCount' => '1-3',
892 ],
893 'FLOOR' => [
894 'category' => Category::CATEGORY_MATH_AND_TRIG,
895 'functionCall' => [MathTrig::class, 'FLOOR'],
896 'argumentCount' => '2',
897 ],
898 'FORECAST' => [
899 'category' => Category::CATEGORY_STATISTICAL,
900 'functionCall' => [Statistical::class, 'FORECAST'],
901 'argumentCount' => '3',
902 ],
903 'FORMULATEXT' => [
904 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
905 'functionCall' => [LookupRef::class, 'FORMULATEXT'],
906 'argumentCount' => '1',
907 'passCellReference' => true,
908 'passByReference' => [true],
909 ],
910 'FREQUENCY' => [
911 'category' => Category::CATEGORY_STATISTICAL,
912 'functionCall' => [Functions::class, 'DUMMY'],
913 'argumentCount' => '2',
914 ],
915 'FTEST' => [
916 'category' => Category::CATEGORY_STATISTICAL,
917 'functionCall' => [Functions::class, 'DUMMY'],
918 'argumentCount' => '2',
919 ],
920 'FV' => [
921 'category' => Category::CATEGORY_FINANCIAL,
922 'functionCall' => [Financial::class, 'FV'],
923 'argumentCount' => '3-5',
924 ],
925 'FVSCHEDULE' => [
926 'category' => Category::CATEGORY_FINANCIAL,
927 'functionCall' => [Financial::class, 'FVSCHEDULE'],
928 'argumentCount' => '2',
929 ],
930 'GAMMADIST' => [
931 'category' => Category::CATEGORY_STATISTICAL,
932 'functionCall' => [Statistical::class, 'GAMMADIST'],
933 'argumentCount' => '4',
934 ],
935 'GAMMAINV' => [
936 'category' => Category::CATEGORY_STATISTICAL,
937 'functionCall' => [Statistical::class, 'GAMMAINV'],
938 'argumentCount' => '3',
939 ],
940 'GAMMALN' => [
941 'category' => Category::CATEGORY_STATISTICAL,
942 'functionCall' => [Statistical::class, 'GAMMALN'],
943 'argumentCount' => '1',
944 ],
945 'GCD' => [
946 'category' => Category::CATEGORY_MATH_AND_TRIG,
947 'functionCall' => [MathTrig::class, 'GCD'],
948 'argumentCount' => '1+',
949 ],
950 'GEOMEAN' => [
951 'category' => Category::CATEGORY_STATISTICAL,
952 'functionCall' => [Statistical::class, 'GEOMEAN'],
953 'argumentCount' => '1+',
954 ],
955 'GESTEP' => [
956 'category' => Category::CATEGORY_ENGINEERING,
957 'functionCall' => [Engineering::class, 'GESTEP'],
958 'argumentCount' => '1,2',
959 ],
960 'GETPIVOTDATA' => [
961 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
962 'functionCall' => [Functions::class, 'DUMMY'],
963 'argumentCount' => '2+',
964 ],
965 'GROWTH' => [
966 'category' => Category::CATEGORY_STATISTICAL,
967 'functionCall' => [Statistical::class, 'GROWTH'],
968 'argumentCount' => '1-4',
969 ],
970 'HARMEAN' => [
971 'category' => Category::CATEGORY_STATISTICAL,
972 'functionCall' => [Statistical::class, 'HARMEAN'],
973 'argumentCount' => '1+',
974 ],
975 'HEX2BIN' => [
976 'category' => Category::CATEGORY_ENGINEERING,
977 'functionCall' => [Engineering::class, 'HEXTOBIN'],
978 'argumentCount' => '1,2',
979 ],
980 'HEX2DEC' => [
981 'category' => Category::CATEGORY_ENGINEERING,
982 'functionCall' => [Engineering::class, 'HEXTODEC'],
983 'argumentCount' => '1',
984 ],
985 'HEX2OCT' => [
986 'category' => Category::CATEGORY_ENGINEERING,
987 'functionCall' => [Engineering::class, 'HEXTOOCT'],
988 'argumentCount' => '1,2',
989 ],
990 'HLOOKUP' => [
991 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
992 'functionCall' => [LookupRef::class, 'HLOOKUP'],
993 'argumentCount' => '3,4',
994 ],
995 'HOUR' => [
996 'category' => Category::CATEGORY_DATE_AND_TIME,
997 'functionCall' => [DateTime::class, 'HOUROFDAY'],
998 'argumentCount' => '1',
999 ],
1000 'HYPERLINK' => [
1001 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1002 'functionCall' => [LookupRef::class, 'HYPERLINK'],
1003 'argumentCount' => '1,2',
1004 'passCellReference' => true,
1005 ],
1006 'HYPGEOMDIST' => [
1007 'category' => Category::CATEGORY_STATISTICAL,
1008 'functionCall' => [Statistical::class, 'HYPGEOMDIST'],
1009 'argumentCount' => '4',
1010 ],
1011 'IF' => [
1012 'category' => Category::CATEGORY_LOGICAL,
1013 'functionCall' => [Logical::class, 'statementIf'],
1014 'argumentCount' => '1-3',
1015 ],
1016 'IFERROR' => [
1017 'category' => Category::CATEGORY_LOGICAL,
1018 'functionCall' => [Logical::class, 'IFERROR'],
1019 'argumentCount' => '2',
1020 ],
1021 'IMABS' => [
1022 'category' => Category::CATEGORY_ENGINEERING,
1023 'functionCall' => [Engineering::class, 'IMABS'],
1024 'argumentCount' => '1',
1025 ],
1026 'IMAGINARY' => [
1027 'category' => Category::CATEGORY_ENGINEERING,
1028 'functionCall' => [Engineering::class, 'IMAGINARY'],
1029 'argumentCount' => '1',
1030 ],
1031 'IMARGUMENT' => [
1032 'category' => Category::CATEGORY_ENGINEERING,
1033 'functionCall' => [Engineering::class, 'IMARGUMENT'],
1034 'argumentCount' => '1',
1035 ],
1036 'IMCONJUGATE' => [
1037 'category' => Category::CATEGORY_ENGINEERING,
1038 'functionCall' => [Engineering::class, 'IMCONJUGATE'],
1039 'argumentCount' => '1',
1040 ],
1041 'IMCOS' => [
1042 'category' => Category::CATEGORY_ENGINEERING,
1043 'functionCall' => [Engineering::class, 'IMCOS'],
1044 'argumentCount' => '1',
1045 ],
1046 'IMCOSH' => [
1047 'category' => Category::CATEGORY_ENGINEERING,
1048 'functionCall' => [Engineering::class, 'IMCOSH'],
1049 'argumentCount' => '1',
1050 ],
1051 'IMCOT' => [
1052 'category' => Category::CATEGORY_ENGINEERING,
1053 'functionCall' => [Engineering::class, 'IMCOT'],
1054 'argumentCount' => '1',
1055 ],
1056 'IMCSC' => [
1057 'category' => Category::CATEGORY_ENGINEERING,
1058 'functionCall' => [Engineering::class, 'IMCSC'],
1059 'argumentCount' => '1',
1060 ],
1061 'IMCSCH' => [
1062 'category' => Category::CATEGORY_ENGINEERING,
1063 'functionCall' => [Engineering::class, 'IMCSCH'],
1064 'argumentCount' => '1',
1065 ],
1066 'IMDIV' => [
1067 'category' => Category::CATEGORY_ENGINEERING,
1068 'functionCall' => [Engineering::class, 'IMDIV'],
1069 'argumentCount' => '2',
1070 ],
1071 'IMEXP' => [
1072 'category' => Category::CATEGORY_ENGINEERING,
1073 'functionCall' => [Engineering::class, 'IMEXP'],
1074 'argumentCount' => '1',
1075 ],
1076 'IMLN' => [
1077 'category' => Category::CATEGORY_ENGINEERING,
1078 'functionCall' => [Engineering::class, 'IMLN'],
1079 'argumentCount' => '1',
1080 ],
1081 'IMLOG10' => [
1082 'category' => Category::CATEGORY_ENGINEERING,
1083 'functionCall' => [Engineering::class, 'IMLOG10'],
1084 'argumentCount' => '1',
1085 ],
1086 'IMLOG2' => [
1087 'category' => Category::CATEGORY_ENGINEERING,
1088 'functionCall' => [Engineering::class, 'IMLOG2'],
1089 'argumentCount' => '1',
1090 ],
1091 'IMPOWER' => [
1092 'category' => Category::CATEGORY_ENGINEERING,
1093 'functionCall' => [Engineering::class, 'IMPOWER'],
1094 'argumentCount' => '2',
1095 ],
1096 'IMPRODUCT' => [
1097 'category' => Category::CATEGORY_ENGINEERING,
1098 'functionCall' => [Engineering::class, 'IMPRODUCT'],
1099 'argumentCount' => '1+',
1100 ],
1101 'IMREAL' => [
1102 'category' => Category::CATEGORY_ENGINEERING,
1103 'functionCall' => [Engineering::class, 'IMREAL'],
1104 'argumentCount' => '1',
1105 ],
1106 'IMSEC' => [
1107 'category' => Category::CATEGORY_ENGINEERING,
1108 'functionCall' => [Engineering::class, 'IMSEC'],
1109 'argumentCount' => '1',
1110 ],
1111 'IMSECH' => [
1112 'category' => Category::CATEGORY_ENGINEERING,
1113 'functionCall' => [Engineering::class, 'IMSECH'],
1114 'argumentCount' => '1',
1115 ],
1116 'IMSIN' => [
1117 'category' => Category::CATEGORY_ENGINEERING,
1118 'functionCall' => [Engineering::class, 'IMSIN'],
1119 'argumentCount' => '1',
1120 ],
1121 'IMSINH' => [
1122 'category' => Category::CATEGORY_ENGINEERING,
1123 'functionCall' => [Engineering::class, 'IMSINH'],
1124 'argumentCount' => '1',
1125 ],
1126 'IMSQRT' => [
1127 'category' => Category::CATEGORY_ENGINEERING,
1128 'functionCall' => [Engineering::class, 'IMSQRT'],
1129 'argumentCount' => '1',
1130 ],
1131 'IMSUB' => [
1132 'category' => Category::CATEGORY_ENGINEERING,
1133 'functionCall' => [Engineering::class, 'IMSUB'],
1134 'argumentCount' => '2',
1135 ],
1136 'IMSUM' => [
1137 'category' => Category::CATEGORY_ENGINEERING,
1138 'functionCall' => [Engineering::class, 'IMSUM'],
1139 'argumentCount' => '1+',
1140 ],
1141 'IMTAN' => [
1142 'category' => Category::CATEGORY_ENGINEERING,
1143 'functionCall' => [Engineering::class, 'IMTAN'],
1144 'argumentCount' => '1',
1145 ],
1146 'INDEX' => [
1147 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1148 'functionCall' => [LookupRef::class, 'INDEX'],
1149 'argumentCount' => '1-4',
1150 ],
1151 'INDIRECT' => [
1152 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1153 'functionCall' => [LookupRef::class, 'INDIRECT'],
1154 'argumentCount' => '1,2',
1155 'passCellReference' => true,
1156 ],
1157 'INFO' => [
1158 'category' => Category::CATEGORY_INFORMATION,
1159 'functionCall' => [Functions::class, 'DUMMY'],
1160 'argumentCount' => '1',
1161 ],
1162 'INT' => [
1163 'category' => Category::CATEGORY_MATH_AND_TRIG,
1164 'functionCall' => [MathTrig::class, 'INT'],
1165 'argumentCount' => '1',
1166 ],
1167 'INTERCEPT' => [
1168 'category' => Category::CATEGORY_STATISTICAL,
1169 'functionCall' => [Statistical::class, 'INTERCEPT'],
1170 'argumentCount' => '2',
1171 ],
1172 'INTRATE' => [
1173 'category' => Category::CATEGORY_FINANCIAL,
1174 'functionCall' => [Financial::class, 'INTRATE'],
1175 'argumentCount' => '4,5',
1176 ],
1177 'IPMT' => [
1178 'category' => Category::CATEGORY_FINANCIAL,
1179 'functionCall' => [Financial::class, 'IPMT'],
1180 'argumentCount' => '4-6',
1181 ],
1182 'IRR' => [
1183 'category' => Category::CATEGORY_FINANCIAL,
1184 'functionCall' => [Financial::class, 'IRR'],
1185 'argumentCount' => '1,2',
1186 ],
1187 'ISBLANK' => [
1188 'category' => Category::CATEGORY_INFORMATION,
1189 'functionCall' => [Functions::class, 'isBlank'],
1190 'argumentCount' => '1',
1191 ],
1192 'ISERR' => [
1193 'category' => Category::CATEGORY_INFORMATION,
1194 'functionCall' => [Functions::class, 'isErr'],
1195 'argumentCount' => '1',
1196 ],
1197 'ISERROR' => [
1198 'category' => Category::CATEGORY_INFORMATION,
1199 'functionCall' => [Functions::class, 'isError'],
1200 'argumentCount' => '1',
1201 ],
1202 'ISEVEN' => [
1203 'category' => Category::CATEGORY_INFORMATION,
1204 'functionCall' => [Functions::class, 'isEven'],
1205 'argumentCount' => '1',
1206 ],
1207 'ISFORMULA' => [
1208 'category' => Category::CATEGORY_INFORMATION,
1209 'functionCall' => [Functions::class, 'isFormula'],
1210 'argumentCount' => '1',
1211 'passCellReference' => true,
1212 'passByReference' => [true],
1213 ],
1214 'ISLOGICAL' => [
1215 'category' => Category::CATEGORY_INFORMATION,
1216 'functionCall' => [Functions::class, 'isLogical'],
1217 'argumentCount' => '1',
1218 ],
1219 'ISNA' => [
1220 'category' => Category::CATEGORY_INFORMATION,
1221 'functionCall' => [Functions::class, 'isNa'],
1222 'argumentCount' => '1',
1223 ],
1224 'ISNONTEXT' => [
1225 'category' => Category::CATEGORY_INFORMATION,
1226 'functionCall' => [Functions::class, 'isNonText'],
1227 'argumentCount' => '1',
1228 ],
1229 'ISNUMBER' => [
1230 'category' => Category::CATEGORY_INFORMATION,
1231 'functionCall' => [Functions::class, 'isNumber'],
1232 'argumentCount' => '1',
1233 ],
1234 'ISODD' => [
1235 'category' => Category::CATEGORY_INFORMATION,
1236 'functionCall' => [Functions::class, 'isOdd'],
1237 'argumentCount' => '1',
1238 ],
1239 'ISOWEEKNUM' => [
1240 'category' => Category::CATEGORY_DATE_AND_TIME,
1241 'functionCall' => [DateTime::class, 'ISOWEEKNUM'],
1242 'argumentCount' => '1',
1243 ],
1244 'ISPMT' => [
1245 'category' => Category::CATEGORY_FINANCIAL,
1246 'functionCall' => [Financial::class, 'ISPMT'],
1247 'argumentCount' => '4',
1248 ],
1249 'ISREF' => [
1250 'category' => Category::CATEGORY_INFORMATION,
1251 'functionCall' => [Functions::class, 'DUMMY'],
1252 'argumentCount' => '1',
1253 ],
1254 'ISTEXT' => [
1255 'category' => Category::CATEGORY_INFORMATION,
1256 'functionCall' => [Functions::class, 'isText'],
1257 'argumentCount' => '1',
1258 ],
1259 'JIS' => [
1260 'category' => Category::CATEGORY_TEXT_AND_DATA,
1261 'functionCall' => [Functions::class, 'DUMMY'],
1262 'argumentCount' => '1',
1263 ],
1264 'KURT' => [
1265 'category' => Category::CATEGORY_STATISTICAL,
1266 'functionCall' => [Statistical::class, 'KURT'],
1267 'argumentCount' => '1+',
1268 ],
1269 'LARGE' => [
1270 'category' => Category::CATEGORY_STATISTICAL,
1271 'functionCall' => [Statistical::class, 'LARGE'],
1272 'argumentCount' => '2',
1273 ],
1274 'LCM' => [
1275 'category' => Category::CATEGORY_MATH_AND_TRIG,
1276 'functionCall' => [MathTrig::class, 'LCM'],
1277 'argumentCount' => '1+',
1278 ],
1279 'LEFT' => [
1280 'category' => Category::CATEGORY_TEXT_AND_DATA,
1281 'functionCall' => [TextData::class, 'LEFT'],
1282 'argumentCount' => '1,2',
1283 ],
1284 'LEFTB' => [
1285 'category' => Category::CATEGORY_TEXT_AND_DATA,
1286 'functionCall' => [TextData::class, 'LEFT'],
1287 'argumentCount' => '1,2',
1288 ],
1289 'LEN' => [
1290 'category' => Category::CATEGORY_TEXT_AND_DATA,
1291 'functionCall' => [TextData::class, 'STRINGLENGTH'],
1292 'argumentCount' => '1',
1293 ],
1294 'LENB' => [
1295 'category' => Category::CATEGORY_TEXT_AND_DATA,
1296 'functionCall' => [TextData::class, 'STRINGLENGTH'],
1297 'argumentCount' => '1',
1298 ],
1299 'LINEST' => [
1300 'category' => Category::CATEGORY_STATISTICAL,
1301 'functionCall' => [Statistical::class, 'LINEST'],
1302 'argumentCount' => '1-4',
1303 ],
1304 'LN' => [
1305 'category' => Category::CATEGORY_MATH_AND_TRIG,
1306 'functionCall' => 'log',
1307 'argumentCount' => '1',
1308 ],
1309 'LOG' => [
1310 'category' => Category::CATEGORY_MATH_AND_TRIG,
1311 'functionCall' => [MathTrig::class, 'logBase'],
1312 'argumentCount' => '1,2',
1313 ],
1314 'LOG10' => [
1315 'category' => Category::CATEGORY_MATH_AND_TRIG,
1316 'functionCall' => 'log10',
1317 'argumentCount' => '1',
1318 ],
1319 'LOGEST' => [
1320 'category' => Category::CATEGORY_STATISTICAL,
1321 'functionCall' => [Statistical::class, 'LOGEST'],
1322 'argumentCount' => '1-4',
1323 ],
1324 'LOGINV' => [
1325 'category' => Category::CATEGORY_STATISTICAL,
1326 'functionCall' => [Statistical::class, 'LOGINV'],
1327 'argumentCount' => '3',
1328 ],
1329 'LOGNORMDIST' => [
1330 'category' => Category::CATEGORY_STATISTICAL,
1331 'functionCall' => [Statistical::class, 'LOGNORMDIST'],
1332 'argumentCount' => '3',
1333 ],
1334 'LOOKUP' => [
1335 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1336 'functionCall' => [LookupRef::class, 'LOOKUP'],
1337 'argumentCount' => '2,3',
1338 ],
1339 'LOWER' => [
1340 'category' => Category::CATEGORY_TEXT_AND_DATA,
1341 'functionCall' => [TextData::class, 'LOWERCASE'],
1342 'argumentCount' => '1',
1343 ],
1344 'MATCH' => [
1345 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1346 'functionCall' => [LookupRef::class, 'MATCH'],
1347 'argumentCount' => '2,3',
1348 ],
1349 'MAX' => [
1350 'category' => Category::CATEGORY_STATISTICAL,
1351 'functionCall' => [Statistical::class, 'MAX'],
1352 'argumentCount' => '1+',
1353 ],
1354 'MAXA' => [
1355 'category' => Category::CATEGORY_STATISTICAL,
1356 'functionCall' => [Statistical::class, 'MAXA'],
1357 'argumentCount' => '1+',
1358 ],
1359 'MAXIF' => [
1360 'category' => Category::CATEGORY_STATISTICAL,
1361 'functionCall' => [Statistical::class, 'MAXIF'],
1362 'argumentCount' => '2+',
1363 ],
1364 'MDETERM' => [
1365 'category' => Category::CATEGORY_MATH_AND_TRIG,
1366 'functionCall' => [MathTrig::class, 'MDETERM'],
1367 'argumentCount' => '1',
1368 ],
1369 'MDURATION' => [
1370 'category' => Category::CATEGORY_FINANCIAL,
1371 'functionCall' => [Functions::class, 'DUMMY'],
1372 'argumentCount' => '5,6',
1373 ],
1374 'MEDIAN' => [
1375 'category' => Category::CATEGORY_STATISTICAL,
1376 'functionCall' => [Statistical::class, 'MEDIAN'],
1377 'argumentCount' => '1+',
1378 ],
1379 'MEDIANIF' => [
1380 'category' => Category::CATEGORY_STATISTICAL,
1381 'functionCall' => [Functions::class, 'DUMMY'],
1382 'argumentCount' => '2+',
1383 ],
1384 'MID' => [
1385 'category' => Category::CATEGORY_TEXT_AND_DATA,
1386 'functionCall' => [TextData::class, 'MID'],
1387 'argumentCount' => '3',
1388 ],
1389 'MIDB' => [
1390 'category' => Category::CATEGORY_TEXT_AND_DATA,
1391 'functionCall' => [TextData::class, 'MID'],
1392 'argumentCount' => '3',
1393 ],
1394 'MIN' => [
1395 'category' => Category::CATEGORY_STATISTICAL,
1396 'functionCall' => [Statistical::class, 'MIN'],
1397 'argumentCount' => '1+',
1398 ],
1399 'MINA' => [
1400 'category' => Category::CATEGORY_STATISTICAL,
1401 'functionCall' => [Statistical::class, 'MINA'],
1402 'argumentCount' => '1+',
1403 ],
1404 'MINIF' => [
1405 'category' => Category::CATEGORY_STATISTICAL,
1406 'functionCall' => [Statistical::class, 'MINIF'],
1407 'argumentCount' => '2+',
1408 ],
1409 'MINUTE' => [
1410 'category' => Category::CATEGORY_DATE_AND_TIME,
1411 'functionCall' => [DateTime::class, 'MINUTE'],
1412 'argumentCount' => '1',
1413 ],
1414 'MINVERSE' => [
1415 'category' => Category::CATEGORY_MATH_AND_TRIG,
1416 'functionCall' => [MathTrig::class, 'MINVERSE'],
1417 'argumentCount' => '1',
1418 ],
1419 'MIRR' => [
1420 'category' => Category::CATEGORY_FINANCIAL,
1421 'functionCall' => [Financial::class, 'MIRR'],
1422 'argumentCount' => '3',
1423 ],
1424 'MMULT' => [
1425 'category' => Category::CATEGORY_MATH_AND_TRIG,
1426 'functionCall' => [MathTrig::class, 'MMULT'],
1427 'argumentCount' => '2',
1428 ],
1429 'MOD' => [
1430 'category' => Category::CATEGORY_MATH_AND_TRIG,
1431 'functionCall' => [MathTrig::class, 'MOD'],
1432 'argumentCount' => '2',
1433 ],
1434 'MODE' => [
1435 'category' => Category::CATEGORY_STATISTICAL,
1436 'functionCall' => [Statistical::class, 'MODE'],
1437 'argumentCount' => '1+',
1438 ],
1439 'MODE.SNGL' => [
1440 'category' => Category::CATEGORY_STATISTICAL,
1441 'functionCall' => [Statistical::class, 'MODE'],
1442 'argumentCount' => '1+',
1443 ],
1444 'MONTH' => [
1445 'category' => Category::CATEGORY_DATE_AND_TIME,
1446 'functionCall' => [DateTime::class, 'MONTHOFYEAR'],
1447 'argumentCount' => '1',
1448 ],
1449 'MROUND' => [
1450 'category' => Category::CATEGORY_MATH_AND_TRIG,
1451 'functionCall' => [MathTrig::class, 'MROUND'],
1452 'argumentCount' => '2',
1453 ],
1454 'MULTINOMIAL' => [
1455 'category' => Category::CATEGORY_MATH_AND_TRIG,
1456 'functionCall' => [MathTrig::class, 'MULTINOMIAL'],
1457 'argumentCount' => '1+',
1458 ],
1459 'N' => [
1460 'category' => Category::CATEGORY_INFORMATION,
1461 'functionCall' => [Functions::class, 'n'],
1462 'argumentCount' => '1',
1463 ],
1464 'NA' => [
1465 'category' => Category::CATEGORY_INFORMATION,
1466 'functionCall' => [Functions::class, 'NA'],
1467 'argumentCount' => '0',
1468 ],
1469 'NEGBINOMDIST' => [
1470 'category' => Category::CATEGORY_STATISTICAL,
1471 'functionCall' => [Statistical::class, 'NEGBINOMDIST'],
1472 'argumentCount' => '3',
1473 ],
1474 'NETWORKDAYS' => [
1475 'category' => Category::CATEGORY_DATE_AND_TIME,
1476 'functionCall' => [DateTime::class, 'NETWORKDAYS'],
1477 'argumentCount' => '2+',
1478 ],
1479 'NOMINAL' => [
1480 'category' => Category::CATEGORY_FINANCIAL,
1481 'functionCall' => [Financial::class, 'NOMINAL'],
1482 'argumentCount' => '2',
1483 ],
1484 'NORMDIST' => [
1485 'category' => Category::CATEGORY_STATISTICAL,
1486 'functionCall' => [Statistical::class, 'NORMDIST'],
1487 'argumentCount' => '4',
1488 ],
1489 'NORMINV' => [
1490 'category' => Category::CATEGORY_STATISTICAL,
1491 'functionCall' => [Statistical::class, 'NORMINV'],
1492 'argumentCount' => '3',
1493 ],
1494 'NORMSDIST' => [
1495 'category' => Category::CATEGORY_STATISTICAL,
1496 'functionCall' => [Statistical::class, 'NORMSDIST'],
1497 'argumentCount' => '1',
1498 ],
1499 'NORMSINV' => [
1500 'category' => Category::CATEGORY_STATISTICAL,
1501 'functionCall' => [Statistical::class, 'NORMSINV'],
1502 'argumentCount' => '1',
1503 ],
1504 'NOT' => [
1505 'category' => Category::CATEGORY_LOGICAL,
1506 'functionCall' => [Logical::class, 'NOT'],
1507 'argumentCount' => '1',
1508 ],
1509 'NOW' => [
1510 'category' => Category::CATEGORY_DATE_AND_TIME,
1511 'functionCall' => [DateTime::class, 'DATETIMENOW'],
1512 'argumentCount' => '0',
1513 ],
1514 'NPER' => [
1515 'category' => Category::CATEGORY_FINANCIAL,
1516 'functionCall' => [Financial::class, 'NPER'],
1517 'argumentCount' => '3-5',
1518 ],
1519 'NPV' => [
1520 'category' => Category::CATEGORY_FINANCIAL,
1521 'functionCall' => [Financial::class, 'NPV'],
1522 'argumentCount' => '2+',
1523 ],
1524 'NUMBERVALUE' => [
1525 'category' => Category::CATEGORY_TEXT_AND_DATA,
1526 'functionCall' => [TextData::class, 'NUMBERVALUE'],
1527 'argumentCount' => '1+',
1528 ],
1529 'OCT2BIN' => [
1530 'category' => Category::CATEGORY_ENGINEERING,
1531 'functionCall' => [Engineering::class, 'OCTTOBIN'],
1532 'argumentCount' => '1,2',
1533 ],
1534 'OCT2DEC' => [
1535 'category' => Category::CATEGORY_ENGINEERING,
1536 'functionCall' => [Engineering::class, 'OCTTODEC'],
1537 'argumentCount' => '1',
1538 ],
1539 'OCT2HEX' => [
1540 'category' => Category::CATEGORY_ENGINEERING,
1541 'functionCall' => [Engineering::class, 'OCTTOHEX'],
1542 'argumentCount' => '1,2',
1543 ],
1544 'ODD' => [
1545 'category' => Category::CATEGORY_MATH_AND_TRIG,
1546 'functionCall' => [MathTrig::class, 'ODD'],
1547 'argumentCount' => '1',
1548 ],
1549 'ODDFPRICE' => [
1550 'category' => Category::CATEGORY_FINANCIAL,
1551 'functionCall' => [Functions::class, 'DUMMY'],
1552 'argumentCount' => '8,9',
1553 ],
1554 'ODDFYIELD' => [
1555 'category' => Category::CATEGORY_FINANCIAL,
1556 'functionCall' => [Functions::class, 'DUMMY'],
1557 'argumentCount' => '8,9',
1558 ],
1559 'ODDLPRICE' => [
1560 'category' => Category::CATEGORY_FINANCIAL,
1561 'functionCall' => [Functions::class, 'DUMMY'],
1562 'argumentCount' => '7,8',
1563 ],
1564 'ODDLYIELD' => [
1565 'category' => Category::CATEGORY_FINANCIAL,
1566 'functionCall' => [Functions::class, 'DUMMY'],
1567 'argumentCount' => '7,8',
1568 ],
1569 'OFFSET' => [
1570 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1571 'functionCall' => [LookupRef::class, 'OFFSET'],
1572 'argumentCount' => '3-5',
1573 'passCellReference' => true,
1574 'passByReference' => [true],
1575 ],
1576 'OR' => [
1577 'category' => Category::CATEGORY_LOGICAL,
1578 'functionCall' => [Logical::class, 'logicalOr'],
1579 'argumentCount' => '1+',
1580 ],
1581 'PDURATION' => [
1582 'category' => Category::CATEGORY_FINANCIAL,
1583 'functionCall' => [Financial::class, 'PDURATION'],
1584 'argumentCount' => '3',
1585 ],
1586 'PEARSON' => [
1587 'category' => Category::CATEGORY_STATISTICAL,
1588 'functionCall' => [Statistical::class, 'CORREL'],
1589 'argumentCount' => '2',
1590 ],
1591 'PERCENTILE' => [
1592 'category' => Category::CATEGORY_STATISTICAL,
1593 'functionCall' => [Statistical::class, 'PERCENTILE'],
1594 'argumentCount' => '2',
1595 ],
1596 'PERCENTRANK' => [
1597 'category' => Category::CATEGORY_STATISTICAL,
1598 'functionCall' => [Statistical::class, 'PERCENTRANK'],
1599 'argumentCount' => '2,3',
1600 ],
1601 'PERMUT' => [
1602 'category' => Category::CATEGORY_STATISTICAL,
1603 'functionCall' => [Statistical::class, 'PERMUT'],
1604 'argumentCount' => '2',
1605 ],
1606 'PHONETIC' => [
1607 'category' => Category::CATEGORY_TEXT_AND_DATA,
1608 'functionCall' => [Functions::class, 'DUMMY'],
1609 'argumentCount' => '1',
1610 ],
1611 'PI' => [
1612 'category' => Category::CATEGORY_MATH_AND_TRIG,
1613 'functionCall' => 'pi',
1614 'argumentCount' => '0',
1615 ],
1616 'PMT' => [
1617 'category' => Category::CATEGORY_FINANCIAL,
1618 'functionCall' => [Financial::class, 'PMT'],
1619 'argumentCount' => '3-5',
1620 ],
1621 'POISSON' => [
1622 'category' => Category::CATEGORY_STATISTICAL,
1623 'functionCall' => [Statistical::class, 'POISSON'],
1624 'argumentCount' => '3',
1625 ],
1626 'POWER' => [
1627 'category' => Category::CATEGORY_MATH_AND_TRIG,
1628 'functionCall' => [MathTrig::class, 'POWER'],
1629 'argumentCount' => '2',
1630 ],
1631 'PPMT' => [
1632 'category' => Category::CATEGORY_FINANCIAL,
1633 'functionCall' => [Financial::class, 'PPMT'],
1634 'argumentCount' => '4-6',
1635 ],
1636 'PRICE' => [
1637 'category' => Category::CATEGORY_FINANCIAL,
1638 'functionCall' => [Financial::class, 'PRICE'],
1639 'argumentCount' => '6,7',
1640 ],
1641 'PRICEDISC' => [
1642 'category' => Category::CATEGORY_FINANCIAL,
1643 'functionCall' => [Financial::class, 'PRICEDISC'],
1644 'argumentCount' => '4,5',
1645 ],
1646 'PRICEMAT' => [
1647 'category' => Category::CATEGORY_FINANCIAL,
1648 'functionCall' => [Financial::class, 'PRICEMAT'],
1649 'argumentCount' => '5,6',
1650 ],
1651 'PROB' => [
1652 'category' => Category::CATEGORY_STATISTICAL,
1653 'functionCall' => [Functions::class, 'DUMMY'],
1654 'argumentCount' => '3,4',
1655 ],
1656 'PRODUCT' => [
1657 'category' => Category::CATEGORY_MATH_AND_TRIG,
1658 'functionCall' => [MathTrig::class, 'PRODUCT'],
1659 'argumentCount' => '1+',
1660 ],
1661 'PROPER' => [
1662 'category' => Category::CATEGORY_TEXT_AND_DATA,
1663 'functionCall' => [TextData::class, 'PROPERCASE'],
1664 'argumentCount' => '1',
1665 ],
1666 'PV' => [
1667 'category' => Category::CATEGORY_FINANCIAL,
1668 'functionCall' => [Financial::class, 'PV'],
1669 'argumentCount' => '3-5',
1670 ],
1671 'QUARTILE' => [
1672 'category' => Category::CATEGORY_STATISTICAL,
1673 'functionCall' => [Statistical::class, 'QUARTILE'],
1674 'argumentCount' => '2',
1675 ],
1676 'QUOTIENT' => [
1677 'category' => Category::CATEGORY_MATH_AND_TRIG,
1678 'functionCall' => [MathTrig::class, 'QUOTIENT'],
1679 'argumentCount' => '2',
1680 ],
1681 'RADIANS' => [
1682 'category' => Category::CATEGORY_MATH_AND_TRIG,
1683 'functionCall' => 'deg2rad',
1684 'argumentCount' => '1',
1685 ],
1686 'RAND' => [
1687 'category' => Category::CATEGORY_MATH_AND_TRIG,
1688 'functionCall' => [MathTrig::class, 'RAND'],
1689 'argumentCount' => '0',
1690 ],
1691 'RANDBETWEEN' => [
1692 'category' => Category::CATEGORY_MATH_AND_TRIG,
1693 'functionCall' => [MathTrig::class, 'RAND'],
1694 'argumentCount' => '2',
1695 ],
1696 'RANK' => [
1697 'category' => Category::CATEGORY_STATISTICAL,
1698 'functionCall' => [Statistical::class, 'RANK'],
1699 'argumentCount' => '2,3',
1700 ],
1701 'RATE' => [
1702 'category' => Category::CATEGORY_FINANCIAL,
1703 'functionCall' => [Financial::class, 'RATE'],
1704 'argumentCount' => '3-6',
1705 ],
1706 'RECEIVED' => [
1707 'category' => Category::CATEGORY_FINANCIAL,
1708 'functionCall' => [Financial::class, 'RECEIVED'],
1709 'argumentCount' => '4-5',
1710 ],
1711 'REPLACE' => [
1712 'category' => Category::CATEGORY_TEXT_AND_DATA,
1713 'functionCall' => [TextData::class, 'REPLACE'],
1714 'argumentCount' => '4',
1715 ],
1716 'REPLACEB' => [
1717 'category' => Category::CATEGORY_TEXT_AND_DATA,
1718 'functionCall' => [TextData::class, 'REPLACE'],
1719 'argumentCount' => '4',
1720 ],
1721 'REPT' => [
1722 'category' => Category::CATEGORY_TEXT_AND_DATA,
1723 'functionCall' => 'str_repeat',
1724 'argumentCount' => '2',
1725 ],
1726 'RIGHT' => [
1727 'category' => Category::CATEGORY_TEXT_AND_DATA,
1728 'functionCall' => [TextData::class, 'RIGHT'],
1729 'argumentCount' => '1,2',
1730 ],
1731 'RIGHTB' => [
1732 'category' => Category::CATEGORY_TEXT_AND_DATA,
1733 'functionCall' => [TextData::class, 'RIGHT'],
1734 'argumentCount' => '1,2',
1735 ],
1736 'ROMAN' => [
1737 'category' => Category::CATEGORY_MATH_AND_TRIG,
1738 'functionCall' => [MathTrig::class, 'ROMAN'],
1739 'argumentCount' => '1,2',
1740 ],
1741 'ROUND' => [
1742 'category' => Category::CATEGORY_MATH_AND_TRIG,
1743 'functionCall' => 'round',
1744 'argumentCount' => '2',
1745 ],
1746 'ROUNDDOWN' => [
1747 'category' => Category::CATEGORY_MATH_AND_TRIG,
1748 'functionCall' => [MathTrig::class, 'ROUNDDOWN'],
1749 'argumentCount' => '2',
1750 ],
1751 'ROUNDUP' => [
1752 'category' => Category::CATEGORY_MATH_AND_TRIG,
1753 'functionCall' => [MathTrig::class, 'ROUNDUP'],
1754 'argumentCount' => '2',
1755 ],
1756 'ROW' => [
1757 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1758 'functionCall' => [LookupRef::class, 'ROW'],
1759 'argumentCount' => '-1',
1760 'passByReference' => [true],
1761 ],
1762 'ROWS' => [
1763 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1764 'functionCall' => [LookupRef::class, 'ROWS'],
1765 'argumentCount' => '1',
1766 ],
1767 'RRI' => [
1768 'category' => Category::CATEGORY_FINANCIAL,
1769 'functionCall' => [Financial::class, 'RRI'],
1770 'argumentCount' => '3',
1771 ],
1772 'RSQ' => [
1773 'category' => Category::CATEGORY_STATISTICAL,
1774 'functionCall' => [Statistical::class, 'RSQ'],
1775 'argumentCount' => '2',
1776 ],
1777 'RTD' => [
1778 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
1779 'functionCall' => [Functions::class, 'DUMMY'],
1780 'argumentCount' => '1+',
1781 ],
1782 'SEARCH' => [
1783 'category' => Category::CATEGORY_TEXT_AND_DATA,
1784 'functionCall' => [TextData::class, 'SEARCHINSENSITIVE'],
1785 'argumentCount' => '2,3',
1786 ],
1787 'SEARCHB' => [
1788 'category' => Category::CATEGORY_TEXT_AND_DATA,
1789 'functionCall' => [TextData::class, 'SEARCHINSENSITIVE'],
1790 'argumentCount' => '2,3',
1791 ],
1792 'SEC' => [
1793 'category' => Category::CATEGORY_MATH_AND_TRIG,
1794 'functionCall' => [MathTrig::class, 'SEC'],
1795 'argumentCount' => '1',
1796 ],
1797 'SECH' => [
1798 'category' => Category::CATEGORY_MATH_AND_TRIG,
1799 'functionCall' => [MathTrig::class, 'SECH'],
1800 'argumentCount' => '1',
1801 ],
1802 'SECOND' => [
1803 'category' => Category::CATEGORY_DATE_AND_TIME,
1804 'functionCall' => [DateTime::class, 'SECOND'],
1805 'argumentCount' => '1',
1806 ],
1807 'SERIESSUM' => [
1808 'category' => Category::CATEGORY_MATH_AND_TRIG,
1809 'functionCall' => [MathTrig::class, 'SERIESSUM'],
1810 'argumentCount' => '4',
1811 ],
1812 'SIGN' => [
1813 'category' => Category::CATEGORY_MATH_AND_TRIG,
1814 'functionCall' => [MathTrig::class, 'SIGN'],
1815 'argumentCount' => '1',
1816 ],
1817 'SIN' => [
1818 'category' => Category::CATEGORY_MATH_AND_TRIG,
1819 'functionCall' => 'sin',
1820 'argumentCount' => '1',
1821 ],
1822 'SINH' => [
1823 'category' => Category::CATEGORY_MATH_AND_TRIG,
1824 'functionCall' => 'sinh',
1825 'argumentCount' => '1',
1826 ],
1827 'SKEW' => [
1828 'category' => Category::CATEGORY_STATISTICAL,
1829 'functionCall' => [Statistical::class, 'SKEW'],
1830 'argumentCount' => '1+',
1831 ],
1832 'SLN' => [
1833 'category' => Category::CATEGORY_FINANCIAL,
1834 'functionCall' => [Financial::class, 'SLN'],
1835 'argumentCount' => '3',
1836 ],
1837 'SLOPE' => [
1838 'category' => Category::CATEGORY_STATISTICAL,
1839 'functionCall' => [Statistical::class, 'SLOPE'],
1840 'argumentCount' => '2',
1841 ],
1842 'SMALL' => [
1843 'category' => Category::CATEGORY_STATISTICAL,
1844 'functionCall' => [Statistical::class, 'SMALL'],
1845 'argumentCount' => '2',
1846 ],
1847 'SQRT' => [
1848 'category' => Category::CATEGORY_MATH_AND_TRIG,
1849 'functionCall' => 'sqrt',
1850 'argumentCount' => '1',
1851 ],
1852 'SQRTPI' => [
1853 'category' => Category::CATEGORY_MATH_AND_TRIG,
1854 'functionCall' => [MathTrig::class, 'SQRTPI'],
1855 'argumentCount' => '1',
1856 ],
1857 'STANDARDIZE' => [
1858 'category' => Category::CATEGORY_STATISTICAL,
1859 'functionCall' => [Statistical::class, 'STANDARDIZE'],
1860 'argumentCount' => '3',
1861 ],
1862 'STDEV' => [
1863 'category' => Category::CATEGORY_STATISTICAL,
1864 'functionCall' => [Statistical::class, 'STDEV'],
1865 'argumentCount' => '1+',
1866 ],
1867 'STDEV.S' => [
1868 'category' => Category::CATEGORY_STATISTICAL,
1869 'functionCall' => [Statistical::class, 'STDEV'],
1870 'argumentCount' => '1+',
1871 ],
1872 'STDEV.P' => [
1873 'category' => Category::CATEGORY_STATISTICAL,
1874 'functionCall' => [Statistical::class, 'STDEVP'],
1875 'argumentCount' => '1+',
1876 ],
1877 'STDEVA' => [
1878 'category' => Category::CATEGORY_STATISTICAL,
1879 'functionCall' => [Statistical::class, 'STDEVA'],
1880 'argumentCount' => '1+',
1881 ],
1882 'STDEVP' => [
1883 'category' => Category::CATEGORY_STATISTICAL,
1884 'functionCall' => [Statistical::class, 'STDEVP'],
1885 'argumentCount' => '1+',
1886 ],
1887 'STDEVPA' => [
1888 'category' => Category::CATEGORY_STATISTICAL,
1889 'functionCall' => [Statistical::class, 'STDEVPA'],
1890 'argumentCount' => '1+',
1891 ],
1892 'STEYX' => [
1893 'category' => Category::CATEGORY_STATISTICAL,
1894 'functionCall' => [Statistical::class, 'STEYX'],
1895 'argumentCount' => '2',
1896 ],
1897 'SUBSTITUTE' => [
1898 'category' => Category::CATEGORY_TEXT_AND_DATA,
1899 'functionCall' => [TextData::class, 'SUBSTITUTE'],
1900 'argumentCount' => '3,4',
1901 ],
1902 'SUBTOTAL' => [
1903 'category' => Category::CATEGORY_MATH_AND_TRIG,
1904 'functionCall' => [MathTrig::class, 'SUBTOTAL'],
1905 'argumentCount' => '2+',
1906 'passCellReference' => true,
1907 ],
1908 'SUM' => [
1909 'category' => Category::CATEGORY_MATH_AND_TRIG,
1910 'functionCall' => [MathTrig::class, 'SUM'],
1911 'argumentCount' => '1+',
1912 ],
1913 'SUMIF' => [
1914 'category' => Category::CATEGORY_MATH_AND_TRIG,
1915 'functionCall' => [MathTrig::class, 'SUMIF'],
1916 'argumentCount' => '2,3',
1917 ],
1918 'SUMIFS' => [
1919 'category' => Category::CATEGORY_MATH_AND_TRIG,
1920 'functionCall' => [MathTrig::class, 'SUMIFS'],
1921 'argumentCount' => '3+',
1922 ],
1923 'SUMPRODUCT' => [
1924 'category' => Category::CATEGORY_MATH_AND_TRIG,
1925 'functionCall' => [MathTrig::class, 'SUMPRODUCT'],
1926 'argumentCount' => '1+',
1927 ],
1928 'SUMSQ' => [
1929 'category' => Category::CATEGORY_MATH_AND_TRIG,
1930 'functionCall' => [MathTrig::class, 'SUMSQ'],
1931 'argumentCount' => '1+',
1932 ],
1933 'SUMX2MY2' => [
1934 'category' => Category::CATEGORY_MATH_AND_TRIG,
1935 'functionCall' => [MathTrig::class, 'SUMX2MY2'],
1936 'argumentCount' => '2',
1937 ],
1938 'SUMX2PY2' => [
1939 'category' => Category::CATEGORY_MATH_AND_TRIG,
1940 'functionCall' => [MathTrig::class, 'SUMX2PY2'],
1941 'argumentCount' => '2',
1942 ],
1943 'SUMXMY2' => [
1944 'category' => Category::CATEGORY_MATH_AND_TRIG,
1945 'functionCall' => [MathTrig::class, 'SUMXMY2'],
1946 'argumentCount' => '2',
1947 ],
1948 'SWITCH' => [
1949 'category' => Category::CATEGORY_LOGICAL,
1950 'functionCall' => [Logical::class, 'statementSwitch'],
1951 'argumentCount' => '3+',
1952 ],
1953 'SYD' => [
1954 'category' => Category::CATEGORY_FINANCIAL,
1955 'functionCall' => [Financial::class, 'SYD'],
1956 'argumentCount' => '4',
1957 ],
1958 'T' => [
1959 'category' => Category::CATEGORY_TEXT_AND_DATA,
1960 'functionCall' => [TextData::class, 'RETURNSTRING'],
1961 'argumentCount' => '1',
1962 ],
1963 'TAN' => [
1964 'category' => Category::CATEGORY_MATH_AND_TRIG,
1965 'functionCall' => 'tan',
1966 'argumentCount' => '1',
1967 ],
1968 'TANH' => [
1969 'category' => Category::CATEGORY_MATH_AND_TRIG,
1970 'functionCall' => 'tanh',
1971 'argumentCount' => '1',
1972 ],
1973 'TBILLEQ' => [
1974 'category' => Category::CATEGORY_FINANCIAL,
1975 'functionCall' => [Financial::class, 'TBILLEQ'],
1976 'argumentCount' => '3',
1977 ],
1978 'TBILLPRICE' => [
1979 'category' => Category::CATEGORY_FINANCIAL,
1980 'functionCall' => [Financial::class, 'TBILLPRICE'],
1981 'argumentCount' => '3',
1982 ],
1983 'TBILLYIELD' => [
1984 'category' => Category::CATEGORY_FINANCIAL,
1985 'functionCall' => [Financial::class, 'TBILLYIELD'],
1986 'argumentCount' => '3',
1987 ],
1988 'TDIST' => [
1989 'category' => Category::CATEGORY_STATISTICAL,
1990 'functionCall' => [Statistical::class, 'TDIST'],
1991 'argumentCount' => '3',
1992 ],
1993 'TEXT' => [
1994 'category' => Category::CATEGORY_TEXT_AND_DATA,
1995 'functionCall' => [TextData::class, 'TEXTFORMAT'],
1996 'argumentCount' => '2',
1997 ],
1998 'TEXTJOIN' => [
1999 'category' => Category::CATEGORY_TEXT_AND_DATA,
2000 'functionCall' => [TextData::class, 'TEXTJOIN'],
2001 'argumentCount' => '3+',
2002 ],
2003 'TIME' => [
2004 'category' => Category::CATEGORY_DATE_AND_TIME,
2005 'functionCall' => [DateTime::class, 'TIME'],
2006 'argumentCount' => '3',
2007 ],
2008 'TIMEVALUE' => [
2009 'category' => Category::CATEGORY_DATE_AND_TIME,
2010 'functionCall' => [DateTime::class, 'TIMEVALUE'],
2011 'argumentCount' => '1',
2012 ],
2013 'TINV' => [
2014 'category' => Category::CATEGORY_STATISTICAL,
2015 'functionCall' => [Statistical::class, 'TINV'],
2016 'argumentCount' => '2',
2017 ],
2018 'TODAY' => [
2019 'category' => Category::CATEGORY_DATE_AND_TIME,
2020 'functionCall' => [DateTime::class, 'DATENOW'],
2021 'argumentCount' => '0',
2022 ],
2023 'TRANSPOSE' => [
2024 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
2025 'functionCall' => [LookupRef::class, 'TRANSPOSE'],
2026 'argumentCount' => '1',
2027 ],
2028 'TREND' => [
2029 'category' => Category::CATEGORY_STATISTICAL,
2030 'functionCall' => [Statistical::class, 'TREND'],
2031 'argumentCount' => '1-4',
2032 ],
2033 'TRIM' => [
2034 'category' => Category::CATEGORY_TEXT_AND_DATA,
2035 'functionCall' => [TextData::class, 'TRIMSPACES'],
2036 'argumentCount' => '1',
2037 ],
2038 'TRIMMEAN' => [
2039 'category' => Category::CATEGORY_STATISTICAL,
2040 'functionCall' => [Statistical::class, 'TRIMMEAN'],
2041 'argumentCount' => '2',
2042 ],
2043 'TRUE' => [
2044 'category' => Category::CATEGORY_LOGICAL,
2045 'functionCall' => [Logical::class, 'TRUE'],
2046 'argumentCount' => '0',
2047 ],
2048 'TRUNC' => [
2049 'category' => Category::CATEGORY_MATH_AND_TRIG,
2050 'functionCall' => [MathTrig::class, 'TRUNC'],
2051 'argumentCount' => '1,2',
2052 ],
2053 'TTEST' => [
2054 'category' => Category::CATEGORY_STATISTICAL,
2055 'functionCall' => [Functions::class, 'DUMMY'],
2056 'argumentCount' => '4',
2057 ],
2058 'TYPE' => [
2059 'category' => Category::CATEGORY_INFORMATION,
2060 'functionCall' => [Functions::class, 'TYPE'],
2061 'argumentCount' => '1',
2062 ],
2063 'UNICHAR' => [
2064 'category' => Category::CATEGORY_TEXT_AND_DATA,
2065 'functionCall' => [TextData::class, 'CHARACTER'],
2066 'argumentCount' => '1',
2067 ],
2068 'UNICODE' => [
2069 'category' => Category::CATEGORY_TEXT_AND_DATA,
2070 'functionCall' => [TextData::class, 'ASCIICODE'],
2071 'argumentCount' => '1',
2072 ],
2073 'UPPER' => [
2074 'category' => Category::CATEGORY_TEXT_AND_DATA,
2075 'functionCall' => [TextData::class, 'UPPERCASE'],
2076 'argumentCount' => '1',
2077 ],
2078 'USDOLLAR' => [
2079 'category' => Category::CATEGORY_FINANCIAL,
2080 'functionCall' => [Functions::class, 'DUMMY'],
2081 'argumentCount' => '2',
2082 ],
2083 'VALUE' => [
2084 'category' => Category::CATEGORY_TEXT_AND_DATA,
2085 'functionCall' => [TextData::class, 'VALUE'],
2086 'argumentCount' => '1',
2087 ],
2088 'VAR' => [
2089 'category' => Category::CATEGORY_STATISTICAL,
2090 'functionCall' => [Statistical::class, 'VARFunc'],
2091 'argumentCount' => '1+',
2092 ],
2093 'VAR.P' => [
2094 'category' => Category::CATEGORY_STATISTICAL,
2095 'functionCall' => [Statistical::class, 'VARP'],
2096 'argumentCount' => '1+',
2097 ],
2098 'VAR.S' => [
2099 'category' => Category::CATEGORY_STATISTICAL,
2100 'functionCall' => [Statistical::class, 'VARFunc'],
2101 'argumentCount' => '1+',
2102 ],
2103 'VARA' => [
2104 'category' => Category::CATEGORY_STATISTICAL,
2105 'functionCall' => [Statistical::class, 'VARA'],
2106 'argumentCount' => '1+',
2107 ],
2108 'VARP' => [
2109 'category' => Category::CATEGORY_STATISTICAL,
2110 'functionCall' => [Statistical::class, 'VARP'],
2111 'argumentCount' => '1+',
2112 ],
2113 'VARPA' => [
2114 'category' => Category::CATEGORY_STATISTICAL,
2115 'functionCall' => [Statistical::class, 'VARPA'],
2116 'argumentCount' => '1+',
2117 ],
2118 'VDB' => [
2119 'category' => Category::CATEGORY_FINANCIAL,
2120 'functionCall' => [Functions::class, 'DUMMY'],
2121 'argumentCount' => '5-7',
2122 ],
2123 'VLOOKUP' => [
2124 'category' => Category::CATEGORY_LOOKUP_AND_REFERENCE,
2125 'functionCall' => [LookupRef::class, 'VLOOKUP'],
2126 'argumentCount' => '3,4',
2127 ],
2128 'WEEKDAY' => [
2129 'category' => Category::CATEGORY_DATE_AND_TIME,
2130 'functionCall' => [DateTime::class, 'WEEKDAY'],
2131 'argumentCount' => '1,2',
2132 ],
2133 'WEEKNUM' => [
2134 'category' => Category::CATEGORY_DATE_AND_TIME,
2135 'functionCall' => [DateTime::class, 'WEEKNUM'],
2136 'argumentCount' => '1,2',
2137 ],
2138 'WEIBULL' => [
2139 'category' => Category::CATEGORY_STATISTICAL,
2140 'functionCall' => [Statistical::class, 'WEIBULL'],
2141 'argumentCount' => '4',
2142 ],
2143 'WORKDAY' => [
2144 'category' => Category::CATEGORY_DATE_AND_TIME,
2145 'functionCall' => [DateTime::class, 'WORKDAY'],
2146 'argumentCount' => '2+',
2147 ],
2148 'XIRR' => [
2149 'category' => Category::CATEGORY_FINANCIAL,
2150 'functionCall' => [Financial::class, 'XIRR'],
2151 'argumentCount' => '2,3',
2152 ],
2153 'XNPV' => [
2154 'category' => Category::CATEGORY_FINANCIAL,
2155 'functionCall' => [Financial::class, 'XNPV'],
2156 'argumentCount' => '3',
2157 ],
2158 'XOR' => [
2159 'category' => Category::CATEGORY_LOGICAL,
2160 'functionCall' => [Logical::class, 'logicalXor'],
2161 'argumentCount' => '1+',
2162 ],
2163 'YEAR' => [
2164 'category' => Category::CATEGORY_DATE_AND_TIME,
2165 'functionCall' => [DateTime::class, 'YEAR'],
2166 'argumentCount' => '1',
2167 ],
2168 'YEARFRAC' => [
2169 'category' => Category::CATEGORY_DATE_AND_TIME,
2170 'functionCall' => [DateTime::class, 'YEARFRAC'],
2171 'argumentCount' => '2,3',
2172 ],
2173 'YIELD' => [
2174 'category' => Category::CATEGORY_FINANCIAL,
2175 'functionCall' => [Functions::class, 'DUMMY'],
2176 'argumentCount' => '6,7',
2177 ],
2178 'YIELDDISC' => [
2179 'category' => Category::CATEGORY_FINANCIAL,
2180 'functionCall' => [Financial::class, 'YIELDDISC'],
2181 'argumentCount' => '4,5',
2182 ],
2183 'YIELDMAT' => [
2184 'category' => Category::CATEGORY_FINANCIAL,
2185 'functionCall' => [Financial::class, 'YIELDMAT'],
2186 'argumentCount' => '5,6',
2187 ],
2188 'ZTEST' => [
2189 'category' => Category::CATEGORY_STATISTICAL,
2190 'functionCall' => [Statistical::class, 'ZTEST'],
2191 'argumentCount' => '2-3',
2192 ],
2193 ];
2194
2195 // Internal functions used for special control purposes
2196 private static $controlFunctions = [
2197 'MKMATRIX' => [
2198 'argumentCount' => '*',
2199 'functionCall' => 'self::mkMatrix',
2200 ],
2201 ];
2202
2203 public function __construct(Spreadsheet $spreadsheet = null)
2204 {
2205 $this->delta = 1 * pow(10, 0 - ini_get('precision'));
2206
2207 $this->spreadsheet = $spreadsheet;
2208 $this->cyclicReferenceStack = new CyclicReferenceStack();
2209 $this->debugLog = new Logger($this->cyclicReferenceStack);
2210 }
2211
2212 private static function loadLocales()
2213 {
2214 $localeFileDirectory = __DIR__ . '/locale/';
2215 foreach (glob($localeFileDirectory . '*', GLOB_ONLYDIR) as $filename) {
2216 $filename = substr($filename, strlen($localeFileDirectory));
2217 if ($filename != 'en') {
2218 self::$validLocaleLanguages[] = $filename;
2219 }
2220 }
2221 }
2222
2223 /**
2224 * Get an instance of this class.
2225 *
2226 * @param Spreadsheet $spreadsheet Injected spreadsheet for working with a PhpSpreadsheet Spreadsheet object,
2227 * or NULL to create a standalone claculation engine
2228 *
2229 * @return Calculation
2230 */
2231 public static function getInstance(Spreadsheet $spreadsheet = null)
2232 {
2233 if ($spreadsheet !== null) {
2234 $instance = $spreadsheet->getCalculationEngine();
2235 if (isset($instance)) {
2236 return $instance;
2237 }
2238 }
2239
2240 if (!isset(self::$instance) || (self::$instance === null)) {
2241 self::$instance = new self();
2242 }
2243
2244 return self::$instance;
2245 }
2246
2247 /**
2248 * Flush the calculation cache for any existing instance of this class
2249 * but only if a Calculation instance exists.
2250 */
2251 public function flushInstance()
2252 {
2253 $this->clearCalculationCache();
2254 }
2255
2256 /**
2257 * Get the Logger for this calculation engine instance.
2258 *
2259 * @return Logger
2260 */
2261 public function getDebugLog()
2262 {
2263 return $this->debugLog;
2264 }
2265
2266 /**
2267 * __clone implementation. Cloning should not be allowed in a Singleton!
2268 *
2269 * @throws Exception
2270 */
2271 final public function __clone()
2272 {
2273 throw new Exception('Cloning the calculation engine is not allowed!');
2274 }
2275
2276 /**
2277 * Return the locale-specific translation of TRUE.
2278 *
2279 * @return string locale-specific translation of TRUE
2280 */
2281 public static function getTRUE()
2282 {
2283 return self::$localeBoolean['TRUE'];
2284 }
2285
2286 /**
2287 * Return the locale-specific translation of FALSE.
2288 *
2289 * @return string locale-specific translation of FALSE
2290 */
2291 public static function getFALSE()
2292 {
2293 return self::$localeBoolean['FALSE'];
2294 }
2295
2296 /**
2297 * Set the Array Return Type (Array or Value of first element in the array).
2298 *
2299 * @param string $returnType Array return type
2300 *
2301 * @return bool Success or failure
2302 */
2303 public static function setArrayReturnType($returnType)
2304 {
2305 if (($returnType == self::RETURN_ARRAY_AS_VALUE) ||
2306 ($returnType == self::RETURN_ARRAY_AS_ERROR) ||
2307 ($returnType == self::RETURN_ARRAY_AS_ARRAY)) {
2308 self::$returnArrayAsType = $returnType;
2309
2310 return true;
2311 }
2312
2313 return false;
2314 }
2315
2316 /**
2317 * Return the Array Return Type (Array or Value of first element in the array).
2318 *
2319 * @return string $returnType Array return type
2320 */
2321 public static function getArrayReturnType()
2322 {
2323 return self::$returnArrayAsType;
2324 }
2325
2326 /**
2327 * Is calculation caching enabled?
2328 *
2329 * @return bool
2330 */
2331 public function getCalculationCacheEnabled()
2332 {
2333 return $this->calculationCacheEnabled;
2334 }
2335
2336 /**
2337 * Enable/disable calculation cache.
2338 *
2339 * @param bool $pValue
2340 */
2341 public function setCalculationCacheEnabled($pValue)
2342 {
2343 $this->calculationCacheEnabled = $pValue;
2344 $this->clearCalculationCache();
2345 }
2346
2347 /**
2348 * Enable calculation cache.
2349 */
2350 public function enableCalculationCache()
2351 {
2352 $this->setCalculationCacheEnabled(true);
2353 }
2354
2355 /**
2356 * Disable calculation cache.
2357 */
2358 public function disableCalculationCache()
2359 {
2360 $this->setCalculationCacheEnabled(false);
2361 }
2362
2363 /**
2364 * Clear calculation cache.
2365 */
2366 public function clearCalculationCache()
2367 {
2368 $this->calculationCache = [];
2369 }
2370
2371 /**
2372 * Clear calculation cache for a specified worksheet.
2373 *
2374 * @param string $worksheetName
2375 */
2376 public function clearCalculationCacheForWorksheet($worksheetName)
2377 {
2378 if (isset($this->calculationCache[$worksheetName])) {
2379 unset($this->calculationCache[$worksheetName]);
2380 }
2381 }
2382
2383 /**
2384 * Rename calculation cache for a specified worksheet.
2385 *
2386 * @param string $fromWorksheetName
2387 * @param string $toWorksheetName
2388 */
2389 public function renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName)
2390 {
2391 if (isset($this->calculationCache[$fromWorksheetName])) {
2392 $this->calculationCache[$toWorksheetName] = &$this->calculationCache[$fromWorksheetName];
2393 unset($this->calculationCache[$fromWorksheetName]);
2394 }
2395 }
2396
2397 /**
2398 * Get the currently defined locale code.
2399 *
2400 * @return string
2401 */
2402 public function getLocale()
2403 {
2404 return self::$localeLanguage;
2405 }
2406
2407 /**
2408 * Set the locale code.
2409 *
2410 * @param string $locale The locale to use for formula translation, eg: 'en_us'
2411 *
2412 * @return bool
2413 */
2414 public function setLocale($locale)
2415 {
2416 // Identify our locale and language
2417 $language = $locale = strtolower($locale);
2418 if (strpos($locale, '_') !== false) {
2419 list($language) = explode('_', $locale);
2420 }
2421 if (count(self::$validLocaleLanguages) == 1) {
2422 self::loadLocales();
2423 }
2424 // Test whether we have any language data for this language (any locale)
2425 if (in_array($language, self::$validLocaleLanguages)) {
2426 // initialise language/locale settings
2427 self::$localeFunctions = [];
2428 self::$localeArgumentSeparator = ',';
2429 self::$localeBoolean = ['TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL'];
2430 // Default is English, if user isn't requesting english, then read the necessary data from the locale files
2431 if ($locale != 'en_us') {
2432 // Search for a file with a list of function names for locale
2433 $functionNamesFile = __DIR__ . '/locale/' . str_replace('_', DIRECTORY_SEPARATOR, $locale) . DIRECTORY_SEPARATOR . 'functions';
2434 if (!file_exists($functionNamesFile)) {
2435 // If there isn't a locale specific function file, look for a language specific function file
2436 $functionNamesFile = __DIR__ . '/locale/' . $language . DIRECTORY_SEPARATOR . 'functions';
2437 if (!file_exists($functionNamesFile)) {
2438 return false;
2439 }
2440 }
2441 // Retrieve the list of locale or language specific function names
2442 $localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
2443 foreach ($localeFunctions as $localeFunction) {
2444 list($localeFunction) = explode('##', $localeFunction); // Strip out comments
2445 if (strpos($localeFunction, '=') !== false) {
2446 list($fName, $lfName) = explode('=', $localeFunction);
2447 $fName = trim($fName);
2448 $lfName = trim($lfName);
2449 if ((isset(self::$phpSpreadsheetFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {
2450 self::$localeFunctions[$fName] = $lfName;
2451 }
2452 }
2453 }
2454 // Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
2455 if (isset(self::$localeFunctions['TRUE'])) {
2456 self::$localeBoolean['TRUE'] = self::$localeFunctions['TRUE'];
2457 }
2458 if (isset(self::$localeFunctions['FALSE'])) {
2459 self::$localeBoolean['FALSE'] = self::$localeFunctions['FALSE'];
2460 }
2461
2462 $configFile = __DIR__ . '/locale/' . str_replace('_', DIRECTORY_SEPARATOR, $locale) . DIRECTORY_SEPARATOR . 'config';
2463 if (!file_exists($configFile)) {
2464 $configFile = __DIR__ . '/locale/' . $language . DIRECTORY_SEPARATOR . 'config';
2465 }
2466 if (file_exists($configFile)) {
2467 $localeSettings = file($configFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
2468 foreach ($localeSettings as $localeSetting) {
2469 list($localeSetting) = explode('##', $localeSetting); // Strip out comments
2470 if (strpos($localeSetting, '=') !== false) {
2471 list($settingName, $settingValue) = explode('=', $localeSetting);
2472 $settingName = strtoupper(trim($settingName));
2473 switch ($settingName) {
2474 case 'ARGUMENTSEPARATOR':
2475 self::$localeArgumentSeparator = trim($settingValue);
2476
2477 break;
2478 }
2479 }
2480 }
2481 }
2482 }
2483
2484 self::$functionReplaceFromExcel = self::$functionReplaceToExcel =
2485 self::$functionReplaceFromLocale = self::$functionReplaceToLocale = null;
2486 self::$localeLanguage = $locale;
2487
2488 return true;
2489 }
2490
2491 return false;
2492 }
2493
2494 /**
2495 * @param string $fromSeparator
2496 * @param string $toSeparator
2497 * @param string $formula
2498 * @param bool $inBraces
2499 *
2500 * @return string
2501 */
2502 public static function translateSeparator($fromSeparator, $toSeparator, $formula, &$inBraces)
2503 {
2504 $strlen = mb_strlen($formula);
2505 for ($i = 0; $i < $strlen; ++$i) {
2506 $chr = mb_substr($formula, $i, 1);
2507 switch ($chr) {
2508 case '{':
2509 $inBraces = true;
2510
2511 break;
2512 case '}':
2513 $inBraces = false;
2514
2515 break;
2516 case $fromSeparator:
2517 if (!$inBraces) {
2518 $formula = mb_substr($formula, 0, $i) . $toSeparator . mb_substr($formula, $i + 1);
2519 }
2520 }
2521 }
2522
2523 return $formula;
2524 }
2525
2526 /**
2527 * @param string[] $from
2528 * @param string[] $to
2529 * @param string $formula
2530 * @param string $fromSeparator
2531 * @param string $toSeparator
2532 *
2533 * @return string
2534 */
2535 private static function translateFormula(array $from, array $to, $formula, $fromSeparator, $toSeparator)
2536 {
2537 // Convert any Excel function names to the required language
2538 if (self::$localeLanguage !== 'en_us') {
2539 $inBraces = false;
2540 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
2541 if (strpos($formula, '"') !== false) {
2542 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
2543 // the formula
2544 $temp = explode('"', $formula);
2545 $i = false;
2546 foreach ($temp as &$value) {
2547 // Only count/replace in alternating array entries
2548 if ($i = !$i) {
2549 $value = preg_replace($from, $to, $value);
2550 $value = self::translateSeparator($fromSeparator, $toSeparator, $value, $inBraces);
2551 }
2552 }
2553 unset($value);
2554 // Then rebuild the formula string
2555 $formula = implode('"', $temp);
2556 } else {
2557 // If there's no quoted strings, then we do a simple count/replace
2558 $formula = preg_replace($from, $to, $formula);
2559 $formula = self::translateSeparator($fromSeparator, $toSeparator, $formula, $inBraces);
2560 }
2561 }
2562
2563 return $formula;
2564 }
2565
2566 private static $functionReplaceFromExcel = null;
2567
2568 private static $functionReplaceToLocale = null;
2569
2570 public function _translateFormulaToLocale($formula)
2571 {
2572 if (self::$functionReplaceFromExcel === null) {
2573 self::$functionReplaceFromExcel = [];
2574 foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
2575 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelFunctionName, '/') . '([\s]*\()/Ui';
2576 }
2577 foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
2578 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/Ui';
2579 }
2580 }
2581
2582 if (self::$functionReplaceToLocale === null) {
2583 self::$functionReplaceToLocale = [];
2584 foreach (self::$localeFunctions as $localeFunctionName) {
2585 self::$functionReplaceToLocale[] = '$1' . trim($localeFunctionName) . '$2';
2586 }
2587 foreach (self::$localeBoolean as $localeBoolean) {
2588 self::$functionReplaceToLocale[] = '$1' . trim($localeBoolean) . '$2';
2589 }
2590 }
2591
2592 return self::translateFormula(self::$functionReplaceFromExcel, self::$functionReplaceToLocale, $formula, ',', self::$localeArgumentSeparator);
2593 }
2594
2595 private static $functionReplaceFromLocale = null;
2596
2597 private static $functionReplaceToExcel = null;
2598
2599 public function _translateFormulaToEnglish($formula)
2600 {
2601 if (self::$functionReplaceFromLocale === null) {
2602 self::$functionReplaceFromLocale = [];
2603 foreach (self::$localeFunctions as $localeFunctionName) {
2604 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($localeFunctionName, '/') . '([\s]*\()/Ui';
2605 }
2606 foreach (self::$localeBoolean as $excelBoolean) {
2607 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/Ui';
2608 }
2609 }
2610
2611 if (self::$functionReplaceToExcel === null) {
2612 self::$functionReplaceToExcel = [];
2613 foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
2614 self::$functionReplaceToExcel[] = '$1' . trim($excelFunctionName) . '$2';
2615 }
2616 foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
2617 self::$functionReplaceToExcel[] = '$1' . trim($excelBoolean) . '$2';
2618 }
2619 }
2620
2621 return self::translateFormula(self::$functionReplaceFromLocale, self::$functionReplaceToExcel, $formula, self::$localeArgumentSeparator, ',');
2622 }
2623
2624 public static function localeFunc($function)
2625 {
2626 if (self::$localeLanguage !== 'en_us') {
2627 $functionName = trim($function, '(');
2628 if (isset(self::$localeFunctions[$functionName])) {
2629 $brace = ($functionName != $function);
2630 $function = self::$localeFunctions[$functionName];
2631 if ($brace) {
2632 $function .= '(';
2633 }
2634 }
2635 }
2636
2637 return $function;
2638 }
2639
2640 /**
2641 * Wrap string values in quotes.
2642 *
2643 * @param mixed $value
2644 *
2645 * @return mixed
2646 */
2647 public static function wrapResult($value)
2648 {
2649 if (is_string($value)) {
2650 // Error values cannot be "wrapped"
2651 if (preg_match('/^' . self::CALCULATION_REGEXP_ERROR . '$/i', $value, $match)) {
2652 // Return Excel errors "as is"
2653 return $value;
2654 }
2655 // Return strings wrapped in quotes
2656 return '"' . $value . '"';
2657 // Convert numeric errors to NaN error
2658 } elseif ((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
2659 return Functions::NAN();
2660 }
2661
2662 return $value;
2663 }
2664
2665 /**
2666 * Remove quotes used as a wrapper to identify string values.
2667 *
2668 * @param mixed $value
2669 *
2670 * @return mixed
2671 */
2672 public static function unwrapResult($value)
2673 {
2674 if (is_string($value)) {
2675 if ((isset($value[0])) && ($value[0] == '"') && (substr($value, -1) == '"')) {
2676 return substr($value, 1, -1);
2677 }
2678 // Convert numeric errors to NAN error
2679 } elseif ((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
2680 return Functions::NAN();
2681 }
2682
2683 return $value;
2684 }
2685
2686 /**
2687 * Calculate cell value (using formula from a cell ID)
2688 * Retained for backward compatibility.
2689 *
2690 * @param Cell $pCell Cell to calculate
2691 *
2692 * @throws Exception
2693 *
2694 * @return mixed
2695 */
2696 public function calculate(Cell $pCell = null)
2697 {
2698 try {
2699 return $this->calculateCellValue($pCell);
2700 } catch (\Exception $e) {
2701 throw new Exception($e->getMessage());
2702 }
2703 }
2704
2705 /**
2706 * Calculate the value of a cell formula.
2707 *
2708 * @param Cell $pCell Cell to calculate
2709 * @param bool $resetLog Flag indicating whether the debug log should be reset or not
2710 *
2711 * @throws \PhpOffice\PhpSpreadsheet\Exception
2712 *
2713 * @return mixed
2714 */
2715 public function calculateCellValue(Cell $pCell = null, $resetLog = true)
2716 {
2717 if ($pCell === null) {
2718 return null;
2719 }
2720
2721 $returnArrayAsType = self::$returnArrayAsType;
2722 if ($resetLog) {
2723 // Initialise the logging settings if requested
2724 $this->formulaError = null;
2725 $this->debugLog->clearLog();
2726 $this->cyclicReferenceStack->clear();
2727 $this->cyclicFormulaCounter = 1;
2728
2729 self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
2730 }
2731
2732 // Execute the calculation for the cell formula
2733 $this->cellStack[] = [
2734 'sheet' => $pCell->getWorksheet()->getTitle(),
2735 'cell' => $pCell->getCoordinate(),
2736 ];
2737
2738 try {
2739 $result = self::unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
2740 $cellAddress = array_pop($this->cellStack);
2741 $this->spreadsheet->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
2742 } catch (\Exception $e) {
2743 $cellAddress = array_pop($this->cellStack);
2744 $this->spreadsheet->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
2745
2746 throw new Exception($e->getMessage());
2747 }
2748
2749 if ((is_array($result)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
2750 self::$returnArrayAsType = $returnArrayAsType;
2751 $testResult = Functions::flattenArray($result);
2752 if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
2753 return Functions::VALUE();
2754 }
2755 // If there's only a single cell in the array, then we allow it
2756 if (count($testResult) != 1) {
2757 // If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
2758 $r = array_keys($result);
2759 $r = array_shift($r);
2760 if (!is_numeric($r)) {
2761 return Functions::VALUE();
2762 }
2763 if (is_array($result[$r])) {
2764 $c = array_keys($result[$r]);
2765 $c = array_shift($c);
2766 if (!is_numeric($c)) {
2767 return Functions::VALUE();
2768 }
2769 }
2770 }
2771 $result = array_shift($testResult);
2772 }
2773 self::$returnArrayAsType = $returnArrayAsType;
2774
2775 if ($result === null) {
2776 return 0;
2777 } elseif ((is_float($result)) && ((is_nan($result)) || (is_infinite($result)))) {
2778 return Functions::NAN();
2779 }
2780
2781 return $result;
2782 }
2783
2784 /**
2785 * Validate and parse a formula string.
2786 *
2787 * @param string $formula Formula to parse
2788 *
2789 * @return array|bool
2790 */
2791 public function parseFormula($formula)
2792 {
2793 // Basic validation that this is indeed a formula
2794 // We return an empty array if not
2795 $formula = trim($formula);
2796 if ((!isset($formula[0])) || ($formula[0] != '=')) {
2797 return [];
2798 }
2799 $formula = ltrim(substr($formula, 1));
2800 if (!isset($formula[0])) {
2801 return [];
2802 }
2803
2804 // Parse the formula and return the token stack
2805 return $this->_parseFormula($formula);
2806 }
2807
2808 /**
2809 * Calculate the value of a formula.
2810 *
2811 * @param string $formula Formula to parse
2812 * @param string $cellID Address of the cell to calculate
2813 * @param Cell $pCell Cell to calculate
2814 *
2815 * @throws \PhpOffice\PhpSpreadsheet\Exception
2816 *
2817 * @return mixed
2818 */
2819 public function calculateFormula($formula, $cellID = null, Cell $pCell = null)
2820 {
2821 // Initialise the logging settings
2822 $this->formulaError = null;
2823 $this->debugLog->clearLog();
2824 $this->cyclicReferenceStack->clear();
2825
2826 if ($this->spreadsheet !== null && $cellID === null && $pCell === null) {
2827 $cellID = 'A1';
2828 $pCell = $this->spreadsheet->getActiveSheet()->getCell($cellID);
2829 } else {
2830 // Disable calculation cacheing because it only applies to cell calculations, not straight formulae
2831 // But don't actually flush any cache
2832 $resetCache = $this->getCalculationCacheEnabled();
2833 $this->calculationCacheEnabled = false;
2834 }
2835
2836 // Execute the calculation
2837 try {
2838 $result = self::unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
2839 } catch (\Exception $e) {
2840 throw new Exception($e->getMessage());
2841 }
2842
2843 if ($this->spreadsheet === null) {
2844 // Reset calculation cacheing to its previous state
2845 $this->calculationCacheEnabled = $resetCache;
2846 }
2847
2848 return $result;
2849 }
2850
2851 /**
2852 * @param string $cellReference
2853 * @param mixed $cellValue
2854 *
2855 * @return bool
2856 */
2857 public function getValueFromCache($cellReference, &$cellValue)
2858 {
2859 // Is calculation cacheing enabled?
2860 // Is the value present in calculation cache?
2861 $this->debugLog->writeDebugLog('Testing cache value for cell ', $cellReference);
2862 if (($this->calculationCacheEnabled) && (isset($this->calculationCache[$cellReference]))) {
2863 $this->debugLog->writeDebugLog('Retrieving value for cell ', $cellReference, ' from cache');
2864 // Return the cached result
2865 $cellValue = $this->calculationCache[$cellReference];
2866
2867 return true;
2868 }
2869
2870 return false;
2871 }
2872
2873 /**
2874 * @param string $cellReference
2875 * @param mixed $cellValue
2876 */
2877 public function saveValueToCache($cellReference, $cellValue)
2878 {
2879 if ($this->calculationCacheEnabled) {
2880 $this->calculationCache[$cellReference] = $cellValue;
2881 }
2882 }
2883
2884 /**
2885 * Parse a cell formula and calculate its value.
2886 *
2887 * @param string $formula The formula to parse and calculate
2888 * @param string $cellID The ID (e.g. A3) of the cell that we are calculating
2889 * @param Cell $pCell Cell to calculate
2890 *
2891 * @throws Exception
2892 *
2893 * @return mixed
2894 */
2895 public function _calculateFormulaValue($formula, $cellID = null, Cell $pCell = null)
2896 {
2897 $cellValue = null;
2898
2899 // Quote-Prefixed cell values cannot be formulae, but are treated as strings
2900 if ($pCell !== null && $pCell->getStyle()->getQuotePrefix() === true) {
2901 return self::wrapResult((string) $formula);
2902 }
2903
2904 if (preg_match('/^=\s*cmd\s*\|/miu', $formula) !== 0) {
2905 return self::wrapResult($formula);
2906 }
2907
2908 // Basic validation that this is indeed a formula
2909 // We simply return the cell value if not
2910 $formula = trim($formula);
2911 if ($formula[0] != '=') {
2912 return self::wrapResult($formula);
2913 }
2914 $formula = ltrim(substr($formula, 1));
2915 if (!isset($formula[0])) {
2916 return self::wrapResult($formula);
2917 }
2918
2919 $pCellParent = ($pCell !== null) ? $pCell->getWorksheet() : null;
2920 $wsTitle = ($pCellParent !== null) ? $pCellParent->getTitle() : "\x00Wrk";
2921 $wsCellReference = $wsTitle . '!' . $cellID;
2922
2923 if (($cellID !== null) && ($this->getValueFromCache($wsCellReference, $cellValue))) {
2924 return $cellValue;
2925 }
2926
2927 if (($wsTitle[0] !== "\x00") && ($this->cyclicReferenceStack->onStack($wsCellReference))) {
2928 if ($this->cyclicFormulaCount <= 0) {
2929 $this->cyclicFormulaCell = '';
2930
2931 return $this->raiseFormulaError('Cyclic Reference in Formula');
2932 } elseif ($this->cyclicFormulaCell === $wsCellReference) {
2933 ++$this->cyclicFormulaCounter;
2934 if ($this->cyclicFormulaCounter >= $this->cyclicFormulaCount) {
2935 $this->cyclicFormulaCell = '';
2936
2937 return $cellValue;
2938 }
2939 } elseif ($this->cyclicFormulaCell == '') {
2940 if ($this->cyclicFormulaCounter >= $this->cyclicFormulaCount) {
2941 return $cellValue;
2942 }
2943 $this->cyclicFormulaCell = $wsCellReference;
2944 }
2945 }
2946
2947 // Parse the formula onto the token stack and calculate the value
2948 $this->cyclicReferenceStack->push($wsCellReference);
2949 $cellValue = $this->processTokenStack($this->_parseFormula($formula, $pCell), $cellID, $pCell);
2950 $this->cyclicReferenceStack->pop();
2951
2952 // Save to calculation cache
2953 if ($cellID !== null) {
2954 $this->saveValueToCache($wsCellReference, $cellValue);
2955 }
2956
2957 // Return the calculated value
2958 return $cellValue;
2959 }
2960
2961 /**
2962 * Ensure that paired matrix operands are both matrices and of the same size.
2963 *
2964 * @param mixed &$operand1 First matrix operand
2965 * @param mixed &$operand2 Second matrix operand
2966 * @param int $resize Flag indicating whether the matrices should be resized to match
2967 * and (if so), whether the smaller dimension should grow or the
2968 * larger should shrink.
2969 * 0 = no resize
2970 * 1 = shrink to fit
2971 * 2 = extend to fit
2972 *
2973 * @return array
2974 */
2975 private static function checkMatrixOperands(&$operand1, &$operand2, $resize = 1)
2976 {
2977 // Examine each of the two operands, and turn them into an array if they aren't one already
2978 // Note that this function should only be called if one or both of the operand is already an array
2979 if (!is_array($operand1)) {
2980 list($matrixRows, $matrixColumns) = self::getMatrixDimensions($operand2);
2981 $operand1 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand1));
2982 $resize = 0;
2983 } elseif (!is_array($operand2)) {
2984 list($matrixRows, $matrixColumns) = self::getMatrixDimensions($operand1);
2985 $operand2 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand2));
2986 $resize = 0;
2987 }
2988
2989 list($matrix1Rows, $matrix1Columns) = self::getMatrixDimensions($operand1);
2990 list($matrix2Rows, $matrix2Columns) = self::getMatrixDimensions($operand2);
2991 if (($matrix1Rows == $matrix2Columns) && ($matrix2Rows == $matrix1Columns)) {
2992 $resize = 1;
2993 }
2994
2995 if ($resize == 2) {
2996 // Given two matrices of (potentially) unequal size, convert the smaller in each dimension to match the larger
2997 self::resizeMatricesExtend($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns);
2998 } elseif ($resize == 1) {
2999 // Given two matrices of (potentially) unequal size, convert the larger in each dimension to match the smaller
3000 self::resizeMatricesShrink($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns);
3001 }
3002
3003 return [$matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns];
3004 }
3005
3006 /**
3007 * Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0, column 0.
3008 *
3009 * @param array &$matrix matrix operand
3010 *
3011 * @return int[] An array comprising the number of rows, and number of columns
3012 */
3013 public static function getMatrixDimensions(array &$matrix)
3014 {
3015 $matrixRows = count($matrix);
3016 $matrixColumns = 0;
3017 foreach ($matrix as $rowKey => $rowValue) {
3018 if (!is_array($rowValue)) {
3019 $matrix[$rowKey] = [$rowValue];
3020 $matrixColumns = max(1, $matrixColumns);
3021 } else {
3022 $matrix[$rowKey] = array_values($rowValue);
3023 $matrixColumns = max(count($rowValue), $matrixColumns);
3024 }
3025 }
3026 $matrix = array_values($matrix);
3027
3028 return [$matrixRows, $matrixColumns];
3029 }
3030
3031 /**
3032 * Ensure that paired matrix operands are both matrices of the same size.
3033 *
3034 * @param mixed &$matrix1 First matrix operand
3035 * @param mixed &$matrix2 Second matrix operand
3036 * @param int $matrix1Rows Row size of first matrix operand
3037 * @param int $matrix1Columns Column size of first matrix operand
3038 * @param int $matrix2Rows Row size of second matrix operand
3039 * @param int $matrix2Columns Column size of second matrix operand
3040 */
3041 private static function resizeMatricesShrink(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
3042 {
3043 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
3044 if ($matrix2Rows < $matrix1Rows) {
3045 for ($i = $matrix2Rows; $i < $matrix1Rows; ++$i) {
3046 unset($matrix1[$i]);
3047 }
3048 }
3049 if ($matrix2Columns < $matrix1Columns) {
3050 for ($i = 0; $i < $matrix1Rows; ++$i) {
3051 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
3052 unset($matrix1[$i][$j]);
3053 }
3054 }
3055 }
3056 }
3057
3058 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
3059 if ($matrix1Rows < $matrix2Rows) {
3060 for ($i = $matrix1Rows; $i < $matrix2Rows; ++$i) {
3061 unset($matrix2[$i]);
3062 }
3063 }
3064 if ($matrix1Columns < $matrix2Columns) {
3065 for ($i = 0; $i < $matrix2Rows; ++$i) {
3066 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
3067 unset($matrix2[$i][$j]);
3068 }
3069 }
3070 }
3071 }
3072 }
3073
3074 /**
3075 * Ensure that paired matrix operands are both matrices of the same size.
3076 *
3077 * @param mixed &$matrix1 First matrix operand
3078 * @param mixed &$matrix2 Second matrix operand
3079 * @param int $matrix1Rows Row size of first matrix operand
3080 * @param int $matrix1Columns Column size of first matrix operand
3081 * @param int $matrix2Rows Row size of second matrix operand
3082 * @param int $matrix2Columns Column size of second matrix operand
3083 */
3084 private static function resizeMatricesExtend(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
3085 {
3086 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
3087 if ($matrix2Columns < $matrix1Columns) {
3088 for ($i = 0; $i < $matrix2Rows; ++$i) {
3089 $x = $matrix2[$i][$matrix2Columns - 1];
3090 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
3091 $matrix2[$i][$j] = $x;
3092 }
3093 }
3094 }
3095 if ($matrix2Rows < $matrix1Rows) {
3096 $x = $matrix2[$matrix2Rows - 1];
3097 for ($i = 0; $i < $matrix1Rows; ++$i) {
3098 $matrix2[$i] = $x;
3099 }
3100 }
3101 }
3102
3103 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
3104 if ($matrix1Columns < $matrix2Columns) {
3105 for ($i = 0; $i < $matrix1Rows; ++$i) {
3106 $x = $matrix1[$i][$matrix1Columns - 1];
3107 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
3108 $matrix1[$i][$j] = $x;
3109 }
3110 }
3111 }
3112 if ($matrix1Rows < $matrix2Rows) {
3113 $x = $matrix1[$matrix1Rows - 1];
3114 for ($i = 0; $i < $matrix2Rows; ++$i) {
3115 $matrix1[$i] = $x;
3116 }
3117 }
3118 }
3119 }
3120
3121 /**
3122 * Format details of an operand for display in the log (based on operand type).
3123 *
3124 * @param mixed $value First matrix operand
3125 *
3126 * @return mixed
3127 */
3128 private function showValue($value)
3129 {
3130 if ($this->debugLog->getWriteDebugLog()) {
3131 $testArray = Functions::flattenArray($value);
3132 if (count($testArray) == 1) {
3133 $value = array_pop($testArray);
3134 }
3135
3136 if (is_array($value)) {
3137 $returnMatrix = [];
3138 $pad = $rpad = ', ';
3139 foreach ($value as $row) {
3140 if (is_array($row)) {
3141 $returnMatrix[] = implode($pad, array_map([$this, 'showValue'], $row));
3142 $rpad = '; ';
3143 } else {
3144 $returnMatrix[] = $this->showValue($row);
3145 }
3146 }
3147
3148 return '{ ' . implode($rpad, $returnMatrix) . ' }';
3149 } elseif (is_string($value) && (trim($value, '"') == $value)) {
3150 return '"' . $value . '"';
3151 } elseif (is_bool($value)) {
3152 return ($value) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
3153 }
3154 }
3155
3156 return Functions::flattenSingleValue($value);
3157 }
3158
3159 /**
3160 * Format type and details of an operand for display in the log (based on operand type).
3161 *
3162 * @param mixed $value First matrix operand
3163 *
3164 * @return null|string
3165 */
3166 private function showTypeDetails($value)
3167 {
3168 if ($this->debugLog->getWriteDebugLog()) {
3169 $testArray = Functions::flattenArray($value);
3170 if (count($testArray) == 1) {
3171 $value = array_pop($testArray);
3172 }
3173
3174 if ($value === null) {
3175 return 'a NULL value';
3176 } elseif (is_float($value)) {
3177 $typeString = 'a floating point number';
3178 } elseif (is_int($value)) {
3179 $typeString = 'an integer number';
3180 } elseif (is_bool($value)) {
3181 $typeString = 'a boolean';
3182 } elseif (is_array($value)) {
3183 $typeString = 'a matrix';
3184 } else {
3185 if ($value == '') {
3186 return 'an empty string';
3187 } elseif ($value[0] == '#') {
3188 return 'a ' . $value . ' error';
3189 }
3190 $typeString = 'a string';
3191 }
3192
3193 return $typeString . ' with a value of ' . $this->showValue($value);
3194 }
3195 }
3196
3197 /**
3198 * @param string $formula
3199 *
3200 * @return string
3201 */
3202 private function convertMatrixReferences($formula)
3203 {
3204 static $matrixReplaceFrom = ['{', ';', '}'];
3205 static $matrixReplaceTo = ['MKMATRIX(MKMATRIX(', '),MKMATRIX(', '))'];
3206
3207 // Convert any Excel matrix references to the MKMATRIX() function
3208 if (strpos($formula, '{') !== false) {
3209 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
3210 if (strpos($formula, '"') !== false) {
3211 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
3212 // the formula
3213 $temp = explode('"', $formula);
3214 // Open and Closed counts used for trapping mismatched braces in the formula
3215 $openCount = $closeCount = 0;
3216 $i = false;
3217 foreach ($temp as &$value) {
3218 // Only count/replace in alternating array entries
3219 if ($i = !$i) {
3220 $openCount += substr_count($value, '{');
3221 $closeCount += substr_count($value, '}');
3222 $value = str_replace($matrixReplaceFrom, $matrixReplaceTo, $value);
3223 }
3224 }
3225 unset($value);
3226 // Then rebuild the formula string
3227 $formula = implode('"', $temp);
3228 } else {
3229 // If there's no quoted strings, then we do a simple count/replace
3230 $openCount = substr_count($formula, '{');
3231 $closeCount = substr_count($formula, '}');
3232 $formula = str_replace($matrixReplaceFrom, $matrixReplaceTo, $formula);
3233 }
3234 // Trap for mismatched braces and trigger an appropriate error
3235 if ($openCount < $closeCount) {
3236 if ($openCount > 0) {
3237 return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '}'");
3238 }
3239
3240 return $this->raiseFormulaError("Formula Error: Unexpected '}' encountered");
3241 } elseif ($openCount > $closeCount) {
3242 if ($closeCount > 0) {
3243 return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '{'");
3244 }
3245
3246 return $this->raiseFormulaError("Formula Error: Unexpected '{' encountered");
3247 }
3248 }
3249
3250 return $formula;
3251 }
3252
3253 private static function mkMatrix(...$args)
3254 {
3255 return $args;
3256 }
3257
3258 // Binary Operators
3259 // These operators always work on two values
3260 // Array key is the operator, the value indicates whether this is a left or right associative operator
3261 private static $operatorAssociativity = [
3262 '^' => 0, // Exponentiation
3263 '*' => 0, '/' => 0, // Multiplication and Division
3264 '+' => 0, '-' => 0, // Addition and Subtraction
3265 '&' => 0, // Concatenation
3266 '|' => 0, ':' => 0, // Intersect and Range
3267 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0, // Comparison
3268 ];
3269
3270 // Comparison (Boolean) Operators
3271 // These operators work on two values, but always return a boolean result
3272 private static $comparisonOperators = ['>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true];
3273
3274 // Operator Precedence
3275 // This list includes all valid operators, whether binary (including boolean) or unary (such as %)
3276 // Array key is the operator, the value is its precedence
3277 private static $operatorPrecedence = [
3278 ':' => 8, // Range
3279 '|' => 7, // Intersect
3280 '~' => 6, // Negation
3281 '%' => 5, // Percentage
3282 '^' => 4, // Exponentiation
3283 '*' => 3, '/' => 3, // Multiplication and Division
3284 '+' => 2, '-' => 2, // Addition and Subtraction
3285 '&' => 1, // Concatenation
3286 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0, // Comparison
3287 ];
3288
3289 // Convert infix to postfix notation
3290
3291 /**
3292 * @param string $formula
3293 * @param null|\PhpOffice\PhpSpreadsheet\Cell\Cell $pCell
3294 *
3295 * @return bool
3296 */
3297 private function _parseFormula($formula, Cell $pCell = null)
3298 {
3299 if (($formula = $this->convertMatrixReferences(trim($formula))) === false) {
3300 return false;
3301 }
3302
3303 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
3304 // so we store the parent worksheet so that we can re-attach it when necessary
3305 $pCellParent = ($pCell !== null) ? $pCell->getWorksheet() : null;
3306
3307 $regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION .
3308 '|' . self::CALCULATION_REGEXP_CELLREF .
3309 '|' . self::CALCULATION_REGEXP_NUMBER .
3310 '|' . self::CALCULATION_REGEXP_STRING .
3311 '|' . self::CALCULATION_REGEXP_OPENBRACE .
3312 '|' . self::CALCULATION_REGEXP_NAMEDRANGE .
3313 '|' . self::CALCULATION_REGEXP_ERROR .
3314 ')/si';
3315
3316 // Start with initialisation
3317 $index = 0;
3318 $stack = new Stack();
3319 $output = [];
3320 $expectingOperator = false; // We use this test in syntax-checking the expression to determine when a
3321 // - is a negation or + is a positive operator rather than an operation
3322 $expectingOperand = false; // We use this test in syntax-checking the expression to determine whether an operand
3323 // should be null in a function call
3324 // The guts of the lexical parser
3325 // Loop through the formula extracting each operator and operand in turn
3326 while (true) {
3327 $opCharacter = $formula[$index]; // Get the first character of the value at the current index position
3328 if ((isset(self::$comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset(self::$comparisonOperators[$formula[$index + 1]]))) {
3329 $opCharacter .= $formula[++$index];
3330 }
3331
3332 // Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
3333 $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);
3334
3335 if ($opCharacter == '-' && !$expectingOperator) { // Is it a negation instead of a minus?
3336 $stack->push('Unary Operator', '~'); // Put a negation on the stack
3337 ++$index; // and drop the negation symbol
3338 } elseif ($opCharacter == '%' && $expectingOperator) {
3339 $stack->push('Unary Operator', '%'); // Put a percentage on the stack
3340 ++$index;
3341 } elseif ($opCharacter == '+' && !$expectingOperator) { // Positive (unary plus rather than binary operator plus) can be discarded?
3342 ++$index; // Drop the redundant plus symbol
3343 } elseif ((($opCharacter == '~') || ($opCharacter == '|')) && (!$isOperandOrFunction)) { // We have to explicitly deny a tilde or pipe, because they are legal
3344 return $this->raiseFormulaError("Formula Error: Illegal character '~'"); // on the stack but not in the input expression
3345 } elseif ((isset(self::$operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) { // Are we putting an operator on the stack?
3346 while ($stack->count() > 0 &&
3347 ($o2 = $stack->last()) &&
3348 isset(self::$operators[$o2['value']]) &&
3349 @(self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2['value']] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2['value']])) {
3350 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
3351 }
3352 $stack->push('Binary Operator', $opCharacter); // Finally put our current operator onto the stack
3353 ++$index;
3354 $expectingOperator = false;
3355 } elseif ($opCharacter == ')' && $expectingOperator) { // Are we expecting to close a parenthesis?
3356 $expectingOperand = false;
3357 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
3358 if ($o2 === null) {
3359 return $this->raiseFormulaError('Formula Error: Unexpected closing brace ")"');
3360 }
3361 $output[] = $o2;
3362 }
3363 $d = $stack->last(2);
3364 if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) { // Did this parenthesis just close a function?
3365 $functionName = $matches[1]; // Get the function name
3366 $d = $stack->pop();
3367 $argumentCount = $d['value']; // See how many arguments there were (argument count is the next value stored on the stack)
3368 $output[] = $d; // Dump the argument count on the output
3369 $output[] = $stack->pop(); // Pop the function and push onto the output
3370 if (isset(self::$controlFunctions[$functionName])) {
3371 $expectedArgumentCount = self::$controlFunctions[$functionName]['argumentCount'];
3372 $functionCall = self::$controlFunctions[$functionName]['functionCall'];
3373 } elseif (isset(self::$phpSpreadsheetFunctions[$functionName])) {
3374 $expectedArgumentCount = self::$phpSpreadsheetFunctions[$functionName]['argumentCount'];
3375 $functionCall = self::$phpSpreadsheetFunctions[$functionName]['functionCall'];
3376 } else { // did we somehow push a non-function on the stack? this should never happen
3377 return $this->raiseFormulaError('Formula Error: Internal error, non-function on stack');
3378 }
3379 // Check the argument count
3380 $argumentCountError = false;
3381 if (is_numeric($expectedArgumentCount)) {
3382 if ($expectedArgumentCount < 0) {
3383 if ($argumentCount > abs($expectedArgumentCount)) {
3384 $argumentCountError = true;
3385 $expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount);
3386 }
3387 } else {
3388 if ($argumentCount != $expectedArgumentCount) {
3389 $argumentCountError = true;
3390 $expectedArgumentCountString = $expectedArgumentCount;
3391 }
3392 }
3393 } elseif ($expectedArgumentCount != '*') {
3394 $isOperandOrFunction = preg_match('/(\d*)([-+,])(\d*)/', $expectedArgumentCount, $argMatch);
3395 switch ($argMatch[2]) {
3396 case '+':
3397 if ($argumentCount < $argMatch[1]) {
3398 $argumentCountError = true;
3399 $expectedArgumentCountString = $argMatch[1] . ' or more ';
3400 }
3401
3402 break;
3403 case '-':
3404 if (($argumentCount < $argMatch[1]) || ($argumentCount > $argMatch[3])) {
3405 $argumentCountError = true;
3406 $expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3];
3407 }
3408
3409 break;
3410 case ',':
3411 if (($argumentCount != $argMatch[1]) && ($argumentCount != $argMatch[3])) {
3412 $argumentCountError = true;
3413 $expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3];
3414 }
3415
3416 break;
3417 }
3418 }
3419 if ($argumentCountError) {
3420 return $this->raiseFormulaError("Formula Error: Wrong number of arguments for $functionName() function: $argumentCount given, " . $expectedArgumentCountString . ' expected');
3421 }
3422 }
3423 ++$index;
3424 } elseif ($opCharacter == ',') { // Is this the separator for function arguments?
3425 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
3426 if ($o2 === null) {
3427 return $this->raiseFormulaError('Formula Error: Unexpected ,');
3428 }
3429 $output[] = $o2; // pop the argument expression stuff and push onto the output
3430 }
3431 // If we've a comma when we're expecting an operand, then what we actually have is a null operand;
3432 // so push a null onto the stack
3433 if (($expectingOperand) || (!$expectingOperator)) {
3434 $output[] = ['type' => 'NULL Value', 'value' => self::$excelConstants['NULL'], 'reference' => null];
3435 }
3436 // make sure there was a function
3437 $d = $stack->last(2);
3438 if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
3439 return $this->raiseFormulaError('Formula Error: Unexpected ,');
3440 }
3441 $d = $stack->pop();
3442 $stack->push($d['type'], ++$d['value'], $d['reference']); // increment the argument count
3443 $stack->push('Brace', '('); // put the ( back on, we'll need to pop back to it again
3444 $expectingOperator = false;
3445 $expectingOperand = true;
3446 ++$index;
3447 } elseif ($opCharacter == '(' && !$expectingOperator) {
3448 $stack->push('Brace', '(');
3449 ++$index;
3450 } elseif ($isOperandOrFunction && !$expectingOperator) { // do we now have a function/variable/number?
3451 $expectingOperator = true;
3452 $expectingOperand = false;
3453 $val = $match[1];
3454 $length = strlen($val);
3455
3456 if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $val, $matches)) {
3457 $val = preg_replace('/\s/u', '', $val);
3458 if (isset(self::$phpSpreadsheetFunctions[strtoupper($matches[1])]) || isset(self::$controlFunctions[strtoupper($matches[1])])) { // it's a function
3459 $stack->push('Function', strtoupper($val));
3460 $ax = preg_match('/^\s*(\s*\))/ui', substr($formula, $index + $length), $amatch);
3461 if ($ax) {
3462 $stack->push('Operand Count for Function ' . strtoupper($val) . ')', 0);
3463 $expectingOperator = true;
3464 } else {
3465 $stack->push('Operand Count for Function ' . strtoupper($val) . ')', 1);
3466 $expectingOperator = false;
3467 }
3468 $stack->push('Brace', '(');
3469 } else { // it's a var w/ implicit multiplication
3470 $output[] = ['type' => 'Value', 'value' => $matches[1], 'reference' => null];
3471 }
3472 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) {
3473 // Watch for this case-change when modifying to allow cell references in different worksheets...
3474 // Should only be applied to the actual cell column, not the worksheet name
3475
3476 // If the last entry on the stack was a : operator, then we have a cell range reference
3477 $testPrevOp = $stack->last(1);
3478 if ($testPrevOp['value'] == ':') {
3479 // If we have a worksheet reference, then we're playing with a 3D reference
3480 if ($matches[2] == '') {
3481 // Otherwise, we 'inherit' the worksheet reference from the start cell reference
3482 // The start of the cell range reference should be the last entry in $output
3483 $startCellRef = $output[count($output) - 1]['value'];
3484 preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $startCellRef, $startMatches);
3485 if ($startMatches[2] > '') {
3486 $val = $startMatches[2] . '!' . $val;
3487 }
3488 } else {
3489 return $this->raiseFormulaError('3D Range references are not yet supported');
3490 }
3491 }
3492
3493 $output[] = ['type' => 'Cell Reference', 'value' => $val, 'reference' => $val];
3494 } else { // it's a variable, constant, string, number or boolean
3495 // If the last entry on the stack was a : operator, then we may have a row or column range reference
3496 $testPrevOp = $stack->last(1);
3497 if ($testPrevOp['value'] == ':') {
3498 $startRowColRef = $output[count($output) - 1]['value'];
3499 list($rangeWS1, $startRowColRef) = Worksheet::extractSheetTitle($startRowColRef, true);
3500 if ($rangeWS1 != '') {
3501 $rangeWS1 .= '!';
3502 }
3503 list($rangeWS2, $val) = Worksheet::extractSheetTitle($val, true);
3504 if ($rangeWS2 != '') {
3505 $rangeWS2 .= '!';
3506 } else {
3507 $rangeWS2 = $rangeWS1;
3508 }
3509 if ((is_int($startRowColRef)) && (ctype_digit($val)) &&
3510 ($startRowColRef <= 1048576) && ($val <= 1048576)) {
3511 // Row range
3512 $endRowColRef = ($pCellParent !== null) ? $pCellParent->getHighestColumn() : 'XFD'; // Max 16,384 columns for Excel2007
3513 $output[count($output) - 1]['value'] = $rangeWS1 . 'A' . $startRowColRef;
3514 $val = $rangeWS2 . $endRowColRef . $val;
3515 } elseif ((ctype_alpha($startRowColRef)) && (ctype_alpha($val)) &&
3516 (strlen($startRowColRef) <= 3) && (strlen($val) <= 3)) {
3517 // Column range
3518 $endRowColRef = ($pCellParent !== null) ? $pCellParent->getHighestRow() : 1048576; // Max 1,048,576 rows for Excel2007
3519 $output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
3520 $val = $rangeWS2 . $val . $endRowColRef;
3521 }
3522 }
3523
3524 $localeConstant = false;
3525 if ($opCharacter == '"') {
3526 // UnEscape any quotes within the string
3527 $val = self::wrapResult(str_replace('""', '"', self::unwrapResult($val)));
3528 } elseif (is_numeric($val)) {
3529 if ((strpos($val, '.') !== false) || (stripos($val, 'e') !== false) || ($val > PHP_INT_MAX) || ($val < -PHP_INT_MAX)) {
3530 $val = (float) $val;
3531 } else {
3532 $val = (int) $val;
3533 }
3534 } elseif (isset(self::$excelConstants[trim(strtoupper($val))])) {
3535 $excelConstant = trim(strtoupper($val));
3536 $val = self::$excelConstants[$excelConstant];
3537 } elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$localeBoolean)) !== false) {
3538 $val = self::$excelConstants[$localeConstant];
3539 }
3540 $details = ['type' => 'Value', 'value' => $val, 'reference' => null];
3541 if ($localeConstant) {
3542 $details['localeValue'] = $localeConstant;
3543 }
3544 $output[] = $details;
3545 }
3546 $index += $length;
3547 } elseif ($opCharacter == '$') { // absolute row or column range
3548 ++$index;
3549 } elseif ($opCharacter == ')') { // miscellaneous error checking
3550 if ($expectingOperand) {
3551 $output[] = ['type' => 'NULL Value', 'value' => self::$excelConstants['NULL'], 'reference' => null];
3552 $expectingOperand = false;
3553 $expectingOperator = true;
3554 } else {
3555 return $this->raiseFormulaError("Formula Error: Unexpected ')'");
3556 }
3557 } elseif (isset(self::$operators[$opCharacter]) && !$expectingOperator) {
3558 return $this->raiseFormulaError("Formula Error: Unexpected operator '$opCharacter'");
3559 } else { // I don't even want to know what you did to get here
3560 return $this->raiseFormulaError('Formula Error: An unexpected error occured');
3561 }
3562 // Test for end of formula string
3563 if ($index == strlen($formula)) {
3564 // Did we end with an operator?.
3565 // Only valid for the % unary operator
3566 if ((isset(self::$operators[$opCharacter])) && ($opCharacter != '%')) {
3567 return $this->raiseFormulaError("Formula Error: Operator '$opCharacter' has no operands");
3568 }
3569
3570 break;
3571 }
3572 // Ignore white space
3573 while (($formula[$index] == "\n") || ($formula[$index] == "\r")) {
3574 ++$index;
3575 }
3576 if ($formula[$index] == ' ') {
3577 while ($formula[$index] == ' ') {
3578 ++$index;
3579 }
3580 // If we're expecting an operator, but only have a space between the previous and next operands (and both are
3581 // Cell References) then we have an INTERSECTION operator
3582 if (($expectingOperator) && (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match)) &&
3583 ($output[count($output) - 1]['type'] == 'Cell Reference')) {
3584 while ($stack->count() > 0 &&
3585 ($o2 = $stack->last()) &&
3586 isset(self::$operators[$o2['value']]) &&
3587 @(self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2['value']] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2['value']])) {
3588 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
3589 }
3590 $stack->push('Binary Operator', '|'); // Put an Intersect Operator on the stack
3591 $expectingOperator = false;
3592 }
3593 }
3594 }
3595
3596 while (($op = $stack->pop()) !== null) { // pop everything off the stack and push onto output
3597 if ((is_array($op) && $op['value'] == '(') || ($op === '(')) {
3598 return $this->raiseFormulaError("Formula Error: Expecting ')'"); // if there are any opening braces on the stack, then braces were unbalanced
3599 }
3600 $output[] = $op;
3601 }
3602
3603 return $output;
3604 }
3605
3606 private static function dataTestReference(&$operandData)
3607 {
3608 $operand = $operandData['value'];
3609 if (($operandData['reference'] === null) && (is_array($operand))) {
3610 $rKeys = array_keys($operand);
3611 $rowKey = array_shift($rKeys);
3612 $cKeys = array_keys(array_keys($operand[$rowKey]));
3613 $colKey = array_shift($cKeys);
3614 if (ctype_upper($colKey)) {
3615 $operandData['reference'] = $colKey . $rowKey;
3616 }
3617 }
3618
3619 return $operand;
3620 }
3621
3622 // evaluate postfix notation
3623
3624 /**
3625 * @param mixed $tokens
3626 * @param null|string $cellID
3627 * @param null|Cell $pCell
3628 *
3629 * @return bool
3630 */
3631 private function processTokenStack($tokens, $cellID = null, Cell $pCell = null)
3632 {
3633 if ($tokens == false) {
3634 return false;
3635 }
3636
3637 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
3638 // so we store the parent cell collection so that we can re-attach it when necessary
3639 $pCellWorksheet = ($pCell !== null) ? $pCell->getWorksheet() : null;
3640 $pCellParent = ($pCell !== null) ? $pCell->getParent() : null;
3641 $stack = new Stack();
3642
3643 // Loop through each token in turn
3644 foreach ($tokens as $tokenData) {
3645 $token = $tokenData['value'];
3646 // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
3647 if (isset(self::$binaryOperators[$token])) {
3648 // We must have two operands, error if we don't
3649 if (($operand2Data = $stack->pop()) === null) {
3650 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
3651 }
3652 if (($operand1Data = $stack->pop()) === null) {
3653 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
3654 }
3655
3656 $operand1 = self::dataTestReference($operand1Data);
3657 $operand2 = self::dataTestReference($operand2Data);
3658
3659 // Log what we're doing
3660 if ($token == ':') {
3661 $this->debugLog->writeDebugLog('Evaluating Range ', $this->showValue($operand1Data['reference']), ' ', $token, ' ', $this->showValue($operand2Data['reference']));
3662 } else {
3663 $this->debugLog->writeDebugLog('Evaluating ', $this->showValue($operand1), ' ', $token, ' ', $this->showValue($operand2));
3664 }
3665
3666 // Process the operation in the appropriate manner
3667 switch ($token) {
3668 // Comparison (Boolean) Operators
3669 case '>': // Greater than
3670 case '<': // Less than
3671 case '>=': // Greater than or Equal to
3672 case '<=': // Less than or Equal to
3673 case '=': // Equality
3674 case '<>': // Inequality
3675 $this->executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
3676
3677 break;
3678 // Binary Operators
3679 case ':': // Range
3680 if (strpos($operand1Data['reference'], '!') !== false) {
3681 list($sheet1, $operand1Data['reference']) = Worksheet::extractSheetTitle($operand1Data['reference'], true);
3682 } else {
3683 $sheet1 = ($pCellParent !== null) ? $pCellWorksheet->getTitle() : '';
3684 }
3685
3686 list($sheet2, $operand2Data['reference']) = Worksheet::extractSheetTitle($operand2Data['reference'], true);
3687 if (empty($sheet2)) {
3688 $sheet2 = $sheet1;
3689 }
3690
3691 if ($sheet1 == $sheet2) {
3692 if ($operand1Data['reference'] === null) {
3693 if ((trim($operand1Data['value']) != '') && (is_numeric($operand1Data['value']))) {
3694 $operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value'];
3695 } elseif (trim($operand1Data['reference']) == '') {
3696 $operand1Data['reference'] = $pCell->getCoordinate();
3697 } else {
3698 $operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow();
3699 }
3700 }
3701 if ($operand2Data['reference'] === null) {
3702 if ((trim($operand2Data['value']) != '') && (is_numeric($operand2Data['value']))) {
3703 $operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value'];
3704 } elseif (trim($operand2Data['reference']) == '') {
3705 $operand2Data['reference'] = $pCell->getCoordinate();
3706 } else {
3707 $operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow();
3708 }
3709 }
3710
3711 $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference']));
3712 $oCol = $oRow = [];
3713 foreach ($oData as $oDatum) {
3714 $oCR = Coordinate::coordinateFromString($oDatum);
3715 $oCol[] = Coordinate::columnIndexFromString($oCR[0]) - 1;
3716 $oRow[] = $oCR[1];
3717 }
3718 $cellRef = Coordinate::stringFromColumnIndex(min($oCol) + 1) . min($oRow) . ':' . Coordinate::stringFromColumnIndex(max($oCol) + 1) . max($oRow);
3719 if ($pCellParent !== null) {
3720 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($sheet1), false);
3721 } else {
3722 return $this->raiseFormulaError('Unable to access Cell Reference');
3723 }
3724 $stack->push('Cell Reference', $cellValue, $cellRef);
3725 } else {
3726 $stack->push('Error', Functions::REF(), null);
3727 }
3728
3729 break;
3730 case '+': // Addition
3731 $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'plusEquals', $stack);
3732
3733 break;
3734 case '-': // Subtraction
3735 $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'minusEquals', $stack);
3736
3737 break;
3738 case '*': // Multiplication
3739 $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'arrayTimesEquals', $stack);
3740
3741 break;
3742 case '/': // Division
3743 $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'arrayRightDivide', $stack);
3744
3745 break;
3746 case '^': // Exponential
3747 $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'power', $stack);
3748
3749 break;
3750 case '&': // Concatenation
3751 // If either of the operands is a matrix, we need to treat them both as matrices
3752 // (converting the other operand to a matrix if need be); then perform the required
3753 // matrix operation
3754 if (is_bool($operand1)) {
3755 $operand1 = ($operand1) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
3756 }
3757 if (is_bool($operand2)) {
3758 $operand2 = ($operand2) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
3759 }
3760 if ((is_array($operand1)) || (is_array($operand2))) {
3761 // Ensure that both operands are arrays/matrices
3762 self::checkMatrixOperands($operand1, $operand2, 2);
3763
3764 try {
3765 // Convert operand 1 from a PHP array to a matrix
3766 $matrix = new Shared\JAMA\Matrix($operand1);
3767 // Perform the required operation against the operand 1 matrix, passing in operand 2
3768 $matrixResult = $matrix->concat($operand2);
3769 $result = $matrixResult->getArray();
3770 } catch (\Exception $ex) {
3771 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
3772 $result = '#VALUE!';
3773 }
3774 } else {
3775 $result = '"' . str_replace('""', '"', self::unwrapResult($operand1) . self::unwrapResult($operand2)) . '"';
3776 }
3777 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
3778 $stack->push('Value', $result);
3779
3780 break;
3781 case '|': // Intersect
3782 $rowIntersect = array_intersect_key($operand1, $operand2);
3783 $cellIntersect = $oCol = $oRow = [];
3784 foreach (array_keys($rowIntersect) as $row) {
3785 $oRow[] = $row;
3786 foreach ($rowIntersect[$row] as $col => $data) {
3787 $oCol[] = Coordinate::columnIndexFromString($col) - 1;
3788 $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]);
3789 }
3790 }
3791 $cellRef = Coordinate::stringFromColumnIndex(min($oCol) + 1) . min($oRow) . ':' . Coordinate::stringFromColumnIndex(max($oCol) + 1) . max($oRow);
3792 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($cellIntersect));
3793 $stack->push('Value', $cellIntersect, $cellRef);
3794
3795 break;
3796 }
3797
3798 // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
3799 } elseif (($token === '~') || ($token === '%')) {
3800 if (($arg = $stack->pop()) === null) {
3801 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
3802 }
3803 $arg = $arg['value'];
3804 if ($token === '~') {
3805 $this->debugLog->writeDebugLog('Evaluating Negation of ', $this->showValue($arg));
3806 $multiplier = -1;
3807 } else {
3808 $this->debugLog->writeDebugLog('Evaluating Percentile of ', $this->showValue($arg));
3809 $multiplier = 0.01;
3810 }
3811 if (is_array($arg)) {
3812 self::checkMatrixOperands($arg, $multiplier, 2);
3813
3814 try {
3815 $matrix1 = new Shared\JAMA\Matrix($arg);
3816 $matrixResult = $matrix1->arrayTimesEquals($multiplier);
3817 $result = $matrixResult->getArray();
3818 } catch (\Exception $ex) {
3819 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
3820 $result = '#VALUE!';
3821 }
3822 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
3823 $stack->push('Value', $result);
3824 } else {
3825 $this->executeNumericBinaryOperation($multiplier, $arg, '*', 'arrayTimesEquals', $stack);
3826 }
3827 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) {
3828 $cellRef = null;
3829 if (isset($matches[8])) {
3830 if ($pCell === null) {
3831 // We can't access the range, so return a REF error
3832 $cellValue = Functions::REF();
3833 } else {
3834 $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10];
3835 if ($matches[2] > '') {
3836 $matches[2] = trim($matches[2], "\"'");
3837 if ((strpos($matches[2], '[') !== false) || (strpos($matches[2], ']') !== false)) {
3838 // It's a Reference to an external spreadsheet (not currently supported)
3839 return $this->raiseFormulaError('Unable to access External Workbook');
3840 }
3841 $matches[2] = trim($matches[2], "\"'");
3842 $this->debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
3843 if ($pCellParent !== null) {
3844 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
3845 } else {
3846 return $this->raiseFormulaError('Unable to access Cell Reference');
3847 }
3848 $this->debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
3849 } else {
3850 $this->debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
3851 if ($pCellParent !== null) {
3852 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
3853 } else {
3854 return $this->raiseFormulaError('Unable to access Cell Reference');
3855 }
3856 $this->debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
3857 }
3858 }
3859 } else {
3860 if ($pCell === null) {
3861 // We can't access the cell, so return a REF error
3862 $cellValue = Functions::REF();
3863 } else {
3864 $cellRef = $matches[6] . $matches[7];
3865 if ($matches[2] > '') {
3866 $matches[2] = trim($matches[2], "\"'");
3867 if ((strpos($matches[2], '[') !== false) || (strpos($matches[2], ']') !== false)) {
3868 // It's a Reference to an external spreadsheet (not currently supported)
3869 return $this->raiseFormulaError('Unable to access External Workbook');
3870 }
3871 $this->debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
3872 if ($pCellParent !== null) {
3873 $cellSheet = $this->spreadsheet->getSheetByName($matches[2]);
3874 if ($cellSheet && $cellSheet->cellExists($cellRef)) {
3875 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
3876 $pCell->attach($pCellParent);
3877 } else {
3878 $cellValue = null;
3879 }
3880 } else {
3881 return $this->raiseFormulaError('Unable to access Cell Reference');
3882 }
3883 $this->debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
3884 } else {
3885 $this->debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
3886 if ($pCellParent->has($cellRef)) {
3887 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
3888 $pCell->attach($pCellParent);
3889 } else {
3890 $cellValue = null;
3891 }
3892 $this->debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
3893 }
3894 }
3895 }
3896 $stack->push('Value', $cellValue, $cellRef);
3897
3898 // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
3899 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) {
3900 $functionName = $matches[1];
3901 $argCount = $stack->pop();
3902 $argCount = $argCount['value'];
3903 if ($functionName != 'MKMATRIX') {
3904 $this->debugLog->writeDebugLog('Evaluating Function ', self::localeFunc($functionName), '() with ', (($argCount == 0) ? 'no' : $argCount), ' argument', (($argCount == 1) ? '' : 's'));
3905 }
3906 if ((isset(self::$phpSpreadsheetFunctions[$functionName])) || (isset(self::$controlFunctions[$functionName]))) { // function
3907 if (isset(self::$phpSpreadsheetFunctions[$functionName])) {
3908 $functionCall = self::$phpSpreadsheetFunctions[$functionName]['functionCall'];
3909 $passByReference = isset(self::$phpSpreadsheetFunctions[$functionName]['passByReference']);
3910 $passCellReference = isset(self::$phpSpreadsheetFunctions[$functionName]['passCellReference']);
3911 } elseif (isset(self::$controlFunctions[$functionName])) {
3912 $functionCall = self::$controlFunctions[$functionName]['functionCall'];
3913 $passByReference = isset(self::$controlFunctions[$functionName]['passByReference']);
3914 $passCellReference = isset(self::$controlFunctions[$functionName]['passCellReference']);
3915 }
3916 // get the arguments for this function
3917 $args = $argArrayVals = [];
3918 for ($i = 0; $i < $argCount; ++$i) {
3919 $arg = $stack->pop();
3920 $a = $argCount - $i - 1;
3921 if (($passByReference) &&
3922 (isset(self::$phpSpreadsheetFunctions[$functionName]['passByReference'][$a])) &&
3923 (self::$phpSpreadsheetFunctions[$functionName]['passByReference'][$a])) {
3924 if ($arg['reference'] === null) {
3925 $args[] = $cellID;
3926 if ($functionName != 'MKMATRIX') {
3927 $argArrayVals[] = $this->showValue($cellID);
3928 }
3929 } else {
3930 $args[] = $arg['reference'];
3931 if ($functionName != 'MKMATRIX') {
3932 $argArrayVals[] = $this->showValue($arg['reference']);
3933 }
3934 }
3935 } else {
3936 $args[] = self::unwrapResult($arg['value']);
3937 if ($functionName != 'MKMATRIX') {
3938 $argArrayVals[] = $this->showValue($arg['value']);
3939 }
3940 }
3941 }
3942 // Reverse the order of the arguments
3943 krsort($args);
3944
3945 if (($passByReference) && ($argCount == 0)) {
3946 $args[] = $cellID;
3947 $argArrayVals[] = $this->showValue($cellID);
3948 }
3949
3950 if ($functionName != 'MKMATRIX') {
3951 if ($this->debugLog->getWriteDebugLog()) {
3952 krsort($argArrayVals);
3953 $this->debugLog->writeDebugLog('Evaluating ', self::localeFunc($functionName), '( ', implode(self::$localeArgumentSeparator . ' ', Functions::flattenArray($argArrayVals)), ' )');
3954 }
3955 }
3956
3957 // Process the argument with the appropriate function call
3958 $args = $this->addCellReference($args, $passCellReference, $functionCall, $pCell);
3959
3960 if (!is_array($functionCall)) {
3961 foreach ($args as &$arg) {
3962 $arg = Functions::flattenSingleValue($arg);
3963 }
3964 unset($arg);
3965 }
3966 $result = call_user_func_array($functionCall, $args);
3967
3968 if ($functionName != 'MKMATRIX') {
3969 $this->debugLog->writeDebugLog('Evaluation Result for ', self::localeFunc($functionName), '() function call is ', $this->showTypeDetails($result));
3970 }
3971 $stack->push('Value', self::wrapResult($result));
3972 }
3973 } else {
3974 // if the token is a number, boolean, string or an Excel error, push it onto the stack
3975 if (isset(self::$excelConstants[strtoupper($token)])) {
3976 $excelConstant = strtoupper($token);
3977 $stack->push('Constant Value', self::$excelConstants[$excelConstant]);
3978 $this->debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->showTypeDetails(self::$excelConstants[$excelConstant]));
3979 } elseif ((is_numeric($token)) || ($token === null) || (is_bool($token)) || ($token == '') || ($token[0] == '"') || ($token[0] == '#')) {
3980 $stack->push('Value', $token);
3981 // if the token is a named range, push the named range name onto the stack
3982 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) {
3983 $namedRange = $matches[6];
3984 $this->debugLog->writeDebugLog('Evaluating Named Range ', $namedRange);
3985
3986 $cellValue = $this->extractNamedRange($namedRange, ((null !== $pCell) ? $pCellWorksheet : null), false);
3987 $pCell->attach($pCellParent);
3988 $this->debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->showTypeDetails($cellValue));
3989 $stack->push('Named Range', $cellValue, $namedRange);
3990 } else {
3991 return $this->raiseFormulaError("undefined variable '$token'");
3992 }
3993 }
3994 }
3995 // when we're out of tokens, the stack should have a single element, the final result
3996 if ($stack->count() != 1) {
3997 return $this->raiseFormulaError('internal error');
3998 }
3999 $output = $stack->pop();
4000 $output = $output['value'];
4001
4002 return $output;
4003 }
4004
4005 private function validateBinaryOperand(&$operand, &$stack)
4006 {
4007 if (is_array($operand)) {
4008 if ((count($operand, COUNT_RECURSIVE) - count($operand)) == 1) {
4009 do {
4010 $operand = array_pop($operand);
4011 } while (is_array($operand));
4012 }
4013 }
4014 // Numbers, matrices and booleans can pass straight through, as they're already valid
4015 if (is_string($operand)) {
4016 // We only need special validations for the operand if it is a string
4017 // Start by stripping off the quotation marks we use to identify true excel string values internally
4018 if ($operand > '' && $operand[0] == '"') {
4019 $operand = self::unwrapResult($operand);
4020 }
4021 // If the string is a numeric value, we treat it as a numeric, so no further testing
4022 if (!is_numeric($operand)) {
4023 // If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
4024 if ($operand > '' && $operand[0] == '#') {
4025 $stack->push('Value', $operand);
4026 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($operand));
4027
4028 return false;
4029 } elseif (!Shared\StringHelper::convertToNumberIfFraction($operand)) {
4030 // If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
4031 $stack->push('Value', '#VALUE!');
4032 $this->debugLog->writeDebugLog('Evaluation Result is a ', $this->showTypeDetails('#VALUE!'));
4033
4034 return false;
4035 }
4036 }
4037 }
4038
4039 // return a true if the value of the operand is one that we can use in normal binary operations
4040 return true;
4041 }
4042
4043 /**
4044 * @param null|string $cellID
4045 * @param mixed $operand1
4046 * @param mixed $operand2
4047 * @param string $operation
4048 * @param Stack $stack
4049 * @param bool $recursingArrays
4050 *
4051 * @return bool
4052 */
4053 private function executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, Stack &$stack, $recursingArrays = false)
4054 {
4055 // If we're dealing with matrix operations, we want a matrix result
4056 if ((is_array($operand1)) || (is_array($operand2))) {
4057 $result = [];
4058 if ((is_array($operand1)) && (!is_array($operand2))) {
4059 foreach ($operand1 as $x => $operandData) {
4060 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operandData), ' ', $operation, ' ', $this->showValue($operand2));
4061 $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2, $operation, $stack);
4062 $r = $stack->pop();
4063 $result[$x] = $r['value'];
4064 }
4065 } elseif ((!is_array($operand1)) && (is_array($operand2))) {
4066 foreach ($operand2 as $x => $operandData) {
4067 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operand1), ' ', $operation, ' ', $this->showValue($operandData));
4068 $this->executeBinaryComparisonOperation($cellID, $operand1, $operandData, $operation, $stack);
4069 $r = $stack->pop();
4070 $result[$x] = $r['value'];
4071 }
4072 } else {
4073 if (!$recursingArrays) {
4074 self::checkMatrixOperands($operand1, $operand2, 2);
4075 }
4076 foreach ($operand1 as $x => $operandData) {
4077 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operandData), ' ', $operation, ' ', $this->showValue($operand2[$x]));
4078 $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2[$x], $operation, $stack, true);
4079 $r = $stack->pop();
4080 $result[$x] = $r['value'];
4081 }
4082 }
4083 // Log the result details
4084 $this->debugLog->writeDebugLog('Comparison Evaluation Result is ', $this->showTypeDetails($result));
4085 // And push the result onto the stack
4086 $stack->push('Array', $result);
4087
4088 return true;
4089 }
4090
4091 // Simple validate the two operands if they are string values
4092 if (is_string($operand1) && $operand1 > '' && $operand1[0] == '"') {
4093 $operand1 = self::unwrapResult($operand1);
4094 }
4095 if (is_string($operand2) && $operand2 > '' && $operand2[0] == '"') {
4096 $operand2 = self::unwrapResult($operand2);
4097 }
4098
4099 // Use case insensitive comparaison if not OpenOffice mode
4100 if (Functions::getCompatibilityMode() != Functions::COMPATIBILITY_OPENOFFICE) {
4101 if (is_string($operand1)) {
4102 $operand1 = strtoupper($operand1);
4103 }
4104 if (is_string($operand2)) {
4105 $operand2 = strtoupper($operand2);
4106 }
4107 }
4108
4109 $useLowercaseFirstComparison = is_string($operand1) && is_string($operand2) && Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE;
4110
4111 // execute the necessary operation
4112 switch ($operation) {
4113 // Greater than
4114 case '>':
4115 if ($useLowercaseFirstComparison) {
4116 $result = $this->strcmpLowercaseFirst($operand1, $operand2) > 0;
4117 } else {
4118 $result = ($operand1 > $operand2);
4119 }
4120
4121 break;
4122 // Less than
4123 case '<':
4124 if ($useLowercaseFirstComparison) {
4125 $result = $this->strcmpLowercaseFirst($operand1, $operand2) < 0;
4126 } else {
4127 $result = ($operand1 < $operand2);
4128 }
4129
4130 break;
4131 // Equality
4132 case '=':
4133 if (is_numeric($operand1) && is_numeric($operand2)) {
4134 $result = (abs($operand1 - $operand2) < $this->delta);
4135 } else {
4136 $result = strcmp($operand1, $operand2) == 0;
4137 }
4138
4139 break;
4140 // Greater than or equal
4141 case '>=':
4142 if (is_numeric($operand1) && is_numeric($operand2)) {
4143 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 > $operand2));
4144 } elseif ($useLowercaseFirstComparison) {
4145 $result = $this->strcmpLowercaseFirst($operand1, $operand2) >= 0;
4146 } else {
4147 $result = strcmp($operand1, $operand2) >= 0;
4148 }
4149
4150 break;
4151 // Less than or equal
4152 case '<=':
4153 if (is_numeric($operand1) && is_numeric($operand2)) {
4154 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 < $operand2));
4155 } elseif ($useLowercaseFirstComparison) {
4156 $result = $this->strcmpLowercaseFirst($operand1, $operand2) <= 0;
4157 } else {
4158 $result = strcmp($operand1, $operand2) <= 0;
4159 }
4160
4161 break;
4162 // Inequality
4163 case '<>':
4164 if (is_numeric($operand1) && is_numeric($operand2)) {
4165 $result = (abs($operand1 - $operand2) > 1E-14);
4166 } else {
4167 $result = strcmp($operand1, $operand2) != 0;
4168 }
4169
4170 break;
4171 }
4172
4173 // Log the result details
4174 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
4175 // And push the result onto the stack
4176 $stack->push('Value', $result);
4177
4178 return true;
4179 }
4180
4181 /**
4182 * Compare two strings in the same way as strcmp() except that lowercase come before uppercase letters.
4183 *
4184 * @param string $str1 First string value for the comparison
4185 * @param string $str2 Second string value for the comparison
4186 *
4187 * @return int
4188 */
4189 private function strcmpLowercaseFirst($str1, $str2)
4190 {
4191 $inversedStr1 = Shared\StringHelper::strCaseReverse($str1);
4192 $inversedStr2 = Shared\StringHelper::strCaseReverse($str2);
4193
4194 return strcmp($inversedStr1, $inversedStr2);
4195 }
4196
4197 /**
4198 * @param mixed $operand1
4199 * @param mixed $operand2
4200 * @param mixed $operation
4201 * @param string $matrixFunction
4202 * @param mixed $stack
4203 *
4204 * @return bool
4205 */
4206 private function executeNumericBinaryOperation($operand1, $operand2, $operation, $matrixFunction, &$stack)
4207 {
4208 // Validate the two operands
4209 if (!$this->validateBinaryOperand($operand1, $stack)) {
4210 return false;
4211 }
4212 if (!$this->validateBinaryOperand($operand2, $stack)) {
4213 return false;
4214 }
4215
4216 // If either of the operands is a matrix, we need to treat them both as matrices
4217 // (converting the other operand to a matrix if need be); then perform the required
4218 // matrix operation
4219 if ((is_array($operand1)) || (is_array($operand2))) {
4220 // Ensure that both operands are arrays/matrices of the same size
4221 self::checkMatrixOperands($operand1, $operand2, 2);
4222
4223 try {
4224 // Convert operand 1 from a PHP array to a matrix
4225 $matrix = new Shared\JAMA\Matrix($operand1);
4226 // Perform the required operation against the operand 1 matrix, passing in operand 2
4227 $matrixResult = $matrix->$matrixFunction($operand2);
4228 $result = $matrixResult->getArray();
4229 } catch (\Exception $ex) {
4230 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
4231 $result = '#VALUE!';
4232 }
4233 } else {
4234 if ((Functions::getCompatibilityMode() != Functions::COMPATIBILITY_OPENOFFICE) &&
4235 ((is_string($operand1) && !is_numeric($operand1) && strlen($operand1) > 0) ||
4236 (is_string($operand2) && !is_numeric($operand2) && strlen($operand2) > 0))) {
4237 $result = Functions::VALUE();
4238 } else {
4239 // If we're dealing with non-matrix operations, execute the necessary operation
4240 switch ($operation) {
4241 // Addition
4242 case '+':
4243 $result = $operand1 + $operand2;
4244
4245 break;
4246 // Subtraction
4247 case '-':
4248 $result = $operand1 - $operand2;
4249
4250 break;
4251 // Multiplication
4252 case '*':
4253 $result = $operand1 * $operand2;
4254
4255 break;
4256 // Division
4257 case '/':
4258 if ($operand2 == 0) {
4259 // Trap for Divide by Zero error
4260 $stack->push('Value', '#DIV/0!');
4261 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails('#DIV/0!'));
4262
4263 return false;
4264 }
4265 $result = $operand1 / $operand2;
4266
4267 break;
4268 // Power
4269 case '^':
4270 $result = pow($operand1, $operand2);
4271
4272 break;
4273 }
4274 }
4275 }
4276
4277 // Log the result details
4278 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
4279 // And push the result onto the stack
4280 $stack->push('Value', $result);
4281
4282 return true;
4283 }
4284
4285 // trigger an error, but nicely, if need be
4286 protected function raiseFormulaError($errorMessage)
4287 {
4288 $this->formulaError = $errorMessage;
4289 $this->cyclicReferenceStack->clear();
4290 if (!$this->suppressFormulaErrors) {
4291 throw new Exception($errorMessage);
4292 }
4293 trigger_error($errorMessage, E_USER_ERROR);
4294
4295 return false;
4296 }
4297
4298 /**
4299 * Extract range values.
4300 *
4301 * @param string &$pRange String based range representation
4302 * @param Worksheet $pSheet Worksheet
4303 * @param bool $resetLog Flag indicating whether calculation log should be reset or not
4304 *
4305 * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
4306 */
4307 public function extractCellRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
4308 {
4309 // Return value
4310 $returnValue = [];
4311
4312 if ($pSheet !== null) {
4313 $pSheetName = $pSheet->getTitle();
4314 if (strpos($pRange, '!') !== false) {
4315 list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true);
4316 $pSheet = $this->spreadsheet->getSheetByName($pSheetName);
4317 }
4318
4319 // Extract range
4320 $aReferences = Coordinate::extractAllCellReferencesInRange($pRange);
4321 $pRange = $pSheetName . '!' . $pRange;
4322 if (!isset($aReferences[1])) {
4323 $currentCol = '';
4324 $currentRow = 0;
4325 // Single cell in range
4326 sscanf($aReferences[0], '%[A-Z]%d', $currentCol, $currentRow);
4327 if ($pSheet->cellExists($aReferences[0])) {
4328 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
4329 } else {
4330 $returnValue[$currentRow][$currentCol] = null;
4331 }
4332 } else {
4333 // Extract cell data for all cells in the range
4334 foreach ($aReferences as $reference) {
4335 $currentCol = '';
4336 $currentRow = 0;
4337 // Extract range
4338 sscanf($reference, '%[A-Z]%d', $currentCol, $currentRow);
4339 if ($pSheet->cellExists($reference)) {
4340 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
4341 } else {
4342 $returnValue[$currentRow][$currentCol] = null;
4343 }
4344 }
4345 }
4346 }
4347
4348 return $returnValue;
4349 }
4350
4351 /**
4352 * Extract range values.
4353 *
4354 * @param string &$pRange String based range representation
4355 * @param Worksheet $pSheet Worksheet
4356 * @param bool $resetLog Flag indicating whether calculation log should be reset or not
4357 *
4358 * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
4359 */
4360 public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
4361 {
4362 // Return value
4363 $returnValue = [];
4364
4365 if ($pSheet !== null) {
4366 $pSheetName = $pSheet->getTitle();
4367 if (strpos($pRange, '!') !== false) {
4368 list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true);
4369 $pSheet = $this->spreadsheet->getSheetByName($pSheetName);
4370 }
4371
4372 // Named range?
4373 $namedRange = NamedRange::resolveRange($pRange, $pSheet);
4374 if ($namedRange !== null) {
4375 $pSheet = $namedRange->getWorksheet();
4376 $pRange = $namedRange->getRange();
4377 $splitRange = Coordinate::splitRange($pRange);
4378 // Convert row and column references
4379 if (ctype_alpha($splitRange[0][0])) {
4380 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
4381 } elseif (ctype_digit($splitRange[0][0])) {
4382 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
4383 }
4384 } else {
4385 return Functions::REF();
4386 }
4387
4388 // Extract range
4389 $aReferences = Coordinate::extractAllCellReferencesInRange($pRange);
4390 if (!isset($aReferences[1])) {
4391 // Single cell (or single column or row) in range
4392 list($currentCol, $currentRow) = Coordinate::coordinateFromString($aReferences[0]);
4393 if ($pSheet->cellExists($aReferences[0])) {
4394 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
4395 } else {
4396 $returnValue[$currentRow][$currentCol] = null;
4397 }
4398 } else {
4399 // Extract cell data for all cells in the range
4400 foreach ($aReferences as $reference) {
4401 // Extract range
4402 list($currentCol, $currentRow) = Coordinate::coordinateFromString($reference);
4403 if ($pSheet->cellExists($reference)) {
4404 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
4405 } else {
4406 $returnValue[$currentRow][$currentCol] = null;
4407 }
4408 }
4409 }
4410 }
4411
4412 return $returnValue;
4413 }
4414
4415 /**
4416 * Is a specific function implemented?
4417 *
4418 * @param string $pFunction Function Name
4419 *
4420 * @return bool
4421 */
4422 public function isImplemented($pFunction)
4423 {
4424 $pFunction = strtoupper($pFunction);
4425 $notImplemented = !isset(self::$phpSpreadsheetFunctions[$pFunction]) || (is_array(self::$phpSpreadsheetFunctions[$pFunction]['functionCall']) && self::$phpSpreadsheetFunctions[$pFunction]['functionCall'][1] === 'DUMMY');
4426
4427 return !$notImplemented;
4428 }
4429
4430 /**
4431 * Get a list of all implemented functions as an array of function objects.
4432 *
4433 * @return array of Category
4434 */
4435 public function getFunctions()
4436 {
4437 return self::$phpSpreadsheetFunctions;
4438 }
4439
4440 /**
4441 * Get a list of implemented Excel function names.
4442 *
4443 * @return array
4444 */
4445 public function getImplementedFunctionNames()
4446 {
4447 $returnValue = [];
4448 foreach (self::$phpSpreadsheetFunctions as $functionName => $function) {
4449 if ($this->isImplemented($functionName)) {
4450 $returnValue[] = $functionName;
4451 }
4452 }
4453
4454 return $returnValue;
4455 }
4456
4457 /**
4458 * Add cell reference if needed while making sure that it is the last argument.
4459 *
4460 * @param array $args
4461 * @param bool $passCellReference
4462 * @param array|string $functionCall
4463 * @param null|Cell $pCell
4464 *
4465 * @return array
4466 */
4467 private function addCellReference(array $args, $passCellReference, $functionCall, Cell $pCell = null)
4468 {
4469 if ($passCellReference) {
4470 if (is_array($functionCall)) {
4471 $className = $functionCall[0];
4472 $methodName = $functionCall[1];
4473
4474 $reflectionMethod = new \ReflectionMethod($className, $methodName);
4475 $argumentCount = count($reflectionMethod->getParameters());
4476 while (count($args) < $argumentCount - 1) {
4477 $args[] = null;
4478 }
4479 }
4480
4481 $args[] = $pCell;
4482 }
4483
4484 return $args;
4485 }
4486 }
4487