ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Calculation.php
Go to the documentation of this file.
1<?php
2
4
15use ReflectionMethod;
16
18{
21 // Numeric operand
22 const CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?';
23 // String operand
24 const CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"';
25 // Opening bracket
27 // Function (allow for the old @ symbol that could be used to prefix a function, but we'll ignore it)
28 const CALCULATION_REGEXP_FUNCTION = '@?(?:_xlfn\.)?([\p{L}][\p{L}\p{N}\.]*)[\s]*\‍(';
29 // Cell reference (cell or range of cells, with or without a sheet reference)
30 const CALCULATION_REGEXP_CELLREF = '((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?\b([a-z]{1,3})\$?(\d{1,7})(?![\w.])';
31 // Cell reference (with or without a sheet reference) ensuring absolute/relative
32 const CALCULATION_REGEXP_CELLREF_RELATIVE = '((([^\s\‍(,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?(\$?\b[a-z]{1,3})(\$?\d{1,7})(?![\w.])';
33 const CALCULATION_REGEXP_COLUMN_RANGE = '(((([^\s\‍(,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?(\$?[a-z]{1,3})):(?![.*])';
34 const CALCULATION_REGEXP_ROW_RANGE = '(((([^\s\‍(,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?(\$?[1-9][0-9]{0,6})):(?![.*])';
35 // Cell reference (with or without a sheet reference) ensuring absolute/relative
36 // Cell ranges ensuring absolute/relative
37 const CALCULATION_REGEXP_COLUMNRANGE_RELATIVE = '(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
38 const CALCULATION_REGEXP_ROWRANGE_RELATIVE = '(\$?\d{1,7}):(\$?\d{1,7})';
39 // Defined Names: Named Range of cells, or Named Formulae
40 const CALCULATION_REGEXP_DEFINEDNAME = '((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_\p{L}][_\p{L}\p{N}\.]*)';
41 // Error
42 const CALCULATION_REGEXP_ERROR = '\#[A-Z][A-Z0_\/]*[!\?]?';
43
45 const RETURN_ARRAY_AS_ERROR = 'error';
46 const RETURN_ARRAY_AS_VALUE = 'value';
47 const RETURN_ARRAY_AS_ARRAY = 'array';
48
52
54
60 private static $instance;
61
67 private $spreadsheet;
68
74 private $calculationCache = [];
75
82
89
90 private $branchPruningEnabled = true;
91
98 private static $operators = [
99 '+' => true, '-' => true, '*' => true, '/' => true,
100 '^' => true, '&' => true, '%' => false, '~' => false,
101 '>' => true, '<' => true, '=' => true, '>=' => true,
102 '<=' => true, '<>' => true, '|' => true, ':' => true,
103 ];
104
110 private static $binaryOperators = [
111 '+' => true, '-' => true, '*' => true, '/' => true,
112 '^' => true, '&' => true, '>' => true, '<' => true,
113 '=' => true, '>=' => true, '<=' => true, '<>' => true,
114 '|' => true, ':' => true,
115 ];
116
122 private $debugLog;
123
132
139
145 private static $referenceHelper;
146
153
154 private $cellStack = [];
155
164
165 private $cyclicFormulaCell = '';
166
173
179 private $delta = 0.1e-12;
180
186 private static $localeLanguage = 'en_us'; // US English (default locale)
187
194 private static $validLocaleLanguages = [
195 'en', // English (default language)
196 ];
197
203 private static $localeArgumentSeparator = ',';
204
205 private static $localeFunctions = [];
206
212 public static $localeBoolean = [
213 'TRUE' => 'TRUE',
214 'FALSE' => 'FALSE',
215 'NULL' => 'NULL',
216 ];
217
224 private static $excelConstants = [
225 'TRUE' => true,
226 'FALSE' => false,
227 'NULL' => null,
228 ];
229
230 // PhpSpreadsheet functions
231 private static $phpSpreadsheetFunctions = [
232 'ABS' => [
234 'functionCall' => [MathTrig\Absolute::class, 'evaluate'],
235 'argumentCount' => '1',
236 ],
237 'ACCRINT' => [
238 'category' => Category::CATEGORY_FINANCIAL,
239 'functionCall' => [Financial\Securities\AccruedInterest::class, 'periodic'],
240 'argumentCount' => '4-8',
241 ],
242 'ACCRINTM' => [
243 'category' => Category::CATEGORY_FINANCIAL,
244 'functionCall' => [Financial\Securities\AccruedInterest::class, 'atMaturity'],
245 'argumentCount' => '3-5',
246 ],
247 'ACOS' => [
249 'functionCall' => [MathTrig\Trig\Cosine::class, 'acos'],
250 'argumentCount' => '1',
251 ],
252 'ACOSH' => [
254 'functionCall' => [MathTrig\Trig\Cosine::class, 'acosh'],
255 'argumentCount' => '1',
256 ],
257 'ACOT' => [
259 'functionCall' => [MathTrig\Trig\Cotangent::class, 'acot'],
260 'argumentCount' => '1',
261 ],
262 'ACOTH' => [
264 'functionCall' => [MathTrig\Trig\Cotangent::class, 'acoth'],
265 'argumentCount' => '1',
266 ],
267 'ADDRESS' => [
269 'functionCall' => [LookupRef\Address::class, 'cell'],
270 'argumentCount' => '2-5',
271 ],
272 'AGGREGATE' => [
274 'functionCall' => [Functions::class, 'DUMMY'],
275 'argumentCount' => '3+',
276 ],
277 'AMORDEGRC' => [
278 'category' => Category::CATEGORY_FINANCIAL,
279 'functionCall' => [Financial\Amortization::class, 'AMORDEGRC'],
280 'argumentCount' => '6,7',
281 ],
282 'AMORLINC' => [
283 'category' => Category::CATEGORY_FINANCIAL,
284 'functionCall' => [Financial\Amortization::class, 'AMORLINC'],
285 'argumentCount' => '6,7',
286 ],
287 'AND' => [
288 'category' => Category::CATEGORY_LOGICAL,
289 'functionCall' => [Logical\Operations::class, 'logicalAnd'],
290 'argumentCount' => '1+',
291 ],
292 'ARABIC' => [
294 'functionCall' => [MathTrig\Arabic::class, 'evaluate'],
295 'argumentCount' => '1',
296 ],
297 'AREAS' => [
299 'functionCall' => [Functions::class, 'DUMMY'],
300 'argumentCount' => '1',
301 ],
302 'ARRAYTOTEXT' => [
304 'functionCall' => [Functions::class, 'DUMMY'],
305 'argumentCount' => '?',
306 ],
307 'ASC' => [
309 'functionCall' => [Functions::class, 'DUMMY'],
310 'argumentCount' => '1',
311 ],
312 'ASIN' => [
314 'functionCall' => [MathTrig\Trig\Sine::class, 'asin'],
315 'argumentCount' => '1',
316 ],
317 'ASINH' => [
319 'functionCall' => [MathTrig\Trig\Sine::class, 'asinh'],
320 'argumentCount' => '1',
321 ],
322 'ATAN' => [
324 'functionCall' => [MathTrig\Trig\Tangent::class, 'atan'],
325 'argumentCount' => '1',
326 ],
327 'ATAN2' => [
329 'functionCall' => [MathTrig\Trig\Tangent::class, 'atan2'],
330 'argumentCount' => '2',
331 ],
332 'ATANH' => [
334 'functionCall' => [MathTrig\Trig\Tangent::class, 'atanh'],
335 'argumentCount' => '1',
336 ],
337 'AVEDEV' => [
338 'category' => Category::CATEGORY_STATISTICAL,
339 'functionCall' => [Statistical\Averages::class, 'averageDeviations'],
340 'argumentCount' => '1+',
341 ],
342 'AVERAGE' => [
343 'category' => Category::CATEGORY_STATISTICAL,
344 'functionCall' => [Statistical\Averages::class, 'average'],
345 'argumentCount' => '1+',
346 ],
347 'AVERAGEA' => [
348 'category' => Category::CATEGORY_STATISTICAL,
349 'functionCall' => [Statistical\Averages::class, 'averageA'],
350 'argumentCount' => '1+',
351 ],
352 'AVERAGEIF' => [
353 'category' => Category::CATEGORY_STATISTICAL,
354 'functionCall' => [Statistical\Conditional::class, 'AVERAGEIF'],
355 'argumentCount' => '2,3',
356 ],
357 'AVERAGEIFS' => [
358 'category' => Category::CATEGORY_STATISTICAL,
359 'functionCall' => [Statistical\Conditional::class, 'AVERAGEIFS'],
360 'argumentCount' => '3+',
361 ],
362 'BAHTTEXT' => [
364 'functionCall' => [Functions::class, 'DUMMY'],
365 'argumentCount' => '1',
366 ],
367 'BASE' => [
369 'functionCall' => [MathTrig\Base::class, 'evaluate'],
370 'argumentCount' => '2,3',
371 ],
372 'BESSELI' => [
373 'category' => Category::CATEGORY_ENGINEERING,
374 'functionCall' => [Engineering\BesselI::class, 'BESSELI'],
375 'argumentCount' => '2',
376 ],
377 'BESSELJ' => [
378 'category' => Category::CATEGORY_ENGINEERING,
379 'functionCall' => [Engineering\BesselJ::class, 'BESSELJ'],
380 'argumentCount' => '2',
381 ],
382 'BESSELK' => [
383 'category' => Category::CATEGORY_ENGINEERING,
384 'functionCall' => [Engineering\BesselK::class, 'BESSELK'],
385 'argumentCount' => '2',
386 ],
387 'BESSELY' => [
388 'category' => Category::CATEGORY_ENGINEERING,
389 'functionCall' => [Engineering\BesselY::class, 'BESSELY'],
390 'argumentCount' => '2',
391 ],
392 'BETADIST' => [
393 'category' => Category::CATEGORY_STATISTICAL,
394 'functionCall' => [Statistical\Distributions\Beta::class, 'distribution'],
395 'argumentCount' => '3-5',
396 ],
397 'BETA.DIST' => [
398 'category' => Category::CATEGORY_STATISTICAL,
399 'functionCall' => [Functions::class, 'DUMMY'],
400 'argumentCount' => '4-6',
401 ],
402 'BETAINV' => [
403 'category' => Category::CATEGORY_STATISTICAL,
404 'functionCall' => [Statistical\Distributions\Beta::class, 'inverse'],
405 'argumentCount' => '3-5',
406 ],
407 'BETA.INV' => [
408 'category' => Category::CATEGORY_STATISTICAL,
409 'functionCall' => [Statistical\Distributions\Beta::class, 'inverse'],
410 'argumentCount' => '3-5',
411 ],
412 'BIN2DEC' => [
413 'category' => Category::CATEGORY_ENGINEERING,
414 'functionCall' => [Engineering\ConvertBinary::class, 'toDecimal'],
415 'argumentCount' => '1',
416 ],
417 'BIN2HEX' => [
418 'category' => Category::CATEGORY_ENGINEERING,
419 'functionCall' => [Engineering\ConvertBinary::class, 'toHex'],
420 'argumentCount' => '1,2',
421 ],
422 'BIN2OCT' => [
423 'category' => Category::CATEGORY_ENGINEERING,
424 'functionCall' => [Engineering\ConvertBinary::class, 'toOctal'],
425 'argumentCount' => '1,2',
426 ],
427 'BINOMDIST' => [
428 'category' => Category::CATEGORY_STATISTICAL,
429 'functionCall' => [Statistical\Distributions\Binomial::class, 'distribution'],
430 'argumentCount' => '4',
431 ],
432 'BINOM.DIST' => [
433 'category' => Category::CATEGORY_STATISTICAL,
434 'functionCall' => [Statistical\Distributions\Binomial::class, 'distribution'],
435 'argumentCount' => '4',
436 ],
437 'BINOM.DIST.RANGE' => [
438 'category' => Category::CATEGORY_STATISTICAL,
439 'functionCall' => [Statistical\Distributions\Binomial::class, 'range'],
440 'argumentCount' => '3,4',
441 ],
442 'BINOM.INV' => [
443 'category' => Category::CATEGORY_STATISTICAL,
444 'functionCall' => [Statistical\Distributions\Binomial::class, 'inverse'],
445 'argumentCount' => '3',
446 ],
447 'BITAND' => [
448 'category' => Category::CATEGORY_ENGINEERING,
449 'functionCall' => [Engineering\BitWise::class, 'BITAND'],
450 'argumentCount' => '2',
451 ],
452 'BITOR' => [
453 'category' => Category::CATEGORY_ENGINEERING,
454 'functionCall' => [Engineering\BitWise::class, 'BITOR'],
455 'argumentCount' => '2',
456 ],
457 'BITXOR' => [
458 'category' => Category::CATEGORY_ENGINEERING,
459 'functionCall' => [Engineering\BitWise::class, 'BITXOR'],
460 'argumentCount' => '2',
461 ],
462 'BITLSHIFT' => [
463 'category' => Category::CATEGORY_ENGINEERING,
464 'functionCall' => [Engineering\BitWise::class, 'BITLSHIFT'],
465 'argumentCount' => '2',
466 ],
467 'BITRSHIFT' => [
468 'category' => Category::CATEGORY_ENGINEERING,
469 'functionCall' => [Engineering\BitWise::class, 'BITRSHIFT'],
470 'argumentCount' => '2',
471 ],
472 'CEILING' => [
474 'functionCall' => [MathTrig\Ceiling::class, 'ceiling'],
475 'argumentCount' => '1-2', // 2 for Excel, 1-2 for Ods/Gnumeric
476 ],
477 'CEILING.MATH' => [
479 'functionCall' => [MathTrig\Ceiling::class, 'math'],
480 'argumentCount' => '1-3',
481 ],
482 'CEILING.PRECISE' => [
484 'functionCall' => [MathTrig\Ceiling::class, 'precise'],
485 'argumentCount' => '1,2',
486 ],
487 'CELL' => [
488 'category' => Category::CATEGORY_INFORMATION,
489 'functionCall' => [Functions::class, 'DUMMY'],
490 'argumentCount' => '1,2',
491 ],
492 'CHAR' => [
494 'functionCall' => [TextData\CharacterConvert::class, 'character'],
495 'argumentCount' => '1',
496 ],
497 'CHIDIST' => [
498 'category' => Category::CATEGORY_STATISTICAL,
499 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'distributionRightTail'],
500 'argumentCount' => '2',
501 ],
502 'CHISQ.DIST' => [
503 'category' => Category::CATEGORY_STATISTICAL,
504 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'distributionLeftTail'],
505 'argumentCount' => '3',
506 ],
507 'CHISQ.DIST.RT' => [
508 'category' => Category::CATEGORY_STATISTICAL,
509 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'distributionRightTail'],
510 'argumentCount' => '2',
511 ],
512 'CHIINV' => [
513 'category' => Category::CATEGORY_STATISTICAL,
514 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'inverseRightTail'],
515 'argumentCount' => '2',
516 ],
517 'CHISQ.INV' => [
518 'category' => Category::CATEGORY_STATISTICAL,
519 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'inverseLeftTail'],
520 'argumentCount' => '2',
521 ],
522 'CHISQ.INV.RT' => [
523 'category' => Category::CATEGORY_STATISTICAL,
524 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'inverseRightTail'],
525 'argumentCount' => '2',
526 ],
527 'CHITEST' => [
528 'category' => Category::CATEGORY_STATISTICAL,
529 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'test'],
530 'argumentCount' => '2',
531 ],
532 'CHISQ.TEST' => [
533 'category' => Category::CATEGORY_STATISTICAL,
534 'functionCall' => [Statistical\Distributions\ChiSquared::class, 'test'],
535 'argumentCount' => '2',
536 ],
537 'CHOOSE' => [
539 'functionCall' => [LookupRef\Selection::class, 'CHOOSE'],
540 'argumentCount' => '2+',
541 ],
542 'CLEAN' => [
544 'functionCall' => [TextData\Trim::class, 'nonPrintable'],
545 'argumentCount' => '1',
546 ],
547 'CODE' => [
549 'functionCall' => [TextData\CharacterConvert::class, 'code'],
550 'argumentCount' => '1',
551 ],
552 'COLUMN' => [
554 'functionCall' => [LookupRef\RowColumnInformation::class, 'COLUMN'],
555 'argumentCount' => '-1',
556 'passCellReference' => true,
557 'passByReference' => [true],
558 ],
559 'COLUMNS' => [
561 'functionCall' => [LookupRef\RowColumnInformation::class, 'COLUMNS'],
562 'argumentCount' => '1',
563 ],
564 'COMBIN' => [
566 'functionCall' => [MathTrig\Combinations::class, 'withoutRepetition'],
567 'argumentCount' => '2',
568 ],
569 'COMBINA' => [
571 'functionCall' => [MathTrig\Combinations::class, 'withRepetition'],
572 'argumentCount' => '2',
573 ],
574 'COMPLEX' => [
575 'category' => Category::CATEGORY_ENGINEERING,
576 'functionCall' => [Engineering\Complex::class, 'COMPLEX'],
577 'argumentCount' => '2,3',
578 ],
579 'CONCAT' => [
581 'functionCall' => [TextData\Concatenate::class, 'CONCATENATE'],
582 'argumentCount' => '1+',
583 ],
584 'CONCATENATE' => [
586 'functionCall' => [TextData\Concatenate::class, 'CONCATENATE'],
587 'argumentCount' => '1+',
588 ],
589 'CONFIDENCE' => [
590 'category' => Category::CATEGORY_STATISTICAL,
591 'functionCall' => [Statistical\Confidence::class, 'CONFIDENCE'],
592 'argumentCount' => '3',
593 ],
594 'CONFIDENCE.NORM' => [
595 'category' => Category::CATEGORY_STATISTICAL,
596 'functionCall' => [Statistical\Confidence::class, 'CONFIDENCE'],
597 'argumentCount' => '3',
598 ],
599 'CONFIDENCE.T' => [
600 'category' => Category::CATEGORY_STATISTICAL,
601 'functionCall' => [Functions::class, 'DUMMY'],
602 'argumentCount' => '3',
603 ],
604 'CONVERT' => [
605 'category' => Category::CATEGORY_ENGINEERING,
606 'functionCall' => [Engineering\ConvertUOM::class, 'CONVERT'],
607 'argumentCount' => '3',
608 ],
609 'CORREL' => [
610 'category' => Category::CATEGORY_STATISTICAL,
611 'functionCall' => [Statistical\Trends::class, 'CORREL'],
612 'argumentCount' => '2',
613 ],
614 'COS' => [
616 'functionCall' => [MathTrig\Trig\Cosine::class, 'cos'],
617 'argumentCount' => '1',
618 ],
619 'COSH' => [
621 'functionCall' => [MathTrig\Trig\Cosine::class, 'cosh'],
622 'argumentCount' => '1',
623 ],
624 'COT' => [
626 'functionCall' => [MathTrig\Trig\Cotangent::class, 'cot'],
627 'argumentCount' => '1',
628 ],
629 'COTH' => [
631 'functionCall' => [MathTrig\Trig\Cotangent::class, 'coth'],
632 'argumentCount' => '1',
633 ],
634 'COUNT' => [
635 'category' => Category::CATEGORY_STATISTICAL,
636 'functionCall' => [Statistical\Counts::class, 'COUNT'],
637 'argumentCount' => '1+',
638 ],
639 'COUNTA' => [
640 'category' => Category::CATEGORY_STATISTICAL,
641 'functionCall' => [Statistical\Counts::class, 'COUNTA'],
642 'argumentCount' => '1+',
643 ],
644 'COUNTBLANK' => [
645 'category' => Category::CATEGORY_STATISTICAL,
646 'functionCall' => [Statistical\Counts::class, 'COUNTBLANK'],
647 'argumentCount' => '1',
648 ],
649 'COUNTIF' => [
650 'category' => Category::CATEGORY_STATISTICAL,
651 'functionCall' => [Statistical\Conditional::class, 'COUNTIF'],
652 'argumentCount' => '2',
653 ],
654 'COUNTIFS' => [
655 'category' => Category::CATEGORY_STATISTICAL,
656 'functionCall' => [Statistical\Conditional::class, 'COUNTIFS'],
657 'argumentCount' => '2+',
658 ],
659 'COUPDAYBS' => [
660 'category' => Category::CATEGORY_FINANCIAL,
661 'functionCall' => [Financial\Coupons::class, 'COUPDAYBS'],
662 'argumentCount' => '3,4',
663 ],
664 'COUPDAYS' => [
665 'category' => Category::CATEGORY_FINANCIAL,
666 'functionCall' => [Financial\Coupons::class, 'COUPDAYS'],
667 'argumentCount' => '3,4',
668 ],
669 'COUPDAYSNC' => [
670 'category' => Category::CATEGORY_FINANCIAL,
671 'functionCall' => [Financial\Coupons::class, 'COUPDAYSNC'],
672 'argumentCount' => '3,4',
673 ],
674 'COUPNCD' => [
675 'category' => Category::CATEGORY_FINANCIAL,
676 'functionCall' => [Financial\Coupons::class, 'COUPNCD'],
677 'argumentCount' => '3,4',
678 ],
679 'COUPNUM' => [
680 'category' => Category::CATEGORY_FINANCIAL,
681 'functionCall' => [Financial\Coupons::class, 'COUPNUM'],
682 'argumentCount' => '3,4',
683 ],
684 'COUPPCD' => [
685 'category' => Category::CATEGORY_FINANCIAL,
686 'functionCall' => [Financial\Coupons::class, 'COUPPCD'],
687 'argumentCount' => '3,4',
688 ],
689 'COVAR' => [
690 'category' => Category::CATEGORY_STATISTICAL,
691 'functionCall' => [Statistical\Trends::class, 'COVAR'],
692 'argumentCount' => '2',
693 ],
694 'COVARIANCE.P' => [
695 'category' => Category::CATEGORY_STATISTICAL,
696 'functionCall' => [Statistical\Trends::class, 'COVAR'],
697 'argumentCount' => '2',
698 ],
699 'COVARIANCE.S' => [
700 'category' => Category::CATEGORY_STATISTICAL,
701 'functionCall' => [Functions::class, 'DUMMY'],
702 'argumentCount' => '2',
703 ],
704 'CRITBINOM' => [
705 'category' => Category::CATEGORY_STATISTICAL,
706 'functionCall' => [Statistical\Distributions\Binomial::class, 'inverse'],
707 'argumentCount' => '3',
708 ],
709 'CSC' => [
711 'functionCall' => [MathTrig\Trig\Cosecant::class, 'csc'],
712 'argumentCount' => '1',
713 ],
714 'CSCH' => [
716 'functionCall' => [MathTrig\Trig\Cosecant::class, 'csch'],
717 'argumentCount' => '1',
718 ],
719 'CUBEKPIMEMBER' => [
720 'category' => Category::CATEGORY_CUBE,
721 'functionCall' => [Functions::class, 'DUMMY'],
722 'argumentCount' => '?',
723 ],
724 'CUBEMEMBER' => [
725 'category' => Category::CATEGORY_CUBE,
726 'functionCall' => [Functions::class, 'DUMMY'],
727 'argumentCount' => '?',
728 ],
729 'CUBEMEMBERPROPERTY' => [
730 'category' => Category::CATEGORY_CUBE,
731 'functionCall' => [Functions::class, 'DUMMY'],
732 'argumentCount' => '?',
733 ],
734 'CUBERANKEDMEMBER' => [
735 'category' => Category::CATEGORY_CUBE,
736 'functionCall' => [Functions::class, 'DUMMY'],
737 'argumentCount' => '?',
738 ],
739 'CUBESET' => [
740 'category' => Category::CATEGORY_CUBE,
741 'functionCall' => [Functions::class, 'DUMMY'],
742 'argumentCount' => '?',
743 ],
744 'CUBESETCOUNT' => [
745 'category' => Category::CATEGORY_CUBE,
746 'functionCall' => [Functions::class, 'DUMMY'],
747 'argumentCount' => '?',
748 ],
749 'CUBEVALUE' => [
750 'category' => Category::CATEGORY_CUBE,
751 'functionCall' => [Functions::class, 'DUMMY'],
752 'argumentCount' => '?',
753 ],
754 'CUMIPMT' => [
755 'category' => Category::CATEGORY_FINANCIAL,
756 'functionCall' => [Financial\CashFlow\Constant\Periodic\Cumulative::class, 'interest'],
757 'argumentCount' => '6',
758 ],
759 'CUMPRINC' => [
760 'category' => Category::CATEGORY_FINANCIAL,
761 'functionCall' => [Financial\CashFlow\Constant\Periodic\Cumulative::class, 'principal'],
762 'argumentCount' => '6',
763 ],
764 'DATE' => [
766 'functionCall' => [DateTimeExcel\Date::class, 'fromYMD'],
767 'argumentCount' => '3',
768 ],
769 'DATEDIF' => [
771 'functionCall' => [DateTimeExcel\Difference::class, 'interval'],
772 'argumentCount' => '2,3',
773 ],
774 'DATESTRING' => [
776 'functionCall' => [Functions::class, 'DUMMY'],
777 'argumentCount' => '?',
778 ],
779 'DATEVALUE' => [
781 'functionCall' => [DateTimeExcel\DateValue::class, 'fromString'],
782 'argumentCount' => '1',
783 ],
784 'DAVERAGE' => [
785 'category' => Category::CATEGORY_DATABASE,
786 'functionCall' => [Database\DAverage::class, 'evaluate'],
787 'argumentCount' => '3',
788 ],
789 'DAY' => [
791 'functionCall' => [DateTimeExcel\DateParts::class, 'day'],
792 'argumentCount' => '1',
793 ],
794 'DAYS' => [
796 'functionCall' => [DateTimeExcel\Days::class, 'between'],
797 'argumentCount' => '2',
798 ],
799 'DAYS360' => [
801 'functionCall' => [DateTimeExcel\Days360::class, 'between'],
802 'argumentCount' => '2,3',
803 ],
804 'DB' => [
805 'category' => Category::CATEGORY_FINANCIAL,
806 'functionCall' => [Financial\Depreciation::class, 'DB'],
807 'argumentCount' => '4,5',
808 ],
809 'DBCS' => [
811 'functionCall' => [Functions::class, 'DUMMY'],
812 'argumentCount' => '1',
813 ],
814 'DCOUNT' => [
815 'category' => Category::CATEGORY_DATABASE,
816 'functionCall' => [Database\DCount::class, 'evaluate'],
817 'argumentCount' => '3',
818 ],
819 'DCOUNTA' => [
820 'category' => Category::CATEGORY_DATABASE,
821 'functionCall' => [Database\DCountA::class, 'evaluate'],
822 'argumentCount' => '3',
823 ],
824 'DDB' => [
825 'category' => Category::CATEGORY_FINANCIAL,
826 'functionCall' => [Financial\Depreciation::class, 'DDB'],
827 'argumentCount' => '4,5',
828 ],
829 'DEC2BIN' => [
830 'category' => Category::CATEGORY_ENGINEERING,
831 'functionCall' => [Engineering\ConvertDecimal::class, 'toBinary'],
832 'argumentCount' => '1,2',
833 ],
834 'DEC2HEX' => [
835 'category' => Category::CATEGORY_ENGINEERING,
836 'functionCall' => [Engineering\ConvertDecimal::class, 'toHex'],
837 'argumentCount' => '1,2',
838 ],
839 'DEC2OCT' => [
840 'category' => Category::CATEGORY_ENGINEERING,
841 'functionCall' => [Engineering\ConvertDecimal::class, 'toOctal'],
842 'argumentCount' => '1,2',
843 ],
844 'DECIMAL' => [
846 'functionCall' => [Functions::class, 'DUMMY'],
847 'argumentCount' => '2',
848 ],
849 'DEGREES' => [
851 'functionCall' => [MathTrig\Angle::class, 'toDegrees'],
852 'argumentCount' => '1',
853 ],
854 'DELTA' => [
855 'category' => Category::CATEGORY_ENGINEERING,
856 'functionCall' => [Engineering\Compare::class, 'DELTA'],
857 'argumentCount' => '1,2',
858 ],
859 'DEVSQ' => [
860 'category' => Category::CATEGORY_STATISTICAL,
861 'functionCall' => [Statistical\Deviations::class, 'sumSquares'],
862 'argumentCount' => '1+',
863 ],
864 'DGET' => [
865 'category' => Category::CATEGORY_DATABASE,
866 'functionCall' => [Database\DGet::class, 'evaluate'],
867 'argumentCount' => '3',
868 ],
869 'DISC' => [
870 'category' => Category::CATEGORY_FINANCIAL,
871 'functionCall' => [Financial\Securities\Rates::class, 'discount'],
872 'argumentCount' => '4,5',
873 ],
874 'DMAX' => [
875 'category' => Category::CATEGORY_DATABASE,
876 'functionCall' => [Database\DMax::class, 'evaluate'],
877 'argumentCount' => '3',
878 ],
879 'DMIN' => [
880 'category' => Category::CATEGORY_DATABASE,
881 'functionCall' => [Database\DMin::class, 'evaluate'],
882 'argumentCount' => '3',
883 ],
884 'DOLLAR' => [
886 'functionCall' => [TextData\Format::class, 'DOLLAR'],
887 'argumentCount' => '1,2',
888 ],
889 'DOLLARDE' => [
890 'category' => Category::CATEGORY_FINANCIAL,
891 'functionCall' => [Financial\Dollar::class, 'decimal'],
892 'argumentCount' => '2',
893 ],
894 'DOLLARFR' => [
895 'category' => Category::CATEGORY_FINANCIAL,
896 'functionCall' => [Financial\Dollar::class, 'fractional'],
897 'argumentCount' => '2',
898 ],
899 'DPRODUCT' => [
900 'category' => Category::CATEGORY_DATABASE,
901 'functionCall' => [Database\DProduct::class, 'evaluate'],
902 'argumentCount' => '3',
903 ],
904 'DSTDEV' => [
905 'category' => Category::CATEGORY_DATABASE,
906 'functionCall' => [Database\DStDev::class, 'evaluate'],
907 'argumentCount' => '3',
908 ],
909 'DSTDEVP' => [
910 'category' => Category::CATEGORY_DATABASE,
911 'functionCall' => [Database\DStDevP::class, 'evaluate'],
912 'argumentCount' => '3',
913 ],
914 'DSUM' => [
915 'category' => Category::CATEGORY_DATABASE,
916 'functionCall' => [Database\DSum::class, 'evaluate'],
917 'argumentCount' => '3',
918 ],
919 'DURATION' => [
920 'category' => Category::CATEGORY_FINANCIAL,
921 'functionCall' => [Functions::class, 'DUMMY'],
922 'argumentCount' => '5,6',
923 ],
924 'DVAR' => [
925 'category' => Category::CATEGORY_DATABASE,
926 'functionCall' => [Database\DVar::class, 'evaluate'],
927 'argumentCount' => '3',
928 ],
929 'DVARP' => [
930 'category' => Category::CATEGORY_DATABASE,
931 'functionCall' => [Database\DVarP::class, 'evaluate'],
932 'argumentCount' => '3',
933 ],
934 'ECMA.CEILING' => [
936 'functionCall' => [Functions::class, 'DUMMY'],
937 'argumentCount' => '1,2',
938 ],
939 'EDATE' => [
941 'functionCall' => [DateTimeExcel\Month::class, 'adjust'],
942 'argumentCount' => '2',
943 ],
944 'EFFECT' => [
945 'category' => Category::CATEGORY_FINANCIAL,
946 'functionCall' => [Financial\InterestRate::class, 'effective'],
947 'argumentCount' => '2',
948 ],
949 'ENCODEURL' => [
950 'category' => Category::CATEGORY_WEB,
951 'functionCall' => [Web\Service::class, 'urlEncode'],
952 'argumentCount' => '1',
953 ],
954 'EOMONTH' => [
956 'functionCall' => [DateTimeExcel\Month::class, 'lastDay'],
957 'argumentCount' => '2',
958 ],
959 'ERF' => [
960 'category' => Category::CATEGORY_ENGINEERING,
961 'functionCall' => [Engineering\Erf::class, 'ERF'],
962 'argumentCount' => '1,2',
963 ],
964 'ERF.PRECISE' => [
965 'category' => Category::CATEGORY_ENGINEERING,
966 'functionCall' => [Engineering\Erf::class, 'ERFPRECISE'],
967 'argumentCount' => '1',
968 ],
969 'ERFC' => [
970 'category' => Category::CATEGORY_ENGINEERING,
971 'functionCall' => [Engineering\ErfC::class, 'ERFC'],
972 'argumentCount' => '1',
973 ],
974 'ERFC.PRECISE' => [
975 'category' => Category::CATEGORY_ENGINEERING,
976 'functionCall' => [Engineering\ErfC::class, 'ERFC'],
977 'argumentCount' => '1',
978 ],
979 'ERROR.TYPE' => [
980 'category' => Category::CATEGORY_INFORMATION,
981 'functionCall' => [Functions::class, 'errorType'],
982 'argumentCount' => '1',
983 ],
984 'EVEN' => [
986 'functionCall' => [MathTrig\Round::class, 'even'],
987 'argumentCount' => '1',
988 ],
989 'EXACT' => [
991 'functionCall' => [TextData\Text::class, 'exact'],
992 'argumentCount' => '2',
993 ],
994 'EXP' => [
996 'functionCall' => [MathTrig\Exp::class, 'evaluate'],
997 'argumentCount' => '1',
998 ],
999 'EXPONDIST' => [
1000 'category' => Category::CATEGORY_STATISTICAL,
1001 'functionCall' => [Statistical\Distributions\Exponential::class, 'distribution'],
1002 'argumentCount' => '3',
1003 ],
1004 'EXPON.DIST' => [
1005 'category' => Category::CATEGORY_STATISTICAL,
1006 'functionCall' => [Statistical\Distributions\Exponential::class, 'distribution'],
1007 'argumentCount' => '3',
1008 ],
1009 'FACT' => [
1011 'functionCall' => [MathTrig\Factorial::class, 'fact'],
1012 'argumentCount' => '1',
1013 ],
1014 'FACTDOUBLE' => [
1016 'functionCall' => [MathTrig\Factorial::class, 'factDouble'],
1017 'argumentCount' => '1',
1018 ],
1019 'FALSE' => [
1020 'category' => Category::CATEGORY_LOGICAL,
1021 'functionCall' => [Logical\Boolean::class, 'FALSE'],
1022 'argumentCount' => '0',
1023 ],
1024 'FDIST' => [
1025 'category' => Category::CATEGORY_STATISTICAL,
1026 'functionCall' => [Functions::class, 'DUMMY'],
1027 'argumentCount' => '3',
1028 ],
1029 'F.DIST' => [
1030 'category' => Category::CATEGORY_STATISTICAL,
1031 'functionCall' => [Statistical\Distributions\F::class, 'distribution'],
1032 'argumentCount' => '4',
1033 ],
1034 'F.DIST.RT' => [
1035 'category' => Category::CATEGORY_STATISTICAL,
1036 'functionCall' => [Functions::class, 'DUMMY'],
1037 'argumentCount' => '3',
1038 ],
1039 'FILTER' => [
1041 'functionCall' => [Functions::class, 'DUMMY'],
1042 'argumentCount' => '3+',
1043 ],
1044 'FILTERXML' => [
1045 'category' => Category::CATEGORY_WEB,
1046 'functionCall' => [Functions::class, 'DUMMY'],
1047 'argumentCount' => '2',
1048 ],
1049 'FIND' => [
1051 'functionCall' => [TextData\Search::class, 'sensitive'],
1052 'argumentCount' => '2,3',
1053 ],
1054 'FINDB' => [
1056 'functionCall' => [TextData\Search::class, 'sensitive'],
1057 'argumentCount' => '2,3',
1058 ],
1059 'FINV' => [
1060 'category' => Category::CATEGORY_STATISTICAL,
1061 'functionCall' => [Functions::class, 'DUMMY'],
1062 'argumentCount' => '3',
1063 ],
1064 'F.INV' => [
1065 'category' => Category::CATEGORY_STATISTICAL,
1066 'functionCall' => [Functions::class, 'DUMMY'],
1067 'argumentCount' => '3',
1068 ],
1069 'F.INV.RT' => [
1070 'category' => Category::CATEGORY_STATISTICAL,
1071 'functionCall' => [Functions::class, 'DUMMY'],
1072 'argumentCount' => '3',
1073 ],
1074 'FISHER' => [
1075 'category' => Category::CATEGORY_STATISTICAL,
1076 'functionCall' => [Statistical\Distributions\Fisher::class, 'distribution'],
1077 'argumentCount' => '1',
1078 ],
1079 'FISHERINV' => [
1080 'category' => Category::CATEGORY_STATISTICAL,
1081 'functionCall' => [Statistical\Distributions\Fisher::class, 'inverse'],
1082 'argumentCount' => '1',
1083 ],
1084 'FIXED' => [
1086 'functionCall' => [TextData\Format::class, 'FIXEDFORMAT'],
1087 'argumentCount' => '1-3',
1088 ],
1089 'FLOOR' => [
1091 'functionCall' => [MathTrig\Floor::class, 'floor'],
1092 'argumentCount' => '1-2', // Excel requries 2, Ods/Gnumeric 1-2
1093 ],
1094 'FLOOR.MATH' => [
1096 'functionCall' => [MathTrig\Floor::class, 'math'],
1097 'argumentCount' => '1-3',
1098 ],
1099 'FLOOR.PRECISE' => [
1101 'functionCall' => [MathTrig\Floor::class, 'precise'],
1102 'argumentCount' => '1-2',
1103 ],
1104 'FORECAST' => [
1105 'category' => Category::CATEGORY_STATISTICAL,
1106 'functionCall' => [Statistical\Trends::class, 'FORECAST'],
1107 'argumentCount' => '3',
1108 ],
1109 'FORECAST.ETS' => [
1110 'category' => Category::CATEGORY_STATISTICAL,
1111 'functionCall' => [Functions::class, 'DUMMY'],
1112 'argumentCount' => '3-6',
1113 ],
1114 'FORECAST.ETS.CONFINT' => [
1115 'category' => Category::CATEGORY_STATISTICAL,
1116 'functionCall' => [Functions::class, 'DUMMY'],
1117 'argumentCount' => '3-6',
1118 ],
1119 'FORECAST.ETS.SEASONALITY' => [
1120 'category' => Category::CATEGORY_STATISTICAL,
1121 'functionCall' => [Functions::class, 'DUMMY'],
1122 'argumentCount' => '2-4',
1123 ],
1124 'FORECAST.ETS.STAT' => [
1125 'category' => Category::CATEGORY_STATISTICAL,
1126 'functionCall' => [Functions::class, 'DUMMY'],
1127 'argumentCount' => '3-6',
1128 ],
1129 'FORECAST.LINEAR' => [
1130 'category' => Category::CATEGORY_STATISTICAL,
1131 'functionCall' => [Statistical\Trends::class, 'FORECAST'],
1132 'argumentCount' => '3',
1133 ],
1134 'FORMULATEXT' => [
1136 'functionCall' => [LookupRef\Formula::class, 'text'],
1137 'argumentCount' => '1',
1138 'passCellReference' => true,
1139 'passByReference' => [true],
1140 ],
1141 'FREQUENCY' => [
1142 'category' => Category::CATEGORY_STATISTICAL,
1143 'functionCall' => [Functions::class, 'DUMMY'],
1144 'argumentCount' => '2',
1145 ],
1146 'FTEST' => [
1147 'category' => Category::CATEGORY_STATISTICAL,
1148 'functionCall' => [Functions::class, 'DUMMY'],
1149 'argumentCount' => '2',
1150 ],
1151 'F.TEST' => [
1152 'category' => Category::CATEGORY_STATISTICAL,
1153 'functionCall' => [Functions::class, 'DUMMY'],
1154 'argumentCount' => '2',
1155 ],
1156 'FV' => [
1157 'category' => Category::CATEGORY_FINANCIAL,
1158 'functionCall' => [Financial\CashFlow\Constant\Periodic::class, 'futureValue'],
1159 'argumentCount' => '3-5',
1160 ],
1161 'FVSCHEDULE' => [
1162 'category' => Category::CATEGORY_FINANCIAL,
1163 'functionCall' => [Financial\CashFlow\Single::class, 'futureValue'],
1164 'argumentCount' => '2',
1165 ],
1166 'GAMMA' => [
1167 'category' => Category::CATEGORY_STATISTICAL,
1168 'functionCall' => [Statistical\Distributions\Gamma::class, 'gamma'],
1169 'argumentCount' => '1',
1170 ],
1171 'GAMMADIST' => [
1172 'category' => Category::CATEGORY_STATISTICAL,
1173 'functionCall' => [Statistical\Distributions\Gamma::class, 'distribution'],
1174 'argumentCount' => '4',
1175 ],
1176 'GAMMA.DIST' => [
1177 'category' => Category::CATEGORY_STATISTICAL,
1178 'functionCall' => [Statistical\Distributions\Gamma::class, 'distribution'],
1179 'argumentCount' => '4',
1180 ],
1181 'GAMMAINV' => [
1182 'category' => Category::CATEGORY_STATISTICAL,
1183 'functionCall' => [Statistical\Distributions\Gamma::class, 'inverse'],
1184 'argumentCount' => '3',
1185 ],
1186 'GAMMA.INV' => [
1187 'category' => Category::CATEGORY_STATISTICAL,
1188 'functionCall' => [Statistical\Distributions\Gamma::class, 'inverse'],
1189 'argumentCount' => '3',
1190 ],
1191 'GAMMALN' => [
1192 'category' => Category::CATEGORY_STATISTICAL,
1193 'functionCall' => [Statistical\Distributions\Gamma::class, 'ln'],
1194 'argumentCount' => '1',
1195 ],
1196 'GAMMALN.PRECISE' => [
1197 'category' => Category::CATEGORY_STATISTICAL,
1198 'functionCall' => [Statistical\Distributions\Gamma::class, 'ln'],
1199 'argumentCount' => '1',
1200 ],
1201 'GAUSS' => [
1202 'category' => Category::CATEGORY_STATISTICAL,
1203 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'gauss'],
1204 'argumentCount' => '1',
1205 ],
1206 'GCD' => [
1208 'functionCall' => [MathTrig\Gcd::class, 'evaluate'],
1209 'argumentCount' => '1+',
1210 ],
1211 'GEOMEAN' => [
1212 'category' => Category::CATEGORY_STATISTICAL,
1213 'functionCall' => [Statistical\Averages\Mean::class, 'geometric'],
1214 'argumentCount' => '1+',
1215 ],
1216 'GESTEP' => [
1217 'category' => Category::CATEGORY_ENGINEERING,
1218 'functionCall' => [Engineering\Compare::class, 'GESTEP'],
1219 'argumentCount' => '1,2',
1220 ],
1221 'GETPIVOTDATA' => [
1223 'functionCall' => [Functions::class, 'DUMMY'],
1224 'argumentCount' => '2+',
1225 ],
1226 'GROWTH' => [
1227 'category' => Category::CATEGORY_STATISTICAL,
1228 'functionCall' => [Statistical\Trends::class, 'GROWTH'],
1229 'argumentCount' => '1-4',
1230 ],
1231 'HARMEAN' => [
1232 'category' => Category::CATEGORY_STATISTICAL,
1233 'functionCall' => [Statistical\Averages\Mean::class, 'harmonic'],
1234 'argumentCount' => '1+',
1235 ],
1236 'HEX2BIN' => [
1237 'category' => Category::CATEGORY_ENGINEERING,
1238 'functionCall' => [Engineering\ConvertHex::class, 'toBinary'],
1239 'argumentCount' => '1,2',
1240 ],
1241 'HEX2DEC' => [
1242 'category' => Category::CATEGORY_ENGINEERING,
1243 'functionCall' => [Engineering\ConvertHex::class, 'toDecimal'],
1244 'argumentCount' => '1',
1245 ],
1246 'HEX2OCT' => [
1247 'category' => Category::CATEGORY_ENGINEERING,
1248 'functionCall' => [Engineering\ConvertHex::class, 'toOctal'],
1249 'argumentCount' => '1,2',
1250 ],
1251 'HLOOKUP' => [
1253 'functionCall' => [LookupRef\HLookup::class, 'lookup'],
1254 'argumentCount' => '3,4',
1255 ],
1256 'HOUR' => [
1258 'functionCall' => [DateTimeExcel\TimeParts::class, 'hour'],
1259 'argumentCount' => '1',
1260 ],
1261 'HYPERLINK' => [
1263 'functionCall' => [LookupRef\Hyperlink::class, 'set'],
1264 'argumentCount' => '1,2',
1265 'passCellReference' => true,
1266 ],
1267 'HYPGEOMDIST' => [
1268 'category' => Category::CATEGORY_STATISTICAL,
1269 'functionCall' => [Statistical\Distributions\HyperGeometric::class, 'distribution'],
1270 'argumentCount' => '4',
1271 ],
1272 'HYPGEOM.DIST' => [
1273 'category' => Category::CATEGORY_STATISTICAL,
1274 'functionCall' => [Functions::class, 'DUMMY'],
1275 'argumentCount' => '5',
1276 ],
1277 'IF' => [
1278 'category' => Category::CATEGORY_LOGICAL,
1279 'functionCall' => [Logical\Conditional::class, 'statementIf'],
1280 'argumentCount' => '1-3',
1281 ],
1282 'IFERROR' => [
1283 'category' => Category::CATEGORY_LOGICAL,
1284 'functionCall' => [Logical\Conditional::class, 'IFERROR'],
1285 'argumentCount' => '2',
1286 ],
1287 'IFNA' => [
1288 'category' => Category::CATEGORY_LOGICAL,
1289 'functionCall' => [Logical\Conditional::class, 'IFNA'],
1290 'argumentCount' => '2',
1291 ],
1292 'IFS' => [
1293 'category' => Category::CATEGORY_LOGICAL,
1294 'functionCall' => [Logical\Conditional::class, 'IFS'],
1295 'argumentCount' => '2+',
1296 ],
1297 'IMABS' => [
1298 'category' => Category::CATEGORY_ENGINEERING,
1299 'functionCall' => [Engineering\ComplexFunctions::class, 'IMABS'],
1300 'argumentCount' => '1',
1301 ],
1302 'IMAGINARY' => [
1303 'category' => Category::CATEGORY_ENGINEERING,
1304 'functionCall' => [Engineering\Complex::class, 'IMAGINARY'],
1305 'argumentCount' => '1',
1306 ],
1307 'IMARGUMENT' => [
1308 'category' => Category::CATEGORY_ENGINEERING,
1309 'functionCall' => [Engineering\ComplexFunctions::class, 'IMARGUMENT'],
1310 'argumentCount' => '1',
1311 ],
1312 'IMCONJUGATE' => [
1313 'category' => Category::CATEGORY_ENGINEERING,
1314 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCONJUGATE'],
1315 'argumentCount' => '1',
1316 ],
1317 'IMCOS' => [
1318 'category' => Category::CATEGORY_ENGINEERING,
1319 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCOS'],
1320 'argumentCount' => '1',
1321 ],
1322 'IMCOSH' => [
1323 'category' => Category::CATEGORY_ENGINEERING,
1324 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCOSH'],
1325 'argumentCount' => '1',
1326 ],
1327 'IMCOT' => [
1328 'category' => Category::CATEGORY_ENGINEERING,
1329 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCOT'],
1330 'argumentCount' => '1',
1331 ],
1332 'IMCSC' => [
1333 'category' => Category::CATEGORY_ENGINEERING,
1334 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCSC'],
1335 'argumentCount' => '1',
1336 ],
1337 'IMCSCH' => [
1338 'category' => Category::CATEGORY_ENGINEERING,
1339 'functionCall' => [Engineering\ComplexFunctions::class, 'IMCSCH'],
1340 'argumentCount' => '1',
1341 ],
1342 'IMDIV' => [
1343 'category' => Category::CATEGORY_ENGINEERING,
1344 'functionCall' => [Engineering\ComplexOperations::class, 'IMDIV'],
1345 'argumentCount' => '2',
1346 ],
1347 'IMEXP' => [
1348 'category' => Category::CATEGORY_ENGINEERING,
1349 'functionCall' => [Engineering\ComplexFunctions::class, 'IMEXP'],
1350 'argumentCount' => '1',
1351 ],
1352 'IMLN' => [
1353 'category' => Category::CATEGORY_ENGINEERING,
1354 'functionCall' => [Engineering\ComplexFunctions::class, 'IMLN'],
1355 'argumentCount' => '1',
1356 ],
1357 'IMLOG10' => [
1358 'category' => Category::CATEGORY_ENGINEERING,
1359 'functionCall' => [Engineering\ComplexFunctions::class, 'IMLOG10'],
1360 'argumentCount' => '1',
1361 ],
1362 'IMLOG2' => [
1363 'category' => Category::CATEGORY_ENGINEERING,
1364 'functionCall' => [Engineering\ComplexFunctions::class, 'IMLOG2'],
1365 'argumentCount' => '1',
1366 ],
1367 'IMPOWER' => [
1368 'category' => Category::CATEGORY_ENGINEERING,
1369 'functionCall' => [Engineering\ComplexFunctions::class, 'IMPOWER'],
1370 'argumentCount' => '2',
1371 ],
1372 'IMPRODUCT' => [
1373 'category' => Category::CATEGORY_ENGINEERING,
1374 'functionCall' => [Engineering\ComplexOperations::class, 'IMPRODUCT'],
1375 'argumentCount' => '1+',
1376 ],
1377 'IMREAL' => [
1378 'category' => Category::CATEGORY_ENGINEERING,
1379 'functionCall' => [Engineering\Complex::class, 'IMREAL'],
1380 'argumentCount' => '1',
1381 ],
1382 'IMSEC' => [
1383 'category' => Category::CATEGORY_ENGINEERING,
1384 'functionCall' => [Engineering\ComplexFunctions::class, 'IMSEC'],
1385 'argumentCount' => '1',
1386 ],
1387 'IMSECH' => [
1388 'category' => Category::CATEGORY_ENGINEERING,
1389 'functionCall' => [Engineering\ComplexFunctions::class, 'IMSECH'],
1390 'argumentCount' => '1',
1391 ],
1392 'IMSIN' => [
1393 'category' => Category::CATEGORY_ENGINEERING,
1394 'functionCall' => [Engineering\ComplexFunctions::class, 'IMSIN'],
1395 'argumentCount' => '1',
1396 ],
1397 'IMSINH' => [
1398 'category' => Category::CATEGORY_ENGINEERING,
1399 'functionCall' => [Engineering\ComplexFunctions::class, 'IMSINH'],
1400 'argumentCount' => '1',
1401 ],
1402 'IMSQRT' => [
1403 'category' => Category::CATEGORY_ENGINEERING,
1404 'functionCall' => [Engineering\ComplexFunctions::class, 'IMSQRT'],
1405 'argumentCount' => '1',
1406 ],
1407 'IMSUB' => [
1408 'category' => Category::CATEGORY_ENGINEERING,
1409 'functionCall' => [Engineering\ComplexOperations::class, 'IMSUB'],
1410 'argumentCount' => '2',
1411 ],
1412 'IMSUM' => [
1413 'category' => Category::CATEGORY_ENGINEERING,
1414 'functionCall' => [Engineering\ComplexOperations::class, 'IMSUM'],
1415 'argumentCount' => '1+',
1416 ],
1417 'IMTAN' => [
1418 'category' => Category::CATEGORY_ENGINEERING,
1419 'functionCall' => [Engineering\ComplexFunctions::class, 'IMTAN'],
1420 'argumentCount' => '1',
1421 ],
1422 'INDEX' => [
1424 'functionCall' => [LookupRef\Matrix::class, 'index'],
1425 'argumentCount' => '1-4',
1426 ],
1427 'INDIRECT' => [
1429 'functionCall' => [LookupRef\Indirect::class, 'INDIRECT'],
1430 'argumentCount' => '1,2',
1431 'passCellReference' => true,
1432 ],
1433 'INFO' => [
1434 'category' => Category::CATEGORY_INFORMATION,
1435 'functionCall' => [Functions::class, 'DUMMY'],
1436 'argumentCount' => '1',
1437 ],
1438 'INT' => [
1440 'functionCall' => [MathTrig\IntClass::class, 'evaluate'],
1441 'argumentCount' => '1',
1442 ],
1443 'INTERCEPT' => [
1444 'category' => Category::CATEGORY_STATISTICAL,
1445 'functionCall' => [Statistical\Trends::class, 'INTERCEPT'],
1446 'argumentCount' => '2',
1447 ],
1448 'INTRATE' => [
1449 'category' => Category::CATEGORY_FINANCIAL,
1450 'functionCall' => [Financial\Securities\Rates::class, 'interest'],
1451 'argumentCount' => '4,5',
1452 ],
1453 'IPMT' => [
1454 'category' => Category::CATEGORY_FINANCIAL,
1455 'functionCall' => [Financial\CashFlow\Constant\Periodic\Interest::class, 'payment'],
1456 'argumentCount' => '4-6',
1457 ],
1458 'IRR' => [
1459 'category' => Category::CATEGORY_FINANCIAL,
1460 'functionCall' => [Financial\CashFlow\Variable\Periodic::class, 'rate'],
1461 'argumentCount' => '1,2',
1462 ],
1463 'ISBLANK' => [
1464 'category' => Category::CATEGORY_INFORMATION,
1465 'functionCall' => [Functions::class, 'isBlank'],
1466 'argumentCount' => '1',
1467 ],
1468 'ISERR' => [
1469 'category' => Category::CATEGORY_INFORMATION,
1470 'functionCall' => [Functions::class, 'isErr'],
1471 'argumentCount' => '1',
1472 ],
1473 'ISERROR' => [
1474 'category' => Category::CATEGORY_INFORMATION,
1475 'functionCall' => [Functions::class, 'isError'],
1476 'argumentCount' => '1',
1477 ],
1478 'ISEVEN' => [
1479 'category' => Category::CATEGORY_INFORMATION,
1480 'functionCall' => [Functions::class, 'isEven'],
1481 'argumentCount' => '1',
1482 ],
1483 'ISFORMULA' => [
1484 'category' => Category::CATEGORY_INFORMATION,
1485 'functionCall' => [Functions::class, 'isFormula'],
1486 'argumentCount' => '1',
1487 'passCellReference' => true,
1488 'passByReference' => [true],
1489 ],
1490 'ISLOGICAL' => [
1491 'category' => Category::CATEGORY_INFORMATION,
1492 'functionCall' => [Functions::class, 'isLogical'],
1493 'argumentCount' => '1',
1494 ],
1495 'ISNA' => [
1496 'category' => Category::CATEGORY_INFORMATION,
1497 'functionCall' => [Functions::class, 'isNa'],
1498 'argumentCount' => '1',
1499 ],
1500 'ISNONTEXT' => [
1501 'category' => Category::CATEGORY_INFORMATION,
1502 'functionCall' => [Functions::class, 'isNonText'],
1503 'argumentCount' => '1',
1504 ],
1505 'ISNUMBER' => [
1506 'category' => Category::CATEGORY_INFORMATION,
1507 'functionCall' => [Functions::class, 'isNumber'],
1508 'argumentCount' => '1',
1509 ],
1510 'ISO.CEILING' => [
1512 'functionCall' => [Functions::class, 'DUMMY'],
1513 'argumentCount' => '1,2',
1514 ],
1515 'ISODD' => [
1516 'category' => Category::CATEGORY_INFORMATION,
1517 'functionCall' => [Functions::class, 'isOdd'],
1518 'argumentCount' => '1',
1519 ],
1520 'ISOWEEKNUM' => [
1522 'functionCall' => [DateTimeExcel\Week::class, 'isoWeekNumber'],
1523 'argumentCount' => '1',
1524 ],
1525 'ISPMT' => [
1526 'category' => Category::CATEGORY_FINANCIAL,
1527 'functionCall' => [Financial\CashFlow\Constant\Periodic\Interest::class, 'schedulePayment'],
1528 'argumentCount' => '4',
1529 ],
1530 'ISREF' => [
1531 'category' => Category::CATEGORY_INFORMATION,
1532 'functionCall' => [Functions::class, 'DUMMY'],
1533 'argumentCount' => '1',
1534 ],
1535 'ISTEXT' => [
1536 'category' => Category::CATEGORY_INFORMATION,
1537 'functionCall' => [Functions::class, 'isText'],
1538 'argumentCount' => '1',
1539 ],
1540 'ISTHAIDIGIT' => [
1542 'functionCall' => [Functions::class, 'DUMMY'],
1543 'argumentCount' => '?',
1544 ],
1545 'JIS' => [
1547 'functionCall' => [Functions::class, 'DUMMY'],
1548 'argumentCount' => '1',
1549 ],
1550 'KURT' => [
1551 'category' => Category::CATEGORY_STATISTICAL,
1552 'functionCall' => [Statistical\Deviations::class, 'kurtosis'],
1553 'argumentCount' => '1+',
1554 ],
1555 'LARGE' => [
1556 'category' => Category::CATEGORY_STATISTICAL,
1557 'functionCall' => [Statistical\Size::class, 'large'],
1558 'argumentCount' => '2',
1559 ],
1560 'LCM' => [
1562 'functionCall' => [MathTrig\Lcm::class, 'evaluate'],
1563 'argumentCount' => '1+',
1564 ],
1565 'LEFT' => [
1567 'functionCall' => [TextData\Extract::class, 'left'],
1568 'argumentCount' => '1,2',
1569 ],
1570 'LEFTB' => [
1572 'functionCall' => [TextData\Extract::class, 'left'],
1573 'argumentCount' => '1,2',
1574 ],
1575 'LEN' => [
1577 'functionCall' => [TextData\Text::class, 'length'],
1578 'argumentCount' => '1',
1579 ],
1580 'LENB' => [
1582 'functionCall' => [TextData\Text::class, 'length'],
1583 'argumentCount' => '1',
1584 ],
1585 'LINEST' => [
1586 'category' => Category::CATEGORY_STATISTICAL,
1587 'functionCall' => [Statistical\Trends::class, 'LINEST'],
1588 'argumentCount' => '1-4',
1589 ],
1590 'LN' => [
1592 'functionCall' => [MathTrig\Logarithms::class, 'natural'],
1593 'argumentCount' => '1',
1594 ],
1595 'LOG' => [
1597 'functionCall' => [MathTrig\Logarithms::class, 'withBase'],
1598 'argumentCount' => '1,2',
1599 ],
1600 'LOG10' => [
1602 'functionCall' => [MathTrig\Logarithms::class, 'base10'],
1603 'argumentCount' => '1',
1604 ],
1605 'LOGEST' => [
1606 'category' => Category::CATEGORY_STATISTICAL,
1607 'functionCall' => [Statistical\Trends::class, 'LOGEST'],
1608 'argumentCount' => '1-4',
1609 ],
1610 'LOGINV' => [
1611 'category' => Category::CATEGORY_STATISTICAL,
1612 'functionCall' => [Statistical\Distributions\LogNormal::class, 'inverse'],
1613 'argumentCount' => '3',
1614 ],
1615 'LOGNORMDIST' => [
1616 'category' => Category::CATEGORY_STATISTICAL,
1617 'functionCall' => [Statistical\Distributions\LogNormal::class, 'cumulative'],
1618 'argumentCount' => '3',
1619 ],
1620 'LOGNORM.DIST' => [
1621 'category' => Category::CATEGORY_STATISTICAL,
1622 'functionCall' => [Statistical\Distributions\LogNormal::class, 'distribution'],
1623 'argumentCount' => '4',
1624 ],
1625 'LOGNORM.INV' => [
1626 'category' => Category::CATEGORY_STATISTICAL,
1627 'functionCall' => [Statistical\Distributions\LogNormal::class, 'inverse'],
1628 'argumentCount' => '3',
1629 ],
1630 'LOOKUP' => [
1632 'functionCall' => [LookupRef\Lookup::class, 'lookup'],
1633 'argumentCount' => '2,3',
1634 ],
1635 'LOWER' => [
1637 'functionCall' => [TextData\CaseConvert::class, 'lower'],
1638 'argumentCount' => '1',
1639 ],
1640 'MATCH' => [
1642 'functionCall' => [LookupRef\ExcelMatch::class, 'MATCH'],
1643 'argumentCount' => '2,3',
1644 ],
1645 'MAX' => [
1646 'category' => Category::CATEGORY_STATISTICAL,
1647 'functionCall' => [Statistical\Maximum::class, 'max'],
1648 'argumentCount' => '1+',
1649 ],
1650 'MAXA' => [
1651 'category' => Category::CATEGORY_STATISTICAL,
1652 'functionCall' => [Statistical\Maximum::class, 'maxA'],
1653 'argumentCount' => '1+',
1654 ],
1655 'MAXIFS' => [
1656 'category' => Category::CATEGORY_STATISTICAL,
1657 'functionCall' => [Statistical\Conditional::class, 'MAXIFS'],
1658 'argumentCount' => '3+',
1659 ],
1660 'MDETERM' => [
1662 'functionCall' => [MathTrig\MatrixFunctions::class, 'determinant'],
1663 'argumentCount' => '1',
1664 ],
1665 'MDURATION' => [
1666 'category' => Category::CATEGORY_FINANCIAL,
1667 'functionCall' => [Functions::class, 'DUMMY'],
1668 'argumentCount' => '5,6',
1669 ],
1670 'MEDIAN' => [
1671 'category' => Category::CATEGORY_STATISTICAL,
1672 'functionCall' => [Statistical\Averages::class, 'median'],
1673 'argumentCount' => '1+',
1674 ],
1675 'MEDIANIF' => [
1676 'category' => Category::CATEGORY_STATISTICAL,
1677 'functionCall' => [Functions::class, 'DUMMY'],
1678 'argumentCount' => '2+',
1679 ],
1680 'MID' => [
1682 'functionCall' => [TextData\Extract::class, 'mid'],
1683 'argumentCount' => '3',
1684 ],
1685 'MIDB' => [
1687 'functionCall' => [TextData\Extract::class, 'mid'],
1688 'argumentCount' => '3',
1689 ],
1690 'MIN' => [
1691 'category' => Category::CATEGORY_STATISTICAL,
1692 'functionCall' => [Statistical\Minimum::class, 'min'],
1693 'argumentCount' => '1+',
1694 ],
1695 'MINA' => [
1696 'category' => Category::CATEGORY_STATISTICAL,
1697 'functionCall' => [Statistical\Minimum::class, 'minA'],
1698 'argumentCount' => '1+',
1699 ],
1700 'MINIFS' => [
1701 'category' => Category::CATEGORY_STATISTICAL,
1702 'functionCall' => [Statistical\Conditional::class, 'MINIFS'],
1703 'argumentCount' => '3+',
1704 ],
1705 'MINUTE' => [
1707 'functionCall' => [DateTimeExcel\TimeParts::class, 'minute'],
1708 'argumentCount' => '1',
1709 ],
1710 'MINVERSE' => [
1712 'functionCall' => [MathTrig\MatrixFunctions::class, 'inverse'],
1713 'argumentCount' => '1',
1714 ],
1715 'MIRR' => [
1716 'category' => Category::CATEGORY_FINANCIAL,
1717 'functionCall' => [Financial\CashFlow\Variable\Periodic::class, 'modifiedRate'],
1718 'argumentCount' => '3',
1719 ],
1720 'MMULT' => [
1722 'functionCall' => [MathTrig\MatrixFunctions::class, 'multiply'],
1723 'argumentCount' => '2',
1724 ],
1725 'MOD' => [
1727 'functionCall' => [MathTrig\Operations::class, 'mod'],
1728 'argumentCount' => '2',
1729 ],
1730 'MODE' => [
1731 'category' => Category::CATEGORY_STATISTICAL,
1732 'functionCall' => [Statistical\Averages::class, 'mode'],
1733 'argumentCount' => '1+',
1734 ],
1735 'MODE.MULT' => [
1736 'category' => Category::CATEGORY_STATISTICAL,
1737 'functionCall' => [Functions::class, 'DUMMY'],
1738 'argumentCount' => '1+',
1739 ],
1740 'MODE.SNGL' => [
1741 'category' => Category::CATEGORY_STATISTICAL,
1742 'functionCall' => [Statistical\Averages::class, 'mode'],
1743 'argumentCount' => '1+',
1744 ],
1745 'MONTH' => [
1747 'functionCall' => [DateTimeExcel\DateParts::class, 'month'],
1748 'argumentCount' => '1',
1749 ],
1750 'MROUND' => [
1752 'functionCall' => [MathTrig\Round::class, 'multiple'],
1753 'argumentCount' => '2',
1754 ],
1755 'MULTINOMIAL' => [
1757 'functionCall' => [MathTrig\Factorial::class, 'multinomial'],
1758 'argumentCount' => '1+',
1759 ],
1760 'MUNIT' => [
1762 'functionCall' => [MathTrig\MatrixFunctions::class, 'identity'],
1763 'argumentCount' => '1',
1764 ],
1765 'N' => [
1766 'category' => Category::CATEGORY_INFORMATION,
1767 'functionCall' => [Functions::class, 'n'],
1768 'argumentCount' => '1',
1769 ],
1770 'NA' => [
1771 'category' => Category::CATEGORY_INFORMATION,
1772 'functionCall' => [Functions::class, 'NA'],
1773 'argumentCount' => '0',
1774 ],
1775 'NEGBINOMDIST' => [
1776 'category' => Category::CATEGORY_STATISTICAL,
1777 'functionCall' => [Statistical\Distributions\Binomial::class, 'negative'],
1778 'argumentCount' => '3',
1779 ],
1780 'NEGBINOM.DIST' => [
1781 'category' => Category::CATEGORY_STATISTICAL,
1782 'functionCall' => [Functions::class, 'DUMMY'],
1783 'argumentCount' => '4',
1784 ],
1785 'NETWORKDAYS' => [
1787 'functionCall' => [DateTimeExcel\NetworkDays::class, 'count'],
1788 'argumentCount' => '2-3',
1789 ],
1790 'NETWORKDAYS.INTL' => [
1792 'functionCall' => [Functions::class, 'DUMMY'],
1793 'argumentCount' => '2-4',
1794 ],
1795 'NOMINAL' => [
1796 'category' => Category::CATEGORY_FINANCIAL,
1797 'functionCall' => [Financial\InterestRate::class, 'nominal'],
1798 'argumentCount' => '2',
1799 ],
1800 'NORMDIST' => [
1801 'category' => Category::CATEGORY_STATISTICAL,
1802 'functionCall' => [Statistical\Distributions\Normal::class, 'distribution'],
1803 'argumentCount' => '4',
1804 ],
1805 'NORM.DIST' => [
1806 'category' => Category::CATEGORY_STATISTICAL,
1807 'functionCall' => [Statistical\Distributions\Normal::class, 'distribution'],
1808 'argumentCount' => '4',
1809 ],
1810 'NORMINV' => [
1811 'category' => Category::CATEGORY_STATISTICAL,
1812 'functionCall' => [Statistical\Distributions\Normal::class, 'inverse'],
1813 'argumentCount' => '3',
1814 ],
1815 'NORM.INV' => [
1816 'category' => Category::CATEGORY_STATISTICAL,
1817 'functionCall' => [Statistical\Distributions\Normal::class, 'inverse'],
1818 'argumentCount' => '3',
1819 ],
1820 'NORMSDIST' => [
1821 'category' => Category::CATEGORY_STATISTICAL,
1822 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'cumulative'],
1823 'argumentCount' => '1',
1824 ],
1825 'NORM.S.DIST' => [
1826 'category' => Category::CATEGORY_STATISTICAL,
1827 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'distribution'],
1828 'argumentCount' => '1,2',
1829 ],
1830 'NORMSINV' => [
1831 'category' => Category::CATEGORY_STATISTICAL,
1832 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'inverse'],
1833 'argumentCount' => '1',
1834 ],
1835 'NORM.S.INV' => [
1836 'category' => Category::CATEGORY_STATISTICAL,
1837 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'inverse'],
1838 'argumentCount' => '1',
1839 ],
1840 'NOT' => [
1841 'category' => Category::CATEGORY_LOGICAL,
1842 'functionCall' => [Logical\Operations::class, 'NOT'],
1843 'argumentCount' => '1',
1844 ],
1845 'NOW' => [
1847 'functionCall' => [DateTimeExcel\Current::class, 'now'],
1848 'argumentCount' => '0',
1849 ],
1850 'NPER' => [
1851 'category' => Category::CATEGORY_FINANCIAL,
1852 'functionCall' => [Financial\CashFlow\Constant\Periodic::class, 'periods'],
1853 'argumentCount' => '3-5',
1854 ],
1855 'NPV' => [
1856 'category' => Category::CATEGORY_FINANCIAL,
1857 'functionCall' => [Financial\CashFlow\Variable\Periodic::class, 'presentValue'],
1858 'argumentCount' => '2+',
1859 ],
1860 'NUMBERSTRING' => [
1862 'functionCall' => [Functions::class, 'DUMMY'],
1863 'argumentCount' => '?',
1864 ],
1865 'NUMBERVALUE' => [
1867 'functionCall' => [TextData\Format::class, 'NUMBERVALUE'],
1868 'argumentCount' => '1+',
1869 ],
1870 'OCT2BIN' => [
1871 'category' => Category::CATEGORY_ENGINEERING,
1872 'functionCall' => [Engineering\ConvertOctal::class, 'toBinary'],
1873 'argumentCount' => '1,2',
1874 ],
1875 'OCT2DEC' => [
1876 'category' => Category::CATEGORY_ENGINEERING,
1877 'functionCall' => [Engineering\ConvertOctal::class, 'toDecimal'],
1878 'argumentCount' => '1',
1879 ],
1880 'OCT2HEX' => [
1881 'category' => Category::CATEGORY_ENGINEERING,
1882 'functionCall' => [Engineering\ConvertOctal::class, 'toHex'],
1883 'argumentCount' => '1,2',
1884 ],
1885 'ODD' => [
1887 'functionCall' => [MathTrig\Round::class, 'odd'],
1888 'argumentCount' => '1',
1889 ],
1890 'ODDFPRICE' => [
1891 'category' => Category::CATEGORY_FINANCIAL,
1892 'functionCall' => [Functions::class, 'DUMMY'],
1893 'argumentCount' => '8,9',
1894 ],
1895 'ODDFYIELD' => [
1896 'category' => Category::CATEGORY_FINANCIAL,
1897 'functionCall' => [Functions::class, 'DUMMY'],
1898 'argumentCount' => '8,9',
1899 ],
1900 'ODDLPRICE' => [
1901 'category' => Category::CATEGORY_FINANCIAL,
1902 'functionCall' => [Functions::class, 'DUMMY'],
1903 'argumentCount' => '7,8',
1904 ],
1905 'ODDLYIELD' => [
1906 'category' => Category::CATEGORY_FINANCIAL,
1907 'functionCall' => [Functions::class, 'DUMMY'],
1908 'argumentCount' => '7,8',
1909 ],
1910 'OFFSET' => [
1912 'functionCall' => [LookupRef\Offset::class, 'OFFSET'],
1913 'argumentCount' => '3-5',
1914 'passCellReference' => true,
1915 'passByReference' => [true],
1916 ],
1917 'OR' => [
1918 'category' => Category::CATEGORY_LOGICAL,
1919 'functionCall' => [Logical\Operations::class, 'logicalOr'],
1920 'argumentCount' => '1+',
1921 ],
1922 'PDURATION' => [
1923 'category' => Category::CATEGORY_FINANCIAL,
1924 'functionCall' => [Financial\CashFlow\Single::class, 'periods'],
1925 'argumentCount' => '3',
1926 ],
1927 'PEARSON' => [
1928 'category' => Category::CATEGORY_STATISTICAL,
1929 'functionCall' => [Statistical\Trends::class, 'CORREL'],
1930 'argumentCount' => '2',
1931 ],
1932 'PERCENTILE' => [
1933 'category' => Category::CATEGORY_STATISTICAL,
1934 'functionCall' => [Statistical\Percentiles::class, 'PERCENTILE'],
1935 'argumentCount' => '2',
1936 ],
1937 'PERCENTILE.EXC' => [
1938 'category' => Category::CATEGORY_STATISTICAL,
1939 'functionCall' => [Functions::class, 'DUMMY'],
1940 'argumentCount' => '2',
1941 ],
1942 'PERCENTILE.INC' => [
1943 'category' => Category::CATEGORY_STATISTICAL,
1944 'functionCall' => [Statistical\Percentiles::class, 'PERCENTILE'],
1945 'argumentCount' => '2',
1946 ],
1947 'PERCENTRANK' => [
1948 'category' => Category::CATEGORY_STATISTICAL,
1949 'functionCall' => [Statistical\Percentiles::class, 'PERCENTRANK'],
1950 'argumentCount' => '2,3',
1951 ],
1952 'PERCENTRANK.EXC' => [
1953 'category' => Category::CATEGORY_STATISTICAL,
1954 'functionCall' => [Functions::class, 'DUMMY'],
1955 'argumentCount' => '2,3',
1956 ],
1957 'PERCENTRANK.INC' => [
1958 'category' => Category::CATEGORY_STATISTICAL,
1959 'functionCall' => [Statistical\Percentiles::class, 'PERCENTRANK'],
1960 'argumentCount' => '2,3',
1961 ],
1962 'PERMUT' => [
1963 'category' => Category::CATEGORY_STATISTICAL,
1964 'functionCall' => [Statistical\Permutations::class, 'PERMUT'],
1965 'argumentCount' => '2',
1966 ],
1967 'PERMUTATIONA' => [
1968 'category' => Category::CATEGORY_STATISTICAL,
1969 'functionCall' => [Statistical\Permutations::class, 'PERMUTATIONA'],
1970 'argumentCount' => '2',
1971 ],
1972 'PHONETIC' => [
1974 'functionCall' => [Functions::class, 'DUMMY'],
1975 'argumentCount' => '1',
1976 ],
1977 'PHI' => [
1978 'category' => Category::CATEGORY_STATISTICAL,
1979 'functionCall' => [Functions::class, 'DUMMY'],
1980 'argumentCount' => '1',
1981 ],
1982 'PI' => [
1984 'functionCall' => 'pi',
1985 'argumentCount' => '0',
1986 ],
1987 'PMT' => [
1988 'category' => Category::CATEGORY_FINANCIAL,
1989 'functionCall' => [Financial\CashFlow\Constant\Periodic\Payments::class, 'annuity'],
1990 'argumentCount' => '3-5',
1991 ],
1992 'POISSON' => [
1993 'category' => Category::CATEGORY_STATISTICAL,
1994 'functionCall' => [Statistical\Distributions\Poisson::class, 'distribution'],
1995 'argumentCount' => '3',
1996 ],
1997 'POISSON.DIST' => [
1998 'category' => Category::CATEGORY_STATISTICAL,
1999 'functionCall' => [Statistical\Distributions\Poisson::class, 'distribution'],
2000 'argumentCount' => '3',
2001 ],
2002 'POWER' => [
2004 'functionCall' => [MathTrig\Operations::class, 'power'],
2005 'argumentCount' => '2',
2006 ],
2007 'PPMT' => [
2008 'category' => Category::CATEGORY_FINANCIAL,
2009 'functionCall' => [Financial\CashFlow\Constant\Periodic\Payments::class, 'interestPayment'],
2010 'argumentCount' => '4-6',
2011 ],
2012 'PRICE' => [
2013 'category' => Category::CATEGORY_FINANCIAL,
2014 'functionCall' => [Financial\Securities\Price::class, 'price'],
2015 'argumentCount' => '6,7',
2016 ],
2017 'PRICEDISC' => [
2018 'category' => Category::CATEGORY_FINANCIAL,
2019 'functionCall' => [Financial\Securities\Price::class, 'priceDiscounted'],
2020 'argumentCount' => '4,5',
2021 ],
2022 'PRICEMAT' => [
2023 'category' => Category::CATEGORY_FINANCIAL,
2024 'functionCall' => [Financial\Securities\Price::class, 'priceAtMaturity'],
2025 'argumentCount' => '5,6',
2026 ],
2027 'PROB' => [
2028 'category' => Category::CATEGORY_STATISTICAL,
2029 'functionCall' => [Functions::class, 'DUMMY'],
2030 'argumentCount' => '3,4',
2031 ],
2032 'PRODUCT' => [
2034 'functionCall' => [MathTrig\Operations::class, 'product'],
2035 'argumentCount' => '1+',
2036 ],
2037 'PROPER' => [
2039 'functionCall' => [TextData\CaseConvert::class, 'proper'],
2040 'argumentCount' => '1',
2041 ],
2042 'PV' => [
2043 'category' => Category::CATEGORY_FINANCIAL,
2044 'functionCall' => [Financial\CashFlow\Constant\Periodic::class, 'presentValue'],
2045 'argumentCount' => '3-5',
2046 ],
2047 'QUARTILE' => [
2048 'category' => Category::CATEGORY_STATISTICAL,
2049 'functionCall' => [Statistical\Percentiles::class, 'QUARTILE'],
2050 'argumentCount' => '2',
2051 ],
2052 'QUARTILE.EXC' => [
2053 'category' => Category::CATEGORY_STATISTICAL,
2054 'functionCall' => [Functions::class, 'DUMMY'],
2055 'argumentCount' => '2',
2056 ],
2057 'QUARTILE.INC' => [
2058 'category' => Category::CATEGORY_STATISTICAL,
2059 'functionCall' => [Statistical\Percentiles::class, 'QUARTILE'],
2060 'argumentCount' => '2',
2061 ],
2062 'QUOTIENT' => [
2064 'functionCall' => [MathTrig\Operations::class, 'quotient'],
2065 'argumentCount' => '2',
2066 ],
2067 'RADIANS' => [
2069 'functionCall' => [MathTrig\Angle::class, 'toRadians'],
2070 'argumentCount' => '1',
2071 ],
2072 'RAND' => [
2074 'functionCall' => [MathTrig\Random::class, 'rand'],
2075 'argumentCount' => '0',
2076 ],
2077 'RANDARRAY' => [
2079 'functionCall' => [Functions::class, 'DUMMY'],
2080 'argumentCount' => '0-5',
2081 ],
2082 'RANDBETWEEN' => [
2084 'functionCall' => [MathTrig\Random::class, 'randBetween'],
2085 'argumentCount' => '2',
2086 ],
2087 'RANK' => [
2088 'category' => Category::CATEGORY_STATISTICAL,
2089 'functionCall' => [Statistical\Percentiles::class, 'RANK'],
2090 'argumentCount' => '2,3',
2091 ],
2092 'RANK.AVG' => [
2093 'category' => Category::CATEGORY_STATISTICAL,
2094 'functionCall' => [Functions::class, 'DUMMY'],
2095 'argumentCount' => '2,3',
2096 ],
2097 'RANK.EQ' => [
2098 'category' => Category::CATEGORY_STATISTICAL,
2099 'functionCall' => [Statistical\Percentiles::class, 'RANK'],
2100 'argumentCount' => '2,3',
2101 ],
2102 'RATE' => [
2103 'category' => Category::CATEGORY_FINANCIAL,
2104 'functionCall' => [Financial\CashFlow\Constant\Periodic\Interest::class, 'rate'],
2105 'argumentCount' => '3-6',
2106 ],
2107 'RECEIVED' => [
2108 'category' => Category::CATEGORY_FINANCIAL,
2109 'functionCall' => [Financial\Securities\Price::class, 'received'],
2110 'argumentCount' => '4-5',
2111 ],
2112 'REPLACE' => [
2114 'functionCall' => [TextData\Replace::class, 'replace'],
2115 'argumentCount' => '4',
2116 ],
2117 'REPLACEB' => [
2119 'functionCall' => [TextData\Replace::class, 'replace'],
2120 'argumentCount' => '4',
2121 ],
2122 'REPT' => [
2124 'functionCall' => [TextData\Concatenate::class, 'builtinREPT'],
2125 'argumentCount' => '2',
2126 ],
2127 'RIGHT' => [
2129 'functionCall' => [TextData\Extract::class, 'right'],
2130 'argumentCount' => '1,2',
2131 ],
2132 'RIGHTB' => [
2134 'functionCall' => [TextData\Extract::class, 'right'],
2135 'argumentCount' => '1,2',
2136 ],
2137 'ROMAN' => [
2139 'functionCall' => [MathTrig\Roman::class, 'evaluate'],
2140 'argumentCount' => '1,2',
2141 ],
2142 'ROUND' => [
2144 'functionCall' => [MathTrig\Round::class, 'round'],
2145 'argumentCount' => '2',
2146 ],
2147 'ROUNDBAHTDOWN' => [
2149 'functionCall' => [Functions::class, 'DUMMY'],
2150 'argumentCount' => '?',
2151 ],
2152 'ROUNDBAHTUP' => [
2154 'functionCall' => [Functions::class, 'DUMMY'],
2155 'argumentCount' => '?',
2156 ],
2157 'ROUNDDOWN' => [
2159 'functionCall' => [MathTrig\Round::class, 'down'],
2160 'argumentCount' => '2',
2161 ],
2162 'ROUNDUP' => [
2164 'functionCall' => [MathTrig\Round::class, 'up'],
2165 'argumentCount' => '2',
2166 ],
2167 'ROW' => [
2169 'functionCall' => [LookupRef\RowColumnInformation::class, 'ROW'],
2170 'argumentCount' => '-1',
2171 'passCellReference' => true,
2172 'passByReference' => [true],
2173 ],
2174 'ROWS' => [
2176 'functionCall' => [LookupRef\RowColumnInformation::class, 'ROWS'],
2177 'argumentCount' => '1',
2178 ],
2179 'RRI' => [
2180 'category' => Category::CATEGORY_FINANCIAL,
2181 'functionCall' => [Financial\CashFlow\Single::class, 'interestRate'],
2182 'argumentCount' => '3',
2183 ],
2184 'RSQ' => [
2185 'category' => Category::CATEGORY_STATISTICAL,
2186 'functionCall' => [Statistical\Trends::class, 'RSQ'],
2187 'argumentCount' => '2',
2188 ],
2189 'RTD' => [
2191 'functionCall' => [Functions::class, 'DUMMY'],
2192 'argumentCount' => '1+',
2193 ],
2194 'SEARCH' => [
2196 'functionCall' => [TextData\Search::class, 'insensitive'],
2197 'argumentCount' => '2,3',
2198 ],
2199 'SEARCHB' => [
2201 'functionCall' => [TextData\Search::class, 'insensitive'],
2202 'argumentCount' => '2,3',
2203 ],
2204 'SEC' => [
2206 'functionCall' => [MathTrig\Trig\Secant::class, 'sec'],
2207 'argumentCount' => '1',
2208 ],
2209 'SECH' => [
2211 'functionCall' => [MathTrig\Trig\Secant::class, 'sech'],
2212 'argumentCount' => '1',
2213 ],
2214 'SECOND' => [
2216 'functionCall' => [DateTimeExcel\TimeParts::class, 'second'],
2217 'argumentCount' => '1',
2218 ],
2219 'SEQUENCE' => [
2221 'functionCall' => [Functions::class, 'DUMMY'],
2222 'argumentCount' => '2',
2223 ],
2224 'SERIESSUM' => [
2226 'functionCall' => [MathTrig\SeriesSum::class, 'evaluate'],
2227 'argumentCount' => '4',
2228 ],
2229 'SHEET' => [
2230 'category' => Category::CATEGORY_INFORMATION,
2231 'functionCall' => [Functions::class, 'DUMMY'],
2232 'argumentCount' => '0,1',
2233 ],
2234 'SHEETS' => [
2235 'category' => Category::CATEGORY_INFORMATION,
2236 'functionCall' => [Functions::class, 'DUMMY'],
2237 'argumentCount' => '0,1',
2238 ],
2239 'SIGN' => [
2241 'functionCall' => [MathTrig\Sign::class, 'evaluate'],
2242 'argumentCount' => '1',
2243 ],
2244 'SIN' => [
2246 'functionCall' => [MathTrig\Trig\Sine::class, 'sin'],
2247 'argumentCount' => '1',
2248 ],
2249 'SINH' => [
2251 'functionCall' => [MathTrig\Trig\Sine::class, 'sinh'],
2252 'argumentCount' => '1',
2253 ],
2254 'SKEW' => [
2255 'category' => Category::CATEGORY_STATISTICAL,
2256 'functionCall' => [Statistical\Deviations::class, 'skew'],
2257 'argumentCount' => '1+',
2258 ],
2259 'SKEW.P' => [
2260 'category' => Category::CATEGORY_STATISTICAL,
2261 'functionCall' => [Functions::class, 'DUMMY'],
2262 'argumentCount' => '1+',
2263 ],
2264 'SLN' => [
2265 'category' => Category::CATEGORY_FINANCIAL,
2266 'functionCall' => [Financial\Depreciation::class, 'SLN'],
2267 'argumentCount' => '3',
2268 ],
2269 'SLOPE' => [
2270 'category' => Category::CATEGORY_STATISTICAL,
2271 'functionCall' => [Statistical\Trends::class, 'SLOPE'],
2272 'argumentCount' => '2',
2273 ],
2274 'SMALL' => [
2275 'category' => Category::CATEGORY_STATISTICAL,
2276 'functionCall' => [Statistical\Size::class, 'small'],
2277 'argumentCount' => '2',
2278 ],
2279 'SORT' => [
2281 'functionCall' => [Functions::class, 'DUMMY'],
2282 'argumentCount' => '1+',
2283 ],
2284 'SORTBY' => [
2286 'functionCall' => [Functions::class, 'DUMMY'],
2287 'argumentCount' => '2+',
2288 ],
2289 'SQRT' => [
2291 'functionCall' => [MathTrig\Sqrt::class, 'sqrt'],
2292 'argumentCount' => '1',
2293 ],
2294 'SQRTPI' => [
2296 'functionCall' => [MathTrig\Sqrt::class, 'pi'],
2297 'argumentCount' => '1',
2298 ],
2299 'STANDARDIZE' => [
2300 'category' => Category::CATEGORY_STATISTICAL,
2301 'functionCall' => [Statistical\Standardize::class, 'execute'],
2302 'argumentCount' => '3',
2303 ],
2304 'STDEV' => [
2305 'category' => Category::CATEGORY_STATISTICAL,
2306 'functionCall' => [Statistical\StandardDeviations::class, 'STDEV'],
2307 'argumentCount' => '1+',
2308 ],
2309 'STDEV.S' => [
2310 'category' => Category::CATEGORY_STATISTICAL,
2311 'functionCall' => [Statistical\StandardDeviations::class, 'STDEV'],
2312 'argumentCount' => '1+',
2313 ],
2314 'STDEV.P' => [
2315 'category' => Category::CATEGORY_STATISTICAL,
2316 'functionCall' => [Statistical\StandardDeviations::class, 'STDEVP'],
2317 'argumentCount' => '1+',
2318 ],
2319 'STDEVA' => [
2320 'category' => Category::CATEGORY_STATISTICAL,
2321 'functionCall' => [Statistical\StandardDeviations::class, 'STDEVA'],
2322 'argumentCount' => '1+',
2323 ],
2324 'STDEVP' => [
2325 'category' => Category::CATEGORY_STATISTICAL,
2326 'functionCall' => [Statistical\StandardDeviations::class, 'STDEVP'],
2327 'argumentCount' => '1+',
2328 ],
2329 'STDEVPA' => [
2330 'category' => Category::CATEGORY_STATISTICAL,
2331 'functionCall' => [Statistical\StandardDeviations::class, 'STDEVPA'],
2332 'argumentCount' => '1+',
2333 ],
2334 'STEYX' => [
2335 'category' => Category::CATEGORY_STATISTICAL,
2336 'functionCall' => [Statistical\Trends::class, 'STEYX'],
2337 'argumentCount' => '2',
2338 ],
2339 'SUBSTITUTE' => [
2341 'functionCall' => [TextData\Replace::class, 'substitute'],
2342 'argumentCount' => '3,4',
2343 ],
2344 'SUBTOTAL' => [
2346 'functionCall' => [MathTrig\Subtotal::class, 'evaluate'],
2347 'argumentCount' => '2+',
2348 'passCellReference' => true,
2349 ],
2350 'SUM' => [
2352 'functionCall' => [MathTrig\Sum::class, 'sumErroringStrings'],
2353 'argumentCount' => '1+',
2354 ],
2355 'SUMIF' => [
2357 'functionCall' => [Statistical\Conditional::class, 'SUMIF'],
2358 'argumentCount' => '2,3',
2359 ],
2360 'SUMIFS' => [
2362 'functionCall' => [Statistical\Conditional::class, 'SUMIFS'],
2363 'argumentCount' => '3+',
2364 ],
2365 'SUMPRODUCT' => [
2367 'functionCall' => [MathTrig\Sum::class, 'product'],
2368 'argumentCount' => '1+',
2369 ],
2370 'SUMSQ' => [
2372 'functionCall' => [MathTrig\SumSquares::class, 'sumSquare'],
2373 'argumentCount' => '1+',
2374 ],
2375 'SUMX2MY2' => [
2377 'functionCall' => [MathTrig\SumSquares::class, 'sumXSquaredMinusYSquared'],
2378 'argumentCount' => '2',
2379 ],
2380 'SUMX2PY2' => [
2382 'functionCall' => [MathTrig\SumSquares::class, 'sumXSquaredPlusYSquared'],
2383 'argumentCount' => '2',
2384 ],
2385 'SUMXMY2' => [
2387 'functionCall' => [MathTrig\SumSquares::class, 'sumXMinusYSquared'],
2388 'argumentCount' => '2',
2389 ],
2390 'SWITCH' => [
2391 'category' => Category::CATEGORY_LOGICAL,
2392 'functionCall' => [Logical\Conditional::class, 'statementSwitch'],
2393 'argumentCount' => '3+',
2394 ],
2395 'SYD' => [
2396 'category' => Category::CATEGORY_FINANCIAL,
2397 'functionCall' => [Financial\Depreciation::class, 'SYD'],
2398 'argumentCount' => '4',
2399 ],
2400 'T' => [
2402 'functionCall' => [TextData\Text::class, 'test'],
2403 'argumentCount' => '1',
2404 ],
2405 'TAN' => [
2407 'functionCall' => [MathTrig\Trig\Tangent::class, 'tan'],
2408 'argumentCount' => '1',
2409 ],
2410 'TANH' => [
2412 'functionCall' => [MathTrig\Trig\Tangent::class, 'tanh'],
2413 'argumentCount' => '1',
2414 ],
2415 'TBILLEQ' => [
2416 'category' => Category::CATEGORY_FINANCIAL,
2417 'functionCall' => [Financial\TreasuryBill::class, 'bondEquivalentYield'],
2418 'argumentCount' => '3',
2419 ],
2420 'TBILLPRICE' => [
2421 'category' => Category::CATEGORY_FINANCIAL,
2422 'functionCall' => [Financial\TreasuryBill::class, 'price'],
2423 'argumentCount' => '3',
2424 ],
2425 'TBILLYIELD' => [
2426 'category' => Category::CATEGORY_FINANCIAL,
2427 'functionCall' => [Financial\TreasuryBill::class, 'yield'],
2428 'argumentCount' => '3',
2429 ],
2430 'TDIST' => [
2431 'category' => Category::CATEGORY_STATISTICAL,
2432 'functionCall' => [Statistical\Distributions\StudentT::class, 'distribution'],
2433 'argumentCount' => '3',
2434 ],
2435 'T.DIST' => [
2436 'category' => Category::CATEGORY_STATISTICAL,
2437 'functionCall' => [Functions::class, 'DUMMY'],
2438 'argumentCount' => '3',
2439 ],
2440 'T.DIST.2T' => [
2441 'category' => Category::CATEGORY_STATISTICAL,
2442 'functionCall' => [Functions::class, 'DUMMY'],
2443 'argumentCount' => '2',
2444 ],
2445 'T.DIST.RT' => [
2446 'category' => Category::CATEGORY_STATISTICAL,
2447 'functionCall' => [Functions::class, 'DUMMY'],
2448 'argumentCount' => '2',
2449 ],
2450 'TEXT' => [
2452 'functionCall' => [TextData\Format::class, 'TEXTFORMAT'],
2453 'argumentCount' => '2',
2454 ],
2455 'TEXTJOIN' => [
2457 'functionCall' => [TextData\Concatenate::class, 'TEXTJOIN'],
2458 'argumentCount' => '3+',
2459 ],
2460 'THAIDAYOFWEEK' => [
2462 'functionCall' => [Functions::class, 'DUMMY'],
2463 'argumentCount' => '?',
2464 ],
2465 'THAIDIGIT' => [
2467 'functionCall' => [Functions::class, 'DUMMY'],
2468 'argumentCount' => '?',
2469 ],
2470 'THAIMONTHOFYEAR' => [
2472 'functionCall' => [Functions::class, 'DUMMY'],
2473 'argumentCount' => '?',
2474 ],
2475 'THAINUMSOUND' => [
2477 'functionCall' => [Functions::class, 'DUMMY'],
2478 'argumentCount' => '?',
2479 ],
2480 'THAINUMSTRING' => [
2482 'functionCall' => [Functions::class, 'DUMMY'],
2483 'argumentCount' => '?',
2484 ],
2485 'THAISTRINGLENGTH' => [
2487 'functionCall' => [Functions::class, 'DUMMY'],
2488 'argumentCount' => '?',
2489 ],
2490 'THAIYEAR' => [
2492 'functionCall' => [Functions::class, 'DUMMY'],
2493 'argumentCount' => '?',
2494 ],
2495 'TIME' => [
2497 'functionCall' => [DateTimeExcel\Time::class, 'fromHMS'],
2498 'argumentCount' => '3',
2499 ],
2500 'TIMEVALUE' => [
2502 'functionCall' => [DateTimeExcel\TimeValue::class, 'fromString'],
2503 'argumentCount' => '1',
2504 ],
2505 'TINV' => [
2506 'category' => Category::CATEGORY_STATISTICAL,
2507 'functionCall' => [Statistical\Distributions\StudentT::class, 'inverse'],
2508 'argumentCount' => '2',
2509 ],
2510 'T.INV' => [
2511 'category' => Category::CATEGORY_STATISTICAL,
2512 'functionCall' => [Statistical\Distributions\StudentT::class, 'inverse'],
2513 'argumentCount' => '2',
2514 ],
2515 'T.INV.2T' => [
2516 'category' => Category::CATEGORY_STATISTICAL,
2517 'functionCall' => [Functions::class, 'DUMMY'],
2518 'argumentCount' => '2',
2519 ],
2520 'TODAY' => [
2522 'functionCall' => [DateTimeExcel\Current::class, 'today'],
2523 'argumentCount' => '0',
2524 ],
2525 'TRANSPOSE' => [
2527 'functionCall' => [LookupRef\Matrix::class, 'transpose'],
2528 'argumentCount' => '1',
2529 ],
2530 'TREND' => [
2531 'category' => Category::CATEGORY_STATISTICAL,
2532 'functionCall' => [Statistical\Trends::class, 'TREND'],
2533 'argumentCount' => '1-4',
2534 ],
2535 'TRIM' => [
2537 'functionCall' => [TextData\Trim::class, 'spaces'],
2538 'argumentCount' => '1',
2539 ],
2540 'TRIMMEAN' => [
2541 'category' => Category::CATEGORY_STATISTICAL,
2542 'functionCall' => [Statistical\Averages\Mean::class, 'trim'],
2543 'argumentCount' => '2',
2544 ],
2545 'TRUE' => [
2546 'category' => Category::CATEGORY_LOGICAL,
2547 'functionCall' => [Logical\Boolean::class, 'TRUE'],
2548 'argumentCount' => '0',
2549 ],
2550 'TRUNC' => [
2552 'functionCall' => [MathTrig\Trunc::class, 'evaluate'],
2553 'argumentCount' => '1,2',
2554 ],
2555 'TTEST' => [
2556 'category' => Category::CATEGORY_STATISTICAL,
2557 'functionCall' => [Functions::class, 'DUMMY'],
2558 'argumentCount' => '4',
2559 ],
2560 'T.TEST' => [
2561 'category' => Category::CATEGORY_STATISTICAL,
2562 'functionCall' => [Functions::class, 'DUMMY'],
2563 'argumentCount' => '4',
2564 ],
2565 'TYPE' => [
2566 'category' => Category::CATEGORY_INFORMATION,
2567 'functionCall' => [Functions::class, 'TYPE'],
2568 'argumentCount' => '1',
2569 ],
2570 'UNICHAR' => [
2572 'functionCall' => [TextData\CharacterConvert::class, 'character'],
2573 'argumentCount' => '1',
2574 ],
2575 'UNICODE' => [
2577 'functionCall' => [TextData\CharacterConvert::class, 'code'],
2578 'argumentCount' => '1',
2579 ],
2580 'UNIQUE' => [
2582 'functionCall' => [Functions::class, 'DUMMY'],
2583 'argumentCount' => '1+',
2584 ],
2585 'UPPER' => [
2587 'functionCall' => [TextData\CaseConvert::class, 'upper'],
2588 'argumentCount' => '1',
2589 ],
2590 'USDOLLAR' => [
2591 'category' => Category::CATEGORY_FINANCIAL,
2592 'functionCall' => [Financial\Dollar::class, 'format'],
2593 'argumentCount' => '2',
2594 ],
2595 'VALUE' => [
2597 'functionCall' => [TextData\Format::class, 'VALUE'],
2598 'argumentCount' => '1',
2599 ],
2600 'VALUETOTEXT' => [
2602 'functionCall' => [Functions::class, 'DUMMY'],
2603 'argumentCount' => '?',
2604 ],
2605 'VAR' => [
2606 'category' => Category::CATEGORY_STATISTICAL,
2607 'functionCall' => [Statistical\Variances::class, 'VAR'],
2608 'argumentCount' => '1+',
2609 ],
2610 'VAR.P' => [
2611 'category' => Category::CATEGORY_STATISTICAL,
2612 'functionCall' => [Statistical\Variances::class, 'VARP'],
2613 'argumentCount' => '1+',
2614 ],
2615 'VAR.S' => [
2616 'category' => Category::CATEGORY_STATISTICAL,
2617 'functionCall' => [Statistical\Variances::class, 'VAR'],
2618 'argumentCount' => '1+',
2619 ],
2620 'VARA' => [
2621 'category' => Category::CATEGORY_STATISTICAL,
2622 'functionCall' => [Statistical\Variances::class, 'VARA'],
2623 'argumentCount' => '1+',
2624 ],
2625 'VARP' => [
2626 'category' => Category::CATEGORY_STATISTICAL,
2627 'functionCall' => [Statistical\Variances::class, 'VARP'],
2628 'argumentCount' => '1+',
2629 ],
2630 'VARPA' => [
2631 'category' => Category::CATEGORY_STATISTICAL,
2632 'functionCall' => [Statistical\Variances::class, 'VARPA'],
2633 'argumentCount' => '1+',
2634 ],
2635 'VDB' => [
2636 'category' => Category::CATEGORY_FINANCIAL,
2637 'functionCall' => [Functions::class, 'DUMMY'],
2638 'argumentCount' => '5-7',
2639 ],
2640 'VLOOKUP' => [
2642 'functionCall' => [LookupRef\VLookup::class, 'lookup'],
2643 'argumentCount' => '3,4',
2644 ],
2645 'WEBSERVICE' => [
2646 'category' => Category::CATEGORY_WEB,
2647 'functionCall' => [Web\Service::class, 'webService'],
2648 'argumentCount' => '1',
2649 ],
2650 'WEEKDAY' => [
2652 'functionCall' => [DateTimeExcel\Week::class, 'day'],
2653 'argumentCount' => '1,2',
2654 ],
2655 'WEEKNUM' => [
2657 'functionCall' => [DateTimeExcel\Week::class, 'number'],
2658 'argumentCount' => '1,2',
2659 ],
2660 'WEIBULL' => [
2661 'category' => Category::CATEGORY_STATISTICAL,
2662 'functionCall' => [Statistical\Distributions\Weibull::class, 'distribution'],
2663 'argumentCount' => '4',
2664 ],
2665 'WEIBULL.DIST' => [
2666 'category' => Category::CATEGORY_STATISTICAL,
2667 'functionCall' => [Statistical\Distributions\Weibull::class, 'distribution'],
2668 'argumentCount' => '4',
2669 ],
2670 'WORKDAY' => [
2672 'functionCall' => [DateTimeExcel\WorkDay::class, 'date'],
2673 'argumentCount' => '2-3',
2674 ],
2675 'WORKDAY.INTL' => [
2677 'functionCall' => [Functions::class, 'DUMMY'],
2678 'argumentCount' => '2-4',
2679 ],
2680 'XIRR' => [
2681 'category' => Category::CATEGORY_FINANCIAL,
2682 'functionCall' => [Financial\CashFlow\Variable\NonPeriodic::class, 'rate'],
2683 'argumentCount' => '2,3',
2684 ],
2685 'XLOOKUP' => [
2687 'functionCall' => [Functions::class, 'DUMMY'],
2688 'argumentCount' => '3-6',
2689 ],
2690 'XNPV' => [
2691 'category' => Category::CATEGORY_FINANCIAL,
2692 'functionCall' => [Financial\CashFlow\Variable\NonPeriodic::class, 'presentValue'],
2693 'argumentCount' => '3',
2694 ],
2695 'XMATCH' => [
2697 'functionCall' => [Functions::class, 'DUMMY'],
2698 'argumentCount' => '2,3',
2699 ],
2700 'XOR' => [
2701 'category' => Category::CATEGORY_LOGICAL,
2702 'functionCall' => [Logical\Operations::class, 'logicalXor'],
2703 'argumentCount' => '1+',
2704 ],
2705 'YEAR' => [
2707 'functionCall' => [DateTimeExcel\DateParts::class, 'year'],
2708 'argumentCount' => '1',
2709 ],
2710 'YEARFRAC' => [
2712 'functionCall' => [DateTimeExcel\YearFrac::class, 'fraction'],
2713 'argumentCount' => '2,3',
2714 ],
2715 'YIELD' => [
2716 'category' => Category::CATEGORY_FINANCIAL,
2717 'functionCall' => [Functions::class, 'DUMMY'],
2718 'argumentCount' => '6,7',
2719 ],
2720 'YIELDDISC' => [
2721 'category' => Category::CATEGORY_FINANCIAL,
2722 'functionCall' => [Financial\Securities\Yields::class, 'yieldDiscounted'],
2723 'argumentCount' => '4,5',
2724 ],
2725 'YIELDMAT' => [
2726 'category' => Category::CATEGORY_FINANCIAL,
2727 'functionCall' => [Financial\Securities\Yields::class, 'yieldAtMaturity'],
2728 'argumentCount' => '5,6',
2729 ],
2730 'ZTEST' => [
2731 'category' => Category::CATEGORY_STATISTICAL,
2732 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'zTest'],
2733 'argumentCount' => '2-3',
2734 ],
2735 'Z.TEST' => [
2736 'category' => Category::CATEGORY_STATISTICAL,
2737 'functionCall' => [Statistical\Distributions\StandardNormal::class, 'zTest'],
2738 'argumentCount' => '2-3',
2739 ],
2740 ];
2741
2742 // Internal functions used for special control purposes
2743 private static $controlFunctions = [
2744 'MKMATRIX' => [
2745 'argumentCount' => '*',
2746 'functionCall' => [Internal\MakeMatrix::class, 'make'],
2747 ],
2748 'NAME.ERROR' => [
2749 'argumentCount' => '*',
2750 'functionCall' => [Functions::class, 'NAME'],
2751 ],
2752 'WILDCARDMATCH' => [
2753 'argumentCount' => '2',
2754 'functionCall' => [Internal\WildcardMatch::class, 'compare'],
2755 ],
2756 ];
2757
2758 public function __construct(?Spreadsheet $spreadsheet = null)
2759 {
2760 $this->delta = 1 * 10 ** (0 - ini_get('precision'));
2761
2762 $this->spreadsheet = $spreadsheet;
2763 $this->cyclicReferenceStack = new CyclicReferenceStack();
2764 $this->debugLog = new Logger($this->cyclicReferenceStack);
2765 self::$referenceHelper = ReferenceHelper::getInstance();
2766 }
2767
2768 private static function loadLocales(): void
2769 {
2770 $localeFileDirectory = __DIR__ . '/locale/';
2771 foreach (glob($localeFileDirectory . '*', GLOB_ONLYDIR) as $filename) {
2772 $filename = substr($filename, strlen($localeFileDirectory));
2773 if ($filename != 'en') {
2774 self::$validLocaleLanguages[] = $filename;
2775 }
2776 }
2777 }
2778
2785 public static function getInstance(?Spreadsheet $spreadsheet = null): self
2786 {
2787 if ($spreadsheet !== null) {
2788 $instance = $spreadsheet->getCalculationEngine();
2789 if (isset($instance)) {
2790 return $instance;
2791 }
2792 }
2793
2794 if (!isset(self::$instance) || (self::$instance === null)) {
2795 self::$instance = new self();
2796 }
2797
2798 return self::$instance;
2799 }
2800
2805 public function flushInstance(): void
2806 {
2807 $this->clearCalculationCache();
2808 $this->clearBranchStore();
2809 }
2810
2816 public function getDebugLog()
2817 {
2818 return $this->debugLog;
2819 }
2820
2824 final public function __clone()
2825 {
2826 throw new Exception('Cloning the calculation engine is not allowed!');
2827 }
2828
2834 public static function getTRUE(): string
2835 {
2836 return self::$localeBoolean['TRUE'];
2837 }
2838
2844 public static function getFALSE(): string
2845 {
2846 return self::$localeBoolean['FALSE'];
2847 }
2848
2856 public static function setArrayReturnType($returnType)
2857 {
2858 if (
2859 ($returnType == self::RETURN_ARRAY_AS_VALUE) ||
2860 ($returnType == self::RETURN_ARRAY_AS_ERROR) ||
2861 ($returnType == self::RETURN_ARRAY_AS_ARRAY)
2862 ) {
2863 self::$returnArrayAsType = $returnType;
2864
2865 return true;
2866 }
2867
2868 return false;
2869 }
2870
2876 public static function getArrayReturnType()
2877 {
2879 }
2880
2887 {
2889 }
2890
2896 public function setCalculationCacheEnabled($pValue): void
2897 {
2898 $this->calculationCacheEnabled = $pValue;
2899 $this->clearCalculationCache();
2900 }
2901
2905 public function enableCalculationCache(): void
2906 {
2907 $this->setCalculationCacheEnabled(true);
2908 }
2909
2913 public function disableCalculationCache(): void
2914 {
2915 $this->setCalculationCacheEnabled(false);
2916 }
2917
2921 public function clearCalculationCache(): void
2922 {
2923 $this->calculationCache = [];
2924 }
2925
2931 public function clearCalculationCacheForWorksheet($worksheetName): void
2932 {
2933 if (isset($this->calculationCache[$worksheetName])) {
2934 unset($this->calculationCache[$worksheetName]);
2935 }
2936 }
2937
2944 public function renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName): void
2945 {
2946 if (isset($this->calculationCache[$fromWorksheetName])) {
2947 $this->calculationCache[$toWorksheetName] = &$this->calculationCache[$fromWorksheetName];
2948 unset($this->calculationCache[$fromWorksheetName]);
2949 }
2950 }
2951
2957 public function setBranchPruningEnabled($enabled): void
2958 {
2959 $this->branchPruningEnabled = $enabled;
2960 }
2961
2962 public function enableBranchPruning(): void
2963 {
2964 $this->setBranchPruningEnabled(true);
2965 }
2966
2967 public function disableBranchPruning(): void
2968 {
2969 $this->setBranchPruningEnabled(false);
2970 }
2971
2972 public function clearBranchStore(): void
2973 {
2974 $this->branchStoreKeyCounter = 0;
2975 }
2976
2982 public function getLocale()
2983 {
2984 return self::$localeLanguage;
2985 }
2986
2987 private function getLocaleFile(string $localeDir, string $locale, string $language, string $file): string
2988 {
2989 $localeFileName = $localeDir . str_replace('_', DIRECTORY_SEPARATOR, $locale) .
2990 DIRECTORY_SEPARATOR . $file;
2991 if (!file_exists($localeFileName)) {
2992 // If there isn't a locale specific file, look for a language specific file
2993 $localeFileName = $localeDir . $language . DIRECTORY_SEPARATOR . $file;
2994 if (!file_exists($localeFileName)) {
2995 throw new Exception('Locale file not found');
2996 }
2997 }
2998
2999 return $localeFileName;
3000 }
3001
3009 public function setLocale(string $locale)
3010 {
3011 // Identify our locale and language
3012 $language = $locale = strtolower($locale);
3013 if (strpos($locale, '_') !== false) {
3014 [$language] = explode('_', $locale);
3015 }
3016 if (count(self::$validLocaleLanguages) == 1) {
3018 }
3019
3020 // Test whether we have any language data for this language (any locale)
3021 if (in_array($language, self::$validLocaleLanguages)) {
3022 // initialise language/locale settings
3023 self::$localeFunctions = [];
3024 self::$localeArgumentSeparator = ',';
3025 self::$localeBoolean = ['TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL'];
3026
3027 // Default is US English, if user isn't requesting US english, then read the necessary data from the locale files
3028 if ($locale !== 'en_us') {
3029 $localeDir = implode(DIRECTORY_SEPARATOR, [__DIR__, 'locale', null]);
3030 // Search for a file with a list of function names for locale
3031 try {
3032 $functionNamesFile = $this->getLocaleFile($localeDir, $locale, $language, 'functions');
3033 } catch (Exception $e) {
3034 return false;
3035 }
3036
3037 // Retrieve the list of locale or language specific function names
3038 $localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
3039 foreach ($localeFunctions as $localeFunction) {
3040 [$localeFunction] = explode('##', $localeFunction); // Strip out comments
3041 if (strpos($localeFunction, '=') !== false) {
3042 [$fName, $lfName] = array_map('trim', explode('=', $localeFunction));
3043 if ((isset(self::$phpSpreadsheetFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {
3044 self::$localeFunctions[$fName] = $lfName;
3045 }
3046 }
3047 }
3048 // Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
3049 if (isset(self::$localeFunctions['TRUE'])) {
3050 self::$localeBoolean['TRUE'] = self::$localeFunctions['TRUE'];
3051 }
3052 if (isset(self::$localeFunctions['FALSE'])) {
3053 self::$localeBoolean['FALSE'] = self::$localeFunctions['FALSE'];
3054 }
3055
3056 try {
3057 $configFile = $this->getLocaleFile($localeDir, $locale, $language, 'config');
3058 } catch (Exception $e) {
3059 return false;
3060 }
3061
3062 $localeSettings = file($configFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
3063 foreach ($localeSettings as $localeSetting) {
3064 [$localeSetting] = explode('##', $localeSetting); // Strip out comments
3065 if (strpos($localeSetting, '=') !== false) {
3066 [$settingName, $settingValue] = array_map('trim', explode('=', $localeSetting));
3067 $settingName = strtoupper($settingName);
3068 if ($settingValue !== '') {
3069 switch ($settingName) {
3070 case 'ARGUMENTSEPARATOR':
3071 self::$localeArgumentSeparator = $settingValue;
3072
3073 break;
3074 }
3075 }
3076 }
3077 }
3078 }
3079
3080 self::$functionReplaceFromExcel = self::$functionReplaceToExcel =
3081 self::$functionReplaceFromLocale = self::$functionReplaceToLocale = null;
3082 self::$localeLanguage = $locale;
3083
3084 return true;
3085 }
3086
3087 return false;
3088 }
3089
3098 public static function translateSeparator($fromSeparator, $toSeparator, $formula, &$inBraces)
3099 {
3100 $strlen = mb_strlen($formula);
3101 for ($i = 0; $i < $strlen; ++$i) {
3102 $chr = mb_substr($formula, $i, 1);
3103 switch ($chr) {
3105 $inBraces = true;
3106
3107 break;
3109 $inBraces = false;
3110
3111 break;
3112 case $fromSeparator:
3113 if (!$inBraces) {
3114 $formula = mb_substr($formula, 0, $i) . $toSeparator . mb_substr($formula, $i + 1);
3115 }
3116 }
3117 }
3118
3119 return $formula;
3120 }
3121
3131 private static function translateFormula(array $from, array $to, $formula, $fromSeparator, $toSeparator)
3132 {
3133 // Convert any Excel function names to the required language
3134 if (self::$localeLanguage !== 'en_us') {
3135 $inBraces = false;
3136 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
3137 if (strpos($formula, self::FORMULA_STRING_QUOTE) !== false) {
3138 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
3139 // the formula
3140 $temp = explode(self::FORMULA_STRING_QUOTE, $formula);
3141 $i = false;
3142 foreach ($temp as &$value) {
3143 // Only count/replace in alternating array entries
3144 if ($i = !$i) {
3145 $value = preg_replace($from, $to, $value);
3146 $value = self::translateSeparator($fromSeparator, $toSeparator, $value, $inBraces);
3147 }
3148 }
3149 unset($value);
3150 // Then rebuild the formula string
3151 $formula = implode(self::FORMULA_STRING_QUOTE, $temp);
3152 } else {
3153 // If there's no quoted strings, then we do a simple count/replace
3154 $formula = preg_replace($from, $to, $formula);
3155 $formula = self::translateSeparator($fromSeparator, $toSeparator, $formula, $inBraces);
3156 }
3157 }
3158
3159 return $formula;
3160 }
3161
3162 private static $functionReplaceFromExcel = null;
3163
3164 private static $functionReplaceToLocale = null;
3165
3166 public function _translateFormulaToLocale($formula)
3167 {
3168 if (self::$functionReplaceFromExcel === null) {
3169 self::$functionReplaceFromExcel = [];
3170 foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
3171 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelFunctionName, '/') . '([\s]*\‍()/Ui';
3172 }
3173 foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
3174 self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/Ui';
3175 }
3176 }
3177
3178 if (self::$functionReplaceToLocale === null) {
3179 self::$functionReplaceToLocale = [];
3180 foreach (self::$localeFunctions as $localeFunctionName) {
3181 self::$functionReplaceToLocale[] = '$1' . trim($localeFunctionName) . '$2';
3182 }
3183 foreach (self::$localeBoolean as $localeBoolean) {
3184 self::$functionReplaceToLocale[] = '$1' . trim($localeBoolean) . '$2';
3185 }
3186 }
3187
3188 return self::translateFormula(self::$functionReplaceFromExcel, self::$functionReplaceToLocale, $formula, ',', self::$localeArgumentSeparator);
3189 }
3190
3191 private static $functionReplaceFromLocale = null;
3192
3193 private static $functionReplaceToExcel = null;
3194
3195 public function _translateFormulaToEnglish($formula)
3196 {
3197 if (self::$functionReplaceFromLocale === null) {
3198 self::$functionReplaceFromLocale = [];
3199 foreach (self::$localeFunctions as $localeFunctionName) {
3200 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($localeFunctionName, '/') . '([\s]*\‍()/Ui';
3201 }
3202 foreach (self::$localeBoolean as $excelBoolean) {
3203 self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/Ui';
3204 }
3205 }
3206
3207 if (self::$functionReplaceToExcel === null) {
3208 self::$functionReplaceToExcel = [];
3209 foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
3210 self::$functionReplaceToExcel[] = '$1' . trim($excelFunctionName) . '$2';
3211 }
3212 foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
3213 self::$functionReplaceToExcel[] = '$1' . trim($excelBoolean) . '$2';
3214 }
3215 }
3216
3217 return self::translateFormula(self::$functionReplaceFromLocale, self::$functionReplaceToExcel, $formula, self::$localeArgumentSeparator, ',');
3218 }
3219
3220 public static function localeFunc($function)
3221 {
3222 if (self::$localeLanguage !== 'en_us') {
3223 $functionName = trim($function, '(');
3224 if (isset(self::$localeFunctions[$functionName])) {
3225 $brace = ($functionName != $function);
3226 $function = self::$localeFunctions[$functionName];
3227 if ($brace) {
3228 $function .= '(';
3229 }
3230 }
3231 }
3232
3233 return $function;
3234 }
3235
3243 public static function wrapResult($value)
3244 {
3245 if (is_string($value)) {
3246 // Error values cannot be "wrapped"
3247 if (preg_match('/^' . self::CALCULATION_REGEXP_ERROR . '$/i', $value, $match)) {
3248 // Return Excel errors "as is"
3249 return $value;
3250 }
3251
3252 // Return strings wrapped in quotes
3253 return self::FORMULA_STRING_QUOTE . $value . self::FORMULA_STRING_QUOTE;
3254 } elseif ((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
3255 // Convert numeric errors to NaN error
3256 return Functions::NAN();
3257 }
3258
3259 return $value;
3260 }
3261
3269 public static function unwrapResult($value)
3270 {
3271 if (is_string($value)) {
3272 if ((isset($value[0])) && ($value[0] == self::FORMULA_STRING_QUOTE) && (substr($value, -1) == self::FORMULA_STRING_QUOTE)) {
3273 return substr($value, 1, -1);
3274 }
3275 // Convert numeric errors to NAN error
3276 } elseif ((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
3277 return Functions::NAN();
3278 }
3279
3280 return $value;
3281 }
3282
3291 public function calculate(?Cell $pCell = null)
3292 {
3293 try {
3294 return $this->calculateCellValue($pCell);
3295 } catch (\Exception $e) {
3296 throw new Exception($e->getMessage());
3297 }
3298 }
3299
3308 public function calculateCellValue(?Cell $pCell = null, $resetLog = true)
3309 {
3310 if ($pCell === null) {
3311 return null;
3312 }
3313
3315 if ($resetLog) {
3316 // Initialise the logging settings if requested
3317 $this->formulaError = null;
3318 $this->debugLog->clearLog();
3319 $this->cyclicReferenceStack->clear();
3320 $this->cyclicFormulaCounter = 1;
3321
3322 self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
3323 }
3324
3325 // Execute the calculation for the cell formula
3326 $this->cellStack[] = [
3327 'sheet' => $pCell->getWorksheet()->getTitle(),
3328 'cell' => $pCell->getCoordinate(),
3329 ];
3330
3331 try {
3332 $result = self::unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
3333 $cellAddress = array_pop($this->cellStack);
3334 $this->spreadsheet->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
3335 } catch (\Exception $e) {
3336 $cellAddress = array_pop($this->cellStack);
3337 $this->spreadsheet->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
3338
3339 throw new Exception($e->getMessage());
3340 }
3341
3342 if ((is_array($result)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
3343 self::$returnArrayAsType = $returnArrayAsType;
3344 $testResult = Functions::flattenArray($result);
3345 if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
3346 return Functions::VALUE();
3347 }
3348 // If there's only a single cell in the array, then we allow it
3349 if (count($testResult) != 1) {
3350 // If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
3351 $r = array_keys($result);
3352 $r = array_shift($r);
3353 if (!is_numeric($r)) {
3354 return Functions::VALUE();
3355 }
3356 if (is_array($result[$r])) {
3357 $c = array_keys($result[$r]);
3358 $c = array_shift($c);
3359 if (!is_numeric($c)) {
3360 return Functions::VALUE();
3361 }
3362 }
3363 }
3364 $result = array_shift($testResult);
3365 }
3366 self::$returnArrayAsType = $returnArrayAsType;
3367
3368 if ($result === null && $pCell->getWorksheet()->getSheetView()->getShowZeros()) {
3369 return 0;
3370 } elseif ((is_float($result)) && ((is_nan($result)) || (is_infinite($result)))) {
3371 return Functions::NAN();
3372 }
3373
3374 return $result;
3375 }
3376
3384 public function parseFormula($formula)
3385 {
3386 // Basic validation that this is indeed a formula
3387 // We return an empty array if not
3388 $formula = trim($formula);
3389 if ((!isset($formula[0])) || ($formula[0] != '=')) {
3390 return [];
3391 }
3392 $formula = ltrim(substr($formula, 1));
3393 if (!isset($formula[0])) {
3394 return [];
3395 }
3396
3397 // Parse the formula and return the token stack
3398 return $this->internalParseFormula($formula);
3399 }
3400
3410 public function calculateFormula($formula, $cellID = null, ?Cell $pCell = null)
3411 {
3412 // Initialise the logging settings
3413 $this->formulaError = null;
3414 $this->debugLog->clearLog();
3415 $this->cyclicReferenceStack->clear();
3416
3417 $resetCache = $this->getCalculationCacheEnabled();
3418 if ($this->spreadsheet !== null && $cellID === null && $pCell === null) {
3419 $cellID = 'A1';
3420 $pCell = $this->spreadsheet->getActiveSheet()->getCell($cellID);
3421 } else {
3422 // Disable calculation cacheing because it only applies to cell calculations, not straight formulae
3423 // But don't actually flush any cache
3424 $this->calculationCacheEnabled = false;
3425 }
3426
3427 // Execute the calculation
3428 try {
3429 $result = self::unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
3430 } catch (\Exception $e) {
3431 throw new Exception($e->getMessage());
3432 }
3433
3434 if ($this->spreadsheet === null) {
3435 // Reset calculation cacheing to its previous state
3436 $this->calculationCacheEnabled = $resetCache;
3437 }
3438
3439 return $result;
3440 }
3441
3445 public function getValueFromCache(string $cellReference, &$cellValue): bool
3446 {
3447 $this->debugLog->writeDebugLog("Testing cache value for cell {$cellReference}");
3448 // Is calculation cacheing enabled?
3449 // If so, is the required value present in calculation cache?
3450 if (($this->calculationCacheEnabled) && (isset($this->calculationCache[$cellReference]))) {
3451 $this->debugLog->writeDebugLog("Retrieving value for cell {$cellReference} from cache");
3452 // Return the cached result
3453
3454 $cellValue = $this->calculationCache[$cellReference];
3455
3456 return true;
3457 }
3458
3459 return false;
3460 }
3461
3466 public function saveValueToCache($cellReference, $cellValue): void
3467 {
3468 if ($this->calculationCacheEnabled) {
3469 $this->calculationCache[$cellReference] = $cellValue;
3470 }
3471 }
3472
3482 public function _calculateFormulaValue($formula, $cellID = null, ?Cell $pCell = null)
3483 {
3484 $cellValue = null;
3485
3486 // Quote-Prefixed cell values cannot be formulae, but are treated as strings
3487 if ($pCell !== null && $pCell->getStyle()->getQuotePrefix() === true) {
3488 return self::wrapResult((string) $formula);
3489 }
3490
3491 if (preg_match('/^=\s*cmd\s*\|/miu', $formula) !== 0) {
3492 return self::wrapResult($formula);
3493 }
3494
3495 // Basic validation that this is indeed a formula
3496 // We simply return the cell value if not
3497 $formula = trim($formula);
3498 if ($formula[0] != '=') {
3499 return self::wrapResult($formula);
3500 }
3501 $formula = ltrim(substr($formula, 1));
3502 if (!isset($formula[0])) {
3503 return self::wrapResult($formula);
3504 }
3505
3506 $pCellParent = ($pCell !== null) ? $pCell->getWorksheet() : null;
3507 $wsTitle = ($pCellParent !== null) ? $pCellParent->getTitle() : "\x00Wrk";
3508 $wsCellReference = $wsTitle . '!' . $cellID;
3509
3510 if (($cellID !== null) && ($this->getValueFromCache($wsCellReference, $cellValue))) {
3511 return $cellValue;
3512 }
3513 $this->debugLog->writeDebugLog("Evaluating formula for cell {$wsCellReference}");
3514
3515 if (($wsTitle[0] !== "\x00") && ($this->cyclicReferenceStack->onStack($wsCellReference))) {
3516 if ($this->cyclicFormulaCount <= 0) {
3517 $this->cyclicFormulaCell = '';
3518
3519 return $this->raiseFormulaError('Cyclic Reference in Formula');
3520 } elseif ($this->cyclicFormulaCell === $wsCellReference) {
3522 if ($this->cyclicFormulaCounter >= $this->cyclicFormulaCount) {
3523 $this->cyclicFormulaCell = '';
3524
3525 return $cellValue;
3526 }
3527 } elseif ($this->cyclicFormulaCell == '') {
3528 if ($this->cyclicFormulaCounter >= $this->cyclicFormulaCount) {
3529 return $cellValue;
3530 }
3531 $this->cyclicFormulaCell = $wsCellReference;
3532 }
3533 }
3534
3535 $this->debugLog->writeDebugLog("Formula for cell {$wsCellReference} is {$formula}");
3536 // Parse the formula onto the token stack and calculate the value
3537 $this->cyclicReferenceStack->push($wsCellReference);
3538
3539 $cellValue = $this->processTokenStack($this->internalParseFormula($formula, $pCell), $cellID, $pCell);
3540 $this->cyclicReferenceStack->pop();
3541
3542 // Save to calculation cache
3543 if ($cellID !== null) {
3544 $this->saveValueToCache($wsCellReference, $cellValue);
3545 }
3546
3547 // Return the calculated value
3548 return $cellValue;
3549 }
3550
3565 private static function checkMatrixOperands(&$operand1, &$operand2, $resize = 1)
3566 {
3567 // Examine each of the two operands, and turn them into an array if they aren't one already
3568 // Note that this function should only be called if one or both of the operand is already an array
3569 if (!is_array($operand1)) {
3570 [$matrixRows, $matrixColumns] = self::getMatrixDimensions($operand2);
3571 $operand1 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand1));
3572 $resize = 0;
3573 } elseif (!is_array($operand2)) {
3574 [$matrixRows, $matrixColumns] = self::getMatrixDimensions($operand1);
3575 $operand2 = array_fill(0, $matrixRows, array_fill(0, $matrixColumns, $operand2));
3576 $resize = 0;
3577 }
3578
3579 [$matrix1Rows, $matrix1Columns] = self::getMatrixDimensions($operand1);
3580 [$matrix2Rows, $matrix2Columns] = self::getMatrixDimensions($operand2);
3581 if (($matrix1Rows == $matrix2Columns) && ($matrix2Rows == $matrix1Columns)) {
3582 $resize = 1;
3583 }
3584
3585 if ($resize == 2) {
3586 // Given two matrices of (potentially) unequal size, convert the smaller in each dimension to match the larger
3587 self::resizeMatricesExtend($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns);
3588 } elseif ($resize == 1) {
3589 // Given two matrices of (potentially) unequal size, convert the larger in each dimension to match the smaller
3590 self::resizeMatricesShrink($operand1, $operand2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns);
3591 }
3592
3593 return [$matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns];
3594 }
3595
3603 public static function getMatrixDimensions(array &$matrix)
3604 {
3605 $matrixRows = count($matrix);
3606 $matrixColumns = 0;
3607 foreach ($matrix as $rowKey => $rowValue) {
3608 if (!is_array($rowValue)) {
3609 $matrix[$rowKey] = [$rowValue];
3610 $matrixColumns = max(1, $matrixColumns);
3611 } else {
3612 $matrix[$rowKey] = array_values($rowValue);
3613 $matrixColumns = max(count($rowValue), $matrixColumns);
3614 }
3615 }
3616 $matrix = array_values($matrix);
3617
3618 return [$matrixRows, $matrixColumns];
3619 }
3620
3631 private static function resizeMatricesShrink(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns): void
3632 {
3633 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
3634 if ($matrix2Rows < $matrix1Rows) {
3635 for ($i = $matrix2Rows; $i < $matrix1Rows; ++$i) {
3636 unset($matrix1[$i]);
3637 }
3638 }
3639 if ($matrix2Columns < $matrix1Columns) {
3640 for ($i = 0; $i < $matrix1Rows; ++$i) {
3641 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
3642 unset($matrix1[$i][$j]);
3643 }
3644 }
3645 }
3646 }
3647
3648 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
3649 if ($matrix1Rows < $matrix2Rows) {
3650 for ($i = $matrix1Rows; $i < $matrix2Rows; ++$i) {
3651 unset($matrix2[$i]);
3652 }
3653 }
3654 if ($matrix1Columns < $matrix2Columns) {
3655 for ($i = 0; $i < $matrix2Rows; ++$i) {
3656 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
3657 unset($matrix2[$i][$j]);
3658 }
3659 }
3660 }
3661 }
3662 }
3663
3674 private static function resizeMatricesExtend(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns): void
3675 {
3676 if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
3677 if ($matrix2Columns < $matrix1Columns) {
3678 for ($i = 0; $i < $matrix2Rows; ++$i) {
3679 $x = $matrix2[$i][$matrix2Columns - 1];
3680 for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
3681 $matrix2[$i][$j] = $x;
3682 }
3683 }
3684 }
3685 if ($matrix2Rows < $matrix1Rows) {
3686 $x = $matrix2[$matrix2Rows - 1];
3687 for ($i = 0; $i < $matrix1Rows; ++$i) {
3688 $matrix2[$i] = $x;
3689 }
3690 }
3691 }
3692
3693 if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
3694 if ($matrix1Columns < $matrix2Columns) {
3695 for ($i = 0; $i < $matrix1Rows; ++$i) {
3696 $x = $matrix1[$i][$matrix1Columns - 1];
3697 for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
3698 $matrix1[$i][$j] = $x;
3699 }
3700 }
3701 }
3702 if ($matrix1Rows < $matrix2Rows) {
3703 $x = $matrix1[$matrix1Rows - 1];
3704 for ($i = 0; $i < $matrix2Rows; ++$i) {
3705 $matrix1[$i] = $x;
3706 }
3707 }
3708 }
3709 }
3710
3718 private function showValue($value)
3719 {
3720 if ($this->debugLog->getWriteDebugLog()) {
3721 $testArray = Functions::flattenArray($value);
3722 if (count($testArray) == 1) {
3723 $value = array_pop($testArray);
3724 }
3725
3726 if (is_array($value)) {
3727 $returnMatrix = [];
3728 $pad = $rpad = ', ';
3729 foreach ($value as $row) {
3730 if (is_array($row)) {
3731 $returnMatrix[] = implode($pad, array_map([$this, 'showValue'], $row));
3732 $rpad = '; ';
3733 } else {
3734 $returnMatrix[] = $this->showValue($row);
3735 }
3736 }
3737
3738 return '{ ' . implode($rpad, $returnMatrix) . ' }';
3739 } elseif (is_string($value) && (trim($value, self::FORMULA_STRING_QUOTE) == $value)) {
3740 return self::FORMULA_STRING_QUOTE . $value . self::FORMULA_STRING_QUOTE;
3741 } elseif (is_bool($value)) {
3742 return ($value) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
3743 }
3744 }
3745
3746 return Functions::flattenSingleValue($value);
3747 }
3748
3756 private function showTypeDetails($value)
3757 {
3758 if ($this->debugLog->getWriteDebugLog()) {
3759 $testArray = Functions::flattenArray($value);
3760 if (count($testArray) == 1) {
3761 $value = array_pop($testArray);
3762 }
3763
3764 if ($value === null) {
3765 return 'a NULL value';
3766 } elseif (is_float($value)) {
3767 $typeString = 'a floating point number';
3768 } elseif (is_int($value)) {
3769 $typeString = 'an integer number';
3770 } elseif (is_bool($value)) {
3771 $typeString = 'a boolean';
3772 } elseif (is_array($value)) {
3773 $typeString = 'a matrix';
3774 } else {
3775 if ($value == '') {
3776 return 'an empty string';
3777 } elseif ($value[0] == '#') {
3778 return 'a ' . $value . ' error';
3779 }
3780 $typeString = 'a string';
3781 }
3782
3783 return $typeString . ' with a value of ' . $this->showValue($value);
3784 }
3785
3786 return null;
3787 }
3788
3794 private function convertMatrixReferences($formula)
3795 {
3797 static $matrixReplaceTo = ['MKMATRIX(MKMATRIX(', '),MKMATRIX(', '))'];
3798
3799 // Convert any Excel matrix references to the MKMATRIX() function
3800 if (strpos($formula, self::FORMULA_OPEN_FUNCTION_BRACE) !== false) {
3801 // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
3802 if (strpos($formula, self::FORMULA_STRING_QUOTE) !== false) {
3803 // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
3804 // the formula
3805 $temp = explode(self::FORMULA_STRING_QUOTE, $formula);
3806 // Open and Closed counts used for trapping mismatched braces in the formula
3807 $openCount = $closeCount = 0;
3808 $i = false;
3809 foreach ($temp as &$value) {
3810 // Only count/replace in alternating array entries
3811 if ($i = !$i) {
3812 $openCount += substr_count($value, self::FORMULA_OPEN_FUNCTION_BRACE);
3813 $closeCount += substr_count($value, self::FORMULA_CLOSE_FUNCTION_BRACE);
3814 $value = str_replace($matrixReplaceFrom, $matrixReplaceTo, $value);
3815 }
3816 }
3817 unset($value);
3818 // Then rebuild the formula string
3819 $formula = implode(self::FORMULA_STRING_QUOTE, $temp);
3820 } else {
3821 // If there's no quoted strings, then we do a simple count/replace
3822 $openCount = substr_count($formula, self::FORMULA_OPEN_FUNCTION_BRACE);
3823 $closeCount = substr_count($formula, self::FORMULA_CLOSE_FUNCTION_BRACE);
3824 $formula = str_replace($matrixReplaceFrom, $matrixReplaceTo, $formula);
3825 }
3826 // Trap for mismatched braces and trigger an appropriate error
3827 if ($openCount < $closeCount) {
3828 if ($openCount > 0) {
3829 return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '}'");
3830 }
3831
3832 return $this->raiseFormulaError("Formula Error: Unexpected '}' encountered");
3833 } elseif ($openCount > $closeCount) {
3834 if ($closeCount > 0) {
3835 return $this->raiseFormulaError("Formula Error: Mismatched matrix braces '{'");
3836 }
3837
3838 return $this->raiseFormulaError("Formula Error: Unexpected '{' encountered");
3839 }
3840 }
3841
3842 return $formula;
3843 }
3844
3845 // Binary Operators
3846 // These operators always work on two values
3847 // Array key is the operator, the value indicates whether this is a left or right associative operator
3848 private static $operatorAssociativity = [
3849 '^' => 0, // Exponentiation
3850 '*' => 0, '/' => 0, // Multiplication and Division
3851 '+' => 0, '-' => 0, // Addition and Subtraction
3852 '&' => 0, // Concatenation
3853 '|' => 0, ':' => 0, // Intersect and Range
3854 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0, // Comparison
3855 ];
3856
3857 // Comparison (Boolean) Operators
3858 // These operators work on two values, but always return a boolean result
3859 private static $comparisonOperators = ['>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true];
3860
3861 // Operator Precedence
3862 // This list includes all valid operators, whether binary (including boolean) or unary (such as %)
3863 // Array key is the operator, the value is its precedence
3864 private static $operatorPrecedence = [
3865 ':' => 8, // Range
3866 '|' => 7, // Intersect
3867 '~' => 6, // Negation
3868 '%' => 5, // Percentage
3869 '^' => 4, // Exponentiation
3870 '*' => 3, '/' => 3, // Multiplication and Division
3871 '+' => 2, '-' => 2, // Addition and Subtraction
3872 '&' => 1, // Concatenation
3873 '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0, // Comparison
3874 ];
3875
3876 // Convert infix to postfix notation
3877
3883 private function internalParseFormula($formula, ?Cell $pCell = null)
3884 {
3885 if (($formula = $this->convertMatrixReferences(trim($formula))) === false) {
3886 return false;
3887 }
3888
3889 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
3890 // so we store the parent worksheet so that we can re-attach it when necessary
3891 $pCellParent = ($pCell !== null) ? $pCell->getWorksheet() : null;
3892
3893 $regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION .
3894 '|' . self::CALCULATION_REGEXP_CELLREF .
3895 '|' . self::CALCULATION_REGEXP_COLUMN_RANGE .
3896 '|' . self::CALCULATION_REGEXP_ROW_RANGE .
3897 '|' . self::CALCULATION_REGEXP_NUMBER .
3898 '|' . self::CALCULATION_REGEXP_STRING .
3899 '|' . self::CALCULATION_REGEXP_OPENBRACE .
3900 '|' . self::CALCULATION_REGEXP_DEFINEDNAME .
3901 '|' . self::CALCULATION_REGEXP_ERROR .
3902 ')/sui';
3903
3904 // Start with initialisation
3905 $index = 0;
3906 $stack = new Stack();
3907 $output = [];
3908 $expectingOperator = false; // We use this test in syntax-checking the expression to determine when a
3909 // - is a negation or + is a positive operator rather than an operation
3910 $expectingOperand = false; // We use this test in syntax-checking the expression to determine whether an operand
3911 // should be null in a function call
3912
3913 // IF branch pruning
3914 // currently pending storeKey (last item of the storeKeysStack
3915 $pendingStoreKey = null;
3916 // stores a list of storeKeys (string[])
3917 $pendingStoreKeysStack = [];
3918 $expectingConditionMap = []; // ['storeKey' => true, ...]
3919 $expectingThenMap = []; // ['storeKey' => true, ...]
3920 $expectingElseMap = []; // ['storeKey' => true, ...]
3921 $parenthesisDepthMap = []; // ['storeKey' => 4, ...]
3922
3923 // The guts of the lexical parser
3924 // Loop through the formula extracting each operator and operand in turn
3925 while (true) {
3926 // Branch pruning: we adapt the output item to the context (it will
3927 // be used to limit its computation)
3928 $currentCondition = null;
3929 $currentOnlyIf = null;
3930 $currentOnlyIfNot = null;
3931 $previousStoreKey = null;
3932 $pendingStoreKey = end($pendingStoreKeysStack);
3933
3934 if ($this->branchPruningEnabled) {
3935 // this is a condition ?
3936 if (isset($expectingConditionMap[$pendingStoreKey]) && $expectingConditionMap[$pendingStoreKey]) {
3937 $currentCondition = $pendingStoreKey;
3938 $stackDepth = count($pendingStoreKeysStack);
3939 if ($stackDepth > 1) { // nested if
3940 $previousStoreKey = $pendingStoreKeysStack[$stackDepth - 2];
3941 }
3942 }
3943 if (isset($expectingThenMap[$pendingStoreKey]) && $expectingThenMap[$pendingStoreKey]) {
3944 $currentOnlyIf = $pendingStoreKey;
3945 } elseif (isset($previousStoreKey)) {
3946 if (isset($expectingThenMap[$previousStoreKey]) && $expectingThenMap[$previousStoreKey]) {
3947 $currentOnlyIf = $previousStoreKey;
3948 }
3949 }
3950 if (isset($expectingElseMap[$pendingStoreKey]) && $expectingElseMap[$pendingStoreKey]) {
3951 $currentOnlyIfNot = $pendingStoreKey;
3952 } elseif (isset($previousStoreKey)) {
3953 if (isset($expectingElseMap[$previousStoreKey]) && $expectingElseMap[$previousStoreKey]) {
3954 $currentOnlyIfNot = $previousStoreKey;
3955 }
3956 }
3957 }
3958
3959 $opCharacter = $formula[$index]; // Get the first character of the value at the current index position
3960
3961 if ((isset(self::$comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset(self::$comparisonOperators[$formula[$index + 1]]))) {
3962 $opCharacter .= $formula[++$index];
3963 }
3964 // Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
3965 $isOperandOrFunction = (bool) preg_match($regexpMatchString, substr($formula, $index), $match);
3966
3967 if ($opCharacter == '-' && !$expectingOperator) { // Is it a negation instead of a minus?
3968 // Put a negation on the stack
3969 $stack->push('Unary Operator', '~', null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
3970 ++$index; // and drop the negation symbol
3971 } elseif ($opCharacter == '%' && $expectingOperator) {
3972 // Put a percentage on the stack
3973 $stack->push('Unary Operator', '%', null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
3974 ++$index;
3975 } elseif ($opCharacter == '+' && !$expectingOperator) { // Positive (unary plus rather than binary operator plus) can be discarded?
3976 ++$index; // Drop the redundant plus symbol
3977 } elseif ((($opCharacter == '~') || ($opCharacter == '|')) && (!$isOperandOrFunction)) { // We have to explicitly deny a tilde or pipe, because they are legal
3978 return $this->raiseFormulaError("Formula Error: Illegal character '~'"); // on the stack but not in the input expression
3979 } elseif ((isset(self::$operators[$opCharacter]) || $isOperandOrFunction) && $expectingOperator) { // Are we putting an operator on the stack?
3980 while (
3981 $stack->count() > 0 &&
3982 ($o2 = $stack->last()) &&
3983 isset(self::$operators[$o2['value']]) &&
3984 @(self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2['value']] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2['value']])
3985 ) {
3986 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
3987 }
3988
3989 // Finally put our current operator onto the stack
3990 $stack->push('Binary Operator', $opCharacter, null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
3991
3992 ++$index;
3993 $expectingOperator = false;
3994 } elseif ($opCharacter == ')' && $expectingOperator) { // Are we expecting to close a parenthesis?
3995 $expectingOperand = false;
3996 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
3997 if ($o2 === null) {
3998 return $this->raiseFormulaError('Formula Error: Unexpected closing brace ")"');
3999 }
4000 $output[] = $o2;
4001 }
4002 $d = $stack->last(2);
4003
4004 // Branch pruning we decrease the depth whether is it a function
4005 // call or a parenthesis
4006 if (!empty($pendingStoreKey)) {
4007 --$parenthesisDepthMap[$pendingStoreKey];
4008 }
4009
4010 if (is_array($d) && preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/miu', $d['value'], $matches)) { // Did this parenthesis just close a function?
4011 if (!empty($pendingStoreKey) && $parenthesisDepthMap[$pendingStoreKey] == -1) {
4012 // we are closing an IF(
4013 if ($d['value'] != 'IF(') {
4014 return $this->raiseFormulaError('Parser bug we should be in an "IF("');
4015 }
4016 if ($expectingConditionMap[$pendingStoreKey]) {
4017 return $this->raiseFormulaError('We should not be expecting a condition');
4018 }
4019 $expectingThenMap[$pendingStoreKey] = false;
4020 $expectingElseMap[$pendingStoreKey] = false;
4021 --$parenthesisDepthMap[$pendingStoreKey];
4022 array_pop($pendingStoreKeysStack);
4023 unset($pendingStoreKey);
4024 }
4025
4026 $functionName = $matches[1]; // Get the function name
4027 $d = $stack->pop();
4028 $argumentCount = $d['value']; // See how many arguments there were (argument count is the next value stored on the stack)
4029 $output[] = $d; // Dump the argument count on the output
4030 $output[] = $stack->pop(); // Pop the function and push onto the output
4031 if (isset(self::$controlFunctions[$functionName])) {
4032 $expectedArgumentCount = self::$controlFunctions[$functionName]['argumentCount'];
4033 $functionCall = self::$controlFunctions[$functionName]['functionCall'];
4034 } elseif (isset(self::$phpSpreadsheetFunctions[$functionName])) {
4035 $expectedArgumentCount = self::$phpSpreadsheetFunctions[$functionName]['argumentCount'];
4036 $functionCall = self::$phpSpreadsheetFunctions[$functionName]['functionCall'];
4037 } else { // did we somehow push a non-function on the stack? this should never happen
4038 return $this->raiseFormulaError('Formula Error: Internal error, non-function on stack');
4039 }
4040 // Check the argument count
4041 $argumentCountError = false;
4042 $expectedArgumentCountString = null;
4043 if (is_numeric($expectedArgumentCount)) {
4044 if ($expectedArgumentCount < 0) {
4045 if ($argumentCount > abs($expectedArgumentCount)) {
4046 $argumentCountError = true;
4047 $expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount);
4048 }
4049 } else {
4050 if ($argumentCount != $expectedArgumentCount) {
4051 $argumentCountError = true;
4052 $expectedArgumentCountString = $expectedArgumentCount;
4053 }
4054 }
4055 } elseif ($expectedArgumentCount != '*') {
4056 $isOperandOrFunction = preg_match('/(\d*)([-+,])(\d*)/', $expectedArgumentCount, $argMatch);
4057 switch ($argMatch[2]) {
4058 case '+':
4059 if ($argumentCount < $argMatch[1]) {
4060 $argumentCountError = true;
4061 $expectedArgumentCountString = $argMatch[1] . ' or more ';
4062 }
4063
4064 break;
4065 case '-':
4066 if (($argumentCount < $argMatch[1]) || ($argumentCount > $argMatch[3])) {
4067 $argumentCountError = true;
4068 $expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3];
4069 }
4070
4071 break;
4072 case ',':
4073 if (($argumentCount != $argMatch[1]) && ($argumentCount != $argMatch[3])) {
4074 $argumentCountError = true;
4075 $expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3];
4076 }
4077
4078 break;
4079 }
4080 }
4081 if ($argumentCountError) {
4082 return $this->raiseFormulaError("Formula Error: Wrong number of arguments for $functionName() function: $argumentCount given, " . $expectedArgumentCountString . ' expected');
4083 }
4084 }
4085 ++$index;
4086 } elseif ($opCharacter == ',') { // Is this the separator for function arguments?
4087 if (
4088 !empty($pendingStoreKey) &&
4089 $parenthesisDepthMap[$pendingStoreKey] == 0
4090 ) {
4091 // We must go to the IF next argument
4092 if ($expectingConditionMap[$pendingStoreKey]) {
4093 $expectingConditionMap[$pendingStoreKey] = false;
4094 $expectingThenMap[$pendingStoreKey] = true;
4095 } elseif ($expectingThenMap[$pendingStoreKey]) {
4096 $expectingThenMap[$pendingStoreKey] = false;
4097 $expectingElseMap[$pendingStoreKey] = true;
4098 } elseif ($expectingElseMap[$pendingStoreKey]) {
4099 return $this->raiseFormulaError('Reaching fourth argument of an IF');
4100 }
4101 }
4102 while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
4103 if ($o2 === null) {
4104 return $this->raiseFormulaError('Formula Error: Unexpected ,');
4105 }
4106 $output[] = $o2; // pop the argument expression stuff and push onto the output
4107 }
4108 // If we've a comma when we're expecting an operand, then what we actually have is a null operand;
4109 // so push a null onto the stack
4110 if (($expectingOperand) || (!$expectingOperator)) {
4111 $output[] = ['type' => 'NULL Value', 'value' => self::$excelConstants['NULL'], 'reference' => null];
4112 }
4113 // make sure there was a function
4114 $d = $stack->last(2);
4115 if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/miu', $d['value'], $matches)) {
4116 return $this->raiseFormulaError('Formula Error: Unexpected ,');
4117 }
4118 $d = $stack->pop();
4119 $itemStoreKey = $d['storeKey'] ?? null;
4120 $itemOnlyIf = $d['onlyIf'] ?? null;
4121 $itemOnlyIfNot = $d['onlyIfNot'] ?? null;
4122 $stack->push($d['type'], ++$d['value'], $d['reference'], $itemStoreKey, $itemOnlyIf, $itemOnlyIfNot); // increment the argument count
4123 $stack->push('Brace', '(', null, $itemStoreKey, $itemOnlyIf, $itemOnlyIfNot); // put the ( back on, we'll need to pop back to it again
4124 $expectingOperator = false;
4125 $expectingOperand = true;
4126 ++$index;
4127 } elseif ($opCharacter == '(' && !$expectingOperator) {
4128 if (!empty($pendingStoreKey)) { // Branch pruning: we go deeper
4129 ++$parenthesisDepthMap[$pendingStoreKey];
4130 }
4131 $stack->push('Brace', '(', null, $currentCondition, $currentOnlyIf, $currentOnlyIf);
4132 ++$index;
4133 } elseif ($isOperandOrFunction && !$expectingOperator) { // do we now have a function/variable/number?
4134 $expectingOperator = true;
4135 $expectingOperand = false;
4136 $val = $match[1];
4137 $length = strlen($val);
4138
4139 if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/miu', $val, $matches)) {
4140 $val = preg_replace('/\s/u', '', $val);
4141 if (isset(self::$phpSpreadsheetFunctions[strtoupper($matches[1])]) || isset(self::$controlFunctions[strtoupper($matches[1])])) { // it's a function
4142 $valToUpper = strtoupper($val);
4143 } else {
4144 $valToUpper = 'NAME.ERROR(';
4145 }
4146 // here $matches[1] will contain values like "IF"
4147 // and $val "IF("
4148 if ($this->branchPruningEnabled && ($valToUpper == 'IF(')) { // we handle a new if
4149 $pendingStoreKey = $this->getUnusedBranchStoreKey();
4150 $pendingStoreKeysStack[] = $pendingStoreKey;
4151 $expectingConditionMap[$pendingStoreKey] = true;
4152 $parenthesisDepthMap[$pendingStoreKey] = 0;
4153 } else { // this is not an if but we go deeper
4154 if (!empty($pendingStoreKey) && array_key_exists($pendingStoreKey, $parenthesisDepthMap)) {
4155 ++$parenthesisDepthMap[$pendingStoreKey];
4156 }
4157 }
4158
4159 $stack->push('Function', $valToUpper, null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
4160 // tests if the function is closed right after opening
4161 $ax = preg_match('/^\s*\‍)/u', substr($formula, $index + $length));
4162 if ($ax) {
4163 $stack->push('Operand Count for Function ' . $valToUpper . ')', 0, null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
4164 $expectingOperator = true;
4165 } else {
4166 $stack->push('Operand Count for Function ' . $valToUpper . ')', 1, null, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
4167 $expectingOperator = false;
4168 }
4169 $stack->push('Brace', '(');
4170 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) {
4171 // Watch for this case-change when modifying to allow cell references in different worksheets...
4172 // Should only be applied to the actual cell column, not the worksheet name
4173 // If the last entry on the stack was a : operator, then we have a cell range reference
4174 $testPrevOp = $stack->last(1);
4175 if ($testPrevOp !== null && $testPrevOp['value'] === ':') {
4176 // If we have a worksheet reference, then we're playing with a 3D reference
4177 if ($matches[2] == '') {
4178 // Otherwise, we 'inherit' the worksheet reference from the start cell reference
4179 // The start of the cell range reference should be the last entry in $output
4180 $rangeStartCellRef = $output[count($output) - 1]['value'];
4181 preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $rangeStartCellRef, $rangeStartMatches);
4182 if ($rangeStartMatches[2] > '') {
4183 $val = $rangeStartMatches[2] . '!' . $val;
4184 }
4185 } else {
4186 $rangeStartCellRef = $output[count($output) - 1]['value'];
4187 preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $rangeStartCellRef, $rangeStartMatches);
4188 if ($rangeStartMatches[2] !== $matches[2]) {
4189 return $this->raiseFormulaError('3D Range references are not yet supported');
4190 }
4191 }
4192 } elseif (strpos($val, '!') === false && $pCellParent !== null) {
4193 $worksheet = $pCellParent->getTitle();
4194 $val = "'{$worksheet}'!{$val}";
4195 }
4196
4197 $outputItem = $stack->getStackItem('Cell Reference', $val, $val, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
4198
4199 $output[] = $outputItem;
4200 } else { // it's a variable, constant, string, number or boolean
4201 $localeConstant = false;
4202 $stackItemType = 'Value';
4203 $stackItemReference = null;
4204
4205 // If the last entry on the stack was a : operator, then we may have a row or column range reference
4206 $testPrevOp = $stack->last(1);
4207 if ($testPrevOp !== null && $testPrevOp['value'] === ':') {
4208 $stackItemType = 'Cell Reference';
4209 $startRowColRef = $output[count($output) - 1]['value'];
4210 [$rangeWS1, $startRowColRef] = Worksheet::extractSheetTitle($startRowColRef, true);
4211 $rangeSheetRef = $rangeWS1;
4212 if ($rangeWS1 !== '') {
4213 $rangeWS1 .= '!';
4214 }
4215 $rangeSheetRef = trim($rangeSheetRef, "'");
4216 [$rangeWS2, $val] = Worksheet::extractSheetTitle($val, true);
4217 if ($rangeWS2 !== '') {
4218 $rangeWS2 .= '!';
4219 } else {
4220 $rangeWS2 = $rangeWS1;
4221 }
4222
4223 $refSheet = $pCellParent;
4224 if ($pCellParent !== null && $rangeSheetRef !== '' && $rangeSheetRef !== $pCellParent->getTitle()) {
4225 $refSheet = $pCellParent->getParent()->getSheetByName($rangeSheetRef);
4226 }
4227
4228 if (ctype_digit($val) && $val <= 1048576) {
4229 // Row range
4230 $stackItemType = 'Row Reference';
4231 $endRowColRef = ($refSheet !== null) ? $refSheet->getHighestDataColumn($val) : 'XFD'; // Max 16,384 columns for Excel2007
4232 $val = "{$rangeWS2}{$endRowColRef}{$val}";
4233 } elseif (ctype_alpha($val) && strlen($val) <= 3) {
4234 // Column range
4235 $stackItemType = 'Column Reference';
4236 $endRowColRef = ($refSheet !== null) ? $refSheet->getHighestDataRow($val) : 1048576; // Max 1,048,576 rows for Excel2007
4237 $val = "{$rangeWS2}{$val}{$endRowColRef}";
4238 }
4239 $stackItemReference = $val;
4240 } elseif ($opCharacter == self::FORMULA_STRING_QUOTE) {
4241 // UnEscape any quotes within the string
4242 $val = self::wrapResult(str_replace('""', self::FORMULA_STRING_QUOTE, self::unwrapResult($val)));
4243 } elseif (isset(self::$excelConstants[trim(strtoupper($val))])) {
4244 $stackItemType = 'Constant';
4245 $excelConstant = trim(strtoupper($val));
4246 $val = self::$excelConstants[$excelConstant];
4247 } elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$localeBoolean)) !== false) {
4248 $stackItemType = 'Constant';
4249 $val = self::$excelConstants[$localeConstant];
4250 } elseif (
4251 preg_match('/^' . self::CALCULATION_REGEXP_ROW_RANGE . '/miu', substr($formula, $index), $rowRangeReference)
4252 ) {
4253 $val = $rowRangeReference[1];
4254 $length = strlen($rowRangeReference[1]);
4255 $stackItemType = 'Row Reference';
4256 $column = 'A';
4257 if (($testPrevOp !== null && $testPrevOp['value'] === ':') && $pCellParent !== null) {
4258 $column = $pCellParent->getHighestDataColumn($val);
4259 }
4260 $val = "{$rowRangeReference[2]}{$column}{$rowRangeReference[7]}";
4261 $stackItemReference = $val;
4262 } elseif (
4263 preg_match('/^' . self::CALCULATION_REGEXP_COLUMN_RANGE . '/miu', substr($formula, $index), $columnRangeReference)
4264 ) {
4265 $val = $columnRangeReference[1];
4266 $length = strlen($val);
4267 $stackItemType = 'Column Reference';
4268 $row = '1';
4269 if (($testPrevOp !== null && $testPrevOp['value'] === ':') && $pCellParent !== null) {
4270 $row = $pCellParent->getHighestDataRow($val);
4271 }
4272 $val = "{$val}{$row}";
4273 $stackItemReference = $val;
4274 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_DEFINEDNAME . '.*/miu', $val, $match)) {
4275 $stackItemType = 'Defined Name';
4276 $stackItemReference = $val;
4277 } elseif (is_numeric($val)) {
4278 if ((strpos($val, '.') !== false) || (stripos($val, 'e') !== false) || ($val > PHP_INT_MAX) || ($val < -PHP_INT_MAX)) {
4279 $val = (float) $val;
4280 } else {
4281 $val = (int) $val;
4282 }
4283 }
4284
4285 $details = $stack->getStackItem($stackItemType, $val, $stackItemReference, $currentCondition, $currentOnlyIf, $currentOnlyIfNot);
4286 if ($localeConstant) {
4287 $details['localeValue'] = $localeConstant;
4288 }
4289 $output[] = $details;
4290 }
4291 $index += $length;
4292 } elseif ($opCharacter == '$') { // absolute row or column range
4293 ++$index;
4294 } elseif ($opCharacter == ')') { // miscellaneous error checking
4295 if ($expectingOperand) {
4296 $output[] = ['type' => 'NULL Value', 'value' => self::$excelConstants['NULL'], 'reference' => null];
4297 $expectingOperand = false;
4298 $expectingOperator = true;
4299 } else {
4300 return $this->raiseFormulaError("Formula Error: Unexpected ')'");
4301 }
4302 } elseif (isset(self::$operators[$opCharacter]) && !$expectingOperator) {
4303 return $this->raiseFormulaError("Formula Error: Unexpected operator '$opCharacter'");
4304 } else { // I don't even want to know what you did to get here
4305 return $this->raiseFormulaError('Formula Error: An unexpected error occurred');
4306 }
4307 // Test for end of formula string
4308 if ($index == strlen($formula)) {
4309 // Did we end with an operator?.
4310 // Only valid for the % unary operator
4311 if ((isset(self::$operators[$opCharacter])) && ($opCharacter != '%')) {
4312 return $this->raiseFormulaError("Formula Error: Operator '$opCharacter' has no operands");
4313 }
4314
4315 break;
4316 }
4317 // Ignore white space
4318 while (($formula[$index] == "\n") || ($formula[$index] == "\r")) {
4319 ++$index;
4320 }
4321
4322 if ($formula[$index] == ' ') {
4323 while ($formula[$index] == ' ') {
4324 ++$index;
4325 }
4326
4327 // If we're expecting an operator, but only have a space between the previous and next operands (and both are
4328 // Cell References) then we have an INTERSECTION operator
4329 if (
4330 ($expectingOperator) &&
4331 ((preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match)) &&
4332 ($output[count($output) - 1]['type'] == 'Cell Reference') ||
4333 (preg_match('/^' . self::CALCULATION_REGEXP_DEFINEDNAME . '.*/miu', substr($formula, $index), $match)) &&
4334 ($output[count($output) - 1]['type'] == 'Defined Name' || $output[count($output) - 1]['type'] == 'Value')
4335 )
4336 ) {
4337 while (
4338 $stack->count() > 0 &&
4339 ($o2 = $stack->last()) &&
4340 isset(self::$operators[$o2['value']]) &&
4341 @(self::$operatorAssociativity[$opCharacter] ? self::$operatorPrecedence[$opCharacter] < self::$operatorPrecedence[$o2['value']] : self::$operatorPrecedence[$opCharacter] <= self::$operatorPrecedence[$o2['value']])
4342 ) {
4343 $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
4344 }
4345 $stack->push('Binary Operator', '|'); // Put an Intersect Operator on the stack
4346 $expectingOperator = false;
4347 }
4348 }
4349 }
4350
4351 while (($op = $stack->pop()) !== null) { // pop everything off the stack and push onto output
4352 if ((is_array($op) && $op['value'] == '(') || ($op === '(')) {
4353 return $this->raiseFormulaError("Formula Error: Expecting ')'"); // if there are any opening braces on the stack, then braces were unbalanced
4354 }
4355 $output[] = $op;
4356 }
4357
4358 return $output;
4359 }
4360
4361 private static function dataTestReference(&$operandData)
4362 {
4363 $operand = $operandData['value'];
4364 if (($operandData['reference'] === null) && (is_array($operand))) {
4365 $rKeys = array_keys($operand);
4366 $rowKey = array_shift($rKeys);
4367 $cKeys = array_keys(array_keys($operand[$rowKey]));
4368 $colKey = array_shift($cKeys);
4369 if (ctype_upper($colKey)) {
4370 $operandData['reference'] = $colKey . $rowKey;
4371 }
4372 }
4373
4374 return $operand;
4375 }
4376
4377 // evaluate postfix notation
4378
4385 private function processTokenStack($tokens, $cellID = null, ?Cell $pCell = null)
4386 {
4387 if ($tokens == false) {
4388 return false;
4389 }
4390
4391 // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
4392 // so we store the parent cell collection so that we can re-attach it when necessary
4393 $pCellWorksheet = ($pCell !== null) ? $pCell->getWorksheet() : null;
4394 $pCellParent = ($pCell !== null) ? $pCell->getParent() : null;
4395 $stack = new Stack();
4396
4397 // Stores branches that have been pruned
4398 $fakedForBranchPruning = [];
4399 // help us to know when pruning ['branchTestId' => true/false]
4400 $branchStore = [];
4401 // Loop through each token in turn
4402 foreach ($tokens as $tokenData) {
4403 $token = $tokenData['value'];
4404
4405 // Branch pruning: skip useless resolutions
4406 $storeKey = $tokenData['storeKey'] ?? null;
4407 if ($this->branchPruningEnabled && isset($tokenData['onlyIf'])) {
4408 $onlyIfStoreKey = $tokenData['onlyIf'];
4409 $storeValue = $branchStore[$onlyIfStoreKey] ?? null;
4410 $storeValueAsBool = ($storeValue === null) ?
4411 true : (bool) Functions::flattenSingleValue($storeValue);
4412 if (is_array($storeValue)) {
4413 $wrappedItem = end($storeValue);
4414 $storeValue = end($wrappedItem);
4415 }
4416
4417 if (
4418 isset($storeValue)
4419 && (
4420 !$storeValueAsBool
4421 || Functions::isError($storeValue)
4422 || ($storeValue === 'Pruned branch')
4423 )
4424 ) {
4425 // If branching value is not true, we don't need to compute
4426 if (!isset($fakedForBranchPruning['onlyIf-' . $onlyIfStoreKey])) {
4427 $stack->push('Value', 'Pruned branch (only if ' . $onlyIfStoreKey . ') ' . $token);
4428 $fakedForBranchPruning['onlyIf-' . $onlyIfStoreKey] = true;
4429 }
4430
4431 if (isset($storeKey)) {
4432 // We are processing an if condition
4433 // We cascade the pruning to the depending branches
4434 $branchStore[$storeKey] = 'Pruned branch';
4435 $fakedForBranchPruning['onlyIfNot-' . $storeKey] = true;
4436 $fakedForBranchPruning['onlyIf-' . $storeKey] = true;
4437 }
4438
4439 continue;
4440 }
4441 }
4442
4443 if ($this->branchPruningEnabled && isset($tokenData['onlyIfNot'])) {
4444 $onlyIfNotStoreKey = $tokenData['onlyIfNot'];
4445 $storeValue = $branchStore[$onlyIfNotStoreKey] ?? null;
4446 $storeValueAsBool = ($storeValue === null) ?
4447 true : (bool) Functions::flattenSingleValue($storeValue);
4448 if (is_array($storeValue)) {
4449 $wrappedItem = end($storeValue);
4450 $storeValue = end($wrappedItem);
4451 }
4452 if (
4453 isset($storeValue)
4454 && (
4455 $storeValueAsBool
4456 || Functions::isError($storeValue)
4457 || ($storeValue === 'Pruned branch'))
4458 ) {
4459 // If branching value is true, we don't need to compute
4460 if (!isset($fakedForBranchPruning['onlyIfNot-' . $onlyIfNotStoreKey])) {
4461 $stack->push('Value', 'Pruned branch (only if not ' . $onlyIfNotStoreKey . ') ' . $token);
4462 $fakedForBranchPruning['onlyIfNot-' . $onlyIfNotStoreKey] = true;
4463 }
4464
4465 if (isset($storeKey)) {
4466 // We are processing an if condition
4467 // We cascade the pruning to the depending branches
4468 $branchStore[$storeKey] = 'Pruned branch';
4469 $fakedForBranchPruning['onlyIfNot-' . $storeKey] = true;
4470 $fakedForBranchPruning['onlyIf-' . $storeKey] = true;
4471 }
4472
4473 continue;
4474 }
4475 }
4476
4477 // 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
4478 if (isset(self::$binaryOperators[$token])) {
4479 // We must have two operands, error if we don't
4480 if (($operand2Data = $stack->pop()) === null) {
4481 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
4482 }
4483 if (($operand1Data = $stack->pop()) === null) {
4484 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
4485 }
4486
4487 $operand1 = self::dataTestReference($operand1Data);
4488 $operand2 = self::dataTestReference($operand2Data);
4489
4490 // Log what we're doing
4491 if ($token == ':') {
4492 $this->debugLog->writeDebugLog('Evaluating Range ', $this->showValue($operand1Data['reference']), ' ', $token, ' ', $this->showValue($operand2Data['reference']));
4493 } else {
4494 $this->debugLog->writeDebugLog('Evaluating ', $this->showValue($operand1), ' ', $token, ' ', $this->showValue($operand2));
4495 }
4496
4497 // Process the operation in the appropriate manner
4498 switch ($token) {
4499 // Comparison (Boolean) Operators
4500 case '>': // Greater than
4501 case '<': // Less than
4502 case '>=': // Greater than or Equal to
4503 case '<=': // Less than or Equal to
4504 case '=': // Equality
4505 case '<>': // Inequality
4506 $result = $this->executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
4507 if (isset($storeKey)) {
4508 $branchStore[$storeKey] = $result;
4509 }
4510
4511 break;
4512 // Binary Operators
4513 case ':': // Range
4514 if (strpos($operand1Data['reference'], '!') !== false) {
4515 [$sheet1, $operand1Data['reference']] = Worksheet::extractSheetTitle($operand1Data['reference'], true);
4516 } else {
4517 $sheet1 = ($pCellParent !== null) ? $pCellWorksheet->getTitle() : '';
4518 }
4519
4520 [$sheet2, $operand2Data['reference']] = Worksheet::extractSheetTitle($operand2Data['reference'], true);
4521 if (empty($sheet2)) {
4522 $sheet2 = $sheet1;
4523 }
4524
4525 if ($sheet1 == $sheet2) {
4526 if ($operand1Data['reference'] === null) {
4527 if ((trim($operand1Data['value']) != '') && (is_numeric($operand1Data['value']))) {
4528 $operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value'];
4529 } elseif (trim($operand1Data['reference']) == '') {
4530 $operand1Data['reference'] = $pCell->getCoordinate();
4531 } else {
4532 $operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow();
4533 }
4534 }
4535 if ($operand2Data['reference'] === null) {
4536 if ((trim($operand2Data['value']) != '') && (is_numeric($operand2Data['value']))) {
4537 $operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value'];
4538 } elseif (trim($operand2Data['reference']) == '') {
4539 $operand2Data['reference'] = $pCell->getCoordinate();
4540 } else {
4541 $operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow();
4542 }
4543 }
4544
4545 $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference']));
4546 $oCol = $oRow = [];
4547 foreach ($oData as $oDatum) {
4548 $oCR = Coordinate::coordinateFromString($oDatum);
4549 $oCol[] = Coordinate::columnIndexFromString($oCR[0]) - 1;
4550 $oRow[] = $oCR[1];
4551 }
4552 $cellRef = Coordinate::stringFromColumnIndex(min($oCol) + 1) . min($oRow) . ':' . Coordinate::stringFromColumnIndex(max($oCol) + 1) . max($oRow);
4553 if ($pCellParent !== null) {
4554 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($sheet1), false);
4555 } else {
4556 return $this->raiseFormulaError('Unable to access Cell Reference');
4557 }
4558
4559 $stack->push('Cell Reference', $cellValue, $cellRef);
4560 } else {
4561 $stack->push('Error', Functions::REF(), null);
4562 }
4563
4564 break;
4565 case '+': // Addition
4566 $result = $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'plusEquals', $stack);
4567 if (isset($storeKey)) {
4568 $branchStore[$storeKey] = $result;
4569 }
4570
4571 break;
4572 case '-': // Subtraction
4573 $result = $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'minusEquals', $stack);
4574 if (isset($storeKey)) {
4575 $branchStore[$storeKey] = $result;
4576 }
4577
4578 break;
4579 case '*': // Multiplication
4580 $result = $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'arrayTimesEquals', $stack);
4581 if (isset($storeKey)) {
4582 $branchStore[$storeKey] = $result;
4583 }
4584
4585 break;
4586 case '/': // Division
4587 $result = $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'arrayRightDivide', $stack);
4588 if (isset($storeKey)) {
4589 $branchStore[$storeKey] = $result;
4590 }
4591
4592 break;
4593 case '^': // Exponential
4594 $result = $this->executeNumericBinaryOperation($operand1, $operand2, $token, 'power', $stack);
4595 if (isset($storeKey)) {
4596 $branchStore[$storeKey] = $result;
4597 }
4598
4599 break;
4600 case '&': // Concatenation
4601 // If either of the operands is a matrix, we need to treat them both as matrices
4602 // (converting the other operand to a matrix if need be); then perform the required
4603 // matrix operation
4604 if (is_bool($operand1)) {
4605 $operand1 = ($operand1) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
4606 }
4607 if (is_bool($operand2)) {
4608 $operand2 = ($operand2) ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
4609 }
4610 if ((is_array($operand1)) || (is_array($operand2))) {
4611 // Ensure that both operands are arrays/matrices
4612 self::checkMatrixOperands($operand1, $operand2, 2);
4613
4614 try {
4615 // Convert operand 1 from a PHP array to a matrix
4616 $matrix = new Shared\JAMA\Matrix($operand1);
4617 // Perform the required operation against the operand 1 matrix, passing in operand 2
4618 $matrixResult = $matrix->concat($operand2);
4619 $result = $matrixResult->getArray();
4620 } catch (\Exception $ex) {
4621 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
4622 $result = '#VALUE!';
4623 }
4624 } else {
4625 $result = self::FORMULA_STRING_QUOTE . str_replace('""', self::FORMULA_STRING_QUOTE, self::unwrapResult($operand1) . self::unwrapResult($operand2)) . self::FORMULA_STRING_QUOTE;
4626 }
4627 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
4628 $stack->push('Value', $result);
4629
4630 if (isset($storeKey)) {
4631 $branchStore[$storeKey] = $result;
4632 }
4633
4634 break;
4635 case '|': // Intersect
4636 $rowIntersect = array_intersect_key($operand1, $operand2);
4637 $cellIntersect = $oCol = $oRow = [];
4638 foreach (array_keys($rowIntersect) as $row) {
4639 $oRow[] = $row;
4640 foreach ($rowIntersect[$row] as $col => $data) {
4641 $oCol[] = Coordinate::columnIndexFromString($col) - 1;
4642 $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]);
4643 }
4644 }
4645 if (count(Functions::flattenArray($cellIntersect)) === 0) {
4646 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($cellIntersect));
4647 $stack->push('Error', Functions::null(), null);
4648 } else {
4649 $cellRef = Coordinate::stringFromColumnIndex(min($oCol) + 1) . min($oRow) . ':' .
4650 Coordinate::stringFromColumnIndex(max($oCol) + 1) . max($oRow);
4651 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($cellIntersect));
4652 $stack->push('Value', $cellIntersect, $cellRef);
4653 }
4654
4655 break;
4656 }
4657
4658 // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
4659 } elseif (($token === '~') || ($token === '%')) {
4660 if (($arg = $stack->pop()) === null) {
4661 return $this->raiseFormulaError('Internal error - Operand value missing from stack');
4662 }
4663 $arg = $arg['value'];
4664 if ($token === '~') {
4665 $this->debugLog->writeDebugLog('Evaluating Negation of ', $this->showValue($arg));
4666 $multiplier = -1;
4667 } else {
4668 $this->debugLog->writeDebugLog('Evaluating Percentile of ', $this->showValue($arg));
4669 $multiplier = 0.01;
4670 }
4671 if (is_array($arg)) {
4672 self::checkMatrixOperands($arg, $multiplier, 2);
4673
4674 try {
4675 $matrix1 = new Shared\JAMA\Matrix($arg);
4676 $matrixResult = $matrix1->arrayTimesEquals($multiplier);
4677 $result = $matrixResult->getArray();
4678 } catch (\Exception $ex) {
4679 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
4680 $result = '#VALUE!';
4681 }
4682 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
4683 $stack->push('Value', $result);
4684 if (isset($storeKey)) {
4685 $branchStore[$storeKey] = $result;
4686 }
4687 } else {
4688 $this->executeNumericBinaryOperation($multiplier, $arg, '*', 'arrayTimesEquals', $stack);
4689 }
4690 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token ?? '', $matches)) {
4691 $cellRef = null;
4692
4693 if (isset($matches[8])) {
4694 if ($pCell === null) {
4695 // We can't access the range, so return a REF error
4696 $cellValue = Functions::REF();
4697 } else {
4698 $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10];
4699 if ($matches[2] > '') {
4700 $matches[2] = trim($matches[2], "\"'");
4701 if ((strpos($matches[2], '[') !== false) || (strpos($matches[2], ']') !== false)) {
4702 // It's a Reference to an external spreadsheet (not currently supported)
4703 return $this->raiseFormulaError('Unable to access External Workbook');
4704 }
4705 $matches[2] = trim($matches[2], "\"'");
4706 $this->debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
4707 if ($pCellParent !== null) {
4708 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
4709 } else {
4710 return $this->raiseFormulaError('Unable to access Cell Reference');
4711 }
4712 $this->debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
4713 } else {
4714 $this->debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
4715 if ($pCellParent !== null) {
4716 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
4717 } else {
4718 return $this->raiseFormulaError('Unable to access Cell Reference');
4719 }
4720 $this->debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
4721 }
4722 }
4723 } else {
4724 if ($pCell === null) {
4725 // We can't access the cell, so return a REF error
4726 $cellValue = Functions::REF();
4727 } else {
4728 $cellRef = $matches[6] . $matches[7];
4729 if ($matches[2] > '') {
4730 $matches[2] = trim($matches[2], "\"'");
4731 if ((strpos($matches[2], '[') !== false) || (strpos($matches[2], ']') !== false)) {
4732 // It's a Reference to an external spreadsheet (not currently supported)
4733 return $this->raiseFormulaError('Unable to access External Workbook');
4734 }
4735 $this->debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
4736 if ($pCellParent !== null) {
4737 $cellSheet = $this->spreadsheet->getSheetByName($matches[2]);
4738 if ($cellSheet && $cellSheet->cellExists($cellRef)) {
4739 $cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
4740 $pCell->attach($pCellParent);
4741 } else {
4742 $cellRef = ($cellSheet !== null) ? "{$matches[2]}!{$cellRef}" : $cellRef;
4743 $cellValue = null;
4744 }
4745 } else {
4746 return $this->raiseFormulaError('Unable to access Cell Reference');
4747 }
4748 $this->debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
4749 } else {
4750 $this->debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
4751 if ($pCellParent->has($cellRef)) {
4752 $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
4753 $pCell->attach($pCellParent);
4754 } else {
4755 $cellValue = null;
4756 }
4757 $this->debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
4758 }
4759 }
4760 }
4761
4762 $stack->push('Cell Value', $cellValue, $cellRef);
4763 if (isset($storeKey)) {
4764 $branchStore[$storeKey] = $cellValue;
4765 }
4766
4767 // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
4768 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/miu', $token ?? '', $matches)) {
4769 if ($pCellParent) {
4770 $pCell->attach($pCellParent);
4771 }
4772
4773 $functionName = $matches[1];
4774 $argCount = $stack->pop();
4775 $argCount = $argCount['value'];
4776 if ($functionName != 'MKMATRIX') {
4777 $this->debugLog->writeDebugLog('Evaluating Function ', self::localeFunc($functionName), '() with ', (($argCount == 0) ? 'no' : $argCount), ' argument', (($argCount == 1) ? '' : 's'));
4778 }
4779 if ((isset(self::$phpSpreadsheetFunctions[$functionName])) || (isset(self::$controlFunctions[$functionName]))) { // function
4780 $passByReference = false;
4781 $passCellReference = false;
4782 $functionCall = null;
4783 if (isset(self::$phpSpreadsheetFunctions[$functionName])) {
4784 $functionCall = self::$phpSpreadsheetFunctions[$functionName]['functionCall'];
4785 $passByReference = isset(self::$phpSpreadsheetFunctions[$functionName]['passByReference']);
4786 $passCellReference = isset(self::$phpSpreadsheetFunctions[$functionName]['passCellReference']);
4787 } elseif (isset(self::$controlFunctions[$functionName])) {
4788 $functionCall = self::$controlFunctions[$functionName]['functionCall'];
4789 $passByReference = isset(self::$controlFunctions[$functionName]['passByReference']);
4790 $passCellReference = isset(self::$controlFunctions[$functionName]['passCellReference']);
4791 }
4792 // get the arguments for this function
4793 $args = $argArrayVals = [];
4794 for ($i = 0; $i < $argCount; ++$i) {
4795 $arg = $stack->pop();
4796 $a = $argCount - $i - 1;
4797 if (
4798 ($passByReference) &&
4799 (isset(self::$phpSpreadsheetFunctions[$functionName]['passByReference'][$a])) &&
4800 (self::$phpSpreadsheetFunctions[$functionName]['passByReference'][$a])
4801 ) {
4802 if ($arg['reference'] === null) {
4803 $args[] = $cellID;
4804 if ($functionName != 'MKMATRIX') {
4805 $argArrayVals[] = $this->showValue($cellID);
4806 }
4807 } else {
4808 $args[] = $arg['reference'];
4809 if ($functionName != 'MKMATRIX') {
4810 $argArrayVals[] = $this->showValue($arg['reference']);
4811 }
4812 }
4813 } else {
4814 $args[] = self::unwrapResult($arg['value']);
4815 if ($functionName != 'MKMATRIX') {
4816 $argArrayVals[] = $this->showValue($arg['value']);
4817 }
4818 }
4819 }
4820
4821 // Reverse the order of the arguments
4822 krsort($args);
4823
4824 if (($passByReference) && ($argCount == 0)) {
4825 $args[] = $cellID;
4826 $argArrayVals[] = $this->showValue($cellID);
4827 }
4828
4829 if ($functionName != 'MKMATRIX') {
4830 if ($this->debugLog->getWriteDebugLog()) {
4831 krsort($argArrayVals);
4832 $this->debugLog->writeDebugLog('Evaluating ', self::localeFunc($functionName), '( ', implode(self::$localeArgumentSeparator . ' ', Functions::flattenArray($argArrayVals)), ' )');
4833 }
4834 }
4835
4836 // Process the argument with the appropriate function call
4837 $args = $this->addCellReference($args, $passCellReference, $functionCall, $pCell);
4838
4839 if (!is_array($functionCall)) {
4840 foreach ($args as &$arg) {
4841 $arg = Functions::flattenSingleValue($arg);
4842 }
4843 unset($arg);
4844 }
4845
4846 $result = call_user_func_array($functionCall, $args);
4847
4848 if ($functionName != 'MKMATRIX') {
4849 $this->debugLog->writeDebugLog('Evaluation Result for ', self::localeFunc($functionName), '() function call is ', $this->showTypeDetails($result));
4850 }
4851 $stack->push('Value', self::wrapResult($result));
4852 if (isset($storeKey)) {
4853 $branchStore[$storeKey] = $result;
4854 }
4855 }
4856 } else {
4857 // if the token is a number, boolean, string or an Excel error, push it onto the stack
4858 if (isset(self::$excelConstants[strtoupper($token ?? '')])) {
4859 $excelConstant = strtoupper($token);
4860 $stack->push('Constant Value', self::$excelConstants[$excelConstant]);
4861 if (isset($storeKey)) {
4862 $branchStore[$storeKey] = self::$excelConstants[$excelConstant];
4863 }
4864 $this->debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->showTypeDetails(self::$excelConstants[$excelConstant]));
4865 } elseif ((is_numeric($token)) || ($token === null) || (is_bool($token)) || ($token == '') || ($token[0] == self::FORMULA_STRING_QUOTE) || ($token[0] == '#')) {
4866 $stack->push('Value', $token);
4867 if (isset($storeKey)) {
4868 $branchStore[$storeKey] = $token;
4869 }
4870 // if the token is a named range or formula, evaluate it and push the result onto the stack
4871 } elseif (preg_match('/^' . self::CALCULATION_REGEXP_DEFINEDNAME . '$/miu', $token, $matches)) {
4872 $definedName = $matches[6];
4873 if ($pCell === null || $pCellWorksheet === null) {
4874 return $this->raiseFormulaError("undefined name '$token'");
4875 }
4876
4877 $this->debugLog->writeDebugLog('Evaluating Defined Name ', $definedName);
4878 $namedRange = DefinedName::resolveName($definedName, $pCellWorksheet);
4879 if ($namedRange === null) {
4880 return $this->raiseFormulaError("undefined name '$definedName'");
4881 }
4882
4883 $result = $this->evaluateDefinedName($pCell, $namedRange, $pCellWorksheet, $stack);
4884 if (isset($storeKey)) {
4885 $branchStore[$storeKey] = $result;
4886 }
4887 } else {
4888 return $this->raiseFormulaError("undefined name '$token'");
4889 }
4890 }
4891 }
4892 // when we're out of tokens, the stack should have a single element, the final result
4893 if ($stack->count() != 1) {
4894 return $this->raiseFormulaError('internal error');
4895 }
4896 $output = $stack->pop();
4897 $output = $output['value'];
4898
4899 return $output;
4900 }
4901
4902 private function validateBinaryOperand(&$operand, &$stack)
4903 {
4904 if (is_array($operand)) {
4905 if ((count($operand, COUNT_RECURSIVE) - count($operand)) == 1) {
4906 do {
4907 $operand = array_pop($operand);
4908 } while (is_array($operand));
4909 }
4910 }
4911 // Numbers, matrices and booleans can pass straight through, as they're already valid
4912 if (is_string($operand)) {
4913 // We only need special validations for the operand if it is a string
4914 // Start by stripping off the quotation marks we use to identify true excel string values internally
4915 if ($operand > '' && $operand[0] == self::FORMULA_STRING_QUOTE) {
4916 $operand = self::unwrapResult($operand);
4917 }
4918 // If the string is a numeric value, we treat it as a numeric, so no further testing
4919 if (!is_numeric($operand)) {
4920 // If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
4921 if ($operand > '' && $operand[0] == '#') {
4922 $stack->push('Value', $operand);
4923 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($operand));
4924
4925 return false;
4926 } elseif (!Shared\StringHelper::convertToNumberIfFraction($operand)) {
4927 // If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
4928 $stack->push('Error', '#VALUE!');
4929 $this->debugLog->writeDebugLog('Evaluation Result is a ', $this->showTypeDetails('#VALUE!'));
4930
4931 return false;
4932 }
4933 }
4934 }
4935
4936 // return a true if the value of the operand is one that we can use in normal binary operations
4937 return true;
4938 }
4939
4948 private function executeArrayComparison($cellID, $operand1, $operand2, $operation, Stack &$stack, bool $recursingArrays)
4949 {
4950 $result = [];
4951 if (!is_array($operand2)) {
4952 // Operand 1 is an array, Operand 2 is a scalar
4953 foreach ($operand1 as $x => $operandData) {
4954 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operandData), ' ', $operation, ' ', $this->showValue($operand2));
4955 $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2, $operation, $stack);
4956 $r = $stack->pop();
4957 $result[$x] = $r['value'];
4958 }
4959 } elseif (!is_array($operand1)) {
4960 // Operand 1 is a scalar, Operand 2 is an array
4961 foreach ($operand2 as $x => $operandData) {
4962 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operand1), ' ', $operation, ' ', $this->showValue($operandData));
4963 $this->executeBinaryComparisonOperation($cellID, $operand1, $operandData, $operation, $stack);
4964 $r = $stack->pop();
4965 $result[$x] = $r['value'];
4966 }
4967 } else {
4968 // Operand 1 and Operand 2 are both arrays
4969 if (!$recursingArrays) {
4970 self::checkMatrixOperands($operand1, $operand2, 2);
4971 }
4972 foreach ($operand1 as $x => $operandData) {
4973 $this->debugLog->writeDebugLog('Evaluating Comparison ', $this->showValue($operandData), ' ', $operation, ' ', $this->showValue($operand2[$x]));
4974 $this->executeBinaryComparisonOperation($cellID, $operandData, $operand2[$x], $operation, $stack, true);
4975 $r = $stack->pop();
4976 $result[$x] = $r['value'];
4977 }
4978 }
4979 // Log the result details
4980 $this->debugLog->writeDebugLog('Comparison Evaluation Result is ', $this->showTypeDetails($result));
4981 // And push the result onto the stack
4982 $stack->push('Array', $result);
4983
4984 return $result;
4985 }
4986
4996 private function executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, Stack &$stack, $recursingArrays = false)
4997 {
4998 // If we're dealing with matrix operations, we want a matrix result
4999 if ((is_array($operand1)) || (is_array($operand2))) {
5000 return $this->executeArrayComparison($cellID, $operand1, $operand2, $operation, $stack, $recursingArrays);
5001 }
5002
5003 // Simple validate the two operands if they are string values
5004 if (is_string($operand1) && $operand1 > '' && $operand1[0] == self::FORMULA_STRING_QUOTE) {
5005 $operand1 = self::unwrapResult($operand1);
5006 }
5007 if (is_string($operand2) && $operand2 > '' && $operand2[0] == self::FORMULA_STRING_QUOTE) {
5008 $operand2 = self::unwrapResult($operand2);
5009 }
5010
5011 // Use case insensitive comparaison if not OpenOffice mode
5013 if (is_string($operand1)) {
5014 $operand1 = Shared\StringHelper::strToUpper($operand1);
5015 }
5016 if (is_string($operand2)) {
5017 $operand2 = Shared\StringHelper::strToUpper($operand2);
5018 }
5019 }
5020
5021 $useLowercaseFirstComparison = is_string($operand1) && is_string($operand2) && Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE;
5022
5023 // execute the necessary operation
5024 switch ($operation) {
5025 // Greater than
5026 case '>':
5027 if ($useLowercaseFirstComparison) {
5028 $result = $this->strcmpLowercaseFirst($operand1, $operand2) > 0;
5029 } else {
5030 $result = ($operand1 > $operand2);
5031 }
5032
5033 break;
5034 // Less than
5035 case '<':
5036 if ($useLowercaseFirstComparison) {
5037 $result = $this->strcmpLowercaseFirst($operand1, $operand2) < 0;
5038 } else {
5039 $result = ($operand1 < $operand2);
5040 }
5041
5042 break;
5043 // Equality
5044 case '=':
5045 if (is_numeric($operand1) && is_numeric($operand2)) {
5046 $result = (abs($operand1 - $operand2) < $this->delta);
5047 } else {
5048 $result = $this->strcmpAllowNull($operand1, $operand2) == 0;
5049 }
5050
5051 break;
5052 // Greater than or equal
5053 case '>=':
5054 if (is_numeric($operand1) && is_numeric($operand2)) {
5055 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 > $operand2));
5056 } elseif ($useLowercaseFirstComparison) {
5057 $result = $this->strcmpLowercaseFirst($operand1, $operand2) >= 0;
5058 } else {
5059 $result = $this->strcmpAllowNull($operand1, $operand2) >= 0;
5060 }
5061
5062 break;
5063 // Less than or equal
5064 case '<=':
5065 if (is_numeric($operand1) && is_numeric($operand2)) {
5066 $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 < $operand2));
5067 } elseif ($useLowercaseFirstComparison) {
5068 $result = $this->strcmpLowercaseFirst($operand1, $operand2) <= 0;
5069 } else {
5070 $result = $this->strcmpAllowNull($operand1, $operand2) <= 0;
5071 }
5072
5073 break;
5074 // Inequality
5075 case '<>':
5076 if (is_numeric($operand1) && is_numeric($operand2)) {
5077 $result = (abs($operand1 - $operand2) > 1E-14);
5078 } else {
5079 $result = $this->strcmpAllowNull($operand1, $operand2) != 0;
5080 }
5081
5082 break;
5083
5084 default:
5085 throw new Exception('Unsupported binary comparison operation');
5086 }
5087
5088 // Log the result details
5089 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
5090 // And push the result onto the stack
5091 $stack->push('Value', $result);
5092
5093 return $result;
5094 }
5095
5104 private function strcmpLowercaseFirst($str1, $str2)
5105 {
5106 $inversedStr1 = Shared\StringHelper::strCaseReverse($str1);
5107 $inversedStr2 = Shared\StringHelper::strCaseReverse($str2);
5108
5109 return strcmp($inversedStr1 ?? '', $inversedStr2 ?? '');
5110 }
5111
5120 private function strcmpAllowNull($str1, $str2)
5121 {
5122 return strcmp($str1 ?? '', $str2 ?? '');
5123 }
5124
5134 private function executeNumericBinaryOperation($operand1, $operand2, $operation, $matrixFunction, &$stack)
5135 {
5136 // Validate the two operands
5137 if (!$this->validateBinaryOperand($operand1, $stack)) {
5138 return false;
5139 }
5140 if (!$this->validateBinaryOperand($operand2, $stack)) {
5141 return false;
5142 }
5143
5144 // If either of the operands is a matrix, we need to treat them both as matrices
5145 // (converting the other operand to a matrix if need be); then perform the required
5146 // matrix operation
5147 if ((is_array($operand1)) || (is_array($operand2))) {
5148 // Ensure that both operands are arrays/matrices of the same size
5149 self::checkMatrixOperands($operand1, $operand2, 2);
5150
5151 try {
5152 // Convert operand 1 from a PHP array to a matrix
5153 $matrix = new Shared\JAMA\Matrix($operand1);
5154 // Perform the required operation against the operand 1 matrix, passing in operand 2
5155 $matrixResult = $matrix->$matrixFunction($operand2);
5156 $result = $matrixResult->getArray();
5157 } catch (\Exception $ex) {
5158 $this->debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
5159 $result = '#VALUE!';
5160 }
5161 } else {
5162 if (
5164 ((is_string($operand1) && !is_numeric($operand1) && strlen($operand1) > 0) ||
5165 (is_string($operand2) && !is_numeric($operand2) && strlen($operand2) > 0))
5166 ) {
5168 } else {
5169 // If we're dealing with non-matrix operations, execute the necessary operation
5170 switch ($operation) {
5171 // Addition
5172 case '+':
5173 $result = $operand1 + $operand2;
5174
5175 break;
5176 // Subtraction
5177 case '-':
5178 $result = $operand1 - $operand2;
5179
5180 break;
5181 // Multiplication
5182 case '*':
5183 $result = $operand1 * $operand2;
5184
5185 break;
5186 // Division
5187 case '/':
5188 if ($operand2 == 0) {
5189 // Trap for Divide by Zero error
5190 $stack->push('Error', '#DIV/0!');
5191 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails('#DIV/0!'));
5192
5193 return false;
5194 }
5195 $result = $operand1 / $operand2;
5196
5197 break;
5198 // Power
5199 case '^':
5200 $result = $operand1 ** $operand2;
5201
5202 break;
5203
5204 default:
5205 throw new Exception('Unsupported numeric binary operation');
5206 }
5207 }
5208 }
5209
5210 // Log the result details
5211 $this->debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
5212 // And push the result onto the stack
5213 $stack->push('Value', $result);
5214
5215 return $result;
5216 }
5217
5218 // trigger an error, but nicely, if need be
5219 protected function raiseFormulaError($errorMessage)
5220 {
5221 $this->formulaError = $errorMessage;
5222 $this->cyclicReferenceStack->clear();
5223 if (!$this->suppressFormulaErrors) {
5224 throw new Exception($errorMessage);
5225 }
5226 trigger_error($errorMessage, E_USER_ERROR);
5227
5228 return false;
5229 }
5230
5240 public function extractCellRange(&$pRange = 'A1', ?Worksheet $pSheet = null, $resetLog = true)
5241 {
5242 // Return value
5243 $returnValue = [];
5244
5245 if ($pSheet !== null) {
5246 $pSheetName = $pSheet->getTitle();
5247
5248 if (strpos($pRange, '!') !== false) {
5249 [$pSheetName, $pRange] = Worksheet::extractSheetTitle($pRange, true);
5250 $pSheet = $this->spreadsheet->getSheetByName($pSheetName);
5251 }
5252
5253 // Extract range
5254 $aReferences = Coordinate::extractAllCellReferencesInRange($pRange);
5255 $pRange = $pSheetName . '!' . $pRange;
5256 if (!isset($aReferences[1])) {
5257 $currentCol = '';
5258 $currentRow = 0;
5259 // Single cell in range
5260 sscanf($aReferences[0], '%[A-Z]%d', $currentCol, $currentRow);
5261 if ($pSheet->cellExists($aReferences[0])) {
5262 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
5263 } else {
5264 $returnValue[$currentRow][$currentCol] = null;
5265 }
5266 } else {
5267 // Extract cell data for all cells in the range
5268 foreach ($aReferences as $reference) {
5269 $currentCol = '';
5270 $currentRow = 0;
5271 // Extract range
5272 sscanf($reference, '%[A-Z]%d', $currentCol, $currentRow);
5273 if ($pSheet->cellExists($reference)) {
5274 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
5275 } else {
5276 $returnValue[$currentRow][$currentCol] = null;
5277 }
5278 }
5279 }
5280 }
5281
5282 return $returnValue;
5283 }
5284
5294 public function extractNamedRange(&$pRange = 'A1', ?Worksheet $pSheet = null, $resetLog = true)
5295 {
5296 // Return value
5297 $returnValue = [];
5298
5299 if ($pSheet !== null) {
5300 $pSheetName = $pSheet->getTitle();
5301 if (strpos($pRange, '!') !== false) {
5302 [$pSheetName, $pRange] = Worksheet::extractSheetTitle($pRange, true);
5303 $pSheet = $this->spreadsheet->getSheetByName($pSheetName);
5304 }
5305
5306 // Named range?
5307 $namedRange = DefinedName::resolveName($pRange, $pSheet);
5308 if ($namedRange === null) {
5309 return Functions::REF();
5310 }
5311
5312 $pSheet = $namedRange->getWorksheet();
5313 $pRange = $namedRange->getValue();
5314 $splitRange = Coordinate::splitRange($pRange);
5315 // Convert row and column references
5316 if (ctype_alpha($splitRange[0][0])) {
5317 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
5318 } elseif (ctype_digit($splitRange[0][0])) {
5319 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
5320 }
5321
5322 // Extract range
5323 $aReferences = Coordinate::extractAllCellReferencesInRange($pRange);
5324 if (!isset($aReferences[1])) {
5325 // Single cell (or single column or row) in range
5326 [$currentCol, $currentRow] = Coordinate::coordinateFromString($aReferences[0]);
5327 if ($pSheet->cellExists($aReferences[0])) {
5328 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
5329 } else {
5330 $returnValue[$currentRow][$currentCol] = null;
5331 }
5332 } else {
5333 // Extract cell data for all cells in the range
5334 foreach ($aReferences as $reference) {
5335 // Extract range
5336 [$currentCol, $currentRow] = Coordinate::coordinateFromString($reference);
5337 if ($pSheet->cellExists($reference)) {
5338 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
5339 } else {
5340 $returnValue[$currentRow][$currentCol] = null;
5341 }
5342 }
5343 }
5344 }
5345
5346 return $returnValue;
5347 }
5348
5356 public function isImplemented($pFunction)
5357 {
5358 $pFunction = strtoupper($pFunction);
5359 $notImplemented = !isset(self::$phpSpreadsheetFunctions[$pFunction]) || (is_array(self::$phpSpreadsheetFunctions[$pFunction]['functionCall']) && self::$phpSpreadsheetFunctions[$pFunction]['functionCall'][1] === 'DUMMY');
5360
5361 return !$notImplemented;
5362 }
5363
5367 public function getFunctions(): array
5368 {
5370 }
5371
5378 {
5379 $returnValue = [];
5380 foreach (self::$phpSpreadsheetFunctions as $functionName => $function) {
5381 if ($this->isImplemented($functionName)) {
5382 $returnValue[] = $functionName;
5383 }
5384 }
5385
5386 return $returnValue;
5387 }
5388
5397 private function addCellReference(array $args, $passCellReference, $functionCall, ?Cell $pCell = null)
5398 {
5399 if ($passCellReference) {
5400 if (is_array($functionCall)) {
5401 $className = $functionCall[0];
5402 $methodName = $functionCall[1];
5403
5404 $reflectionMethod = new ReflectionMethod($className, $methodName);
5405 $argumentCount = count($reflectionMethod->getParameters());
5406 while (count($args) < $argumentCount - 1) {
5407 $args[] = null;
5408 }
5409 }
5410
5411 $args[] = $pCell;
5412 }
5413
5414 return $args;
5415 }
5416
5417 private function getUnusedBranchStoreKey()
5418 {
5419 $storeKeyValue = 'storeKey-' . $this->branchStoreKeyCounter;
5421
5422 return $storeKeyValue;
5423 }
5424
5425 private function getTokensAsString($tokens)
5426 {
5427 $tokensStr = array_map(function ($token) {
5428 $value = $token['value'] ?? 'no value';
5429 while (is_array($value)) {
5430 $value = array_pop($value);
5431 }
5432
5433 return $value;
5434 }, $tokens);
5435
5436 return '[ ' . implode(' | ', $tokensStr) . ' ]';
5437 }
5438
5442 private function evaluateDefinedName(Cell $pCell, DefinedName $namedRange, Worksheet $pCellWorksheet, Stack $stack)
5443 {
5444 $definedNameScope = $namedRange->getScope();
5445 if ($definedNameScope !== null && $definedNameScope !== $pCellWorksheet) {
5446 // The defined name isn't in our current scope, so #REF
5448 $stack->push('Error', $result, $namedRange->getName());
5449
5450 return $result;
5451 }
5452
5453 $definedNameValue = $namedRange->getValue();
5454 $definedNameType = $namedRange->isFormula() ? 'Formula' : 'Range';
5455 $definedNameWorksheet = $namedRange->getWorksheet();
5456
5457 if ($definedNameValue[0] !== '=') {
5458 $definedNameValue = '=' . $definedNameValue;
5459 }
5460
5461 $this->debugLog->writeDebugLog("Defined Name is a {$definedNameType} with a value of {$definedNameValue}");
5462
5463 $recursiveCalculationCell = ($definedNameWorksheet !== null && $definedNameWorksheet !== $pCellWorksheet)
5464 ? $definedNameWorksheet->getCell('A1')
5465 : $pCell;
5466 $recursiveCalculationCellAddress = $recursiveCalculationCell->getCoordinate();
5467
5468 // Adjust relative references in ranges and formulae so that we execute the calculation for the correct rows and columns
5469 $definedNameValue = self::$referenceHelper->updateFormulaReferencesAnyWorksheet(
5470 $definedNameValue,
5472 $pCell->getRow() - 1
5473 );
5474
5475 $this->debugLog->writeDebugLog("Value adjusted for relative references is {$definedNameValue}");
5476
5477 $recursiveCalculator = new self($this->spreadsheet);
5478 $recursiveCalculator->getDebugLog()->setWriteDebugLog($this->getDebugLog()->getWriteDebugLog());
5479 $recursiveCalculator->getDebugLog()->setEchoDebugLog($this->getDebugLog()->getEchoDebugLog());
5480 $result = $recursiveCalculator->_calculateFormulaValue($definedNameValue, $recursiveCalculationCellAddress, $recursiveCalculationCell);
5481
5482 if ($this->getDebugLog()->getWriteDebugLog()) {
5483 $this->debugLog->mergeDebugLog(array_slice($recursiveCalculator->getDebugLog()->getLog(), 3));
5484 $this->debugLog->writeDebugLog("Evaluation Result for Named {$definedNameType} {$namedRange->getName()} is {$this->showTypeDetails($result)}");
5485 }
5486
5487 $stack->push('Defined Name', $result, $namedRange->getName());
5488
5489 return $result;
5490 }
5491}
$result
$filename
Definition: buildRTE.php:89
An exception for terminatinating execution or to throw for unit testing.
getLocale()
Get the currently defined locale code.
clearCalculationCacheForWorksheet($worksheetName)
Clear calculation cache for a specified worksheet.
executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, Stack &$stack, $recursingArrays=false)
setBranchPruningEnabled($enabled)
Enable/disable calculation cache.
executeNumericBinaryOperation($operand1, $operand2, $operation, $matrixFunction, &$stack)
getCalculationCacheEnabled()
Is calculation caching enabled?
isImplemented($pFunction)
Is a specific function implemented?
static translateFormula(array $from, array $to, $formula, $fromSeparator, $toSeparator)
disableCalculationCache()
Disable calculation cache.
strcmpLowercaseFirst($str1, $str2)
Compare two strings in the same way as strcmp() except that lowercase come before uppercase letters.
enableCalculationCache()
Enable calculation cache.
executeArrayComparison($cellID, $operand1, $operand2, $operation, Stack &$stack, bool $recursingArrays)
evaluateDefinedName(Cell $pCell, DefinedName $namedRange, Worksheet $pCellWorksheet, Stack $stack)
static resizeMatricesExtend(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
Ensure that paired matrix operands are both matrices of the same size.
strcmpAllowNull($str1, $str2)
PHP8.1 deprecates passing null to strcmp.
static setArrayReturnType($returnType)
Set the Array Return Type (Array or Value of first element in the array).
getImplementedFunctionNames()
Get a list of implemented Excel function names.
renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName)
Rename calculation cache for a specified worksheet.
processTokenStack($tokens, $cellID=null, ?Cell $pCell=null)
addCellReference(array $args, $passCellReference, $functionCall, ?Cell $pCell=null)
Add cell reference if needed while making sure that it is the last argument.
internalParseFormula($formula, ?Cell $pCell=null)
showValue($value)
Format details of an operand for display in the log (based on operand type).
static getTRUE()
Return the locale-specific translation of TRUE.
getValueFromCache(string $cellReference, &$cellValue)
setCalculationCacheEnabled($pValue)
Enable/disable calculation cache.
static getFALSE()
Return the locale-specific translation of FALSE.
extractNamedRange(&$pRange='A1', ?Worksheet $pSheet=null, $resetLog=true)
Extract range values.
static wrapResult($value)
Wrap string values in quotes.
getDebugLog()
Get the Logger for this calculation engine instance.
getLocaleFile(string $localeDir, string $locale, string $language, string $file)
calculateFormula($formula, $cellID=null, ?Cell $pCell=null)
Calculate the value of a formula.
setLocale(string $locale)
Set the locale code.
static checkMatrixOperands(&$operand1, &$operand2, $resize=1)
Ensure that paired matrix operands are both matrices and of the same size.
_calculateFormulaValue($formula, $cellID=null, ?Cell $pCell=null)
Parse a cell formula and calculate its value.
__construct(?Spreadsheet $spreadsheet=null)
static resizeMatricesShrink(&$matrix1, &$matrix2, $matrix1Rows, $matrix1Columns, $matrix2Rows, $matrix2Columns)
Ensure that paired matrix operands are both matrices of the same size.
calculateCellValue(?Cell $pCell=null, $resetLog=true)
Calculate the value of a cell formula.
parseFormula($formula)
Validate and parse a formula string.
getFunctions()
Get a list of all implemented functions as an array of function objects.
showTypeDetails($value)
Format type and details of an operand for display in the log (based on operand type).
static getArrayReturnType()
Return the Array Return Type (Array or Value of first element in the array).
flushInstance()
Flush the calculation cache for any existing instance of this class but only if a Calculation instanc...
static translateSeparator($fromSeparator, $toSeparator, $formula, &$inBraces)
static getMatrixDimensions(array &$matrix)
Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0,...
static unwrapResult($value)
Remove quotes used as a wrapper to identify string values.
static getInstance(?Spreadsheet $spreadsheet=null)
Get an instance of this class.
calculate(?Cell $pCell=null)
Calculate cell value (using formula from a cell ID) Retained for backward compatibility.
extractCellRange(&$pRange='A1', ?Worksheet $pSheet=null, $resetLog=true)
Extract range values.
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:583
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:649
static getCompatibilityMode()
Return the current Compatibility Mode.
Definition: Functions.php:93
push( $type, $value, $reference=null, $storeKey=null, $onlyIf=null, $onlyIfNot=null)
Push a new entry onto the stack.
Definition: Stack.php:45
pop()
Pop the last entry from the stack.
Definition: Stack.php:99
getColumn()
Get cell coordinate column.
Definition: Cell.php:120
getCoordinate()
Get cell coordinate.
Definition: Cell.php:140
getRow()
Get cell coordinate row.
Definition: Cell.php:130
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static coordinateFromString($pCoordinateString)
Coordinate from string.
Definition: Coordinate.php:32
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
static extractAllCellReferencesInRange($cellRange)
Extract all cell references in range, which may be comprised of multiple cell ranges.
Definition: Coordinate.php:338
static resolveName(string $pDefinedName, Worksheet $pSheet, string $sheetName='')
Resolve a named range to a regular cell range or formula.
getValue()
Get range or formula value.
isFormula()
Identify whether this is a named range or a named formula.
static getInstance()
Get an instance of this class.
static strToUpper($pValue)
Convert a UTF-8 encoded string to upper case.
static strCaseReverse($pValue)
Reverse the case of a string, so that all uppercase characters become lowercase and all lowercase cha...
static convertToNumberIfFraction(&$operand)
Identify whether a string contains a fractional numeric value, and convert it to a numeric if it is.
$x
Definition: complexTest.php:9
for( $i=6;$i< 13;$i++) for($i=1; $i< 13; $i++) $d
Definition: date.php:296
$i
Definition: disco.tpl.php:19
$r
Definition: example_031.php:79
$index
Definition: metadata.php:60
$matrix
Definition: test.php:18
$row
$from
$data
Definition: bench.php:6