Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

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  }