[Summary view]

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