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