Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5 use PhpOffice\PhpSpreadsheet\Cell\Cell; 6 7 class Functions 8 { 9 const PRECISION = 8.88E-016; 10 11 /** 12 * 2 / PI. 13 */ 14 const M_2DIVPI = 0.63661977236758134307553505349006; 15 16 /** constants */ 17 const COMPATIBILITY_EXCEL = 'Excel'; 18 const COMPATIBILITY_GNUMERIC = 'Gnumeric'; 19 const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc'; 20 21 const RETURNDATE_PHP_NUMERIC = 'P'; 22 const RETURNDATE_UNIX_TIMESTAMP = 'P'; 23 const RETURNDATE_PHP_OBJECT = 'O'; 24 const RETURNDATE_PHP_DATETIME_OBJECT = 'O'; 25 const RETURNDATE_EXCEL = 'E'; 26 27 /** 28 * Compatibility mode to use for error checking and responses. 29 * 30 * @var string 31 */ 32 protected static $compatibilityMode = self::COMPATIBILITY_EXCEL; 33 34 /** 35 * Data Type to use when returning date values. 36 * 37 * @var string 38 */ 39 protected static $returnDateType = self::RETURNDATE_EXCEL; 40 41 /** 42 * List of error codes. 43 * 44 * @var array 45 */ 46 protected static $errorCodes = [ 47 'null' => '#NULL!', 48 'divisionbyzero' => '#DIV/0!', 49 'value' => '#VALUE!', 50 'reference' => '#REF!', 51 'name' => '#NAME?', 52 'num' => '#NUM!', 53 'na' => '#N/A', 54 'gettingdata' => '#GETTING_DATA', 55 ]; 56 57 /** 58 * Set the Compatibility Mode. 59 * 60 * @param string $compatibilityMode Compatibility Mode 61 * Permitted values are: 62 * Functions::COMPATIBILITY_EXCEL 'Excel' 63 * Functions::COMPATIBILITY_GNUMERIC 'Gnumeric' 64 * Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc' 65 * 66 * @return bool (Success or Failure) 67 */ 68 public static function setCompatibilityMode($compatibilityMode) 69 { 70 if ( 71 ($compatibilityMode == self::COMPATIBILITY_EXCEL) || 72 ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) || 73 ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE) 74 ) { 75 self::$compatibilityMode = $compatibilityMode; 76 77 return true; 78 } 79 80 return false; 81 } 82 83 /** 84 * Return the current Compatibility Mode. 85 * 86 * @return string Compatibility Mode 87 * Possible Return values are: 88 * Functions::COMPATIBILITY_EXCEL 'Excel' 89 * Functions::COMPATIBILITY_GNUMERIC 'Gnumeric' 90 * Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc' 91 */ 92 public static function getCompatibilityMode() 93 { 94 return self::$compatibilityMode; 95 } 96 97 /** 98 * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object). 99 * 100 * @param string $returnDateType Return Date Format 101 * Permitted values are: 102 * Functions::RETURNDATE_UNIX_TIMESTAMP 'P' 103 * Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O' 104 * Functions::RETURNDATE_EXCEL 'E' 105 * 106 * @return bool Success or failure 107 */ 108 public static function setReturnDateType($returnDateType) 109 { 110 if ( 111 ($returnDateType == self::RETURNDATE_UNIX_TIMESTAMP) || 112 ($returnDateType == self::RETURNDATE_PHP_DATETIME_OBJECT) || 113 ($returnDateType == self::RETURNDATE_EXCEL) 114 ) { 115 self::$returnDateType = $returnDateType; 116 117 return true; 118 } 119 120 return false; 121 } 122 123 /** 124 * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object). 125 * 126 * @return string Return Date Format 127 * Possible Return values are: 128 * Functions::RETURNDATE_UNIX_TIMESTAMP 'P' 129 * Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O' 130 * Functions::RETURNDATE_EXCEL 'E' 131 */ 132 public static function getReturnDateType() 133 { 134 return self::$returnDateType; 135 } 136 137 /** 138 * DUMMY. 139 * 140 * @return string #Not Yet Implemented 141 */ 142 public static function DUMMY() 143 { 144 return '#Not Yet Implemented'; 145 } 146 147 /** 148 * DIV0. 149 * 150 * @return string #Not Yet Implemented 151 */ 152 public static function DIV0() 153 { 154 return self::$errorCodes['divisionbyzero']; 155 } 156 157 /** 158 * NA. 159 * 160 * Excel Function: 161 * =NA() 162 * 163 * Returns the error value #N/A 164 * #N/A is the error value that means "no value is available." 165 * 166 * @return string #N/A! 167 */ 168 public static function NA() 169 { 170 return self::$errorCodes['na']; 171 } 172 173 /** 174 * NaN. 175 * 176 * Returns the error value #NUM! 177 * 178 * @return string #NUM! 179 */ 180 public static function NAN() 181 { 182 return self::$errorCodes['num']; 183 } 184 185 /** 186 * NAME. 187 * 188 * Returns the error value #NAME? 189 * 190 * @return string #NAME? 191 */ 192 public static function NAME() 193 { 194 return self::$errorCodes['name']; 195 } 196 197 /** 198 * REF. 199 * 200 * Returns the error value #REF! 201 * 202 * @return string #REF! 203 */ 204 public static function REF() 205 { 206 return self::$errorCodes['reference']; 207 } 208 209 /** 210 * NULL. 211 * 212 * Returns the error value #NULL! 213 * 214 * @return string #NULL! 215 */ 216 public static function null() 217 { 218 return self::$errorCodes['null']; 219 } 220 221 /** 222 * VALUE. 223 * 224 * Returns the error value #VALUE! 225 * 226 * @return string #VALUE! 227 */ 228 public static function VALUE() 229 { 230 return self::$errorCodes['value']; 231 } 232 233 public static function isMatrixValue($idx) 234 { 235 return (substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0); 236 } 237 238 public static function isValue($idx) 239 { 240 return substr_count($idx, '.') == 0; 241 } 242 243 public static function isCellValue($idx) 244 { 245 return substr_count($idx, '.') > 1; 246 } 247 248 public static function ifCondition($condition) 249 { 250 $condition = self::flattenSingleValue($condition); 251 252 if ($condition === '') { 253 $condition = '=""'; 254 } 255 256 if (!is_string($condition) || !in_array($condition[0], ['>', '<', '='])) { 257 if (!is_numeric($condition)) { 258 $condition = Calculation::wrapResult(strtoupper($condition)); 259 } 260 261 return str_replace('""""', '""', '=' . $condition); 262 } 263 preg_match('/(=|<[>=]?|>=?)(.*)/', $condition, $matches); 264 [, $operator, $operand] = $matches; 265 266 if (is_numeric(trim($operand, '"'))) { 267 $operand = trim($operand, '"'); 268 } elseif (!is_numeric($operand)) { 269 $operand = str_replace('"', '""', $operand); 270 $operand = Calculation::wrapResult(strtoupper($operand)); 271 } 272 273 return str_replace('""""', '""', $operator . $operand); 274 } 275 276 /** 277 * ERROR_TYPE. 278 * 279 * @param mixed $value Value to check 280 * 281 * @return bool 282 */ 283 public static function errorType($value = '') 284 { 285 $value = self::flattenSingleValue($value); 286 287 $i = 1; 288 foreach (self::$errorCodes as $errorCode) { 289 if ($value === $errorCode) { 290 return $i; 291 } 292 ++$i; 293 } 294 295 return self::NA(); 296 } 297 298 /** 299 * IS_BLANK. 300 * 301 * @param mixed $value Value to check 302 * 303 * @return bool 304 */ 305 public static function isBlank($value = null) 306 { 307 if ($value !== null) { 308 $value = self::flattenSingleValue($value); 309 } 310 311 return $value === null; 312 } 313 314 /** 315 * IS_ERR. 316 * 317 * @param mixed $value Value to check 318 * 319 * @return bool 320 */ 321 public static function isErr($value = '') 322 { 323 $value = self::flattenSingleValue($value); 324 325 return self::isError($value) && (!self::isNa(($value))); 326 } 327 328 /** 329 * IS_ERROR. 330 * 331 * @param mixed $value Value to check 332 * 333 * @return bool 334 */ 335 public static function isError($value = '') 336 { 337 $value = self::flattenSingleValue($value); 338 339 if (!is_string($value)) { 340 return false; 341 } 342 343 return in_array($value, self::$errorCodes); 344 } 345 346 /** 347 * IS_NA. 348 * 349 * @param mixed $value Value to check 350 * 351 * @return bool 352 */ 353 public static function isNa($value = '') 354 { 355 $value = self::flattenSingleValue($value); 356 357 return $value === self::NA(); 358 } 359 360 /** 361 * IS_EVEN. 362 * 363 * @param mixed $value Value to check 364 * 365 * @return bool|string 366 */ 367 public static function isEven($value = null) 368 { 369 $value = self::flattenSingleValue($value); 370 371 if ($value === null) { 372 return self::NAME(); 373 } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) { 374 return self::VALUE(); 375 } 376 377 return $value % 2 == 0; 378 } 379 380 /** 381 * IS_ODD. 382 * 383 * @param mixed $value Value to check 384 * 385 * @return bool|string 386 */ 387 public static function isOdd($value = null) 388 { 389 $value = self::flattenSingleValue($value); 390 391 if ($value === null) { 392 return self::NAME(); 393 } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) { 394 return self::VALUE(); 395 } 396 397 return abs($value) % 2 == 1; 398 } 399 400 /** 401 * IS_NUMBER. 402 * 403 * @param mixed $value Value to check 404 * 405 * @return bool 406 */ 407 public static function isNumber($value = null) 408 { 409 $value = self::flattenSingleValue($value); 410 411 if (is_string($value)) { 412 return false; 413 } 414 415 return is_numeric($value); 416 } 417 418 /** 419 * IS_LOGICAL. 420 * 421 * @param mixed $value Value to check 422 * 423 * @return bool 424 */ 425 public static function isLogical($value = null) 426 { 427 $value = self::flattenSingleValue($value); 428 429 return is_bool($value); 430 } 431 432 /** 433 * IS_TEXT. 434 * 435 * @param mixed $value Value to check 436 * 437 * @return bool 438 */ 439 public static function isText($value = null) 440 { 441 $value = self::flattenSingleValue($value); 442 443 return is_string($value) && !self::isError($value); 444 } 445 446 /** 447 * IS_NONTEXT. 448 * 449 * @param mixed $value Value to check 450 * 451 * @return bool 452 */ 453 public static function isNonText($value = null) 454 { 455 return !self::isText($value); 456 } 457 458 /** 459 * N. 460 * 461 * Returns a value converted to a number 462 * 463 * @param null|mixed $value The value you want converted 464 * 465 * @return number N converts values listed in the following table 466 * If value is or refers to N returns 467 * A number That number 468 * A date The serial number of that date 469 * TRUE 1 470 * FALSE 0 471 * An error value The error value 472 * Anything else 0 473 */ 474 public static function n($value = null) 475 { 476 while (is_array($value)) { 477 $value = array_shift($value); 478 } 479 480 switch (gettype($value)) { 481 case 'double': 482 case 'float': 483 case 'integer': 484 return $value; 485 case 'boolean': 486 return (int) $value; 487 case 'string': 488 // Errors 489 if ((strlen($value) > 0) && ($value[0] == '#')) { 490 return $value; 491 } 492 493 break; 494 } 495 496 return 0; 497 } 498 499 /** 500 * TYPE. 501 * 502 * Returns a number that identifies the type of a value 503 * 504 * @param null|mixed $value The value you want tested 505 * 506 * @return number N converts values listed in the following table 507 * If value is or refers to N returns 508 * A number 1 509 * Text 2 510 * Logical Value 4 511 * An error value 16 512 * Array or Matrix 64 513 */ 514 public static function TYPE($value = null) 515 { 516 $value = self::flattenArrayIndexed($value); 517 if (is_array($value) && (count($value) > 1)) { 518 end($value); 519 $a = key($value); 520 // Range of cells is an error 521 if (self::isCellValue($a)) { 522 return 16; 523 // Test for Matrix 524 } elseif (self::isMatrixValue($a)) { 525 return 64; 526 } 527 } elseif (empty($value)) { 528 // Empty Cell 529 return 1; 530 } 531 $value = self::flattenSingleValue($value); 532 533 if (($value === null) || (is_float($value)) || (is_int($value))) { 534 return 1; 535 } elseif (is_bool($value)) { 536 return 4; 537 } elseif (is_array($value)) { 538 return 64; 539 } elseif (is_string($value)) { 540 // Errors 541 if ((strlen($value) > 0) && ($value[0] == '#')) { 542 return 16; 543 } 544 545 return 2; 546 } 547 548 return 0; 549 } 550 551 /** 552 * Convert a multi-dimensional array to a simple 1-dimensional array. 553 * 554 * @param array $array Array to be flattened 555 * 556 * @return array Flattened array 557 */ 558 public static function flattenArray($array) 559 { 560 if (!is_array($array)) { 561 return (array) $array; 562 } 563 564 $arrayValues = []; 565 foreach ($array as $value) { 566 if (is_array($value)) { 567 foreach ($value as $val) { 568 if (is_array($val)) { 569 foreach ($val as $v) { 570 $arrayValues[] = $v; 571 } 572 } else { 573 $arrayValues[] = $val; 574 } 575 } 576 } else { 577 $arrayValues[] = $value; 578 } 579 } 580 581 return $arrayValues; 582 } 583 584 /** 585 * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing. 586 * 587 * @param array $array Array to be flattened 588 * 589 * @return array Flattened array 590 */ 591 public static function flattenArrayIndexed($array) 592 { 593 if (!is_array($array)) { 594 return (array) $array; 595 } 596 597 $arrayValues = []; 598 foreach ($array as $k1 => $value) { 599 if (is_array($value)) { 600 foreach ($value as $k2 => $val) { 601 if (is_array($val)) { 602 foreach ($val as $k3 => $v) { 603 $arrayValues[$k1 . '.' . $k2 . '.' . $k3] = $v; 604 } 605 } else { 606 $arrayValues[$k1 . '.' . $k2] = $val; 607 } 608 } 609 } else { 610 $arrayValues[$k1] = $value; 611 } 612 } 613 614 return $arrayValues; 615 } 616 617 /** 618 * Convert an array to a single scalar value by extracting the first element. 619 * 620 * @param mixed $value Array or scalar value 621 * 622 * @return mixed 623 */ 624 public static function flattenSingleValue($value = '') 625 { 626 while (is_array($value)) { 627 $value = array_shift($value); 628 } 629 630 return $value; 631 } 632 633 /** 634 * ISFORMULA. 635 * 636 * @param mixed $cellReference The cell to check 637 * @param Cell $pCell The current cell (containing this formula) 638 * 639 * @return bool|string 640 */ 641 public static function isFormula($cellReference = '', ?Cell $pCell = null) 642 { 643 if ($pCell === null) { 644 return self::REF(); 645 } 646 647 preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches); 648 649 $cellReference = $matches[6] . $matches[7]; 650 $worksheetName = str_replace("''", "'", trim($matches[2], "'")); 651 652 $worksheet = (!empty($worksheetName)) 653 ? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName) 654 : $pCell->getWorksheet(); 655 656 return $worksheet->getCell($cellReference)->isFormula(); 657 } 658 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body