5 * Copyright (c) 2006 - 2009 PHPExcel
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.
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.
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
23 * @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
24 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25 * @version 1.7.0, 2009-08-10
29 /** PHPExcel root directory */
30 if (!defined('PHPEXCEL_ROOT')) {
31 define('PHPEXCEL_ROOT', dirname(__FILE__
) . '/');
35 require_once PHPEXCEL_ROOT
. 'PHPExcel/Cell.php';
37 /** PHPExcel_DocumentProperties */
38 require_once PHPEXCEL_ROOT
. 'PHPExcel/DocumentProperties.php';
40 /** PHPExcel_DocumentSecurity */
41 require_once PHPEXCEL_ROOT
. 'PHPExcel/DocumentSecurity.php';
43 /** PHPExcel_Worksheet */
44 require_once PHPEXCEL_ROOT
. 'PHPExcel/Worksheet.php';
46 /** PHPExcel_Shared_ZipStreamWrapper */
47 require_once PHPEXCEL_ROOT
. 'PHPExcel/Shared/ZipStreamWrapper.php';
49 /** PHPExcel_NamedRange */
50 require_once PHPEXCEL_ROOT
. 'PHPExcel/NamedRange.php';
52 /** PHPExcel_WorksheetIterator */
53 require_once PHPEXCEL_ROOT
. 'PHPExcel/WorksheetIterator.php';
61 * @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
68 * @var PHPExcel_DocumentProperties
75 * @var PHPExcel_DocumentSecurity
80 * Collection of Worksheet objects
82 * @var PHPExcel_Worksheet[]
84 private $_workSheetCollection = array();
91 private $_activeSheetIndex = 0;
96 * @var PHPExcel_NamedRange[]
98 private $_namedRanges = array();
103 * @var PHPExcel_Style
105 private $_cellXfSupervisor;
110 * @var PHPExcel_Style[]
112 private $_cellXfCollection = array();
115 * CellStyleXf collection
117 * @var PHPExcel_Style[]
119 private $_cellStyleXfCollection = array();
122 * Create a new PHPExcel with one Worksheet
124 public function __construct()
126 // Initialise worksheet collection and add one worksheet
127 $this->_workSheetCollection
= array();
128 $this->_workSheetCollection
[] = new PHPExcel_Worksheet($this);
129 $this->_activeSheetIndex
= 0;
131 // Create document properties
132 $this->_properties
= new PHPExcel_DocumentProperties();
134 // Create document security
135 $this->_security
= new PHPExcel_DocumentSecurity();
138 $this->_namedRanges
= array();
140 // Create the cellXf supervisor
141 $this->_cellXfSupervisor
= new PHPExcel_Style(true);
142 $this->_cellXfSupervisor
->bindParent($this);
144 // Create the default style
145 $this->addCellXf(new PHPExcel_Style
);
146 $this->addCellStyleXf(new PHPExcel_Style
);
152 * @return PHPExcel_DocumentProperties
154 public function getProperties()
156 return $this->_properties
;
162 * @param PHPExcel_DocumentProperties $pValue
164 public function setProperties(PHPExcel_DocumentProperties
$pValue)
166 $this->_properties
= $pValue;
172 * @return PHPExcel_DocumentSecurity
174 public function getSecurity()
176 return $this->_security
;
182 * @param PHPExcel_DocumentSecurity $pValue
184 public function setSecurity(PHPExcel_DocumentSecurity
$pValue)
186 $this->_security
= $pValue;
192 * @return PHPExcel_Worksheet
194 public function getActiveSheet()
196 return $this->_workSheetCollection
[$this->_activeSheetIndex
];
200 * Create sheet and add it to this workbook
202 * @return PHPExcel_Worksheet
204 public function createSheet($iSheetIndex = null)
206 $newSheet = new PHPExcel_Worksheet($this);
207 $this->addSheet($newSheet, $iSheetIndex);
214 * @param PHPExcel_Worksheet $pSheet
217 public function addSheet(PHPExcel_Worksheet
$pSheet = null, $iSheetIndex = null)
219 if(is_null($iSheetIndex))
221 $this->_workSheetCollection
[] = $pSheet;
225 // Insert the sheet at the requested index
227 $this->_workSheetCollection
,
236 * Remove sheet by index
238 * @param int $pIndex Active sheet index
241 public function removeSheetByIndex($pIndex = 0)
243 if ($pIndex > count($this->_workSheetCollection
) - 1) {
244 throw new Exception("Sheet index is out of bounds.");
246 array_splice($this->_workSheetCollection
, $pIndex, 1);
253 * @param int $pIndex Sheet index
254 * @return PHPExcel_Worksheet
257 public function getSheet($pIndex = 0)
259 if ($pIndex > count($this->_workSheetCollection
) - 1) {
260 throw new Exception("Sheet index is out of bounds.");
262 return $this->_workSheetCollection
[$pIndex];
269 * @return PHPExcel_Worksheet[]
271 public function getAllSheets()
273 return $this->_workSheetCollection
;
279 * @param string $pName Sheet name
280 * @return PHPExcel_Worksheet
283 public function getSheetByName($pName = '')
285 $worksheetCount = count($this->_workSheetCollection
);
286 for ($i = 0; $i < $worksheetCount; ++
$i) {
287 if ($this->_workSheetCollection
[$i]->getTitle() == $pName) {
288 return $this->_workSheetCollection
[$i];
296 * Get index for sheet
298 * @param PHPExcel_Worksheet $pSheet
299 * @return Sheet index
302 public function getIndex(PHPExcel_Worksheet
$pSheet)
304 foreach ($this->_workSheetCollection
as $key => $value) {
305 if ($value->getHashCode() == $pSheet->getHashCode()) {
312 * Set index for sheet by sheet name.
314 * @param string $sheetName Sheet name to modify index for
315 * @param int $newIndex New index for the sheet
316 * @return New sheet index
319 public function setIndexByName($sheetName, $newIndex)
321 $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
322 $pSheet = array_splice(
323 $this->_workSheetCollection
,
328 $this->_workSheetCollection
,
341 public function getSheetCount()
343 return count($this->_workSheetCollection
);
347 * Get active sheet index
349 * @return int Active sheet index
351 public function getActiveSheetIndex()
353 return $this->_activeSheetIndex
;
357 * Set active sheet index
359 * @param int $pIndex Active sheet index
361 * @return PHPExcel_Worksheet
363 public function setActiveSheetIndex($pIndex = 0)
365 if ($pIndex > count($this->_workSheetCollection
) - 1) {
366 throw new Exception("Active sheet index is out of bounds.");
368 $this->_activeSheetIndex
= $pIndex;
370 return $this->getActiveSheet();
378 public function getSheetNames()
380 $returnValue = array();
381 $worksheetCount = $this->getSheetCount();
382 for ($i = 0; $i < $worksheetCount; ++
$i) {
383 array_push($returnValue, $this->getSheet($i)->getTitle());
392 * @param PHPExcel_Worksheet $pSheet External sheet to add
394 * @return PHPExcel_Worksheet
396 public function addExternalSheet(PHPExcel_Worksheet
$pSheet) {
397 if (!is_null($this->getSheetByName($pSheet->getTitle()))) {
398 throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
401 // count how many cellXfs there are in this workbook currently, we will need this below
402 $countCellXfs = count($this->_cellXfCollection
);
404 // copy all the shared cellXfs from the external workbook and append them to the current
405 foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
406 $this->addCellXf(clone $cellXf);
409 // move sheet to this workbook
410 $pSheet->rebindParent($this);
412 // update the cellXfs
413 foreach ($pSheet->getCellCollection(false) as $cell) {
414 $cell->setXfIndex( $cell->getXfIndex() +
$countCellXfs );
417 return $this->addSheet($pSheet);
423 * @return PHPExcel_NamedRange[]
425 public function getNamedRanges() {
426 return $this->_namedRanges
;
432 * @param PHPExcel_NamedRange $namedRange
435 public function addNamedRange(PHPExcel_NamedRange
$namedRange) {
436 $this->_namedRanges
[$namedRange->getWorksheet()->getTitle().'!'.$namedRange->getName()] = $namedRange;
443 * @param string $namedRange
445 public function getNamedRange($namedRange, PHPExcel_Worksheet
$pSheet = null) {
446 if ($namedRange != '' && !is_null($namedRange)) {
447 if (!is_null($pSheet)) {
448 $key = $pSheet->getTitle().'!'.$namedRange;
449 if (isset($this->_namedRanges
[$key])) {
450 return $this->_namedRanges
[$key];
454 foreach($this->_namedRanges
as $_namedRange) {
455 if ($_namedRange->getName() == $namedRange) {
456 if ((!is_null($pSheet)) && ($_namedRange->getWorksheet()->getTitle() == $pSheet->getTitle())) {
460 $returnValue = $_namedRange;
464 if ($returnCount == 1) {
475 * @param string $namedRange
478 public function removeNamedRange($namedRange, PHPExcel_Worksheet
$pSheet = null) {
479 if ($namedRange != '' && !is_null($namedRange)) {
480 if (!is_null($pSheet)) {
481 $key = $pSheet->getTitle().'!'.$namedRange;
482 if (isset($this->_namedRanges
[$key])) {
483 unset($this->_namedRanges
[$key]);
486 foreach($this->_namedRanges
as $_namedRange) {
487 if ($_namedRange->getName() == $namedRange) {
488 if ((!is_null($pSheet)) && ($_namedRange->getWorksheet()->getTitle() == $pSheet->getTitle())) {
489 $key = $pSheet->getTitle().'!'.$namedRange;
490 if (isset($this->_namedRanges
[$key])) {
491 unset($this->_namedRanges
[$key]);
501 * Get worksheet iterator
503 * @return PHPExcel_WorksheetIterator
505 public function getWorksheetIterator() {
506 return new PHPExcel_WorksheetIterator($this);
510 * Copy workbook (!= clone!)
514 public function copy() {
515 $copied = clone $this;
517 $worksheetCount = count($this->_workSheetCollection
);
518 for ($i = 0; $i < $worksheetCount; ++
$i) {
519 $this->_workSheetCollection
[$i] = $this->_workSheetCollection
[$i]->copy();
520 $this->_workSheetCollection
[$i]->rebindParent($this);
527 * Implement PHP __clone to create a deep clone, not just a shallow copy.
529 public function __clone() {
530 foreach($this as $key => $val) {
531 if (is_object($val) ||
(is_array($val))) {
532 $this->{$key} = unserialize(serialize($val));
538 * Get the workbook collection of cellXfs
540 * @return PHPExcel_Style[]
542 public function getCellXfCollection()
544 return $this->_cellXfCollection
;
548 * Get cellXf by index
551 * @return PHPExcel_Style
553 public function getCellXfByIndex($pIndex = 0)
555 return $this->_cellXfCollection
[$pIndex];
559 * Get cellXf by hash code
561 * @param string $pValue
562 * @return PHPExcel_Style|false
564 public function getCellXfByHashCode($pValue = '')
566 foreach ($this->_cellXfCollection
as $cellXf) {
567 if ($cellXf->getHashCode() == $pValue) {
577 * @return PHPExcel_Style
580 public function getDefaultStyle()
582 if (isset($this->_cellXfCollection
[0])) {
583 return $this->_cellXfCollection
[0];
585 throw new Exception('No default style found for this workbook');
589 * Add a cellXf to the workbook
591 * @param PHPExcel_Style
593 public function addCellXf(PHPExcel_Style
$style)
595 $this->_cellXfCollection
[] = $style;
596 $style->setIndex(count($this->_cellXfCollection
) - 1);
600 * Remove cellXf by index. It is ensured that all cells get their xf index updated.
602 * @param int $pIndex Index to cellXf
605 public function removeCellXfByIndex($pIndex = 0)
607 if ($pIndex > count($this->_cellXfCollection
) - 1) {
608 throw new Exception("CellXf index is out of bounds.");
610 // first remove the cellXf
611 array_splice($this->_cellXfCollection
, $pIndex, 1);
613 // then update cellXf indexes for cells
614 foreach ($this->_workSheetCollection
as $worksheet) {
615 foreach ($worksheet->getCellCollection(false) as $cell) {
616 $xfIndex = $cell->getXfIndex();
617 if ($xfIndex > $pIndex ) {
618 // decrease xf index by 1
619 $cell->setXfIndex($xfIndex - 1);
620 } else if ($xfIndex == $pIndex) {
621 // set to default xf index 0
622 $cell->setXfIndex(0);
630 * Get the cellXf supervisor
632 * @return PHPExcel_Style
634 public function getCellXfSupervisor()
636 return $this->_cellXfSupervisor
;
640 * Get the workbook collection of cellStyleXfs
642 * @return PHPExcel_Style[]
644 public function getCellStyleXfCollection()
646 return $this->_cellStyleXfCollection
;
650 * Get cellStyleXf by index
653 * @return PHPExcel_Style
655 public function getCellStyleXfByIndex($pIndex = 0)
657 return $this->_cellStyleXfCollection
[$pIndex];
661 * Get cellStyleXf by hash code
663 * @param string $pValue
664 * @return PHPExcel_Style|false
666 public function getCellStyleXfByHashCode($pValue = '')
668 foreach ($this->_cellXfStyleCollection
as $cellStyleXf) {
669 if ($cellStyleXf->getHashCode() == $pValue) {
677 * Add a cellStyleXf to the workbook
679 * @param PHPExcel_Style $pStyle
681 public function addCellStyleXf(PHPExcel_Style
$pStyle)
683 $this->_cellStyleXfCollection
[] = $pStyle;
684 $pStyle->setIndex(count($this->_cellStyleXfCollection
) - 1);
688 * Remove cellStyleXf by index
693 public function removeCellStyleXfByIndex($pIndex = 0)
695 if ($pIndex > count($this->_cellStyleXfCollection
) - 1) {
696 throw new Exception("CellStyleXf index is out of bounds.");
698 array_splice($this->_cellStyleXfCollection
, $pIndex, 1);
703 * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells in the workbook
705 public function garbageCollect()
707 // how many references are there to each cellXf ?
708 $countReferencesCellXf = array();
709 foreach ($this->_cellXfCollection
as $index => $cellXf) {
710 $countReferencesCellXf[$index] = 0;
713 foreach ($this->getWorksheetIterator() as $sheet) {
714 foreach ($sheet->getCellCollection(false) as $cell) {
715 ++
$countReferencesCellXf[$cell->getXfIndex()];
719 // remove those cellXfs that have zero references and create mapping so we can update xfIndex for all cells
720 $countNeededCellXfs = 0;
721 foreach ($this->_cellXfCollection
as $index => $cellXf) {
722 if ($countReferencesCellXf[$index] > 0 ||
$index == 0) { // we must never remove the first cellXf
723 ++
$countNeededCellXfs;
725 unset($this->_cellXfCollection
[$index]);
727 $map[$index] = $countNeededCellXfs - 1;
729 $this->_cellXfCollection
= array_values($this->_cellXfCollection
);
731 // if we removed the first style by accident, recreate it
732 if (count($this->_cellXfCollection
) == 0) {
733 $this->_cellXfCollection
[] = new PHPExcel_Style();
736 // update the xfIndex for all cells
737 foreach ($this->getWorksheetIterator() as $sheet) {
738 foreach ($sheet->getCellCollection(false) as $cell) {
739 $cell->setXfIndex( $map[$cell->getXfIndex()] );
743 // also do garbage collection for all the sheets
744 foreach ($this->getWorksheetIterator() as $sheet) {
745 $sheet->garbageCollect();