Functions.php

Go to the documentation of this file.

<?php
if (!defined('PHPEXCEL_ROOT')) {
        define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
        require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}
 
 
define('MAX_VALUE', 1.2e308);
 
define('M_2DIVPI', 0.63661977236758134307553505349006);
 
define('MAX_ITERATIONS', 256);
 
define('PRECISION', 8.88E-016);
 
 
class PHPExcel_Calculation_Functions {
 
        const COMPATIBILITY_EXCEL               = 'Excel';
        const COMPATIBILITY_GNUMERIC    = 'Gnumeric';
        const COMPATIBILITY_OPENOFFICE  = 'OpenOfficeCalc';
 
        const RETURNDATE_PHP_NUMERIC    = 'P';
        const RETURNDATE_PHP_OBJECT             = 'O';
        const RETURNDATE_EXCEL                  = 'E';
 
 
        protected static $compatibilityMode     = self::COMPATIBILITY_EXCEL;
 
        protected static $ReturnDateType        = self::RETURNDATE_EXCEL;
 
        protected static $_errorCodes   = array( 'null'                         => '#NULL!',
                                                                                         'divisionbyzero'       => '#DIV/0!',
                                                                                         'value'                        => '#VALUE!',
                                                                                         'reference'            => '#REF!',
                                                                                         'name'                         => '#NAME?',
                                                                                         'num'                          => '#NUM!',
                                                                                         'na'                           => '#N/A',
                                                                                         'gettingdata'          => '#GETTING_DATA'
                                                                                   );
 
 
        public static function setCompatibilityMode($compatibilityMode) {
                if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
                        ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
                        ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
                        self::$compatibilityMode = $compatibilityMode;
                        return True;
                }
                return False;
        }       //      function setCompatibilityMode()
 
 
        public static function getCompatibilityMode() {
                return self::$compatibilityMode;
        }       //      function getCompatibilityMode()
 
 
        public static function setReturnDateType($returnDateType) {
                if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
                        ($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
                        ($returnDateType == self::RETURNDATE_EXCEL)) {
                        self::$ReturnDateType = $returnDateType;
                        return True;
                }
                return False;
        }       //      function setReturnDateType()
 
 
        public static function getReturnDateType() {
                return self::$ReturnDateType;
        }       //      function getReturnDateType()
 
 
        public static function DUMMY() {
                return '#Not Yet Implemented';
        }       //      function DUMMY()
 
 
        public static function DIV0() {
                return self::$_errorCodes['divisionbyzero'];
        }       //      function DIV0()
 
 
        public static function NA() {
                return self::$_errorCodes['na'];
        }       //      function NA()
 
 
        public static function NaN() {
                return self::$_errorCodes['num'];
        }       //      function NaN()
 
 
        public static function NAME() {
                return self::$_errorCodes['name'];
        }       //      function NAME()
 
 
        public static function REF() {
                return self::$_errorCodes['reference'];
        }       //      function REF()
 
 
        public static function NULL() {
                return self::$_errorCodes['null'];
        }       //      function NULL()
 
 
        public static function VALUE() {
                return self::$_errorCodes['value'];
        }       //      function VALUE()
 
 
        public static function isMatrixValue($idx) {
                return ((substr_count($idx,'.') <= 1) || (preg_match('/\.[A-Z]/',$idx) > 0));
        }
 
 
        public static function isValue($idx) {
                return (substr_count($idx,'.') == 0);
        }
 
 
        public static function isCellValue($idx) {
                return (substr_count($idx,'.') > 1);
        }
 
 
        public static function _ifCondition($condition) {
                $condition      = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
                if (!isset($condition{0}))
                        $condition = '=""';
                if (!in_array($condition{0},array('>', '<', '='))) {
                        if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
                        return '='.$condition;
                } else {
                        preg_match('/([<>=]+)(.*)/',$condition,$matches);
                        list(,$operator,$operand) = $matches;
 
                        if (!is_numeric($operand)) {
                                $operand = str_replace('"', '""', $operand);
                                $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand));
                        }
 
                        return $operator.$operand;
                }
        }       //      function _ifCondition()
 
 
        public static function ERROR_TYPE($value = '') {
                $value  = self::flattenSingleValue($value);
 
                $i = 1;
                foreach(self::$_errorCodes as $errorCode) {
                        if ($value === $errorCode) {
                                return $i;
                        }
                        ++$i;
                }
                return self::NA();
        }       //      function ERROR_TYPE()
 
 
        public static function IS_BLANK($value = NULL) {
                if (!is_null($value)) {
                        $value  = self::flattenSingleValue($value);
                }
 
                return is_null($value);
        }       //      function IS_BLANK()
 
 
        public static function IS_ERR($value = '') {
                $value          = self::flattenSingleValue($value);
 
                return self::IS_ERROR($value) && (!self::IS_NA($value));
        }       //      function IS_ERR()
 
 
        public static function IS_ERROR($value = '') {
                $value          = self::flattenSingleValue($value);
 
                if (!is_string($value))
                        return false;
                return in_array($value, array_values(self::$_errorCodes));
        }       //      function IS_ERROR()
 
 
        public static function IS_NA($value = '') {
                $value          = self::flattenSingleValue($value);
 
                return ($value === self::NA());
        }       //      function IS_NA()
 
 
        public static function IS_EVEN($value = NULL) {
                $value = self::flattenSingleValue($value);
 
                if ($value === NULL)
                        return self::NAME();
                if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value))))
                        return self::VALUE();
                return ($value % 2 == 0);
        }       //      function IS_EVEN()
 
 
        public static function IS_ODD($value = NULL) {
                $value = self::flattenSingleValue($value);
 
                if ($value === NULL)
                        return self::NAME();
                if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value))))
                        return self::VALUE();
                return (abs($value) % 2 == 1);
        }       //      function IS_ODD()
 
 
        public static function IS_NUMBER($value = NULL) {
                $value          = self::flattenSingleValue($value);
 
                if (is_string($value)) {
                        return False;
                }
                return is_numeric($value);
        }       //      function IS_NUMBER()
 
 
        public static function IS_LOGICAL($value = NULL) {
                $value          = self::flattenSingleValue($value);
 
                return is_bool($value);
        }       //      function IS_LOGICAL()
 
 
        public static function IS_TEXT($value = NULL) {
                $value          = self::flattenSingleValue($value);
 
                return (is_string($value) && !self::IS_ERROR($value));
        }       //      function IS_TEXT()
 
 
        public static function IS_NONTEXT($value = NULL) {
                return !self::IS_TEXT($value);
        }       //      function IS_NONTEXT()
 
 
        public static function VERSION() {
                return 'PHPExcel 1.8.1, 2015-04-30';
        }       //      function VERSION()
 
 
        public static function N($value = NULL) {
                while (is_array($value)) {
                        $value = array_shift($value);
                }
 
                switch (gettype($value)) {
                        case 'double'   :
                        case 'float'    :
                        case 'integer'  :
                                return $value;
                                break;
                        case 'boolean'  :
                                return (integer) $value;
                                break;
                        case 'string'   :
                                //      Errors
                                if ((strlen($value) > 0) && ($value{0} == '#')) {
                                        return $value;
                                }
                                break;
                }
                return 0;
        }       //      function N()
 
 
        public static function TYPE($value = NULL) {
                $value  = self::flattenArrayIndexed($value);
                if (is_array($value) && (count($value) > 1)) {
                        $a = array_keys($value);
                        $a = array_pop($a);
                        //      Range of cells is an error
                        if (self::isCellValue($a)) {
                                return 16;
                        //      Test for Matrix
                        } elseif (self::isMatrixValue($a)) {
                                return 64;
                        }
                } elseif(empty($value)) {
                        //      Empty Cell
                        return 1;
                }
                $value  = self::flattenSingleValue($value);
 
                if (($value === NULL) || (is_float($value)) || (is_int($value))) {
                                return 1;
                } elseif(is_bool($value)) {
                                return 4;
                } elseif(is_array($value)) {
                                return 64;
                } elseif(is_string($value)) {
                        //      Errors
                        if ((strlen($value) > 0) && ($value{0} == '#')) {
                                return 16;
                        }
                        return 2;
                }
                return 0;
        }       //      function TYPE()
 
 
        public static function flattenArray($array) {
                if (!is_array($array)) {
                        return (array) $array;
                }
 
                $arrayValues = array();
                foreach ($array as $value) {
                        if (is_array($value)) {
                                foreach ($value as $val) {
                                        if (is_array($val)) {
                                                foreach ($val as $v) {
                                                        $arrayValues[] = $v;
                                                }
                                        } else {
                                                $arrayValues[] = $val;
                                        }
                                }
                        } else {
                                $arrayValues[] = $value;
                        }
                }
 
                return $arrayValues;
        }       //      function flattenArray()
 
 
        public static function flattenArrayIndexed($array) {
                if (!is_array($array)) {
                        return (array) $array;
                }
 
                $arrayValues = array();
                foreach ($array as $k1 => $value) {
                        if (is_array($value)) {
                                foreach ($value as $k2 => $val) {
                                        if (is_array($val)) {
                                                foreach ($val as $k3 => $v) {
                                                        $arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
                                                }
                                        } else {
                                                $arrayValues[$k1.'.'.$k2] = $val;
                                        }
                                }
                        } else {
                                $arrayValues[$k1] = $value;
                        }
                }
 
                return $arrayValues;
        }       //      function flattenArrayIndexed()
 
 
        public static function flattenSingleValue($value = '') {
                while (is_array($value)) {
                        $value = array_pop($value);
                }
 
                return $value;
        }       //      function flattenSingleValue()
 
}       //      class PHPExcel_Calculation_Functions
 
 
//
//      There are a few mathematical functions that aren't available on all versions of PHP for all platforms
//      These functions aren't available in Windows implementations of PHP prior to version 5.3.0
//      So we test if they do exist for this version of PHP/operating platform; and if not we create them
//
if (!function_exists('acosh')) {
        function acosh($x) {
                return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
        }       //      function acosh()
}
 
if (!function_exists('asinh')) {
        function asinh($x) {
                return log($x + sqrt(1 + $x * $x));
        }       //      function asinh()
}
 
if (!function_exists('atanh')) {
        function atanh($x) {
                return (log(1 + $x) - log(1 - $x)) / 2;
        }       //      function atanh()
}
 
 
//
//      Strangely, PHP doesn't have a mb_str_replace multibyte function
//      As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
//
if ((!function_exists('mb_str_replace')) &&
        (function_exists('mb_substr')) && (function_exists('mb_strlen')) && (function_exists('mb_strpos'))) {
        function mb_str_replace($search, $replace, $subject) {
                if(is_array($subject)) {
                        $ret = array();
                        foreach($subject as $key => $val) {
                                $ret[$key] = mb_str_replace($search, $replace, $val);
                        }
                        return $ret;
                }
 
                foreach((array) $search as $key => $s) {
                        if($s == '' && $s !== 0) {
                                continue;
                        }
                        $r = !is_array($replace) ? $replace : (array_key_exists($key, $replace) ? $replace[$key] : '');
                        $pos = mb_strpos($subject, $s, 0, 'UTF-8');
                        while($pos !== false) {
                                $subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
                                $pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
                        }
                }
                return $subject;
        }
}