visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Calculation / Functions.php

Functions.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.4.8, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Functions.php

674 lines 16.5 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Calculation;
4
5	use PhpOffice\PhpSpreadsheet\Cell\Cell;
6
7	class Functions
8	{
9	const PRECISION = 8.88E-016;
10
11	/**
12	* 2 / PI.
13	*/
14	const M_2DIVPI = 0.63661977236758134307553505349006;
15
16	/** constants */
17	const COMPATIBILITY_EXCEL = 'Excel';
18	const COMPATIBILITY_GNUMERIC = 'Gnumeric';
19	const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
20	const RETURNDATE_PHP_NUMERIC = 'P';
21	const RETURNDATE_PHP_OBJECT = 'O';
22	const RETURNDATE_EXCEL = 'E';
23
24	/**
25	* Compatibility mode to use for error checking and responses.
26	*
27	* @var string
28	*/
29	protected static $compatibilityMode = self::COMPATIBILITY_EXCEL;
30
31	/**
32	* Data Type to use when returning date values.
33	*
34	* @var string
35	*/
36	protected static $returnDateType = self::RETURNDATE_EXCEL;
37
38	/**
39	* List of error codes.
40	*
41	* @var array
42	*/
43	protected static $errorCodes = [
44	'null' => '#NULL!',
45	'divisionbyzero' => '#DIV/0!',
46	'value' => '#VALUE!',
47	'reference' => '#REF!',
48	'name' => '#NAME?',
49	'num' => '#NUM!',
50	'na' => '#N/A',
51	'gettingdata' => '#GETTING_DATA',
52	];
53
54	/**
55	* Set the Compatibility Mode.
56	*
57	* @category Function Configuration
58	*
59	* @param string $compatibilityMode Compatibility Mode
60	* Permitted values are:
61	* Functions::COMPATIBILITY_EXCEL 'Excel'
62	* Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
63	* Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
64	*
65	* @return bool (Success or Failure)
66	*/
67	public static function setCompatibilityMode($compatibilityMode)
68	{
69	if (($compatibilityMode == self::COMPATIBILITY_EXCEL) \|\|
70	($compatibilityMode == self::COMPATIBILITY_GNUMERIC) \|\|
71	($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)
72	) {
73	self::$compatibilityMode = $compatibilityMode;
74
75	return true;
76	}
77
78	return false;
79	}
80
81	/**
82	* Return the current Compatibility Mode.
83	*
84	* @category Function Configuration
85	*
86	* @return string Compatibility Mode
87	* Possible Return values are:
88	* Functions::COMPATIBILITY_EXCEL 'Excel'
89	* Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
90	* Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
91	*/
92	public static function getCompatibilityMode()
93	{
94	return self::$compatibilityMode;
95	}
96
97	/**
98	* Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).
99	*
100	* @category Function Configuration
101	*
102	* @param string $returnDateType Return Date Format
103	* Permitted values are:
104	* Functions::RETURNDATE_PHP_NUMERIC 'P'
105	* Functions::RETURNDATE_PHP_OBJECT 'O'
106	* Functions::RETURNDATE_EXCEL 'E'
107	*
108	* @return bool Success or failure
109	*/
110	public static function setReturnDateType($returnDateType)
111	{
112	if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) \|\|
113	($returnDateType == self::RETURNDATE_PHP_OBJECT) \|\|
114	($returnDateType == self::RETURNDATE_EXCEL)
115	) {
116	self::$returnDateType = $returnDateType;
117
118	return true;
119	}
120
121	return false;
122	}
123
124	/**
125	* Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).
126	*
127	* @category Function Configuration
128	*
129	* @return string Return Date Format
130	* Possible Return values are:
131	* Functions::RETURNDATE_PHP_NUMERIC 'P'
132	* Functions::RETURNDATE_PHP_OBJECT 'O'
133	* Functions::RETURNDATE_EXCEL 'E'
134	*/
135	public static function getReturnDateType()
136	{
137	return self::$returnDateType;
138	}
139
140	/**
141	* DUMMY.
142	*
143	* @category Error Returns
144	*
145	* @return string #Not Yet Implemented
146	*/
147	public static function DUMMY()
148	{
149	return '#Not Yet Implemented';
150	}
151
152	/**
153	* DIV0.
154	*
155	* @category Error Returns
156	*
157	* @return string #Not Yet Implemented
158	*/
159	public static function DIV0()
160	{
161	return self::$errorCodes['divisionbyzero'];
162	}
163
164	/**
165	* NA.
166	*
167	* Excel Function:
168	* =NA()
169	*
170	* Returns the error value #N/A
171	* #N/A is the error value that means "no value is available."
172	*
173	* @category Logical Functions
174	*
175	* @return string #N/A!
176	*/
177	public static function NA()
178	{
179	return self::$errorCodes['na'];
180	}
181
182	/**
183	* NaN.
184	*
185	* Returns the error value #NUM!
186	*
187	* @category Error Returns
188	*
189	* @return string #NUM!
190	*/
191	public static function NAN()
192	{
193	return self::$errorCodes['num'];
194	}
195
196	/**
197	* NAME.
198	*
199	* Returns the error value #NAME?
200	*
201	* @category Error Returns
202	*
203	* @return string #NAME?
204	*/
205	public static function NAME()
206	{
207	return self::$errorCodes['name'];
208	}
209
210	/**
211	* REF.
212	*
213	* Returns the error value #REF!
214	*
215	* @category Error Returns
216	*
217	* @return string #REF!
218	*/
219	public static function REF()
220	{
221	return self::$errorCodes['reference'];
222	}
223
224	/**
225	* NULL.
226	*
227	* Returns the error value #NULL!
228	*
229	* @category Error Returns
230	*
231	* @return string #NULL!
232	*/
233	public static function null()
234	{
235	return self::$errorCodes['null'];
236	}
237
238	/**
239	* VALUE.
240	*
241	* Returns the error value #VALUE!
242	*
243	* @category Error Returns
244	*
245	* @return string #VALUE!
246	*/
247	public static function VALUE()
248	{
249	return self::$errorCodes['value'];
250	}
251
252	public static function isMatrixValue($idx)
253	{
254	return (substr_count($idx, '.') <= 1) \|\| (preg_match('/\.[A-Z]/', $idx) > 0);
255	}
256
257	public static function isValue($idx)
258	{
259	return substr_count($idx, '.') == 0;
260	}
261
262	public static function isCellValue($idx)
263	{
264	return substr_count($idx, '.') > 1;
265	}
266
267	public static function ifCondition($condition)
268	{
269	$condition = self::flattenSingleValue($condition);
270	if (!isset($condition[0]) && !is_numeric($condition)) {
271	$condition = '=""';
272	}
273	if (!in_array($condition[0], ['>', '<', '='])) {
274	if (!is_numeric($condition)) {
275	$condition = Calculation::wrapResult(strtoupper($condition));
276	}
277
278	return '=' . $condition;
279	}
280	preg_match('/(=\|<[>=]?\|>=?)(.*)/', $condition, $matches);
281	list(, $operator, $operand) = $matches;
282
283	if (!is_numeric($operand)) {
284	$operand = str_replace('"', '""', $operand);
285	$operand = Calculation::wrapResult(strtoupper($operand));
286	}
287
288	return $operator . $operand;
289	}
290
291	/**
292	* ERROR_TYPE.
293	*
294	* @param mixed $value Value to check
295	*
296	* @return bool
297	*/
298	public static function errorType($value = '')
299	{
300	$value = self::flattenSingleValue($value);
301
302	$i = 1;
303	foreach (self::$errorCodes as $errorCode) {
304	if ($value === $errorCode) {
305	return $i;
306	}
307	++$i;
308	}
309
310	return self::NA();
311	}
312
313	/**
314	* IS_BLANK.
315	*
316	* @param mixed $value Value to check
317	*
318	* @return bool
319	*/
320	public static function isBlank($value = null)
321	{
322	if ($value !== null) {
323	$value = self::flattenSingleValue($value);
324	}
325
326	return $value === null;
327	}
328
329	/**
330	* IS_ERR.
331	*
332	* @param mixed $value Value to check
333	*
334	* @return bool
335	*/
336	public static function isErr($value = '')
337	{
338	$value = self::flattenSingleValue($value);
339
340	return self::isError($value) && (!self::isNa(($value)));
341	}
342
343	/**
344	* IS_ERROR.
345	*
346	* @param mixed $value Value to check
347	*
348	* @return bool
349	*/
350	public static function isError($value = '')
351	{
352	$value = self::flattenSingleValue($value);
353
354	if (!is_string($value)) {
355	return false;
356	}
357
358	return in_array($value, self::$errorCodes);
359	}
360
361	/**
362	* IS_NA.
363	*
364	* @param mixed $value Value to check
365	*
366	* @return bool
367	*/
368	public static function isNa($value = '')
369	{
370	$value = self::flattenSingleValue($value);
371
372	return $value === self::NA();
373	}
374
375	/**
376	* IS_EVEN.
377	*
378	* @param mixed $value Value to check
379	*
380	* @return bool\|string
381	*/
382	public static function isEven($value = null)
383	{
384	$value = self::flattenSingleValue($value);
385
386	if ($value === null) {
387	return self::NAME();
388	} elseif ((is_bool($value)) \|\| ((is_string($value)) && (!is_numeric($value)))) {
389	return self::VALUE();
390	}
391
392	return $value % 2 == 0;
393	}
394
395	/**
396	* IS_ODD.
397	*
398	* @param mixed $value Value to check
399	*
400	* @return bool\|string
401	*/
402	public static function isOdd($value = null)
403	{
404	$value = self::flattenSingleValue($value);
405
406	if ($value === null) {
407	return self::NAME();
408	} elseif ((is_bool($value)) \|\| ((is_string($value)) && (!is_numeric($value)))) {
409	return self::VALUE();
410	}
411
412	return abs($value) % 2 == 1;
413	}
414
415	/**
416	* IS_NUMBER.
417	*
418	* @param mixed $value Value to check
419	*
420	* @return bool
421	*/
422	public static function isNumber($value = null)
423	{
424	$value = self::flattenSingleValue($value);
425
426	if (is_string($value)) {
427	return false;
428	}
429
430	return is_numeric($value);
431	}
432
433	/**
434	* IS_LOGICAL.
435	*
436	* @param mixed $value Value to check
437	*
438	* @return bool
439	*/
440	public static function isLogical($value = null)
441	{
442	$value = self::flattenSingleValue($value);
443
444	return is_bool($value);
445	}
446
447	/**
448	* IS_TEXT.
449	*
450	* @param mixed $value Value to check
451	*
452	* @return bool
453	*/
454	public static function isText($value = null)
455	{
456	$value = self::flattenSingleValue($value);
457
458	return is_string($value) && !self::isError($value);
459	}
460
461	/**
462	* IS_NONTEXT.
463	*
464	* @param mixed $value Value to check
465	*
466	* @return bool
467	*/
468	public static function isNonText($value = null)
469	{
470	return !self::isText($value);
471	}
472
473	/**
474	* N.
475	*
476	* Returns a value converted to a number
477	*
478	* @param null\|mixed $value The value you want converted
479	*
480	* @return number N converts values listed in the following table
481	* If value is or refers to N returns
482	* A number That number
483	* A date The serial number of that date
484	* TRUE 1
485	* FALSE 0
486	* An error value The error value
487	* Anything else 0
488	*/
489	public static function n($value = null)
490	{
491	while (is_array($value)) {
492	$value = array_shift($value);
493	}
494
495	switch (gettype($value)) {
496	case 'double':
497	case 'float':
498	case 'integer':
499	return $value;
500	case 'boolean':
501	return (int) $value;
502	case 'string':
503	// Errors
504	if ((strlen($value) > 0) && ($value[0] == '#')) {
505	return $value;
506	}
507
508	break;
509	}
510
511	return 0;
512	}
513
514	/**
515	* TYPE.
516	*
517	* Returns a number that identifies the type of a value
518	*
519	* @param null\|mixed $value The value you want tested
520	*
521	* @return number N converts values listed in the following table
522	* If value is or refers to N returns
523	* A number 1
524	* Text 2
525	* Logical Value 4
526	* An error value 16
527	* Array or Matrix 64
528	*/
529	public static function TYPE($value = null)
530	{
531	$value = self::flattenArrayIndexed($value);
532	if (is_array($value) && (count($value) > 1)) {
533	end($value);
534	$a = key($value);
535	// Range of cells is an error
536	if (self::isCellValue($a)) {
537	return 16;
538	// Test for Matrix
539	} elseif (self::isMatrixValue($a)) {
540	return 64;
541	}
542	} elseif (empty($value)) {
543	// Empty Cell
544	return 1;
545	}
546	$value = self::flattenSingleValue($value);
547
548	if (($value === null) \|\| (is_float($value)) \|\| (is_int($value))) {
549	return 1;
550	} elseif (is_bool($value)) {
551	return 4;
552	} elseif (is_array($value)) {
553	return 64;
554	} elseif (is_string($value)) {
555	// Errors
556	if ((strlen($value) > 0) && ($value[0] == '#')) {
557	return 16;
558	}
559
560	return 2;
561	}
562
563	return 0;
564	}
565
566	/**
567	* Convert a multi-dimensional array to a simple 1-dimensional array.
568	*
569	* @param array $array Array to be flattened
570	*
571	* @return array Flattened array
572	*/
573	public static function flattenArray($array)
574	{
575	if (!is_array($array)) {
576	return (array) $array;
577	}
578
579	$arrayValues = [];
580	foreach ($array as $value) {
581	if (is_array($value)) {
582	foreach ($value as $val) {
583	if (is_array($val)) {
584	foreach ($val as $v) {
585	$arrayValues[] = $v;
586	}
587	} else {
588	$arrayValues[] = $val;
589	}
590	}
591	} else {
592	$arrayValues[] = $value;
593	}
594	}
595
596	return $arrayValues;
597	}
598
599	/**
600	* Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.
601	*
602	* @param array $array Array to be flattened
603	*
604	* @return array Flattened array
605	*/
606	public static function flattenArrayIndexed($array)
607	{
608	if (!is_array($array)) {
609	return (array) $array;
610	}
611
612	$arrayValues = [];
613	foreach ($array as $k1 => $value) {
614	if (is_array($value)) {
615	foreach ($value as $k2 => $val) {
616	if (is_array($val)) {
617	foreach ($val as $k3 => $v) {
618	$arrayValues[$k1 . '.' . $k2 . '.' . $k3] = $v;
619	}
620	} else {
621	$arrayValues[$k1 . '.' . $k2] = $val;
622	}
623	}
624	} else {
625	$arrayValues[$k1] = $value;
626	}
627	}
628
629	return $arrayValues;
630	}
631
632	/**
633	* Convert an array to a single scalar value by extracting the first element.
634	*
635	* @param mixed $value Array or scalar value
636	*
637	* @return mixed
638	*/
639	public static function flattenSingleValue($value = '')
640	{
641	while (is_array($value)) {
642	$value = array_pop($value);
643	}
644
645	return $value;
646	}
647
648	/**
649	* ISFORMULA.
650	*
651	* @param mixed $cellReference The cell to check
652	* @param Cell $pCell The current cell (containing this formula)
653	*
654	* @return bool\|string
655	*/
656	public static function isFormula($cellReference = '', Cell $pCell = null)
657	{
658	if ($pCell === null) {
659	return self::REF();
660	}
661
662	preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches);
663
664	$cellReference = $matches[6] . $matches[7];
665	$worksheetName = trim($matches[3], "'");
666
667	$worksheet = (!empty($worksheetName))
668	? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName)
669	: $pCell->getWorksheet();
670
671	return $worksheet->getCell($cellReference)->isFormula();
672	}
673	}
674