3 namespace PhpOffice\PhpSpreadsheet\Collection
;
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
;
14 * @var \Psr\SimpleCache\CacheInterface
26 * The currently active Cell.
33 * Coordinate of the currently active Cell.
37 private $currentCoordinate;
40 * Flag indicating whether the currently active Cell requires saving.
44 private $currentCellIsDirty = false;
47 * An index of existing cells. Booleans indexed by their coordinate.
54 * Prefix used to uniquely identify cache data for this worksheet.
61 * Initialise this new cell collection.
63 * @param Worksheet $parent The worksheet for this cell collection
64 * @param CacheInterface $cache
66 public function __construct(Worksheet
$parent, CacheInterface
$cache)
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();
77 * Return the parent worksheet for this cell collection.
81 public function getParent()
87 * Whether the collection holds a cell for the given coordinate.
89 * @param string $pCoord Coordinate of the cell to check
93 public function has($pCoord)
95 if ($pCoord === $this->currentCoordinate
) {
99 // Check if the requested entry exists in the index
100 return isset($this->index
[$pCoord]);
104 * Add or update a cell in the collection.
106 * @param Cell $cell Cell to update
108 * @throws PhpSpreadsheetException
112 public function update(Cell
$cell)
114 return $this->add($cell->getCoordinate(), $cell);
118 * Delete a cell in cache identified by coordinate.
120 * @param string $pCoord Coordinate of the cell to delete
122 public function delete($pCoord)
124 if ($pCoord === $this->currentCoordinate
&& $this->currentCell
!== null) {
125 $this->currentCell
->detach();
126 $this->currentCoordinate
= null;
127 $this->currentCell
= null;
128 $this->currentCellIsDirty
= false;
131 unset($this->index
[$pCoord]);
133 // Delete the entry from cache
134 $this->cache
->delete($this->cachePrefix
. $pCoord);
138 * Get a list of all cell coordinates currently held in the collection.
142 public function getCoordinates()
144 return array_keys($this->index
);
148 * Get a sorted list of all cell coordinates currently held in the collection by row and column.
152 public function getSortedCoordinates()
155 foreach ($this->getCoordinates() as $coord) {
156 sscanf($coord, '%[A-Z]%d', $column, $row);
157 $sortKeys[sprintf('%09d%3s', $row, $column)] = $coord;
161 return array_values($sortKeys);
165 * Get highest worksheet column and highest row that have cell records.
167 * @return array Highest column name and highest row number
169 public function getHighestRowAndColumn()
171 // Lookup highest column and highest row
172 $col = ['A' => '1A'];
174 foreach ($this->getCoordinates() as $coord) {
175 sscanf($coord, '%[A-Z]%d', $c, $r);
177 $col[$c] = strlen($c) . $c;
180 // Determine highest column and row
181 $highestRow = max($row);
182 $highestColumn = substr(max($col), 1);
186 'row' => $highestRow,
187 'column' => $highestColumn,
192 * Return the cell coordinate of the currently active cell object.
196 public function getCurrentCoordinate()
198 return $this->currentCoordinate
;
202 * Return the column coordinate of the currently active cell object.
206 public function getCurrentColumn()
208 sscanf($this->currentCoordinate
, '%[A-Z]%d', $column, $row);
214 * Return the row coordinate of the currently active cell object.
218 public function getCurrentRow()
220 sscanf($this->currentCoordinate
, '%[A-Z]%d', $column, $row);
226 * Get highest worksheet column.
228 * @param string $row Return the highest column for the specified row,
229 * or the highest column of any row if no row number is passed
231 * @return string Highest column name
233 public function getHighestColumn($row = null)
236 $colRow = $this->getHighestRowAndColumn();
238 return $colRow['column'];
242 foreach ($this->getCoordinates() as $coord) {
243 sscanf($coord, '%[A-Z]%d', $c, $r);
247 $columnList[] = Coordinate
::columnIndexFromString($c);
250 return Coordinate
::stringFromColumnIndex(max($columnList) +
1);
254 * Get highest worksheet row.
256 * @param string $column Return the highest row for the specified column,
257 * or the highest row of any column if no column letter is passed
259 * @return int Highest row number
261 public function getHighestRow($column = null)
263 if ($column == null) {
264 $colRow = $this->getHighestRowAndColumn();
266 return $colRow['row'];
270 foreach ($this->getCoordinates() as $coord) {
271 sscanf($coord, '%[A-Z]%d', $c, $r);
278 return max($rowList);
282 * Generate a unique ID for cache referencing.
284 * @return string Unique Reference
286 private function getUniqueID()
288 return uniqid('phpspreadsheet.', true) . '.';
292 * Clone the cell collection.
294 * @param Worksheet $parent The new worksheet that we're copying to
298 public function cloneCellCollection(Worksheet
$parent)
300 $this->storeCurrentCell();
301 $newCollection = clone $this;
303 $newCollection->parent
= $parent;
304 if (($newCollection->currentCell
!== null) && (is_object($newCollection->currentCell
))) {
305 $newCollection->currentCell
->attach($this);
309 $oldKeys = $newCollection->getAllCacheKeys();
310 $oldValues = $newCollection->cache
->getMultiple($oldKeys);
312 $oldCachePrefix = $newCollection->cachePrefix
;
315 $newCollection->cachePrefix
= $newCollection->getUniqueID();
316 foreach ($oldValues as $oldKey => $value) {
317 $newValues[str_replace($oldCachePrefix, $newCollection->cachePrefix
, $oldKey)] = clone $value;
321 $stored = $newCollection->cache
->setMultiple($newValues);
323 $newCollection->__destruct();
325 throw new PhpSpreadsheetException('Failed to copy cells in cache');
328 return $newCollection;
332 * Remove a row, deleting all cells in that row.
334 * @param string $row Row number to remove
336 public function removeRow($row)
338 foreach ($this->getCoordinates() as $coord) {
339 sscanf($coord, '%[A-Z]%d', $c, $r);
341 $this->delete($coord);
347 * Remove a column, deleting all cells in that column.
349 * @param string $column Column ID to remove
351 public function removeColumn($column)
353 foreach ($this->getCoordinates() as $coord) {
354 sscanf($coord, '%[A-Z]%d', $c, $r);
356 $this->delete($coord);
362 * Store cell data in cache for the current cell object if it's "dirty",
363 * and the 'nullify' the current cell object.
365 * @throws PhpSpreadsheetException
367 private function storeCurrentCell()
369 if ($this->currentCellIsDirty
&& !empty($this->currentCoordinate
)) {
370 $this->currentCell
->detach();
372 $stored = $this->cache
->set($this->cachePrefix
. $this->currentCoordinate
, $this->currentCell
);
376 throw new PhpSpreadsheetException("Failed to store cell {$this->currentCoordinate} in cache");
378 $this->currentCellIsDirty
= false;
381 $this->currentCoordinate
= null;
382 $this->currentCell
= null;
386 * Add or update a cell identified by its coordinate into the collection.
388 * @param string $pCoord Coordinate of the cell to update
389 * @param Cell $cell Cell to update
391 * @throws PhpSpreadsheetException
393 * @return \PhpOffice\PhpSpreadsheet\Cell\Cell
395 public function add($pCoord, Cell
$cell)
397 if ($pCoord !== $this->currentCoordinate
) {
398 $this->storeCurrentCell();
400 $this->index
[$pCoord] = true;
402 $this->currentCoordinate
= $pCoord;
403 $this->currentCell
= $cell;
404 $this->currentCellIsDirty
= true;
410 * Get cell at a specific coordinate.
412 * @param string $pCoord Coordinate of the cell
414 * @throws PhpSpreadsheetException
416 * @return \PhpOffice\PhpSpreadsheet\Cell\Cell Cell that was found, or null if not found
418 public function get($pCoord)
420 if ($pCoord === $this->currentCoordinate
) {
421 return $this->currentCell
;
423 $this->storeCurrentCell();
425 // Return null if requested entry doesn't exist in collection
426 if (!$this->has($pCoord)) {
430 // Check if the entry that has been requested actually exists
431 $cell = $this->cache
->get($this->cachePrefix
. $pCoord);
432 if ($cell === null) {
433 throw new PhpSpreadsheetException("Cell entry {$pCoord} no longer exists in cache. This probably means that the cache was cleared by someone else.");
436 // Set current entry to the requested entry
437 $this->currentCoordinate
= $pCoord;
438 $this->currentCell
= $cell;
439 // Re-attach this as the cell's parent
440 $this->currentCell
->attach($this);
442 // Return requested entry
443 return $this->currentCell
;
447 * Clear the cell collection and disconnect from our parent.
449 public function unsetWorksheetCells()
451 if ($this->currentCell
!== null) {
452 $this->currentCell
->detach();
453 $this->currentCell
= null;
454 $this->currentCoordinate
= null;
462 // detach ourself from the worksheet, so that it can then delete this object successfully
463 $this->parent
= null;
467 * Destroy this cell collection.
469 public function __destruct()
471 $this->cache
->deleteMultiple($this->getAllCacheKeys());
475 * Returns all known cache keys.
479 private function getAllCacheKeys()
482 foreach ($this->getCoordinates() as $coordinate) {
483 $keys[] = $this->cachePrefix
. $coordinate;