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