[Summary view]

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

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