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