[Summary view]

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

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