[Summary view] [Print] [Text view]

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