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/** MAX_VALUE */
14define('MAX_VALUE', 1.2e308);
15
16/** 2 / PI */
17define('M_2DIVPI', 0.63661977236758134307553505349006);
18
19/** MAX_ITERATIONS */
20define('MAX_ITERATIONS', 256);
21
22/** PRECISION */
23define('PRECISION', 8.88E-016);
24
25
26/**
27 * PHPExcel_Calculation_Functions
28 *
29 * Copyright (c) 2006 - 2015 PHPExcel
30 *
31 * This library is free software; you can redistribute it and/or
32 * modify it under the terms of the GNU Lesser General Public
33 * License as published by the Free Software Foundation; either
34 * version 2.1 of the License, or (at your option) any later version.
35 *
36 * This library is distributed in the hope that it will be useful,
37 * but WITHOUT ANY WARRANTY; without even the implied warranty of
38 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
39 * Lesser General Public License for more details.
40 *
41 * You should have received a copy of the GNU Lesser General Public
42 * License along with this library; if not, write to the Free Software
43 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
44 *
45 * @category    PHPExcel
46 * @package        PHPExcel_Calculation
47 * @copyright    Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
48 * @license        http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
49 * @version        ##VERSION##, ##DATE##
50 */
51class PHPExcel_Calculation_Functions
52{
53
54    /** constants */
55    const COMPATIBILITY_EXCEL      = 'Excel';
56    const COMPATIBILITY_GNUMERIC   = 'Gnumeric';
57    const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
58
59    const RETURNDATE_PHP_NUMERIC = 'P';
60    const RETURNDATE_PHP_OBJECT  = 'O';
61    const RETURNDATE_EXCEL       = 'E';
62
63
64    /**
65     * Compatibility mode to use for error checking and responses
66     *
67     * @access    private
68     * @var string
69     */
70    protected static $compatibilityMode = self::COMPATIBILITY_EXCEL;
71
72    /**
73     * Data Type to use when returning date values
74     *
75     * @access    private
76     * @var string
77     */
78    protected static $returnDateType = self::RETURNDATE_EXCEL;
79
80    /**
81     * List of error codes
82     *
83     * @access    private
84     * @var array
85     */
86    protected static $errorCodes = array(
87        'null'           => '#NULL!',
88        'divisionbyzero' => '#DIV/0!',
89        'value'          => '#VALUE!',
90        'reference'      => '#REF!',
91        'name'           => '#NAME?',
92        'num'            => '#NUM!',
93        'na'             => '#N/A',
94        'gettingdata'    => '#GETTING_DATA'
95    );
96
97
98    /**
99     * Set the Compatibility Mode
100     *
101     * @access    public
102     * @category Function Configuration
103     * @param     string        $compatibilityMode        Compatibility Mode
104     *                                                Permitted values are:
105     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL            'Excel'
106     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC        'Gnumeric'
107     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE    'OpenOfficeCalc'
108     * @return     boolean    (Success or Failure)
109     */
110    public static function setCompatibilityMode($compatibilityMode)
111    {
112        if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
113            ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
114            ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
115            self::$compatibilityMode = $compatibilityMode;
116            return true;
117        }
118        return false;
119    }
120
121
122    /**
123     * Return the current Compatibility Mode
124     *
125     * @access    public
126     * @category Function Configuration
127     * @return     string        Compatibility Mode
128     *                            Possible Return values are:
129     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL            'Excel'
130     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC        'Gnumeric'
131     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE    'OpenOfficeCalc'
132     */
133    public static function getCompatibilityMode()
134    {
135        return self::$compatibilityMode;
136    }
137
138
139    /**
140     * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
141     *
142     * @access    public
143     * @category Function Configuration
144     * @param     string    $returnDateType            Return Date Format
145     *                                                Permitted values are:
146     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC        'P'
147     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT        'O'
148     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_EXCEL            'E'
149     * @return     boolean                            Success or failure
150     */
151    public static function setReturnDateType($returnDateType)
152    {
153        if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
154            ($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
155            ($returnDateType == self::RETURNDATE_EXCEL)) {
156            self::$returnDateType = $returnDateType;
157            return true;
158        }
159        return false;
160    }
161
162
163    /**
164     * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
165     *
166     * @access    public
167     * @category Function Configuration
168     * @return     string        Return Date Format
169     *                            Possible Return values are:
170     *                                PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC        'P'
171     *                                PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT        'O'
172     *                                PHPExcel_Calculation_Functions::RETURNDATE_EXCEL            'E'
173     */
174    public static function getReturnDateType()
175    {
176        return self::$returnDateType;
177    }
178
179
180    /**
181     * DUMMY
182     *
183     * @access    public
184     * @category Error Returns
185     * @return    string    #Not Yet Implemented
186     */
187    public static function DUMMY()
188    {
189        return '#Not Yet Implemented';
190    }
191
192
193    /**
194     * DIV0
195     *
196     * @access    public
197     * @category Error Returns
198     * @return    string    #Not Yet Implemented
199     */
200    public static function DIV0()
201    {
202        return self::$errorCodes['divisionbyzero'];
203    }
204
205
206    /**
207     * NA
208     *
209     * Excel Function:
210     *        =NA()
211     *
212     * Returns the error value #N/A
213     *        #N/A is the error value that means "no value is available."
214     *
215     * @access    public
216     * @category Logical Functions
217     * @return    string    #N/A!
218     */
219    public static function NA()
220    {
221        return self::$errorCodes['na'];
222    }
223
224
225    /**
226     * NaN
227     *
228     * Returns the error value #NUM!
229     *
230     * @access    public
231     * @category Error Returns
232     * @return    string    #NUM!
233     */
234    public static function NaN()
235    {
236        return self::$errorCodes['num'];
237    }
238
239
240    /**
241     * NAME
242     *
243     * Returns the error value #NAME?
244     *
245     * @access    public
246     * @category Error Returns
247     * @return    string    #NAME?
248     */
249    public static function NAME()
250    {
251        return self::$errorCodes['name'];
252    }
253
254
255    /**
256     * REF
257     *
258     * Returns the error value #REF!
259     *
260     * @access    public
261     * @category Error Returns
262     * @return    string    #REF!
263     */
264    public static function REF()
265    {
266        return self::$errorCodes['reference'];
267    }
268
269
270    /**
271     * NULL
272     *
273     * Returns the error value #NULL!
274     *
275     * @access    public
276     * @category Error Returns
277     * @return    string    #NULL!
278     */
279    public static function NULL()
280    {
281        return self::$errorCodes['null'];
282    }
283
284
285    /**
286     * VALUE
287     *
288     * Returns the error value #VALUE!
289     *
290     * @access    public
291     * @category Error Returns
292     * @return    string    #VALUE!
293     */
294    public static function VALUE()
295    {
296        return self::$errorCodes['value'];
297    }
298
299
300    public static function isMatrixValue($idx)
301    {
302        return ((substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0));
303    }
304
305
306    public static function isValue($idx)
307    {
308        return (substr_count($idx, '.') == 0);
309    }
310
311
312    public static function isCellValue($idx)
313    {
314        return (substr_count($idx, '.') > 1);
315    }
316
317
318    public static function ifCondition($condition)
319    {
320        $condition    = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
321        if (!isset($condition{0})) {
322            $condition = '=""';
323        }
324        if (!in_array($condition{0}, array('>', '<', '='))) {
325            if (!is_numeric($condition)) {
326                $condition = PHPExcel_Calculation::wrapResult(strtoupper($condition));
327            }
328            return '=' . $condition;
329        } else {
330            preg_match('/([<>=]+)(.*)/', $condition, $matches);
331            list(, $operator, $operand) = $matches;
332
333            if (!is_numeric($operand)) {
334                $operand = str_replace('"', '""', $operand);
335                $operand = PHPExcel_Calculation::wrapResult(strtoupper($operand));
336            }
337
338            return $operator.$operand;
339        }
340    }
341
342    /**
343     * ERROR_TYPE
344     *
345     * @param    mixed    $value    Value to check
346     * @return    boolean
347     */
348    public static function ERROR_TYPE($value = '')
349    {
350        $value = self::flattenSingleValue($value);
351
352        $i = 1;
353        foreach (self::$errorCodes as $errorCode) {
354            if ($value === $errorCode) {
355                return $i;
356            }
357            ++$i;
358        }
359        return self::NA();
360    }
361
362
363    /**
364     * IS_BLANK
365     *
366     * @param    mixed    $value    Value to check
367     * @return    boolean
368     */
369    public static function IS_BLANK($value = null)
370    {
371        if (!is_null($value)) {
372            $value    = self::flattenSingleValue($value);
373        }
374
375        return is_null($value);
376    }
377
378
379    /**
380     * IS_ERR
381     *
382     * @param    mixed    $value    Value to check
383     * @return    boolean
384     */
385    public static function IS_ERR($value = '')
386    {
387        $value = self::flattenSingleValue($value);
388
389        return self::IS_ERROR($value) && (!self::IS_NA($value));
390    }
391
392
393    /**
394     * IS_ERROR
395     *
396     * @param    mixed    $value    Value to check
397     * @return    boolean
398     */
399    public static function IS_ERROR($value = '')
400    {
401        $value = self::flattenSingleValue($value);
402
403        if (!is_string($value)) {
404            return false;
405        }
406        return in_array($value, array_values(self::$errorCodes));
407    }
408
409
410    /**
411     * IS_NA
412     *
413     * @param    mixed    $value    Value to check
414     * @return    boolean
415     */
416    public static function IS_NA($value = '')
417    {
418        $value = self::flattenSingleValue($value);
419
420        return ($value === self::NA());
421    }
422
423
424    /**
425     * IS_EVEN
426     *
427     * @param    mixed    $value    Value to check
428     * @return    boolean
429     */
430    public static function IS_EVEN($value = null)
431    {
432        $value = self::flattenSingleValue($value);
433
434        if ($value === null) {
435            return self::NAME();
436        } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
437            return self::VALUE();
438        }
439
440        return ($value % 2 == 0);
441    }
442
443
444    /**
445     * IS_ODD
446     *
447     * @param    mixed    $value    Value to check
448     * @return    boolean
449     */
450    public static function IS_ODD($value = null)
451    {
452        $value = self::flattenSingleValue($value);
453
454        if ($value === null) {
455            return self::NAME();
456        } elseif ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
457            return self::VALUE();
458        }
459
460        return (abs($value) % 2 == 1);
461    }
462
463
464    /**
465     * IS_NUMBER
466     *
467     * @param    mixed    $value        Value to check
468     * @return    boolean
469     */
470    public static function IS_NUMBER($value = null)
471    {
472        $value = self::flattenSingleValue($value);
473
474        if (is_string($value)) {
475            return false;
476        }
477        return is_numeric($value);
478    }
479
480
481    /**
482     * IS_LOGICAL
483     *
484     * @param    mixed    $value        Value to check
485     * @return    boolean
486     */
487    public static function IS_LOGICAL($value = null)
488    {
489        $value = self::flattenSingleValue($value);
490
491        return is_bool($value);
492    }
493
494
495    /**
496     * IS_TEXT
497     *
498     * @param    mixed    $value        Value to check
499     * @return    boolean
500     */
501    public static function IS_TEXT($value = null)
502    {
503        $value = self::flattenSingleValue($value);
504
505        return (is_string($value) && !self::IS_ERROR($value));
506    }
507
508
509    /**
510     * IS_NONTEXT
511     *
512     * @param    mixed    $value        Value to check
513     * @return    boolean
514     */
515    public static function IS_NONTEXT($value = null)
516    {
517        return !self::IS_TEXT($value);
518    }
519
520
521	/**
522	 * VERSION
523	 *
524	 * @return	string	Version information
525	 */
526	public static function VERSION() {
527		return 'PHPExcel 1.8.2, 2018-11-22';
528	}	//	function VERSION()
529
530
531    /**
532     * N
533     *
534     * Returns a value converted to a number
535     *
536     * @param    value        The value you want converted
537     * @return    number        N converts values listed in the following table
538     *        If value is or refers to N returns
539     *        A number            That number
540     *        A date                The serial number of that date
541     *        TRUE                1
542     *        FALSE                0
543     *        An error value        The error value
544     *        Anything else        0
545     */
546    public static function N($value = null)
547    {
548        while (is_array($value)) {
549            $value = array_shift($value);
550        }
551
552        switch (gettype($value)) {
553            case 'double':
554            case 'float':
555            case 'integer':
556                return $value;
557            case 'boolean':
558                return (integer) $value;
559            case 'string':
560                //    Errors
561                if ((strlen($value) > 0) && ($value{0} == '#')) {
562                    return $value;
563                }
564                break;
565        }
566        return 0;
567    }
568
569
570    /**
571     * TYPE
572     *
573     * Returns a number that identifies the type of a value
574     *
575     * @param    value        The value you want tested
576     * @return    number        N converts values listed in the following table
577     *        If value is or refers to N returns
578     *        A number            1
579     *        Text                2
580     *        Logical Value        4
581     *        An error value        16
582     *        Array or Matrix        64
583     */
584    public static function TYPE($value = null)
585    {
586        $value = self::flattenArrayIndexed($value);
587        if (is_array($value) && (count($value) > 1)) {
588            end($value);
589            $a = key($value);
590            //    Range of cells is an error
591            if (self::isCellValue($a)) {
592                return 16;
593            //    Test for Matrix
594            } elseif (self::isMatrixValue($a)) {
595                return 64;
596            }
597        } elseif (empty($value)) {
598            //    Empty Cell
599            return 1;
600        }
601        $value = self::flattenSingleValue($value);
602
603        if (($value === null) || (is_float($value)) || (is_int($value))) {
604                return 1;
605        } elseif (is_bool($value)) {
606                return 4;
607        } elseif (is_array($value)) {
608                return 64;
609        } elseif (is_string($value)) {
610            //    Errors
611            if ((strlen($value) > 0) && ($value{0} == '#')) {
612                return 16;
613            }
614            return 2;
615        }
616        return 0;
617    }
618
619
620    /**
621     * Convert a multi-dimensional array to a simple 1-dimensional array
622     *
623     * @param    array    $array    Array to be flattened
624     * @return    array    Flattened array
625     */
626    public static function flattenArray($array)
627    {
628        if (!is_array($array)) {
629            return (array) $array;
630        }
631
632        $arrayValues = array();
633        foreach ($array as $value) {
634            if (is_array($value)) {
635                foreach ($value as $val) {
636                    if (is_array($val)) {
637                        foreach ($val as $v) {
638                            $arrayValues[] = $v;
639                        }
640                    } else {
641                        $arrayValues[] = $val;
642                    }
643                }
644            } else {
645                $arrayValues[] = $value;
646            }
647        }
648
649        return $arrayValues;
650    }
651
652
653    /**
654     * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
655     *
656     * @param    array    $array    Array to be flattened
657     * @return    array    Flattened array
658     */
659    public static function flattenArrayIndexed($array)
660    {
661        if (!is_array($array)) {
662            return (array) $array;
663        }
664
665        $arrayValues = array();
666        foreach ($array as $k1 => $value) {
667            if (is_array($value)) {
668                foreach ($value as $k2 => $val) {
669                    if (is_array($val)) {
670                        foreach ($val as $k3 => $v) {
671                            $arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
672                        }
673                    } else {
674                        $arrayValues[$k1.'.'.$k2] = $val;
675                    }
676                }
677            } else {
678                $arrayValues[$k1] = $value;
679            }
680        }
681
682        return $arrayValues;
683    }
684
685
686    /**
687     * Convert an array to a single scalar value by extracting the first element
688     *
689     * @param    mixed        $value        Array or scalar value
690     * @return    mixed
691     */
692    public static function flattenSingleValue($value = '')
693    {
694        while (is_array($value)) {
695            $value = array_pop($value);
696        }
697
698        return $value;
699    }
700}
701
702
703//
704//    There are a few mathematical functions that aren't available on all versions of PHP for all platforms
705//    These functions aren't available in Windows implementations of PHP prior to version 5.3.0
706//    So we test if they do exist for this version of PHP/operating platform; and if not we create them
707//
708if (!function_exists('acosh')) {
709    function acosh($x)
710    {
711        return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
712    }    //    function acosh()
713}
714
715if (!function_exists('asinh')) {
716    function asinh($x)
717    {
718        return log($x + sqrt(1 + $x * $x));
719    }    //    function asinh()
720}
721
722if (!function_exists('atanh')) {
723    function atanh($x)
724    {
725        return (log(1 + $x) - log(1 - $x)) / 2;
726    }    //    function atanh()
727}
728
729
730//
731//    Strangely, PHP doesn't have a mb_str_replace multibyte function
732//    As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
733//
734if ((!function_exists('mb_str_replace')) &&
735    (function_exists('mb_substr')) && (function_exists('mb_strlen')) && (function_exists('mb_strpos'))) {
736    function mb_str_replace($search, $replace, $subject)
737    {
738        if (is_array($subject)) {
739            $ret = array();
740            foreach ($subject as $key => $val) {
741                $ret[$key] = mb_str_replace($search, $replace, $val);
742            }
743            return $ret;
744        }
745
746        foreach ((array) $search as $key => $s) {
747            if ($s == '' && $s !== 0) {
748                continue;
749            }
750            $r = !is_array($replace) ? $replace : (array_key_exists($key, $replace) ? $replace[$key] : '');
751            $pos = mb_strpos($subject, $s, 0, 'UTF-8');
752            while ($pos !== false) {
753                $subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
754                $pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
755            }
756        }
757        return $subject;
758    }
759}
760