[Summary view]

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

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