Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5 class Logical 6 { 7 /** 8 * TRUE. 9 * 10 * Returns the boolean TRUE. 11 * 12 * Excel Function: 13 * =TRUE() 14 * 15 * @return bool True 16 */ 17 public static function true() 18 { 19 return true; 20 } 21 22 /** 23 * FALSE. 24 * 25 * Returns the boolean FALSE. 26 * 27 * Excel Function: 28 * =FALSE() 29 * 30 * @return bool False 31 */ 32 public static function false() 33 { 34 return false; 35 } 36 37 private static function countTrueValues(array $args) 38 { 39 $returnValue = 0; 40 41 foreach ($args as $arg) { 42 // Is it a boolean value? 43 if (is_bool($arg)) { 44 $returnValue += $arg; 45 } elseif ((is_numeric($arg)) && (!is_string($arg))) { 46 $returnValue += ((int) $arg != 0); 47 } elseif (is_string($arg)) { 48 $arg = strtoupper($arg); 49 if (($arg == 'TRUE') || ($arg == Calculation::getTRUE())) { 50 $arg = true; 51 } elseif (($arg == 'FALSE') || ($arg == Calculation::getFALSE())) { 52 $arg = false; 53 } else { 54 return Functions::VALUE(); 55 } 56 $returnValue += ($arg != 0); 57 } 58 } 59 60 return $returnValue; 61 } 62 63 /** 64 * LOGICAL_AND. 65 * 66 * Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE. 67 * 68 * Excel Function: 69 * =AND(logical1[,logical2[, ...]]) 70 * 71 * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays 72 * or references that contain logical values. 73 * 74 * Boolean arguments are treated as True or False as appropriate 75 * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False 76 * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds 77 * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value 78 * 79 * @param mixed ...$args Data values 80 * 81 * @return bool|string the logical AND of the arguments 82 */ 83 public static function logicalAnd(...$args) 84 { 85 $args = Functions::flattenArray($args); 86 87 if (count($args) == 0) { 88 return Functions::VALUE(); 89 } 90 91 $args = array_filter($args, function ($value) { 92 return $value !== null || (is_string($value) && trim($value) == ''); 93 }); 94 $argCount = count($args); 95 96 $returnValue = self::countTrueValues($args); 97 if (is_string($returnValue)) { 98 return $returnValue; 99 } 100 101 return ($returnValue > 0) && ($returnValue == $argCount); 102 } 103 104 /** 105 * LOGICAL_OR. 106 * 107 * Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE. 108 * 109 * Excel Function: 110 * =OR(logical1[,logical2[, ...]]) 111 * 112 * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays 113 * or references that contain logical values. 114 * 115 * Boolean arguments are treated as True or False as appropriate 116 * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False 117 * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds 118 * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value 119 * 120 * @param mixed $args Data values 121 * 122 * @return bool|string the logical OR of the arguments 123 */ 124 public static function logicalOr(...$args) 125 { 126 $args = Functions::flattenArray($args); 127 128 if (count($args) == 0) { 129 return Functions::VALUE(); 130 } 131 132 $args = array_filter($args, function ($value) { 133 return $value !== null || (is_string($value) && trim($value) == ''); 134 }); 135 136 $returnValue = self::countTrueValues($args); 137 if (is_string($returnValue)) { 138 return $returnValue; 139 } 140 141 return $returnValue > 0; 142 } 143 144 /** 145 * LOGICAL_XOR. 146 * 147 * Returns the Exclusive Or logical operation for one or more supplied conditions. 148 * i.e. the Xor function returns TRUE if an odd number of the supplied conditions evaluate to TRUE, and FALSE otherwise. 149 * 150 * Excel Function: 151 * =XOR(logical1[,logical2[, ...]]) 152 * 153 * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays 154 * or references that contain logical values. 155 * 156 * Boolean arguments are treated as True or False as appropriate 157 * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False 158 * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds 159 * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value 160 * 161 * @param mixed $args Data values 162 * 163 * @return bool|string the logical XOR of the arguments 164 */ 165 public static function logicalXor(...$args) 166 { 167 $args = Functions::flattenArray($args); 168 169 if (count($args) == 0) { 170 return Functions::VALUE(); 171 } 172 173 $args = array_filter($args, function ($value) { 174 return $value !== null || (is_string($value) && trim($value) == ''); 175 }); 176 177 $returnValue = self::countTrueValues($args); 178 if (is_string($returnValue)) { 179 return $returnValue; 180 } 181 182 return $returnValue % 2 == 1; 183 } 184 185 /** 186 * NOT. 187 * 188 * Returns the boolean inverse of the argument. 189 * 190 * Excel Function: 191 * =NOT(logical) 192 * 193 * The argument must evaluate to a logical value such as TRUE or FALSE 194 * 195 * Boolean arguments are treated as True or False as appropriate 196 * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False 197 * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds 198 * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value 199 * 200 * @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE 201 * 202 * @return bool|string the boolean inverse of the argument 203 */ 204 public static function NOT($logical = false) 205 { 206 $logical = Functions::flattenSingleValue($logical); 207 208 if (is_string($logical)) { 209 $logical = strtoupper($logical); 210 if (($logical == 'TRUE') || ($logical == Calculation::getTRUE())) { 211 return false; 212 } elseif (($logical == 'FALSE') || ($logical == Calculation::getFALSE())) { 213 return true; 214 } 215 216 return Functions::VALUE(); 217 } 218 219 return !$logical; 220 } 221 222 /** 223 * STATEMENT_IF. 224 * 225 * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. 226 * 227 * Excel Function: 228 * =IF(condition[,returnIfTrue[,returnIfFalse]]) 229 * 230 * Condition is any value or expression that can be evaluated to TRUE or FALSE. 231 * For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, 232 * the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. 233 * This argument can use any comparison calculation operator. 234 * ReturnIfTrue is the value that is returned if condition evaluates to TRUE. 235 * For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE, 236 * then the IF function returns the text "Within budget" 237 * If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use 238 * the logical value TRUE for this argument. 239 * ReturnIfTrue can be another formula. 240 * ReturnIfFalse is the value that is returned if condition evaluates to FALSE. 241 * For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE, 242 * then the IF function returns the text "Over budget". 243 * If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned. 244 * If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned. 245 * ReturnIfFalse can be another formula. 246 * 247 * @param mixed $condition Condition to evaluate 248 * @param mixed $returnIfTrue Value to return when condition is true 249 * @param mixed $returnIfFalse Optional value to return when condition is false 250 * 251 * @return mixed The value of returnIfTrue or returnIfFalse determined by condition 252 */ 253 public static function statementIf($condition = true, $returnIfTrue = 0, $returnIfFalse = false) 254 { 255 if (Functions::isError($condition)) { 256 return $condition; 257 } 258 259 $condition = ($condition === null) ? true : (bool) Functions::flattenSingleValue($condition); 260 $returnIfTrue = ($returnIfTrue === null) ? 0 : Functions::flattenSingleValue($returnIfTrue); 261 $returnIfFalse = ($returnIfFalse === null) ? false : Functions::flattenSingleValue($returnIfFalse); 262 263 return ($condition) ? $returnIfTrue : $returnIfFalse; 264 } 265 266 /** 267 * STATEMENT_SWITCH. 268 * 269 * Returns corresponding with first match (any data type such as a string, numeric, date, etc). 270 * 271 * Excel Function: 272 * =SWITCH (expression, value1, result1, value2, result2, ... value_n, result_n [, default]) 273 * 274 * Expression 275 * The expression to compare to a list of values. 276 * value1, value2, ... value_n 277 * A list of values that are compared to expression. The SWITCH function is looking for the first value that matches the expression. 278 * result1, result2, ... result_n 279 * A list of results. The SWITCH function returns the corresponding result when a value matches expression. 280 * default 281 * Optional. It is the default to return if expression does not match any of the values (value1, value2, ... value_n). 282 * 283 * @param mixed $arguments Statement arguments 284 * 285 * @return mixed The value of matched expression 286 */ 287 public static function statementSwitch(...$arguments) 288 { 289 $result = Functions::VALUE(); 290 291 if (count($arguments) > 0) { 292 $targetValue = Functions::flattenSingleValue($arguments[0]); 293 $argc = count($arguments) - 1; 294 $switchCount = floor($argc / 2); 295 $switchSatisfied = false; 296 $hasDefaultClause = $argc % 2 !== 0; 297 $defaultClause = $argc % 2 === 0 ? null : $arguments[count($arguments) - 1]; 298 299 if ($switchCount) { 300 for ($index = 0; $index < $switchCount; ++$index) { 301 if ($targetValue == $arguments[$index * 2 + 1]) { 302 $result = $arguments[$index * 2 + 2]; 303 $switchSatisfied = true; 304 305 break; 306 } 307 } 308 } 309 310 if (!$switchSatisfied) { 311 $result = $hasDefaultClause ? $defaultClause : Functions::NA(); 312 } 313 } 314 315 return $result; 316 } 317 318 /** 319 * IFERROR. 320 * 321 * Excel Function: 322 * =IFERROR(testValue,errorpart) 323 * 324 * @param mixed $testValue Value to check, is also the value returned when no error 325 * @param mixed $errorpart Value to return when testValue is an error condition 326 * 327 * @return mixed The value of errorpart or testValue determined by error condition 328 */ 329 public static function IFERROR($testValue = '', $errorpart = '') 330 { 331 $testValue = ($testValue === null) ? '' : Functions::flattenSingleValue($testValue); 332 $errorpart = ($errorpart === null) ? '' : Functions::flattenSingleValue($errorpart); 333 334 return self::statementIf(Functions::isError($testValue), $errorpart, $testValue); 335 } 336 337 /** 338 * IFNA. 339 * 340 * Excel Function: 341 * =IFNA(testValue,napart) 342 * 343 * @param mixed $testValue Value to check, is also the value returned when not an NA 344 * @param mixed $napart Value to return when testValue is an NA condition 345 * 346 * @return mixed The value of errorpart or testValue determined by error condition 347 */ 348 public static function IFNA($testValue = '', $napart = '') 349 { 350 $testValue = ($testValue === null) ? '' : Functions::flattenSingleValue($testValue); 351 $napart = ($napart === null) ? '' : Functions::flattenSingleValue($napart); 352 353 return self::statementIf(Functions::isNa($testValue), $napart, $testValue); 354 } 355 356 /** 357 * IFS. 358 * 359 * Excel Function: 360 * =IFS(testValue1;returnIfTrue1;testValue2;returnIfTrue2;...;testValue_n;returnIfTrue_n) 361 * 362 * testValue1 ... testValue_n 363 * Conditions to Evaluate 364 * returnIfTrue1 ... returnIfTrue_n 365 * Value returned if corresponding testValue (nth) was true 366 * 367 * @param mixed ...$arguments Statement arguments 368 * 369 * @return mixed|string The value of returnIfTrue_n, if testValue_n was true. #N/A if none of testValues was true 370 */ 371 public static function IFS(...$arguments) 372 { 373 if (count($arguments) % 2 != 0) { 374 return Functions::NA(); 375 } 376 // We use instance of Exception as a falseValue in order to prevent string collision with value in cell 377 $falseValueException = new Exception(); 378 for ($i = 0; $i < count($arguments); $i += 2) { 379 $testValue = ($arguments[$i] === null) ? '' : Functions::flattenSingleValue($arguments[$i]); 380 $returnIfTrue = ($arguments[$i + 1] === null) ? '' : Functions::flattenSingleValue($arguments[$i + 1]); 381 $result = self::statementIf($testValue, $returnIfTrue, $falseValueException); 382 383 if ($result !== $falseValueException) { 384 return $result; 385 } 386 } 387 388 return Functions::NA(); 389 } 390 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body