[Summary view]

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

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