[Summary view]

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

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