[Summary view] [Print] [Text view]

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