[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   8  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   9  use PhpOffice\PhpSpreadsheet\Style\Conditional;
  10  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  11  
  12  class CellMatcher
  13  {
  14      public const COMPARISON_OPERATORS = [
  15          Conditional::OPERATOR_EQUAL => '=',
  16          Conditional::OPERATOR_GREATERTHAN => '>',
  17          Conditional::OPERATOR_GREATERTHANOREQUAL => '>=',
  18          Conditional::OPERATOR_LESSTHAN => '<',
  19          Conditional::OPERATOR_LESSTHANOREQUAL => '<=',
  20          Conditional::OPERATOR_NOTEQUAL => '<>',
  21      ];
  22  
  23      public const COMPARISON_RANGE_OPERATORS = [
  24          Conditional::OPERATOR_BETWEEN => 'IF(AND(A1>=%s,A1<=%s),TRUE,FALSE)',
  25          Conditional::OPERATOR_NOTBETWEEN => 'IF(AND(A1>=%s,A1<=%s),FALSE,TRUE)',
  26      ];
  27  
  28      public const COMPARISON_DUPLICATES_OPERATORS = [
  29          Conditional::CONDITION_DUPLICATES => "COUNTIF('%s'!%s,%s)>1",
  30          Conditional::CONDITION_UNIQUE => "COUNTIF('%s'!%s,%s)=1",
  31      ];
  32  
  33      /**
  34       * @var Cell
  35       */
  36      protected $cell;
  37  
  38      /**
  39       * @var int
  40       */
  41      protected $cellRow;
  42  
  43      /**
  44       * @var Worksheet
  45       */
  46      protected $worksheet;
  47  
  48      /**
  49       * @var int
  50       */
  51      protected $cellColumn;
  52  
  53      /**
  54       * @var string
  55       */
  56      protected $conditionalRange;
  57  
  58      /**
  59       * @var string
  60       */
  61      protected $referenceCell;
  62  
  63      /**
  64       * @var int
  65       */
  66      protected $referenceRow;
  67  
  68      /**
  69       * @var int
  70       */
  71      protected $referenceColumn;
  72  
  73      /**
  74       * @var Calculation
  75       */
  76      protected $engine;
  77  
  78      public function __construct(Cell $cell, string $conditionalRange)
  79      {
  80          $this->cell = $cell;
  81          $this->worksheet = $cell->getWorksheet();
  82          [$this->cellColumn, $this->cellRow] = Coordinate::indexesFromString($this->cell->getCoordinate());
  83          $this->setReferenceCellForExpressions($conditionalRange);
  84  
  85          $this->engine = Calculation::getInstance($this->worksheet->getParent());
  86      }
  87  
  88      protected function setReferenceCellForExpressions(string $conditionalRange): void
  89      {
  90          $conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($conditionalRange)));
  91          [$this->referenceCell] = $conditionalRange[0];
  92  
  93          [$this->referenceColumn, $this->referenceRow] = Coordinate::indexesFromString($this->referenceCell);
  94  
  95          // Convert our conditional range to an absolute conditional range, so it can be used  "pinned" in formulae
  96          $rangeSets = [];
  97          foreach ($conditionalRange as $rangeSet) {
  98              $absoluteRangeSet = array_map(
  99                  [Coordinate::class, 'absoluteCoordinate'],
 100                  $rangeSet
 101              );
 102              $rangeSets[] = implode(':', $absoluteRangeSet);
 103          }
 104          $this->conditionalRange = implode(',', $rangeSets);
 105      }
 106  
 107      public function evaluateConditional(Conditional $conditional): bool
 108      {
 109          // Some calculations may modify the stored cell; so reset it before every evaluation.
 110          $cellColumn = Coordinate::stringFromColumnIndex($this->cellColumn);
 111          $cellAddress = "{$cellColumn}{$this->cellRow}";
 112          $this->cell = $this->worksheet->getCell($cellAddress);
 113  
 114          switch ($conditional->getConditionType()) {
 115              case Conditional::CONDITION_CELLIS:
 116                  return $this->processOperatorComparison($conditional);
 117              case Conditional::CONDITION_DUPLICATES:
 118              case Conditional::CONDITION_UNIQUE:
 119                  return $this->processDuplicatesComparison($conditional);
 120              case Conditional::CONDITION_CONTAINSTEXT:
 121                  // Expression is NOT(ISERROR(SEARCH("<TEXT>",<Cell Reference>)))
 122              case Conditional::CONDITION_NOTCONTAINSTEXT:
 123                  // Expression is ISERROR(SEARCH("<TEXT>",<Cell Reference>))
 124              case Conditional::CONDITION_BEGINSWITH:
 125                  // Expression is LEFT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
 126              case Conditional::CONDITION_ENDSWITH:
 127                  // Expression is RIGHT(<Cell Reference>,LEN("<TEXT>"))="<TEXT>"
 128              case Conditional::CONDITION_CONTAINSBLANKS:
 129                  // Expression is LEN(TRIM(<Cell Reference>))=0
 130              case Conditional::CONDITION_NOTCONTAINSBLANKS:
 131                  // Expression is LEN(TRIM(<Cell Reference>))>0
 132              case Conditional::CONDITION_CONTAINSERRORS:
 133                  // Expression is ISERROR(<Cell Reference>)
 134              case Conditional::CONDITION_NOTCONTAINSERRORS:
 135                  // Expression is NOT(ISERROR(<Cell Reference>))
 136              case Conditional::CONDITION_TIMEPERIOD:
 137                  // Expression varies, depending on specified timePeriod value, e.g.
 138                  // Yesterday FLOOR(<Cell Reference>,1)=TODAY()-1
 139                  // Today FLOOR(<Cell Reference>,1)=TODAY()
 140                  // Tomorrow FLOOR(<Cell Reference>,1)=TODAY()+1
 141                  // Last 7 Days AND(TODAY()-FLOOR(<Cell Reference>,1)<=6,FLOOR(<Cell Reference>,1)<=TODAY())
 142              case Conditional::CONDITION_EXPRESSION:
 143                  return $this->processExpression($conditional);
 144          }
 145  
 146          return false;
 147      }
 148  
 149      /**
 150       * @param mixed $value
 151       *
 152       * @return float|int|string
 153       */
 154      protected function wrapValue($value)
 155      {
 156          if (!is_numeric($value)) {
 157              if (is_bool($value)) {
 158                  return $value ? 'TRUE' : 'FALSE';
 159              } elseif ($value === null) {
 160                  return 'NULL';
 161              }
 162  
 163              return '"' . $value . '"';
 164          }
 165  
 166          return $value;
 167      }
 168  
 169      /**
 170       * @return float|int|string
 171       */
 172      protected function wrapCellValue()
 173      {
 174          return $this->wrapValue($this->cell->getCalculatedValue());
 175      }
 176  
 177      /**
 178       * @return float|int|string
 179       */
 180      protected function conditionCellAdjustment(array $matches)
 181      {
 182          $column = $matches[6];
 183          $row = $matches[7];
 184  
 185          if (strpos($column, '$') === false) {
 186              $column = Coordinate::columnIndexFromString($column);
 187              $column += $this->cellColumn - $this->referenceColumn;
 188              $column = Coordinate::stringFromColumnIndex($column);
 189          }
 190  
 191          if (strpos($row, '$') === false) {
 192              $row += $this->cellRow - $this->referenceRow;
 193          }
 194  
 195          if (!empty($matches[4])) {
 196              $worksheet = $this->worksheet->getParent()->getSheetByName(trim($matches[4], "'"));
 197              if ($worksheet === null) {
 198                  return $this->wrapValue(null);
 199              }
 200  
 201              return $this->wrapValue(
 202                  $worksheet
 203                      ->getCell(str_replace('$', '', "{$column}{$row}"))
 204                      ->getCalculatedValue()
 205              );
 206          }
 207  
 208          return $this->wrapValue(
 209              $this->worksheet
 210                  ->getCell(str_replace('$', '', "{$column}{$row}"))
 211                  ->getCalculatedValue()
 212          );
 213      }
 214  
 215      protected function cellConditionCheck(string $condition): string
 216      {
 217          $splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition);
 218          $i = false;
 219          foreach ($splitCondition as &$value) {
 220              //    Only count/replace in alternating array entries (ie. not in quoted strings)
 221              if ($i = !$i) {
 222                  $value = (string) preg_replace_callback(
 223                      '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
 224                      [$this, 'conditionCellAdjustment'],
 225                      $value
 226                  );
 227              }
 228          }
 229          unset($value);
 230          //    Then rebuild the condition string to return it
 231          return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
 232      }
 233  
 234      protected function adjustConditionsForCellReferences(array $conditions): array
 235      {
 236          return array_map(
 237              [$this, 'cellConditionCheck'],
 238              $conditions
 239          );
 240      }
 241  
 242      protected function processOperatorComparison(Conditional $conditional): bool
 243      {
 244          if (array_key_exists($conditional->getOperatorType(), self::COMPARISON_RANGE_OPERATORS)) {
 245              return $this->processRangeOperator($conditional);
 246          }
 247  
 248          $operator = self::COMPARISON_OPERATORS[$conditional->getOperatorType()];
 249          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 250          $expression = sprintf('%s%s%s', (string) $this->wrapCellValue(), $operator, (string) array_pop($conditions));
 251  
 252          return $this->evaluateExpression($expression);
 253      }
 254  
 255      protected function processRangeOperator(Conditional $conditional): bool
 256      {
 257          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 258          sort($conditions);
 259          $expression = sprintf(
 260              (string) preg_replace(
 261                  '/\bA1\b/i',
 262                  (string) $this->wrapCellValue(),
 263                  self::COMPARISON_RANGE_OPERATORS[$conditional->getOperatorType()]
 264              ),
 265              ...$conditions
 266          );
 267  
 268          return $this->evaluateExpression($expression);
 269      }
 270  
 271      protected function processDuplicatesComparison(Conditional $conditional): bool
 272      {
 273          $worksheetName = $this->cell->getWorksheet()->getTitle();
 274  
 275          $expression = sprintf(
 276              self::COMPARISON_DUPLICATES_OPERATORS[$conditional->getConditionType()],
 277              $worksheetName,
 278              $this->conditionalRange,
 279              $this->cellConditionCheck($this->cell->getCalculatedValue())
 280          );
 281  
 282          return $this->evaluateExpression($expression);
 283      }
 284  
 285      protected function processExpression(Conditional $conditional): bool
 286      {
 287          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 288          $expression = array_pop($conditions);
 289  
 290          $expression = (string) preg_replace(
 291              '/\b' . $this->referenceCell . '\b/i',
 292              (string) $this->wrapCellValue(),
 293              $expression
 294          );
 295  
 296          return $this->evaluateExpression($expression);
 297      }
 298  
 299      protected function evaluateExpression(string $expression): bool
 300      {
 301          $expression = "={$expression}";
 302  
 303          try {
 304              $this->engine->flushInstance();
 305              $result = (bool) $this->engine->calculateFormula($expression);
 306          } catch (Exception $e) {
 307              return false;
 308          }
 309  
 310          return $result;
 311      }
 312  }