Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

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\Shared\Date;
   6  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   7  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
   8  
   9  class TextData
  10  {
  11      private static $invalidChars;
  12  
  13      private static function unicodeToOrd($character)
  14      {
  15          return unpack('V', iconv('UTF-8', 'UCS-4LE', $character))[1];
  16      }
  17  
  18      /**
  19       * CHARACTER.
  20       *
  21       * @param string $character Value
  22       *
  23       * @return string
  24       */
  25      public static function CHARACTER($character)
  26      {
  27          $character = Functions::flattenSingleValue($character);
  28  
  29          if ((!is_numeric($character)) || ($character < 0)) {
  30              return Functions::VALUE();
  31          }
  32  
  33          if (function_exists('iconv')) {
  34              return iconv('UCS-4LE', 'UTF-8', pack('V', $character));
  35          }
  36  
  37          return mb_convert_encoding('&#' . (int) $character . ';', 'UTF-8', 'HTML-ENTITIES');
  38      }
  39  
  40      /**
  41       * TRIMNONPRINTABLE.
  42       *
  43       * @param mixed $stringValue Value to check
  44       *
  45       * @return string
  46       */
  47      public static function TRIMNONPRINTABLE($stringValue = '')
  48      {
  49          $stringValue = Functions::flattenSingleValue($stringValue);
  50  
  51          if (is_bool($stringValue)) {
  52              return ($stringValue) ? Calculation::getTRUE() : Calculation::getFALSE();
  53          }
  54  
  55          if (self::$invalidChars === null) {
  56              self::$invalidChars = range(chr(0), chr(31));
  57          }
  58  
  59          if (is_string($stringValue) || is_numeric($stringValue)) {
  60              return str_replace(self::$invalidChars, '', trim($stringValue, "\x00..\x1F"));
  61          }
  62  
  63          return null;
  64      }
  65  
  66      /**
  67       * TRIMSPACES.
  68       *
  69       * @param mixed $stringValue Value to check
  70       *
  71       * @return string
  72       */
  73      public static function TRIMSPACES($stringValue = '')
  74      {
  75          $stringValue = Functions::flattenSingleValue($stringValue);
  76          if (is_bool($stringValue)) {
  77              return ($stringValue) ? Calculation::getTRUE() : Calculation::getFALSE();
  78          }
  79  
  80          if (is_string($stringValue) || is_numeric($stringValue)) {
  81              return trim(preg_replace('/ +/', ' ', trim($stringValue, ' ')), ' ');
  82          }
  83  
  84          return null;
  85      }
  86  
  87      private static function convertBooleanValue($value)
  88      {
  89          if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
  90              return (int) $value;
  91          }
  92  
  93          return ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
  94      }
  95  
  96      /**
  97       * ASCIICODE.
  98       *
  99       * @param string $characters Value
 100       *
 101       * @return int
 102       */
 103      public static function ASCIICODE($characters)
 104      {
 105          if (($characters === null) || ($characters === '')) {
 106              return Functions::VALUE();
 107          }
 108          $characters = Functions::flattenSingleValue($characters);
 109          if (is_bool($characters)) {
 110              $characters = self::convertBooleanValue($characters);
 111          }
 112  
 113          $character = $characters;
 114          if (mb_strlen($characters, 'UTF-8') > 1) {
 115              $character = mb_substr($characters, 0, 1, 'UTF-8');
 116          }
 117  
 118          return self::unicodeToOrd($character);
 119      }
 120  
 121      /**
 122       * CONCATENATE.
 123       *
 124       * @return string
 125       */
 126      public static function CONCATENATE(...$args)
 127      {
 128          $returnValue = '';
 129  
 130          // Loop through arguments
 131          $aArgs = Functions::flattenArray($args);
 132          foreach ($aArgs as $arg) {
 133              if (is_bool($arg)) {
 134                  $arg = self::convertBooleanValue($arg);
 135              }
 136              $returnValue .= $arg;
 137          }
 138  
 139          return $returnValue;
 140      }
 141  
 142      /**
 143       * DOLLAR.
 144       *
 145       * This function converts a number to text using currency format, with the decimals rounded to the specified place.
 146       * The format used is $#,##0.00_);($#,##0.00)..
 147       *
 148       * @param float $value The value to format
 149       * @param int $decimals The number of digits to display to the right of the decimal point.
 150       *                                    If decimals is negative, number is rounded to the left of the decimal point.
 151       *                                    If you omit decimals, it is assumed to be 2
 152       *
 153       * @return string
 154       */
 155      public static function DOLLAR($value = 0, $decimals = 2)
 156      {
 157          $value = Functions::flattenSingleValue($value);
 158          $decimals = $decimals === null ? 0 : Functions::flattenSingleValue($decimals);
 159  
 160          // Validate parameters
 161          if (!is_numeric($value) || !is_numeric($decimals)) {
 162              return Functions::NAN();
 163          }
 164          $decimals = floor($decimals);
 165  
 166          $mask = '$#,##0';
 167          if ($decimals > 0) {
 168              $mask .= '.' . str_repeat('0', $decimals);
 169          } else {
 170              $round = pow(10, abs($decimals));
 171              if ($value < 0) {
 172                  $round = 0 - $round;
 173              }
 174              $value = MathTrig::MROUND($value, $round);
 175          }
 176  
 177          return NumberFormat::toFormattedString($value, $mask);
 178      }
 179  
 180      /**
 181       * SEARCHSENSITIVE.
 182       *
 183       * @param string $needle The string to look for
 184       * @param string $haystack The string in which to look
 185       * @param int $offset Offset within $haystack
 186       *
 187       * @return string
 188       */
 189      public static function SEARCHSENSITIVE($needle, $haystack, $offset = 1)
 190      {
 191          $needle = Functions::flattenSingleValue($needle);
 192          $haystack = Functions::flattenSingleValue($haystack);
 193          $offset = Functions::flattenSingleValue($offset);
 194  
 195          if (!is_bool($needle)) {
 196              if (is_bool($haystack)) {
 197                  $haystack = ($haystack) ? Calculation::getTRUE() : Calculation::getFALSE();
 198              }
 199  
 200              if (($offset > 0) && (StringHelper::countCharacters($haystack) > $offset)) {
 201                  if (StringHelper::countCharacters($needle) === 0) {
 202                      return $offset;
 203                  }
 204  
 205                  $pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
 206                  if ($pos !== false) {
 207                      return ++$pos;
 208                  }
 209              }
 210          }
 211  
 212          return Functions::VALUE();
 213      }
 214  
 215      /**
 216       * SEARCHINSENSITIVE.
 217       *
 218       * @param string $needle The string to look for
 219       * @param string $haystack The string in which to look
 220       * @param int $offset Offset within $haystack
 221       *
 222       * @return string
 223       */
 224      public static function SEARCHINSENSITIVE($needle, $haystack, $offset = 1)
 225      {
 226          $needle = Functions::flattenSingleValue($needle);
 227          $haystack = Functions::flattenSingleValue($haystack);
 228          $offset = Functions::flattenSingleValue($offset);
 229  
 230          if (!is_bool($needle)) {
 231              if (is_bool($haystack)) {
 232                  $haystack = ($haystack) ? Calculation::getTRUE() : Calculation::getFALSE();
 233              }
 234  
 235              if (($offset > 0) && (StringHelper::countCharacters($haystack) > $offset)) {
 236                  if (StringHelper::countCharacters($needle) === 0) {
 237                      return $offset;
 238                  }
 239  
 240                  $pos = mb_stripos($haystack, $needle, --$offset, 'UTF-8');
 241                  if ($pos !== false) {
 242                      return ++$pos;
 243                  }
 244              }
 245          }
 246  
 247          return Functions::VALUE();
 248      }
 249  
 250      /**
 251       * FIXEDFORMAT.
 252       *
 253       * @param mixed $value Value to check
 254       * @param int $decimals
 255       * @param bool $no_commas
 256       *
 257       * @return string
 258       */
 259      public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = false)
 260      {
 261          $value = Functions::flattenSingleValue($value);
 262          $decimals = Functions::flattenSingleValue($decimals);
 263          $no_commas = Functions::flattenSingleValue($no_commas);
 264  
 265          // Validate parameters
 266          if (!is_numeric($value) || !is_numeric($decimals)) {
 267              return Functions::NAN();
 268          }
 269          $decimals = (int) floor($decimals);
 270  
 271          $valueResult = round($value, $decimals);
 272          if ($decimals < 0) {
 273              $decimals = 0;
 274          }
 275          if (!$no_commas) {
 276              $valueResult = number_format(
 277                  $valueResult,
 278                  $decimals,
 279                  StringHelper::getDecimalSeparator(),
 280                  StringHelper::getThousandsSeparator()
 281              );
 282          }
 283  
 284          return (string) $valueResult;
 285      }
 286  
 287      /**
 288       * LEFT.
 289       *
 290       * @param string $value Value
 291       * @param int $chars Number of characters
 292       *
 293       * @return string
 294       */
 295      public static function LEFT($value = '', $chars = 1)
 296      {
 297          $value = Functions::flattenSingleValue($value);
 298          $chars = Functions::flattenSingleValue($chars);
 299  
 300          if ($chars < 0) {
 301              return Functions::VALUE();
 302          }
 303  
 304          if (is_bool($value)) {
 305              $value = ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
 306          }
 307  
 308          return mb_substr($value, 0, $chars, 'UTF-8');
 309      }
 310  
 311      /**
 312       * MID.
 313       *
 314       * @param string $value Value
 315       * @param int $start Start character
 316       * @param int $chars Number of characters
 317       *
 318       * @return string
 319       */
 320      public static function MID($value = '', $start = 1, $chars = null)
 321      {
 322          $value = Functions::flattenSingleValue($value);
 323          $start = Functions::flattenSingleValue($start);
 324          $chars = Functions::flattenSingleValue($chars);
 325  
 326          if (($start < 1) || ($chars < 0)) {
 327              return Functions::VALUE();
 328          }
 329  
 330          if (is_bool($value)) {
 331              $value = ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
 332          }
 333  
 334          if (empty($chars)) {
 335              return '';
 336          }
 337  
 338          return mb_substr($value, --$start, $chars, 'UTF-8');
 339      }
 340  
 341      /**
 342       * RIGHT.
 343       *
 344       * @param string $value Value
 345       * @param int $chars Number of characters
 346       *
 347       * @return string
 348       */
 349      public static function RIGHT($value = '', $chars = 1)
 350      {
 351          $value = Functions::flattenSingleValue($value);
 352          $chars = Functions::flattenSingleValue($chars);
 353  
 354          if ($chars < 0) {
 355              return Functions::VALUE();
 356          }
 357  
 358          if (is_bool($value)) {
 359              $value = ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
 360          }
 361  
 362          return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
 363      }
 364  
 365      /**
 366       * STRINGLENGTH.
 367       *
 368       * @param string $value Value
 369       *
 370       * @return int
 371       */
 372      public static function STRINGLENGTH($value = '')
 373      {
 374          $value = Functions::flattenSingleValue($value);
 375  
 376          if (is_bool($value)) {
 377              $value = ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
 378          }
 379  
 380          return mb_strlen($value, 'UTF-8');
 381      }
 382  
 383      /**
 384       * LOWERCASE.
 385       *
 386       * Converts a string value to upper case.
 387       *
 388       * @param string $mixedCaseString
 389       *
 390       * @return string
 391       */
 392      public static function LOWERCASE($mixedCaseString)
 393      {
 394          $mixedCaseString = Functions::flattenSingleValue($mixedCaseString);
 395  
 396          if (is_bool($mixedCaseString)) {
 397              $mixedCaseString = ($mixedCaseString) ? Calculation::getTRUE() : Calculation::getFALSE();
 398          }
 399  
 400          return StringHelper::strToLower($mixedCaseString);
 401      }
 402  
 403      /**
 404       * UPPERCASE.
 405       *
 406       * Converts a string value to upper case.
 407       *
 408       * @param string $mixedCaseString
 409       *
 410       * @return string
 411       */
 412      public static function UPPERCASE($mixedCaseString)
 413      {
 414          $mixedCaseString = Functions::flattenSingleValue($mixedCaseString);
 415  
 416          if (is_bool($mixedCaseString)) {
 417              $mixedCaseString = ($mixedCaseString) ? Calculation::getTRUE() : Calculation::getFALSE();
 418          }
 419  
 420          return StringHelper::strToUpper($mixedCaseString);
 421      }
 422  
 423      /**
 424       * PROPERCASE.
 425       *
 426       * Converts a string value to upper case.
 427       *
 428       * @param string $mixedCaseString
 429       *
 430       * @return string
 431       */
 432      public static function PROPERCASE($mixedCaseString)
 433      {
 434          $mixedCaseString = Functions::flattenSingleValue($mixedCaseString);
 435  
 436          if (is_bool($mixedCaseString)) {
 437              $mixedCaseString = ($mixedCaseString) ? Calculation::getTRUE() : Calculation::getFALSE();
 438          }
 439  
 440          return StringHelper::strToTitle($mixedCaseString);
 441      }
 442  
 443      /**
 444       * REPLACE.
 445       *
 446       * @param string $oldText String to modify
 447       * @param int $start Start character
 448       * @param int $chars Number of characters
 449       * @param string $newText String to replace in defined position
 450       *
 451       * @return string
 452       */
 453      public static function REPLACE($oldText, $start, $chars, $newText)
 454      {
 455          $oldText = Functions::flattenSingleValue($oldText);
 456          $start = Functions::flattenSingleValue($start);
 457          $chars = Functions::flattenSingleValue($chars);
 458          $newText = Functions::flattenSingleValue($newText);
 459  
 460          $left = self::LEFT($oldText, $start - 1);
 461          $right = self::RIGHT($oldText, self::STRINGLENGTH($oldText) - ($start + $chars) + 1);
 462  
 463          return $left . $newText . $right;
 464      }
 465  
 466      /**
 467       * SUBSTITUTE.
 468       *
 469       * @param string $text Value
 470       * @param string $fromText From Value
 471       * @param string $toText To Value
 472       * @param int $instance Instance Number
 473       *
 474       * @return string
 475       */
 476      public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0)
 477      {
 478          $text = Functions::flattenSingleValue($text);
 479          $fromText = Functions::flattenSingleValue($fromText);
 480          $toText = Functions::flattenSingleValue($toText);
 481          $instance = floor(Functions::flattenSingleValue($instance));
 482  
 483          if ($instance == 0) {
 484              return str_replace($fromText, $toText, $text);
 485          }
 486  
 487          $pos = -1;
 488          while ($instance > 0) {
 489              $pos = mb_strpos($text, $fromText, $pos + 1, 'UTF-8');
 490              if ($pos === false) {
 491                  break;
 492              }
 493              --$instance;
 494          }
 495  
 496          if ($pos !== false) {
 497              return self::REPLACE($text, ++$pos, mb_strlen($fromText, 'UTF-8'), $toText);
 498          }
 499  
 500          return $text;
 501      }
 502  
 503      /**
 504       * RETURNSTRING.
 505       *
 506       * @param mixed $testValue Value to check
 507       *
 508       * @return null|string
 509       */
 510      public static function RETURNSTRING($testValue = '')
 511      {
 512          $testValue = Functions::flattenSingleValue($testValue);
 513  
 514          if (is_string($testValue)) {
 515              return $testValue;
 516          }
 517  
 518          return null;
 519      }
 520  
 521      /**
 522       * TEXTFORMAT.
 523       *
 524       * @param mixed $value Value to check
 525       * @param string $format Format mask to use
 526       *
 527       * @return string
 528       */
 529      public static function TEXTFORMAT($value, $format)
 530      {
 531          $value = Functions::flattenSingleValue($value);
 532          $format = Functions::flattenSingleValue($format);
 533  
 534          if ((is_string($value)) && (!is_numeric($value)) && Date::isDateTimeFormatCode($format)) {
 535              $value = DateTime::DATEVALUE($value);
 536          }
 537  
 538          return (string) NumberFormat::toFormattedString($value, $format);
 539      }
 540  
 541      /**
 542       * VALUE.
 543       *
 544       * @param mixed $value Value to check
 545       *
 546       * @return bool
 547       */
 548      public static function VALUE($value = '')
 549      {
 550          $value = Functions::flattenSingleValue($value);
 551  
 552          if (!is_numeric($value)) {
 553              $numberValue = str_replace(
 554                  StringHelper::getThousandsSeparator(),
 555                  '',
 556                  trim($value, " \t\n\r\0\x0B" . StringHelper::getCurrencyCode())
 557              );
 558              if (is_numeric($numberValue)) {
 559                  return (float) $numberValue;
 560              }
 561  
 562              $dateSetting = Functions::getReturnDateType();
 563              Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
 564  
 565              if (strpos($value, ':') !== false) {
 566                  $timeValue = DateTime::TIMEVALUE($value);
 567                  if ($timeValue !== Functions::VALUE()) {
 568                      Functions::setReturnDateType($dateSetting);
 569  
 570                      return $timeValue;
 571                  }
 572              }
 573              $dateValue = DateTime::DATEVALUE($value);
 574              if ($dateValue !== Functions::VALUE()) {
 575                  Functions::setReturnDateType($dateSetting);
 576  
 577                  return $dateValue;
 578              }
 579              Functions::setReturnDateType($dateSetting);
 580  
 581              return Functions::VALUE();
 582          }
 583  
 584          return (float) $value;
 585      }
 586  
 587      /**
 588       * NUMBERVALUE.
 589       *
 590       * @param mixed $value Value to check
 591       * @param string $decimalSeparator decimal separator, defaults to locale defined value
 592       * @param string $groupSeparator group/thosands separator, defaults to locale defined value
 593       *
 594       * @return float|string
 595       */
 596      public static function NUMBERVALUE($value = '', $decimalSeparator = null, $groupSeparator = null)
 597      {
 598          $value = Functions::flattenSingleValue($value);
 599          $decimalSeparator = Functions::flattenSingleValue($decimalSeparator);
 600          $groupSeparator = Functions::flattenSingleValue($groupSeparator);
 601  
 602          if (!is_numeric($value)) {
 603              $decimalSeparator = empty($decimalSeparator) ? StringHelper::getDecimalSeparator() : $decimalSeparator;
 604              $groupSeparator = empty($groupSeparator) ? StringHelper::getThousandsSeparator() : $groupSeparator;
 605  
 606              $decimalPositions = preg_match_all('/' . preg_quote($decimalSeparator) . '/', $value, $matches, PREG_OFFSET_CAPTURE);
 607              if ($decimalPositions > 1) {
 608                  return Functions::VALUE();
 609              }
 610              $decimalOffset = array_pop($matches[0])[1];
 611              if (strpos($value, $groupSeparator, $decimalOffset) !== false) {
 612                  return Functions::VALUE();
 613              }
 614  
 615              $value = str_replace([$groupSeparator, $decimalSeparator], ['', '.'], $value);
 616  
 617              // Handle the special case of trailing % signs
 618              $percentageString = rtrim($value, '%');
 619              if (!is_numeric($percentageString)) {
 620                  return Functions::VALUE();
 621              }
 622  
 623              $percentageAdjustment = strlen($value) - strlen($percentageString);
 624              if ($percentageAdjustment) {
 625                  $value = (float) $percentageString;
 626                  $value /= pow(10, $percentageAdjustment * 2);
 627              }
 628          }
 629  
 630          return (float) $value;
 631      }
 632  
 633      /**
 634       * Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
 635       * EXACT is case-sensitive but ignores formatting differences.
 636       * Use EXACT to test text being entered into a document.
 637       *
 638       * @param $value1
 639       * @param $value2
 640       *
 641       * @return bool
 642       */
 643      public static function EXACT($value1, $value2)
 644      {
 645          $value1 = Functions::flattenSingleValue($value1);
 646          $value2 = Functions::flattenSingleValue($value2);
 647  
 648          return (string) $value2 === (string) $value1;
 649      }
 650  
 651      /**
 652       * TEXTJOIN.
 653       *
 654       * @param mixed $delimiter
 655       * @param mixed $ignoreEmpty
 656       * @param mixed $args
 657       *
 658       * @return string
 659       */
 660      public static function TEXTJOIN($delimiter, $ignoreEmpty, ...$args)
 661      {
 662          // Loop through arguments
 663          $aArgs = Functions::flattenArray($args);
 664          foreach ($aArgs as $key => &$arg) {
 665              if ($ignoreEmpty && trim($arg) == '') {
 666                  unset($aArgs[$key]);
 667              } elseif (is_bool($arg)) {
 668                  $arg = self::convertBooleanValue($arg);
 669              }
 670          }
 671  
 672          return implode($delimiter, $aArgs);
 673      }
 674  }