Subversion Repositories eFlore/Applications.cel

Rev

Blame | Last modification | View Log | RSS feed

<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 *
 * @category    PHPExcel
 * @package             PHPExcel_Calculation
 * @copyright   Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license             http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt   LGPL
 * @version             ##VERSION##, ##DATE##
 */


/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
        /**
         * @ignore
         */
        define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
        require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}


/** MAX_VALUE */
define('MAX_VALUE', 1.2e308);

/** 2 / PI */
define('M_2DIVPI', 0.63661977236758134307553505349006);

/** MAX_ITERATIONS */
define('MAX_ITERATIONS', 256);

/** PRECISION */
define('PRECISION', 8.88E-016);


/**
 * PHPExcel_Calculation_Functions
 *
 * @category    PHPExcel
 * @package             PHPExcel_Calculation
 * @copyright   Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel_Calculation_Functions {

        /** constants */
        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';


        /**
         * Compatibility mode to use for error checking and responses
         *
         * @access      private
         * @var string
         */
        protected static $compatibilityMode     = self::COMPATIBILITY_EXCEL;

        /**
         * Data Type to use when returning date values
         *
         * @access      private
         * @var string
         */
        protected static $ReturnDateType        = self::RETURNDATE_EXCEL;

        /**
         * List of error codes
         *
         * @access      private
         * @var array
         */
        protected static $_errorCodes   = array( 'null'                         => '#NULL!',
                                                                                         'divisionbyzero'       => '#DIV/0!',
                                                                                         'value'                        => '#VALUE!',
                                                                                         'reference'            => '#REF!',
                                                                                         'name'                         => '#NAME?',
                                                                                         'num'                          => '#NUM!',
                                                                                         'na'                           => '#N/A',
                                                                                         'gettingdata'          => '#GETTING_DATA'
                                                                                   );


        /**
         * Set the Compatibility Mode
         *
         * @access      public
         * @category Function Configuration
         * @param        string         $compatibilityMode              Compatibility Mode
         *                                                                                              Permitted values are:
         *                                                                                                      PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL                     'Excel'
         *                                                                                                      PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC          'Gnumeric'
         *                                                                                                      PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE        'OpenOfficeCalc'
         * @return       boolean        (Success or Failure)
         */
        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()


        /**
         * Return the current Compatibility Mode
         *
         * @access      public
         * @category Function Configuration
         * @return       string         Compatibility Mode
         *                                                      Possible Return values are:
         *                                                              PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL                     'Excel'
         *                                                              PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC          'Gnumeric'
         *                                                              PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE        'OpenOfficeCalc'
         */
        public static function getCompatibilityMode() {
                return self::$compatibilityMode;
        }       //      function getCompatibilityMode()


        /**
         * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
         *
         * @access      public
         * @category Function Configuration
         * @param        string $returnDateType                 Return Date Format
         *                                                                                              Permitted values are:
         *                                                                                                      PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC          'P'
         *                                                                                                      PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT           'O'
         *                                                                                                      PHPExcel_Calculation_Functions::RETURNDATE_EXCEL                        'E'
         * @return       boolean                                                        Success or failure
         */
        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()


        /**
         * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
         *
         * @access      public
         * @category Function Configuration
         * @return       string         Return Date Format
         *                                                      Possible Return values are:
         *                                                              PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC          'P'
         *                                                              PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT           'O'
         *                                                              PHPExcel_Calculation_Functions::RETURNDATE_EXCEL                        'E'
         */
        public static function getReturnDateType() {
                return self::$ReturnDateType;
        }       //      function getReturnDateType()


        /**
         * DUMMY
         *
         * @access      public
         * @category Error Returns
         * @return      string  #Not Yet Implemented
         */
        public static function DUMMY() {
                return '#Not Yet Implemented';
        }       //      function DUMMY()


        /**
         * DIV0
         *
         * @access      public
         * @category Error Returns
         * @return      string  #Not Yet Implemented
         */
        public static function DIV0() {
                return self::$_errorCodes['divisionbyzero'];
        }       //      function DIV0()


        /**
         * NA
         *
         * Excel Function:
         *              =NA()
         *
         * Returns the error value #N/A
         *              #N/A is the error value that means "no value is available."
         *
         * @access      public
         * @category Logical Functions
         * @return      string  #N/A!
         */
        public static function NA() {
                return self::$_errorCodes['na'];
        }       //      function NA()


        /**
         * NaN
         *
         * Returns the error value #NUM!
         *
         * @access      public
         * @category Error Returns
         * @return      string  #NUM!
         */
        public static function NaN() {
                return self::$_errorCodes['num'];
        }       //      function NaN()


        /**
         * NAME
         *
         * Returns the error value #NAME?
         *
         * @access      public
         * @category Error Returns
         * @return      string  #NAME?
         */
        public static function NAME() {
                return self::$_errorCodes['name'];
        }       //      function NAME()


        /**
         * REF
         *
         * Returns the error value #REF!
         *
         * @access      public
         * @category Error Returns
         * @return      string  #REF!
         */
        public static function REF() {
                return self::$_errorCodes['reference'];
        }       //      function REF()


        /**
         * NULL
         *
         * Returns the error value #NULL!
         *
         * @access      public
         * @category Error Returns
         * @return      string  #REF!
         */
        public static function NULL() {
                return self::$_errorCodes['null'];
        }       //      function NULL()


        /**
         * VALUE
         *
         * Returns the error value #VALUE!
         *
         * @access      public
         * @category Error Returns
         * @return      string  #VALUE!
         */
        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 = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); }
                        return $operator.$operand;
                }
        }       //      function _ifCondition()


        /**
         * ERROR_TYPE
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        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()


        /**
         * IS_BLANK
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        public static function IS_BLANK($value = NULL) {
                if (!is_null($value)) {
                        $value  = self::flattenSingleValue($value);
                }

                return is_null($value);
        }       //      function IS_BLANK()


        /**
         * IS_ERR
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        public static function IS_ERR($value = '') {
                $value          = self::flattenSingleValue($value);

                return self::IS_ERROR($value) && (!self::IS_NA($value));
        }       //      function IS_ERR()


        /**
         * IS_ERROR
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        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()


        /**
         * IS_NA
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        public static function IS_NA($value = '') {
                $value          = self::flattenSingleValue($value);

                return ($value === self::NA());
        }       //      function IS_NA()


        /**
         * IS_EVEN
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        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()


        /**
         * IS_ODD
         *
         * @param       mixed   $value  Value to check
         * @return      boolean
         */
        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()


        /**
         * IS_NUMBER
         *
         * @param       mixed   $value          Value to check
         * @return      boolean
         */
        public static function IS_NUMBER($value = NULL) {
                $value          = self::flattenSingleValue($value);

                if (is_string($value)) {
                        return False;
                }
                return is_numeric($value);
        }       //      function IS_NUMBER()


        /**
         * IS_LOGICAL
         *
         * @param       mixed   $value          Value to check
         * @return      boolean
         */
        public static function IS_LOGICAL($value = NULL) {
                $value          = self::flattenSingleValue($value);

                return is_bool($value);
        }       //      function IS_LOGICAL()


        /**
         * IS_TEXT
         *
         * @param       mixed   $value          Value to check
         * @return      boolean
         */
        public static function IS_TEXT($value = NULL) {
                $value          = self::flattenSingleValue($value);

                return (is_string($value) && !self::IS_ERROR($value));
        }       //      function IS_TEXT()


        /**
         * IS_NONTEXT
         *
         * @param       mixed   $value          Value to check
         * @return      boolean
         */
        public static function IS_NONTEXT($value = NULL) {
                return !self::IS_TEXT($value);
        }       //      function IS_NONTEXT()


        /**
         * VERSION
         *
         * @return      string  Version information
         */
        public static function VERSION() {
                return 'PHPExcel ##VERSION##, ##DATE##';
        }       //      function VERSION()


        /**
         * N
         *
         * Returns a value converted to a number
         *
         * @param       value           The value you want converted
         * @return      number          N converts values listed in the following table
         *              If value is or refers to N returns
         *              A number                        That number
         *              A date                          The serial number of that date
         *              TRUE                            1
         *              FALSE                           0
         *              An error value          The error value
         *              Anything else           0
         */
        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()


        /**
         * TYPE
         *
         * Returns a number that identifies the type of a value
         *
         * @param       value           The value you want tested
         * @return      number          N converts values listed in the following table
         *              If value is or refers to N returns
         *              A number                        1
         *              Text                            2
         *              Logical Value           4
         *              An error value          16
         *              Array or Matrix         64
         */
        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;
                                break;
                } elseif(is_string($value)) {
                        //      Errors
                        if ((strlen($value) > 0) && ($value{0} == '#')) {
                                return 16;
                        }
                        return 2;
                }
                return 0;
        }       //      function TYPE()


        /**
         * Convert a multi-dimensional array to a simple 1-dimensional array
         *
         * @param       array   $array  Array to be flattened
         * @return      array   Flattened array
         */
        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()


        /**
         * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
         *
         * @param       array   $array  Array to be flattened
         * @return      array   Flattened array
         */
        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()


        /**
         * Convert an array to a single scalar value by extracting the first element
         *
         * @param       mixed           $value          Array or scalar value
         * @return      mixed
         */
        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()
}

