[Summary view] [Print] [Text view]

   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  }