1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5use PhpOffice\PhpSpreadsheet\Shared\Date; 6use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 7use PhpOffice\PhpSpreadsheet\Style\NumberFormat; 8 9class 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} 675