tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Logical / Conditional.php

Conditional.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/Logical/Conditional.php

212 lines 8.1 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Logical;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
10	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\Value;
11
12	class Conditional
13	{
14	use ArrayEnabled;
15
16	/**
17	* STATEMENT_IF.
18	*
19	* Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
20	*
21	* Excel Function:
22	* =IF(condition[,returnIfTrue[,returnIfFalse]])
23	*
24	* Condition is any value or expression that can be evaluated to TRUE or FALSE.
25	* For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
26	* the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
27	* This argument can use any comparison calculation operator.
28	* ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
29	* For example, if this argument is the text string "Within budget" and
30	* the condition argument evaluates to TRUE, then the IF function returns the text "Within budget"
31	* If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero).
32	* To display the word TRUE, use the logical value TRUE for this argument.
33	* ReturnIfTrue can be another formula.
34	* ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
35	* For example, if this argument is the text string "Over budget" and the condition argument evaluates
36	* to FALSE, then the IF function returns the text "Over budget".
37	* If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
38	* If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
39	* ReturnIfFalse can be another formula.
40	*
41	* @param mixed $condition Condition to evaluate
42	* @param mixed $returnIfTrue Value to return when condition is true
43	* Note that this can be an array value
44	* @param mixed $returnIfFalse Optional value to return when condition is false
45	* Note that this can be an array value
46	*
47	* @return mixed The value of returnIfTrue or returnIfFalse determined by condition
48	*/
49	public static function statementIf($condition = true, $returnIfTrue = 0, $returnIfFalse = false)
50	{
51	$condition = ($condition === null) ? true : Functions::flattenSingleValue($condition);
52
53	if (ErrorValue::isError($condition, true)) {
54	return $condition;
55	}
56
57	$returnIfTrue = $returnIfTrue ?? 0;
58	$returnIfFalse = $returnIfFalse ?? false;
59
60	return ((bool) $condition) ? $returnIfTrue : $returnIfFalse;
61	}
62
63	/**
64	* STATEMENT_SWITCH.
65	*
66	* Returns corresponding with first match (any data type such as a string, numeric, date, etc).
67	*
68	* Excel Function:
69	* =SWITCH (expression, value1, result1, value2, result2, ... value_n, result_n [, default])
70	*
71	* Expression
72	* The expression to compare to a list of values.
73	* value1, value2, ... value_n
74	* A list of values that are compared to expression.
75	* The SWITCH function is looking for the first value that matches the expression.
76	* result1, result2, ... result_n
77	* A list of results. The SWITCH function returns the corresponding result when a value
78	* matches expression.
79	* Note that these can be array values to be returned
80	* default
81	* Optional. It is the default to return if expression does not match any of the values
82	* (value1, value2, ... value_n).
83	* Note that this can be an array value to be returned
84	*
85	* @param mixed $arguments Statement arguments
86	*
87	* @return mixed The value of matched expression
88	*/
89	public static function statementSwitch(...$arguments)
90	{
91	$result = ExcelError::VALUE();
92
93	if (count($arguments) > 0) {
94	$targetValue = Functions::flattenSingleValue($arguments[0]);
95	$argc = count($arguments) - 1;
96	$switchCount = floor($argc / 2);
97	$hasDefaultClause = $argc % 2 !== 0;
98	$defaultClause = $argc % 2 === 0 ? null : $arguments[$argc];
99
100	$switchSatisfied = false;
101	if ($switchCount > 0) {
102	for ($index = 0; $index < $switchCount; ++$index) {
103	if ($targetValue == Functions::flattenSingleValue($arguments[$index * 2 + 1])) {
104	$result = $arguments[$index * 2 + 2];
105	$switchSatisfied = true;
106
107	break;
108	}
109	}
110	}
111
112	if ($switchSatisfied !== true) {
113	$result = $hasDefaultClause ? $defaultClause : ExcelError::NA();
114	}
115	}
116
117	return $result;
118	}
119
120	/**
121	* IFERROR.
122	*
123	* Excel Function:
124	* =IFERROR(testValue,errorpart)
125	*
126	* @param mixed $testValue Value to check, is also the value returned when no error
127	* Or can be an array of values
128	* @param mixed $errorpart Value to return when testValue is an error condition
129	* Note that this can be an array value to be returned
130	*
131	* @return mixed The value of errorpart or testValue determined by error condition
132	* If an array of values is passed as the $testValue argument, then the returned result will also be
133	* an array with the same dimensions
134	*/
135	public static function IFERROR($testValue = '', $errorpart = '')
136	{
137	if (is_array($testValue)) {
138	return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $testValue, $errorpart);
139	}
140
141	$errorpart = $errorpart ?? '';
142	$testValue = $testValue ?? 0; // this is how Excel handles empty cell
143
144	return self::statementIf(ErrorValue::isError($testValue), $errorpart, $testValue);
145	}
146
147	/**
148	* IFNA.
149	*
150	* Excel Function:
151	* =IFNA(testValue,napart)
152	*
153	* @param mixed $testValue Value to check, is also the value returned when not an NA
154	* Or can be an array of values
155	* @param mixed $napart Value to return when testValue is an NA condition
156	* Note that this can be an array value to be returned
157	*
158	* @return mixed The value of errorpart or testValue determined by error condition
159	* If an array of values is passed as the $testValue argument, then the returned result will also be
160	* an array with the same dimensions
161	*/
162	public static function IFNA($testValue = '', $napart = '')
163	{
164	if (is_array($testValue)) {
165	return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $testValue, $napart);
166	}
167
168	$napart = $napart ?? '';
169	$testValue = $testValue ?? 0; // this is how Excel handles empty cell
170
171	return self::statementIf(ErrorValue::isNa($testValue), $napart, $testValue);
172	}
173
174	/**
175	* IFS.
176	*
177	* Excel Function:
178	* =IFS(testValue1;returnIfTrue1;testValue2;returnIfTrue2;...;testValue_n;returnIfTrue_n)
179	*
180	* testValue1 ... testValue_n
181	* Conditions to Evaluate
182	* returnIfTrue1 ... returnIfTrue_n
183	* Value returned if corresponding testValue (nth) was true
184	*
185	* @param mixed ...$arguments Statement arguments
186	* Note that this can be an array value to be returned
187	*
188	* @return mixed\|string The value of returnIfTrue_n, if testValue_n was true. #N/A if none of testValues was true
189	*/
190	public static function IFS(...$arguments)
191	{
192	$argumentCount = count($arguments);
193
194	if ($argumentCount % 2 != 0) {
195	return ExcelError::NA();
196	}
197	// We use instance of Exception as a falseValue in order to prevent string collision with value in cell
198	$falseValueException = new Exception();
199	for ($i = 0; $i < $argumentCount; $i += 2) {
200	$testValue = ($arguments[$i] === null) ? '' : Functions::flattenSingleValue($arguments[$i]);
201	$returnIfTrue = ($arguments[$i + 1] === null) ? '' : $arguments[$i + 1];
202	$result = self::statementIf($testValue, $returnIfTrue, $falseValueException);
203
204	if ($result !== $falseValueException) {
205	return $result;
206	}
207	}
208
209	return ExcelError::NA();
210	}
211	}
212