[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Worksheet\Table;
   4  
   5  use PhpOffice\PhpSpreadsheet\Cell\DataType;
   6  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   7  use PhpOffice\PhpSpreadsheet\Spreadsheet;
   8  use PhpOffice\PhpSpreadsheet\Worksheet\Table;
   9  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  10  
  11  class Column
  12  {
  13      /**
  14       * Table Column Index.
  15       *
  16       * @var string
  17       */
  18      private $columnIndex = '';
  19  
  20      /**
  21       * Show Filter Button.
  22       *
  23       * @var bool
  24       */
  25      private $showFilterButton = true;
  26  
  27      /**
  28       * Total Row Label.
  29       *
  30       * @var string
  31       */
  32      private $totalsRowLabel;
  33  
  34      /**
  35       * Total Row Function.
  36       *
  37       * @var string
  38       */
  39      private $totalsRowFunction;
  40  
  41      /**
  42       * Total Row Formula.
  43       *
  44       * @var string
  45       */
  46      private $totalsRowFormula;
  47  
  48      /**
  49       * Column Formula.
  50       *
  51       * @var string
  52       */
  53      private $columnFormula;
  54  
  55      /**
  56       * Table.
  57       *
  58       * @var null|Table
  59       */
  60      private $table;
  61  
  62      /**
  63       * Create a new Column.
  64       *
  65       * @param string $column Column (e.g. A)
  66       * @param Table $table Table for this column
  67       */
  68      public function __construct($column, ?Table $table = null)
  69      {
  70          $this->columnIndex = $column;
  71          $this->table = $table;
  72      }
  73  
  74      /**
  75       * Get Table column index as string eg: 'A'.
  76       */
  77      public function getColumnIndex(): string
  78      {
  79          return $this->columnIndex;
  80      }
  81  
  82      /**
  83       * Set Table column index as string eg: 'A'.
  84       *
  85       * @param string $column Column (e.g. A)
  86       */
  87      public function setColumnIndex($column): self
  88      {
  89          // Uppercase coordinate
  90          $column = strtoupper($column);
  91          if ($this->table !== null) {
  92              $this->table->isColumnInRange($column);
  93          }
  94  
  95          $this->columnIndex = $column;
  96  
  97          return $this;
  98      }
  99  
 100      /**
 101       * Get show Filter Button.
 102       */
 103      public function getShowFilterButton(): bool
 104      {
 105          return $this->showFilterButton;
 106      }
 107  
 108      /**
 109       * Set show Filter Button.
 110       */
 111      public function setShowFilterButton(bool $showFilterButton): self
 112      {
 113          $this->showFilterButton = $showFilterButton;
 114  
 115          return $this;
 116      }
 117  
 118      /**
 119       * Get total Row Label.
 120       */
 121      public function getTotalsRowLabel(): ?string
 122      {
 123          return $this->totalsRowLabel;
 124      }
 125  
 126      /**
 127       * Set total Row Label.
 128       */
 129      public function setTotalsRowLabel(string $totalsRowLabel): self
 130      {
 131          $this->totalsRowLabel = $totalsRowLabel;
 132  
 133          return $this;
 134      }
 135  
 136      /**
 137       * Get total Row Function.
 138       */
 139      public function getTotalsRowFunction(): ?string
 140      {
 141          return $this->totalsRowFunction;
 142      }
 143  
 144      /**
 145       * Set total Row Function.
 146       */
 147      public function setTotalsRowFunction(string $totalsRowFunction): self
 148      {
 149          $this->totalsRowFunction = $totalsRowFunction;
 150  
 151          return $this;
 152      }
 153  
 154      /**
 155       * Get total Row Formula.
 156       */
 157      public function getTotalsRowFormula(): ?string
 158      {
 159          return $this->totalsRowFormula;
 160      }
 161  
 162      /**
 163       * Set total Row Formula.
 164       */
 165      public function setTotalsRowFormula(string $totalsRowFormula): self
 166      {
 167          $this->totalsRowFormula = $totalsRowFormula;
 168  
 169          return $this;
 170      }
 171  
 172      /**
 173       * Get column Formula.
 174       */
 175      public function getColumnFormula(): ?string
 176      {
 177          return $this->columnFormula;
 178      }
 179  
 180      /**
 181       * Set column Formula.
 182       */
 183      public function setColumnFormula(string $columnFormula): self
 184      {
 185          $this->columnFormula = $columnFormula;
 186  
 187          return $this;
 188      }
 189  
 190      /**
 191       * Get this Column's Table.
 192       */
 193      public function getTable(): ?Table
 194      {
 195          return $this->table;
 196      }
 197  
 198      /**
 199       * Set this Column's Table.
 200       */
 201      public function setTable(?Table $table = null): self
 202      {
 203          $this->table = $table;
 204  
 205          return $this;
 206      }
 207  
 208      public static function updateStructuredReferences(?Worksheet $workSheet, ?string $oldTitle, ?string $newTitle): void
 209      {
 210          if ($workSheet === null || $oldTitle === null || $oldTitle === '' || $newTitle === null) {
 211              return;
 212          }
 213  
 214          // Remember that table headings are case-insensitive
 215          if (StringHelper::strToLower($oldTitle) !== StringHelper::strToLower($newTitle)) {
 216              // We need to check all formula cells that might contain Structured References that refer
 217              //    to this column, and update those formulae to reference the new column text
 218              $spreadsheet = $workSheet->getParentOrThrow();
 219              foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
 220                  self::updateStructuredReferencesInCells($sheet, $oldTitle, $newTitle);
 221              }
 222              self::updateStructuredReferencesInNamedFormulae($spreadsheet, $oldTitle, $newTitle);
 223          }
 224      }
 225  
 226      private static function updateStructuredReferencesInCells(Worksheet $worksheet, string $oldTitle, string $newTitle): void
 227      {
 228          $pattern = '/\[(@?)' . preg_quote($oldTitle, '/') . '\]/mui';
 229  
 230          foreach ($worksheet->getCoordinates(false) as $coordinate) {
 231              $cell = $worksheet->getCell($coordinate);
 232              if ($cell->getDataType() === DataType::TYPE_FORMULA) {
 233                  $formula = $cell->getValue();
 234                  if (preg_match($pattern, $formula) === 1) {
 235                      $formula = preg_replace($pattern, "[$1{$newTitle}]", $formula);
 236                      $cell->setValueExplicit($formula, DataType::TYPE_FORMULA);
 237                  }
 238              }
 239          }
 240      }
 241  
 242      private static function updateStructuredReferencesInNamedFormulae(Spreadsheet $spreadsheet, string $oldTitle, string $newTitle): void
 243      {
 244          $pattern = '/\[(@?)' . preg_quote($oldTitle, '/') . '\]/mui';
 245  
 246          foreach ($spreadsheet->getNamedFormulae() as $namedFormula) {
 247              $formula = $namedFormula->getValue();
 248              if (preg_match($pattern, $formula) === 1) {
 249                  $formula = preg_replace($pattern, "[$1{$newTitle}]", $formula);
 250                  $namedFormula->setValue($formula); // @phpstan-ignore-line
 251              }
 252          }
 253      }
 254  }