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