[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\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard;
   8  use PhpOffice\PhpSpreadsheet\Style\Style;
   9  
  10  abstract class WizardAbstract
  11  {
  12      /**
  13       * @var ?Style
  14       */
  15      protected $style;
  16  
  17      /**
  18       * @var string
  19       */
  20      protected $expression;
  21  
  22      /**
  23       * @var string
  24       */
  25      protected $cellRange;
  26  
  27      /**
  28       * @var string
  29       */
  30      protected $referenceCell;
  31  
  32      /**
  33       * @var int
  34       */
  35      protected $referenceRow;
  36  
  37      /**
  38       * @var bool
  39       */
  40      protected $stopIfTrue = false;
  41  
  42      /**
  43       * @var int
  44       */
  45      protected $referenceColumn;
  46  
  47      public function __construct(string $cellRange)
  48      {
  49          $this->setCellRange($cellRange);
  50      }
  51  
  52      public function getCellRange(): string
  53      {
  54          return $this->cellRange;
  55      }
  56  
  57      public function setCellRange(string $cellRange): void
  58      {
  59          $this->cellRange = $cellRange;
  60          $this->setReferenceCellForExpressions($cellRange);
  61      }
  62  
  63      protected function setReferenceCellForExpressions(string $conditionalRange): void
  64      {
  65          $conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($conditionalRange)));
  66          [$this->referenceCell] = $conditionalRange[0];
  67  
  68          [$this->referenceColumn, $this->referenceRow] = Coordinate::indexesFromString($this->referenceCell);
  69      }
  70  
  71      public function getStopIfTrue(): bool
  72      {
  73          return $this->stopIfTrue;
  74      }
  75  
  76      public function setStopIfTrue(bool $stopIfTrue): void
  77      {
  78          $this->stopIfTrue = $stopIfTrue;
  79      }
  80  
  81      public function getStyle(): Style
  82      {
  83          return $this->style ?? new Style(false, true);
  84      }
  85  
  86      public function setStyle(Style $style): void
  87      {
  88          $this->style = $style;
  89      }
  90  
  91      protected function validateOperand(string $operand, string $operandValueType = Wizard::VALUE_TYPE_LITERAL): string
  92      {
  93          if (
  94              $operandValueType === Wizard::VALUE_TYPE_LITERAL &&
  95              substr($operand, 0, 1) === '"' &&
  96              substr($operand, -1) === '"'
  97          ) {
  98              $operand = str_replace('""', '"', substr($operand, 1, -1));
  99          } elseif ($operandValueType === Wizard::VALUE_TYPE_FORMULA && substr($operand, 0, 1) === '=') {
 100              $operand = substr($operand, 1);
 101          }
 102  
 103          return $operand;
 104      }
 105  
 106      protected static function reverseCellAdjustment(array $matches, int $referenceColumn, int $referenceRow): string
 107      {
 108          $worksheet = $matches[1];
 109          $column = $matches[6];
 110          $row = $matches[7];
 111  
 112          if (strpos($column, '$') === false) {
 113              $column = Coordinate::columnIndexFromString($column);
 114              $column -= $referenceColumn - 1;
 115              $column = Coordinate::stringFromColumnIndex($column);
 116          }
 117  
 118          if (strpos($row, '$') === false) {
 119              $row -= $referenceRow - 1;
 120          }
 121  
 122          return "{$worksheet}{$column}{$row}";
 123      }
 124  
 125      public static function reverseAdjustCellRef(string $condition, string $cellRange): string
 126      {
 127          $conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($cellRange)));
 128          [$referenceCell] = $conditionalRange[0];
 129          [$referenceColumnIndex, $referenceRow] = Coordinate::indexesFromString($referenceCell);
 130  
 131          $splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition);
 132          $i = false;
 133          foreach ($splitCondition as &$value) {
 134              //    Only count/replace in alternating array entries (ie. not in quoted strings)
 135              if ($i = !$i) {
 136                  $value = (string) preg_replace_callback(
 137                      '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
 138                      function ($matches) use ($referenceColumnIndex, $referenceRow) {
 139                          return self::reverseCellAdjustment($matches, $referenceColumnIndex, $referenceRow);
 140                      },
 141                      $value
 142                  );
 143              }
 144          }
 145          unset($value);
 146  
 147          //    Then rebuild the condition string to return it
 148          return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
 149      }
 150  
 151      protected function conditionCellAdjustment(array $matches): string
 152      {
 153          $worksheet = $matches[1];
 154          $column = $matches[6];
 155          $row = $matches[7];
 156  
 157          if (strpos($column, '$') === false) {
 158              $column = Coordinate::columnIndexFromString($column);
 159              $column += $this->referenceColumn - 1;
 160              $column = Coordinate::stringFromColumnIndex($column);
 161          }
 162  
 163          if (strpos($row, '$') === false) {
 164              $row += $this->referenceRow - 1;
 165          }
 166  
 167          return "{$worksheet}{$column}{$row}";
 168      }
 169  
 170      protected function cellConditionCheck(string $condition): string
 171      {
 172          $splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition);
 173          $i = false;
 174          foreach ($splitCondition as &$value) {
 175              //    Only count/replace in alternating array entries (ie. not in quoted strings)
 176              if ($i = !$i) {
 177                  $value = (string) preg_replace_callback(
 178                      '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i',
 179                      [$this, 'conditionCellAdjustment'],
 180                      $value
 181                  );
 182              }
 183          }
 184          unset($value);
 185  
 186          //    Then rebuild the condition string to return it
 187          return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition);
 188      }
 189  
 190      protected function adjustConditionsForCellReferences(array $conditions): array
 191      {
 192          return array_map(
 193              [$this, 'cellConditionCheck'],
 194              $conditions
 195          );
 196      }
 197  }