[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Worksheet;
   4  
   5  use ArrayObject;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
   9  use PhpOffice\PhpSpreadsheet\Cell\Cell;
  10  use PhpOffice\PhpSpreadsheet\Cell\CellAddress;
  11  use PhpOffice\PhpSpreadsheet\Cell\CellRange;
  12  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  13  use PhpOffice\PhpSpreadsheet\Cell\DataType;
  14  use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
  15  use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
  16  use PhpOffice\PhpSpreadsheet\Chart\Chart;
  17  use PhpOffice\PhpSpreadsheet\Collection\Cells;
  18  use PhpOffice\PhpSpreadsheet\Collection\CellsFactory;
  19  use PhpOffice\PhpSpreadsheet\Comment;
  20  use PhpOffice\PhpSpreadsheet\DefinedName;
  21  use PhpOffice\PhpSpreadsheet\Exception;
  22  use PhpOffice\PhpSpreadsheet\IComparable;
  23  use PhpOffice\PhpSpreadsheet\ReferenceHelper;
  24  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  25  use PhpOffice\PhpSpreadsheet\Shared;
  26  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  27  use PhpOffice\PhpSpreadsheet\Style\Color;
  28  use PhpOffice\PhpSpreadsheet\Style\Conditional;
  29  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  30  use PhpOffice\PhpSpreadsheet\Style\Style;
  31  
  32  class Worksheet implements IComparable
  33  {
  34      // Break types
  35      public const BREAK_NONE = 0;
  36      public const BREAK_ROW = 1;
  37      public const BREAK_COLUMN = 2;
  38  
  39      // Sheet state
  40      public const SHEETSTATE_VISIBLE = 'visible';
  41      public const SHEETSTATE_HIDDEN = 'hidden';
  42      public const SHEETSTATE_VERYHIDDEN = 'veryHidden';
  43  
  44      public const MERGE_CELL_CONTENT_EMPTY = 'empty';
  45      public const MERGE_CELL_CONTENT_HIDE = 'hide';
  46      public const MERGE_CELL_CONTENT_MERGE = 'merge';
  47  
  48      protected const SHEET_NAME_REQUIRES_NO_QUOTES = '/^[_\p{L}][_\p{L}\p{N}]*$/mui';
  49  
  50      /**
  51       * Maximum 31 characters allowed for sheet title.
  52       *
  53       * @var int
  54       */
  55      const SHEET_TITLE_MAXIMUM_LENGTH = 31;
  56  
  57      /**
  58       * Invalid characters in sheet title.
  59       *
  60       * @var array
  61       */
  62      private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']'];
  63  
  64      /**
  65       * Parent spreadsheet.
  66       *
  67       * @var Spreadsheet
  68       */
  69      private $parent;
  70  
  71      /**
  72       * Collection of cells.
  73       *
  74       * @var Cells
  75       */
  76      private $cellCollection;
  77  
  78      /**
  79       * Collection of row dimensions.
  80       *
  81       * @var RowDimension[]
  82       */
  83      private $rowDimensions = [];
  84  
  85      /**
  86       * Default row dimension.
  87       *
  88       * @var RowDimension
  89       */
  90      private $defaultRowDimension;
  91  
  92      /**
  93       * Collection of column dimensions.
  94       *
  95       * @var ColumnDimension[]
  96       */
  97      private $columnDimensions = [];
  98  
  99      /**
 100       * Default column dimension.
 101       *
 102       * @var ColumnDimension
 103       */
 104      private $defaultColumnDimension;
 105  
 106      /**
 107       * Collection of drawings.
 108       *
 109       * @var ArrayObject<int, BaseDrawing>
 110       */
 111      private $drawingCollection;
 112  
 113      /**
 114       * Collection of Chart objects.
 115       *
 116       * @var ArrayObject<int, Chart>
 117       */
 118      private $chartCollection;
 119  
 120      /**
 121       * Collection of Table objects.
 122       *
 123       * @var ArrayObject<int, Table>
 124       */
 125      private $tableCollection;
 126  
 127      /**
 128       * Worksheet title.
 129       *
 130       * @var string
 131       */
 132      private $title;
 133  
 134      /**
 135       * Sheet state.
 136       *
 137       * @var string
 138       */
 139      private $sheetState;
 140  
 141      /**
 142       * Page setup.
 143       *
 144       * @var PageSetup
 145       */
 146      private $pageSetup;
 147  
 148      /**
 149       * Page margins.
 150       *
 151       * @var PageMargins
 152       */
 153      private $pageMargins;
 154  
 155      /**
 156       * Page header/footer.
 157       *
 158       * @var HeaderFooter
 159       */
 160      private $headerFooter;
 161  
 162      /**
 163       * Sheet view.
 164       *
 165       * @var SheetView
 166       */
 167      private $sheetView;
 168  
 169      /**
 170       * Protection.
 171       *
 172       * @var Protection
 173       */
 174      private $protection;
 175  
 176      /**
 177       * Collection of styles.
 178       *
 179       * @var Style[]
 180       */
 181      private $styles = [];
 182  
 183      /**
 184       * Conditional styles. Indexed by cell coordinate, e.g. 'A1'.
 185       *
 186       * @var array
 187       */
 188      private $conditionalStylesCollection = [];
 189  
 190      /**
 191       * Collection of breaks.
 192       *
 193       * @var int[]
 194       */
 195      private $breaks = [];
 196  
 197      /**
 198       * Collection of merged cell ranges.
 199       *
 200       * @var string[]
 201       */
 202      private $mergeCells = [];
 203  
 204      /**
 205       * Collection of protected cell ranges.
 206       *
 207       * @var string[]
 208       */
 209      private $protectedCells = [];
 210  
 211      /**
 212       * Autofilter Range and selection.
 213       *
 214       * @var AutoFilter
 215       */
 216      private $autoFilter;
 217  
 218      /**
 219       * Freeze pane.
 220       *
 221       * @var null|string
 222       */
 223      private $freezePane;
 224  
 225      /**
 226       * Default position of the right bottom pane.
 227       *
 228       * @var null|string
 229       */
 230      private $topLeftCell;
 231  
 232      /**
 233       * Show gridlines?
 234       *
 235       * @var bool
 236       */
 237      private $showGridlines = true;
 238  
 239      /**
 240       * Print gridlines?
 241       *
 242       * @var bool
 243       */
 244      private $printGridlines = false;
 245  
 246      /**
 247       * Show row and column headers?
 248       *
 249       * @var bool
 250       */
 251      private $showRowColHeaders = true;
 252  
 253      /**
 254       * Show summary below? (Row/Column outline).
 255       *
 256       * @var bool
 257       */
 258      private $showSummaryBelow = true;
 259  
 260      /**
 261       * Show summary right? (Row/Column outline).
 262       *
 263       * @var bool
 264       */
 265      private $showSummaryRight = true;
 266  
 267      /**
 268       * Collection of comments.
 269       *
 270       * @var Comment[]
 271       */
 272      private $comments = [];
 273  
 274      /**
 275       * Active cell. (Only one!).
 276       *
 277       * @var string
 278       */
 279      private $activeCell = 'A1';
 280  
 281      /**
 282       * Selected cells.
 283       *
 284       * @var string
 285       */
 286      private $selectedCells = 'A1';
 287  
 288      /**
 289       * Cached highest column.
 290       *
 291       * @var int
 292       */
 293      private $cachedHighestColumn = 1;
 294  
 295      /**
 296       * Cached highest row.
 297       *
 298       * @var int
 299       */
 300      private $cachedHighestRow = 1;
 301  
 302      /**
 303       * Right-to-left?
 304       *
 305       * @var bool
 306       */
 307      private $rightToLeft = false;
 308  
 309      /**
 310       * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'.
 311       *
 312       * @var array
 313       */
 314      private $hyperlinkCollection = [];
 315  
 316      /**
 317       * Data validation objects. Indexed by cell coordinate, e.g. 'A1'.
 318       *
 319       * @var array
 320       */
 321      private $dataValidationCollection = [];
 322  
 323      /**
 324       * Tab color.
 325       *
 326       * @var null|Color
 327       */
 328      private $tabColor;
 329  
 330      /**
 331       * Dirty flag.
 332       *
 333       * @var bool
 334       */
 335      private $dirty = true;
 336  
 337      /**
 338       * Hash.
 339       *
 340       * @var string
 341       */
 342      private $hash;
 343  
 344      /**
 345       * CodeName.
 346       *
 347       * @var string
 348       */
 349      private $codeName;
 350  
 351      /**
 352       * Create a new worksheet.
 353       *
 354       * @param string $title
 355       */
 356      public function __construct(?Spreadsheet $parent = null, $title = 'Worksheet')
 357      {
 358          // Set parent and title
 359          $this->parent = $parent;
 360          $this->setTitle($title, false);
 361          // setTitle can change $pTitle
 362          $this->setCodeName($this->getTitle());
 363          $this->setSheetState(self::SHEETSTATE_VISIBLE);
 364  
 365          $this->cellCollection = CellsFactory::getInstance($this);
 366          // Set page setup
 367          $this->pageSetup = new PageSetup();
 368          // Set page margins
 369          $this->pageMargins = new PageMargins();
 370          // Set page header/footer
 371          $this->headerFooter = new HeaderFooter();
 372          // Set sheet view
 373          $this->sheetView = new SheetView();
 374          // Drawing collection
 375          $this->drawingCollection = new ArrayObject();
 376          // Chart collection
 377          $this->chartCollection = new ArrayObject();
 378          // Protection
 379          $this->protection = new Protection();
 380          // Default row dimension
 381          $this->defaultRowDimension = new RowDimension(null);
 382          // Default column dimension
 383          $this->defaultColumnDimension = new ColumnDimension(null);
 384          // AutoFilter
 385          $this->autoFilter = new AutoFilter('', $this);
 386          // Table collection
 387          $this->tableCollection = new ArrayObject();
 388      }
 389  
 390      /**
 391       * Disconnect all cells from this Worksheet object,
 392       * typically so that the worksheet object can be unset.
 393       */
 394      public function disconnectCells(): void
 395      {
 396          if ($this->cellCollection !== null) {
 397              $this->cellCollection->unsetWorksheetCells();
 398              // @phpstan-ignore-next-line
 399              $this->cellCollection = null;
 400          }
 401          //    detach ourself from the workbook, so that it can then delete this worksheet successfully
 402          // @phpstan-ignore-next-line
 403          $this->parent = null;
 404      }
 405  
 406      /**
 407       * Code to execute when this worksheet is unset().
 408       */
 409      public function __destruct()
 410      {
 411          Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title);
 412  
 413          $this->disconnectCells();
 414          $this->rowDimensions = [];
 415      }
 416  
 417      /**
 418       * Return the cell collection.
 419       *
 420       * @return Cells
 421       */
 422      public function getCellCollection()
 423      {
 424          return $this->cellCollection;
 425      }
 426  
 427      /**
 428       * Get array of invalid characters for sheet title.
 429       *
 430       * @return array
 431       */
 432      public static function getInvalidCharacters()
 433      {
 434          return self::$invalidCharacters;
 435      }
 436  
 437      /**
 438       * Check sheet code name for valid Excel syntax.
 439       *
 440       * @param string $sheetCodeName The string to check
 441       *
 442       * @return string The valid string
 443       */
 444      private static function checkSheetCodeName($sheetCodeName)
 445      {
 446          $charCount = Shared\StringHelper::countCharacters($sheetCodeName);
 447          if ($charCount == 0) {
 448              throw new Exception('Sheet code name cannot be empty.');
 449          }
 450          // Some of the printable ASCII characters are invalid:  * : / \ ? [ ] and  first and last characters cannot be a "'"
 451          if (
 452              (str_replace(self::$invalidCharacters, '', $sheetCodeName) !== $sheetCodeName) ||
 453              (Shared\StringHelper::substring($sheetCodeName, -1, 1) == '\'') ||
 454              (Shared\StringHelper::substring($sheetCodeName, 0, 1) == '\'')
 455          ) {
 456              throw new Exception('Invalid character found in sheet code name');
 457          }
 458  
 459          // Enforce maximum characters allowed for sheet title
 460          if ($charCount > self::SHEET_TITLE_MAXIMUM_LENGTH) {
 461              throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.');
 462          }
 463  
 464          return $sheetCodeName;
 465      }
 466  
 467      /**
 468       * Check sheet title for valid Excel syntax.
 469       *
 470       * @param string $sheetTitle The string to check
 471       *
 472       * @return string The valid string
 473       */
 474      private static function checkSheetTitle($sheetTitle)
 475      {
 476          // Some of the printable ASCII characters are invalid:  * : / \ ? [ ]
 477          if (str_replace(self::$invalidCharacters, '', $sheetTitle) !== $sheetTitle) {
 478              throw new Exception('Invalid character found in sheet title');
 479          }
 480  
 481          // Enforce maximum characters allowed for sheet title
 482          if (Shared\StringHelper::countCharacters($sheetTitle) > self::SHEET_TITLE_MAXIMUM_LENGTH) {
 483              throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.');
 484          }
 485  
 486          return $sheetTitle;
 487      }
 488  
 489      /**
 490       * Get a sorted list of all cell coordinates currently held in the collection by row and column.
 491       *
 492       * @param bool $sorted Also sort the cell collection?
 493       *
 494       * @return string[]
 495       */
 496      public function getCoordinates($sorted = true)
 497      {
 498          if ($this->cellCollection == null) {
 499              return [];
 500          }
 501  
 502          if ($sorted) {
 503              return $this->cellCollection->getSortedCoordinates();
 504          }
 505  
 506          return $this->cellCollection->getCoordinates();
 507      }
 508  
 509      /**
 510       * Get collection of row dimensions.
 511       *
 512       * @return RowDimension[]
 513       */
 514      public function getRowDimensions()
 515      {
 516          return $this->rowDimensions;
 517      }
 518  
 519      /**
 520       * Get default row dimension.
 521       *
 522       * @return RowDimension
 523       */
 524      public function getDefaultRowDimension()
 525      {
 526          return $this->defaultRowDimension;
 527      }
 528  
 529      /**
 530       * Get collection of column dimensions.
 531       *
 532       * @return ColumnDimension[]
 533       */
 534      public function getColumnDimensions()
 535      {
 536          return $this->columnDimensions;
 537      }
 538  
 539      /**
 540       * Get default column dimension.
 541       *
 542       * @return ColumnDimension
 543       */
 544      public function getDefaultColumnDimension()
 545      {
 546          return $this->defaultColumnDimension;
 547      }
 548  
 549      /**
 550       * Get collection of drawings.
 551       *
 552       * @return ArrayObject<int, BaseDrawing>
 553       */
 554      public function getDrawingCollection()
 555      {
 556          return $this->drawingCollection;
 557      }
 558  
 559      /**
 560       * Get collection of charts.
 561       *
 562       * @return ArrayObject<int, Chart>
 563       */
 564      public function getChartCollection()
 565      {
 566          return $this->chartCollection;
 567      }
 568  
 569      /**
 570       * Add chart.
 571       *
 572       * @param null|int $chartIndex Index where chart should go (0,1,..., or null for last)
 573       *
 574       * @return Chart
 575       */
 576      public function addChart(Chart $chart, $chartIndex = null)
 577      {
 578          $chart->setWorksheet($this);
 579          if ($chartIndex === null) {
 580              $this->chartCollection[] = $chart;
 581          } else {
 582              // Insert the chart at the requested index
 583              // @phpstan-ignore-next-line
 584              array_splice($this->chartCollection, $chartIndex, 0, [$chart]);
 585          }
 586  
 587          return $chart;
 588      }
 589  
 590      /**
 591       * Return the count of charts on this worksheet.
 592       *
 593       * @return int The number of charts
 594       */
 595      public function getChartCount()
 596      {
 597          return count($this->chartCollection);
 598      }
 599  
 600      /**
 601       * Get a chart by its index position.
 602       *
 603       * @param string $index Chart index position
 604       *
 605       * @return Chart|false
 606       */
 607      public function getChartByIndex($index)
 608      {
 609          $chartCount = count($this->chartCollection);
 610          if ($chartCount == 0) {
 611              return false;
 612          }
 613          if ($index === null) {
 614              $index = --$chartCount;
 615          }
 616          if (!isset($this->chartCollection[$index])) {
 617              return false;
 618          }
 619  
 620          return $this->chartCollection[$index];
 621      }
 622  
 623      /**
 624       * Return an array of the names of charts on this worksheet.
 625       *
 626       * @return string[] The names of charts
 627       */
 628      public function getChartNames()
 629      {
 630          $chartNames = [];
 631          foreach ($this->chartCollection as $chart) {
 632              $chartNames[] = $chart->getName();
 633          }
 634  
 635          return $chartNames;
 636      }
 637  
 638      /**
 639       * Get a chart by name.
 640       *
 641       * @param string $chartName Chart name
 642       *
 643       * @return Chart|false
 644       */
 645      public function getChartByName($chartName)
 646      {
 647          $chartCount = count($this->chartCollection);
 648          if ($chartCount == 0) {
 649              return false;
 650          }
 651          foreach ($this->chartCollection as $index => $chart) {
 652              if ($chart->getName() == $chartName) {
 653                  return $this->chartCollection[$index];
 654              }
 655          }
 656  
 657          return false;
 658      }
 659  
 660      /**
 661       * Refresh column dimensions.
 662       *
 663       * @return $this
 664       */
 665      public function refreshColumnDimensions()
 666      {
 667          $newColumnDimensions = [];
 668          foreach ($this->getColumnDimensions() as $objColumnDimension) {
 669              $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
 670          }
 671  
 672          $this->columnDimensions = $newColumnDimensions;
 673  
 674          return $this;
 675      }
 676  
 677      /**
 678       * Refresh row dimensions.
 679       *
 680       * @return $this
 681       */
 682      public function refreshRowDimensions()
 683      {
 684          $newRowDimensions = [];
 685          foreach ($this->getRowDimensions() as $objRowDimension) {
 686              $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
 687          }
 688  
 689          $this->rowDimensions = $newRowDimensions;
 690  
 691          return $this;
 692      }
 693  
 694      /**
 695       * Calculate worksheet dimension.
 696       *
 697       * @return string String containing the dimension of this worksheet
 698       */
 699      public function calculateWorksheetDimension()
 700      {
 701          // Return
 702          return 'A1:' . $this->getHighestColumn() . $this->getHighestRow();
 703      }
 704  
 705      /**
 706       * Calculate worksheet data dimension.
 707       *
 708       * @return string String containing the dimension of this worksheet that actually contain data
 709       */
 710      public function calculateWorksheetDataDimension()
 711      {
 712          // Return
 713          return 'A1:' . $this->getHighestDataColumn() . $this->getHighestDataRow();
 714      }
 715  
 716      /**
 717       * Calculate widths for auto-size columns.
 718       *
 719       * @return $this
 720       */
 721      public function calculateColumnWidths()
 722      {
 723          // initialize $autoSizes array
 724          $autoSizes = [];
 725          foreach ($this->getColumnDimensions() as $colDimension) {
 726              if ($colDimension->getAutoSize()) {
 727                  $autoSizes[$colDimension->getColumnIndex()] = -1;
 728              }
 729          }
 730  
 731          // There is only something to do if there are some auto-size columns
 732          if (!empty($autoSizes)) {
 733              // build list of cells references that participate in a merge
 734              $isMergeCell = [];
 735              foreach ($this->getMergeCells() as $cells) {
 736                  foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) {
 737                      $isMergeCell[$cellReference] = true;
 738                  }
 739              }
 740  
 741              $autoFilterRange = $autoFilterFirstRowRange = $this->autoFilter->getRange();
 742              if (!empty($autoFilterRange)) {
 743                  $autoFilterRangeBoundaries = Coordinate::rangeBoundaries($autoFilterRange);
 744                  $autoFilterFirstRowRange = (string) new CellRange(
 745                      CellAddress::fromColumnAndRow($autoFilterRangeBoundaries[0][0], $autoFilterRangeBoundaries[0][1]),
 746                      CellAddress::fromColumnAndRow($autoFilterRangeBoundaries[1][0], $autoFilterRangeBoundaries[0][1])
 747                  );
 748              }
 749  
 750              // loop through all cells in the worksheet
 751              foreach ($this->getCoordinates(false) as $coordinate) {
 752                  $cell = $this->getCellOrNull($coordinate);
 753  
 754                  if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) {
 755                      //Determine if cell is in merge range
 756                      $isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]);
 757  
 758                      //By default merged cells should be ignored
 759                      $isMergedButProceed = false;
 760  
 761                      //The only exception is if it's a merge range value cell of a 'vertical' range (1 column wide)
 762                      if ($isMerged && $cell->isMergeRangeValueCell()) {
 763                          $range = $cell->getMergeRange();
 764                          $rangeBoundaries = Coordinate::rangeDimension($range);
 765                          if ($rangeBoundaries[0] === 1) {
 766                              $isMergedButProceed = true;
 767                          }
 768                      }
 769  
 770                      // Determine width if cell is not part of a merge or does and is a value cell of 1-column wide range
 771                      if (!$isMerged || $isMergedButProceed) {
 772                          // Determine if we need to make an adjustment for the first row in an AutoFilter range that
 773                          //    has a column filter dropdown
 774                          $filterAdjustment = false;
 775                          if (!empty($autoFilterRange) && $cell->isInRange($autoFilterFirstRowRange)) {
 776                              $filterAdjustment = true;
 777                          }
 778  
 779                          $indentAdjustment = $cell->getStyle()->getAlignment()->getIndent();
 780  
 781                          // Calculated value
 782                          // To formatted string
 783                          $cellValue = NumberFormat::toFormattedString(
 784                              $cell->getCalculatedValue(),
 785                              $this->getParent()->getCellXfByIndex($cell->getXfIndex())
 786                                  ->getNumberFormat()->getFormatCode()
 787                          );
 788  
 789                          if ($cellValue !== null && $cellValue !== '') {
 790                              $autoSizes[$this->cellCollection->getCurrentColumn()] = max(
 791                                  (float) $autoSizes[$this->cellCollection->getCurrentColumn()],
 792                                  (float) Shared\Font::calculateColumnWidth(
 793                                      $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
 794                                      $cellValue,
 795                                      $this->getParent()->getCellXfByIndex($cell->getXfIndex())
 796                                          ->getAlignment()->getTextRotation(),
 797                                      $this->getParent()->getDefaultStyle()->getFont(),
 798                                      $filterAdjustment,
 799                                      $indentAdjustment
 800                                  )
 801                              );
 802                          }
 803                      }
 804                  }
 805              }
 806  
 807              // adjust column widths
 808              foreach ($autoSizes as $columnIndex => $width) {
 809                  if ($width == -1) {
 810                      $width = $this->getDefaultColumnDimension()->getWidth();
 811                  }
 812                  $this->getColumnDimension($columnIndex)->setWidth($width);
 813              }
 814          }
 815  
 816          return $this;
 817      }
 818  
 819      /**
 820       * Get parent.
 821       *
 822       * @return Spreadsheet
 823       */
 824      public function getParent()
 825      {
 826          return $this->parent;
 827      }
 828  
 829      /**
 830       * Re-bind parent.
 831       *
 832       * @return $this
 833       */
 834      public function rebindParent(Spreadsheet $parent)
 835      {
 836          if ($this->parent !== null) {
 837              $definedNames = $this->parent->getDefinedNames();
 838              foreach ($definedNames as $definedName) {
 839                  $parent->addDefinedName($definedName);
 840              }
 841  
 842              $this->parent->removeSheetByIndex(
 843                  $this->parent->getIndex($this)
 844              );
 845          }
 846          $this->parent = $parent;
 847  
 848          return $this;
 849      }
 850  
 851      /**
 852       * Get title.
 853       *
 854       * @return string
 855       */
 856      public function getTitle()
 857      {
 858          return $this->title;
 859      }
 860  
 861      /**
 862       * Set title.
 863       *
 864       * @param string $title String containing the dimension of this worksheet
 865       * @param bool $updateFormulaCellReferences Flag indicating whether cell references in formulae should
 866       *            be updated to reflect the new sheet name.
 867       *          This should be left as the default true, unless you are
 868       *          certain that no formula cells on any worksheet contain
 869       *          references to this worksheet
 870       * @param bool $validate False to skip validation of new title. WARNING: This should only be set
 871       *                       at parse time (by Readers), where titles can be assumed to be valid.
 872       *
 873       * @return $this
 874       */
 875      public function setTitle($title, $updateFormulaCellReferences = true, $validate = true)
 876      {
 877          // Is this a 'rename' or not?
 878          if ($this->getTitle() == $title) {
 879              return $this;
 880          }
 881  
 882          // Old title
 883          $oldTitle = $this->getTitle();
 884  
 885          if ($validate) {
 886              // Syntax check
 887              self::checkSheetTitle($title);
 888  
 889              if ($this->parent) {
 890                  // Is there already such sheet name?
 891                  if ($this->parent->sheetNameExists($title)) {
 892                      // Use name, but append with lowest possible integer
 893  
 894                      if (Shared\StringHelper::countCharacters($title) > 29) {
 895                          $title = Shared\StringHelper::substring($title, 0, 29);
 896                      }
 897                      $i = 1;
 898                      while ($this->parent->sheetNameExists($title . ' ' . $i)) {
 899                          ++$i;
 900                          if ($i == 10) {
 901                              if (Shared\StringHelper::countCharacters($title) > 28) {
 902                                  $title = Shared\StringHelper::substring($title, 0, 28);
 903                              }
 904                          } elseif ($i == 100) {
 905                              if (Shared\StringHelper::countCharacters($title) > 27) {
 906                                  $title = Shared\StringHelper::substring($title, 0, 27);
 907                              }
 908                          }
 909                      }
 910  
 911                      $title .= " $i";
 912                  }
 913              }
 914          }
 915  
 916          // Set title
 917          $this->title = $title;
 918          $this->dirty = true;
 919  
 920          if ($this->parent && $this->parent->getCalculationEngine()) {
 921              // New title
 922              $newTitle = $this->getTitle();
 923              $this->parent->getCalculationEngine()
 924                  ->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
 925              if ($updateFormulaCellReferences) {
 926                  ReferenceHelper::getInstance()->updateNamedFormulae($this->parent, $oldTitle, $newTitle);
 927              }
 928          }
 929  
 930          return $this;
 931      }
 932  
 933      /**
 934       * Get sheet state.
 935       *
 936       * @return string Sheet state (visible, hidden, veryHidden)
 937       */
 938      public function getSheetState()
 939      {
 940          return $this->sheetState;
 941      }
 942  
 943      /**
 944       * Set sheet state.
 945       *
 946       * @param string $value Sheet state (visible, hidden, veryHidden)
 947       *
 948       * @return $this
 949       */
 950      public function setSheetState($value)
 951      {
 952          $this->sheetState = $value;
 953  
 954          return $this;
 955      }
 956  
 957      /**
 958       * Get page setup.
 959       *
 960       * @return PageSetup
 961       */
 962      public function getPageSetup()
 963      {
 964          return $this->pageSetup;
 965      }
 966  
 967      /**
 968       * Set page setup.
 969       *
 970       * @return $this
 971       */
 972      public function setPageSetup(PageSetup $pageSetup)
 973      {
 974          $this->pageSetup = $pageSetup;
 975  
 976          return $this;
 977      }
 978  
 979      /**
 980       * Get page margins.
 981       *
 982       * @return PageMargins
 983       */
 984      public function getPageMargins()
 985      {
 986          return $this->pageMargins;
 987      }
 988  
 989      /**
 990       * Set page margins.
 991       *
 992       * @return $this
 993       */
 994      public function setPageMargins(PageMargins $pageMargins)
 995      {
 996          $this->pageMargins = $pageMargins;
 997  
 998          return $this;
 999      }
