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