libraries/PHPExcel/PHPExcel.php

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