1000  
1001      /**
1002       * Get page header/footer.
1003       *
1004       * @return HeaderFooter
1005       */
1006      public function getHeaderFooter()
1007      {
1008          return $this->headerFooter;
1009      }
1010  
1011      /**
1012       * Set page header/footer.
1013       *
1014       * @return $this
1015       */
1016      public function setHeaderFooter(HeaderFooter $headerFooter)
1017      {
1018          $this->headerFooter = $headerFooter;
1019  
1020          return $this;
1021      }
1022  
1023      /**
1024       * Get sheet view.
1025       *
1026       * @return SheetView
1027       */
1028      public function getSheetView()
1029      {
1030          return $this->sheetView;
1031      }
1032  
1033      /**
1034       * Set sheet view.
1035       *
1036       * @return $this
1037       */
1038      public function setSheetView(SheetView $sheetView)
1039      {
1040          $this->sheetView = $sheetView;
1041  
1042          return $this;
1043      }
1044  
1045      /**
1046       * Get Protection.
1047       *
1048       * @return Protection
1049       */
1050      public function getProtection()
1051      {
1052          return $this->protection;
1053      }
1054  
1055      /**
1056       * Set Protection.
1057       *
1058       * @return $this
1059       */
1060      public function setProtection(Protection $protection)
1061      {
1062          $this->protection = $protection;
1063          $this->dirty = true;
1064  
1065          return $this;
1066      }
1067  
1068      /**
1069       * Get highest worksheet column.
1070       *
1071       * @param null|int|string $row Return the data highest column for the specified row,
1072       *                                     or the highest column of any row if no row number is passed
1073       *
1074       * @return string Highest column name
1075       */
1076      public function getHighestColumn($row = null)
1077      {
1078          if ($row === null) {
1079              return Coordinate::stringFromColumnIndex($this->cachedHighestColumn);
1080          }
1081  
1082          return $this->getHighestDataColumn($row);
1083      }
1084  
1085      /**
1086       * Get highest worksheet column that contains data.
1087       *
1088       * @param null|int|string $row Return the highest data column for the specified row,
1089       *                                     or the highest data column of any row if no row number is passed
1090       *
1091       * @return string Highest column name that contains data
1092       */
1093      public function getHighestDataColumn($row = null)
1094      {
1095          return $this->cellCollection->getHighestColumn($row);
1096      }
1097  
1098      /**
1099       * Get highest worksheet row.
1100       *
1101       * @param null|string $column Return the highest data row for the specified column,
1102       *                                     or the highest row of any column if no column letter is passed
1103       *
1104       * @return int Highest row number
1105       */
1106      public function getHighestRow($column = null)
1107      {
1108          if ($column === null) {
1109              return $this->cachedHighestRow;
1110          }
1111  
1112          return $this->getHighestDataRow($column);
1113      }
1114  
1115      /**
1116       * Get highest worksheet row that contains data.
1117       *
1118       * @param null|string $column Return the highest data row for the specified column,
1119       *                                     or the highest data row of any column if no column letter is passed
1120       *
1121       * @return int Highest row number that contains data
1122       */
1123      public function getHighestDataRow($column = null)
1124      {
1125          return $this->cellCollection->getHighestRow($column);
1126      }
1127  
1128      /**
1129       * Get highest worksheet column and highest row that have cell records.
1130       *
1131       * @return array Highest column name and highest row number
1132       */
1133      public function getHighestRowAndColumn()
1134      {
1135          return $this->cellCollection->getHighestRowAndColumn();
1136      }
1137  
1138      /**
1139       * Set a cell value.
1140       *
1141       * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1142       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1143       * @param mixed $value Value for the cell
1144       *
1145       * @return $this
1146       */
1147      public function setCellValue($coordinate, $value)
1148      {
1149          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1150          $this->getCell($cellAddress)->setValue($value);
1151  
1152          return $this;
1153      }
1154  
1155      /**
1156       * Set a cell value by using numeric cell coordinates.
1157       *
1158       * @Deprecated 1.23.0
1159       *      Use the setCellValue() method with a cell address such as 'C5' instead;,
1160       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1161       *
1162       * @param int $columnIndex Numeric column coordinate of the cell
1163       * @param int $row Numeric row coordinate of the cell
1164       * @param mixed $value Value of the cell
1165       *
1166       * @return $this
1167       */
1168      public function setCellValueByColumnAndRow($columnIndex, $row, $value)
1169      {
1170          $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValue($value);
1171  
1172          return $this;
1173      }
1174  
1175      /**
1176       * Set a cell value.
1177       *
1178       * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1179       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1180       * @param mixed $value Value of the cell
1181       * @param string $dataType Explicit data type, see DataType::TYPE_*
1182       *        Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
1183       *             method, then it is your responsibility as an end-user developer to validate that the value and
1184       *             the datatype match.
1185       *       If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype
1186       *          that you specify.
1187       *
1188       * @return $this
1189       */
1190      public function setCellValueExplicit($coordinate, $value, $dataType)
1191      {
1192          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1193          $this->getCell($cellAddress)->setValueExplicit($value, $dataType);
1194  
1195          return $this;
1196      }
1197  
1198      /**
1199       * Set a cell value by using numeric cell coordinates.
1200       *
1201       * @Deprecated 1.23.0
1202       *      Use the setCellValueExplicit() method with a cell address such as 'C5' instead;,
1203       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1204       *
1205       * @param int $columnIndex Numeric column coordinate of the cell
1206       * @param int $row Numeric row coordinate of the cell
1207       * @param mixed $value Value of the cell
1208       * @param string $dataType Explicit data type, see DataType::TYPE_*
1209       *        Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
1210       *             method, then it is your responsibility as an end-user developer to validate that the value and
1211       *             the datatype match.
1212       *       If you do mismatch value and datatpe, then the value you enter may be changed to match the datatype
1213       *          that you specify.
1214       *
1215       * @return $this
1216       */
1217      public function setCellValueExplicitByColumnAndRow($columnIndex, $row, $value, $dataType)
1218      {
1219          $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row)->setValueExplicit($value, $dataType);
1220  
1221          return $this;
1222      }
1223  
1224      /**
1225       * Get cell at a specific coordinate.
1226       *
1227       * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1228       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1229       *
1230       * @return Cell Cell that was found or created
1231       */
1232      public function getCell($coordinate): Cell
1233      {
1234          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1235  
1236          // Shortcut for increased performance for the vast majority of simple cases
1237          if ($this->cellCollection->has($cellAddress)) {
1238              /** @var Cell $cell */
1239              $cell = $this->cellCollection->get($cellAddress);
1240  
1241              return $cell;
1242          }
1243  
1244          /** @var Worksheet $sheet */
1245          [$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress);
1246          $cell = $sheet->cellCollection->get($finalCoordinate);
1247  
1248          return $cell ?? $sheet->createNewCell($finalCoordinate);
1249      }
1250  
1251      /**
1252       * Get the correct Worksheet and coordinate from a coordinate that may
1253       * contains reference to another sheet or a named range.
1254       *
1255       * @return array{0: Worksheet, 1: string}
1256       */
1257      private function getWorksheetAndCoordinate(string $coordinate): array
1258      {
1259          $sheet = null;
1260          $finalCoordinate = null;
1261  
1262          // Worksheet reference?
1263          if (strpos($coordinate, '!') !== false) {
1264              $worksheetReference = self::extractSheetTitle($coordinate, true);
1265  
1266              $sheet = $this->parent->getSheetByName($worksheetReference[0]);
1267              $finalCoordinate = strtoupper($worksheetReference[1]);
1268  
1269              if ($sheet === null) {
1270                  throw new Exception('Sheet not found for name: ' . $worksheetReference[0]);
1271              }
1272          } elseif (
1273              !preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $coordinate) &&
1274              preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/iu', $coordinate)
1275          ) {
1276              // Named range?
1277              $namedRange = $this->validateNamedRange($coordinate, true);
1278              if ($namedRange !== null) {
1279                  $sheet = $namedRange->getWorksheet();
1280                  if ($sheet === null) {
1281                      throw new Exception('Sheet not found for named range: ' . $namedRange->getName());
1282                  }
1283  
1284                  /** @phpstan-ignore-next-line */
1285                  $cellCoordinate = ltrim(substr($namedRange->getValue(), strrpos($namedRange->getValue(), '!')), '!');
1286                  $finalCoordinate = str_replace('$', '', $cellCoordinate);
1287              }
1288          }
1289  
1290          if ($sheet === null || $finalCoordinate === null) {
1291              $sheet = $this;
1292              $finalCoordinate = strtoupper($coordinate);
1293          }
1294  
1295          if (Coordinate::coordinateIsRange($finalCoordinate)) {
1296              throw new Exception('Cell coordinate string can not be a range of cells.');
1297          } elseif (strpos($finalCoordinate, '$') !== false) {
1298              throw new Exception('Cell coordinate must not be absolute.');
1299          }
1300  
1301          return [$sheet, $finalCoordinate];
1302      }
1303  
1304      /**
1305       * Get an existing cell at a specific coordinate, or null.
1306       *
1307       * @param string $coordinate Coordinate of the cell, eg: 'A1'
1308       *
1309       * @return null|Cell Cell that was found or null
1310       */
1311      private function getCellOrNull($coordinate): ?Cell
1312      {
1313          // Check cell collection
1314          if ($this->cellCollection->has($coordinate)) {
1315              return $this->cellCollection->get($coordinate);
1316          }
1317  
1318          return null;
1319      }
1320  
1321      /**
1322       * Get cell at a specific coordinate by using numeric cell coordinates.
1323       *
1324       * @Deprecated 1.23.0
1325       *      Use the getCell() method with a cell address such as 'C5' instead;,
1326       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1327       *
1328       * @param int $columnIndex Numeric column coordinate of the cell
1329       * @param int $row Numeric row coordinate of the cell
1330       *
1331       * @return Cell Cell that was found/created or null
1332       */
1333      public function getCellByColumnAndRow($columnIndex, $row): Cell
1334      {
1335          return $this->getCell(Coordinate::stringFromColumnIndex($columnIndex) . $row);
1336      }
1337  
1338      /**
1339       * Create a new cell at the specified coordinate.
1340       *
1341       * @param string $coordinate Coordinate of the cell
1342       *
1343       * @return Cell Cell that was created
1344       */
1345      public function createNewCell($coordinate): Cell
1346      {
1347          [$column, $row, $columnString] = Coordinate::indexesFromString($coordinate);
1348          $cell = new Cell(null, DataType::TYPE_NULL, $this);
1349          $this->cellCollection->add($coordinate, $cell);
1350  
1351          // Coordinates
1352          if ($column > $this->cachedHighestColumn) {
1353              $this->cachedHighestColumn = $column;
1354          }
1355          if ($row > $this->cachedHighestRow) {
1356              $this->cachedHighestRow = $row;
1357          }
1358  
1359          // Cell needs appropriate xfIndex from dimensions records
1360          //    but don't create dimension records if they don't already exist
1361          $rowDimension = $this->rowDimensions[$row] ?? null;
1362          $columnDimension = $this->columnDimensions[$columnString] ?? null;
1363  
1364          if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) {
1365              // then there is a row dimension with explicit style, assign it to the cell
1366              $cell->setXfIndex(/** @scrutinizer ignore-type */ $rowDimension->getXfIndex());
1367          } elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) {
1368              // then there is a column dimension, assign it to the cell
1369              $cell->setXfIndex($columnDimension->getXfIndex());
1370          }
1371  
1372          return $cell;
1373      }
1374  
1375      /**
1376       * Does the cell at a specific coordinate exist?
1377       *
1378       * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1379       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1380       */
1381      public function cellExists($coordinate): bool
1382      {
1383          $cellAddress = Validations::validateCellAddress($coordinate);
1384          /** @var Worksheet $sheet */
1385          [$sheet, $finalCoordinate] = $this->getWorksheetAndCoordinate($cellAddress);
1386  
1387          return $sheet->cellCollection->has($finalCoordinate);
1388      }
1389  
1390      /**
1391       * Cell at a specific coordinate by using numeric cell coordinates exists?
1392       *
1393       * @Deprecated 1.23.0
1394       *      Use the cellExists() method with a cell address such as 'C5' instead;,
1395       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1396       *
1397       * @param int $columnIndex Numeric column coordinate of the cell
1398       * @param int $row Numeric row coordinate of the cell
1399       */
1400      public function cellExistsByColumnAndRow($columnIndex, $row): bool
1401      {
1402          return $this->cellExists(Coordinate::stringFromColumnIndex($columnIndex) . $row);
1403      }
1404  
1405      /**
1406       * Get row dimension at a specific row.
1407       *
1408       * @param int $row Numeric index of the row
1409       */
1410      public function getRowDimension(int $row): RowDimension
1411      {
1412          // Get row dimension
1413          if (!isset($this->rowDimensions[$row])) {
1414              $this->rowDimensions[$row] = new RowDimension($row);
1415  
1416              $this->cachedHighestRow = max($this->cachedHighestRow, $row);
1417          }
1418  
1419          return $this->rowDimensions[$row];
1420      }
1421  
1422      public function rowDimensionExists(int $row): bool
1423      {
1424          return isset($this->rowDimensions[$row]);
1425      }
1426  
1427      /**
1428       * Get column dimension at a specific column.
1429       *
1430       * @param string $column String index of the column eg: 'A'
1431       */
1432      public function getColumnDimension(string $column): ColumnDimension
1433      {
1434          // Uppercase coordinate
1435          $column = strtoupper($column);
1436  
1437          // Fetch dimensions
1438          if (!isset($this->columnDimensions[$column])) {
1439              $this->columnDimensions[$column] = new ColumnDimension($column);
1440  
1441              $columnIndex = Coordinate::columnIndexFromString($column);
1442              if ($this->cachedHighestColumn < $columnIndex) {
1443                  $this->cachedHighestColumn = $columnIndex;
1444              }
1445          }
1446  
1447          return $this->columnDimensions[$column];
1448      }
1449  
1450      /**
1451       * Get column dimension at a specific column by using numeric cell coordinates.
1452       *
1453       * @param int $columnIndex Numeric column coordinate of the cell
1454       */
1455      public function getColumnDimensionByColumn(int $columnIndex): ColumnDimension
1456      {
1457          return $this->getColumnDimension(Coordinate::stringFromColumnIndex($columnIndex));
1458      }
1459  
1460      /**
1461       * Get styles.
1462       *
1463       * @return Style[]
1464       */
1465      public function getStyles()
1466      {
1467          return $this->styles;
1468      }
1469  
1470      /**
1471       * Get style for cell.
1472       *
1473       * @param AddressRange|array<int>|CellAddress|int|string $cellCoordinate
1474       *              A simple string containing a cell address like 'A1' or a cell range like 'A1:E10'
1475       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1476       *              or a CellAddress or AddressRange object.
1477       */
1478      public function getStyle($cellCoordinate): Style
1479      {
1480          $cellCoordinate = Validations::validateCellOrCellRange($cellCoordinate);
1481  
1482          // set this sheet as active
1483          $this->parent->setActiveSheetIndex($this->parent->getIndex($this));
1484  
1485          // set cell coordinate as active
1486          $this->setSelectedCells($cellCoordinate);
1487  
1488          return $this->parent->getCellXfSupervisor();
1489      }
1490  
1491      /**
1492       * Get style for cell by using numeric cell coordinates.
1493       *
1494       * @Deprecated 1.23.0
1495       *      Use the getStyle() method with a cell address range such as 'C5:F8' instead;,
1496       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1497       *          or an AddressRange object.
1498       *
1499       * @param int $columnIndex1 Numeric column coordinate of the cell
1500       * @param int $row1 Numeric row coordinate of the cell
1501       * @param null|int $columnIndex2 Numeric column coordinate of the range cell
1502       * @param null|int $row2 Numeric row coordinate of the range cell
1503       *
1504       * @return Style
1505       */
1506      public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null)
1507      {
1508          if ($columnIndex2 !== null && $row2 !== null) {
1509              $cellRange = new CellRange(
1510                  CellAddress::fromColumnAndRow($columnIndex1, $row1),
1511                  CellAddress::fromColumnAndRow($columnIndex2, $row2)
1512              );
1513  
1514              return $this->getStyle($cellRange);
1515          }
1516  
1517          return $this->getStyle(CellAddress::fromColumnAndRow($columnIndex1, $row1));
1518      }
1519  
1520      /**
1521       * Get conditional styles for a cell.
1522       *
1523       * @param string $coordinate eg: 'A1' or 'A1:A3'.
1524       *          If a single cell is referenced, then the array of conditional styles will be returned if the cell is
1525       *               included in a conditional style range.
1526       *          If a range of cells is specified, then the styles will only be returned if the range matches the entire
1527       *               range of the conditional.
1528       *
1529       * @return Conditional[]
1530       */
1531      public function getConditionalStyles(string $coordinate): array
1532      {
1533          $coordinate = strtoupper($coordinate);
1534          if (strpos($coordinate, ':') !== false) {
1535              return $this->conditionalStylesCollection[$coordinate] ?? [];
1536          }
1537  
1538          $cell = $this->getCell($coordinate);
1539          foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1540              if ($cell->isInRange($conditionalRange)) {
1541                  return $this->conditionalStylesCollection[$conditionalRange];
1542              }
1543          }
1544  
1545          return [];
1546      }
1547  
1548      public function getConditionalRange(string $coordinate): ?string
1549      {
1550          $coordinate = strtoupper($coordinate);
1551          $cell = $this->getCell($coordinate);
1552          foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1553              if ($cell->isInRange($conditionalRange)) {
1554                  return $conditionalRange;
1555              }
1556          }
1557  
1558          return null;
1559      }
1560  
1561      /**
1562       * Do conditional styles exist for this cell?
1563       *
1564       * @param string $coordinate eg: 'A1' or 'A1:A3'.
1565       *          If a single cell is specified, then this method will return true if that cell is included in a
1566       *               conditional style range.
1567       *          If a range of cells is specified, then true will only be returned if the range matches the entire
1568       *               range of the conditional.
1569       */
1570      public function conditionalStylesExists($coordinate): bool
1571      {
1572          $coordinate = strtoupper($coordinate);
1573          if (strpos($coordinate, ':') !== false) {
1574              return isset($this->conditionalStylesCollection[$coordinate]);
1575          }
1576  
1577          $cell = $this->getCell($coordinate);
1578          foreach (array_keys($this->conditionalStylesCollection) as $conditionalRange) {
1579              if ($cell->isInRange($conditionalRange)) {
1580                  return true;
1581              }
1582          }
1583  
1584          return false;
1585      }
1586  
1587      /**
1588       * Removes conditional styles for a cell.
1589       *
1590       * @param string $coordinate eg: 'A1'
1591       *
1592       * @return $this
1593       */
1594      public function removeConditionalStyles($coordinate)
1595      {
1596          unset($this->conditionalStylesCollection[strtoupper($coordinate)]);
1597  
1598          return $this;
1599      }
1600  
1601      /**
1602       * Get collection of conditional styles.
1603       *
1604       * @return array
1605       */
1606      public function getConditionalStylesCollection()
1607      {
1608          return $this->conditionalStylesCollection;
1609      }
1610  
1611      /**
1612       * Set conditional styles.
1613       *
1614       * @param string $coordinate eg: 'A1'
1615       * @param Conditional[] $styles
1616       *
1617       * @return $this
1618       */
1619      public function setConditionalStyles($coordinate, $styles)
1620      {
1621          $this->conditionalStylesCollection[strtoupper($coordinate)] = $styles;
1622  
1623          return $this;
1624      }
1625  
1626      /**
1627       * Duplicate cell style to a range of cells.
1628       *
1629       * Please note that this will overwrite existing cell styles for cells in range!
1630       *
1631       * @param Style $style Cell style to duplicate
1632       * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1633       *
1634       * @return $this
1635       */
1636      public function duplicateStyle(Style $style, $range)
1637      {
1638          // Add the style to the workbook if necessary
1639          $workbook = $this->parent;
1640          if ($existingStyle = $this->parent->getCellXfByHashCode($style->getHashCode())) {
1641              // there is already such cell Xf in our collection
1642              $xfIndex = $existingStyle->getIndex();
1643          } else {
1644              // we don't have such a cell Xf, need to add
1645              $workbook->addCellXf($style);
1646              $xfIndex = $style->getIndex();
1647          }
1648  
1649          // Calculate range outer borders
1650          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);
1651  
1652          // Make sure we can loop upwards on rows and columns
1653          if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1654              $tmp = $rangeStart;
1655              $rangeStart = $rangeEnd;
1656              $rangeEnd = $tmp;
1657          }
1658  
1659          // Loop through cells and apply styles
1660          for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1661              for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1662                  $this->getCell(Coordinate::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
1663              }
1664          }
1665  
1666          return $this;
1667      }
1668  
1669      /**
1670       * Duplicate conditional style to a range of cells.
1671       *
1672       * Please note that this will overwrite existing cell styles for cells in range!
1673       *
1674       * @param Conditional[] $styles Cell style to duplicate
1675       * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1676       *
1677       * @return $this
1678       */
1679      public function duplicateConditionalStyle(array $styles, $range = '')
1680      {
1681          foreach ($styles as $cellStyle) {
1682              if (!($cellStyle instanceof Conditional)) {
1683                  throw new Exception('Style is not a conditional style');
1684              }
1685          }
1686  
1687          // Calculate range outer borders
1688          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range . ':' . $range);
1689  
1690          // Make sure we can loop upwards on rows and columns
1691          if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1692              $tmp = $rangeStart;
1693              $rangeStart = $rangeEnd;
1694              $rangeEnd = $tmp;
1695          }
1696  
1697          // Loop through cells and apply styles
1698          for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1699              for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1700                  $this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $styles);
1701              }
1702          }
1703  
1704          return $this;
1705      }
1706  
1707      /**
1708       * Set break on a cell.
1709       *
1710       * @param array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
1711       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1712       * @param int $break Break type (type of Worksheet::BREAK_*)
1713       *
1714       * @return $this
1715       */
1716      public function setBreak($coordinate, $break)
1717      {
1718          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate));
1719  
1720          if ($break === self::BREAK_NONE) {
1721              if (isset($this->breaks[$cellAddress])) {
1722                  unset($this->breaks[$cellAddress]);
1723              }
1724          } else {
1725              $this->breaks[$cellAddress] = $break;
1726          }
1727  
1728          return $this;
1729      }
1730  
1731      /**
1732       * Set break on a cell by using numeric cell coordinates.
1733       *
1734       * @Deprecated 1.23.0
1735       *      Use the setBreak() method with a cell address such as 'C5' instead;,
1736       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
1737       *
1738       * @param int $columnIndex Numeric column coordinate of the cell
1739       * @param int $row Numeric row coordinate of the cell
1740       * @param int $break Break type (type of Worksheet::BREAK_*)
1741       *
1742       * @return $this
1743       */
1744      public function setBreakByColumnAndRow($columnIndex, $row, $break)
1745      {
1746          return $this->setBreak(Coordinate::stringFromColumnIndex($columnIndex) . $row, $break);
1747      }
1748  
1749      /**
1750       * Get breaks.
1751       *
1752       * @return int[]
1753       */
1754      public function getBreaks()
1755      {
1756          return $this->breaks;
1757      }
1758  
1759      /**
1760       * Set merge on a cell range.
1761       *
1762       * @param AddressRange|array<int>|string $range A simple string containing a Cell range like 'A1:E10'
1763       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1764       *              or an AddressRange.
1765       * @param string $behaviour How the merged cells should behave.
1766       *               Possible values are:
1767       *                   MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells
1768       *                   MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells
1769       *                   MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell
1770       *
1771       * @return $this
1772       */
1773      public function mergeCells($range, $behaviour = self::MERGE_CELL_CONTENT_EMPTY)
1774      {
1775          $range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range));
1776  
1777          if (strpos($range, ':') === false) {
1778              $range .= ":{$range}";
1779          }
1780  
1781          if (preg_match('/^([A-Z]+)(\\d+):([A-Z]+)(\\d+)$/', $range, $matches) !== 1) {
1782              throw new Exception('Merge must be on a valid range of cells.');
1783          }
1784  
1785          $this->mergeCells[$range] = $range;
1786          $firstRow = (int) $matches[2];
1787          $lastRow = (int) $matches[4];
1788          $firstColumn = $matches[1];
1789          $lastColumn = $matches[3];
1790          $firstColumnIndex = Coordinate::columnIndexFromString($firstColumn);
1791          $lastColumnIndex = Coordinate::columnIndexFromString($lastColumn);
1792          $numberRows = $lastRow - $firstRow;
1793          $numberColumns = $lastColumnIndex - $firstColumnIndex;
1794  
1795          if ($numberRows === 1 && $numberColumns === 1) {
1796              return $this;
1797          }
1798  
1799          // create upper left cell if it does not already exist
1800          $upperLeft = "{$firstColumn}{$firstRow}";
1801          if (!$this->cellExists($upperLeft)) {
1802              $this->getCell($upperLeft)->setValueExplicit(null, DataType::TYPE_NULL);
1803          }
1804  
1805          if ($behaviour !== self::MERGE_CELL_CONTENT_HIDE) {
1806              // Blank out the rest of the cells in the range (if they exist)
1807              if ($numberRows > $numberColumns) {
1808                  $this->clearMergeCellsByColumn($firstColumn, $lastColumn, $firstRow, $lastRow, $upperLeft, $behaviour);
1809              } else {
1810                  $this->clearMergeCellsByRow($firstColumn, $lastColumnIndex, $firstRow, $lastRow, $upperLeft, $behaviour);
1811              }
1812          }
1813  
1814          return $this;
1815      }
1816  
1817      private function clearMergeCellsByColumn(string $firstColumn, string $lastColumn, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void
1818      {
1819          $leftCellValue = [$this->getCell($upperLeft)->getFormattedValue()];
1820  
1821          foreach ($this->getColumnIterator($firstColumn, $lastColumn) as $column) {
1822              $iterator = $column->getCellIterator($firstRow);
1823              $iterator->setIterateOnlyExistingCells(true);
1824              foreach ($iterator as $cell) {
1825                  if ($cell !== null) {
1826                      $row = $cell->getRow();
1827                      if ($row > $lastRow) {
1828                          break;
1829                      }
1830                      $leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue);
1831                  }
1832              }
1833          }
1834  
1835          $leftCellValue = implode(' ', $leftCellValue);
1836          if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1837              $this->getCell($upperLeft)->setValueExplicit($leftCellValue, DataType::TYPE_STRING);
1838          }
1839      }
1840  
1841      private function clearMergeCellsByRow(string $firstColumn, int $lastColumnIndex, int $firstRow, int $lastRow, string $upperLeft, string $behaviour): void
1842      {
1843          $leftCellValue = [$this->getCell($upperLeft)->getFormattedValue()];
1844  
1845          foreach ($this->getRowIterator($firstRow, $lastRow) as $row) {
1846              $iterator = $row->getCellIterator($firstColumn);
1847              $iterator->setIterateOnlyExistingCells(true);
1848              foreach ($iterator as $cell) {
1849                  if ($cell !== null) {
1850                      $column = $cell->getColumn();
1851                      $columnIndex = Coordinate::columnIndexFromString($column);
1852                      if ($columnIndex > $lastColumnIndex) {
1853                          break;
1854                      }
1855                      $leftCellValue = $this->mergeCellBehaviour($cell, $upperLeft, $behaviour, $leftCellValue);
1856                  }
1857              }
1858          }
1859  
1860          $leftCellValue = implode(' ', $leftCellValue);
1861          if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1862              $this->getCell($upperLeft)->setValueExplicit($leftCellValue, DataType::TYPE_STRING);
1863          }
1864      }
1865  
1866      public function mergeCellBehaviour(Cell $cell, string $upperLeft, string $behaviour, array $leftCellValue): array
1867      {
1868          if ($cell->getCoordinate() !== $upperLeft) {
1869              Calculation::getInstance($cell->getWorksheet()->getParent())->flushInstance();
1870              if ($behaviour === self::MERGE_CELL_CONTENT_MERGE) {
1871                  $cellValue = $cell->getFormattedValue();
1872                  if ($cellValue !== '') {
1873                      $leftCellValue[] = $cellValue;
1874                  }
1875              }
1876              $cell->setValueExplicit(null, DataType::TYPE_NULL);
1877          }
1878  
1879          return $leftCellValue;
1880      }
1881  
1882      /**
1883       * Set merge on a cell range by using numeric cell coordinates.
1884       *
1885       * @Deprecated 1.23.0
1886       *      Use the mergeCells() method with a cell address range such as 'C5:F8' instead;,
1887       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1888       *          or an AddressRange object.
1889       *
1890       * @param int $columnIndex1 Numeric column coordinate of the first cell
1891       * @param int $row1 Numeric row coordinate of the first cell
1892       * @param int $columnIndex2 Numeric column coordinate of the last cell
1893       * @param int $row2 Numeric row coordinate of the last cell
1894       * @param string $behaviour How the merged cells should behave.
1895       *               Possible values are:
1896       *                   MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells
1897       *                   MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells
1898       *                   MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell
1899       *
1900       * @return $this
1901       */
1902      public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $behaviour = self::MERGE_CELL_CONTENT_EMPTY)
1903      {
1904          $cellRange = new CellRange(
1905              CellAddress::fromColumnAndRow($columnIndex1, $row1),
1906              CellAddress::fromColumnAndRow($columnIndex2, $row2)
1907          );
1908  
1909          return $this->mergeCells($cellRange, $behaviour);
1910      }
1911  
1912      /**
1913       * Remove merge on a cell range.
1914       *
1915       * @param AddressRange|array<int>|string $range A simple string containing a Cell range like 'A1:E10'
1916       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1917       *              or an AddressRange.
1918       *
1919       * @return $this
1920       */
1921      public function unmergeCells($range)
1922      {
1923          $range = Functions::trimSheetFromCellReference(Validations::validateCellRange($range));
1924  
1925          if (strpos($range, ':') !== false) {
1926              if (isset($this->mergeCells[$range])) {
1927                  unset($this->mergeCells[$range]);
1928              } else {
1929                  throw new Exception('Cell range ' . $range . ' not known as merged.');
1930              }
1931          } else {
1932              throw new Exception('Merge can only be removed from a range of cells.');
1933          }
1934  
1935          return $this;
1936      }
1937  
1938      /**
1939       * Remove merge on a cell range by using numeric cell coordinates.
1940       *
1941       * @Deprecated 1.23.0
1942       *      Use the unmergeCells() method with a cell address range such as 'C5:F8' instead;,
1943       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1944       *          or an AddressRange object.
1945       *
1946       * @param int $columnIndex1 Numeric column coordinate of the first cell
1947       * @param int $row1 Numeric row coordinate of the first cell
1948       * @param int $columnIndex2 Numeric column coordinate of the last cell
1949       * @param int $row2 Numeric row coordinate of the last cell
1950       *
1951       * @return $this
1952       */
1953      public function unmergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
1954      {
1955          $cellRange = new CellRange(
1956              CellAddress::fromColumnAndRow($columnIndex1, $row1),
1957              CellAddress::fromColumnAndRow($columnIndex2, $row2)
1958          );
1959  
1960          return $this->unmergeCells($cellRange);
1961      }
1962  
1963      /**
1964       * Get merge cells array.
1965       *
1966       * @return string[]
1967       */
1968      public function getMergeCells()
1969      {
1970          return $this->mergeCells;
1971      }
1972  
1973      /**
1974       * Set merge cells array for the entire sheet. Use instead mergeCells() to merge
1975       * a single cell range.
1976       *
1977       * @param string[] $mergeCells
1978       *
1979       * @return $this
1980       */
1981      public function setMergeCells(array $mergeCells)
1982      {
1983          $this->mergeCells = $mergeCells;
1984  
1985          return $this;
1986      }
1987  
1988      /**
1989       * Set protection on a cell or cell range.
1990       *
1991       * @param AddressRange|array<int>|CellAddress|int|string $range A simple string containing a Cell range like 'A1:E10'
1992       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
1993       *              or a CellAddress or AddressRange object.
1994       * @param string $password Password to unlock the protection
1995       * @param bool $alreadyHashed If the password has already been hashed, set this to true
1996       *
1997       * @return $this
1998       */
1999      public function protectCells($range, $password, $alreadyHashed = false)
2000      {
2001          $range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));
2002  
2003          if (!$alreadyHashed) {
2004              $password = Shared\PasswordHasher::hashPassword($password);
2005          }
2006          $this->protectedCells[$range] = $password;
2007  
2008          return $this;
2009      }
2010  
2011      /**
2012       * Set protection on a cell range by using numeric cell coordinates.
2013       *
2014       * @Deprecated 1.23.0
2015       *      Use the protectCells() method with a cell address range such as 'C5:F8' instead;,
2016       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2017       *          or an AddressRange object.
2018       *
2019       * @param int $columnIndex1 Numeric column coordinate of the first cell
2020       * @param int $row1 Numeric row coordinate of the first cell
2021       * @param int $columnIndex2 Numeric column coordinate of the last cell
2022       * @param int $row2 Numeric row coordinate of the last cell
2023       * @param string $password Password to unlock the protection
2024       * @param bool $alreadyHashed If the password has already been hashed, set this to true
2025       *
2026       * @return $this
2027       */
2028      public function protectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $password, $alreadyHashed = false)
2029      {
2030          $cellRange = new CellRange(
2031              CellAddress::fromColumnAndRow($columnIndex1, $row1),
2032              CellAddress::fromColumnAndRow($columnIndex2, $row2)
2033          );
2034  
2035          return $this->protectCells($cellRange, $password, $alreadyHashed);
2036      }
2037  
2038      /**
2039       * Remove protection on a cell or cell range.
2040       *
2041       * @param AddressRange|array<int>|CellAddress|int|string $range A simple string containing a Cell range like 'A1:E10'
2042       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2043       *              or a CellAddress or AddressRange object.
2044       *
2045       * @return $this
2046       */
2047      public function unprotectCells($range)
2048      {
2049          $range = Functions::trimSheetFromCellReference(Validations::validateCellOrCellRange($range));
2050  
2051          if (isset($this->protectedCells[$range])) {
2052              unset($this->protectedCells[$range]);
2053          } else {
2054              throw new Exception('Cell range ' . $range . ' not known as protected.');
2055          }
2056  
2057          return $this;
2058      }
2059  
2060      /**
2061       * Remove protection on a cell range by using numeric cell coordinates.
2062       *
2063       * @Deprecated 1.23.0
2064       *      Use the protectCells() method with a cell address range such as 'C5:F8' instead;,
2065       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2066       *          or an AddressRange object.
2067       *
2068       * @param int $columnIndex1 Numeric column coordinate of the first cell
2069       * @param int $row1 Numeric row coordinate of the first cell
2070       * @param int $columnIndex2 Numeric column coordinate of the last cell
2071       * @param int $row2 Numeric row coordinate of the last cell
2072       *
2073       * @return $this
2074       */
2075      public function unprotectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
2076      {
2077          $cellRange = new CellRange(
2078              CellAddress::fromColumnAndRow($columnIndex1, $row1),
2079              CellAddress::fromColumnAndRow($columnIndex2, $row2)
2080          );
2081  
2082          return $this->unprotectCells($cellRange);
2083      }
2084  
2085      /**
2086       * Get protected cells.
2087       *
2088       * @return string[]
2089       */
2090      public function getProtectedCells()
2091      {
2092          return $this->protectedCells;
2093      }
2094  
2095      /**
2096       * Get Autofilter.
2097       *
2098       * @return AutoFilter
2099       */
2100      public function getAutoFilter()
2101      {
2102          return $this->autoFilter;
2103      }
2104  
2105      /**
2106       * Set AutoFilter.
2107       *
2108       * @param AddressRange|array<int>|AutoFilter|string $autoFilterOrRange
2109       *            A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility
2110       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2111       *              or an AddressRange.
2112       *
2113       * @return $this
2114       */
2115      public function setAutoFilter($autoFilterOrRange)
2116      {
2117          if (is_object($autoFilterOrRange) && ($autoFilterOrRange instanceof AutoFilter)) {
2118              $this->autoFilter = $autoFilterOrRange;
2119          } else {
2120              $cellRange = Functions::trimSheetFromCellReference(Validations::validateCellRange($autoFilterOrRange));
2121  
2122              $this->autoFilter->setRange($cellRange);
2123          }
2124  
2125          return $this;
2126      }
2127  
2128      /**
2129       * Set Autofilter Range by using numeric cell coordinates.
2130       *
2131       * @Deprecated 1.23.0
2132       *      Use the setAutoFilter() method with a cell address range such as 'C5:F8' instead;,
2133       *          or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2134       *          or an AddressRange object or AutoFilter object.
2135       *
2136       * @param int $columnIndex1 Numeric column coordinate of the first cell
2137       * @param int $row1 Numeric row coordinate of the first cell
2138       * @param int $columnIndex2 Numeric column coordinate of the second cell
2139       * @param int $row2 Numeric row coordinate of the second cell
2140       *
2141       * @return $this
2142       */
2143      public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)
2144      {
2145          $cellRange = new CellRange(
2146              CellAddress::fromColumnAndRow($columnIndex1, $row1),
2147              CellAddress::fromColumnAndRow($columnIndex2, $row2)
2148          );
2149  
2150          return $this->setAutoFilter($cellRange);
2151      }
2152  
2153      /**
2154       * Remove autofilter.
2155       */
2156      public function removeAutoFilter(): self
2157      {
2158          $this->autoFilter->setRange('');
2159  
2160          return $this;
2161      }
2162  
2163      /**
2164       * Get collection of Tables.
2165       *
2166       * @return ArrayObject<int, Table>
2167       */
2168      public function getTableCollection()
2169      {
2170          return $this->tableCollection;
2171      }
2172  
2173      /**
2174       * Add Table.
2175       *
2176       * @return $this
2177       */
2178      public function addTable(Table $table): self
2179      {
2180          $table->setWorksheet($this);
2181          $this->tableCollection[] = $table;
2182  
2183          return $this;
2184      }
2185  
2186      /**
2187       * Remove Table by name.
2188       *
2189       * @param string $name Table name
2190       *
2191       * @return $this
2192       */
2193      public function removeTableByName(string $name): self
2194      {
2195          $name = Shared\StringHelper::strToUpper($name);
2196          foreach ($this->tableCollection as $key => $table) {
2197              if (Shared\StringHelper::strToUpper($table->getName()) === $name) {
2198                  unset($this->tableCollection[$key]);
2199              }
2200          }
2201  
2202          return $this;
2203      }
2204  
2205      /**
2206       * Remove collection of Tables.
2207       */
2208      public function removeTableCollection(): self
2209      {
2210          $this->tableCollection = new ArrayObject();
2211  
2212          return $this;
2213      }
2214  
2215      /**
2216       * Get Freeze Pane.
2217       *
2218       * @return null|string
2219       */
2220      public function getFreezePane()
2221      {
2222          return $this->freezePane;
2223      }
2224  
2225      /**
2226       * Freeze Pane.
2227       *
2228       * Examples:
2229       *
2230       *     - A2 will freeze the rows above cell A2 (i.e row 1)
2231       *     - B1 will freeze the columns to the left of cell B1 (i.e column A)
2232       *     - B2 will freeze the rows above and to the left of cell B2 (i.e row 1 and column A)
2233       *
2234       * @param null|array<int>|CellAddress|string $coordinate Coordinate of the cell as a string, eg: 'C5';
2235       *            or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2236       *        Passing a null value for this argument will clear any existing freeze pane for this worksheet.
2237       * @param null|array<int>|CellAddress|string $topLeftCell default position of the right bottom pane
2238       *            Coordinate of the cell as a string, eg: 'C5'; or as an array of [$columnIndex, $row] (e.g. [3, 5]),
2239       *            or a CellAddress object.
2240       *
2241       * @return $this
2242       */
2243      public function freezePane($coordinate, $topLeftCell = null)
2244      {
2245          $cellAddress = ($coordinate !== null)
2246              ? Functions::trimSheetFromCellReference(Validations::validateCellAddress($coordinate))
2247              : null;
2248          if ($cellAddress !== null && Coordinate::coordinateIsRange($cellAddress)) {
2249              throw new Exception('Freeze pane can not be set on a range of cells.');
2250          }
2251          $topLeftCell = ($topLeftCell !== null)
2252              ? Functions::trimSheetFromCellReference(Validations::validateCellAddress($topLeftCell))
2253              : null;
2254  
2255          if ($cellAddress !== null && $topLeftCell === null) {
2256              $coordinate = Coordinate::coordinateFromString($cellAddress);
2257              $topLeftCell = $coordinate[0] . $coordinate[1];
2258          }
2259  
2260          $this->freezePane = $cellAddress;
2261          $this->topLeftCell = $topLeftCell;
2262  
2263          return $this;
2264      }
2265  
2266      public function setTopLeftCell(string $topLeftCell): self
2267      {
2268          $this->topLeftCell = $topLeftCell;
2269  
2270          return $this;
2271      }
2272  
2273      /**
2274       * Freeze Pane by using numeric cell coordinates.
2275       *
2276       * @Deprecated 1.23.0
2277       *      Use the freezePane() method with a cell address such as 'C5' instead;,
2278       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2279       *
2280       * @param int $columnIndex Numeric column coordinate of the cell
2281       * @param int $row Numeric row coordinate of the cell
2282       *
2283       * @return $this
2284       */
2285      public function freezePaneByColumnAndRow($columnIndex, $row)
2286      {
2287          return $this->freezePane(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2288      }
2289  
2290      /**
2291       * Unfreeze Pane.
2292       *
2293       * @return $this
2294       */
2295      public function unfreezePane()
2296      {
2297          return $this->freezePane(null);
2298      }
2299  
2300      /**
2301       * Get the default position of the right bottom pane.
2302       *
2303       * @return null|string
2304       */
2305      public function getTopLeftCell()
2306      {
2307          return $this->topLeftCell;
2308      }
2309  
2310      /**
2311       * Insert a new row, updating all possible related data.
2312       *
2313       * @param int $before Insert before this one
2314       * @param int $numberOfRows Number of rows to insert
2315       *
2316       * @return $this
2317       */
2318      public function insertNewRowBefore($before, $numberOfRows = 1)
2319      {
2320          if ($before >= 1) {
2321              $objReferenceHelper = ReferenceHelper::getInstance();
2322              $objReferenceHelper->insertNewBefore('A' . $before, 0, $numberOfRows, $this);
2323          } else {
2324              throw new Exception('Rows can only be inserted before at least row 1.');
2325          }
2326  
2327          return $this;
2328      }
2329  
2330      /**
2331       * Insert a new column, updating all possible related data.
2332       *
2333       * @param string $before Insert before this one, eg: 'A'
2334       * @param int $numberOfColumns Number of columns to insert
2335       *
2336       * @return $this
2337       */
2338      public function insertNewColumnBefore($before, $numberOfColumns = 1)
2339      {
2340          if (!is_numeric($before)) {
2341              $objReferenceHelper = ReferenceHelper::getInstance();
2342              $objReferenceHelper->insertNewBefore($before . '1', $numberOfColumns, 0, $this);
2343          } else {
2344              throw new Exception('Column references should not be numeric.');
2345          }
2346  
2347          return $this;
2348      }
2349  
2350      /**
2351       * Insert a new column, updating all possible related data.
2352       *
2353       * @param int $beforeColumnIndex Insert before this one (numeric column coordinate of the cell)
2354       * @param int $numberOfColumns Number of columns to insert
2355       *
2356       * @return $this
2357       */
2358      public function insertNewColumnBeforeByIndex($beforeColumnIndex, $numberOfColumns = 1)
2359      {
2360          if ($beforeColumnIndex >= 1) {
2361              return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $numberOfColumns);
2362          }
2363  
2364          throw new Exception('Columns can only be inserted before at least column A (1).');
2365      }
2366  
2367      /**
2368       * Delete a row, updating all possible related data.
2369       *
2370       * @param int $row Remove starting with this one
2371       * @param int $numberOfRows Number of rows to remove
2372       *
2373       * @return $this
2374       */
2375      public function removeRow($row, $numberOfRows = 1)
2376      {
2377          if ($row < 1) {
2378              throw new Exception('Rows to be deleted should at least start from row 1.');
2379          }
2380  
2381          $holdRowDimensions = $this->removeRowDimensions($row, $numberOfRows);
2382          $highestRow = $this->getHighestDataRow();
2383          $removedRowsCounter = 0;
2384  
2385          for ($r = 0; $r < $numberOfRows; ++$r) {
2386              if ($row + $r <= $highestRow) {
2387                  $this->getCellCollection()->removeRow($row + $r);
2388                  ++$removedRowsCounter;
2389              }
2390          }
2391  
2392          $objReferenceHelper = ReferenceHelper::getInstance();
2393          $objReferenceHelper->insertNewBefore('A' . ($row + $numberOfRows), 0, -$numberOfRows, $this);
2394          for ($r = 0; $r < $removedRowsCounter; ++$r) {
2395              $this->getCellCollection()->removeRow($highestRow);
2396              --$highestRow;
2397          }
2398  
2399          $this->rowDimensions = $holdRowDimensions;
2400  
2401          return $this;
2402      }
2403  
2404      private function removeRowDimensions(int $row, int $numberOfRows): array
2405      {
2406          $highRow = $row + $numberOfRows - 1;
2407          $holdRowDimensions = [];
2408          foreach ($this->rowDimensions as $rowDimension) {
2409              $num = $rowDimension->getRowIndex();
2410              if ($num < $row) {
2411                  $holdRowDimensions[$num] = $rowDimension;
2412              } elseif ($num > $highRow) {
2413                  $num -= $numberOfRows;
2414                  $cloneDimension = clone $rowDimension;
2415                  $cloneDimension->setRowIndex($num);
2416                  $holdRowDimensions[$num] = $cloneDimension;
2417              }
2418          }
2419  
2420          return $holdRowDimensions;
2421      }
2422  
2423      /**
2424       * Remove a column, updating all possible related data.
2425       *
2426       * @param string $column Remove starting with this one, eg: 'A'
2427       * @param int $numberOfColumns Number of columns to remove
2428       *
2429       * @return $this
2430       */
2431      public function removeColumn($column, $numberOfColumns = 1)
2432      {
2433          if (is_numeric($column)) {
2434              throw new Exception('Column references should not be numeric.');
2435          }
2436  
2437          $highestColumn = $this->getHighestDataColumn();
2438          $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
2439          $pColumnIndex = Coordinate::columnIndexFromString($column);
2440  
2441          $holdColumnDimensions = $this->removeColumnDimensions($pColumnIndex, $numberOfColumns);
2442  
2443          $column = Coordinate::stringFromColumnIndex($pColumnIndex + $numberOfColumns);
2444          $objReferenceHelper = ReferenceHelper::getInstance();
2445          $objReferenceHelper->insertNewBefore($column . '1', -$numberOfColumns, 0, $this);
2446  
2447          $this->columnDimensions = $holdColumnDimensions;
2448  
2449          if ($pColumnIndex > $highestColumnIndex) {
2450              return $this;
2451          }
2452  
2453          $maxPossibleColumnsToBeRemoved = $highestColumnIndex - $pColumnIndex + 1;
2454  
2455          for ($c = 0, $n = min($maxPossibleColumnsToBeRemoved, $numberOfColumns); $c < $n; ++$c) {
2456              $this->getCellCollection()->removeColumn($highestColumn);
2457              $highestColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 1);
2458          }
2459  
2460          $this->garbageCollect();
2461  
2462          return $this;
2463      }
2464  
2465      private function removeColumnDimensions(int $pColumnIndex, int $numberOfColumns): array
2466      {
2467          $highCol = $pColumnIndex + $numberOfColumns - 1;
2468          $holdColumnDimensions = [];
2469          foreach ($this->columnDimensions as $columnDimension) {
2470              $num = $columnDimension->getColumnNumeric();
2471              if ($num < $pColumnIndex) {
2472                  $str = $columnDimension->getColumnIndex();
2473                  $holdColumnDimensions[$str] = $columnDimension;
2474              } elseif ($num > $highCol) {
2475                  $cloneDimension = clone $columnDimension;
2476                  $cloneDimension->setColumnNumeric($num - $numberOfColumns);
2477                  $str = $cloneDimension->getColumnIndex();
2478                  $holdColumnDimensions[$str] = $cloneDimension;
2479              }
2480          }
2481  
2482          return $holdColumnDimensions;
2483      }
2484  
2485      /**
2486       * Remove a column, updating all possible related data.
2487       *
2488       * @param int $columnIndex Remove starting with this one (numeric column coordinate of the cell)
2489       * @param int $numColumns Number of columns to remove
2490       *
2491       * @return $this
2492       */
2493      public function removeColumnByIndex($columnIndex, $numColumns = 1)
2494      {
2495          if ($columnIndex >= 1) {
2496              return $this->removeColumn(Coordinate::stringFromColumnIndex($columnIndex), $numColumns);
2497          }
2498  
2499          throw new Exception('Columns to be deleted should at least start from column A (1)');
2500      }
2501  
2502      /**
2503       * Show gridlines?
2504       */
2505      public function getShowGridlines(): bool
2506      {
2507          return $this->showGridlines;
2508      }
2509  
2510      /**
2511       * Set show gridlines.
2512       *
2513       * @param bool $showGridLines Show gridlines (true/false)
2514       *
2515       * @return $this
2516       */
2517      public function setShowGridlines(bool $showGridLines): self
2518      {
2519          $this->showGridlines = $showGridLines;
2520  
2521          return $this;
2522      }
2523  
2524      /**
2525       * Print gridlines?
2526       */
2527      public function getPrintGridlines(): bool
2528      {
2529          return $this->printGridlines;
2530      }
2531  
2532      /**
2533       * Set print gridlines.
2534       *
2535       * @param bool $printGridLines Print gridlines (true/false)
2536       *
2537       * @return $this
2538       */
2539      public function setPrintGridlines(bool $printGridLines): self
2540      {
2541          $this->printGridlines = $printGridLines;
2542  
2543          return $this;
2544      }
2545  
2546      /**
2547       * Show row and column headers?
2548       */
2549      public function getShowRowColHeaders(): bool
2550      {
2551          return $this->showRowColHeaders;
2552      }
2553  
2554      /**
2555       * Set show row and column headers.
2556       *
2557       * @param bool $showRowColHeaders Show row and column headers (true/false)
2558       *
2559       * @return $this
2560       */
2561      public function setShowRowColHeaders(bool $showRowColHeaders): self
2562      {
2563          $this->showRowColHeaders = $showRowColHeaders;
2564  
2565          return $this;
2566      }
2567  
2568      /**
2569       * Show summary below? (Row/Column outlining).
2570       */
2571      public function getShowSummaryBelow(): bool
2572      {
2573          return $this->showSummaryBelow;
2574      }
2575  
2576      /**
2577       * Set show summary below.
2578       *
2579       * @param bool $showSummaryBelow Show summary below (true/false)
2580       *
2581       * @return $this
2582       */
2583      public function setShowSummaryBelow(bool $showSummaryBelow): self
2584      {
2585          $this->showSummaryBelow = $showSummaryBelow;
2586  
2587          return $this;
2588      }
2589  
2590      /**
2591       * Show summary right? (Row/Column outlining).
2592       */
2593      public function getShowSummaryRight(): bool
2594      {
2595          return $this->showSummaryRight;
2596      }
2597  
2598      /**
2599       * Set show summary right.
2600       *
2601       * @param bool $showSummaryRight Show summary right (true/false)
2602       *
2603       * @return $this
2604       */
2605      public function setShowSummaryRight(bool $showSummaryRight): self
2606      {
2607          $this->showSummaryRight = $showSummaryRight;
2608  
2609          return $this;
2610      }
2611  
2612      /**
2613       * Get comments.
2614       *
2615       * @return Comment[]
2616       */
2617      public function getComments()
2618      {
2619          return $this->comments;
2620      }
2621  
2622      /**
2623       * Set comments array for the entire sheet.
2624       *
2625       * @param Comment[] $comments
2626       *
2627       * @return $this
2628       */
2629      public function setComments(array $comments): self
2630      {
2631          $this->comments = $comments;
2632  
2633          return $this;
2634      }
2635  
2636      /**
2637       * Remove comment from cell.
2638       *
2639       * @param array<int>|CellAddress|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5';
2640       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2641       *
2642       * @return $this
2643       */
2644      public function removeComment($cellCoordinate): self
2645      {
2646          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate));
2647  
2648          if (Coordinate::coordinateIsRange($cellAddress)) {
2649              throw new Exception('Cell coordinate string can not be a range of cells.');
2650          } elseif (strpos($cellAddress, '$') !== false) {
2651              throw new Exception('Cell coordinate string must not be absolute.');
2652          } elseif ($cellAddress == '') {
2653              throw new Exception('Cell coordinate can not be zero-length string.');
2654          }
2655          // Check if we have a comment for this cell and delete it
2656          if (isset($this->comments[$cellAddress])) {
2657              unset($this->comments[$cellAddress]);
2658          }
2659  
2660          return $this;
2661      }
2662  
2663      /**
2664       * Get comment for cell.
2665       *
2666       * @param array<int>|CellAddress|string $cellCoordinate Coordinate of the cell as a string, eg: 'C5';
2667       *               or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2668       */
2669      public function getComment($cellCoordinate): Comment
2670      {
2671          $cellAddress = Functions::trimSheetFromCellReference(Validations::validateCellAddress($cellCoordinate));
2672  
2673          if (Coordinate::coordinateIsRange($cellAddress)) {
2674              throw new Exception('Cell coordinate string can not be a range of cells.');
2675          } elseif (strpos($cellAddress, '$') !== false) {
2676              throw new Exception('Cell coordinate string must not be absolute.');
2677          } elseif ($cellAddress == '') {
2678              throw new Exception('Cell coordinate can not be zero-length string.');
2679          }
2680  
2681          // Check if we already have a comment for this cell.
2682          if (isset($this->comments[$cellAddress])) {
2683              return $this->comments[$cellAddress];
2684          }
2685  
2686          // If not, create a new comment.
2687          $newComment = new Comment();
2688          $this->comments[$cellAddress] = $newComment;
2689  
2690          return $newComment;
2691      }
2692  
2693      /**
2694       * Get comment for cell by using numeric cell coordinates.
2695       *
2696       * @Deprecated 1.23.0
2697       *      Use the getComment() method with a cell address such as 'C5' instead;,
2698       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2699       *
2700       * @param int $columnIndex Numeric column coordinate of the cell
2701       * @param int $row Numeric row coordinate of the cell
2702       */
2703      public function getCommentByColumnAndRow($columnIndex, $row): Comment
2704      {
2705          return $this->getComment(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2706      }
2707  
2708      /**
2709       * Get active cell.
2710       *
2711       * @return string Example: 'A1'
2712       */
2713      public function getActiveCell()
2714      {
2715          return $this->activeCell;
2716      }
2717  
2718      /**
2719       * Get selected cells.
2720       *
2721       * @return string
2722       */
2723      public function getSelectedCells()
2724      {
2725          return $this->selectedCells;
2726      }
2727  
2728      /**
2729       * Selected cell.
2730       *
2731       * @param string $coordinate Cell (i.e. A1)
2732       *
2733       * @return $this
2734       */
2735      public function setSelectedCell($coordinate)
2736      {
2737          return $this->setSelectedCells($coordinate);
2738      }
2739  
2740      /**
2741       * Select a range of cells.
2742       *
2743       * @param AddressRange|array<int>|CellAddress|int|string $coordinate A simple string containing a Cell range like 'A1:E10'
2744       *              or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]),
2745       *              or a CellAddress or AddressRange object.
2746       *
2747       * @return $this
2748       */
2749      public function setSelectedCells($coordinate)
2750      {
2751          if (is_string($coordinate)) {
2752              $coordinate = Validations::definedNameToCoordinate($coordinate, $this);
2753          }
2754          $coordinate = Validations::validateCellOrCellRange($coordinate);
2755  
2756          if (Coordinate::coordinateIsRange($coordinate)) {
2757              [$first] = Coordinate::splitRange($coordinate);
2758              $this->activeCell = $first[0];
2759          } else {
2760              $this->activeCell = $coordinate;
2761          }
2762          $this->selectedCells = $coordinate;
2763  
2764          return $this;
2765      }
2766  
2767      /**
2768       * Selected cell by using numeric cell coordinates.
2769       *
2770       * @Deprecated 1.23.0
2771       *      Use the setSelectedCells() method with a cell address such as 'C5' instead;,
2772       *          or passing in an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object.
2773       *
2774       * @param int $columnIndex Numeric column coordinate of the cell
2775       * @param int $row Numeric row coordinate of the cell
2776       *
2777       * @return $this
2778       */
2779      public function setSelectedCellByColumnAndRow($columnIndex, $row)
2780      {
2781          return $this->setSelectedCells(Coordinate::stringFromColumnIndex($columnIndex) . $row);
2782      }
2783  
2784      /**
2785       * Get right-to-left.
2786       *
2787       * @return bool
2788       */
2789      public function getRightToLeft()
2790      {
2791          return $this->rightToLeft;
2792      }
2793  
2794      /**
2795       * Set right-to-left.
2796       *
2797       * @param bool $value Right-to-left true/false
2798       *
2799       * @return $this
2800       */
2801      public function setRightToLeft($value)
2802      {
2803          $this->rightToLeft = $value;
2804  
2805          return $this;
2806      }
2807  
2808      /**
2809       * Fill worksheet from values in array.
2810       *
2811       * @param array $source Source array
2812       * @param mixed $nullValue Value in source array that stands for blank cell
2813       * @param string $startCell Insert array starting from this cell address as the top left coordinate
2814       * @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
2815       *
2816       * @return $this
2817       */
2818      public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
2819      {
2820          //    Convert a 1-D array to 2-D (for ease of looping)
2821          if (!is_array(end($source))) {
2822              $source = [$source];
2823          }
2824  
2825          // start coordinate
2826          [$startColumn, $startRow] = Coordinate::coordinateFromString($startCell);
2827  
2828          // Loop through $source
2829          foreach ($source as $rowData) {
2830              $currentColumn = $startColumn;
2831              foreach ($rowData as $cellValue) {
2832                  if ($strictNullComparison) {
2833                      if ($cellValue !== $nullValue) {
2834                          // Set cell value
2835                          $this->getCell($currentColumn . $startRow)->setValue($cellValue);
2836                      }
2837                  } else {
2838                      if ($cellValue != $nullValue) {
2839                          // Set cell value
2840                          $this->getCell($currentColumn . $startRow)->setValue($cellValue);
2841                      }
2842                  }
2843                  ++$currentColumn;
2844              }
2845              ++$startRow;
2846          }
2847  
2848          return $this;
2849      }
2850  
2851      /**
2852       * Create array from a range of cells.
2853       *
2854       * @param string $range Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
2855       * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2856       * @param bool $calculateFormulas Should formulas be calculated?
2857       * @param bool $formatData Should formatting be applied to cell values?
2858       * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2859       *                               True - Return rows and columns indexed by their actual row and column IDs
2860       *
2861       * @return array
2862       */
2863      public function rangeToArray($range, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2864      {
2865          // Returnvalue
2866          $returnValue = [];
2867          //    Identify the range that we need to extract from the worksheet
2868          [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);
2869          $minCol = Coordinate::stringFromColumnIndex($rangeStart[0]);
2870          $minRow = $rangeStart[1];
2871          $maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]);
2872          $maxRow = $rangeEnd[1];
2873  
2874          ++$maxCol;
2875          // Loop through rows
2876          $r = -1;
2877          for ($row = $minRow; $row <= $maxRow; ++$row) {
2878              $rRef = $returnCellRef ? $row : ++$r;
2879              $c = -1;
2880              // Loop through columns in the current row
2881              for ($col = $minCol; $col != $maxCol; ++$col) {
2882                  $cRef = $returnCellRef ? $col : ++$c;
2883                  //    Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
2884                  //        so we test and retrieve directly against cellCollection
2885                  if ($this->cellCollection->has($col . $row)) {
2886                      // Cell exists
2887                      $cell = $this->cellCollection->get($col . $row);
2888                      if ($cell->getValue() !== null) {
2889                          if ($cell->getValue() instanceof RichText) {
2890                              $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
2891                          } else {
2892                              if ($calculateFormulas) {
2893                                  $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
2894                              } else {
2895                                  $returnValue[$rRef][$cRef] = $cell->getValue();
2896                              }
2897                          }
2898  
2899                          if ($formatData) {
2900                              $style = $this->parent->getCellXfByIndex($cell->getXfIndex());
2901                              $returnValue[$rRef][$cRef] = NumberFormat::toFormattedString(
2902                                  $returnValue[$rRef][$cRef],
2903                                  ($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL
2904                              );
2905                          }
2906                      } else {
2907                          // Cell holds a NULL
2908                          $returnValue[$rRef][$cRef] = $nullValue;
2909                      }
2910                  } else {
2911                      // Cell doesn't exist
2912                      $returnValue[$rRef][$cRef] = $nullValue;
2913                  }
2914              }
2915          }
2916  
2917          // Return
2918          return $returnValue;
2919      }
2920  
2921      private function validateNamedRange(string $definedName, bool $returnNullIfInvalid = false): ?DefinedName
2922      {
2923          $namedRange = DefinedName::resolveName($definedName, $this);
2924          if ($namedRange === null) {
2925              if ($returnNullIfInvalid) {
2926                  return null;
2927              }
2928  
2929              throw new Exception('Named Range ' . $definedName . ' does not exist.');
2930          }
2931  
2932          if ($namedRange->isFormula()) {
2933              if ($returnNullIfInvalid) {
2934                  return null;
2935              }
2936  
2937              throw new Exception('Defined Named ' . $definedName . ' is a formula, not a range or cell.');
2938          }
2939  
2940          if ($namedRange->getLocalOnly() && $this->getHashCode() !== $namedRange->getWorksheet()->getHashCode()) {
2941              if ($returnNullIfInvalid) {
2942                  return null;
2943              }
2944  
2945              throw new Exception(
2946                  'Named range ' . $definedName . ' is not accessible from within sheet ' . $this->getTitle()
2947              );
2948          }
2949  
2950          return $namedRange;
2951      }
2952  
2953      /**
2954       * Create array from a range of cells.
2955       *
2956       * @param string $definedName The Named Range that should be returned
2957       * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2958       * @param bool $calculateFormulas Should formulas be calculated?
2959       * @param bool $formatData Should formatting be applied to cell values?
2960       * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2961       *                                True - Return rows and columns indexed by their actual row and column IDs
2962       *
2963       * @return array
2964       */
2965      public function namedRangeToArray(string $definedName, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2966      {
2967          $namedRange = $this->validateNamedRange($definedName);
2968          $workSheet = $namedRange->getWorksheet();
2969          /** @phpstan-ignore-next-line */
2970          $cellRange = ltrim(substr($namedRange->getValue(), strrpos($namedRange->getValue(), '!')), '!');
2971          $cellRange = str_replace('$', '', $cellRange);
2972  
2973          return $workSheet->rangeToArray($cellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2974      }
2975  
2976      /**
2977       * Create array from worksheet.
2978       *
2979       * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2980       * @param bool $calculateFormulas Should formulas be calculated?
2981       * @param bool $formatData Should formatting be applied to cell values?
2982       * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2983       *                               True - Return rows and columns indexed by their actual row and column IDs
2984       *
2985       * @return array
2986       */
2987      public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
2988      {
2989          // Garbage collect...
2990          $this->garbageCollect();
2991  
2992          //    Identify the range that we need to extract from the worksheet
2993          $maxCol = $this->getHighestColumn();
2994          $maxRow = $this->getHighestRow();
2995  
2996          // Return
2997          return $this->rangeToArray('A1:' . $maxCol . $maxRow, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2998      }
2999  
3000      /**
3001       * Get row iterator.
3002       *
3003       * @param int $startRow The row number at which to start iterating
3004       * @param int $endRow The row number at which to stop iterating
3005       *
3006       * @return RowIterator
3007       */
3008      public function getRowIterator($startRow = 1, $endRow = null)
3009      {
3010          return new RowIterator($this, $startRow, $endRow);
3011      }
3012  
3013      /**
3014       * Get column iterator.
3015       *
3016       * @param string $startColumn The column address at which to start iterating
3017       * @param string $endColumn The column address at which to stop iterating
3018       *
3019       * @return ColumnIterator
3020       */
3021      public function getColumnIterator($startColumn = 'A', $endColumn = null)
3022      {
3023          return new ColumnIterator($this, $startColumn, $endColumn);
3024      }
3025  
3026      /**
3027       * Run PhpSpreadsheet garbage collector.
3028       *
3029       * @return $this
3030       */
3031      public function garbageCollect()
3032      {
3033          // Flush cache
3034          $this->cellCollection->get('A1');
3035  
3036          // Lookup highest column and highest row if cells are cleaned
3037          $colRow = $this->cellCollection->getHighestRowAndColumn();
3038          $highestRow = $colRow['row'];
3039          $highestColumn = Coordinate::columnIndexFromString($colRow['column']);
3040  
3041          // Loop through column dimensions
3042          foreach ($this->columnDimensions as $dimension) {
3043              $highestColumn = max($highestColumn, Coordinate::columnIndexFromString($dimension->getColumnIndex()));
3044          }
3045  
3046          // Loop through row dimensions
3047          foreach ($this->rowDimensions as $dimension) {
3048              $highestRow = max($highestRow, $dimension->getRowIndex());
3049          }
3050  
3051          // Cache values
3052          if ($highestColumn < 1) {
3053              $this->cachedHighestColumn = 1;
3054          } else {
3055              $this->cachedHighestColumn = $highestColumn;
3056          }
3057          $this->cachedHighestRow = $highestRow;
3058  
3059          // Return
3060          return $this;
3061      }
3062  
3063      /**
3064       * Get hash code.
3065       *
3066       * @return string Hash code
3067       */
3068      public function getHashCode()
3069      {
3070          if ($this->dirty) {
3071              $this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__);
3072              $this->dirty = false;
3073          }
3074  
3075          return $this->hash;
3076      }
3077  
3078      /**
3079       * Extract worksheet title from range.
3080       *
3081       * Example: extractSheetTitle("testSheet!A1") ==> 'A1'
3082       * Example: extractSheetTitle("testSheet!A1:C3") ==> 'A1:C3'
3083       * Example: extractSheetTitle("'testSheet 1'!A1", true) ==> ['testSheet 1', 'A1'];
3084       * Example: extractSheetTitle("'testSheet 1'!A1:C3", true) ==> ['testSheet 1', 'A1:C3'];
3085       * Example: extractSheetTitle("A1", true) ==> ['', 'A1'];
3086       * Example: extractSheetTitle("A1:C3", true) ==> ['', 'A1:C3']
3087       *
3088       * @param string $range Range to extract title from
3089       * @param bool $returnRange Return range? (see example)
3090       *
3091       * @return mixed
3092       */
3093      public static function extractSheetTitle($range, $returnRange = false)
3094      {
3095          if ($range === null) {
3096              return $returnRange ? [null, null] : null;
3097          }
3098  
3099          // Sheet title included?
3100          if (($sep = strrpos($range, '!')) === false) {
3101              return $returnRange ? ['', $range] : '';
3102          }
3103  
3104          if ($returnRange) {
3105              return [substr($range, 0, $sep), substr($range, $sep + 1)];
3106          }
3107  
3108          return substr($range, $sep + 1);
3109      }
3110  
3111      /**
3112       * Get hyperlink.
3113       *
3114       * @param string $cellCoordinate Cell coordinate to get hyperlink for, eg: 'A1'
3115       *
3116       * @return Hyperlink
3117       */
3118      public function getHyperlink($cellCoordinate)
3119      {
3120          // return hyperlink if we already have one
3121          if (isset($this->hyperlinkCollection[$cellCoordinate])) {
3122              return $this->hyperlinkCollection[$cellCoordinate];
3123          }
3124  
3125          // else create hyperlink
3126          $this->hyperlinkCollection[$cellCoordinate] = new Hyperlink();
3127  
3128          return $this->hyperlinkCollection[$cellCoordinate];
3129      }
3130  
3131      /**
3132       * Set hyperlink.
3133       *
3134       * @param string $cellCoordinate Cell coordinate to insert hyperlink, eg: 'A1'
3135       *
3136       * @return $this
3137       */
3138      public function setHyperlink($cellCoordinate, ?Hyperlink $hyperlink = null)
3139      {
3140          if ($hyperlink === null) {
3141              unset($this->hyperlinkCollection[$cellCoordinate]);
3142          } else {
3143              $this->hyperlinkCollection[$cellCoordinate] = $hyperlink;
3144          }
3145  
3146          return $this;
3147      }
3148  
3149      /**
3150       * Hyperlink at a specific coordinate exists?
3151       *
3152       * @param string $coordinate eg: 'A1'
3153       *
3154       * @return bool
3155       */
3156      public function hyperlinkExists($coordinate)
3157      {
3158          return isset($this->hyperlinkCollection[$coordinate]);
3159      }
3160  
3161      /**
3162       * Get collection of hyperlinks.
3163       *
3164       * @return Hyperlink[]
3165       */
3166      public function getHyperlinkCollection()
3167      {
3168          return $this->hyperlinkCollection;
3169      }
3170  
3171      /**
3172       * Get data validation.
3173       *
3174       * @param string $cellCoordinate Cell coordinate to get data validation for, eg: 'A1'
3175       *
3176       * @return DataValidation
3177       */
3178      public function getDataValidation($cellCoordinate)
3179      {
3180          // return data validation if we already have one
3181          if (isset($this->dataValidationCollection[$cellCoordinate])) {
3182              return $this->dataValidationCollection[$cellCoordinate];
3183          }
3184  
3185          // else create data validation
3186          $this->dataValidationCollection[$cellCoordinate] = new DataValidation();
3187  
3188          return $this->dataValidationCollection[$cellCoordinate];
3189      }
3190  
3191      /**
3192       * Set data validation.
3193       *
3194       * @param string $cellCoordinate Cell coordinate to insert data validation, eg: 'A1'
3195       *
3196       * @return $this
3197       */
3198      public function setDataValidation($cellCoordinate, ?DataValidation $dataValidation = null)
3199      {
3200          if ($dataValidation === null) {
3201              unset($this->dataValidationCollection[$cellCoordinate]);
3202          } else {
3203              $this->dataValidationCollection[$cellCoordinate] = $dataValidation;
3204          }
3205  
3206          return $this;
3207      }
3208  
3209      /**
3210       * Data validation at a specific coordinate exists?
3211       *
3212       * @param string $coordinate eg: 'A1'
3213       *
3214       * @return bool
3215       */
3216      public function dataValidationExists($coordinate)
3217      {
3218          return isset($this->dataValidationCollection[$coordinate]);
3219      }
3220  
3221      /**
3222       * Get collection of data validations.
3223       *
3224       * @return DataValidation[]
3225       */
3226      public function getDataValidationCollection()
3227      {
3228          return $this->dataValidationCollection;
3229      }
3230  
3231      /**
3232       * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet.
3233       *
3234       * @param string $range
3235       *
3236       * @return string Adjusted range value
3237       */
3238      public function shrinkRangeToFit($range)
3239      {
3240          $maxCol = $this->getHighestColumn();
3241          $maxRow = $this->getHighestRow();
3242          $maxCol = Coordinate::columnIndexFromString($maxCol);
3243  
3244          $rangeBlocks = explode(' ', $range);
3245          foreach ($rangeBlocks as &$rangeSet) {
3246              $rangeBoundaries = Coordinate::getRangeBoundaries($rangeSet);
3247  
3248              if (Coordinate::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
3249                  $rangeBoundaries[0][0] = Coordinate::stringFromColumnIndex($maxCol);
3250              }
3251              if ($rangeBoundaries[0][1] > $maxRow) {
3252                  $rangeBoundaries[0][1] = $maxRow;
3253              }
3254              if (Coordinate::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
3255                  $rangeBoundaries[1][0] = Coordinate::stringFromColumnIndex($maxCol);
3256              }
3257              if ($rangeBoundaries[1][1] > $maxRow) {
3258                  $rangeBoundaries[1][1] = $maxRow;
3259              }
3260              $rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
3261          }
3262          unset($rangeSet);
3263  
3264          return implode(' ', $rangeBlocks);
3265      }
3266  
3267      /**
3268       * Get tab color.
3269       *
3270       * @return Color
3271       */
3272      public function getTabColor()
3273      {
3274          if ($this->tabColor === null) {
3275              $this->tabColor = new Color();
3276          }
3277  
3278          return $this->tabColor;
3279      }
3280  
3281      /**
3282       * Reset tab color.
3283       *
3284       * @return $this
3285       */
3286      public function resetTabColor()
3287      {
3288          $this->tabColor = null;
3289  
3290          return $this;
3291      }
3292  
3293      /**
3294       * Tab color set?
3295       *
3296       * @return bool
3297       */
3298      public function isTabColorSet()
3299      {
3300          return $this->tabColor !== null;
3301      }
3302  
3303      /**
3304       * Copy worksheet (!= clone!).
3305       *
3306       * @return static
3307       */
3308      public function copy()
3309      {
3310          return clone $this;
3311      }
3312  
3313      /**
3314       * Returns a boolean true if the specified row contains no cells. By default, this means that no cell records
3315       *          exist in the collection for this row. false will be returned otherwise.
3316       *     This rule can be modified by passing a $definitionOfEmptyFlags value:
3317       *          1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value
3318       *                  cells, then the row will be considered empty.
3319       *          2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty
3320       *                  string value cells, then the row will be considered empty.
3321       *          3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3322       *                  If the only cells in the collection are null value or empty string value cells, then the row
3323       *                  will be considered empty.
3324       *
3325       * @param int $definitionOfEmptyFlags
3326       *              Possible Flag Values are:
3327       *                  CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL
3328       *                  CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3329       */
3330      public function isEmptyRow(int $rowId, int $definitionOfEmptyFlags = 0): bool
3331      {
3332          try {
3333              $iterator = new RowIterator($this, $rowId, $rowId);
3334              $iterator->seek($rowId);
3335              $row = $iterator->current();
3336          } catch (Exception $e) {
3337              return true;
3338          }
3339  
3340          return $row->isEmpty($definitionOfEmptyFlags);
3341      }
3342  
3343      /**
3344       * Returns a boolean true if the specified column contains no cells. By default, this means that no cell records
3345       *          exist in the collection for this column. false will be returned otherwise.
3346       *     This rule can be modified by passing a $definitionOfEmptyFlags value:
3347       *          1 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL If the only cells in the collection are null value
3348       *                  cells, then the column will be considered empty.
3349       *          2 - CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL If the only cells in the collection are empty
3350       *                  string value cells, then the column will be considered empty.
3351       *          3 - CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL | CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3352       *                  If the only cells in the collection are null value or empty string value cells, then the column
3353       *                  will be considered empty.
3354       *
3355       * @param int $definitionOfEmptyFlags
3356       *              Possible Flag Values are:
3357       *                  CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL
3358       *                  CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL
3359       */
3360      public function isEmptyColumn(string $columnId, int $definitionOfEmptyFlags = 0): bool
3361      {
3362          try {
3363              $iterator = new ColumnIterator($this, $columnId, $columnId);
3364              $iterator->seek($columnId);
3365              $column = $iterator->current();
3366          } catch (Exception $e) {
3367              return true;
3368          }
3369  
3370          return $column->isEmpty($definitionOfEmptyFlags);
3371      }
3372  
3373      /**
3374       * Implement PHP __clone to create a deep clone, not just a shallow copy.
3375       */
3376      public function __clone()
3377      {
3378          // @phpstan-ignore-next-line
3379          foreach ($this as $key => $val) {
3380              if ($key == 'parent') {
3381                  continue;
3382              }
3383  
3384              if (is_object($val) || (is_array($val))) {
3385                  if ($key == 'cellCollection') {
3386                      $newCollection = $this->cellCollection->cloneCellCollection($this);
3387                      $this->cellCollection = $newCollection;
3388                  } elseif ($key == 'drawingCollection') {
3389                      $currentCollection = $this->drawingCollection;
3390                      $this->drawingCollection = new ArrayObject();
3391                      foreach ($currentCollection as $item) {
3392                          if (is_object($item)) {
3393                              $newDrawing = clone $item;
3394                              $newDrawing->setWorksheet($this);
3395                          }
3396                      }
3397                  } elseif (($key == 'autoFilter') && ($this->autoFilter instanceof AutoFilter)) {
3398                      $newAutoFilter = clone $this->autoFilter;
3399                      $this->autoFilter = $newAutoFilter;
3400                      $this->autoFilter->setParent($this);
3401                  } else {
3402                      $this->{$key} = unserialize(serialize($val));
3403                  }
3404              }
3405          }
3406      }
3407  
3408      /**
3409       * Define the code name of the sheet.
3410       *
3411       * @param string $codeName Same rule as Title minus space not allowed (but, like Excel, change
3412       *                       silently space to underscore)
3413       * @param bool $validate False to skip validation of new title. WARNING: This should only be set
3414       *                       at parse time (by Readers), where titles can be assumed to be valid.
3415       *
3416       * @return $this
3417       */
3418      public function setCodeName($codeName, $validate = true)
3419      {
3420          // Is this a 'rename' or not?
3421          if ($this->getCodeName() == $codeName) {
3422              return $this;
3423          }
3424  
3425          if ($validate) {
3426              $codeName = str_replace(' ', '_', $codeName); //Excel does this automatically without flinching, we are doing the same
3427  
3428              // Syntax check
3429              // throw an exception if not valid
3430              self::checkSheetCodeName($codeName);
3431  
3432              // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'
3433  
3434              if ($this->getParent()) {
3435                  // Is there already such sheet name?
3436                  if ($this->getParent()->sheetCodeNameExists($codeName)) {
3437                      // Use name, but append with lowest possible integer
3438  
3439                      if (Shared\StringHelper::countCharacters($codeName) > 29) {
3440                          $codeName = Shared\StringHelper::substring($codeName, 0, 29);
3441                      }
3442                      $i = 1;
3443                      while ($this->getParent()->sheetCodeNameExists($codeName . '_' . $i)) {
3444                          ++$i;
3445                          if ($i == 10) {
3446                              if (Shared\StringHelper::countCharacters($codeName) > 28) {
3447                                  $codeName = Shared\StringHelper::substring($codeName, 0, 28);
3448                              }
3449                          } elseif ($i == 100) {
3450                              if (Shared\StringHelper::countCharacters($codeName) > 27) {
3451                                  $codeName = Shared\StringHelper::substring($codeName, 0, 27);
3452                              }
3453                          }
3454                      }
3455  
3456                      $codeName .= '_' . $i; // ok, we have a valid name
3457                  }
3458              }
3459          }
3460  
3461          $this->codeName = $codeName;
3462  
3463          return $this;
3464      }
3465  
3466      /**
3467       * Return the code name of the sheet.
3468       *
3469       * @return null|string
3470       */
3471      public function getCodeName()
3472      {
3473          return $this->codeName;
3474      }
3475  
3476      /**
3477       * Sheet has a code name ?
3478       *
3479       * @return bool
3480       */
3481      public function hasCodeName()
3482      {
3483          return $this->codeName !== null;
3484      }
3485  
3486      public static function nameRequiresQuotes(string $sheetName): bool
3487      {
3488          return preg_match(self::SHEET_NAME_REQUIRES_NO_QUOTES, $sheetName) !== 1;
3489      }
3490  }