[Summary view]

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

   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 == $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  
 143          return self::statementIf(ErrorValue::isError($testValue), $errorpart, $testValue);
 144      }
 145  
 146      /**
 147       * IFNA.
 148       *
 149       * Excel Function:
 150       *        =IFNA(testValue,napart)
 151       *
 152       * @param mixed $testValue Value to check, is also the value returned when not an NA
 153       *                      Or can be an array of values
 154       * @param mixed $napart Value to return when testValue is an NA condition
 155       *              Note that this can be an array value to be returned
 156       *
 157       * @return mixed The value of errorpart or testValue determined by error condition
 158       *         If an array of values is passed as the $testValue argument, then the returned result will also be
 159       *            an array with the same dimensions
 160       */
 161      public static function IFNA($testValue = '', $napart = '')
 162      {
 163          if (is_array($testValue)) {
 164              return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $testValue, $napart);
 165          }
 166  
 167          $napart = $napart ?? '';
 168  
 169          return self::statementIf(ErrorValue::isNa($testValue), $napart, $testValue);
 170      }
 171  
 172      /**
 173       * IFS.
 174       *
 175       * Excel Function:
 176       *         =IFS(testValue1;returnIfTrue1;testValue2;returnIfTrue2;...;testValue_n;returnIfTrue_n)
 177       *
 178       *         testValue1 ... testValue_n
 179       *             Conditions to Evaluate
 180       *         returnIfTrue1 ... returnIfTrue_n
 181       *             Value returned if corresponding testValue (nth) was true
 182       *
 183       * @param mixed ...$arguments Statement arguments
 184       *              Note that this can be an array value to be returned
 185       *
 186       * @return mixed|string The value of returnIfTrue_n, if testValue_n was true. #N/A if none of testValues was true
 187       */
 188      public static function IFS(...$arguments)
 189      {
 190          $argumentCount = count($arguments);
 191  
 192          if ($argumentCount % 2 != 0) {
 193              return ExcelError::NA();
 194          }
 195          // We use instance of Exception as a falseValue in order to prevent string collision with value in cell
 196          $falseValueException = new Exception();
 197          for ($i = 0; $i < $argumentCount; $i += 2) {
 198              $testValue = ($arguments[$i] === null) ? '' : Functions::flattenSingleValue($arguments[$i]);
 199              $returnIfTrue = ($arguments[$i + 1] === null) ? '' : $arguments[$i + 1];
 200              $result = self::statementIf($testValue, $returnIfTrue, $falseValueException);
 201  
 202              if ($result !== $falseValueException) {
 203                  return $result;
 204              }
 205          }
 206  
 207          return ExcelError::NA();
 208      }
 209  }