visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / Calculation / Functions.php

Functions.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/Functions.php

726 lines 17.0 KB

Raw Download Zip

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