[Summary view]

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