[Summary view]

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