[Summary view]

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

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