Subversion Repositories eFlore/Applications.cel

<?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;

	}

}