Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.3.x will end 7 October 2024 (12 months).
  • Bug fixes for security issues in 4.3.x will end 21 April 2025 (18 months).
  • PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.2.x is supported too.

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Collection;
   4  
   5  use Generator;
   6  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   7  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   8  use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
   9  use PhpOffice\PhpSpreadsheet\Settings;
  10  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  11  use Psr\SimpleCache\CacheInterface;
  12  
  13  class Cells
  14  {
  15      protected const MAX_COLUMN_ID = 16384;
  16  
  17      /**
  18       * @var CacheInterface
  19       */
  20      private $cache;
  21  
  22      /**
  23       * Parent worksheet.
  24       *
  25       * @var null|Worksheet
  26       */
  27      private $parent;
  28  
  29      /**
  30       * The currently active Cell.
  31       *
  32       * @var null|Cell
  33       */
  34      private $currentCell;
  35  
  36      /**
  37       * Coordinate of the currently active Cell.
  38       *
  39       * @var null|string
  40       */
  41      private $currentCoordinate;
  42  
  43      /**
  44       * Flag indicating whether the currently active Cell requires saving.
  45       *
  46       * @var bool
  47       */
  48      private $currentCellIsDirty = false;
  49  
  50      /**
  51       * An index of existing cells. int pointer to the coordinate (0-base-indexed row * 16,384 + 1-base indexed column)
  52       *    indexed by their coordinate.
  53       *
  54       * @var int[]
  55       */
  56      private $index = [];
  57  
  58      /**
  59       * Prefix used to uniquely identify cache data for this worksheet.
  60       *
  61       * @var string
  62       */
  63      private $cachePrefix;
  64  
  65      /**
  66       * Initialise this new cell collection.
  67       *
  68       * @param Worksheet $parent The worksheet for this cell collection
  69       */
  70      public function __construct(Worksheet $parent, CacheInterface $cache)
  71      {
  72          // Set our parent worksheet.
  73          // This is maintained here to facilitate re-attaching it to Cell objects when
  74          // they are woken from a serialized state
  75          $this->parent = $parent;
  76          $this->cache = $cache;
  77          $this->cachePrefix = $this->getUniqueID();
  78      }
  79  
  80      /**
  81       * Return the parent worksheet for this cell collection.
  82       *
  83       * @return null|Worksheet
  84       */
  85      public function getParent()
  86      {
  87          return $this->parent;
  88      }
  89  
  90      /**
  91       * Whether the collection holds a cell for the given coordinate.
  92       *
  93       * @param string $cellCoordinate Coordinate of the cell to check
  94       */
  95      public function has($cellCoordinate): bool
  96      {
  97          return ($cellCoordinate === $this->currentCoordinate) || isset($this->index[$cellCoordinate]);
  98      }
  99  
 100      /**
 101       * Add or update a cell in the collection.
 102       *
 103       * @param Cell $cell Cell to update
 104       */
 105      public function update(Cell $cell): Cell
 106      {
 107          return $this->add($cell->getCoordinate(), $cell);
 108      }
 109  
 110      /**
 111       * Delete a cell in cache identified by coordinate.
 112       *
 113       * @param string $cellCoordinate Coordinate of the cell to delete
 114       */
 115      public function delete($cellCoordinate): void
 116      {
 117          if ($cellCoordinate === $this->currentCoordinate && $this->currentCell !== null) {
 118              $this->currentCell->detach();
 119              $this->currentCoordinate = null;
 120              $this->currentCell = null;
 121              $this->currentCellIsDirty = false;
 122          }
 123  
 124          unset($this->index[$cellCoordinate]);
 125  
 126          // Delete the entry from cache
 127          $this->cache->delete($this->cachePrefix . $cellCoordinate);
 128      }
 129  
 130      /**
 131       * Get a list of all cell coordinates currently held in the collection.
 132       *
 133       * @return string[]
 134       */
 135      public function getCoordinates()
 136      {
 137          return array_keys($this->index);
 138      }
 139  
 140      /**
 141       * Get a sorted list of all cell coordinates currently held in the collection by row and column.
 142       *
 143       * @return string[]
 144       */
 145      public function getSortedCoordinates()
 146      {
 147          asort($this->index);
 148  
 149          return array_keys($this->index);
 150      }
 151  
 152      /**
 153       * Return the cell coordinate of the currently active cell object.
 154       *
 155       * @return null|string
 156       */
 157      public function getCurrentCoordinate()
 158      {
 159          return $this->currentCoordinate;
 160      }
 161  
 162      /**
 163       * Return the column coordinate of the currently active cell object.
 164       */
 165      public function getCurrentColumn(): string
 166      {
 167          $column = 0;
 168          $row = '';
 169          sscanf($this->currentCoordinate ?? '', '%[A-Z]%d', $column, $row);
 170  
 171          return (string) $column;
 172      }
 173  
 174      /**
 175       * Return the row coordinate of the currently active cell object.
 176       */
 177      public function getCurrentRow(): int
 178      {
 179          $column = 0;
 180          $row = '';
 181          sscanf($this->currentCoordinate ?? '', '%[A-Z]%d', $column, $row);
 182  
 183          return (int) $row;
 184      }
 185  
 186      /**
 187       * Get highest worksheet column and highest row that have cell records.
 188       *
 189       * @return array Highest column name and highest row number
 190       */
 191      public function getHighestRowAndColumn()
 192      {
 193          // Lookup highest column and highest row
 194          $maxRow = $maxColumn = 1;
 195          foreach ($this->index as $coordinate) {
 196              $row = (int) floor($coordinate / self::MAX_COLUMN_ID) + 1;
 197              $maxRow = ($maxRow > $row) ? $maxRow : $row;
 198              $column = $coordinate % self::MAX_COLUMN_ID;
 199              $maxColumn = ($maxColumn > $column) ? $maxColumn : $column;
 200          }
 201  
 202          return [
 203              'row' => $maxRow,
 204              'column' => Coordinate::stringFromColumnIndex($maxColumn),
 205          ];
 206      }
 207  
 208      /**
 209       * Get highest worksheet column.
 210       *
 211       * @param null|int|string $row Return the highest column for the specified row,
 212       *                    or the highest column of any row if no row number is passed
 213       *
 214       * @return string Highest column name
 215       */
 216      public function getHighestColumn($row = null)
 217      {
 218          if ($row === null) {
 219              return $this->getHighestRowAndColumn()['column'];
 220          }
 221  
 222          $row = (int) $row;
 223          if ($row <= 0) {
 224              throw new PhpSpreadsheetException('Row number must be a positive integer');
 225          }
 226  
 227          $maxColumn = 1;
 228          $toRow = $row * self::MAX_COLUMN_ID;
 229          $fromRow = --$row * self::MAX_COLUMN_ID;
 230          foreach ($this->index as $coordinate) {
 231              if ($coordinate < $fromRow || $coordinate >= $toRow) {
 232                  continue;
 233              }
 234              $column = $coordinate % self::MAX_COLUMN_ID;
 235              $maxColumn = $maxColumn > $column ? $maxColumn : $column;
 236          }
 237  
 238          return Coordinate::stringFromColumnIndex($maxColumn);
 239      }
 240  
 241      /**
 242       * Get highest worksheet row.
 243       *
 244       * @param null|string $column Return the highest row for the specified column,
 245       *                       or the highest row of any column if no column letter is passed
 246       *
 247       * @return int Highest row number
 248       */
 249      public function getHighestRow($column = null)
 250      {
 251          if ($column === null) {
 252              return $this->getHighestRowAndColumn()['row'];
 253          }
 254  
 255          $maxRow = 1;
 256          $columnIndex = Coordinate::columnIndexFromString($column);
 257          foreach ($this->index as $coordinate) {
 258              if ($coordinate % self::MAX_COLUMN_ID !== $columnIndex) {
 259                  continue;
 260              }
 261              $row = (int) floor($coordinate / self::MAX_COLUMN_ID) + 1;
 262              $maxRow = ($maxRow > $row) ? $maxRow : $row;
 263          }
 264  
 265          return $maxRow;
 266      }
 267  
 268      /**
 269       * Generate a unique ID for cache referencing.
 270       *
 271       * @return string Unique Reference
 272       */
 273      private function getUniqueID()
 274      {
 275          $cacheType = Settings::getCache();
 276  
 277          return ($cacheType instanceof Memory\SimpleCache1 || $cacheType instanceof Memory\SimpleCache3)
 278              ? random_bytes(7) . ':'
 279              : uniqid('phpspreadsheet.', true) . '.';
 280      }
 281  
 282      /**
 283       * Clone the cell collection.
 284       *
 285       * @return self
 286       */
 287      public function cloneCellCollection(Worksheet $worksheet)
 288      {
 289          $this->storeCurrentCell();
 290          $newCollection = clone $this;
 291  
 292          $newCollection->parent = $worksheet;
 293          $newCollection->cachePrefix = $newCollection->getUniqueID();
 294  
 295          foreach ($this->index as $key => $value) {
 296              $newCollection->index[$key] = $value;
 297              $stored = $newCollection->cache->set(
 298                  $newCollection->cachePrefix . $key,
 299                  clone $this->cache->get($this->cachePrefix . $key)
 300              );
 301              if ($stored === false) {
 302                  $this->destructIfNeeded($newCollection, 'Failed to copy cells in cache');
 303              }
 304          }
 305  
 306          return $newCollection;
 307      }
 308  
 309      /**
 310       * Remove a row, deleting all cells in that row.
 311       *
 312       * @param int|string $row Row number to remove
 313       */
 314      public function removeRow($row): void
 315      {
 316          $this->storeCurrentCell();
 317          $row = (int) $row;
 318          if ($row <= 0) {
 319              throw new PhpSpreadsheetException('Row number must be a positive integer');
 320          }
 321  
 322          $toRow = $row * self::MAX_COLUMN_ID;
 323          $fromRow = --$row * self::MAX_COLUMN_ID;
 324          foreach ($this->index as $coordinate) {
 325              if ($coordinate >= $fromRow && $coordinate < $toRow) {
 326                  $row = (int) floor($coordinate / self::MAX_COLUMN_ID) + 1;
 327                  $column = Coordinate::stringFromColumnIndex($coordinate % self::MAX_COLUMN_ID);
 328                  $this->delete("{$column}{$row}");
 329              }
 330          }
 331      }
 332  
 333      /**
 334       * Remove a column, deleting all cells in that column.
 335       *
 336       * @param string $column Column ID to remove
 337       */
 338      public function removeColumn($column): void
 339      {
 340          $this->storeCurrentCell();
 341  
 342          $columnIndex = Coordinate::columnIndexFromString($column);
 343          foreach ($this->index as $coordinate) {
 344              if ($coordinate % self::MAX_COLUMN_ID === $columnIndex) {
 345                  $row = (int) floor($coordinate / self::MAX_COLUMN_ID) + 1;
 346                  $column = Coordinate::stringFromColumnIndex($coordinate % self::MAX_COLUMN_ID);
 347                  $this->delete("{$column}{$row}");
 348              }
 349          }
 350      }
 351  
 352      /**
 353       * Store cell data in cache for the current cell object if it's "dirty",
 354       * and the 'nullify' the current cell object.
 355       */
 356      private function storeCurrentCell(): void
 357      {
 358          if ($this->currentCellIsDirty && isset($this->currentCoordinate, $this->currentCell)) {
 359              $this->currentCell->/** @scrutinizer ignore-call */ detach();
 360  
 361              $stored = $this->cache->set($this->cachePrefix . $this->currentCoordinate, $this->currentCell);
 362              if ($stored === false) {
 363                  $this->destructIfNeeded($this, "Failed to store cell {$this->currentCoordinate} in cache");
 364              }
 365              $this->currentCellIsDirty = false;
 366          }
 367  
 368          $this->currentCoordinate = null;
 369          $this->currentCell = null;
 370      }
 371  
 372      private function destructIfNeeded(self $cells, string $message): void
 373      {
 374          $cells->__destruct();
 375  
 376          throw new PhpSpreadsheetException($message);
 377      }
 378  
 379      /**
 380       * Add or update a cell identified by its coordinate into the collection.
 381       *
 382       * @param string $cellCoordinate Coordinate of the cell to update
 383       * @param Cell $cell Cell to update
 384       *
 385       * @return Cell
 386       */
 387      public function add($cellCoordinate, Cell $cell)
 388      {
 389          if ($cellCoordinate !== $this->currentCoordinate) {
 390              $this->storeCurrentCell();
 391          }
 392          $column = 0;
 393          $row = '';
 394          sscanf($cellCoordinate, '%[A-Z]%d', $column, $row);
 395          $this->index[$cellCoordinate] = (--$row * self::MAX_COLUMN_ID) + Coordinate::columnIndexFromString((string) $column);
 396  
 397          $this->currentCoordinate = $cellCoordinate;
 398          $this->currentCell = $cell;
 399          $this->currentCellIsDirty = true;
 400  
 401          return $cell;
 402      }
 403  
 404      /**
 405       * Get cell at a specific coordinate.
 406       *
 407       * @param string $cellCoordinate Coordinate of the cell
 408       *
 409       * @return null|Cell Cell that was found, or null if not found
 410       */
 411      public function get($cellCoordinate)
 412      {
 413          if ($cellCoordinate === $this->currentCoordinate) {
 414              return $this->currentCell;
 415          }
 416          $this->storeCurrentCell();
 417  
 418          // Return null if requested entry doesn't exist in collection
 419          if ($this->has($cellCoordinate) === false) {
 420              return null;
 421          }
 422  
 423          // Check if the entry that has been requested actually exists in the cache
 424          $cell = $this->cache->get($this->cachePrefix . $cellCoordinate);
 425          if ($cell === null) {
 426              throw new PhpSpreadsheetException("Cell entry {$cellCoordinate} no longer exists in cache. This probably means that the cache was cleared by someone else.");
 427          }
 428  
 429          // Set current entry to the requested entry
 430          $this->currentCoordinate = $cellCoordinate;
 431          $this->currentCell = $cell;
 432          // Re-attach this as the cell's parent
 433          $this->currentCell->attach($this);
 434  
 435          // Return requested entry
 436          return $this->currentCell;
 437      }
 438  
 439      /**
 440       * Clear the cell collection and disconnect from our parent.
 441       */
 442      public function unsetWorksheetCells(): void
 443      {
 444          if ($this->currentCell !== null) {
 445              $this->currentCell->detach();
 446              $this->currentCell = null;
 447              $this->currentCoordinate = null;
 448          }
 449  
 450          // Flush the cache
 451          $this->__destruct();
 452  
 453          $this->index = [];
 454  
 455          // detach ourself from the worksheet, so that it can then delete this object successfully
 456          $this->parent = null;
 457      }
 458  
 459      /**
 460       * Destroy this cell collection.
 461       */
 462      public function __destruct()
 463      {
 464          $this->cache->deleteMultiple($this->getAllCacheKeys());
 465      }
 466  
 467      /**
 468       * Returns all known cache keys.
 469       *
 470       * @return Generator|string[]
 471       */
 472      private function getAllCacheKeys()
 473      {
 474          foreach ($this->index as $coordinate => $value) {
 475              yield $this->cachePrefix . $coordinate;
 476          }
 477      }
 478  }