[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Exception;
   7  use PhpOffice\PhpSpreadsheet\Style\Conditional;
   8  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\CellMatcher;
   9  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard;
  10  
  11  /**
  12   * @method CellValue equals($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  13   * @method CellValue notEquals($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  14   * @method CellValue greaterThan($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  15   * @method CellValue greaterThanOrEqual($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  16   * @method CellValue lessThan($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  17   * @method CellValue lessThanOrEqual($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  18   * @method CellValue between($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  19   * @method CellValue notBetween($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  20   * @method CellValue and($value, string $operandValueType = Wizard::VALUE_TYPE_LITERAL)
  21   */
  22  class CellValue extends WizardAbstract implements WizardInterface
  23  {
  24      protected const MAGIC_OPERATIONS = [
  25          'equals' => Conditional::OPERATOR_EQUAL,
  26          'notEquals' => Conditional::OPERATOR_NOTEQUAL,
  27          'greaterThan' => Conditional::OPERATOR_GREATERTHAN,
  28          'greaterThanOrEqual' => Conditional::OPERATOR_GREATERTHANOREQUAL,
  29          'lessThan' => Conditional::OPERATOR_LESSTHAN,
  30          'lessThanOrEqual' => Conditional::OPERATOR_LESSTHANOREQUAL,
  31          'between' => Conditional::OPERATOR_BETWEEN,
  32          'notBetween' => Conditional::OPERATOR_NOTBETWEEN,
  33      ];
  34  
  35      protected const SINGLE_OPERATORS = CellMatcher::COMPARISON_OPERATORS;
  36  
  37      protected const RANGE_OPERATORS = CellMatcher::COMPARISON_RANGE_OPERATORS;
  38  
  39      /** @var string */
  40      protected $operator = Conditional::OPERATOR_EQUAL;
  41  
  42      /** @var array */
  43      protected $operand = [0];
  44  
  45      /**
  46       * @var string[]
  47       */
  48      protected $operandValueType = [];
  49  
  50      public function __construct(string $cellRange)
  51      {
  52          parent::__construct($cellRange);
  53      }
  54  
  55      protected function operator(string $operator): void
  56      {
  57          if ((!isset(self::SINGLE_OPERATORS[$operator])) && (!isset(self::RANGE_OPERATORS[$operator]))) {
  58              throw new Exception('Invalid Operator for Cell Value CF Rule Wizard');
  59          }
  60  
  61          $this->operator = $operator;
  62      }
  63  
  64      /**
  65       * @param mixed $operand
  66       */
  67      protected function operand(int $index, $operand, string $operandValueType = Wizard::VALUE_TYPE_LITERAL): void
  68      {
  69          if (is_string($operand)) {
  70              $operand = $this->validateOperand($operand, $operandValueType);
  71          }
  72  
  73          $this->operand[$index] = $operand;
  74          $this->operandValueType[$index] = $operandValueType;
  75      }
  76  
  77      /**
  78       * @param mixed $value
  79       *
  80       * @return float|int|string
  81       */
  82      protected function wrapValue($value, string $operandValueType)
  83      {
  84          if (!is_numeric($value) && !is_bool($value) && null !== $value) {
  85              if ($operandValueType === Wizard::VALUE_TYPE_LITERAL) {
  86                  return '"' . str_replace('"', '""', $value) . '"';
  87              }
  88  
  89              return $this->cellConditionCheck($value);
  90          }
  91  
  92          if (null === $value) {
  93              $value = 'NULL';
  94          } elseif (is_bool($value)) {
  95              $value = $value ? 'TRUE' : 'FALSE';
  96          }
  97  
  98          return $value;
  99      }
 100  
 101      public function getConditional(): Conditional
 102      {
 103          if (!isset(self::RANGE_OPERATORS[$this->operator])) {
 104              unset($this->operand[1], $this->operandValueType[1]);
 105          }
 106          $values = array_map([$this, 'wrapValue'], $this->operand, $this->operandValueType);
 107  
 108          $conditional = new Conditional();
 109          $conditional->setConditionType(Conditional::CONDITION_CELLIS);
 110          $conditional->setOperatorType($this->operator);
 111          $conditional->setConditions($values);
 112          $conditional->setStyle($this->getStyle());
 113          $conditional->setStopIfTrue($this->getStopIfTrue());
 114  
 115          return $conditional;
 116      }
 117  
 118      protected static function unwrapString(string $condition): string
 119      {
 120          if ((strpos($condition, '"') === 0) && (strpos(strrev($condition), '"') === 0)) {
 121              $condition = substr($condition, 1, -1);
 122          }
 123  
 124          return str_replace('""', '"', $condition);
 125      }
 126  
 127      public static function fromConditional(Conditional $conditional, string $cellRange = 'A1'): WizardInterface
 128      {
 129          if ($conditional->getConditionType() !== Conditional::CONDITION_CELLIS) {
 130              throw new Exception('Conditional is not a Cell Value CF Rule conditional');
 131          }
 132  
 133          $wizard = new self($cellRange);
 134          $wizard->style = $conditional->getStyle();
 135          $wizard->stopIfTrue = $conditional->getStopIfTrue();
 136  
 137          $wizard->operator = $conditional->getOperatorType();
 138          $conditions = $conditional->getConditions();
 139          foreach ($conditions as $index => $condition) {
 140              // Best-guess to try and identify if the text is a string literal, a cell reference or a formula?
 141              $operandValueType = Wizard::VALUE_TYPE_LITERAL;
 142              if (is_string($condition)) {
 143                  if (array_key_exists($condition, Calculation::$excelConstants)) {
 144                      $condition = Calculation::$excelConstants[$condition];
 145                  } elseif (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '$/i', $condition)) {
 146                      $operandValueType = Wizard::VALUE_TYPE_CELL;
 147                      $condition = self::reverseAdjustCellRef($condition, $cellRange);
 148                  } elseif (
 149                      preg_match('/\(\)/', $condition) ||
 150                      preg_match('/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i', $condition)
 151                  ) {
 152                      $operandValueType = Wizard::VALUE_TYPE_FORMULA;
 153                      $condition = self::reverseAdjustCellRef($condition, $cellRange);
 154                  } else {
 155                      $condition = self::unwrapString($condition);
 156                  }
 157              }
 158              $wizard->operand($index, $condition, $operandValueType);
 159          }
 160  
 161          return $wizard;
 162      }
 163  
 164      /**
 165       * @param string $methodName
 166       * @param mixed[] $arguments
 167       */
 168      public function __call($methodName, $arguments): self
 169      {
 170          if (!isset(self::MAGIC_OPERATIONS[$methodName]) && $methodName !== 'and') {
 171              throw new Exception('Invalid Operator for Cell Value CF Rule Wizard');
 172          }
 173  
 174          if ($methodName === 'and') {
 175              if (!isset(self::RANGE_OPERATORS[$this->operator])) {
 176                  throw new Exception('AND Value is only appropriate for range operators');
 177              }
 178  
 179              $this->operand(1, ...$arguments);
 180  
 181              return $this;
 182          }
 183  
 184          $this->operator(self::MAGIC_OPERATIONS[$methodName]);
 185          $this->operand(0, ...$arguments);
 186  
 187          return $this;
 188      }
 189  }