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