File Source for Worksheet.php

PHPExcel_Worksheet

# Source for file Worksheet.php

Documentation is available at Worksheet.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_Worksheet
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. /**
30. * PHPExcel_Worksheet
31. *
32. * @category PHPExcel
33. * @package PHPExcel_Worksheet
34. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
35. */
36. class PHPExcel_Worksheet implements PHPExcel_IComparable
37. {
38. /* Break types */
39. const BREAK_NONE = 0;
40. const BREAK_ROW = 1;
41. const BREAK_COLUMN = 2;
43. /* Sheet state */
44. const SHEETSTATE_VISIBLE = 'visible';
45. const SHEETSTATE_HIDDEN = 'hidden';
46. const SHEETSTATE_VERYHIDDEN = 'veryHidden';
48. /**
49. * Invalid characters in sheet title
50. *
51. * @var array
52. */
53. private static $_invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
55. /**
56. * Parent spreadsheet
57. *
58. * @var PHPExcel
59. */
60. private $_parent;
62. /**
63. * Cacheable collection of cells
64. *
65. * @var PHPExcel_CachedObjectStorage_xxx
66. */
67. private $_cellCollection = null;
69. /**
70. * Collection of row dimensions
71. *
72. * @var PHPExcel_Worksheet_RowDimension[]
73. */
74. private $_rowDimensions = array();
76. /**
77. * Default row dimension
78. *
79. * @var PHPExcel_Worksheet_RowDimension
80. */
81. private $_defaultRowDimension = null;
83. /**
84. * Collection of column dimensions
85. *
86. * @var PHPExcel_Worksheet_ColumnDimension[]
87. */
88. private $_columnDimensions = array();
90. /**
91. * Default column dimension
92. *
93. * @var PHPExcel_Worksheet_ColumnDimension
94. */
95. private $_defaultColumnDimension = null;
97. /**
98. * Collection of drawings
99. *
100. * @var PHPExcel_Worksheet_BaseDrawing[]
101. */
102. private $_drawingCollection = null;
104. /**
105. * Worksheet title
106. *
107. * @var string
108. */
109. private $_title;
111. /**
112. * Sheet state
113. *
114. * @var string
115. */
116. private $_sheetState;
118. /**
119. * Page setup
120. *
121. * @var PHPExcel_Worksheet_PageSetup
122. */
123. private $_pageSetup;
125. /**
126. * Page margins
127. *
128. * @var PHPExcel_Worksheet_PageMargins
129. */
130. private $_pageMargins;
132. /**
133. * Page header/footer
134. *
135. * @var PHPExcel_Worksheet_HeaderFooter
136. */
137. private $_headerFooter;
139. /**
140. * Sheet view
141. *
142. * @var PHPExcel_Worksheet_SheetView
143. */
144. private $_sheetView;
146. /**
147. * Protection
148. *
149. * @var PHPExcel_Worksheet_Protection
150. */
151. private $_protection;
153. /**
154. * Collection of styles
155. *
156. * @var PHPExcel_Style[]
157. */
158. private $_styles = array();
160. /**
161. * Conditional styles. Indexed by cell coordinate, e.g. 'A1'
162. *
163. * @var array
164. */
165. private $_conditionalStylesCollection = array();
167. /**
168. * Is the current cell collection sorted already?
169. *
170. * @var boolean
171. */
172. private $_cellCollectionIsSorted = false;
174. /**
175. * Collection of breaks
176. *
177. * @var array
178. */
179. private $_breaks = array();
181. /**
182. * Collection of merged cell ranges
183. *
184. * @var array
185. */
186. private $_mergeCells = array();
188. /**
189. * Collection of protected cell ranges
190. *
191. * @var array
192. */
193. private $_protectedCells = array();
195. /**
196. * Autofilter Range
197. *
198. * @var string
199. */
200. private $_autoFilter = '';
202. /**
203. * Freeze pane
204. *
205. * @var string
206. */
207. private $_freezePane = '';
209. /**
210. * Show gridlines?
211. *
212. * @var boolean
213. */
214. private $_showGridlines = true;
216. /**
217. * Print gridlines?
218. *
219. * @var boolean
220. */
221. private $_printGridlines = false;
223. /**
224. * Show row and column headers?
225. *
226. * @var boolean
227. */
228. private $_showRowColHeaders = true;
230. /**
231. * Show summary below? (Row/Column outline)
232. *
233. * @var boolean
234. */
235. private $_showSummaryBelow = true;
237. /**
238. * Show summary right? (Row/Column outline)
239. *
240. * @var boolean
241. */
242. private $_showSummaryRight = true;
244. /**
245. * Collection of comments
246. *
247. * @var PHPExcel_Comment[]
248. */
249. private $_comments = array();
251. /**
252. * Active cell. (Only one!)
253. *
254. * @var string
255. */
256. private $_activeCell = 'A1';
258. /**
259. * Selected cells
260. *
261. * @var string
262. */
263. private $_selectedCells = 'A1';
265. /**
266. * Cached highest column
267. *
268. * @var string
269. */
270. private $_cachedHighestColumn = 'A';
272. /**
273. * Cached highest row
274. *
275. * @var int
276. */
277. private $_cachedHighestRow = 1;
279. /**
280. * Right-to-left?
281. *
282. * @var boolean
283. */
284. private $_rightToLeft = false;
286. /**
287. * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'
288. *
289. * @var array
290. */
291. private $_hyperlinkCollection = array();
293. /**
294. * Data validation objects. Indexed by cell coordinate, e.g. 'A1'
295. *
296. * @var array
297. */
298. private $_dataValidationCollection = array();
300. /**
301. * Tab color
302. *
303. * @var PHPExcel_Style_Color
304. */
305. private $_tabColor;
307. /**
308. * Dirty flag
309. *
310. * @var boolean
311. */
312. private $_dirty = true;
314. /**
315. * Hash
316. *
317. * @var string
318. */
319. private $_hash = null;
321. /**
322. * Create a new worksheet
323. *
324. * @param PHPExcel $pParent
325. * @param string $pTitle
326. */
327. public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
328. {
329. // Set parent and title
330. $this->_parent = $pParent;
331. $this-> setTitle($pTitle);
332. $this-> setSheetState( PHPExcel_Worksheet:: SHEETSTATE_VISIBLE);
334. $this->_cellCollection = PHPExcel_CachedObjectStorageFactory:: getInstance($this);
336. // Set page setup
337. $this->_pageSetup = new PHPExcel_Worksheet_PageSetup();
339. // Set page margins
340. $this->_pageMargins = new PHPExcel_Worksheet_PageMargins();
342. // Set page header/footer
343. $this->_headerFooter = new PHPExcel_Worksheet_HeaderFooter();
345. // Set sheet view
346. $this->_sheetView = new PHPExcel_Worksheet_SheetView();
348. // Drawing collection
349. $this->_drawingCollection = new ArrayObject();
351. // Protection
352. $this->_protection = new PHPExcel_Worksheet_Protection();
354. // Default row dimension
355. $this->_defaultRowDimension = new PHPExcel_Worksheet_RowDimension(null);
357. // Default column dimension
358. $this->_defaultColumnDimension = new PHPExcel_Worksheet_ColumnDimension(null);
359. }
362. public function disconnectCells() {
363. $this->_cellCollection->unsetWorksheetCells();
364. $this->_cellCollection = null;
366. // detach ourself from the workbook, so that it can then delete this worksheet successfully
367. $this->_parent = null;
368. }
370. /**
371. * Return the cache controller for the cell collection
372. *
373. * @return PHPExcel_CachedObjectStorage_xxx
374. */
375. public function getCellCacheController() {
376. return $this->_cellCollection;
377. } // function getCellCacheController()
380. /**
381. * Get array of invalid characters for sheet title
382. *
383. * @return array
384. */
385. public static function getInvalidCharacters()
386. {
387. return self::$_invalidCharacters;
388. }
390. /**
391. * Check sheet title for valid Excel syntax
392. *
393. * @param string $pValue The string to check
394. * @return string The valid string
395. * @throws Exception
396. */
397. private static function _checkSheetTitle($pValue)
398. {
399. // Some of the printable ASCII characters are invalid: * : / \ ? [ ]
400. if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
401. throw new Exception('Invalid character found in sheet title');
402. }
404. // Maximum 31 characters allowed for sheet title
405. if ( PHPExcel_Shared_String:: CountCharacters($pValue) > 31) {
406. throw new Exception('Maximum 31 characters allowed in sheet title.');
407. }
409. return $pValue;
410. }
412. /**
413. * Get collection of cells
414. *
415. * @param boolean $pSorted Also sort the cell collection?
416. * @return PHPExcel_Cell[]
417. */
418. public function getCellCollection($pSorted = true)
419. {
420. if ($pSorted) {
421. // Re-order cell collection
422. return $this-> sortCellCollection();
423. }
424. if (! is_null($this->_cellCollection)) {
425. return $this->_cellCollection->getCellList();
426. }
427. return array();
428. }
430. /**
431. * Sort collection of cells
432. *
433. * @return PHPExcel_Worksheet
434. */
435. public function sortCellCollection()
436. {
437. if (! is_null($this->_cellCollection)) {
438. return $this->_cellCollection->getSortedCellList();
439. }
440. return array();
441. }
443. /**
444. * Get collection of row dimensions
445. *
446. * @return PHPExcel_Worksheet_RowDimension[]
447. */
448. public function getRowDimensions()
449. {
450. return $this->_rowDimensions;
451. }
453. /**
454. * Get default row dimension
455. *
456. * @return PHPExcel_Worksheet_RowDimension
457. */
458. public function getDefaultRowDimension()
459. {
460. return $this->_defaultRowDimension;
461. }
463. /**
464. * Get collection of column dimensions
465. *
466. * @return PHPExcel_Worksheet_ColumnDimension[]
467. */
468. public function getColumnDimensions()
469. {
470. return $this->_columnDimensions;
471. }
473. /**
474. * Get default column dimension
475. *
476. * @return PHPExcel_Worksheet_ColumnDimension
477. */
478. public function getDefaultColumnDimension()
479. {
480. return $this->_defaultColumnDimension;
481. }
483. /**
484. * Get collection of drawings
485. *
486. * @return PHPExcel_Worksheet_BaseDrawing[]
487. */
488. public function getDrawingCollection()
489. {
490. return $this->_drawingCollection;
491. }
493. /**
494. * Refresh column dimensions
495. *
496. * @return PHPExcel_Worksheet
497. */
498. public function refreshColumnDimensions()
499. {
500. $currentColumnDimensions = $this-> getColumnDimensions();
501. $newColumnDimensions = array();
503. foreach ($currentColumnDimensions as $objColumnDimension) {
504. $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
505. }
507. $this->_columnDimensions = $newColumnDimensions;
509. return $this;
510. }
512. /**
513. * Refresh row dimensions
514. *
515. * @return PHPExcel_Worksheet
516. */
517. public function refreshRowDimensions()
518. {
519. $currentRowDimensions = $this-> getRowDimensions();
520. $newRowDimensions = array();
522. foreach ($currentRowDimensions as $objRowDimension) {
523. $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
524. }
526. $this->_rowDimensions = $newRowDimensions;
528. return $this;
529. }
531. /**
532. * Calculate worksheet dimension
533. *
534. * @return string String containing the dimension of this worksheet
535. */
536. public function calculateWorksheetDimension()
537. {
538. // Return
539. return 'A1' . ':' . $this-> getHighestColumn() . $this-> getHighestRow();
540. }
542. /**
543. * Calculate widths for auto-size columns
544. *
545. * @param boolean $calculateMergeCells Calculate merge cell width
546. * @return PHPExcel_Worksheet;
547. */
548. public function calculateColumnWidths($calculateMergeCells = false)
549. {
550. // initialize $autoSizes array
551. $autoSizes = array();
552. foreach ($this-> getColumnDimensions() as $colDimension) {
553. if ($colDimension->getAutoSize()) {
554. $autoSizes[$colDimension->getColumnIndex()] = -1;
555. }
556. }
558. // There is only something to do if there are some auto-size columns
559. if (! empty($autoSizes)) {
561. // build list of cells references that participate in a merge
562. $isMergeCell = array();
563. foreach ($this-> getMergeCells() as $cells) {
564. foreach ( PHPExcel_Cell:: extractAllCellReferencesInRange($cells) as $cellReference) {
565. $isMergeCell[$cellReference] = true;
566. }
567. }
569. // loop through all cells in the worksheet
570. foreach ($this-> getCellCollection(false) as $cellID) {
571. $cell = $this-> getCell($cellID);
572. if (isset($autoSizes[$cell->getColumn()])) {
573. // Determine width if cell does not participate in a merge
574. if (!isset($isMergeCell[$cell->getCoordinate()])) {
575. // Calculated value
576. $cellValue = $cell->getCalculatedValue();
578. // To formatted string
579. $cellValue = PHPExcel_Style_NumberFormat:: toFormattedString($cellValue, $this-> getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
581. $autoSizes[$cell->getColumn()] = max(
582. (float)$autoSizes[$cell->getColumn()],
583. (float)PHPExcel_Shared_Font:: calculateColumnWidth(
584. $this-> getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
585. $cellValue,
586. $this-> getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
587. $this-> getDefaultStyle()->getFont()
588. )
589. );
590. }
591. }
592. }
594. // adjust column widths
595. foreach ($autoSizes as $columnIndex => $width) {
596. if ($width == -1) $width = $this-> getDefaultColumnDimension()->getWidth();
597. $this-> getColumnDimension($columnIndex)->setWidth($width);
598. }
599. }
601. return $this;
602. }
604. /**
605. * Get parent
606. *
607. * @return PHPExcel
608. */
609. public function getParent() {
610. return $this->_parent;
611. }
613. /**
614. * Re-bind parent
615. *
616. * @param PHPExcel $parent
617. * @return PHPExcel_Worksheet
618. */
619. public function rebindParent(PHPExcel $parent) {
620. $namedRanges = $this->_parent->getNamedRanges();
621. foreach ($namedRanges as $namedRange) {
622. $parent->addNamedRange($namedRange);
623. }
625. $this->_parent->removeSheetByIndex(
626. $this->_parent->getIndex($this)
627. );
628. $this->_parent = $parent;
630. return $this;
631. }
633. /**
634. * Get title
635. *
636. * @return string
637. */
638. public function getTitle()
639. {
640. return $this->_title;
641. }
643. /**
644. * Set title
645. *
646. * @param string $pValue String containing the dimension of this worksheet
647. * @return PHPExcel_Worksheet
648. */
649. public function setTitle($pValue = 'Worksheet')
650. {
651. // Is this a 'rename' or not?
652. if ($this-> getTitle() == $pValue) {
653. return $this;
654. }
656. // Syntax check
657. self::_checkSheetTitle($pValue);
659. // Old title
660. $oldTitle = $this-> getTitle();
662. // Is there already such sheet name?
663. if ($this-> getParent()->getSheetByName($pValue)) {
664. // Use name, but append with lowest possible integer
666. if ( PHPExcel_Shared_String:: CountCharacters($pValue) > 29) {
667. $pValue = PHPExcel_Shared_String:: Substring($pValue,0,29);
668. }
669. $i = 1;
670. while ($this-> getParent()->getSheetByName($pValue . ' ' . $i)) {
671. ++$i;
672. if ($i == 10) {
673. if ( PHPExcel_Shared_String:: CountCharacters($pValue) > 28) {
674. $pValue = PHPExcel_Shared_String:: Substring($pValue,0,28);
675. }
676. } elseif ($i == 100) {
677. if ( PHPExcel_Shared_String:: CountCharacters($pValue) > 27) {
678. $pValue = PHPExcel_Shared_String:: Substring($pValue,0,27);
679. }
680. }
681. }
683. $altTitle = $pValue . ' ' . $i;
684. return $this-> setTitle($altTitle);
685. }
687. // Set title
688. $this->_title = $pValue;
689. $this->_dirty = true;
691. // New title
692. $newTitle = $this-> getTitle();
693. PHPExcel_ReferenceHelper:: getInstance()->updateNamedFormulas($this-> getParent(), $oldTitle, $newTitle);
695. return $this;
696. }
698. /**
699. * Get sheet state
700. *
701. * @return string Sheet state (visible, hidden, veryHidden)
702. */
703. public function getSheetState() {
704. return $this->_sheetState;
705. }
707. /**
708. * Set sheet state
709. *
710. * @param string $value Sheet state (visible, hidden, veryHidden)
711. * @return PHPExcel_Worksheet
712. */
713. public function setSheetState($value = PHPExcel_Worksheet:: SHEETSTATE_VISIBLE) {
714. $this->_sheetState = $value;
715. return $this;
716. }
718. /**
719. * Get page setup
720. *
721. * @return PHPExcel_Worksheet_PageSetup
722. */
723. public function getPageSetup()
724. {
725. return $this->_pageSetup;
726. }
728. /**
729. * Set page setup
730. *
731. * @param PHPExcel_Worksheet_PageSetup $pValue
732. * @return PHPExcel_Worksheet
733. */
734. public function setPageSetup(PHPExcel_Worksheet_PageSetup $pValue)
735. {
736. $this->_pageSetup = $pValue;
737. return $this;
738. }
740. /**
741. * Get page margins
742. *
743. * @return PHPExcel_Worksheet_PageMargins
744. */
745. public function getPageMargins()
746. {
747. return $this->_pageMargins;
748. }
750. /**
751. * Set page margins
752. *
753. * @param PHPExcel_Worksheet_PageMargins $pValue
754. * @return PHPExcel_Worksheet
755. */
756. public function setPageMargins(PHPExcel_Worksheet_PageMargins $pValue)
757. {
758. $this->_pageMargins = $pValue;
759. return $this;
760. }
762. /**
763. * Get page header/footer
764. *
765. * @return PHPExcel_Worksheet_HeaderFooter
766. */
767. public function getHeaderFooter()
768. {
769. return $this->_headerFooter;
770. }
772. /**
773. * Set page header/footer
774. *
775. * @param PHPExcel_Worksheet_HeaderFooter $pValue
776. * @return PHPExcel_Worksheet
777. */
778. public function setHeaderFooter(PHPExcel_Worksheet_HeaderFooter $pValue)
779. {
780. $this->_headerFooter = $pValue;
781. return $this;
782. }
784. /**
785. * Get sheet view
786. *
787. * @return PHPExcel_Worksheet_HeaderFooter
788. */
789. public function getSheetView()
790. {
791. return $this->_sheetView;
792. }
794. /**
795. * Set sheet view
796. *
797. * @param PHPExcel_Worksheet_SheetView $pValue
798. * @return PHPExcel_Worksheet
799. */
800. public function setSheetView(PHPExcel_Worksheet_SheetView $pValue)
801. {
802. $this->_sheetView = $pValue;
803. return $this;
804. }
806. /**
807. * Get Protection
808. *
809. * @return PHPExcel_Worksheet_Protection
810. */
811. public function getProtection()
812. {
813. return $this->_protection;
814. }
816. /**
817. * Set Protection
818. *
819. * @param PHPExcel_Worksheet_Protection $pValue
820. * @return PHPExcel_Worksheet
821. */
822. public function setProtection(PHPExcel_Worksheet_Protection $pValue)
823. {
824. $this->_protection = $pValue;
825. $this->_dirty = true;
827. return $this;
828. }
830. /**
831. * Get highest worksheet column
832. *
833. * @return string Highest column name
834. */
835. public function getHighestColumn()
836. {
837. return $this->_cachedHighestColumn;
838. }
840. /**
841. * Get highest worksheet row
842. *
843. * @return int Highest row number
844. */
845. public function getHighestRow()
846. {
847. return $this->_cachedHighestRow;
848. }
850. /**
851. * Set a cell value
852. *
853. * @param string $pCoordinate Coordinate of the cell
854. * @param mixed $pValue Value of the cell
855. * @param bool $returnCell Return the worksheet (false, default) or the cell (true)
856. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
857. */
858. public function setCellValue($pCoordinate = 'A1', $pValue = null, $returnCell = false)
859. {
860. $cell = $this-> getCell($pCoordinate);
861. $cell->setValue($pValue);
863. if ($returnCell) {
864. return $cell;
865. }
866. return $this;
867. }
869. /**
870. * Set a cell value by using numeric cell coordinates
871. *
872. * @param string $pColumn Numeric column coordinate of the cell
873. * @param string $pRow Numeric row coordinate of the cell
874. * @param mixed $pValue Value of the cell
875. * @param bool $returnCell Return the worksheet (false, default) or the cell (true)
876. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
877. */
878. public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $returnCell = false)
879. {
880. $cell = $this-> getCell( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
881. $cell->setValue($pValue);
883. if ($returnCell) {
884. return $cell;
885. }
886. return $this;
887. }
889. /**
890. * Set a cell value
891. *
892. * @param string $pCoordinate Coordinate of the cell
893. * @param mixed $pValue Value of the cell
894. * @param string $pDataType Explicit data type
895. * @return PHPExcel_Worksheet
896. */
897. public function setCellValueExplicit($pCoordinate = 'A1', $pValue = null, $pDataType = PHPExcel_Cell_DataType:: TYPE_STRING)
898. {
899. // Set value
900. $this-> getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);
901. return $this;
902. }
904. /**
905. * Set a cell value by using numeric cell coordinates
906. *
907. * @param string $pColumn Numeric column coordinate of the cell
908. * @param string $pRow Numeric row coordinate of the cell
909. * @param mixed $pValue Value of the cell
910. * @param string $pDataType Explicit data type
911. * @return PHPExcel_Worksheet
912. */
913. public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType:: TYPE_STRING)
914. {
915. return $this-> getCell( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow)->setValueExplicit($pValue, $pDataType);
916. }
918. /**
919. * Get cell at a specific coordinate
920. *
921. * @param string $pCoordinate Coordinate of the cell
922. * @throws Exception
923. * @return PHPExcel_Cell Cell that was found
924. */
925. public function getCell($pCoordinate = 'A1')
926. {
927. // Check cell collection
928. if ($this->_cellCollection->isDataSet($pCoordinate)) {
929. return $this->_cellCollection->getCacheData($pCoordinate);
930. }
932. // Worksheet reference?
933. if ( strpos($pCoordinate, '!') !== false) {
934. $worksheetReference = PHPExcel_Worksheet:: extractSheetTitle($pCoordinate, true);
935. return $this-> getParent()->getSheetByName($worksheetReference[0])->getCell($worksheetReference[1]);
936. }
938. // Named range?
939. if ((! preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
940. ( preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
941. $namedRange = PHPExcel_NamedRange:: resolveRange($pCoordinate, $this);
942. if (! is_null($namedRange)) {
943. $pCoordinate = $namedRange->getRange();
944. return $namedRange->getWorksheet()->getCell($pCoordinate);
945. }
946. }
948. // Uppercase coordinate
949. $pCoordinate = strtoupper($pCoordinate);
951. if ( strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
952. throw new Exception('Cell coordinate can not be a range of cells.');
953. } elseif ( strpos($pCoordinate,'$') !== false) {
954. throw new Exception('Cell coordinate must not be absolute.');
955. } else {
956. // Create new cell object
958. // Coordinates
959. $aCoordinates = PHPExcel_Cell:: coordinateFromString($pCoordinate);
961. $cell = $this->_cellCollection->addCacheData($pCoordinate,new PHPExcel_Cell($aCoordinates[0], $aCoordinates[1], null, PHPExcel_Cell_DataType:: TYPE_NULL, $this));
962. $this->_cellCollectionIsSorted = false;
964. if ( PHPExcel_Cell:: columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell:: columnIndexFromString($aCoordinates[0]))
965. $this->_cachedHighestColumn = $aCoordinates[0];
967. $this->_cachedHighestRow = max($this->_cachedHighestRow,$aCoordinates[1]);
969. // Cell needs appropriate xfIndex
970. $rowDimensions = $this-> getRowDimensions();
971. $columnDimensions = $this-> getColumnDimensions();
973. if ( isset($rowDimensions[$aCoordinates[1]]) && $rowDimensions[$aCoordinates[1]]->getXfIndex() !== null ) {
974. // then there is a row dimension with explicit style, assign it to the cell
975. $cell->setXfIndex($rowDimensions[$aCoordinates[1]]->getXfIndex());
976. } else if ( isset($columnDimensions[$aCoordinates[0]]) ) {
977. // then there is a column dimension, assign it to the cell
978. $cell->setXfIndex($columnDimensions[$aCoordinates[0]]->getXfIndex());
979. } else {
980. // set to default index
981. $cell->setXfIndex(0);
982. }
984. return $cell;
985. }
986. }
988. /**
989. * Get cell at a specific coordinate by using numeric cell coordinates
990. *
991. * @param string $pColumn Numeric column coordinate of the cell
992. * @param string $pRow Numeric row coordinate of the cell
993. * @return PHPExcel_Cell Cell that was found
994. */
995. public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
996. {
997. $columnLetter = PHPExcel_Cell:: stringFromColumnIndex($pColumn);
998. $coordinate = $columnLetter . $pRow;
1000. if (!$this->_cellCollection->isDataSet($coordinate)) {
1001. $cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType:: TYPE_NULL, $this));
1002. $this->_cellCollectionIsSorted = false;
1004. if ( PHPExcel_Cell:: columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
1005. $this->_cachedHighestColumn = $columnLetter;
1007. $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
1009. return $cell;
1010. }
1012. return $this->_cellCollection->getCacheData($coordinate);
1013. }
1015. /**
1016. * Cell at a specific coordinate exists?
1017. *
1018. * @param string $pCoordinate Coordinate of the cell
1019. * @throws Exception
1020. * @return boolean
1021. */
1022. public function cellExists($pCoordinate = 'A1')
1023. {
1024. // Worksheet reference?
1025. if ( strpos($pCoordinate, '!') !== false) {
1026. $worksheetReference = PHPExcel_Worksheet:: extractSheetTitle($pCoordinate, true);
1027. return $this-> getParent()->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
1028. }
1030. // Named range?
1031. if ((! preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
1032. ( preg_match('/^'.PHPExcel_Calculation:: CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
1033. $namedRange = PHPExcel_NamedRange:: resolveRange($pCoordinate, $this);
1034. if (! is_null($namedRange)) {
1035. $pCoordinate = $namedRange->getRange();
1036. if ($this-> getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
1037. if (!$namedRange->getLocalOnly()) {
1038. return $namedRange->getWorksheet()->cellExists($pCoordinate);
1039. } else {
1040. throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this-> getTitle());
1041. }
1042. }
1043. }
1044. }
1046. // Uppercase coordinate
1047. $pCoordinate = strtoupper($pCoordinate);
1049. if ( strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
1050. throw new Exception('Cell coordinate can not be a range of cells.');
1051. } elseif ( strpos($pCoordinate,'$') !== false) {
1052. throw new Exception('Cell coordinate must not be absolute.');
1053. } else {
1054. // Coordinates
1055. $aCoordinates = PHPExcel_Cell:: coordinateFromString($pCoordinate);
1057. // Cell exists?
1058. return $this->_cellCollection->isDataSet($pCoordinate);
1059. }
1060. }
1062. /**
1063. * Cell at a specific coordinate by using numeric cell coordinates exists?
1064. *
1065. * @param string $pColumn Numeric column coordinate of the cell
1066. * @param string $pRow Numeric row coordinate of the cell
1067. * @return boolean
1068. */
1069. public function cellExistsByColumnAndRow($pColumn = 0, $pRow = 1)
1070. {
1071. return $this-> cellExists( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
1072. }
1074. /**
1075. * Get row dimension at a specific row
1076. *
1077. * @param int $pRow Numeric index of the row
1078. * @return PHPExcel_Worksheet_RowDimension
1079. */
1080. public function getRowDimension($pRow = 1)
1081. {
1082. // Found
1083. $found = null;
1085. // Get row dimension
1086. if (!isset($this->_rowDimensions[$pRow])) {
1087. $this->_rowDimensions[$pRow] = new PHPExcel_Worksheet_RowDimension($pRow);
1089. $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
1090. }
1091. return $this->_rowDimensions[$pRow];
1092. }
1094. /**
1095. * Get column dimension at a specific column
1096. *
1097. * @param string $pColumn String index of the column
1098. * @return PHPExcel_Worksheet_ColumnDimension
1099. */
1100. public function getColumnDimension($pColumn = 'A')
1101. {
1102. // Uppercase coordinate
1103. $pColumn = strtoupper($pColumn);
1105. // Fetch dimensions
1106. if (!isset($this->_columnDimensions[$pColumn])) {
1107. $this->_columnDimensions[$pColumn] = new PHPExcel_Worksheet_ColumnDimension($pColumn);
1109. if ( PHPExcel_Cell:: columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell:: columnIndexFromString($pColumn))
1110. $this->_cachedHighestColumn = $pColumn;
1111. }
1112. return $this->_columnDimensions[$pColumn];
1113. }
1115. /**
1116. * Get column dimension at a specific column by using numeric cell coordinates
1117. *
1118. * @param string $pColumn Numeric column coordinate of the cell
1119. * @param string $pRow Numeric row coordinate of the cell
1120. * @return PHPExcel_Worksheet_ColumnDimension
1121. */
1122. public function getColumnDimensionByColumn($pColumn = 0)
1123. {
1124. return $this-> getColumnDimension( PHPExcel_Cell:: stringFromColumnIndex($pColumn));
1125. }
1127. /**
1128. * Get styles
1129. *
1130. * @return PHPExcel_Style[]
1131. */
1132. public function getStyles()
1133. {
1134. return $this->_styles;
1135. }
1137. /**
1138. * Get default style of workbork.
1139. *
1140. * @deprecated
1141. * @return PHPExcel_Style
1142. * @throws Exception
1143. */
1144. public function getDefaultStyle()
1145. {
1146. return $this->_parent->getDefaultStyle();
1147. }
1149. /**
1150. * Set default style - should only be used by PHPExcel_IReader implementations!
1151. *
1152. * @deprecated
1153. * @param PHPExcel_Style $value
1154. * @throws Exception
1155. * @return PHPExcel_Worksheet
1156. */
1157. public function setDefaultStyle(PHPExcel_Style $pValue)
1158. {
1159. $this->_parent->getDefaultStyle()->applyFromArray(array(
1160. 'font' => array(
1161. 'name' => $pValue->getFont()->getName(),
1162. 'size' => $pValue->getFont()->getSize(),
1163. ),
1164. ));
1165. return $this;
1166. }
1168. /**
1169. * Get style for cell
1170. *
1171. * @param string $pCellCoordinate Cell coordinate to get style for
1172. * @return PHPExcel_Style
1173. * @throws Exception
1174. */
1175. public function getStyle($pCellCoordinate = 'A1')
1176. {
1177. // set this sheet as active
1178. $this->_parent->setActiveSheetIndex($this->_parent->getIndex($this));
1180. // set cell coordinate as active
1181. $this-> setSelectedCells($pCellCoordinate);
1183. return $this->_parent->getCellXfSupervisor();
1184. }
1186. /**
1187. * Get conditional styles for a cell
1188. *
1189. * @param string $pCoordinate
1190. * @return PHPExcel_Style_Conditional[]
1191. */
1192. public function getConditionalStyles($pCoordinate = 'A1')
1193. {
1194. if (!isset($this->_conditionalStylesCollection[$pCoordinate])) {
1195. $this->_conditionalStylesCollection[$pCoordinate] = array();
1196. }
1197. return $this->_conditionalStylesCollection[$pCoordinate];
1198. }
1200. /**
1201. * Do conditional styles exist for this cell?
1202. *
1203. * @param string $pCoordinate
1204. * @return boolean
1205. */
1206. public function conditionalStylesExists($pCoordinate = 'A1')
1207. {
1208. if (isset($this->_conditionalStylesCollection[$pCoordinate])) {
1209. return true;
1210. }
1211. return false;
1212. }
1214. /**
1215. * Removes conditional styles for a cell
1216. *
1217. * @param string $pCoordinate
1218. * @return PHPExcel_Worksheet
1219. */
1220. public function removeConditionalStyles($pCoordinate = 'A1')
1221. {
1222. unset($this->_conditionalStylesCollection[$pCoordinate]);
1223. return $this;
1224. }
1226. /**
1227. * Get collection of conditional styles
1228. *
1229. * @return array
1230. */
1231. public function getConditionalStylesCollection()
1232. {
1233. return $this->_conditionalStylesCollection;
1234. }
1236. /**
1237. * Set conditional styles
1238. *
1239. * @param $pCoordinate string E.g. 'A1'
1240. * @param $pValue PHPExcel_Style_Conditional[]
1241. * @return PHPExcel_Worksheet
1242. */
1243. public function setConditionalStyles($pCoordinate = 'A1', $pValue)
1244. {
1245. $this->_conditionalStylesCollection[$pCoordinate] = $pValue;
1246. return $this;
1247. }
1249. /**
1250. * Get style for cell by using numeric cell coordinates
1251. *
1252. * @param int $pColumn Numeric column coordinate of the cell
1253. * @param int $pRow Numeric row coordinate of the cell
1254. * @return PHPExcel_Style
1255. */
1256. public function getStyleByColumnAndRow($pColumn = 0, $pRow = 1)
1257. {
1258. return $this-> getStyle( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
1259. }
1261. /**
1262. * Set shared cell style to a range of cells
1263. *
1264. * Please note that this will overwrite existing cell styles for cells in range!
1265. *
1266. * @deprecated
1267. * @param PHPExcel_Style $pSharedCellStyle Cell style to share
1268. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1269. * @throws Exception
1270. * @return PHPExcel_Worksheet
1271. */
1272. public function setSharedStyle(PHPExcel_Style $pSharedCellStyle = null, $pRange = '')
1273. {
1274. $this-> duplicateStyle($pSharedCellStyle, $pRange);
1275. return $this;
1276. }
1278. /**
1279. * Duplicate cell style to a range of cells
1280. *
1281. * Please note that this will overwrite existing cell styles for cells in range!
1282. *
1283. * @param PHPExcel_Style $pCellStyle Cell style to duplicate
1284. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1285. * @throws Exception
1286. * @return PHPExcel_Worksheet
1287. */
1288. public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
1289. {
1290. // make sure we have a real style and not supervisor
1291. $style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
1293. // Add the style to the workbook if necessary
1294. $workbook = $this->_parent;
1295. if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
1296. // there is already such cell Xf in our collection
1297. $xfIndex = $existingStyle->getIndex();
1298. } else {
1299. // we don't have such a cell Xf, need to add
1300. $workbook->addCellXf($pCellStyle);
1301. $xfIndex = $pCellStyle->getIndex();
1302. }
1304. // Uppercase coordinate
1305. $pRange = strtoupper($pRange);
1307. // Is it a cell range or a single cell?
1308. $rangeA = '';
1309. $rangeB = '';
1310. if ( strpos($pRange, ':') === false) {
1311. $rangeA = $pRange;
1312. $rangeB = $pRange;
1313. } else {
1314. list($rangeA, $rangeB) = explode(':', $pRange);
1315. }
1317. // Calculate range outer borders
1318. $rangeStart = PHPExcel_Cell:: coordinateFromString($rangeA);
1319. $rangeEnd = PHPExcel_Cell:: coordinateFromString($rangeB);
1321. // Translate column into index
1322. $rangeStart[0] = PHPExcel_Cell:: columnIndexFromString($rangeStart[0]) - 1;
1323. $rangeEnd[0] = PHPExcel_Cell:: columnIndexFromString($rangeEnd[0]) - 1;
1325. // Make sure we can loop upwards on rows and columns
1326. if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1327. $tmp = $rangeStart;
1328. $rangeStart = $rangeEnd;
1329. $rangeEnd = $tmp;
1330. }
1332. // Loop through cells and apply styles
1333. for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1334. for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1335. $this-> getCell( PHPExcel_Cell:: stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
1336. }
1337. }
1339. return $this;
1340. }
1342. /**
1343. * Duplicate cell style array to a range of cells
1344. *
1345. * Please note that this will overwrite existing cell styles for cells in range,
1346. * if they are in the styles array. For example, if you decide to set a range of
1347. * cells to font bold, only include font bold in the styles array.
1348. *
1349. * @deprecated
1350. * @param array $pStyles Array containing style information
1351. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1352. * @param boolean $pAdvanced Advanced mode for setting borders.
1353. * @throws Exception
1354. * @return PHPExcel_Worksheet
1355. */
1356. public function duplicateStyleArray($pStyles = null, $pRange = '', $pAdvanced = true)
1357. {
1358. $this-> getStyle($pRange)->applyFromArray($pStyles, $pAdvanced);
1359. return $this;
1360. }
1362. /**
1363. * Set break on a cell
1364. *
1365. * @param string $pCell Cell coordinate (e.g. A1)
1366. * @param int $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
1367. * @throws Exception
1368. * @return PHPExcel_Worksheet
1369. */
1370. public function setBreak($pCell = 'A1', $pBreak = PHPExcel_Worksheet:: BREAK_NONE)
1371. {
1372. // Uppercase coordinate
1373. $pCell = strtoupper($pCell);
1375. if ($pCell != '') {
1376. $this->_breaks[$pCell] = $pBreak;
1377. } else {
1378. throw new Exception('No cell coordinate specified.');
1379. }
1381. return $this;
1382. }
1384. /**
1385. * Set break on a cell by using numeric cell coordinates
1386. *
1387. * @param integer $pColumn Numeric column coordinate of the cell
1388. * @param integer $pRow Numeric row coordinate of the cell
1389. * @param integer $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
1390. * @throws Exception
1391. * @return PHPExcel_Worksheet
1392. */
1393. public function setBreakByColumnAndRow($pColumn = 0, $pRow = 1, $pBreak = PHPExcel_Worksheet:: BREAK_NONE)
1394. {
1395. return $this-> setBreak( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow, $pBreak);
1396. }
1398. /**
1399. * Get breaks
1400. *
1401. * @return array[]
1402. */
1403. public function getBreaks()
1404. {
1405. return $this->_breaks;
1406. }
1408. /**
1409. * Set merge on a cell range
1410. *
1411. * @param string $pRange Cell range (e.g. A1:E1)
1412. * @throws Exception
1413. * @return PHPExcel_Worksheet
1414. */
1415. public function mergeCells($pRange = 'A1:A1')
1416. {
1417. // Uppercase coordinate
1418. $pRange = strtoupper($pRange);
1420. if ( strpos($pRange,':') !== false) {
1421. $this->_mergeCells[$pRange] = $pRange;
1423. // make sure cells are created
1425. // get the cells in the range
1426. $aReferences = PHPExcel_Cell:: extractAllCellReferencesInRange($pRange);
1428. // create upper left cell if it does not already exist
1429. $upperLeft = $aReferences[0];
1430. if (!$this-> cellExists($upperLeft)) {
1431. $this-> getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType:: TYPE_NULL);
1432. }
1434. // create or blank out the rest of the cells in the range
1435. $count = count($aReferences);
1436. for ($i = 1; $i < $count; $i++) {
1437. $this-> getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType:: TYPE_NULL);
1438. }
1440. } else {
1441. throw new Exception('Merge must be set on a range of cells.');
1442. }
1444. return $this;
1445. }
1447. /**
1448. * Set merge on a cell range by using numeric cell coordinates
1449. *
1450. * @param int $pColumn1 Numeric column coordinate of the first cell
1451. * @param int $pRow1 Numeric row coordinate of the first cell
1452. * @param int $pColumn2 Numeric column coordinate of the last cell
1453. * @param int $pRow2 Numeric row coordinate of the last cell
1454. * @throws Exception
1455. * @return PHPExcel_Worksheet
1456. */
1457. public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1458. {
1459. $cellRange = PHPExcel_Cell:: stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell:: stringFromColumnIndex($pColumn2) . $pRow2;
1460. return $this-> mergeCells($cellRange);
1461. }
1463. /**
1464. * Remove merge on a cell range
1465. *
1466. * @param string $pRange Cell range (e.g. A1:E1)
1467. * @throws Exception
1468. * @return PHPExcel_Worksheet
1469. */
1470. public function unmergeCells($pRange = 'A1:A1')
1471. {
1472. // Uppercase coordinate
1473. $pRange = strtoupper($pRange);
1475. if ( strpos($pRange,':') !== false) {
1476. if (isset($this->_mergeCells[$pRange])) {
1477. unset($this->_mergeCells[$pRange]);
1478. } else {
1479. throw new Exception('Cell range ' . $pRange . ' not known as merged.');
1480. }
1481. } else {
1482. throw new Exception('Merge can only be removed from a range of cells.');
1483. }
1485. return $this;
1486. }
1488. /**
1489. * Remove merge on a cell range by using numeric cell coordinates
1490. *
1491. * @param int $pColumn1 Numeric column coordinate of the first cell
1492. * @param int $pRow1 Numeric row coordinate of the first cell
1493. * @param int $pColumn2 Numeric column coordinate of the last cell
1494. * @param int $pRow2 Numeric row coordinate of the last cell
1495. * @throws Exception
1496. * @return PHPExcel_Worksheet
1497. */
1498. public function unmergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1499. {
1500. $cellRange = PHPExcel_Cell:: stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell:: stringFromColumnIndex($pColumn2) . $pRow2;
1501. return $this-> unmergeCells($cellRange);
1502. }
1504. /**
1505. * Get merge cells array.
1506. *
1507. * @return array[]
1508. */
1509. public function getMergeCells()
1510. {
1511. return $this->_mergeCells;
1512. }
1514. /**
1515. * Set merge cells array for the entire sheet. Use instead mergeCells() to merge
1516. * a single cell range.
1517. *
1518. * @param array
1519. */
1520. public function setMergeCells($pValue = array())
1521. {
1522. $this->_mergeCells = $pValue;
1524. return $this;
1525. }
1527. /**
1528. * Set protection on a cell range
1529. *
1530. * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1531. * @param string $pPassword Password to unlock the protection
1532. * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1533. * @throws Exception
1534. * @return PHPExcel_Worksheet
1535. */
1536. public function protectCells($pRange = 'A1', $pPassword = '', $pAlreadyHashed = false)
1537. {
1538. // Uppercase coordinate
1539. $pRange = strtoupper($pRange);
1541. if (!$pAlreadyHashed) {
1542. $pPassword = PHPExcel_Shared_PasswordHasher:: hashPassword($pPassword);
1543. }
1544. $this->_protectedCells[$pRange] = $pPassword;
1546. return $this;
1547. }
1549. /**
1550. * Set protection on a cell range by using numeric cell coordinates
1551. *
1552. * @param int $pColumn1 Numeric column coordinate of the first cell
1553. * @param int $pRow1 Numeric row coordinate of the first cell
1554. * @param int $pColumn2 Numeric column coordinate of the last cell
1555. * @param int $pRow2 Numeric row coordinate of the last cell
1556. * @param string $pPassword Password to unlock the protection
1557. * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1558. * @throws Exception
1559. * @return PHPExcel_Worksheet
1560. */
1561. public function protectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1562. {
1563. $cellRange = PHPExcel_Cell:: stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell:: stringFromColumnIndex($pColumn2) . $pRow2;
1564. return $this-> protectCells($cellRange, $pPassword, $pAlreadyHashed);
1565. }
1567. /**
1568. * Remove protection on a cell range
1569. *
1570. * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1571. * @throws Exception
1572. * @return PHPExcel_Worksheet
1573. */
1574. public function unprotectCells($pRange = 'A1')
1575. {
1576. // Uppercase coordinate
1577. $pRange = strtoupper($pRange);
1579. if (isset($this->_protectedCells[$pRange])) {
1580. unset($this->_protectedCells[$pRange]);
1581. } else {
1582. throw new Exception('Cell range ' . $pRange . ' not known as protected.');
1583. }
1584. return $this;
1585. }
1587. /**
1588. * Remove protection on a cell range by using numeric cell coordinates
1589. *
1590. * @param int $pColumn1 Numeric column coordinate of the first cell
1591. * @param int $pRow1 Numeric row coordinate of the first cell
1592. * @param int $pColumn2 Numeric column coordinate of the last cell
1593. * @param int $pRow2 Numeric row coordinate of the last cell
1594. * @param string $pPassword Password to unlock the protection
1595. * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1596. * @throws Exception
1597. * @return PHPExcel_Worksheet
1598. */
1599. public function unprotectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1600. {
1601. $cellRange = PHPExcel_Cell:: stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell:: stringFromColumnIndex($pColumn2) . $pRow2;
1602. return $this-> unprotectCells($cellRange, $pPassword, $pAlreadyHashed);
1603. }
1605. /**
1606. * Get protected cells
1607. *
1608. * @return array[]
1609. */
1610. public function getProtectedCells()
1611. {
1612. return $this->_protectedCells;
1613. }
1615. /**
1616. * Get Autofilter Range
1617. *
1618. * @return string
1619. */
1620. public function getAutoFilter()
1621. {
1622. return $this->_autoFilter;
1623. }
1625. /**
1626. * Set Autofilter Range
1627. *
1628. * @param string $pRange Cell range (i.e. A1:E10)
1629. * @throws Exception
1630. * @return PHPExcel_Worksheet
1631. */
1632. public function setAutoFilter($pRange = '')
1633. {
1634. // Uppercase coordinate
1635. $pRange = strtoupper($pRange);
1637. if ( strpos($pRange,':') !== false) {
1638. $this->_autoFilter = $pRange;
1639. $this->_dirty = true;
1640. } else {
1641. throw new Exception('Autofilter must be set on a range of cells.');
1642. }
1643. return $this;
1644. }
1646. /**
1647. * Set Autofilter Range by using numeric cell coordinates
1648. *
1649. * @param int $pColumn1 Numeric column coordinate of the first cell
1650. * @param int $pRow1 Numeric row coordinate of the first cell
1651. * @param int $pColumn2 Numeric column coordinate of the second cell
1652. * @param int $pRow2 Numeric row coordinate of the second cell
1653. * @throws Exception
1654. * @return PHPExcel_Worksheet
1655. */
1656. public function setAutoFilterByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1657. {
1658. return $this-> setAutoFilter(
1659. PHPExcel_Cell:: stringFromColumnIndex($pColumn1) . $pRow1
1660. . ':' .
1661. PHPExcel_Cell:: stringFromColumnIndex($pColumn2) . $pRow2
1662. );
1663. }
1665. /**
1666. * Remove autofilter
1667. *
1668. * @return PHPExcel_Worksheet
1669. */
1670. public function removeAutoFilter()
1671. {
1672. $this->_autoFilter = '';
1673. return $this;
1674. }
1676. /**
1677. * Get Freeze Pane
1678. *
1679. * @return string
1680. */
1681. public function getFreezePane()
1682. {
1683. return $this->_freezePane;
1684. }
1686. /**
1687. * Freeze Pane
1688. *
1689. * @param string $pCell Cell (i.e. A1)
1690. * @throws Exception
1691. * @return PHPExcel_Worksheet
1692. */
1693. public function freezePane($pCell = '')
1694. {
1695. // Uppercase coordinate
1696. $pCell = strtoupper($pCell);
1698. if ( strpos($pCell,':') === false && strpos($pCell,',') === false) {
1699. $this->_freezePane = $pCell;
1700. } else {
1701. throw new Exception('Freeze pane can not be set on a range of cells.');
1702. }
1703. return $this;
1704. }
1706. /**
1707. * Freeze Pane by using numeric cell coordinates
1708. *
1709. * @param int $pColumn Numeric column coordinate of the cell
1710. * @param int $pRow Numeric row coordinate of the cell
1711. * @throws Exception
1712. * @return PHPExcel_Worksheet
1713. */
1714. public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1)
1715. {
1716. return $this-> freezePane( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
1717. }
1719. /**
1720. * Unfreeze Pane
1721. *
1722. * @return PHPExcel_Worksheet
1723. */
1724. public function unfreezePane()
1725. {
1726. return $this-> freezePane('');
1727. }
1729. /**
1730. * Insert a new row, updating all possible related data
1731. *
1732. * @param int $pBefore Insert before this one
1733. * @param int $pNumRows Number of rows to insert
1734. * @throws Exception
1735. * @return PHPExcel_Worksheet
1736. */
1737. public function insertNewRowBefore($pBefore = 1, $pNumRows = 1) {
1738. if ($pBefore >= 1) {
1739. $objReferenceHelper = PHPExcel_ReferenceHelper:: getInstance();
1740. $objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);
1741. } else {
1742. throw new Exception("Rows can only be inserted before at least row 1.");
1743. }
1744. return $this;
1745. }
1747. /**
1748. * Insert a new column, updating all possible related data
1749. *
1750. * @param int $pBefore Insert before this one
1751. * @param int $pNumCols Number of columns to insert
1752. * @throws Exception
1753. * @return PHPExcel_Worksheet
1754. */
1755. public function insertNewColumnBefore($pBefore = 'A', $pNumCols = 1) {
1756. if (! is_numeric($pBefore)) {
1757. $objReferenceHelper = PHPExcel_ReferenceHelper:: getInstance();
1758. $objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this);
1759. } else {
1760. throw new Exception("Column references should not be numeric.");
1761. }
1762. return $this;
1763. }
1765. /**
1766. * Insert a new column, updating all possible related data
1767. *
1768. * @param int $pBefore Insert before this one (numeric column coordinate of the cell)
1769. * @param int $pNumCols Number of columns to insert
1770. * @throws Exception
1771. * @return PHPExcel_Worksheet
1772. */
1773. public function insertNewColumnBeforeByIndex($pBefore = 0, $pNumCols = 1) {
1774. if ($pBefore >= 0) {
1775. return $this-> insertNewColumnBefore( PHPExcel_Cell:: stringFromColumnIndex($pBefore), $pNumCols);
1776. } else {
1777. throw new Exception("Columns can only be inserted before at least column A (0).");
1778. }
1779. }
1781. /**
1782. * Delete a row, updating all possible related data
1783. *
1784. * @param int $pRow Remove starting with this one
1785. * @param int $pNumRows Number of rows to remove
1786. * @throws Exception
1787. * @return PHPExcel_Worksheet
1788. */
1789. public function removeRow($pRow = 1, $pNumRows = 1) {
1790. if ($pRow >= 1) {
1791. $objReferenceHelper = PHPExcel_ReferenceHelper:: getInstance();
1792. $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this);
1793. } else {
1794. throw new Exception("Rows to be deleted should at least start from row 1.");
1795. }
1796. return $this;
1797. }
1799. /**
1800. * Remove a column, updating all possible related data
1801. *
1802. * @param int $pColumn Remove starting with this one
1803. * @param int $pNumCols Number of columns to remove
1804. * @throws Exception
1805. * @return PHPExcel_Worksheet
1806. */
1807. public function removeColumn($pColumn = 'A', $pNumCols = 1) {
1808. if (! is_numeric($pColumn)) {
1809. $pColumn = PHPExcel_Cell:: stringFromColumnIndex( PHPExcel_Cell:: columnIndexFromString($pColumn) - 1 + $pNumCols);
1810. $objReferenceHelper = PHPExcel_ReferenceHelper:: getInstance();
1811. $objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this);
1812. } else {
1813. throw new Exception("Column references should not be numeric.");
1814. }
1815. return $this;
1816. }
1818. /**
1819. * Remove a column, updating all possible related data
1820. *
1821. * @param int $pColumn Remove starting with this one (numeric column coordinate of the cell)
1822. * @param int $pNumCols Number of columns to remove
1823. * @throws Exception
1824. * @return PHPExcel_Worksheet
1825. */
1826. public function removeColumnByIndex($pColumn = 0, $pNumCols = 1) {
1827. if ($pColumn >= 0) {
1828. return $this-> removeColumn( PHPExcel_Cell:: stringFromColumnIndex($pColumn), $pNumCols);
1829. } else {
1830. throw new Exception("Columns to be deleted should at least start from column 0");
1831. }
1832. }
1834. /**
1835. * Show gridlines?
1836. *
1837. * @return boolean
1838. */
1839. public function getShowGridlines() {
1840. return $this->_showGridlines;
1841. }
1843. /**
1844. * Set show gridlines
1845. *
1846. * @param boolean $pValue Show gridlines (true/false)
1847. * @return PHPExcel_Worksheet
1848. */
1849. public function setShowGridlines($pValue = false) {
1850. $this->_showGridlines = $pValue;
1851. return $this;
1852. }
1854. /**
1855. * Print gridlines?
1856. *
1857. * @return boolean
1858. */
1859. public function getPrintGridlines() {
1860. return $this->_printGridlines;
1861. }
1863. /**
1864. * Set print gridlines
1865. *
1866. * @param boolean $pValue Print gridlines (true/false)
1867. * @return PHPExcel_Worksheet
1868. */
1869. public function setPrintGridlines($pValue = false) {
1870. $this->_printGridlines = $pValue;
1871. return $this;
1872. }
1874. /**
1875. * Show row and column headers?
1876. *
1877. * @return boolean
1878. */
1879. public function getShowRowColHeaders() {
1880. return $this->_showRowColHeaders;
1881. }
1883. /**
1884. * Set show row and column headers
1885. *
1886. * @param boolean $pValue Show row and column headers (true/false)
1887. * @return PHPExcel_Worksheet
1888. */
1889. public function setShowRowColHeaders($pValue = false) {
1890. $this->_showRowColHeaders = $pValue;
1891. return $this;
1892. }
1894. /**
1895. * Show summary below? (Row/Column outlining)
1896. *
1897. * @return boolean
1898. */
1899. public function getShowSummaryBelow() {
1900. return $this->_showSummaryBelow;
1901. }
1903. /**
1904. * Set show summary below
1905. *
1906. * @param boolean $pValue Show summary below (true/false)
1907. * @return PHPExcel_Worksheet
1908. */
1909. public function setShowSummaryBelow($pValue = true) {
1910. $this->_showSummaryBelow = $pValue;
1911. return $this;
1912. }
1914. /**
1915. * Show summary right? (Row/Column outlining)
1916. *
1917. * @return boolean
1918. */
1919. public function getShowSummaryRight() {
1920. return $this->_showSummaryRight;
1921. }
1923. /**
1924. * Set show summary right
1925. *
1926. * @param boolean $pValue Show summary right (true/false)
1927. * @return PHPExcel_Worksheet
1928. */
1929. public function setShowSummaryRight($pValue = true) {
1930. $this->_showSummaryRight = $pValue;
1931. return $this;
1932. }
1934. /**
1935. * Get comments
1936. *
1937. * @return PHPExcel_Comment[]
1938. */
1939. public function getComments()
1940. {
1941. return $this->_comments;
1942. }
1944. /**
1945. * Set comments array for the entire sheet.
1946. *
1947. * @param array of PHPExcel_Comment
1948. * @return PHPExcel_Worksheet
1949. */
1950. public function setComments($pValue = array())
1951. {
1952. $this->_comments = $pValue;
1954. return $this;
1955. }
1957. /**
1958. * Get comment for cell
1959. *
1960. * @param string $pCellCoordinate Cell coordinate to get comment for
1961. * @return PHPExcel_Comment
1962. * @throws Exception
1963. */
1964. public function getComment($pCellCoordinate = 'A1')
1965. {
1966. // Uppercase coordinate
1967. $pCellCoordinate = strtoupper($pCellCoordinate);
1969. if ( strpos($pCellCoordinate,':') !== false || strpos($pCellCoordinate,',') !== false) {
1970. throw new Exception('Cell coordinate string can not be a range of cells.');
1971. } else if ( strpos($pCellCoordinate,'$') !== false) {
1972. throw new Exception('Cell coordinate string must not be absolute.');
1973. } else if ($pCellCoordinate == '') {
1974. throw new Exception('Cell coordinate can not be zero-length string.');
1975. } else {
1976. // Check if we already have a comment for this cell.
1977. // If not, create a new comment.
1978. if (isset($this->_comments[$pCellCoordinate])) {
1979. return $this->_comments[$pCellCoordinate];
1980. } else {
1981. $newComment = new PHPExcel_Comment();
1982. $this->_comments[$pCellCoordinate] = $newComment;
1983. return $newComment;
1984. }
1985. }
1986. }
1988. /**
1989. * Get comment for cell by using numeric cell coordinates
1990. *
1991. * @param int $pColumn Numeric column coordinate of the cell
1992. * @param int $pRow Numeric row coordinate of the cell
1993. * @return PHPExcel_Comment
1994. */
1995. public function getCommentByColumnAndRow($pColumn = 0, $pRow = 1)
1996. {
1997. return $this-> getComment( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
1998. }
2000. /**
2001. * Get selected cell
2002. *
2003. * @deprecated
2004. * @return string
2005. */
2006. public function getSelectedCell()
2007. {
2008. return $this-> getSelectedCells();
2009. }
2011. /**
2012. * Get active cell
2013. *
2014. * @return string Example: 'A1'
2015. */
2016. public function getActiveCell()
2017. {
2018. return $this->_activeCell;
2019. }
2021. /**
2022. * Get selected cells
2023. *
2024. * @return string
2025. */
2026. public function getSelectedCells()
2027. {
2028. return $this->_selectedCells;
2029. }
2031. /**
2032. * Selected cell
2033. *
2034. * @param string $pCell Cell (i.e. A1)
2035. * @return PHPExcel_Worksheet
2036. */
2037. public function setSelectedCell($pCoordinate = 'A1')
2038. {
2039. return $this-> setSelectedCells($pCoordinate);
2040. }
2042. /**
2043. * Select a range of cells.
2044. *
2045. * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
2046. * @throws Exception
2047. * @return PHPExcel_Worksheet
2048. */
2049. public function setSelectedCells($pCoordinate = 'A1')
2050. {
2051. // Uppercase coordinate
2052. $pCoordinate = strtoupper($pCoordinate);
2054. // Convert 'A' to 'A:A'
2055. $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
2057. // Convert '1' to '1:1'
2058. $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
2060. // Convert 'A:C' to 'A1:C1048576'
2061. $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
2063. // Convert '1:3' to 'A1:XFD3'
2064. $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
2066. if ( strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
2067. list($first, ) = PHPExcel_Cell:: splitRange($pCoordinate);
2068. $this->_activeCell = $first[0];
2069. } else {
2070. $this->_activeCell = $pCoordinate;
2071. }
2072. $this->_selectedCells = $pCoordinate;
2073. return $this;
2074. }
2076. /**
2077. * Selected cell by using numeric cell coordinates
2078. *
2079. * @param int $pColumn Numeric column coordinate of the cell
2080. * @param int $pRow Numeric row coordinate of the cell
2081. * @throws Exception
2082. * @return PHPExcel_Worksheet
2083. */
2084. public function setSelectedCellByColumnAndRow($pColumn = 0, $pRow = 1)
2085. {
2086. return $this-> setSelectedCells( PHPExcel_Cell:: stringFromColumnIndex($pColumn) . $pRow);
2087. }
2089. /**
2090. * Get right-to-left
2091. *
2092. * @return boolean
2093. */
2094. public function getRightToLeft() {
2095. return $this->_rightToLeft;
2096. }
2098. /**
2099. * Set right-to-left
2100. *
2101. * @param boolean $value Right-to-left true/false
2102. * @return PHPExcel_Worksheet
2103. */
2104. public function setRightToLeft($value = false) {
2105. $this->_rightToLeft = $value;
2106. return $this;
2107. }
2109. /**
2110. * Fill worksheet from values in array
2111. *
2112. * @param array $source Source array
2113. * @param mixed $nullValue Value in source array that stands for blank cell
2114. * @param string $startCell Insert array starting from this cell address as the top left coordinate
2115. * @param boolean $strictNullComparison Apply strict comparison when testing for null values in the array
2116. * @throws Exception
2117. * @return PHPExcel_Worksheet
2118. */
2119. public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) {
2120. if ( is_array($source)) {
2121. // Convert a 1-D array to 2-D (for ease of looping)
2122. if (! is_array( end($source))) {
2123. $source = array($source);
2124. }
2126. // start coordinate
2127. list ($startColumn, $startRow) = PHPExcel_Cell:: coordinateFromString($startCell);
2129. // Loop through $source
2130. foreach ($source as $rowData) {
2131. $currentColumn = $startColumn;
2132. foreach($rowData as $cellValue) {
2133. if ($strictNullComparison) {
2134. if ($cellValue !== $nullValue) {
2135. // Set cell value
2136. $this-> getCell($currentColumn . $startRow)->setValue($cellValue);
2137. }
2138. } else {
2139. if ($cellValue != $nullValue) {
2140. // Set cell value
2141. $this-> getCell($currentColumn . $startRow)->setValue($cellValue);
2142. }
2143. }
2144. ++$currentColumn;
2145. }
2146. ++$startRow;
2147. }
2148. } else {
2149. throw new Exception("Parameter \$source should be an array.");
2150. }
2151. return $this;
2152. }
2154. /**
2155. * Create array from a range of cells
2156. *
2157. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
2158. * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2159. * @param boolean $calculateFormulas Should formulas be calculated?
2160. * @param boolean $formatData Should formatting be applied to cell values?
2161. * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2162. * True - Return rows and columns indexed by their actual row and column IDs
2163. * @return array
2164. */
2165. public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2166. // Returnvalue
2167. $returnValue = array();
2169. // Identify the range that we need to extract from the worksheet
2170. list($rangeStart, $rangeEnd) = PHPExcel_Cell:: rangeBoundaries($pRange);
2171. $minCol = PHPExcel_Cell:: stringFromColumnIndex($rangeStart[0] -1);
2172. $minRow = $rangeStart[1];
2173. $maxCol = PHPExcel_Cell:: stringFromColumnIndex($rangeEnd[0] -1);
2174. $maxRow = $rangeEnd[1];
2176. $maxCol++;
2178. // Loop through rows
2179. for ($row = $minRow; $row <= $maxRow; ++$row) {
2180. $c = -1;
2181. // Loop through columns in the current row
2182. for ($col = $minCol; $col != $maxCol; ++$col) {
2183. $rRef = ($returnCellRef) ? $row : $row-1;
2184. $cRef = ($returnCellRef) ? $col : ++$c;
2185. // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
2186. // so we test and retrieve directly against _cellCollection
2187. if ($this->_cellCollection->isDataSet($col.$row)) {
2188. // Cell exists
2189. $cell = $this->_cellCollection->getCacheData($col.$row);
2190. if ($cell->getValue() !== null) {
2191. if ($cell->getValue() instanceof PHPExcel_RichText) {
2192. $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
2193. } else {
2194. if ($calculateFormulas) {
2195. $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
2196. } else {
2197. $returnValue[$rRef][$cRef] = $cell->getValue();
2198. }
2199. }
2201. if ($formatData) {
2202. $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
2203. $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat:: toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
2204. }
2205. } else {
2206. // Cell holds a NULL
2207. $returnValue[$rRef][$cRef] = $nullValue;
2208. }
2209. } else {
2210. // Cell doesn't exist
2211. $returnValue[$rRef][$cRef] = $nullValue;
2212. }
2213. }
2214. }
2216. // Return
2217. return $returnValue;
2218. }
2221. /**
2222. * Create array from a range of cells
2223. *
2224. * @param string $pNamedRange Name of the Named Range
2225. * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2226. * @param boolean $calculateFormulas Should formulas be calculated?
2227. * @param boolean $formatData Should formatting be applied to cell values?
2228. * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2229. * True - Return rows and columns indexed by their actual row and column IDs
2230. * @return array
2231. * @throws Exception
2232. */
2233. public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2234. $namedRange = PHPExcel_NamedRange:: resolveRange($pNamedRange, $this);
2235. if (! is_null($namedRange)) {
2236. $pWorkSheet = $namedRange->getWorksheet();
2237. $pCellRange = $namedRange->getRange();
2239. return $pWorkSheet->rangeToArray( $pCellRange,
2240. $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2241. }
2243. throw new Exception('Named Range '.$pNamedRange.' does not exist.');
2244. }
2247. /**
2248. * Create array from worksheet
2249. *
2250. * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2251. * @param boolean $calculateFormulas Should formulas be calculated?
2252. * @param boolean $formatData Should formatting be applied to cell values?
2253. * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2254. * True - Return rows and columns indexed by their actual row and column IDs
2255. * @return array
2256. */
2257. public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2258. // Garbage collect...
2259. $this-> garbageCollect();
2261. // Identify the range that we need to extract from the worksheet
2262. $maxCol = $this-> getHighestColumn();
2263. $maxRow = $this-> getHighestRow();
2265. // Return
2266. return $this-> rangeToArray( 'A1:'.$maxCol.$maxRow,
2267. $nullValue, $calculateFormulas, $formatData, $returnCellRef);
2268. }
2270. /**
2271. * Get row iterator
2272. *
2273. * @return PHPExcel_Worksheet_RowIterator
2274. */
2275. public function getRowIterator() {
2276. return new PHPExcel_Worksheet_RowIterator($this);
2277. }
2279. /**
2280. * Run PHPExcel garabage collector.
2281. *
2282. * @return PHPExcel_Worksheet
2283. */
2284. public function garbageCollect() {
2285. // Build a reference table from images
2286. // $imageCoordinates = array();
2287. // $iterator = $this->getDrawingCollection()->getIterator();
2288. // while ($iterator->valid()) {
2289. // $imageCoordinates[$iterator->current()->getCoordinates()] = true;
2290. //
2291. // $iterator->next();
2292. // }
2293. //
2294. // Lookup highest column and highest row if cells are cleaned
2295. $highestColumn = -1;
2296. $highestRow = 1;
2298. // Find cells that can be cleaned
2299. $col = $row = array();
2300. foreach ($this->_cellCollection->getCellList() as $coord) {
2301. list($c,$r) = sscanf($coord,'%[A-Z]%d');
2302. $row[$r] = $r;
2303. $col[$c] = strlen($c).$c;
2304. }
2305. if ( count($row) > 0) {
2306. // Determine highest column and row
2307. $highestRow = max($row);
2308. $highestColumn = PHPExcel_Cell:: columnIndexFromString( substr( max($col),1));
2309. }
2311. // Loop through column dimensions
2312. foreach ($this->_columnDimensions as $dimension) {
2313. $highestColumn = max($highestColumn, PHPExcel_Cell:: columnIndexFromString($dimension->getColumnIndex()));
2314. }
2316. // Loop through row dimensions
2317. foreach ($this->_rowDimensions as $dimension) {
2318. $highestRow = max($highestRow,$dimension->getRowIndex());
2319. }
2321. // Cache values
2322. if ($highestColumn < 0) {
2323. $this->_cachedHighestColumn = 'A';
2324. } else {
2325. $this->_cachedHighestColumn = PHPExcel_Cell:: stringFromColumnIndex(--$highestColumn);
2326. }
2327. $this->_cachedHighestRow = $highestRow;
2329. // Return
2330. return $this;
2331. }
2333. /**
2334. * Get hash code
2335. *
2336. * @return string Hash code
2337. */
2338. public function getHashCode() {
2339. if ($this->_dirty) {
2340. $this->_hash = md5( $this->_title .
2341. $this->_autoFilter .
2342. ($this->_protection-> isProtectionEnabled() ? 't' : 'f') .
2343. __CLASS__
2344. );
2345. $this->_dirty = false;
2346. }
2347. return $this->_hash;
2348. }
2350. /**
2351. * Extract worksheet title from range.
2352. *
2353. * Example: extractSheetTitle("testSheet!A1") ==> 'A1'
2354. * Example: extractSheetTitle("'testSheet 1'!A1", true) ==> array('testSheet 1', 'A1');
2355. *
2356. * @param string $pRange Range to extract title from
2357. * @param bool $returnRange Return range? (see example)
2358. * @return mixed
2359. */
2360. public static function extractSheetTitle($pRange, $returnRange = false) {
2361. // Sheet title included?
2362. if (($sep = strpos($pRange, '!')) === false) {
2363. return '';
2364. }
2366. if ($returnRange) {
2367. return array( trim( substr($pRange, 0, $sep),"'"),
2368. substr($pRange, $sep + 1)
2369. );
2370. }
2372. return substr($pRange, $sep + 1);
2373. }
2375. /**
2376. * Get hyperlink
2377. *
2378. * @param string $pCellCoordinate Cell coordinate to get hyperlink for
2379. */
2380. public function getHyperlink($pCellCoordinate = 'A1')
2381. {
2382. // return hyperlink if we already have one
2383. if (isset($this->_hyperlinkCollection[$pCellCoordinate])) {
2384. return $this->_hyperlinkCollection[$pCellCoordinate];
2385. }
2387. // else create hyperlink
2388. $this->_hyperlinkCollection[$pCellCoordinate] = new PHPExcel_Cell_Hyperlink();
2389. return $this->_hyperlinkCollection[$pCellCoordinate];
2390. }
2392. /**
2393. * Set hyperlnk
2394. *
2395. * @param string $pCellCoordinate Cell coordinate to insert hyperlink
2396. * @param PHPExcel_Cell_Hyperlink $pHyperlink
2397. * @return PHPExcel_Worksheet
2398. */
2399. public function setHyperlink($pCellCoordinate = 'A1', PHPExcel_Cell_Hyperlink $pHyperlink = null)
2400. {
2401. if ($pHyperlink === null) {
2402. unset($this->_hyperlinkCollection[$pCellCoordinate]);
2403. } else {
2404. $this->_hyperlinkCollection[$pCellCoordinate] = $pHyperlink;
2405. }
2406. return $this;
2407. }
2409. /**
2410. * Hyperlink at a specific coordinate exists?
2411. *
2412. * @param string $pCellCoordinate
2413. * @return boolean
2414. */
2415. public function hyperlinkExists($pCoordinate = 'A1')
2416. {
2417. return isset($this->_hyperlinkCollection[$pCoordinate]);
2418. }
2420. /**
2421. * Get collection of hyperlinks
2422. *
2423. * @return PHPExcel_Cell_Hyperlink[]
2424. */
2425. public function getHyperlinkCollection()
2426. {
2427. return $this->_hyperlinkCollection;
2428. }
2430. /**
2431. * Get data validation
2432. *
2433. * @param string $pCellCoordinate Cell coordinate to get data validation for
2434. */
2435. public function getDataValidation($pCellCoordinate = 'A1')
2436. {
2437. // return data validation if we already have one
2438. if (isset($this->_dataValidationCollection[$pCellCoordinate])) {
2439. return $this->_dataValidationCollection[$pCellCoordinate];
2440. }
2442. // else create data validation
2443. $this->_dataValidationCollection[$pCellCoordinate] = new PHPExcel_Cell_DataValidation();
2444. return $this->_dataValidationCollection[$pCellCoordinate];
2445. }
2447. /**
2448. * Set data validation
2449. *
2450. * @param string $pCellCoordinate Cell coordinate to insert data validation
2451. * @param PHPExcel_Cell_DataValidation $pDataValidation
2452. * @return PHPExcel_Worksheet
2453. */
2454. public function setDataValidation($pCellCoordinate = 'A1', PHPExcel_Cell_DataValidation $pDataValidation = null)
2455. {
2456. if ($pDataValidation === null) {
2457. unset($this->_dataValidationCollection[$pCellCoordinate]);
2458. } else {
2459. $this->_dataValidationCollection[$pCellCoordinate] = $pDataValidation;
2460. }
2461. return $this;
2462. }
2464. /**
2465. * Data validation at a specific coordinate exists?
2466. *
2467. * @param string $pCellCoordinate
2468. * @return boolean
2469. */
2470. public function dataValidationExists($pCoordinate = 'A1')
2471. {
2472. return isset($this->_dataValidationCollection[$pCoordinate]);
2473. }
2475. /**
2476. * Get collection of data validations
2477. *
2478. * @return PHPExcel_Cell_DataValidation[]
2479. */
2480. public function getDataValidationCollection()
2481. {
2482. return $this->_dataValidationCollection;
2483. }
2485. /**
2486. * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet
2487. *
2488. * @param string $range
2489. * @return string Adjusted range value
2490. */
2491. public function shrinkRangeToFit($range) {
2492. $maxCol = $this-> getHighestColumn();
2493. $maxRow = $this-> getHighestRow();
2494. $maxCol = PHPExcel_Cell:: columnIndexFromString($maxCol);
2496. $rangeBlocks = explode(' ',$range);
2497. foreach ($rangeBlocks as &$rangeSet) {
2498. $rangeBoundaries = PHPExcel_Cell:: getRangeBoundaries($rangeSet);
2500. if ( PHPExcel_Cell:: columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { $rangeBoundaries[0][0] = PHPExcel_Cell:: stringFromColumnIndex($maxCol); }
2501. if ($rangeBoundaries[0][1] > $maxRow) { $rangeBoundaries[0][1] = $maxRow; }
2502. if ( PHPExcel_Cell:: columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { $rangeBoundaries[1][0] = PHPExcel_Cell:: stringFromColumnIndex($maxCol); }
2503. if ($rangeBoundaries[1][1] > $maxRow) { $rangeBoundaries[1][1] = $maxRow; }
2504. $rangeSet = $rangeBoundaries[0][0].$rangeBoundaries[0][1].':'.$rangeBoundaries[1][0].$rangeBoundaries[1][1];
2505. }
2506. unset($rangeSet);
2507. $stRange = implode(' ',$rangeBlocks);
2509. return $stRange;
2510. }
2513. /**
2514. * Get tab color
2515. *
2516. * @return PHPExcel_Style_Color
2517. */
2518. public function getTabColor()
2519. {
2520. if ( is_null($this->_tabColor))
2521. $this->_tabColor = new PHPExcel_Style_Color();
2523. return $this->_tabColor;
2524. }
2526. /**
2527. * Reset tab color
2528. *
2529. * @return PHPExcel_Worksheet
2530. */
2531. public function resetTabColor()
2532. {
2533. $this->_tabColor = null;
2534. unset($this->_tabColor);
2536. return $this;
2537. }
2539. /**
2540. * Tab color set?
2541. *
2542. * @return boolean
2543. */
2544. public function isTabColorSet()
2545. {
2546. return ! is_null($this->_tabColor);
2547. }
2549. /**
2550. * Copy worksheet (!= clone!)
2551. *
2552. * @return PHPExcel_Worksheet
2553. */
2554. public function copy() {
2555. $copied = clone $this;
2557. return $copied;
2558. }
2560. /**
2561. * Implement PHP __clone to create a deep clone, not just a shallow copy.
2562. */
2563. public function __clone() {
2564. foreach ($this as $key => $val) {
2565. if ($key == '_parent') {
2566. continue;
2567. }
2569. if ( is_object($val) || ( is_array($val))) {
2570. if ($key == '_cellCollection') {
2571. $newCollection = clone $this->_cellCollection;
2572. $newCollection->copyCellCollection($this);
2573. $this->_cellCollection = $newCollection;
2574. } elseif ($key == '_drawingCollection') {
2575. $newCollection = clone $this->_drawingCollection;
2576. $this->_drawingCollection = $newCollection;
2577. } else {
2578. $this->{$key} = unserialize( serialize($val));
2579. }
2580. }
2581. }
2582. }
2583. }

Documentation generated on Sun, 27 Feb 2011 16:37:13 -0800 by phpDocumentor 1.4.3