1<?php
2
3/** PHPExcel root directory */
4if (!defined('PHPEXCEL_ROOT')) {
5    /**
6     * @ignore
7     */
8    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
9    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
10}
11
12/**
13 * PHPExcel_Calculation_TextData
14 *
15 * Copyright (c) 2006 - 2015 PHPExcel
16 *
17 * This library is free software; you can redistribute it and/or
18 * modify it under the terms of the GNU Lesser General Public
19 * License as published by the Free Software Foundation; either
20 * version 2.1 of the License, or (at your option) any later version.
21 *
22 * This library is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
25 * Lesser General Public License for more details.
26 *
27 * You should have received a copy of the GNU Lesser General Public
28 * License along with this library; if not, write to the Free Software
29 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
30 *
31 * @category    PHPExcel
32 * @package        PHPExcel_Calculation
33 * @copyright    Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
34 * @license        http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
35 * @version        ##VERSION##, ##DATE##
36 */
37class PHPExcel_Calculation_TextData
38{
39    private static $invalidChars;
40
41    private static function unicodeToOrd($c)
42    {
43        if (ord($c{0}) >=0 && ord($c{0}) <= 127) {
44            return ord($c{0});
45        } elseif (ord($c{0}) >= 192 && ord($c{0}) <= 223) {
46            return (ord($c{0})-192)*64 + (ord($c{1})-128);
47        } elseif (ord($c{0}) >= 224 && ord($c{0}) <= 239) {
48            return (ord($c{0})-224)*4096 + (ord($c{1})-128)*64 + (ord($c{2})-128);
49        } elseif (ord($c{0}) >= 240 && ord($c{0}) <= 247) {
50            return (ord($c{0})-240)*262144 + (ord($c{1})-128)*4096 + (ord($c{2})-128)*64 + (ord($c{3})-128);
51        } elseif (ord($c{0}) >= 248 && ord($c{0}) <= 251) {
52            return (ord($c{0})-248)*16777216 + (ord($c{1})-128)*262144 + (ord($c{2})-128)*4096 + (ord($c{3})-128)*64 + (ord($c{4})-128);
53        } elseif (ord($c{0}) >= 252 && ord($c{0}) <= 253) {
54            return (ord($c{0})-252)*1073741824 + (ord($c{1})-128)*16777216 + (ord($c{2})-128)*262144 + (ord($c{3})-128)*4096 + (ord($c{4})-128)*64 + (ord($c{5})-128);
55        } elseif (ord($c{0}) >= 254 && ord($c{0}) <= 255) {
56            // error
57            return PHPExcel_Calculation_Functions::VALUE();
58        }
59        return 0;
60    }
61
62    /**
63     * CHARACTER
64     *
65     * @param    string    $character    Value
66     * @return    int
67     */
68    public static function CHARACTER($character)
69    {
70        $character = PHPExcel_Calculation_Functions::flattenSingleValue($character);
71
72        if ((!is_numeric($character)) || ($character < 0)) {
73            return PHPExcel_Calculation_Functions::VALUE();
74        }
75
76        if (function_exists('mb_convert_encoding')) {
77            return mb_convert_encoding('&#'.intval($character).';', 'UTF-8', 'HTML-ENTITIES');
78        } else {
79            return chr(intval($character));
80        }
81    }
82
83
84    /**
85     * TRIMNONPRINTABLE
86     *
87     * @param    mixed    $stringValue    Value to check
88     * @return    string
89     */
90    public static function TRIMNONPRINTABLE($stringValue = '')
91    {
92        $stringValue    = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
93
94        if (is_bool($stringValue)) {
95            return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
96        }
97
98        if (self::$invalidChars == null) {
99            self::$invalidChars = range(chr(0), chr(31));
100        }
101
102        if (is_string($stringValue) || is_numeric($stringValue)) {
103            return str_replace(self::$invalidChars, '', trim($stringValue, "\x00..\x1F"));
104        }
105        return null;
106    }
107
108
109    /**
110     * TRIMSPACES
111     *
112     * @param    mixed    $stringValue    Value to check
113     * @return    string
114     */
115    public static function TRIMSPACES($stringValue = '')
116    {
117        $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
118        if (is_bool($stringValue)) {
119            return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
120        }
121
122        if (is_string($stringValue) || is_numeric($stringValue)) {
123            return trim(preg_replace('/ +/', ' ', trim($stringValue, ' ')), ' ');
124        }
125        return null;
126    }
127
128
129    /**
130     * ASCIICODE
131     *
132     * @param    string    $characters        Value
133     * @return    int
134     */
135    public static function ASCIICODE($characters)
136    {
137        if (($characters === null) || ($characters === '')) {
138            return PHPExcel_Calculation_Functions::VALUE();
139        }
140        $characters    = PHPExcel_Calculation_Functions::flattenSingleValue($characters);
141        if (is_bool($characters)) {
142            if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
143                $characters = (int) $characters;
144            } else {
145                $characters = ($characters) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
146            }
147        }
148
149        $character = $characters;
150        if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
151            if (mb_strlen($characters, 'UTF-8') > 1) {
152                $character = mb_substr($characters, 0, 1, 'UTF-8');
153            }
154            return self::unicodeToOrd($character);
155        } else {
156            if (strlen($characters) > 0) {
157                $character = substr($characters, 0, 1);
158            }
159            return ord($character);
160        }
161    }
162
163
164    /**
165     * CONCATENATE
166     *
167     * @return    string
168     */
169    public static function CONCATENATE()
170    {
171        $returnValue = '';
172
173        // Loop through arguments
174        $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
175        foreach ($aArgs as $arg) {
176            if (is_bool($arg)) {
177                if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
178                    $arg = (int) $arg;
179                } else {
180                    $arg = ($arg) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
181                }
182            }
183            $returnValue .= $arg;
184        }
185
186        return $returnValue;
187    }
188
189
190    /**
191     * DOLLAR
192     *
193     * This function converts a number to text using currency format, with the decimals rounded to the specified place.
194     * The format used is $#,##0.00_);($#,##0.00)..
195     *
196     * @param    float    $value            The value to format
197     * @param    int        $decimals        The number of digits to display to the right of the decimal point.
198     *                                    If decimals is negative, number is rounded to the left of the decimal point.
199     *                                    If you omit decimals, it is assumed to be 2
200     * @return    string
201     */
202    public static function DOLLAR($value = 0, $decimals = 2)
203    {
204        $value        = PHPExcel_Calculation_Functions::flattenSingleValue($value);
205        $decimals    = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
206
207        // Validate parameters
208        if (!is_numeric($value) || !is_numeric($decimals)) {
209            return PHPExcel_Calculation_Functions::NaN();
210        }
211        $decimals = floor($decimals);
212
213        $mask = '$#,##0';
214        if ($decimals > 0) {
215            $mask .= '.' . str_repeat('0', $decimals);
216        } else {
217            $round = pow(10, abs($decimals));
218            if ($value < 0) {
219                $round = 0-$round;
220            }
221            $value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
222        }
223
224        return PHPExcel_Style_NumberFormat::toFormattedString($value, $mask);
225
226    }
227
228
229    /**
230     * SEARCHSENSITIVE
231     *
232     * @param    string    $needle        The string to look for
233     * @param    string    $haystack    The string in which to look
234     * @param    int        $offset        Offset within $haystack
235     * @return    string
236     */
237    public static function SEARCHSENSITIVE($needle, $haystack, $offset = 1)
238    {
239        $needle   = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
240        $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
241        $offset   = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
242
243        if (!is_bool($needle)) {
244            if (is_bool($haystack)) {
245                $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
246            }
247
248            if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
249                if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
250                    return $offset;
251                }
252                if (function_exists('mb_strpos')) {
253                    $pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
254                } else {
255                    $pos = strpos($haystack, $needle, --$offset);
256                }
257                if ($pos !== false) {
258                    return ++$pos;
259                }
260            }
261        }
262        return PHPExcel_Calculation_Functions::VALUE();
263    }
264
265
266    /**
267     * SEARCHINSENSITIVE
268     *
269     * @param    string    $needle        The string to look for
270     * @param    string    $haystack    The string in which to look
271     * @param    int        $offset        Offset within $haystack
272     * @return    string
273     */
274    public static function SEARCHINSENSITIVE($needle, $haystack, $offset = 1)
275    {
276        $needle   = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
277        $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
278        $offset   = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
279
280        if (!is_bool($needle)) {
281            if (is_bool($haystack)) {
282                $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
283            }
284
285            if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
286                if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
287                    return $offset;
288                }
289                if (function_exists('mb_stripos')) {
290                    $pos = mb_stripos($haystack, $needle, --$offset, 'UTF-8');
291                } else {
292                    $pos = stripos($haystack, $needle, --$offset);
293                }
294                if ($pos !== false) {
295                    return ++$pos;
296                }
297            }
298        }
299        return PHPExcel_Calculation_Functions::VALUE();
300    }
301
302
303    /**
304     * FIXEDFORMAT
305     *
306     * @param    mixed        $value    Value to check
307     * @param    integer        $decimals
308     * @param    boolean        $no_commas
309     * @return    boolean
310     */
311    public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = false)
312    {
313        $value     = PHPExcel_Calculation_Functions::flattenSingleValue($value);
314        $decimals  = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
315        $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
316
317        // Validate parameters
318        if (!is_numeric($value) || !is_numeric($decimals)) {
319            return PHPExcel_Calculation_Functions::NaN();
320        }
321        $decimals = floor($decimals);
322
323        $valueResult = round($value, $decimals);
324        if ($decimals < 0) {
325            $decimals = 0;
326        }
327        if (!$no_commas) {
328            $valueResult = number_format($valueResult, $decimals);
329        }
330
331        return (string) $valueResult;
332    }
333
334
335    /**
336     * LEFT
337     *
338     * @param    string    $value    Value
339     * @param    int        $chars    Number of characters
340     * @return    string
341     */
342    public static function LEFT($value = '', $chars = 1)
343    {
344        $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
345        $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
346
347        if ($chars < 0) {
348            return PHPExcel_Calculation_Functions::VALUE();
349        }
350
351        if (is_bool($value)) {
352            $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
353        }
354
355        if (function_exists('mb_substr')) {
356            return mb_substr($value, 0, $chars, 'UTF-8');
357        } else {
358            return substr($value, 0, $chars);
359        }
360    }
361
362
363    /**
364     * MID
365     *
366     * @param    string    $value    Value
367     * @param    int        $start    Start character
368     * @param    int        $chars    Number of characters
369     * @return    string
370     */
371    public static function MID($value = '', $start = 1, $chars = null)
372    {
373        $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
374        $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
375        $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
376
377        if (($start < 1) || ($chars < 0)) {
378            return PHPExcel_Calculation_Functions::VALUE();
379        }
380
381        if (is_bool($value)) {
382            $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
383        }
384
385        if (function_exists('mb_substr')) {
386            return mb_substr($value, --$start, $chars, 'UTF-8');
387        } else {
388            return substr($value, --$start, $chars);
389        }
390    }
391
392
393    /**
394     * RIGHT
395     *
396     * @param    string    $value    Value
397     * @param    int        $chars    Number of characters
398     * @return    string
399     */
400    public static function RIGHT($value = '', $chars = 1)
401    {
402        $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
403        $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
404
405        if ($chars < 0) {
406            return PHPExcel_Calculation_Functions::VALUE();
407        }
408
409        if (is_bool($value)) {
410            $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
411        }
412
413        if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
414            return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
415        } else {
416            return substr($value, strlen($value) - $chars);
417        }
418    }
419
420
421    /**
422     * STRINGLENGTH
423     *
424     * @param    string    $value    Value
425     * @return    string
426     */
427    public static function STRINGLENGTH($value = '')
428    {
429        $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
430
431        if (is_bool($value)) {
432            $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
433        }
434
435        if (function_exists('mb_strlen')) {
436            return mb_strlen($value, 'UTF-8');
437        } else {
438            return strlen($value);
439        }
440    }
441
442
443    /**
444     * LOWERCASE
445     *
446     * Converts a string value to upper case.
447     *
448     * @param    string        $mixedCaseString
449     * @return    string
450     */
451    public static function LOWERCASE($mixedCaseString)
452    {
453        $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
454
455        if (is_bool($mixedCaseString)) {
456            $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
457        }
458
459        return PHPExcel_Shared_String::StrToLower($mixedCaseString);
460    }
461
462
463    /**
464     * UPPERCASE
465     *
466     * Converts a string value to upper case.
467     *
468     * @param    string        $mixedCaseString
469     * @return    string
470     */
471    public static function UPPERCASE($mixedCaseString)
472    {
473        $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
474
475        if (is_bool($mixedCaseString)) {
476            $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
477        }
478
479        return PHPExcel_Shared_String::StrToUpper($mixedCaseString);
480    }
481
482
483    /**
484     * PROPERCASE
485     *
486     * Converts a string value to upper case.
487     *
488     * @param    string        $mixedCaseString
489     * @return    string
490     */
491    public static function PROPERCASE($mixedCaseString)
492    {
493        $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
494
495        if (is_bool($mixedCaseString)) {
496            $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
497        }
498
499        return PHPExcel_Shared_String::StrToTitle($mixedCaseString);
500    }
501
502
503    /**
504     * REPLACE
505     *
506     * @param    string    $oldText    String to modify
507     * @param    int        $start        Start character
508     * @param    int        $chars        Number of characters
509     * @param    string    $newText    String to replace in defined position
510     * @return    string
511     */
512    public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText)
513    {
514        $oldText = PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
515        $start   = PHPExcel_Calculation_Functions::flattenSingleValue($start);
516        $chars   = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
517        $newText = PHPExcel_Calculation_Functions::flattenSingleValue($newText);
518
519        $left = self::LEFT($oldText, $start-1);
520        $right = self::RIGHT($oldText, self::STRINGLENGTH($oldText)-($start+$chars)+1);
521
522        return $left.$newText.$right;
523    }
524
525
526    /**
527     * SUBSTITUTE
528     *
529     * @param    string    $text        Value
530     * @param    string    $fromText    From Value
531     * @param    string    $toText        To Value
532     * @param    integer    $instance    Instance Number
533     * @return    string
534     */
535    public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0)
536    {
537        $text     = PHPExcel_Calculation_Functions::flattenSingleValue($text);
538        $fromText = PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
539        $toText   = PHPExcel_Calculation_Functions::flattenSingleValue($toText);
540        $instance = floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
541
542        if ($instance == 0) {
543            if (function_exists('mb_str_replace')) {
544                return mb_str_replace($fromText, $toText, $text);
545            } else {
546                return str_replace($fromText, $toText, $text);
547            }
548        } else {
549            $pos = -1;
550            while ($instance > 0) {
551                if (function_exists('mb_strpos')) {
552                    $pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
553                } else {
554                    $pos = strpos($text, $fromText, $pos+1);
555                }
556                if ($pos === false) {
557                    break;
558                }
559                --$instance;
560            }
561            if ($pos !== false) {
562                if (function_exists('mb_strlen')) {
563                    return self::REPLACE($text, ++$pos, mb_strlen($fromText, 'UTF-8'), $toText);
564                } else {
565                    return self::REPLACE($text, ++$pos, strlen($fromText), $toText);
566                }
567            }
568        }
569
570        return $text;
571    }
572
573
574    /**
575     * RETURNSTRING
576     *
577     * @param    mixed    $testValue    Value to check
578     * @return    boolean
579     */
580    public static function RETURNSTRING($testValue = '')
581    {
582        $testValue = PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
583
584        if (is_string($testValue)) {
585            return $testValue;
586        }
587        return null;
588    }
589
590
591    /**
592     * TEXTFORMAT
593     *
594     * @param    mixed    $value    Value to check
595     * @param    string    $format    Format mask to use
596     * @return    boolean
597     */
598    public static function TEXTFORMAT($value, $format)
599    {
600        $value  = PHPExcel_Calculation_Functions::flattenSingleValue($value);
601        $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
602
603        if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
604            $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
605        }
606
607        return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
608    }
609
610    /**
611     * VALUE
612     *
613     * @param    mixed    $value    Value to check
614     * @return    boolean
615     */
616    public static function VALUE($value = '')
617    {
618        $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
619
620        if (!is_numeric($value)) {
621            $numberValue = str_replace(
622                PHPExcel_Shared_String::getThousandsSeparator(),
623                '',
624                trim($value, " \t\n\r\0\x0B" . PHPExcel_Shared_String::getCurrencyCode())
625            );
626            if (is_numeric($numberValue)) {
627                return (float) $numberValue;
628            }
629
630            $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
631            PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
632
633            if (strpos($value, ':') !== false) {
634                $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
635                if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
636                    PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
637                    return $timeValue;
638                }
639            }
640            $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
641            if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
642                PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
643                return $dateValue;
644            }
645            PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
646
647            return PHPExcel_Calculation_Functions::VALUE();
648        }
649        return (float) $value;
650    }
651}
652