lib/phpexcel/PHPExcel.php

   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    ##VERSION##, ##DATE##
  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 }