[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Cell\DataType;
   8  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   9  
  10  class ReferenceHelper
  11  {
  12      /**    Constants                */
  13      /**    Regular Expressions      */
  14      const REFHELPER_REGEXP_CELLREF = '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
  15      const REFHELPER_REGEXP_CELLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
  16      const REFHELPER_REGEXP_ROWRANGE = '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
  17      const REFHELPER_REGEXP_COLRANGE = '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
  18  
  19      /**
  20       * Instance of this class.
  21       *
  22       * @var ReferenceHelper
  23       */
  24      private static $instance;
  25  
  26      /**
  27       * Get an instance of this class.
  28       *
  29       * @return ReferenceHelper
  30       */
  31      public static function getInstance()
  32      {
  33          if (!isset(self::$instance) || (self::$instance === null)) {
  34              self::$instance = new self();
  35          }
  36  
  37          return self::$instance;
  38      }
  39  
  40      /**
  41       * Create a new ReferenceHelper.
  42       */
  43      protected function __construct()
  44      {
  45      }
  46  
  47      /**
  48       * Compare two column addresses
  49       * Intended for use as a Callback function for sorting column addresses by column.
  50       *
  51       * @param string $a First column to test (e.g. 'AA')
  52       * @param string $b Second column to test (e.g. 'Z')
  53       *
  54       * @return int
  55       */
  56      public static function columnSort($a, $b)
  57      {
  58          return strcasecmp(strlen($a) . $a, strlen($b) . $b);
  59      }
  60  
  61      /**
  62       * Compare two column addresses
  63       * Intended for use as a Callback function for reverse sorting column addresses by column.
  64       *
  65       * @param string $a First column to test (e.g. 'AA')
  66       * @param string $b Second column to test (e.g. 'Z')
  67       *
  68       * @return int
  69       */
  70      public static function columnReverseSort($a, $b)
  71      {
  72          return -strcasecmp(strlen($a) . $a, strlen($b) . $b);
  73      }
  74  
  75      /**
  76       * Compare two cell addresses
  77       * Intended for use as a Callback function for sorting cell addresses by column and row.
  78       *
  79       * @param string $a First cell to test (e.g. 'AA1')
  80       * @param string $b Second cell to test (e.g. 'Z1')
  81       *
  82       * @return int
  83       */
  84      public static function cellSort($a, $b)
  85      {
  86          [$ac, $ar] = sscanf($a, '%[A-Z]%d');
  87          [$bc, $br] = sscanf($b, '%[A-Z]%d');
  88  
  89          if ($ar === $br) {
  90              return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
  91          }
  92  
  93          return ($ar < $br) ? -1 : 1;
  94      }
  95  
  96      /**
  97       * Compare two cell addresses
  98       * Intended for use as a Callback function for sorting cell addresses by column and row.
  99       *
 100       * @param string $a First cell to test (e.g. 'AA1')
 101       * @param string $b Second cell to test (e.g. 'Z1')
 102       *
 103       * @return int
 104       */
 105      public static function cellReverseSort($a, $b)
 106      {
 107          [$ac, $ar] = sscanf($a, '%[A-Z]%d');
 108          [$bc, $br] = sscanf($b, '%[A-Z]%d');
 109  
 110          if ($ar === $br) {
 111              return -strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
 112          }
 113  
 114          return ($ar < $br) ? 1 : -1;
 115      }
 116  
 117      /**
 118       * Test whether a cell address falls within a defined range of cells.
 119       *
 120       * @param string $cellAddress Address of the cell we're testing
 121       * @param int $beforeRow Number of the row we're inserting/deleting before
 122       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 123       * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before
 124       * @param int $numberOfCols Number of columns to insert/delete (negative values indicate deletion)
 125       *
 126       * @return bool
 127       */
 128      private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $numberOfRows, $beforeColumnIndex, $numberOfCols)
 129      {
 130          [$cellColumn, $cellRow] = Coordinate::coordinateFromString($cellAddress);
 131          $cellColumnIndex = Coordinate::columnIndexFromString($cellColumn);
 132          //    Is cell within the range of rows/columns if we're deleting
 133          if (
 134              $numberOfRows < 0 &&
 135              ($cellRow >= ($beforeRow + $numberOfRows)) &&
 136              ($cellRow < $beforeRow)
 137          ) {
 138              return true;
 139          } elseif (
 140              $numberOfCols < 0 &&
 141              ($cellColumnIndex >= ($beforeColumnIndex + $numberOfCols)) &&
 142              ($cellColumnIndex < $beforeColumnIndex)
 143          ) {
 144              return true;
 145          }
 146  
 147          return false;
 148      }
 149  
 150      /**
 151       * Update page breaks when inserting/deleting rows/columns.
 152       *
 153       * @param Worksheet $worksheet The worksheet that we're editing
 154       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 155       * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before
 156       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 157       * @param int $beforeRow Number of the row we're inserting/deleting before
 158       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 159       */
 160      protected function adjustPageBreaks(Worksheet $worksheet, $beforeCellAddress, $beforeColumnIndex, $numberOfColumns, $beforeRow, $numberOfRows): void
 161      {
 162          $aBreaks = $worksheet->getBreaks();
 163          ($numberOfColumns > 0 || $numberOfRows > 0) ?
 164              uksort($aBreaks, ['self', 'cellReverseSort']) : uksort($aBreaks, ['self', 'cellSort']);
 165  
 166          foreach ($aBreaks as $key => $value) {
 167              if (self::cellAddressInDeleteRange($key, $beforeRow, $numberOfRows, $beforeColumnIndex, $numberOfColumns)) {
 168                  //    If we're deleting, then clear any defined breaks that are within the range
 169                  //        of rows/columns that we're deleting
 170                  $worksheet->setBreak($key, Worksheet::BREAK_NONE);
 171              } else {
 172                  //    Otherwise update any affected breaks by inserting a new break at the appropriate point
 173                  //        and removing the old affected break
 174                  $newReference = $this->updateCellReference($key, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 175                  if ($key != $newReference) {
 176                      $worksheet->setBreak($newReference, $value)
 177                          ->setBreak($key, Worksheet::BREAK_NONE);
 178                  }
 179              }
 180          }
 181      }
 182  
 183      /**
 184       * Update cell comments when inserting/deleting rows/columns.
 185       *
 186       * @param Worksheet $worksheet The worksheet that we're editing
 187       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 188       * @param int $beforeColumnIndex Index number of the column we're inserting/deleting before
 189       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 190       * @param int $beforeRow Number of the row we're inserting/deleting before
 191       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 192       */
 193      protected function adjustComments($worksheet, $beforeCellAddress, $beforeColumnIndex, $numberOfColumns, $beforeRow, $numberOfRows): void
 194      {
 195          $aComments = $worksheet->getComments();
 196          $aNewComments = []; // the new array of all comments
 197  
 198          foreach ($aComments as $key => &$value) {
 199              // Any comments inside a deleted range will be ignored
 200              if (!self::cellAddressInDeleteRange($key, $beforeRow, $numberOfRows, $beforeColumnIndex, $numberOfColumns)) {
 201                  // Otherwise build a new array of comments indexed by the adjusted cell reference
 202                  $newReference = $this->updateCellReference($key, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 203                  $aNewComments[$newReference] = $value;
 204              }
 205          }
 206          //    Replace the comments array with the new set of comments
 207          $worksheet->setComments($aNewComments);
 208      }
 209  
 210      /**
 211       * Update hyperlinks when inserting/deleting rows/columns.
 212       *
 213       * @param Worksheet $worksheet The worksheet that we're editing
 214       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 215       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 216       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 217       */
 218      protected function adjustHyperlinks($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows): void
 219      {
 220          $aHyperlinkCollection = $worksheet->getHyperlinkCollection();
 221          ($numberOfColumns > 0 || $numberOfRows > 0) ?
 222              uksort($aHyperlinkCollection, ['self', 'cellReverseSort']) : uksort($aHyperlinkCollection, ['self', 'cellSort']);
 223  
 224          foreach ($aHyperlinkCollection as $key => $value) {
 225              $newReference = $this->updateCellReference($key, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 226              if ($key != $newReference) {
 227                  $worksheet->setHyperlink($newReference, $value);
 228                  $worksheet->setHyperlink($key, null);
 229              }
 230          }
 231      }
 232  
 233      /**
 234       * Update data validations when inserting/deleting rows/columns.
 235       *
 236       * @param Worksheet $worksheet The worksheet that we're editing
 237       * @param string $before Insert/Delete before this cell address (e.g. 'A1')
 238       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 239       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 240       */
 241      protected function adjustDataValidations(Worksheet $worksheet, $before, $numberOfColumns, $numberOfRows): void
 242      {
 243          $aDataValidationCollection = $worksheet->getDataValidationCollection();
 244          ($numberOfColumns > 0 || $numberOfRows > 0) ?
 245              uksort($aDataValidationCollection, ['self', 'cellReverseSort']) : uksort($aDataValidationCollection, ['self', 'cellSort']);
 246  
 247          foreach ($aDataValidationCollection as $key => $value) {
 248              $newReference = $this->updateCellReference($key, $before, $numberOfColumns, $numberOfRows);
 249              if ($key != $newReference) {
 250                  $worksheet->setDataValidation($newReference, $value);
 251                  $worksheet->setDataValidation($key, null);
 252              }
 253          }
 254      }
 255  
 256      /**
 257       * Update merged cells when inserting/deleting rows/columns.
 258       *
 259       * @param Worksheet $worksheet The worksheet that we're editing
 260       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 261       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 262       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 263       */
 264      protected function adjustMergeCells(Worksheet $worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows): void
 265      {
 266          $aMergeCells = $worksheet->getMergeCells();
 267          $aNewMergeCells = []; // the new array of all merge cells
 268          foreach ($aMergeCells as $key => &$value) {
 269              $newReference = $this->updateCellReference($key, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 270              $aNewMergeCells[$newReference] = $newReference;
 271          }
 272          $worksheet->setMergeCells($aNewMergeCells); // replace the merge cells array
 273      }
 274  
 275      /**
 276       * Update protected cells when inserting/deleting rows/columns.
 277       *
 278       * @param Worksheet $worksheet The worksheet that we're editing
 279       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 280       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 281       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 282       */
 283      protected function adjustProtectedCells(Worksheet $worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows): void
 284      {
 285          $aProtectedCells = $worksheet->getProtectedCells();
 286          ($numberOfColumns > 0 || $numberOfRows > 0) ?
 287              uksort($aProtectedCells, ['self', 'cellReverseSort']) : uksort($aProtectedCells, ['self', 'cellSort']);
 288          foreach ($aProtectedCells as $key => $value) {
 289              $newReference = $this->updateCellReference($key, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 290              if ($key != $newReference) {
 291                  $worksheet->protectCells($newReference, $value, true);
 292                  $worksheet->unprotectCells($key);
 293              }
 294          }
 295      }
 296  
 297      /**
 298       * Update column dimensions when inserting/deleting rows/columns.
 299       *
 300       * @param Worksheet $worksheet The worksheet that we're editing
 301       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 302       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 303       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 304       */
 305      protected function adjustColumnDimensions(Worksheet $worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows): void
 306      {
 307          $aColumnDimensions = array_reverse($worksheet->getColumnDimensions(), true);
 308          if (!empty($aColumnDimensions)) {
 309              foreach ($aColumnDimensions as $objColumnDimension) {
 310                  $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $beforeCellAddress, $numberOfColumns, $numberOfRows);
 311                  [$newReference] = Coordinate::coordinateFromString($newReference);
 312                  if ($objColumnDimension->getColumnIndex() != $newReference) {
 313                      $objColumnDimension->setColumnIndex($newReference);
 314                  }
 315              }
 316              $worksheet->refreshColumnDimensions();
 317          }
 318      }
 319  
 320      /**
 321       * Update row dimensions when inserting/deleting rows/columns.
 322       *
 323       * @param Worksheet $worksheet The worksheet that we're editing
 324       * @param string $beforeCellAddress Insert/Delete before this cell address (e.g. 'A1')
 325       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 326       * @param int $beforeRow Number of the row we're inserting/deleting before
 327       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 328       */
 329      protected function adjustRowDimensions(Worksheet $worksheet, $beforeCellAddress, $numberOfColumns, $beforeRow, $numberOfRows): void
 330      {
 331          $aRowDimensions = array_reverse($worksheet->getRowDimensions(), true);
 332          if (!empty($aRowDimensions)) {
 333              foreach ($aRowDimensions as $objRowDimension) {
 334                  $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $beforeCellAddress, $numberOfColumns, $numberOfRows);
 335                  [, $newReference] = Coordinate::coordinateFromString($newReference);
 336                  if ($objRowDimension->getRowIndex() != $newReference) {
 337                      $objRowDimension->setRowIndex($newReference);
 338                  }
 339              }
 340              $worksheet->refreshRowDimensions();
 341  
 342              $copyDimension = $worksheet->getRowDimension($beforeRow - 1);
 343              for ($i = $beforeRow; $i <= $beforeRow - 1 + $numberOfRows; ++$i) {
 344                  $newDimension = $worksheet->getRowDimension($i);
 345                  $newDimension->setRowHeight($copyDimension->getRowHeight());
 346                  $newDimension->setVisible($copyDimension->getVisible());
 347                  $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
 348                  $newDimension->setCollapsed($copyDimension->getCollapsed());
 349              }
 350          }
 351      }
 352  
 353      /**
 354       * Insert a new column or row, updating all possible related data.
 355       *
 356       * @param string $beforeCellAddress Insert before this cell address (e.g. 'A1')
 357       * @param int $numberOfColumns Number of columns to insert/delete (negative values indicate deletion)
 358       * @param int $numberOfRows Number of rows to insert/delete (negative values indicate deletion)
 359       * @param Worksheet $worksheet The worksheet that we're editing
 360       */
 361      public function insertNewBefore($beforeCellAddress, $numberOfColumns, $numberOfRows, Worksheet $worksheet): void
 362      {
 363          $remove = ($numberOfColumns < 0 || $numberOfRows < 0);
 364          $allCoordinates = $worksheet->getCoordinates();
 365  
 366          // Get coordinate of $beforeCellAddress
 367          [$beforeColumn, $beforeRow] = Coordinate::indexesFromString($beforeCellAddress);
 368  
 369          // Clear cells if we are removing columns or rows
 370          $highestColumn = $worksheet->getHighestColumn();
 371          $highestRow = $worksheet->getHighestRow();
 372  
 373          // 1. Clear column strips if we are removing columns
 374          if ($numberOfColumns < 0 && $beforeColumn - 2 + $numberOfColumns > 0) {
 375              for ($i = 1; $i <= $highestRow - 1; ++$i) {
 376                  for ($j = $beforeColumn - 1 + $numberOfColumns; $j <= $beforeColumn - 2; ++$j) {
 377                      $coordinate = Coordinate::stringFromColumnIndex($j + 1) . $i;
 378                      $worksheet->removeConditionalStyles($coordinate);
 379                      if ($worksheet->cellExists($coordinate)) {
 380                          $worksheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL);
 381                          $worksheet->getCell($coordinate)->setXfIndex(0);
 382                      }
 383                  }
 384              }
 385          }
 386  
 387          // 2. Clear row strips if we are removing rows
 388          if ($numberOfRows < 0 && $beforeRow - 1 + $numberOfRows > 0) {
 389              for ($i = $beforeColumn - 1; $i <= Coordinate::columnIndexFromString($highestColumn) - 1; ++$i) {
 390                  for ($j = $beforeRow + $numberOfRows; $j <= $beforeRow - 1; ++$j) {
 391                      $coordinate = Coordinate::stringFromColumnIndex($i + 1) . $j;
 392                      $worksheet->removeConditionalStyles($coordinate);
 393                      if ($worksheet->cellExists($coordinate)) {
 394                          $worksheet->getCell($coordinate)->setValueExplicit('', DataType::TYPE_NULL);
 395                          $worksheet->getCell($coordinate)->setXfIndex(0);
 396                      }
 397                  }
 398              }
 399          }
 400  
 401          // Loop through cells, bottom-up, and change cell coordinate
 402          if ($remove) {
 403              // It's faster to reverse and pop than to use unshift, especially with large cell collections
 404              $allCoordinates = array_reverse($allCoordinates);
 405          }
 406          while ($coordinate = array_pop($allCoordinates)) {
 407              $cell = $worksheet->getCell($coordinate);
 408              $cellIndex = Coordinate::columnIndexFromString($cell->getColumn());
 409  
 410              if ($cellIndex - 1 + $numberOfColumns < 0) {
 411                  continue;
 412              }
 413  
 414              // New coordinate
 415              $newCoordinate = Coordinate::stringFromColumnIndex($cellIndex + $numberOfColumns) . ($cell->getRow() + $numberOfRows);
 416  
 417              // Should the cell be updated? Move value and cellXf index from one cell to another.
 418              if (($cellIndex >= $beforeColumn) && ($cell->getRow() >= $beforeRow)) {
 419                  // Update cell styles
 420                  $worksheet->getCell($newCoordinate)->setXfIndex($cell->getXfIndex());
 421  
 422                  // Insert this cell at its new location
 423                  if ($cell->getDataType() == DataType::TYPE_FORMULA) {
 424                      // Formula should be adjusted
 425                      $worksheet->getCell($newCoordinate)
 426                          ->setValue($this->updateFormulaReferences($cell->getValue(), $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()));
 427                  } else {
 428                      // Formula should not be adjusted
 429                      $worksheet->getCell($newCoordinate)->setValueExplicit($cell->getValue(), $cell->getDataType());
 430                  }
 431  
 432                  // Clear the original cell
 433                  $worksheet->getCellCollection()->delete($coordinate);
 434              } else {
 435                  /*    We don't need to update styles for rows/columns before our insertion position,
 436                          but we do still need to adjust any formulae    in those cells                    */
 437                  if ($cell->getDataType() == DataType::TYPE_FORMULA) {
 438                      // Formula should be adjusted
 439                      $cell->setValue($this->updateFormulaReferences($cell->getValue(), $beforeCellAddress, $numberOfColumns, $numberOfRows, $worksheet->getTitle()));
 440                  }
 441              }
 442          }
 443  
 444          // Duplicate styles for the newly inserted cells
 445          $highestColumn = $worksheet->getHighestColumn();
 446          $highestRow = $worksheet->getHighestRow();
 447  
 448          if ($numberOfColumns > 0 && $beforeColumn - 2 > 0) {
 449              for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
 450                  // Style
 451                  $coordinate = Coordinate::stringFromColumnIndex($beforeColumn - 1) . $i;
 452                  if ($worksheet->cellExists($coordinate)) {
 453                      $xfIndex = $worksheet->getCell($coordinate)->getXfIndex();
 454                      $conditionalStyles = $worksheet->conditionalStylesExists($coordinate) ?
 455                          $worksheet->getConditionalStyles($coordinate) : false;
 456                      for ($j = $beforeColumn; $j <= $beforeColumn - 1 + $numberOfColumns; ++$j) {
 457                          $worksheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
 458                          if ($conditionalStyles) {
 459                              $cloned = [];
 460                              foreach ($conditionalStyles as $conditionalStyle) {
 461                                  $cloned[] = clone $conditionalStyle;
 462                              }
 463                              $worksheet->setConditionalStyles(Coordinate::stringFromColumnIndex($j) . $i, $cloned);
 464                          }
 465                      }
 466                  }
 467              }
 468          }
 469  
 470          if ($numberOfRows > 0 && $beforeRow - 1 > 0) {
 471              for ($i = $beforeColumn; $i <= Coordinate::columnIndexFromString($highestColumn); ++$i) {
 472                  // Style
 473                  $coordinate = Coordinate::stringFromColumnIndex($i) . ($beforeRow - 1);
 474                  if ($worksheet->cellExists($coordinate)) {
 475                      $xfIndex = $worksheet->getCell($coordinate)->getXfIndex();
 476                      $conditionalStyles = $worksheet->conditionalStylesExists($coordinate) ?
 477                          $worksheet->getConditionalStyles($coordinate) : false;
 478                      for ($j = $beforeRow; $j <= $beforeRow - 1 + $numberOfRows; ++$j) {
 479                          $worksheet->getCell(Coordinate::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
 480                          if ($conditionalStyles) {
 481                              $cloned = [];
 482                              foreach ($conditionalStyles as $conditionalStyle) {
 483                                  $cloned[] = clone $conditionalStyle;
 484                              }
 485                              $worksheet->setConditionalStyles(Coordinate::stringFromColumnIndex($i) . $j, $cloned);
 486                          }
 487                      }
 488                  }
 489              }
 490          }
 491  
 492          // Update worksheet: column dimensions
 493          $this->adjustColumnDimensions($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 494  
 495          // Update worksheet: row dimensions
 496          $this->adjustRowDimensions($worksheet, $beforeCellAddress, $numberOfColumns, $beforeRow, $numberOfRows);
 497  
 498          //    Update worksheet: page breaks
 499          $this->adjustPageBreaks($worksheet, $beforeCellAddress, $beforeColumn, $numberOfColumns, $beforeRow, $numberOfRows);
 500  
 501          //    Update worksheet: comments
 502          $this->adjustComments($worksheet, $beforeCellAddress, $beforeColumn, $numberOfColumns, $beforeRow, $numberOfRows);
 503  
 504          // Update worksheet: hyperlinks
 505          $this->adjustHyperlinks($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 506  
 507          // Update worksheet: data validations
 508          $this->adjustDataValidations($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 509  
 510          // Update worksheet: merge cells
 511          $this->adjustMergeCells($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 512  
 513          // Update worksheet: protected cells
 514          $this->adjustProtectedCells($worksheet, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 515  
 516          // Update worksheet: autofilter
 517          $autoFilter = $worksheet->getAutoFilter();
 518          $autoFilterRange = $autoFilter->getRange();
 519          if (!empty($autoFilterRange)) {
 520              if ($numberOfColumns != 0) {
 521                  $autoFilterColumns = $autoFilter->getColumns();
 522                  if (count($autoFilterColumns) > 0) {
 523                      $column = '';
 524                      $row = 0;
 525                      sscanf($beforeCellAddress, '%[A-Z]%d', $column, $row);
 526                      $columnIndex = Coordinate::columnIndexFromString($column);
 527                      [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($autoFilterRange);
 528                      if ($columnIndex <= $rangeEnd[0]) {
 529                          if ($numberOfColumns < 0) {
 530                              //    If we're actually deleting any columns that fall within the autofilter range,
 531                              //        then we delete any rules for those columns
 532                              $deleteColumn = $columnIndex + $numberOfColumns - 1;
 533                              $deleteCount = abs($numberOfColumns);
 534                              for ($i = 1; $i <= $deleteCount; ++$i) {
 535                                  if (isset($autoFilterColumns[Coordinate::stringFromColumnIndex($deleteColumn + 1)])) {
 536                                      $autoFilter->clearColumn(Coordinate::stringFromColumnIndex($deleteColumn + 1));
 537                                  }
 538                                  ++$deleteColumn;
 539                              }
 540                          }
 541                          $startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];
 542  
 543                          //    Shuffle columns in autofilter range
 544                          if ($numberOfColumns > 0) {
 545                              $startColRef = $startCol;
 546                              $endColRef = $rangeEnd[0];
 547                              $toColRef = $rangeEnd[0] + $numberOfColumns;
 548  
 549                              do {
 550                                  $autoFilter->shiftColumn(Coordinate::stringFromColumnIndex($endColRef), Coordinate::stringFromColumnIndex($toColRef));
 551                                  --$endColRef;
 552                                  --$toColRef;
 553                              } while ($startColRef <= $endColRef);
 554                          } else {
 555                              //    For delete, we shuffle from beginning to end to avoid overwriting
 556                              $startColID = Coordinate::stringFromColumnIndex($startCol);
 557                              $toColID = Coordinate::stringFromColumnIndex($startCol + $numberOfColumns);
 558                              $endColID = Coordinate::stringFromColumnIndex($rangeEnd[0] + 1);
 559                              do {
 560                                  $autoFilter->shiftColumn($startColID, $toColID);
 561                                  ++$startColID;
 562                                  ++$toColID;
 563                              } while ($startColID != $endColID);
 564                          }
 565                      }
 566                  }
 567              }
 568              $worksheet->setAutoFilter($this->updateCellReference($autoFilterRange, $beforeCellAddress, $numberOfColumns, $numberOfRows));
 569          }
 570  
 571          // Update worksheet: freeze pane
 572          if ($worksheet->getFreezePane()) {
 573              $splitCell = $worksheet->getFreezePane() ?? '';
 574              $topLeftCell = $worksheet->getTopLeftCell() ?? '';
 575  
 576              $splitCell = $this->updateCellReference($splitCell, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 577              $topLeftCell = $this->updateCellReference($topLeftCell, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 578  
 579              $worksheet->freezePane($splitCell, $topLeftCell);
 580          }
 581  
 582          // Page setup
 583          if ($worksheet->getPageSetup()->isPrintAreaSet()) {
 584              $worksheet->getPageSetup()->setPrintArea($this->updateCellReference($worksheet->getPageSetup()->getPrintArea(), $beforeCellAddress, $numberOfColumns, $numberOfRows));
 585          }
 586  
 587          // Update worksheet: drawings
 588          $aDrawings = $worksheet->getDrawingCollection();
 589          foreach ($aDrawings as $objDrawing) {
 590              $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $beforeCellAddress, $numberOfColumns, $numberOfRows);
 591              if ($objDrawing->getCoordinates() != $newReference) {
 592                  $objDrawing->setCoordinates($newReference);
 593              }
 594          }
 595  
 596          // Update workbook: define names
 597          if (count($worksheet->getParent()->getDefinedNames()) > 0) {
 598              foreach ($worksheet->getParent()->getDefinedNames() as $definedName) {
 599                  if ($definedName->getWorksheet() !== null && $definedName->getWorksheet()->getHashCode() === $worksheet->getHashCode()) {
 600                      $definedName->setValue($this->updateCellReference($definedName->getValue(), $beforeCellAddress, $numberOfColumns, $numberOfRows));
 601                  }
 602              }
 603          }
 604  
 605          // Garbage collect
 606          $worksheet->garbageCollect();
 607      }
 608  
 609      /**
 610       * Update references within formulas.
 611       *
 612       * @param string $formula Formula to update
 613       * @param string $beforeCellAddress Insert before this one
 614       * @param int $numberOfColumns Number of columns to insert
 615       * @param int $numberOfRows Number of rows to insert
 616       * @param string $worksheetName Worksheet name/title
 617       *
 618       * @return string Updated formula
 619       */
 620      public function updateFormulaReferences($formula = '', $beforeCellAddress = 'A1', $numberOfColumns = 0, $numberOfRows = 0, $worksheetName = '')
 621      {
 622          //    Update cell references in the formula
 623          $formulaBlocks = explode('"', $formula);
 624          $i = false;
 625          foreach ($formulaBlocks as &$formulaBlock) {
 626              //    Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
 627              if ($i = !$i) {
 628                  $adjustCount = 0;
 629                  $newCellTokens = $cellTokens = [];
 630                  //    Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
 631                  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_ROWRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
 632                  if ($matchCount > 0) {
 633                      foreach ($matches as $match) {
 634                          $fromString = ($match[2] > '') ? $match[2] . '!' : '';
 635                          $fromString .= $match[3] . ':' . $match[4];
 636                          $modified3 = substr($this->updateCellReference('$A' . $match[3], $beforeCellAddress, $numberOfColumns, $numberOfRows), 2);
 637                          $modified4 = substr($this->updateCellReference('$A' . $match[4], $beforeCellAddress, $numberOfColumns, $numberOfRows), 2);
 638  
 639                          if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {
 640                              if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {
 641                                  $toString = ($match[2] > '') ? $match[2] . '!' : '';
 642                                  $toString .= $modified3 . ':' . $modified4;
 643                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 644                                  $column = 100000;
 645                                  $row = 10000000 + (int) trim($match[3], '$');
 646                                  $cellIndex = $column . $row;
 647  
 648                                  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
 649                                  $cellTokens[$cellIndex] = '/(?<!\d\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i';
 650                                  ++$adjustCount;
 651                              }
 652                          }
 653                      }
 654                  }
 655                  //    Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
 656                  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_COLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
 657                  if ($matchCount > 0) {
 658                      foreach ($matches as $match) {
 659                          $fromString = ($match[2] > '') ? $match[2] . '!' : '';
 660                          $fromString .= $match[3] . ':' . $match[4];
 661                          $modified3 = substr($this->updateCellReference($match[3] . '$1', $beforeCellAddress, $numberOfColumns, $numberOfRows), 0, -2);
 662                          $modified4 = substr($this->updateCellReference($match[4] . '$1', $beforeCellAddress, $numberOfColumns, $numberOfRows), 0, -2);
 663  
 664                          if ($match[3] . ':' . $match[4] !== $modified3 . ':' . $modified4) {
 665                              if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {
 666                                  $toString = ($match[2] > '') ? $match[2] . '!' : '';
 667                                  $toString .= $modified3 . ':' . $modified4;
 668                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 669                                  $column = Coordinate::columnIndexFromString(trim($match[3], '$')) + 100000;
 670                                  $row = 10000000;
 671                                  $cellIndex = $column . $row;
 672  
 673                                  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
 674                                  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?![A-Z])/i';
 675                                  ++$adjustCount;
 676                              }
 677                          }
 678                      }
 679                  }
 680                  //    Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
 681                  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLRANGE . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
 682                  if ($matchCount > 0) {
 683                      foreach ($matches as $match) {
 684                          $fromString = ($match[2] > '') ? $match[2] . '!' : '';
 685                          $fromString .= $match[3] . ':' . $match[4];
 686                          $modified3 = $this->updateCellReference($match[3], $beforeCellAddress, $numberOfColumns, $numberOfRows);
 687                          $modified4 = $this->updateCellReference($match[4], $beforeCellAddress, $numberOfColumns, $numberOfRows);
 688  
 689                          if ($match[3] . $match[4] !== $modified3 . $modified4) {
 690                              if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {
 691                                  $toString = ($match[2] > '') ? $match[2] . '!' : '';
 692                                  $toString .= $modified3 . ':' . $modified4;
 693                                  [$column, $row] = Coordinate::coordinateFromString($match[3]);
 694                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 695                                  $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;
 696                                  $row = (int) trim($row, '$') + 10000000;
 697                                  $cellIndex = $column . $row;
 698  
 699                                  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
 700                                  $cellTokens[$cellIndex] = '/(?<![A-Z]\$\!)' . preg_quote($fromString, '/') . '(?!\d)/i';
 701                                  ++$adjustCount;
 702                              }
 703                          }
 704                      }
 705                  }
 706                  //    Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
 707                  $matchCount = preg_match_all('/' . self::REFHELPER_REGEXP_CELLREF . '/i', ' ' . $formulaBlock . ' ', $matches, PREG_SET_ORDER);
 708  
 709                  if ($matchCount > 0) {
 710                      foreach ($matches as $match) {
 711                          $fromString = ($match[2] > '') ? $match[2] . '!' : '';
 712                          $fromString .= $match[3];
 713  
 714                          $modified3 = $this->updateCellReference($match[3], $beforeCellAddress, $numberOfColumns, $numberOfRows);
 715                          if ($match[3] !== $modified3) {
 716                              if (($match[2] == '') || (trim($match[2], "'") == $worksheetName)) {
 717                                  $toString = ($match[2] > '') ? $match[2] . '!' : '';
 718                                  $toString .= $modified3;
 719                                  [$column, $row] = Coordinate::coordinateFromString($match[3]);
 720                                  $columnAdditionalIndex = $column[0] === '$' ? 1 : 0;
 721                                  $rowAdditionalIndex = $row[0] === '$' ? 1 : 0;
 722                                  //    Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
 723                                  $column = Coordinate::columnIndexFromString(trim($column, '$')) + 100000;
 724                                  $row = (int) trim($row, '$') + 10000000;
 725                                  $cellIndex = $row . $rowAdditionalIndex . $column . $columnAdditionalIndex;
 726  
 727                                  $newCellTokens[$cellIndex] = preg_quote($toString, '/');
 728                                  $cellTokens[$cellIndex] = '/(?<![A-Z\$\!])' . preg_quote($fromString, '/') . '(?!\d)/i';
 729                                  ++$adjustCount;
 730                              }
 731                          }
 732                      }
 733                  }
 734                  if ($adjustCount > 0) {
 735                      if ($numberOfColumns > 0 || $numberOfRows > 0) {
 736                          krsort($cellTokens);
 737                          krsort($newCellTokens);
 738                      } else {
 739                          ksort($cellTokens);
 740                          ksort($newCellTokens);
 741                      }   //  Update cell references in the formula
 742                      $formulaBlock = str_replace('\\', '', preg_replace($cellTokens, $newCellTokens, $formulaBlock));
 743                  }
 744              }
 745          }
 746          unset($formulaBlock);
 747  
 748          //    Then rebuild the formula string
 749          return implode('"', $formulaBlocks);
 750      }
 751  
 752      /**
 753       * Update all cell references within a formula, irrespective of worksheet.
 754       */
 755      public function updateFormulaReferencesAnyWorksheet(string $formula = '', int $numberOfColumns = 0, int $numberOfRows = 0): string
 756      {
 757          $formula = $this->updateCellReferencesAllWorksheets($formula, $numberOfColumns, $numberOfRows);
 758  
 759          if ($numberOfColumns !== 0) {
 760              $formula = $this->updateColumnRangesAllWorksheets($formula, $numberOfColumns);
 761          }
 762  
 763          if ($numberOfRows !== 0) {
 764              $formula = $this->updateRowRangesAllWorksheets($formula, $numberOfRows);
 765          }
 766  
 767          return $formula;
 768      }
 769  
 770      private function updateCellReferencesAllWorksheets(string $formula, int $numberOfColumns, int $numberOfRows): string
 771      {
 772          $splitCount = preg_match_all(
 773              '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
 774              $formula,
 775              $splitRanges,
 776              PREG_OFFSET_CAPTURE
 777          );
 778  
 779          $columnLengths = array_map('strlen', array_column($splitRanges[6], 0));
 780          $rowLengths = array_map('strlen', array_column($splitRanges[7], 0));
 781          $columnOffsets = array_column($splitRanges[6], 1);
 782          $rowOffsets = array_column($splitRanges[7], 1);
 783  
 784          $columns = $splitRanges[6];
 785          $rows = $splitRanges[7];
 786  
 787          while ($splitCount > 0) {
 788              --$splitCount;
 789              $columnLength = $columnLengths[$splitCount];
 790              $rowLength = $rowLengths[$splitCount];
 791              $columnOffset = $columnOffsets[$splitCount];
 792              $rowOffset = $rowOffsets[$splitCount];
 793              $column = $columns[$splitCount][0];
 794              $row = $rows[$splitCount][0];
 795  
 796              if (!empty($column) && $column[0] !== '$') {
 797                  $column = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($column) + $numberOfColumns);
 798                  $formula = substr($formula, 0, $columnOffset) . $column . substr($formula, $columnOffset + $columnLength);
 799              }
 800              if (!empty($row) && $row[0] !== '$') {
 801                  $row += $numberOfRows;
 802                  $formula = substr($formula, 0, $rowOffset) . $row . substr($formula, $rowOffset + $rowLength);
 803              }
 804          }
 805  
 806          return $formula;
 807      }
 808  
 809      private function updateColumnRangesAllWorksheets(string $formula, int $numberOfColumns): string
 810      {
 811          $splitCount = preg_match_all(
 812              '/' . Calculation::CALCULATION_REGEXP_COLUMNRANGE_RELATIVE . '/mui',
 813              $formula,
 814              $splitRanges,
 815              PREG_OFFSET_CAPTURE
 816          );
 817  
 818          $fromColumnLengths = array_map('strlen', array_column($splitRanges[1], 0));
 819          $fromColumnOffsets = array_column($splitRanges[1], 1);
 820          $toColumnLengths = array_map('strlen', array_column($splitRanges[2], 0));
 821          $toColumnOffsets = array_column($splitRanges[2], 1);
 822  
 823          $fromColumns = $splitRanges[1];
 824          $toColumns = $splitRanges[2];
 825  
 826          while ($splitCount > 0) {
 827              --$splitCount;
 828              $fromColumnLength = $fromColumnLengths[$splitCount];
 829              $toColumnLength = $toColumnLengths[$splitCount];
 830              $fromColumnOffset = $fromColumnOffsets[$splitCount];
 831              $toColumnOffset = $toColumnOffsets[$splitCount];
 832              $fromColumn = $fromColumns[$splitCount][0];
 833              $toColumn = $toColumns[$splitCount][0];
 834  
 835              if (!empty($fromColumn) && $fromColumn[0] !== '$') {
 836                  $fromColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($fromColumn) + $numberOfColumns);
 837                  $formula = substr($formula, 0, $fromColumnOffset) . $fromColumn . substr($formula, $fromColumnOffset + $fromColumnLength);
 838              }
 839              if (!empty($toColumn) && $toColumn[0] !== '$') {
 840                  $toColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($toColumn) + $numberOfColumns);
 841                  $formula = substr($formula, 0, $toColumnOffset) . $toColumn . substr($formula, $toColumnOffset + $toColumnLength);
 842              }
 843          }
 844  
 845          return $formula;
 846      }
 847  
 848      private function updateRowRangesAllWorksheets(string $formula, int $numberOfRows): string
 849      {
 850          $splitCount = preg_match_all(
 851              '/' . Calculation::CALCULATION_REGEXP_ROWRANGE_RELATIVE . '/mui',
 852              $formula,
 853              $splitRanges,
 854              PREG_OFFSET_CAPTURE
 855          );
 856  
 857          $fromRowLengths = array_map('strlen', array_column($splitRanges[1], 0));
 858          $fromRowOffsets = array_column($splitRanges[1], 1);
 859          $toRowLengths = array_map('strlen', array_column($splitRanges[2], 0));
 860          $toRowOffsets = array_column($splitRanges[2], 1);
 861  
 862          $fromRows = $splitRanges[1];
 863          $toRows = $splitRanges[2];
 864  
 865          while ($splitCount > 0) {
 866              --$splitCount;
 867              $fromRowLength = $fromRowLengths[$splitCount];
 868              $toRowLength = $toRowLengths[$splitCount];
 869              $fromRowOffset = $fromRowOffsets[$splitCount];
 870              $toRowOffset = $toRowOffsets[$splitCount];
 871              $fromRow = $fromRows[$splitCount][0];
 872              $toRow = $toRows[$splitCount][0];
 873  
 874              if (!empty($fromRow) && $fromRow[0] !== '$') {
 875                  $fromRow += $numberOfRows;
 876                  $formula = substr($formula, 0, $fromRowOffset) . $fromRow . substr($formula, $fromRowOffset + $fromRowLength);
 877              }
 878              if (!empty($toRow) && $toRow[0] !== '$') {
 879                  $toRow += $numberOfRows;
 880                  $formula = substr($formula, 0, $toRowOffset) . $toRow . substr($formula, $toRowOffset + $toRowLength);
 881              }
 882          }
 883  
 884          return $formula;
 885      }
 886  
 887      /**
 888       * Update cell reference.
 889       *
 890       * @param string $cellReference Cell address or range of addresses
 891       * @param string $beforeCellAddress Insert before this one
 892       * @param int $numberOfColumns Number of columns to increment
 893       * @param int $numberOfRows Number of rows to increment
 894       *
 895       * @return string Updated cell range
 896       */
 897      public function updateCellReference($cellReference = 'A1', $beforeCellAddress = 'A1', $numberOfColumns = 0, $numberOfRows = 0)
 898      {
 899          // Is it in another worksheet? Will not have to update anything.
 900          if (strpos($cellReference, '!') !== false) {
 901              return $cellReference;
 902          // Is it a range or a single cell?
 903          } elseif (!Coordinate::coordinateIsRange($cellReference)) {
 904              // Single cell
 905              return $this->updateSingleCellReference($cellReference, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 906          } elseif (Coordinate::coordinateIsRange($cellReference)) {
 907              // Range
 908              return $this->updateCellRange($cellReference, $beforeCellAddress, $numberOfColumns, $numberOfRows);
 909          }
 910  
 911          // Return original
 912          return $cellReference;
 913      }
 914  
 915      /**
 916       * Update named formulas (i.e. containing worksheet references / named ranges).
 917       *
 918       * @param Spreadsheet $spreadsheet Object to update
 919       * @param string $oldName Old name (name to replace)
 920       * @param string $newName New name
 921       */
 922      public function updateNamedFormulas(Spreadsheet $spreadsheet, $oldName = '', $newName = ''): void
 923      {
 924          if ($oldName == '') {
 925              return;
 926          }
 927  
 928          foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
 929              foreach ($sheet->getCoordinates(false) as $coordinate) {
 930                  $cell = $sheet->getCell($coordinate);
 931                  if (($cell !== null) && ($cell->getDataType() == DataType::TYPE_FORMULA)) {
 932                      $formula = $cell->getValue();
 933                      if (strpos($formula, $oldName) !== false) {
 934                          $formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
 935                          $formula = str_replace($oldName . '!', $newName . '!', $formula);
 936                          $cell->setValueExplicit($formula, DataType::TYPE_FORMULA);
 937                      }
 938                  }
 939              }
 940          }
 941      }
 942  
 943      /**
 944       * Update cell range.
 945       *
 946       * @param string $cellRange Cell range    (e.g. 'B2:D4', 'B:C' or '2:3')
 947       * @param string $beforeCellAddress Insert before this one
 948       * @param int $numberOfColumns Number of columns to increment
 949       * @param int $numberOfRows Number of rows to increment
 950       *
 951       * @return string Updated cell range
 952       */
 953      private function updateCellRange($cellRange = 'A1:A1', $beforeCellAddress = 'A1', $numberOfColumns = 0, $numberOfRows = 0)
 954      {
 955          if (!Coordinate::coordinateIsRange($cellRange)) {
 956              throw new Exception('Only cell ranges may be passed to this method.');
 957          }
 958  
 959          // Update range
 960          $range = Coordinate::splitRange($cellRange);
 961          $ic = count($range);
 962          for ($i = 0; $i < $ic; ++$i) {
 963              $jc = count($range[$i]);
 964              for ($j = 0; $j < $jc; ++$j) {
 965                  if (ctype_alpha($range[$i][$j])) {
 966                      $r = Coordinate::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $beforeCellAddress, $numberOfColumns, $numberOfRows));
 967                      $range[$i][$j] = $r[0];
 968                  } elseif (ctype_digit($range[$i][$j])) {
 969                      $r = Coordinate::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $beforeCellAddress, $numberOfColumns, $numberOfRows));
 970                      $range[$i][$j] = $r[1];
 971                  } else {
 972                      $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $beforeCellAddress, $numberOfColumns, $numberOfRows);
 973                  }
 974              }
 975          }
 976  
 977          // Recreate range string
 978          return Coordinate::buildRange($range);
 979      }
 980  
 981      /**
 982       * Update single cell reference.
 983       *
 984       * @param string $cellReference Single cell reference
 985       * @param string $beforeCellAddress Insert before this one
 986       * @param int $numberOfColumns Number of columns to increment
 987       * @param int $numberOfRows Number of rows to increment
 988       *
 989       * @return string Updated cell reference
 990       */
 991      private function updateSingleCellReference($cellReference = 'A1', $beforeCellAddress = 'A1', $numberOfColumns = 0, $numberOfRows = 0)
 992      {
 993          if (Coordinate::coordinateIsRange($cellReference)) {
 994              throw new Exception('Only single cell references may be passed to this method.');
 995          }
 996  
 997          // Get coordinate of $beforeCellAddress
 998          [$beforeColumn, $beforeRow] = Coordinate::coordinateFromString($beforeCellAddress);
 999  
1000          // Get coordinate of $cellReference
1001          [$newColumn, $newRow] = Coordinate::coordinateFromString($cellReference);
1002  
1003          // Verify which parts should be updated
1004          $updateColumn = (($newColumn[0] != '$') && ($beforeColumn[0] != '$') && (Coordinate::columnIndexFromString($newColumn) >= Coordinate::columnIndexFromString($beforeColumn)));
1005          $updateRow = (($newRow[0] != '$') && ($beforeRow[0] != '$') && $newRow >= $beforeRow);
1006  
1007          // Create new column reference
1008          if ($updateColumn) {
1009              $newColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($newColumn) + $numberOfColumns);
1010          }
1011  
1012          // Create new row reference
1013          if ($updateRow) {
1014              $newRow = (int) $newRow + $numberOfRows;
1015          }
1016  
1017          // Return new reference
1018          return $newColumn . $newRow;
1019      }
1020  
1021      /**
1022       * __clone implementation. Cloning should not be allowed in a Singleton!
1023       */
1024      final public function __clone()
1025      {
1026          throw new Exception('Cloning a Singleton is not allowed!');
1027      }
1028  }