[Summary view]

Differences Between: [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Logical;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
  10  use 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)) {
  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  }