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