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
  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: /** PHPExcel root directory */
  30: if (!defined('PHPEXCEL_ROOT')) {
  31:     define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
  32:     require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  33: }
  34: 
  35: 
  36: /**
  37:  * PHPExcel
  38:  *
  39:  * @category   PHPExcel
  40:  * @package    PHPExcel
  41:  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  42:  */
  43: class PHPExcel
  44: {
  45:     /**
  46:      * Unique ID
  47:      *
  48:      * @var string
  49:      */
  50:     private $_uniqueID;
  51: 
  52:     /**
  53:      * Document properties
  54:      *
  55:      * @var PHPExcel_DocumentProperties
  56:      */
  57:     private $_properties;
  58: 
  59:     /**
  60:      * Document security
  61:      *
  62:      * @var PHPExcel_DocumentSecurity
  63:      */
  64:     private $_security;
  65: 
  66:     /**
  67:      * Collection of Worksheet objects
  68:      *
  69:      * @var PHPExcel_Worksheet[]
  70:      */
  71:     private $_workSheetCollection = array();
  72: 
  73:     /**
  74:      * Calculation Engine
  75:      *
  76:      * @var PHPExcel_Calculation
  77:      */
  78:     private $_calculationEngine = NULL;
  79: 
  80:     /**
  81:      * Active sheet index
  82:      *
  83:      * @var int
  84:      */
  85:     private $_activeSheetIndex = 0;
  86: 
  87:     /**
  88:      * Named ranges
  89:      *
  90:      * @var PHPExcel_NamedRange[]
  91:      */
  92:     private $_namedRanges = array();
  93: 
  94:     /**
  95:      * CellXf supervisor
  96:      *
  97:      * @var PHPExcel_Style
  98:      */
  99:     private $_cellXfSupervisor;
 100: 
 101:     /**
 102:      * CellXf collection
 103:      *
 104:      * @var PHPExcel_Style[]
 105:      */
 106:     private $_cellXfCollection = array();
 107: 
 108:     /**
 109:      * CellStyleXf collection
 110:      *
 111:      * @var PHPExcel_Style[]
 112:      */
 113:     private $_cellStyleXfCollection = array();
 114: 
 115:     /**
 116:     * _hasMacros : this workbook have macros ?
 117:     *
 118:     * @var bool
 119:     */
 120:     private $_hasMacros = FALSE;
 121: 
 122:     /**
 123:     * _macrosCode : all macros code (the vbaProject.bin file, this include form, code,  etc.), NULL if no macro
 124:     *
 125:     * @var binary
 126:     */
 127:     private $_macrosCode=NULL;
 128:     /**
 129:     * _macrosCertificate : if macros are signed, contains vbaProjectSignature.bin file, NULL if not signed
 130:     *
 131:     * @var binary
 132:     */
 133:     private $_macrosCertificate=NULL;
 134: 
 135:     /**
 136:     * _ribbonXMLData : NULL if workbook is'nt Excel 2007 or not contain a customized UI
 137:     *
 138:     * @var NULL|string
 139:     */
 140:     private $_ribbonXMLData=NULL;
 141: 
 142:     /**
 143:     * _ribbonBinObjects : NULL if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
 144:     * ignored if $_ribbonXMLData is null
 145:     *
 146:     * @var NULL|array
 147:     */
 148:     private $_ribbonBinObjects=NULL;
 149: 
 150:     /**
 151:     * The workbook has macros ?
 152:     *
 153:     * @return true if workbook has macros, false if not
 154:     */
 155:     public function hasMacros(){
 156:         return $this->_hasMacros;
 157:     }
 158: 
 159:     /**
 160:     * Define if a workbook has macros
 161:     *
 162:     * @param true|false
 163:     */
 164:     public function setHasMacros($hasMacros=false){
 165:         $this->_hasMacros=(bool)$hasMacros;
 166:     }
 167: 
 168:     /**
 169:     * Set the macros code
 170:     *
 171:     * @param binary string|null
 172:     */
 173:     public function setMacrosCode($MacrosCode){
 174:         $this->_macrosCode=$MacrosCode;
 175:         $this->setHasMacros(!is_null($MacrosCode));
 176:     }
 177: 
 178:     /**
 179:     * Return the macros code
 180:     *
 181:     * @return binary|null
 182:     */
 183:     public function getMacrosCode(){
 184:         return $this->_macrosCode;
 185:     }
 186: 
 187:     /**
 188:     * Set the macros certificate
 189:     *
 190:     * @param binary|null
 191:     */
 192:     public function setMacrosCertificate($Certificate=NULL){
 193:         $this->_macrosCertificate=$Certificate;
 194:     }
 195: 
 196:     /**
 197:     * Is the project signed ?
 198:     *
 199:     * @return true|false
 200:     */
 201:     public function hasMacrosCertificate(){
 202:         return !is_null($this->_macrosCertificate);
 203:     }
 204: 
 205:     /**
 206:     * Return the macros certificate
 207:     *
 208:     * @return binary|null
 209:     */
 210:     public function getMacrosCertificate(){
 211:         return $this->_macrosCertificate;
 212:     }
 213: 
 214:     /**
 215:     * Remove all macros, certificate from spreadsheet
 216:     *
 217:     * @param none
 218:     * @return void
 219:     */
 220:     public function discardMacros(){
 221:         $this->_hasMacros=false;
 222:         $this->_macrosCode=NULL;
 223:         $this->_macrosCertificate=NULL;
 224:     }
 225: 
 226:     /**
 227:     * set ribbon XML data
 228:     *
 229:     */
 230:     public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
 231:         if(!is_null($Target) && !is_null($XMLData)){
 232:             $this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
 233:         }else{
 234:             $this->_ribbonXMLData=NULL;
 235:         }
 236:     }
 237: 
 238:     /**
 239:     * retrieve ribbon XML Data
 240:     *
 241:     * return string|null|array
 242:     */
 243:     public function getRibbonXMLData($What='all'){//we need some constants here...
 244:         $ReturnData=NULL;
 245:         $What=strtolower($What);
 246:         switch($What){
 247:         case 'all':
 248:             $ReturnData=$this->_ribbonXMLData;
 249:             break;
 250:         case 'target':
 251:         case 'data':
 252:             if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
 253:                 $ReturnData=$this->_ribbonXMLData[$What];
 254:             }//else $ReturnData stay at null
 255:             break;
 256:         }//default: $ReturnData at null
 257:         return $ReturnData;
 258:     }
 259: 
 260:     /**
 261:     * store binaries ribbon objects (pictures)
 262:     *
 263:     */
 264:     public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
 265:         if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
 266:             $this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
 267:         }else{
 268:             $this->_ribbonBinObjects=NULL;
 269:         }
 270:     }
 271:     /**
 272:     * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function)
 273:     *
 274:     */
 275:     private function _getExtensionOnly($ThePath){
 276:         return pathinfo($ThePath, PATHINFO_EXTENSION);
 277:     }
 278: 
 279:     /**
 280:     * retrieve Binaries Ribbon Objects
 281:     *
 282:     */
 283:     public function getRibbonBinObjects($What='all'){
 284:         $ReturnData=NULL;
 285:         $What=strtolower($What);
 286:         switch($What){
 287:         case 'all':
 288:             return $this->_ribbonBinObjects;
 289:             break;
 290:         case 'names':
 291:         case 'data':
 292:             if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
 293:                 $ReturnData=$this->_ribbonBinObjects[$What];
 294:             }
 295:             break;
 296:         case 'types':
 297:             if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
 298:                 $tmpTypes=array_keys($this->_ribbonBinObjects['data']);
 299:                 $ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
 300:             }else
 301:                 $ReturnData=array();//the caller want an array... not null if empty
 302:             break;
 303:         }
 304:         return $ReturnData;
 305:     }
 306: 
 307:     /**
 308:     * This workbook have a custom UI ?
 309:     *
 310:     * @return true|false
 311:     */
 312:     public function hasRibbon(){
 313:         return !is_null($this->_ribbonXMLData);
 314:     }
 315: 
 316:     /**
 317:     * This workbook have additionnal object for the ribbon ?
 318:     *
 319:     * @return true|false
 320:     */
 321:     public function hasRibbonBinObjects(){
 322:         return !is_null($this->_ribbonBinObjects);
 323:     }
 324: 
 325:     /**
 326:      * Check if a sheet with a specified code name already exists
 327:      *
 328:      * @param string $pSheetCodeName  Name of the worksheet to check
 329:      * @return boolean
 330:      */
 331:     public function sheetCodeNameExists($pSheetCodeName)
 332:     {
 333:         return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
 334:     }
 335: 
 336:     /**
 337:      * Get sheet by code name. Warning : sheet don't have always a code name !
 338:      *
 339:      * @param string $pName Sheet name
 340:      * @return PHPExcel_Worksheet
 341:      */
 342:     public function getSheetByCodeName($pName = '')
 343:     {
 344:         $worksheetCount = count($this->_workSheetCollection);
 345:         for ($i = 0; $i < $worksheetCount; ++$i) {
 346:             if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
 347:                 return $this->_workSheetCollection[$i];
 348:             }
 349:         }
 350: 
 351:         return null;
 352:     }
 353: 
 354:      /**
 355:      * Create a new PHPExcel with one Worksheet
 356:      */
 357:     public function __construct()
 358:     {
 359:         $this->_uniqueID = uniqid();
 360:         $this->_calculationEngine   = PHPExcel_Calculation::getInstance($this);
 361: 
 362:         // Initialise worksheet collection and add one worksheet
 363:         $this->_workSheetCollection = array();
 364:         $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
 365:         $this->_activeSheetIndex = 0;
 366: 
 367:         // Create document properties
 368:         $this->_properties = new PHPExcel_DocumentProperties();
 369: 
 370:         // Create document security
 371:         $this->_security = new PHPExcel_DocumentSecurity();
 372: 
 373:         // Set named ranges
 374:         $this->_namedRanges = array();
 375: 
 376:         // Create the cellXf supervisor
 377:         $this->_cellXfSupervisor = new PHPExcel_Style(true);
 378:         $this->_cellXfSupervisor->bindParent($this);
 379: 
 380:         // Create the default style
 381:         $this->addCellXf(new PHPExcel_Style);
 382:         $this->addCellStyleXf(new PHPExcel_Style);
 383:     }
 384: 
 385:     /**
 386:      * Code to execute when this worksheet is unset()
 387:      *
 388:      */
 389:     public function __destruct() {
 390:         PHPExcel_Calculation::unsetInstance($this);
 391:         $this->disconnectWorksheets();
 392:     }    //    function __destruct()
 393: 
 394:     /**
 395:      * Disconnect all worksheets from this PHPExcel workbook object,
 396:      *    typically so that the PHPExcel object can be unset
 397:      *
 398:      */
 399:     public function disconnectWorksheets()
 400:     {
 401:         $worksheet = NULL;
 402:         foreach($this->_workSheetCollection as $k => &$worksheet) {
 403:             $worksheet->disconnectCells();
 404:             $this->_workSheetCollection[$k] = null;
 405:         }
 406:         unset($worksheet);
 407:         $this->_workSheetCollection = array();
 408:     }
 409: 
 410:     /**
 411:      * Return the calculation engine for this worksheet
 412:      *
 413:      * @return PHPExcel_Calculation
 414:      */
 415:     public function getCalculationEngine()
 416:     {
 417:         return $this->_calculationEngine;
 418:     }   //  function getCellCacheController()
 419: 
 420:     /**
 421:      * Get properties
 422:      *
 423:      * @return PHPExcel_DocumentProperties
 424:      */
 425:     public function getProperties()
 426:     {
 427:         return $this->_properties;
 428:     }
 429: 
 430:     /**
 431:      * Set properties
 432:      *
 433:      * @param PHPExcel_DocumentProperties    $pValue
 434:      */
 435:     public function setProperties(PHPExcel_DocumentProperties $pValue)
 436:     {
 437:         $this->_properties = $pValue;
 438:     }
 439: 
 440:     /**
 441:      * Get security
 442:      *
 443:      * @return PHPExcel_DocumentSecurity
 444:      */
 445:     public function getSecurity()
 446:     {
 447:         return $this->_security;
 448:     }
 449: 
 450:     /**
 451:      * Set security
 452:      *
 453:      * @param PHPExcel_DocumentSecurity    $pValue
 454:      */
 455:     public function setSecurity(PHPExcel_DocumentSecurity $pValue)
 456:     {
 457:         $this->_security = $pValue;
 458:     }
 459: 
 460:     /**
 461:      * Get active sheet
 462:      *
 463:      * @return PHPExcel_Worksheet
 464:      */
 465:     public function getActiveSheet()
 466:     {
 467:         return $this->_workSheetCollection[$this->_activeSheetIndex];
 468:     }
 469: 
 470:     /**
 471:      * Create sheet and add it to this workbook
 472:      *
 473:      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 474:      * @return PHPExcel_Worksheet
 475:      * @throws PHPExcel_Exception
 476:      */
 477:     public function createSheet($iSheetIndex = NULL)
 478:     {
 479:         $newSheet = new PHPExcel_Worksheet($this);
 480:         $this->addSheet($newSheet, $iSheetIndex);
 481:         return $newSheet;
 482:     }
 483: 
 484:     /**
 485:      * Check if a sheet with a specified name already exists
 486:      *
 487:      * @param  string $pSheetName  Name of the worksheet to check
 488:      * @return boolean
 489:      */
 490:     public function sheetNameExists($pSheetName)
 491:     {
 492:         return ($this->getSheetByName($pSheetName) !== NULL);
 493:     }
 494: 
 495:     /**
 496:      * Add sheet
 497:      *
 498:      * @param  PHPExcel_Worksheet $pSheet
 499:      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 500:      * @return PHPExcel_Worksheet
 501:      * @throws PHPExcel_Exception
 502:      */
 503:     public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
 504:     {
 505:         if ($this->sheetNameExists($pSheet->getTitle())) {
 506:             throw new PHPExcel_Exception(
 507:                 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
 508:             );
 509:         }
 510: 
 511:         if($iSheetIndex === NULL) {
 512:             if ($this->_activeSheetIndex < 0) {
 513:                 $this->_activeSheetIndex = 0;
 514:             }
 515:             $this->_workSheetCollection[] = $pSheet;
 516:         } else {
 517:             // Insert the sheet at the requested index
 518:             array_splice(
 519:                 $this->_workSheetCollection,
 520:                 $iSheetIndex,
 521:                 0,
 522:                 array($pSheet)
 523:                 );
 524: 
 525:             // Adjust active sheet index if necessary
 526:             if ($this->_activeSheetIndex >= $iSheetIndex) {
 527:                 ++$this->_activeSheetIndex;
 528:             }
 529:         }
 530: 
 531:         if ($pSheet->getParent() === null) {
 532:             $pSheet->rebindParent($this);
 533:         }
 534: 
 535:         return $pSheet;
 536:     }
 537: 
 538:     /**
 539:      * Remove sheet by index
 540:      *
 541:      * @param  int $pIndex Active sheet index
 542:      * @throws PHPExcel_Exception
 543:      */
 544:     public function removeSheetByIndex($pIndex = 0)
 545:     {
 546: 
 547:         $numSheets = count($this->_workSheetCollection);
 548: 
 549:         if ($pIndex > $numSheets - 1) {
 550:             throw new PHPExcel_Exception(
 551:                 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
 552:             );
 553:         } else {
 554:             array_splice($this->_workSheetCollection, $pIndex, 1);
 555:         }
 556:         // Adjust active sheet index if necessary
 557:         if (($this->_activeSheetIndex >= $pIndex) &&
 558:             ($pIndex > count($this->_workSheetCollection) - 1)) {
 559:             --$this->_activeSheetIndex;
 560:         }
 561: 
 562:     }
 563: 
 564:     /**
 565:      * Get sheet by index
 566:      *
 567:      * @param  int $pIndex Sheet index
 568:      * @return PHPExcel_Worksheet
 569:      * @throws PHPExcel_Exception
 570:      */
 571:     public function getSheet($pIndex = 0)
 572:     {
 573: 
 574:         $numSheets = count($this->_workSheetCollection);
 575: 
 576:         if ($pIndex > $numSheets - 1) {
 577:             throw new PHPExcel_Exception(
 578:                 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
 579:             );
 580:         } else {
 581:             return $this->_workSheetCollection[$pIndex];
 582:         }
 583:     }
 584: 
 585:     /**
 586:      * Get all sheets
 587:      *
 588:      * @return PHPExcel_Worksheet[]
 589:      */
 590:     public function getAllSheets()
 591:     {
 592:         return $this->_workSheetCollection;
 593:     }
 594: 
 595:     /**
 596:      * Get sheet by name
 597:      *
 598:      * @param  string $pName Sheet name
 599:      * @return PHPExcel_Worksheet
 600:      */
 601:     public function getSheetByName($pName = '')
 602:     {
 603:         $worksheetCount = count($this->_workSheetCollection);
 604:         for ($i = 0; $i < $worksheetCount; ++$i) {
 605:             if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
 606:                 return $this->_workSheetCollection[$i];
 607:             }
 608:         }
 609: 
 610:         return NULL;
 611:     }
 612: 
 613:     /**
 614:      * Get index for sheet
 615:      *
 616:      * @param  PHPExcel_Worksheet $pSheet
 617:      * @return Sheet index
 618:      * @throws PHPExcel_Exception
 619:      */
 620:     public function getIndex(PHPExcel_Worksheet $pSheet)
 621:     {
 622:         foreach ($this->_workSheetCollection as $key => $value) {
 623:             if ($value->getHashCode() == $pSheet->getHashCode()) {
 624:                 return $key;
 625:             }
 626:         }
 627: 
 628:         throw new PHPExcel_Exception("Sheet does not exist.");
 629:     }
 630: 
 631:     /**
 632:      * Set index for sheet by sheet name.
 633:      *
 634:      * @param  string $sheetName Sheet name to modify index for
 635:      * @param  int $newIndex New index for the sheet
 636:      * @return New sheet index
 637:      * @throws PHPExcel_Exception
 638:      */
 639:     public function setIndexByName($sheetName, $newIndex)
 640:     {
 641:         $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
 642:         $pSheet = array_splice(
 643:             $this->_workSheetCollection,
 644:             $oldIndex,
 645:             1
 646:         );
 647:         array_splice(
 648:             $this->_workSheetCollection,
 649:             $newIndex,
 650:             0,
 651:             $pSheet
 652:         );
 653:         return $newIndex;
 654:     }
 655: 
 656:     /**
 657:      * Get sheet count
 658:      *
 659:      * @return int
 660:      */
 661:     public function getSheetCount()
 662:     {
 663:         return count($this->_workSheetCollection);
 664:     }
 665: 
 666:     /**
 667:      * Get active sheet index
 668:      *
 669:      * @return int Active sheet index
 670:      */
 671:     public function getActiveSheetIndex()
 672:     {
 673:         return $this->_activeSheetIndex;
 674:     }
 675: 
 676:     /**
 677:      * Set active sheet index
 678:      *
 679:      * @param  int $pIndex Active sheet index
 680:      * @throws PHPExcel_Exception
 681:      * @return PHPExcel_Worksheet
 682:      */
 683:     public function setActiveSheetIndex($pIndex = 0)
 684:     {
 685:             $numSheets = count($this->_workSheetCollection);
 686: 
 687:         if ($pIndex > $numSheets - 1) {
 688:             throw new PHPExcel_Exception(
 689:                 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
 690:             );
 691:         } else {
 692:             $this->_activeSheetIndex = $pIndex;
 693:         }
 694:         return $this->getActiveSheet();
 695:     }
 696: 
 697:     /**
 698:      * Set active sheet index by name
 699:      *
 700:      * @param  string $pValue Sheet title
 701:      * @return PHPExcel_Worksheet
 702:      * @throws PHPExcel_Exception
 703:      */
 704:     public function setActiveSheetIndexByName($pValue = '')
 705:     {
 706:         if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
 707:             $this->setActiveSheetIndex($this->getIndex($worksheet));
 708:             return $worksheet;
 709:         }
 710: 
 711:         throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
 712:     }
 713: 
 714:     /**
 715:      * Get sheet names
 716:      *
 717:      * @return string[]
 718:      */
 719:     public function getSheetNames()
 720:     {
 721:         $returnValue = array();
 722:         $worksheetCount = $this->getSheetCount();
 723:         for ($i = 0; $i < $worksheetCount; ++$i) {
 724:             $returnValue[] = $this->getSheet($i)->getTitle();
 725:         }
 726: 
 727:         return $returnValue;
 728:     }
 729: 
 730:     /**
 731:      * Add external sheet
 732:      *
 733:      * @param  PHPExcel_Worksheet $pSheet External sheet to add
 734:      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 735:      * @throws PHPExcel_Exception
 736:      * @return PHPExcel_Worksheet
 737:      */
 738:     public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
 739:         if ($this->sheetNameExists($pSheet->getTitle())) {
 740:             throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
 741:         }
 742: 
 743:         // count how many cellXfs there are in this workbook currently, we will need this below
 744:         $countCellXfs = count($this->_cellXfCollection);
 745: 
 746:         // copy all the shared cellXfs from the external workbook and append them to the current
 747:         foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
 748:             $this->addCellXf(clone $cellXf);
 749:         }
 750: 
 751:         // move sheet to this workbook
 752:         $pSheet->rebindParent($this);
 753: 
 754:         // update the cellXfs
 755:         foreach ($pSheet->getCellCollection(false) as $cellID) {
 756:             $cell = $pSheet->getCell($cellID);
 757:             $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
 758:         }
 759: 
 760:         return $this->addSheet($pSheet, $iSheetIndex);
 761:     }
 762: 
 763:     /**
 764:      * Get named ranges
 765:      *
 766:      * @return PHPExcel_NamedRange[]
 767:      */
 768:     public function getNamedRanges() {
 769:         return $this->_namedRanges;
 770:     }
 771: 
 772:     /**
 773:      * Add named range
 774:      *
 775:      * @param  PHPExcel_NamedRange $namedRange
 776:      * @return PHPExcel
 777:      */
 778:     public function addNamedRange(PHPExcel_NamedRange $namedRange) {
 779:         if ($namedRange->getScope() == null) {
 780:             // global scope
 781:             $this->_namedRanges[$namedRange->getName()] = $namedRange;
 782:         } else {
 783:             // local scope
 784:             $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
 785:         }
 786:         return true;
 787:     }
 788: 
 789:     /**
 790:      * Get named range
 791:      *
 792:      * @param  string $namedRange
 793:      * @param  PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope
 794:      * @return PHPExcel_NamedRange|null
 795:      */
 796:     public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 797:         $returnValue = null;
 798: 
 799:         if ($namedRange != '' && ($namedRange !== NULL)) {
 800:             // first look for global defined name
 801:             if (isset($this->_namedRanges[$namedRange])) {
 802:                 $returnValue = $this->_namedRanges[$namedRange];
 803:             }
 804: 
 805:             // then look for local defined name (has priority over global defined name if both names exist)
 806:             if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 807:                 $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
 808:             }
 809:         }
 810: 
 811:         return $returnValue;
 812:     }
 813: 
 814:     /**
 815:      * Remove named range
 816:      *
 817:      * @param  string  $namedRange
 818:      * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.
 819:      * @return PHPExcel
 820:      */
 821:     public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 822:         if ($pSheet === NULL) {
 823:             if (isset($this->_namedRanges[$namedRange])) {
 824:                 unset($this->_namedRanges[$namedRange]);
 825:             }
 826:         } else {
 827:             if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 828:                 unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
 829:             }
 830:         }
 831:         return $this;
 832:     }
 833: 
 834:     /**
 835:      * Get worksheet iterator
 836:      *
 837:      * @return PHPExcel_WorksheetIterator
 838:      */
 839:     public function getWorksheetIterator() {
 840:         return new PHPExcel_WorksheetIterator($this);
 841:     }
 842: 
 843:     /**
 844:      * Copy workbook (!= clone!)
 845:      *
 846:      * @return PHPExcel
 847:      */
 848:     public function copy() {
 849:         $copied = clone $this;
 850: 
 851:         $worksheetCount = count($this->_workSheetCollection);
 852:         for ($i = 0; $i < $worksheetCount; ++$i) {
 853:             $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
 854:             $this->_workSheetCollection[$i]->rebindParent($this);
 855:         }
 856: 
 857:         return $copied;
 858:     }
 859: 
 860:     /**
 861:      * Implement PHP __clone to create a deep clone, not just a shallow copy.
 862:      */
 863:     public function __clone() {
 864:         foreach($this as $key => $val) {
 865:             if (is_object($val) || (is_array($val))) {
 866:                 $this->{$key} = unserialize(serialize($val));
 867:             }
 868:         }
 869:     }
 870: 
 871:     /**
 872:      * Get the workbook collection of cellXfs
 873:      *
 874:      * @return PHPExcel_Style[]
 875:      */
 876:     public function getCellXfCollection()
 877:     {
 878:         return $this->_cellXfCollection;
 879:     }
 880: 
 881:     /**
 882:      * Get cellXf by index
 883:      *
 884:      * @param  int $pIndex
 885:      * @return PHPExcel_Style
 886:      */
 887:     public function getCellXfByIndex($pIndex = 0)
 888:     {
 889:         return $this->_cellXfCollection[$pIndex];
 890:     }
 891: 
 892:     /**
 893:      * Get cellXf by hash code
 894:      *
 895:      * @param  string $pValue
 896:      * @return PHPExcel_Style|false
 897:      */
 898:     public function getCellXfByHashCode($pValue = '')
 899:     {
 900:         foreach ($this->_cellXfCollection as $cellXf) {
 901:             if ($cellXf->getHashCode() == $pValue) {
 902:                 return $cellXf;
 903:             }
 904:         }
 905:         return false;
 906:     }
 907: 
 908:     /**
 909:      * Check if style exists in style collection
 910:      *
 911:      * @param  PHPExcel_Style $pCellStyle
 912:      * @return boolean
 913:      */
 914:     public function cellXfExists($pCellStyle = null)
 915:     {
 916:         return in_array($pCellStyle, $this->_cellXfCollection, true);
 917:     }
 918: 
 919:     /**
 920:      * Get default style
 921:      *
 922:      * @return PHPExcel_Style
 923:      * @throws PHPExcel_Exception
 924:      */
 925:     public function getDefaultStyle()
 926:     {
 927:         if (isset($this->_cellXfCollection[0])) {
 928:             return $this->_cellXfCollection[0];
 929:         }
 930:         throw new PHPExcel_Exception('No default style found for this workbook');
 931:     }
 932: 
 933:     /**
 934:      * Add a cellXf to the workbook
 935:      *
 936:      * @param PHPExcel_Style $style
 937:      */
 938:     public function addCellXf(PHPExcel_Style $style)
 939:     {
 940:         $this->_cellXfCollection[] = $style;
 941:         $style->setIndex(count($this->_cellXfCollection) - 1);
 942:     }
 943: 
 944:     /**
 945:      * Remove cellXf by index. It is ensured that all cells get their xf index updated.
 946:      *
 947:      * @param  int $pIndex Index to cellXf
 948:      * @throws PHPExcel_Exception
 949:      */
 950:     public function removeCellXfByIndex($pIndex = 0)
 951:     {
 952:         if ($pIndex > count($this->_cellXfCollection) - 1) {
 953:             throw new PHPExcel_Exception("CellXf index is out of bounds.");
 954:         } else {
 955:             // first remove the cellXf
 956:             array_splice($this->_cellXfCollection, $pIndex, 1);
 957: 
 958:             // then update cellXf indexes for cells
 959:             foreach ($this->_workSheetCollection as $worksheet) {
 960:                 foreach ($worksheet->getCellCollection(false) as $cellID) {
 961:                     $cell = $worksheet->getCell($cellID);
 962:                     $xfIndex = $cell->getXfIndex();
 963:                     if ($xfIndex > $pIndex ) {
 964:                         // decrease xf index by 1
 965:                         $cell->setXfIndex($xfIndex - 1);
 966:                     } else if ($xfIndex == $pIndex) {
 967:                         // set to default xf index 0
 968:                         $cell->setXfIndex(0);
 969:                     }
 970:                 }
 971:             }
 972:         }
 973:     }
 974: 
 975:     /**
 976:      * Get the cellXf supervisor
 977:      *
 978:      * @return PHPExcel_Style
 979:      */
 980:     public function getCellXfSupervisor()
 981:     {
 982:         return $this->_cellXfSupervisor;
 983:     }
 984: 
 985:     /**
 986:      * Get the workbook collection of cellStyleXfs
 987:      *
 988:      * @return PHPExcel_Style[]
 989:      */
 990:     public function getCellStyleXfCollection()
 991:     {
 992:         return $this->_cellStyleXfCollection;
 993:     }
 994: 
 995:     /**
 996:      * Get cellStyleXf by index
 997:      *
 998:      * @param  int $pIndex
 999:      * @return PHPExcel_Style
1000:      */
1001:     public function getCellStyleXfByIndex($pIndex = 0)
1002:     {
1003:         return $this->_cellStyleXfCollection[$pIndex];
1004:     }
1005: 
1006:     /**
1007:      * Get cellStyleXf by hash code
1008:      *
1009:      * @param  string $pValue
1010:      * @return PHPExcel_Style|false
1011:      */
1012:     public function getCellStyleXfByHashCode($pValue = '')
1013:     {
1014:         foreach ($this->_cellXfStyleCollection as $cellStyleXf) {
1015:             if ($cellStyleXf->getHashCode() == $pValue) {
1016:                 return $cellStyleXf;
1017:             }
1018:         }
1019:         return false;
1020:     }
1021: 
1022:     /**
1023:      * Add a cellStyleXf to the workbook
1024:      *
1025:      * @param PHPExcel_Style $pStyle
1026:      */
1027:     public function addCellStyleXf(PHPExcel_Style $pStyle)
1028:     {
1029:         $this->_cellStyleXfCollection[] = $pStyle;
1030:         $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
1031:     }
1032: 
1033:     /**
1034:      * Remove cellStyleXf by index
1035:      *
1036:      * @param int $pIndex
1037:      * @throws PHPExcel_Exception
1038:      */
1039:     public function removeCellStyleXfByIndex($pIndex = 0)
1040:     {
1041:         if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
1042:             throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
1043:         } else {
1044:             array_splice($this->_cellStyleXfCollection, $pIndex, 1);
1045:         }
1046:     }
1047: 
1048:     /**
1049:      * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
1050:      * and columns in the workbook
1051:      */
1052:     public function garbageCollect()
1053:     {
1054:         // how many references are there to each cellXf ?
1055:         $countReferencesCellXf = array();
1056:         foreach ($this->_cellXfCollection as $index => $cellXf) {
1057:             $countReferencesCellXf[$index] = 0;
1058:         }
1059: 
1060:         foreach ($this->getWorksheetIterator() as $sheet) {
1061: 
1062:             // from cells
1063:             foreach ($sheet->getCellCollection(false) as $cellID) {
1064:                 $cell = $sheet->getCell($cellID);
1065:                 ++$countReferencesCellXf[$cell->getXfIndex()];
1066:             }
1067: 
1068:             // from row dimensions
1069:             foreach ($sheet->getRowDimensions() as $rowDimension) {
1070:                 if ($rowDimension->getXfIndex() !== null) {
1071:                     ++$countReferencesCellXf[$rowDimension->getXfIndex()];
1072:                 }
1073:             }
1074: 
1075:             // from column dimensions
1076:             foreach ($sheet->getColumnDimensions() as $columnDimension) {
1077:                 ++$countReferencesCellXf[$columnDimension->getXfIndex()];
1078:             }
1079:         }
1080: 
1081:         // remove cellXfs without references and create mapping so we can update xfIndex
1082:         // for all cells and columns
1083:         $countNeededCellXfs = 0;
1084:         foreach ($this->_cellXfCollection as $index => $cellXf) {
1085:             if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
1086:                 ++$countNeededCellXfs;
1087:             } else {
1088:                 unset($this->_cellXfCollection[$index]);
1089:             }
1090:             $map[$index] = $countNeededCellXfs - 1;
1091:         }
1092:         $this->_cellXfCollection = array_values($this->_cellXfCollection);
1093: 
1094:         // update the index for all cellXfs
1095:         foreach ($this->_cellXfCollection as $i => $cellXf) {
1096:             $cellXf->setIndex($i);
1097:         }
1098: 
1099:         // make sure there is always at least one cellXf (there should be)
1100:         if (empty($this->_cellXfCollection)) {
1101:             $this->_cellXfCollection[] = new PHPExcel_Style();
1102:         }
1103: 
1104:         // update the xfIndex for all cells, row dimensions, column dimensions
1105:         foreach ($this->getWorksheetIterator() as $sheet) {
1106: 
1107:             // for all cells
1108:             foreach ($sheet->getCellCollection(false) as $cellID) {
1109:                 $cell = $sheet->getCell($cellID);
1110:                 $cell->setXfIndex( $map[$cell->getXfIndex()] );
1111:             }
1112: 
1113:             // for all row dimensions
1114:             foreach ($sheet->getRowDimensions() as $rowDimension) {
1115:                 if ($rowDimension->getXfIndex() !== null) {
1116:                     $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
1117:                 }
1118:             }
1119: 
1120:             // for all column dimensions
1121:             foreach ($sheet->getColumnDimensions() as $columnDimension) {
1122:                 $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
1123:             }
1124: 
1125:             // also do garbage collection for all the sheets
1126:             $sheet->garbageCollect();
1127:         }
1128:     }
1129: 
1130:     /**
1131:      * Return the unique ID value assigned to this spreadsheet workbook
1132:      *
1133:      * @return string
1134:      */
1135:     public function getID() {
1136:         return $this->_uniqueID;
1137:     }
1138: 
1139: }
1140: