File Source for PHPExcel.php

PHPExcel

# Source for file PHPExcel.php

Documentation is available at 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. */
29. /** PHPExcel root directory */
30. if (! defined('PHPEXCEL_ROOT')) {
31. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
32. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
33. }
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;
52. /**
53. * Document security
54. *
55. * @var PHPExcel_DocumentSecurity
56. */
57. private $_security;
59. /**
60. * Collection of Worksheet objects
61. *
62. * @var PHPExcel_Worksheet[]
63. */
64. private $_workSheetCollection = array();
66. /**
67. * Active sheet index
68. *
69. * @var int
70. */
71. private $_activeSheetIndex = 0;
73. /**
74. * Named ranges
75. *
76. * @var PHPExcel_NamedRange[]
77. */
78. private $_namedRanges = array();
80. /**
81. * CellXf supervisor
82. *
83. * @var PHPExcel_Style
84. */
85. private $_cellXfSupervisor;
87. /**
88. * CellXf collection
89. *
90. * @var PHPExcel_Style[]
91. */
92. private $_cellXfCollection = array();
94. /**
95. * CellStyleXf collection
96. *
97. * @var PHPExcel_Style[]
98. */
99. private $_cellStyleXfCollection = array();
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;
111. // Create document properties
112. $this->_properties = new PHPExcel_DocumentProperties();
114. // Create document security
115. $this->_security = new PHPExcel_DocumentSecurity();
117. // Set named ranges
118. $this->_namedRanges = array();
120. // Create the cellXf supervisor
121. $this->_cellXfSupervisor = new PHPExcel_Style(true);
122. $this->_cellXfSupervisor->bindParent($this);
124. // Create the default style
125. $this-> addCellXf(new PHPExcel_Style);
126. $this-> addCellStyleXf(new PHPExcel_Style);
127. }
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. }
139. /**
140. * Get properties
141. *
142. * @return PHPExcel_DocumentProperties
143. */
144. public function getProperties()
145. {
146. return $this->_properties;
147. }
149. /**
150. * Set properties
151. *
152. * @param PHPExcel_DocumentProperties $pValue
153. */
154. public function setProperties(PHPExcel_DocumentProperties $pValue)
155. {
156. $this->_properties = $pValue;
157. }
159. /**
160. * Get security
161. *
162. * @return PHPExcel_DocumentSecurity
163. */
164. public function getSecurity()
165. {
166. return $this->_security;
167. }
169. /**
170. * Set security
171. *
172. * @param PHPExcel_DocumentSecurity $pValue
173. */
174. public function setSecurity(PHPExcel_DocumentSecurity $pValue)
175. {
176. $this->_security = $pValue;
177. }
179. /**
180. * Get active sheet
181. *
182. * @return PHPExcel_Worksheet
183. */
184. public function getActiveSheet()
185. {
186. return $this->_workSheetCollection[$this->_activeSheetIndex];
187. }
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. }
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. );
225. // Adjust active sheet index if necessary
226. if ($this->_activeSheetIndex >= $iSheetIndex) {
227. ++$this->_activeSheetIndex;
228. }
230. }
231. return $pSheet;
232. }
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. }
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. }
265. /**
266. * Get all sheets
267. *
268. * @return PHPExcel_Worksheet[]
269. */
270. public function getAllSheets()
271. {
272. return $this->_workSheetCollection;
273. }
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. }
291. return null;
292. }
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. }
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. }
335. /**
336. * Get sheet count
337. *
338. * @return int
339. */
340. public function getSheetCount()
341. {
342. return count($this->_workSheetCollection);
343. }
345. /**
346. * Get active sheet index
347. *
348. * @return int Active sheet index
349. */
350. public function getActiveSheetIndex()
351. {
352. return $this->_activeSheetIndex;
353. }
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. }
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. }
386. throw new Exception('Workbook does not contain sheet:' . $pValue);
387. }
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. }
402. return $returnValue;
403. }
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. }
418. // count how many cellXfs there are in this workbook currently, we will need this below
419. $countCellXfs = count($this->_cellXfCollection);
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. }
426. // move sheet to this workbook
427. $pSheet->rebindParent($this);
429. // update the cellXfs
430. foreach ($pSheet->getCellCollection(false) as $cellID) {
431. $cell = $pSheet->getCell($cellID);
432. $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
433. }
435. return $this-> addSheet($pSheet, $iSheetIndex);
436. }
438. /**
439. * Get named ranges
440. *
441. * @return PHPExcel_NamedRange[]
442. */
443. public function getNamedRanges() {
444. return $this->_namedRanges;
445. }
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. }
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;
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. }
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. }
486. return $returnValue;
487. }
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. }
509. /**
510. * Get worksheet iterator
511. *
512. * @return PHPExcel_WorksheetIterator
513. */
514. public function getWorksheetIterator() {
515. return new PHPExcel_WorksheetIterator($this);
516. }
518. /**
519. * Copy workbook (!= clone!)
520. *
521. * @return PHPExcel
522. */
523. public function copy() {
524. $copied = clone $this;
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. }
532. return $copied;
533. }
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. }
546. /**
547. * Get the workbook collection of cellXfs
548. *
549. * @return PHPExcel_Style[]
550. */
551. public function getCellXfCollection()
552. {
553. return $this->_cellXfCollection;
554. }
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. }
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. }
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. }
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. }
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);
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. }
639. /**
640. * Get the cellXf supervisor
641. *
642. * @return PHPExcel_Style
643. */
644. public function getCellXfSupervisor()
645. {
646. return $this->_cellXfSupervisor;
647. }
649. /**
650. * Get the workbook collection of cellStyleXfs
651. *
652. * @return PHPExcel_Style[]
653. */
654. public function getCellStyleXfCollection()
655. {
656. return $this->_cellStyleXfCollection;
657. }
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. }
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. }
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. }
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. }
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. }
724. foreach ($this-> getWorksheetIterator() as $sheet) {
726. // from cells
727. foreach ($sheet->getCellCollection(false) as $cellID) {
728. $cell = $sheet->getCell($cellID);
729. ++$countReferencesCellXf[$cell->getXfIndex()];
730. }
732. // from row dimensions
733. foreach ($sheet->getRowDimensions() as $rowDimension) {
734. if ($rowDimension->getXfIndex() !== null) {
735. ++$countReferencesCellXf[$rowDimension->getXfIndex()];
736. }
737. }
739. // from column dimensions
740. foreach ($sheet->getColumnDimensions() as $columnDimension) {
741. ++$countReferencesCellXf[$columnDimension->getXfIndex()];
742. }
743. }
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);
758. // update the index for all cellXfs
759. foreach ($this->_cellXfCollection as $i => $cellXf) {
760. $cellXf->setIndex($i);
761. }
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. }
768. // update the xfIndex for all cells, row dimensions, column dimensions
769. foreach ($this-> getWorksheetIterator() as $sheet) {
771. // for all cells
772. foreach ($sheet->getCellCollection(false) as $cellID) {
773. $cell = $sheet->getCell($cellID);
774. $cell->setXfIndex( $map[$cell->getXfIndex()] );
775. }
777. // for all row dimensions
778. foreach ($sheet->getRowDimensions() as $rowDimension) {
779. if ($rowDimension->getXfIndex() !== null) {
780. $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
781. }
782. }
784. // for all column dimensions
785. foreach ($sheet->getColumnDimensions() as $columnDimension) {
786. $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
787. }
788. }
790. // also do garbage collection for all the sheets
791. foreach ($this-> getWorksheetIterator() as $sheet) {
792. $sheet-> garbageCollect();
793. }
794. }
796. }

Documentation generated on Sun, 27 Feb 2011 16:33:26 -0800 by phpDocumentor 1.4.3