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