if (!function_exists('money_format')) {
        function money_format($format, $number) {
                $regex = array( '/%((?:[\^!\-]|\+|\(|\=.)*)([0-9]+)?(?:#([0-9]+))?',
                                                 '(?:\.([0-9]+))?([in%])/'
                                          );
                $regex = implode('', $regex);
                if (setlocale(LC_MONETARY, null) == '') {
                        setlocale(LC_MONETARY, '');
                }
                $locale = localeconv();
                $number = floatval($number);
                if (!preg_match($regex, $format, $fmatch)) {
                        trigger_error("No format specified or invalid format", E_USER_WARNING);
                        return $number;
                }
                $flags = array( 'fillchar'      => preg_match('/\=(.)/', $fmatch[1], $match) ? $match[1] : ' ',
                                                'nogroup'       => preg_match('/\^/', $fmatch[1]) > 0,
                                                'usesignal'     => preg_match('/\+|\(/', $fmatch[1], $match) ? $match[0] : '+',
                                                'nosimbol'      => preg_match('/\!/', $fmatch[1]) > 0,
                                                'isleft'        => preg_match('/\-/', $fmatch[1]) > 0
                                          );
                $width  = trim($fmatch[2]) ? (int)$fmatch[2] : 0;
                $left   = trim($fmatch[3]) ? (int)$fmatch[3] : 0;
                $right  = trim($fmatch[4]) ? (int)$fmatch[4] : $locale['int_frac_digits'];
                $conversion = $fmatch[5];
                $positive = true;
                if ($number < 0) {
                        $positive = false;
                        $number *= -1;
                }
                $letter = $positive ? 'p' : 'n';
                $prefix = $suffix = $cprefix = $csuffix = $signal = '';
                if (!$positive) {
                        $signal = $locale['negative_sign'];
                        switch (true) {
                                case $locale['n_sign_posn'] == 0 || $flags['usesignal'] == '(':
                                        $prefix = '(';
                                        $suffix = ')';
                                        break;
                                case $locale['n_sign_posn'] == 1:
                                        $prefix = $signal;
                                        break;
                                case $locale['n_sign_posn'] == 2:
                                        $suffix = $signal;
                                        break;
                                case $locale['n_sign_posn'] == 3:
                                        $cprefix = $signal;
                                        break;
                                case $locale['n_sign_posn'] == 4:
                                        $csuffix = $signal;
                                        break;
                        }
                }
                if (!$flags['nosimbol']) {
                        $currency = $cprefix;
                        $currency .= ($conversion == 'i' ? $locale['int_curr_symbol'] : $locale['currency_symbol']);
                        $currency .= $csuffix;
                        $currency = iconv('ISO-8859-1','UTF-8',$currency);
                } else {
                        $currency = '';
                }
                $space = $locale["{$letter}_sep_by_space"] ? ' ' : '';

                if (!isset($locale['mon_decimal_point']) || empty($locale['mon_decimal_point'])) {
                        $locale['mon_decimal_point'] = (!isset($locale['decimal_point']) || empty($locale['decimal_point'])) ?
                                                                                        $locale['decimal_point'] :
                                                                                        '.';
                }

                $number = number_format($number, $right, $locale['mon_decimal_point'], $flags['nogroup'] ? '' : $locale['mon_thousands_sep'] );
                $number = explode($locale['mon_decimal_point'], $number);

                $n = strlen($prefix) + strlen($currency);
                if ($left > 0 && $left > $n) {
                        if ($flags['isleft']) {
                                $number[0] .= str_repeat($flags['fillchar'], $left - $n);
                        } else {
                                $number[0] = str_repeat($flags['fillchar'], $left - $n) . $number[0];
                        }
                }
                $number = implode($locale['mon_decimal_point'], $number);
                if ($locale["{$letter}_cs_precedes"]) {
                        $number = $prefix . $currency . $space . $number . $suffix;
                } else {
                        $number = $prefix . $number . $space . $currency . $suffix;
                }
                if ($width > 0) {
                        $number = str_pad($number, $width, $flags['fillchar'], $flags['isleft'] ? STR_PAD_RIGHT : STR_PAD_LEFT);
                }
                $format = str_replace($fmatch[0], $number, $format);
                return $format;
        }       //      function money_format()
}


//
//      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 == '') {
                                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;
        }
}