lib/phpexcel/PHPExcel.php

   1 <?php
   2 /**
   3  * PHPExcel
   4  *
   5  * Copyright (c) 2006 - 2012 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 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  42  */
  43 class PHPExcel
  44 {
  45         /**
  46          * Document properties
  47          *
  48          * @var PHPExcel_DocumentProperties
  49          */
  50         private $_properties;
  51
  52         /**
  53          * Document security
  54          *
  55          * @var PHPExcel_DocumentSecurity
  56          */
  57         private $_security;
  58
  59         /**
  60          * Collection of Worksheet objects
  61          *
  62          * @var PHPExcel_Worksheet[]
  63          */
  64         private $_workSheetCollection = array();
  65
  66         /**
  67          * Active sheet index
  68          *
  69          * @var int
  70          */
  71         private $_activeSheetIndex = 0;
  72
  73         /**
  74          * Named ranges
  75          *
  76          * @var PHPExcel_NamedRange[]
  77          */
  78         private $_namedRanges = array();
  79
  80         /**
  81          * CellXf supervisor
  82          *
  83          * @var PHPExcel_Style
  84          */
  85         private $_cellXfSupervisor;
  86
  87         /**
  88          * CellXf collection
  89          *
  90          * @var PHPExcel_Style[]
  91          */
  92         private $_cellXfCollection = array();
  93
  94         /**
  95          * CellStyleXf collection
  96          *
  97          * @var PHPExcel_Style[]
  98          */
  99         private $_cellStyleXfCollection = array();
 100
 101         /**
 102          * Create a new PHPExcel with one Worksheet
 103          */
 104         public function __construct()
 105         {
 106                 // Initialise worksheet collection and add one worksheet
 107                 $this->_workSheetCollection = array();
 108                 $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
 109                 $this->_activeSheetIndex = 0;
 110
 111                 // Create document properties
 112                 $this->_properties = new PHPExcel_DocumentProperties();
 113
 114                 // Create document security
 115                 $this->_security = new PHPExcel_DocumentSecurity();
 116
 117                 // Set named ranges
 118                 $this->_namedRanges = array();
 119
 120                 // Create the cellXf supervisor
 121                 $this->_cellXfSupervisor = new PHPExcel_Style(true);
 122                 $this->_cellXfSupervisor->bindParent($this);
 123
 124                 // Create the default style
 125                 $this->addCellXf(new PHPExcel_Style);
 126                 $this->addCellStyleXf(new PHPExcel_Style);
 127         }
 128
 129
 130         /**
 131          * Disconnect all worksheets from this PHPExcel workbook object,
 132          *    typically so that the PHPExcel object can be unset
 133          *
 134          */
 135         public function disconnectWorksheets() {
 136                 foreach($this->_workSheetCollection as $k => &$worksheet) {
 137                         $worksheet->disconnectCells();
 138                         $this->_workSheetCollection[$k] = null;
 139                 }
 140                 unset($worksheet);
 141                 $this->_workSheetCollection = array();
 142         }
 143
 144         /**
 145          * Get properties
 146          *
 147          * @return PHPExcel_DocumentProperties
 148          */
 149         public function getProperties()
 150         {
 151                 return $this->_properties;
 152         }
 153
 154         /**
 155          * Set properties
 156          *
 157          * @param PHPExcel_DocumentProperties   $pValue
 158          */
 159         public function setProperties(PHPExcel_DocumentProperties $pValue)
 160         {
 161                 $this->_properties = $pValue;
 162         }
 163
 164         /**
 165          * Get security
 166          *
 167          * @return PHPExcel_DocumentSecurity
 168          */
 169         public function getSecurity()
 170         {
 171                 return $this->_security;
 172         }
 173
 174         /**
 175          * Set security
 176          *
 177          * @param PHPExcel_DocumentSecurity     $pValue
 178          */
 179         public function setSecurity(PHPExcel_DocumentSecurity $pValue)
 180         {
 181                 $this->_security = $pValue;
 182         }
 183
 184         /**
 185          * Get active sheet
 186          *
 187          * @return PHPExcel_Worksheet
 188          */
 189         public function getActiveSheet()
 190         {
 191                 return $this->_workSheetCollection[$this->_activeSheetIndex];
 192         }
 193
 194     /**
 195      * Create sheet and add it to this workbook
 196      *
 197          * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 198      * @return PHPExcel_Worksheet
 199      * @throws Exception
 200      */
 201     public function createSheet($iSheetIndex = NULL)
 202     {
 203         $newSheet = new PHPExcel_Worksheet($this);
 204         $this->addSheet($newSheet, $iSheetIndex);
 205         return $newSheet;
 206     }
 207
 208     /**
 209      * Chech if a sheet with a specified name already exists
 210      *
 211      * @param string $pSheetName  Name of the worksheet to check
 212      * @return boolean
 213      */
 214     public function sheetNameExists($pSheetName)
 215     {
 216                 return ($this->getSheetByName($pSheetName) !== NULL);
 217     }
 218
 219     /**
 220      * Add sheet
 221      *
 222      * @param PHPExcel_Worksheet $pSheet
 223          * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 224      * @return PHPExcel_Worksheet
 225      * @throws Exception
 226      */
 227     public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
 228     {
 229                 if ($this->sheetNameExists($pSheet->getTitle())) {
 230                         throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first.");
 231                 }
 232
 233         if($iSheetIndex === NULL) {
 234             if ($this->_activeSheetIndex < 0) {
 235                 $this->_activeSheetIndex = 0;
 236             }
 237             $this->_workSheetCollection[] = $pSheet;
 238         } else {
 239             // Insert the sheet at the requested index
 240             array_splice(
 241                 $this->_workSheetCollection,
 242                 $iSheetIndex,
 243                 0,
 244                 array($pSheet)
 245                 );
 246
 247                         // Adjust active sheet index if necessary
 248                         if ($this->_activeSheetIndex >= $iSheetIndex) {
 249                                 ++$this->_activeSheetIndex;
 250                         }
 251         }
 252                 return $pSheet;
 253     }
 254
 255         /**
 256          * Remove sheet by index
 257          *
 258          * @param int $pIndex Active sheet index
 259          * @throws Exception
 260          */
 261         public function removeSheetByIndex($pIndex = 0)
 262         {
 263                 if ($pIndex > count($this->_workSheetCollection) - 1) {
 264                         throw new Exception("Sheet index is out of bounds.");
 265                 } else {
 266                         array_splice($this->_workSheetCollection, $pIndex, 1);
 267                 }
 268                 // Adjust active sheet index if necessary
 269                 if (($this->_activeSheetIndex >= $pIndex) &&
 270                         ($pIndex > count($this->_workSheetCollection) - 1)) {
 271                         --$this->_activeSheetIndex;
 272                 }
 273
 274         }
 275
 276         /**
 277          * Get sheet by index
 278          *
 279          * @param int $pIndex Sheet index
 280          * @return PHPExcel_Worksheet
 281          * @throws Exception
 282          */
 283         public function getSheet($pIndex = 0)
 284         {
 285                 if ($pIndex > count($this->_workSheetCollection) - 1) {
 286                         throw new Exception("Sheet index is out of bounds.");
 287                 } else {
 288                         return $this->_workSheetCollection[$pIndex];
 289                 }
 290         }
 291
 292         /**
 293          * Get all sheets
 294          *
 295          * @return PHPExcel_Worksheet[]
 296          */
 297         public function getAllSheets()
 298         {
 299                 return $this->_workSheetCollection;
 300         }
 301
 302         /**
 303          * Get sheet by name
 304          *
 305          * @param string $pName Sheet name
 306          * @return PHPExcel_Worksheet
 307          * @throws Exception
 308          */
 309         public function getSheetByName($pName = '')
 310         {
 311                 $worksheetCount = count($this->_workSheetCollection);
 312                 for ($i = 0; $i < $worksheetCount; ++$i) {
 313                         if ($this->_workSheetCollection[$i]->getTitle() == $pName) {
 314                                 return $this->_workSheetCollection[$i];
 315                         }
 316                 }
 317
 318                 return null;
 319         }
 320
 321         /**
 322          * Get index for sheet
 323          *
 324          * @param PHPExcel_Worksheet $pSheet
 325          * @return Sheet index
 326          * @throws Exception
 327          */
 328         public function getIndex(PHPExcel_Worksheet $pSheet)
 329         {
 330                 foreach ($this->_workSheetCollection as $key => $value) {
 331                         if ($value->getHashCode() == $pSheet->getHashCode()) {
 332                                 return $key;
 333                         }
 334                 }
 335         }
 336
 337     /**
 338          * Set index for sheet by sheet name.
 339          *
 340          * @param string $sheetName Sheet name to modify index for
 341          * @param int $newIndex New index for the sheet
 342          * @return New sheet index
 343          * @throws Exception
 344          */
 345     public function setIndexByName($sheetName, $newIndex)
 346     {
 347         $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
 348         $pSheet = array_splice(
 349             $this->_workSheetCollection,
 350             $oldIndex,
 351             1
 352             );
 353         array_splice(
 354             $this->_workSheetCollection,
 355             $newIndex,
 356             0,
 357             $pSheet
 358             );
 359         return $newIndex;
 360     }
 361
 362         /**
 363          * Get sheet count
 364          *
 365          * @return int
 366          */
 367         public function getSheetCount()
 368         {
 369                 return count($this->_workSheetCollection);
 370         }
 371
 372         /**
 373          * Get active sheet index
 374          *
 375          * @return int Active sheet index
 376          */
 377         public function getActiveSheetIndex()
 378         {
 379                 return $this->_activeSheetIndex;
 380         }
 381
 382         /**
 383          * Set active sheet index
 384          *
 385          * @param int $pIndex Active sheet index
 386          * @throws Exception
 387          * @return PHPExcel_Worksheet
 388          */
 389         public function setActiveSheetIndex($pIndex = 0)
 390         {
 391                 if ($pIndex > count($this->_workSheetCollection) - 1) {
 392                         throw new Exception("Active sheet index is out of bounds.");
 393                 } else {
 394                         $this->_activeSheetIndex = $pIndex;
 395                 }
 396                 return $this->getActiveSheet();
 397         }
 398
 399         /**
 400          * Set active sheet index by name
 401          *
 402          * @param string $pValue Sheet title
 403          * @return PHPExcel_Worksheet
 404          * @throws Exception
 405          */
 406         public function setActiveSheetIndexByName($pValue = '')
 407         {
 408                 if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
 409                         $this->setActiveSheetIndex($this->getIndex($worksheet));
 410                         return $worksheet;
 411                 }
 412
 413                 throw new Exception('Workbook does not contain sheet:' . $pValue);
 414         }
 415
 416         /**
 417          * Get sheet names
 418          *
 419          * @return string[]
 420          */
 421         public function getSheetNames()
 422         {
 423                 $returnValue = array();
 424                 $worksheetCount = $this->getSheetCount();
 425                 for ($i = 0; $i < $worksheetCount; ++$i) {
 426                         $returnValue[] = $this->getSheet($i)->getTitle();
 427                 }
 428
 429                 return $returnValue;
 430         }
 431
 432         /**
 433          * Add external sheet
 434          *
 435          * @param PHPExcel_Worksheet $pSheet External sheet to add
 436          * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 437          * @throws Exception
 438          * @return PHPExcel_Worksheet
 439          */
 440         public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
 441                 if ($this->sheetNameExists($pSheet->getTitle())) {
 442                         throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
 443                 }
 444
 445                 // count how many cellXfs there are in this workbook currently, we will need this below
 446                 $countCellXfs = count($this->_cellXfCollection);
 447
 448                 // copy all the shared cellXfs from the external workbook and append them to the current
 449                 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
 450                         $this->addCellXf(clone $cellXf);
 451                 }
 452
 453                 // move sheet to this workbook
 454                 $pSheet->rebindParent($this);
 455
 456                 // update the cellXfs
 457                 foreach ($pSheet->getCellCollection(false) as $cellID) {
 458                         $cell = $pSheet->getCell($cellID);
 459                         $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
 460                 }
 461
 462                 return $this->addSheet($pSheet, $iSheetIndex);
 463         }
 464
 465         /**
 466          * Get named ranges
 467          *
 468          * @return PHPExcel_NamedRange[]
 469          */
 470         public function getNamedRanges() {
 471                 return $this->_namedRanges;
 472         }
 473
 474         /**
 475          * Add named range
 476          *
 477          * @param PHPExcel_NamedRange $namedRange
 478          * @return PHPExcel
 479          */
 480         public function addNamedRange(PHPExcel_NamedRange $namedRange) {
 481                 if ($namedRange->getScope() == null) {
 482                         // global scope
 483                         $this->_namedRanges[$namedRange->getName()] = $namedRange;
 484                 } else {
 485                         // local scope
 486                         $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
 487                 }
 488                 return true;
 489         }
 490
 491         /**
 492          * Get named range
 493          *
 494          * @param string $namedRange
 495          * @param PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope
 496          * @return PHPExcel_NamedRange|null
 497          */
 498         public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 499                 $returnValue = null;
 500
 501                 if ($namedRange != '' && ($namedRange !== NULL)) {
 502                         // first look for global defined name
 503                         if (isset($this->_namedRanges[$namedRange])) {
 504                                 $returnValue = $this->_namedRanges[$namedRange];
 505                         }
 506
 507                         // then look for local defined name (has priority over global defined name if both names exist)
 508                         if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 509                                 $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
 510                         }
 511                 }
 512
 513                 return $returnValue;
 514         }
 515
 516         /**
 517          * Remove named range
 518          *
 519          * @param  string  $namedRange
 520          * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.
 521          * @return PHPExcel
 522          */
 523         public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 524                 if ($pSheet === NULL) {
 525                         if (isset($this->_namedRanges[$namedRange])) {
 526                                 unset($this->_namedRanges[$namedRange]);
 527                         }
 528                 } else {
 529                         if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 530                                 unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
 531                         }
 532                 }
 533                 return $this;
 534         }
 535
 536         /**
 537          * Get worksheet iterator
 538          *
 539          * @return PHPExcel_WorksheetIterator
 540          */
 541         public function getWorksheetIterator() {
 542                 return new PHPExcel_WorksheetIterator($this);
 543         }
 544
 545         /**
 546          * Copy workbook (!= clone!)
 547          *
 548          * @return PHPExcel
 549          */
 550         public function copy() {
 551                 $copied = clone $this;
 552
 553                 $worksheetCount = count($this->_workSheetCollection);
 554                 for ($i = 0; $i < $worksheetCount; ++$i) {
 555                         $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
 556                         $this->_workSheetCollection[$i]->rebindParent($this);
 557                 }
 558
 559                 return $copied;
 560         }
 561
 562         /**
 563          * Implement PHP __clone to create a deep clone, not just a shallow copy.
 564          */
 565         public function __clone() {
 566                 foreach($this as $key => $val) {
 567                         if (is_object($val) || (is_array($val))) {
 568                                 $this->{$key} = unserialize(serialize($val));
 569                         }
 570                 }
 571         }
 572
 573         /**
 574          * Get the workbook collection of cellXfs
 575          *
 576          * @return PHPExcel_Style[]
 577          */
 578         public function getCellXfCollection()
 579         {
 580                 return $this->_cellXfCollection;
 581         }
 582
 583         /**
 584          * Get cellXf by index
 585          *
 586          * @param int $pIndex
 587          * @return PHPExcel_Style
 588          */
 589         public function getCellXfByIndex($pIndex = 0)
 590         {
 591                 return $this->_cellXfCollection[$pIndex];
 592         }
 593
 594         /**
 595          * Get cellXf by hash code
 596          *
 597          * @param string $pValue
 598          * @return PHPExcel_Style|false
 599          */
 600         public function getCellXfByHashCode($pValue = '')
 601         {
 602                 foreach ($this->_cellXfCollection as $cellXf) {
 603                         if ($cellXf->getHashCode() == $pValue) {
 604                                 return $cellXf;
 605                         }
 606                 }
 607                 return false;
 608         }
 609
 610         /**
 611          * Get default style
 612          *
 613          * @return PHPExcel_Style
 614          * @throws Exception
 615          */
 616         public function getDefaultStyle()
 617         {
 618                 if (isset($this->_cellXfCollection[0])) {
 619                         return $this->_cellXfCollection[0];
 620                 }
 621                 throw new Exception('No default style found for this workbook');
 622         }
 623
 624         /**
 625          * Add a cellXf to the workbook
 626          *
 627          * @param PHPExcel_Style $style
 628          */
 629         public function addCellXf(PHPExcel_Style $style)
 630         {
 631                 $this->_cellXfCollection[] = $style;
 632                 $style->setIndex(count($this->_cellXfCollection) - 1);
 633         }
 634
 635         /**
 636          * Remove cellXf by index. It is ensured that all cells get their xf index updated.
 637          *
 638          * @param int $pIndex Index to cellXf
 639          * @throws Exception
 640          */
 641         public function removeCellXfByIndex($pIndex = 0)
 642         {
 643                 if ($pIndex > count($this->_cellXfCollection) - 1) {
 644                         throw new Exception("CellXf index is out of bounds.");
 645                 } else {
 646                         // first remove the cellXf
 647                         array_splice($this->_cellXfCollection, $pIndex, 1);
 648
 649                         // then update cellXf indexes for cells
 650                         foreach ($this->_workSheetCollection as $worksheet) {
 651                                 foreach ($worksheet->getCellCollection(false) as $cellID) {
 652                                         $cell = $worksheet->getCell($cellID);
 653                                         $xfIndex = $cell->getXfIndex();
 654                                         if ($xfIndex > $pIndex ) {
 655                                                 // decrease xf index by 1
 656                                                 $cell->setXfIndex($xfIndex - 1);
 657                                         } else if ($xfIndex == $pIndex) {
 658                                                 // set to default xf index 0
 659                                                 $cell->setXfIndex(0);
 660                                         }
 661                                 }
 662                         }
 663                 }
 664         }
 665
 666         /**
 667          * Get the cellXf supervisor
 668          *
 669          * @return PHPExcel_Style
 670          */
 671         public function getCellXfSupervisor()
 672         {
 673                 return $this->_cellXfSupervisor;
 674         }
 675
 676         /**
 677          * Get the workbook collection of cellStyleXfs
 678          *
 679          * @return PHPExcel_Style[]
 680          */
 681         public function getCellStyleXfCollection()
 682         {
 683                 return $this->_cellStyleXfCollection;
 684         }
 685
 686         /**
 687          * Get cellStyleXf by index
 688          *
 689          * @param int $pIndex
 690          * @return PHPExcel_Style
 691          */
 692         public function getCellStyleXfByIndex($pIndex = 0)
 693         {
 694                 return $this->_cellStyleXfCollection[$pIndex];
 695         }
 696
 697         /**
 698          * Get cellStyleXf by hash code
 699          *
 700          * @param string $pValue
 701          * @return PHPExcel_Style|false
 702          */
 703         public function getCellStyleXfByHashCode($pValue = '')
 704         {
 705                 foreach ($this->_cellXfStyleCollection as $cellStyleXf) {
 706                         if ($cellStyleXf->getHashCode() == $pValue) {
 707                                 return $cellStyleXf;
 708                         }
 709                 }
 710                 return false;
 711         }
 712
 713         /**
 714          * Add a cellStyleXf to the workbook
 715          *
 716          * @param PHPExcel_Style $pStyle
 717          */
 718         public function addCellStyleXf(PHPExcel_Style $pStyle)
 719         {
 720                 $this->_cellStyleXfCollection[] = $pStyle;
 721                 $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
 722         }
 723
 724         /**
 725          * Remove cellStyleXf by index
 726          *
 727          * @param int $pIndex
 728          * @throws Exception
 729          */
 730         public function removeCellStyleXfByIndex($pIndex = 0)
 731         {
 732                 if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
 733                         throw new Exception("CellStyleXf index is out of bounds.");
 734                 } else {
 735                         array_splice($this->_cellStyleXfCollection, $pIndex, 1);
 736                 }
 737         }
 738
 739         /**
 740          * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
 741          * and columns in the workbook
 742          */
 743         public function garbageCollect()
 744         {
 745         // how many references are there to each cellXf ?
 746                 $countReferencesCellXf = array();
 747                 foreach ($this->_cellXfCollection as $index => $cellXf) {
 748                         $countReferencesCellXf[$index] = 0;
 749                 }
 750
 751                 foreach ($this->getWorksheetIterator() as $sheet) {
 752
 753                         // from cells
 754                         foreach ($sheet->getCellCollection(false) as $cellID) {
 755                                 $cell = $sheet->getCell($cellID);
 756                                 ++$countReferencesCellXf[$cell->getXfIndex()];
 757                         }
 758
 759                         // from row dimensions
 760                         foreach ($sheet->getRowDimensions() as $rowDimension) {
 761                                 if ($rowDimension->getXfIndex() !== null) {
 762                                         ++$countReferencesCellXf[$rowDimension->getXfIndex()];
 763                                 }
 764                         }
 765
 766                         // from column dimensions
 767                         foreach ($sheet->getColumnDimensions() as $columnDimension) {
 768                                 ++$countReferencesCellXf[$columnDimension->getXfIndex()];
 769                         }
 770                 }
 771
 772                 // remove cellXfs without references and create mapping so we can update xfIndex
 773                 // for all cells and columns
 774                 $countNeededCellXfs = 0;
 775                 foreach ($this->_cellXfCollection as $index => $cellXf) {
 776                         if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
 777                                 ++$countNeededCellXfs;
 778                         } else {
 779                                 unset($this->_cellXfCollection[$index]);
 780                         }
 781                         $map[$index] = $countNeededCellXfs - 1;
 782                 }
 783                 $this->_cellXfCollection = array_values($this->_cellXfCollection);
 784
 785                 // update the index for all cellXfs
 786                 foreach ($this->_cellXfCollection as $i => $cellXf) {
 787                         $cellXf->setIndex($i);
 788                 }
 789
 790                 // make sure there is always at least one cellXf (there should be)
 791                 if (empty($this->_cellXfCollection)) {
 792                         $this->_cellXfCollection[] = new PHPExcel_Style();
 793                 }
 794
 795                 // update the xfIndex for all cells, row dimensions, column dimensions
 796                 foreach ($this->getWorksheetIterator() as $sheet) {
 797
 798                         // for all cells
 799                         foreach ($sheet->getCellCollection(false) as $cellID) {
 800                                 $cell = $sheet->getCell($cellID);
 801                                 $cell->setXfIndex( $map[$cell->getXfIndex()] );
 802                         }
 803
 804                         // for all row dimensions
 805                         foreach ($sheet->getRowDimensions() as $rowDimension) {
 806                                 if ($rowDimension->getXfIndex() !== null) {
 807                                         $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
 808                                 }
 809                         }
 810
 811                         // for all column dimensions
 812                         foreach ($sheet->getColumnDimensions() as $columnDimension) {
 813                                 $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
 814                         }
 815                 }
 816
 817                 // also do garbage collection for all the sheets
 818                 foreach ($this->getWorksheetIterator() as $sheet) {
 819                         $sheet->garbageCollect();
 820                 }
 821         }
 822
 823 }