[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Worksheet;
   4  
   5  use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Cell\DataType;
   8  use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
   9  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  10  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  11  use PhpOffice\PhpSpreadsheet\Worksheet\Table\TableStyle;
  12  
  13  class Table
  14  {
  15      /**
  16       * Table Name.
  17       *
  18       * @var string
  19       */
  20      private $name;
  21  
  22      /**
  23       * Show Header Row.
  24       *
  25       * @var bool
  26       */
  27      private $showHeaderRow = true;
  28  
  29      /**
  30       * Show Totals Row.
  31       *
  32       * @var bool
  33       */
  34      private $showTotalsRow = false;
  35  
  36      /**
  37       * Table Range.
  38       *
  39       * @var string
  40       */
  41      private $range = '';
  42  
  43      /**
  44       * Table Worksheet.
  45       *
  46       * @var null|Worksheet
  47       */
  48      private $workSheet;
  49  
  50      /**
  51       * Table allow filter.
  52       *
  53       * @var bool
  54       */
  55      private $allowFilter = true;
  56  
  57      /**
  58       * Table Column.
  59       *
  60       * @var Table\Column[]
  61       */
  62      private $columns = [];
  63  
  64      /**
  65       * Table Style.
  66       *
  67       * @var TableStyle
  68       */
  69      private $style;
  70  
  71      /**
  72       * Table AutoFilter.
  73       *
  74       * @var AutoFilter
  75       */
  76      private $autoFilter;
  77  
  78      /**
  79       * Create a new Table.
  80       *
  81       * @param AddressRange|array<int>|string $range
  82       *            A simple string containing a Cell range like 'A1:E10' is permitted
  83       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
  84       *              or an AddressRange object.
  85       * @param string $name (e.g. Table1)
  86       */
  87      public function __construct($range = '', string $name = '')
  88      {
  89          $this->style = new TableStyle();
  90          $this->autoFilter = new AutoFilter($range);
  91          $this->setRange($range);
  92          $this->setName($name);
  93      }
  94  
  95      /**
  96       * Get Table name.
  97       */
  98      public function getName(): string
  99      {
 100          return $this->name;
 101      }
 102  
 103      /**
 104       * Set Table name.
 105       *
 106       * @throws PhpSpreadsheetException
 107       */
 108      public function setName(string $name): self
 109      {
 110          $name = trim($name);
 111  
 112          if (!empty($name)) {
 113              if (strlen($name) === 1 && in_array($name, ['C', 'c', 'R', 'r'])) {
 114                  throw new PhpSpreadsheetException('The table name is invalid');
 115              }
 116              if (StringHelper::countCharacters($name) > 255) {
 117                  throw new PhpSpreadsheetException('The table name cannot be longer than 255 characters');
 118              }
 119              // Check for A1 or R1C1 cell reference notation
 120              if (
 121                  preg_match(Coordinate::A1_COORDINATE_REGEX, $name) ||
 122                  preg_match('/^R\[?\-?[0-9]*\]?C\[?\-?[0-9]*\]?$/i', $name)
 123              ) {
 124                  throw new PhpSpreadsheetException('The table name can\'t be the same as a cell reference');
 125              }
 126              if (!preg_match('/^[\p{L}_\\\\]/iu', $name)) {
 127                  throw new PhpSpreadsheetException('The table name must begin a name with a letter, an underscore character (_), or a backslash (\)');
 128              }
 129              if (!preg_match('/^[\p{L}_\\\\][\p{L}\p{M}0-9\._]+$/iu', $name)) {
 130                  throw new PhpSpreadsheetException('The table name contains invalid characters');
 131              }
 132  
 133              $this->checkForDuplicateTableNames($name, $this->workSheet);
 134              $this->updateStructuredReferences($name);
 135          }
 136  
 137          $this->name = $name;
 138  
 139          return $this;
 140      }
 141  
 142      /**
 143       * @throws PhpSpreadsheetException
 144       */
 145      private function checkForDuplicateTableNames(string $name, ?Worksheet $worksheet): void
 146      {
 147          // Remember that table names are case-insensitive
 148          $tableName = StringHelper::strToLower($name);
 149  
 150          if ($worksheet !== null && StringHelper::strToLower($this->name) !== $name) {
 151              $spreadsheet = $worksheet->getParentOrThrow();
 152  
 153              foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
 154                  foreach ($sheet->getTableCollection() as $table) {
 155                      if (StringHelper::strToLower($table->getName()) === $tableName && $table != $this) {
 156                          throw new PhpSpreadsheetException("Spreadsheet already contains a table named '{$this->name}'");
 157                      }
 158                  }
 159              }
 160          }
 161      }
 162  
 163      private function updateStructuredReferences(string $name): void
 164      {
 165          if ($this->workSheet === null || $this->name === null || $this->name === '') {
 166              return;
 167          }
 168  
 169          // Remember that table names are case-insensitive
 170          if (StringHelper::strToLower($this->name) !== StringHelper::strToLower($name)) {
 171              // We need to check all formula cells that might contain fully-qualified Structured References
 172              //    that refer to this table, and update those formulae to reference the new table name
 173              $spreadsheet = $this->workSheet->getParentOrThrow();
 174              foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
 175                  $this->updateStructuredReferencesInCells($sheet, $name);
 176              }
 177              $this->updateStructuredReferencesInNamedFormulae($spreadsheet, $name);
 178          }
 179      }
 180  
 181      private function updateStructuredReferencesInCells(Worksheet $worksheet, string $newName): void
 182      {
 183          $pattern = '/' . preg_quote($this->name) . '\[/mui';
 184  
 185          foreach ($worksheet->getCoordinates(false) as $coordinate) {
 186              $cell = $worksheet->getCell($coordinate);
 187              if ($cell->getDataType() === DataType::TYPE_FORMULA) {
 188                  $formula = $cell->getValue();
 189                  if (preg_match($pattern, $formula) === 1) {
 190                      $formula = preg_replace($pattern, "{$newName}[", $formula);
 191                      $cell->setValueExplicit($formula, DataType::TYPE_FORMULA);
 192                  }
 193              }
 194          }
 195      }
 196  
 197      private function updateStructuredReferencesInNamedFormulae(Spreadsheet $spreadsheet, string $newName): void
 198      {
 199          $pattern = '/' . preg_quote($this->name) . '\[/mui';
 200  
 201          foreach ($spreadsheet->getNamedFormulae() as $namedFormula) {
 202              $formula = $namedFormula->getValue();
 203              if (preg_match($pattern, $formula) === 1) {
 204                  $formula = preg_replace($pattern, "{$newName}[", $formula);
 205                  $namedFormula->setValue($formula); // @phpstan-ignore-line
 206              }
 207          }
 208      }
 209  
 210      /**
 211       * Get show Header Row.
 212       */
 213      public function getShowHeaderRow(): bool
 214      {
 215          return $this->showHeaderRow;
 216      }
 217  
 218      /**
 219       * Set show Header Row.
 220       */
 221      public function setShowHeaderRow(bool $showHeaderRow): self
 222      {
 223          $this->showHeaderRow = $showHeaderRow;
 224  
 225          return $this;
 226      }
 227  
 228      /**
 229       * Get show Totals Row.
 230       */
 231      public function getShowTotalsRow(): bool
 232      {
 233          return $this->showTotalsRow;
 234      }
 235  
 236      /**
 237       * Set show Totals Row.
 238       */
 239      public function setShowTotalsRow(bool $showTotalsRow): self
 240      {
 241          $this->showTotalsRow = $showTotalsRow;
 242  
 243          return $this;
 244      }
 245  
 246      /**
 247       * Get allow filter.
 248       * If false, autofiltering is disabled for the table, if true it is enabled.
 249       */
 250      public function getAllowFilter(): bool
 251      {
 252          return $this->allowFilter;
 253      }
 254  
 255      /**
 256       * Set show Autofiltering.
 257       * Disabling autofiltering has the same effect as hiding the filter button on all the columns in the table.
 258       */
 259      public function setAllowFilter(bool $allowFilter): self
 260      {
 261          $this->allowFilter = $allowFilter;
 262  
 263          return $this;
 264      }
 265  
 266      /**
 267       * Get Table Range.
 268       */
 269      public function getRange(): string
 270      {
 271          return $this->range;
 272      }
 273  
 274      /**
 275       * Set Table Cell Range.
 276       *
 277       * @param AddressRange|array<int>|string $range
 278       *            A simple string containing a Cell range like 'A1:E10' is permitted
 279       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
 280       *              or an AddressRange object.
 281       */
 282      public function setRange($range = ''): self
 283      {
 284          // extract coordinate
 285          if ($range !== '') {
 286              [, $range] = Worksheet::extractSheetTitle(Validations::validateCellRange($range), true);
 287          }
 288          if (empty($range)) {
 289              //    Discard all column rules
 290              $this->columns = [];
 291              $this->range = '';
 292  
 293              return $this;
 294          }
 295  
 296          if (strpos($range, ':') === false) {
 297              throw new PhpSpreadsheetException('Table must be set on a range of cells.');
 298          }
 299  
 300          [$width, $height] = Coordinate::rangeDimension($range);
 301          if ($width < 1 || $height < 1) {
 302              throw new PhpSpreadsheetException('The table range must be at least 1 column and row');
 303          }
 304  
 305          $this->range = $range;
 306          $this->autoFilter->setRange($range);
 307  
 308          //    Discard any column rules that are no longer valid within this range
 309          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
 310          foreach ($this->columns as $key => $value) {
 311              $colIndex = Coordinate::columnIndexFromString($key);
 312              if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) {
 313                  unset($this->columns[$key]);
 314              }
 315          }
 316  
 317          return $this;
 318      }
 319  
 320      /**
 321       * Set Table Cell Range to max row.
 322       */
 323      public function setRangeToMaxRow(): self
 324      {
 325          if ($this->workSheet !== null) {
 326              $thisrange = $this->range;
 327              $range = (string) preg_replace('/\\d+$/', (string) $this->workSheet->getHighestRow(), $thisrange);
 328              if ($range !== $thisrange) {
 329                  $this->setRange($range);
 330              }
 331          }
 332  
 333          return $this;
 334      }
 335  
 336      /**
 337       * Get Table's Worksheet.
 338       */
 339      public function getWorksheet(): ?Worksheet
 340      {
 341          return $this->workSheet;
 342      }
 343  
 344      /**
 345       * Set Table's Worksheet.
 346       */
 347      public function setWorksheet(?Worksheet $worksheet = null): self
 348      {
 349          if ($this->name !== '' && $worksheet !== null) {
 350              $spreadsheet = $worksheet->getParentOrThrow();
 351              $tableName = StringHelper::strToUpper($this->name);
 352  
 353              foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
 354                  foreach ($sheet->getTableCollection() as $table) {
 355                      if (StringHelper::strToUpper($table->getName()) === $tableName) {
 356                          throw new PhpSpreadsheetException("Workbook already contains a table named '{$this->name}'");
 357                      }
 358                  }
 359              }
 360          }
 361  
 362          $this->workSheet = $worksheet;
 363          $this->autoFilter->setParent($worksheet);
 364  
 365          return $this;
 366      }
 367  
 368      /**
 369       * Get all Table Columns.
 370       *
 371       * @return Table\Column[]
 372       */
 373      public function getColumns(): array
 374      {
 375          return $this->columns;
 376      }
 377  
 378      /**
 379       * Validate that the specified column is in the Table range.
 380       *
 381       * @param string $column Column name (e.g. A)
 382       *
 383       * @return int The column offset within the table range
 384       */
 385      public function isColumnInRange(string $column): int
 386      {
 387          if (empty($this->range)) {
 388              throw new PhpSpreadsheetException('No table range is defined.');
 389          }
 390  
 391          $columnIndex = Coordinate::columnIndexFromString($column);
 392          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
 393          if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) {
 394              throw new PhpSpreadsheetException('Column is outside of current table range.');
 395          }
 396  
 397          return $columnIndex - $rangeStart[0];
 398      }
 399  
 400      /**
 401       * Get a specified Table Column Offset within the defined Table range.
 402       *
 403       * @param string $column Column name (e.g. A)
 404       *
 405       * @return int The offset of the specified column within the table range
 406       */
 407      public function getColumnOffset($column): int
 408      {
 409          return $this->isColumnInRange($column);
 410      }
 411  
 412      /**
 413       * Get a specified Table Column.
 414       *
 415       * @param string $column Column name (e.g. A)
 416       */
 417      public function getColumn($column): Table\Column
 418      {
 419          $this->isColumnInRange($column);
 420  
 421          if (!isset($this->columns[$column])) {
 422              $this->columns[$column] = new Table\Column($column, $this);
 423          }
 424  
 425          return $this->columns[$column];
 426      }
 427  
 428      /**
 429       * Get a specified Table Column by it's offset.
 430       *
 431       * @param int $columnOffset Column offset within range (starting from 0)
 432       */
 433      public function getColumnByOffset($columnOffset): Table\Column
 434      {
 435          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($this->range);
 436          $pColumn = Coordinate::stringFromColumnIndex($rangeStart[0] + $columnOffset);
 437  
 438          return $this->getColumn($pColumn);
 439      }
 440  
 441      /**
 442       * Set Table.
 443       *
 444       * @param string|Table\Column $columnObjectOrString
 445       *            A simple string containing a Column ID like 'A' is permitted
 446       */
 447      public function setColumn($columnObjectOrString): self
 448      {
 449          if ((is_string($columnObjectOrString)) && (!empty($columnObjectOrString))) {
 450              $column = $columnObjectOrString;
 451          } elseif (is_object($columnObjectOrString) && ($columnObjectOrString instanceof Table\Column)) {
 452              $column = $columnObjectOrString->getColumnIndex();
 453          } else {
 454              throw new PhpSpreadsheetException('Column is not within the table range.');
 455          }
 456          $this->isColumnInRange($column);
 457  
 458          if (is_string($columnObjectOrString)) {
 459              $this->columns[$columnObjectOrString] = new Table\Column($columnObjectOrString, $this);
 460          } else {
 461              $columnObjectOrString->setTable($this);
 462              $this->columns[$column] = $columnObjectOrString;
 463          }
 464          ksort($this->columns);
 465  
 466          return $this;
 467      }
 468  
 469      /**
 470       * Clear a specified Table Column.
 471       *
 472       * @param string $column Column name (e.g. A)
 473       */
 474      public function clearColumn($column): self
 475      {
 476          $this->isColumnInRange($column);
 477  
 478          if (isset($this->columns[$column])) {
 479              unset($this->columns[$column]);
 480          }
 481  
 482          return $this;
 483      }
 484  
 485      /**
 486       * Shift an Table Column Rule to a different column.
 487       *
 488       * Note: This method bypasses validation of the destination column to ensure it is within this Table range.
 489       *        Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value.
 490       *        Use with caution.
 491       *
 492       * @param string $fromColumn Column name (e.g. A)
 493       * @param string $toColumn Column name (e.g. B)
 494       */
 495      public function shiftColumn($fromColumn, $toColumn): self
 496      {
 497          $fromColumn = strtoupper($fromColumn);
 498          $toColumn = strtoupper($toColumn);
 499  
 500          if (($fromColumn !== null) && (isset($this->columns[$fromColumn])) && ($toColumn !== null)) {
 501              $this->columns[$fromColumn]->setTable();
 502              $this->columns[$fromColumn]->setColumnIndex($toColumn);
 503              $this->columns[$toColumn] = $this->columns[$fromColumn];
 504              $this->columns[$toColumn]->setTable($this);
 505              unset($this->columns[$fromColumn]);
 506  
 507              ksort($this->columns);
 508          }
 509  
 510          return $this;
 511      }
 512  
 513      /**
 514       * Get table Style.
 515       */
 516      public function getStyle(): Table\TableStyle
 517      {
 518          return $this->style;
 519      }
 520  
 521      /**
 522       * Set table Style.
 523       */
 524      public function setStyle(TableStyle $style): self
 525      {
 526          $this->style = $style;
 527  
 528          return $this;
 529      }
 530  
 531      /**
 532       * Get AutoFilter.
 533       */
 534      public function getAutoFilter(): AutoFilter
 535      {
 536          return $this->autoFilter;
 537      }
 538  
 539      /**
 540       * Set AutoFilter.
 541       */
 542      public function setAutoFilter(AutoFilter $autoFilter): self
 543      {
 544          $this->autoFilter = $autoFilter;
 545  
 546          return $this;
 547      }
 548  
 549      /**
 550       * Implement PHP __clone to create a deep clone, not just a shallow copy.
 551       */
 552      public function __clone()
 553      {
 554          $vars = get_object_vars($this);
 555          foreach ($vars as $key => $value) {
 556              if (is_object($value)) {
 557                  if ($key === 'workSheet') {
 558                      //    Detach from worksheet
 559                      $this->{$key} = null;
 560                  } else {
 561                      $this->{$key} = clone $value;
 562                  }
 563              } elseif ((is_array($value)) && ($key === 'columns')) {
 564                  //    The columns array of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet\Table objects
 565                  $this->{$key} = [];
 566                  foreach ($value as $k => $v) {
 567                      $this->{$key}[$k] = clone $v;
 568                      // attach the new cloned Column to this new cloned Table object
 569                      $this->{$key}[$k]->setTable($this);
 570                  }
 571              } else {
 572                  $this->{$key} = $value;
 573              }
 574          }
 575      }
 576  
 577      /**
 578       * toString method replicates previous behavior by returning the range if object is
 579       * referenced as a property of its worksheet.
 580       */
 581      public function __toString()
 582      {
 583          return (string) $this->range;
 584      }
 585  }