[Summary view]

Differences Between: [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->getParentOrThrow()->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              $i = $i === false;
 222              if ($i) {
 223                  $value = (string) preg_replace_callback(
 224                      '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
 225                      [$this, 'conditionCellAdjustment'],
 226                      $value
 227                  );
 228              }
 229          }
 230          unset($value);
 231          //    Then rebuild the condition string to return it
 232          return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
 233      }
 234  
 235      protected function adjustConditionsForCellReferences(array $conditions): array
 236      {
 237          return array_map(
 238              [$this, 'cellConditionCheck'],
 239              $conditions
 240          );
 241      }
 242  
 243      protected function processOperatorComparison(Conditional $conditional): bool
 244      {
 245          if (array_key_exists($conditional->getOperatorType(), self::COMPARISON_RANGE_OPERATORS)) {
 246              return $this->processRangeOperator($conditional);
 247          }
 248  
 249          $operator = self::COMPARISON_OPERATORS[$conditional->getOperatorType()];
 250          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 251          $expression = sprintf('%s%s%s', (string) $this->wrapCellValue(), $operator, (string) array_pop($conditions));
 252  
 253          return $this->evaluateExpression($expression);
 254      }
 255  
 256      protected function processRangeOperator(Conditional $conditional): bool
 257      {
 258          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 259          sort($conditions);
 260          $expression = sprintf(
 261              (string) preg_replace(
 262                  '/\bA1\b/i',
 263                  (string) $this->wrapCellValue(),
 264                  self::COMPARISON_RANGE_OPERATORS[$conditional->getOperatorType()]
 265              ),
 266              ...$conditions
 267          );
 268  
 269          return $this->evaluateExpression($expression);
 270      }
 271  
 272      protected function processDuplicatesComparison(Conditional $conditional): bool
 273      {
 274          $worksheetName = $this->cell->getWorksheet()->getTitle();
 275  
 276          $expression = sprintf(
 277              self::COMPARISON_DUPLICATES_OPERATORS[$conditional->getConditionType()],
 278              $worksheetName,
 279              $this->conditionalRange,
 280              $this->cellConditionCheck($this->cell->getCalculatedValue())
 281          );
 282  
 283          return $this->evaluateExpression($expression);
 284      }
 285  
 286      protected function processExpression(Conditional $conditional): bool
 287      {
 288          $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions());
 289          $expression = array_pop($conditions);
 290  
 291          $expression = (string) preg_replace(
 292              '/\b' . $this->referenceCell . '\b/i',
 293              (string) $this->wrapCellValue(),
 294              $expression
 295          );
 296  
 297          return $this->evaluateExpression($expression);
 298      }
 299  
 300      protected function evaluateExpression(string $expression): bool
 301      {
 302          $expression = "={$expression}";
 303  
 304          try {
 305              $this->engine->flushInstance();
 306              $result = (bool) $this->engine->calculateFormula($expression);
 307          } catch (Exception $e) {
 308              return false;
 309          }
 310  
 311          return $result;
 312      }
 313  }