[Summary view]

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