[Summary view]

Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

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