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