PageRenderTime 56ms CodeModel.GetById 19ms RepoModel.GetById 1ms app.codeStats 0ms

/PHPExcel_1.7.8-with_documentation-msoffice_format/Classes/PHPExcel/Worksheet.php

https://bitbucket.org/izubizarreta/https-bitbucket.org-bityvip
PHP | 2795 lines | 2136 code | 118 blank | 541 comment | 83 complexity | ae06431cdbb4b19615c2c0bff42be0f4 MD5 | raw file
Possible License(s): LGPL-3.0, LGPL-2.0, JSON, GPL-2.0, BSD-3-Clause, LGPL-2.1, MIT

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2012 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Worksheet
  23. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.8, 2012-10-12
  26. */
  27. /**
  28. * PHPExcel_Worksheet
  29. *
  30. * @category PHPExcel
  31. * @package PHPExcel_Worksheet
  32. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  33. */
  34. class PHPExcel_Worksheet implements PHPExcel_IComparable
  35. {
  36. /* Break types */
  37. const BREAK_NONE = 0;
  38. const BREAK_ROW = 1;
  39. const BREAK_COLUMN = 2;
  40. /* Sheet state */
  41. const SHEETSTATE_VISIBLE = 'visible';
  42. const SHEETSTATE_HIDDEN = 'hidden';
  43. const SHEETSTATE_VERYHIDDEN = 'veryHidden';
  44. /**
  45. * Invalid characters in sheet title
  46. *
  47. * @var array
  48. */
  49. private static $_invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
  50. /**
  51. * Parent spreadsheet
  52. *
  53. * @var PHPExcel
  54. */
  55. private $_parent;
  56. /**
  57. * Cacheable collection of cells
  58. *
  59. * @var PHPExcel_CachedObjectStorage_xxx
  60. */
  61. private $_cellCollection = null;
  62. /**
  63. * Collection of row dimensions
  64. *
  65. * @var PHPExcel_Worksheet_RowDimension[]
  66. */
  67. private $_rowDimensions = array();
  68. /**
  69. * Default row dimension
  70. *
  71. * @var PHPExcel_Worksheet_RowDimension
  72. */
  73. private $_defaultRowDimension = null;
  74. /**
  75. * Collection of column dimensions
  76. *
  77. * @var PHPExcel_Worksheet_ColumnDimension[]
  78. */
  79. private $_columnDimensions = array();
  80. /**
  81. * Default column dimension
  82. *
  83. * @var PHPExcel_Worksheet_ColumnDimension
  84. */
  85. private $_defaultColumnDimension = null;
  86. /**
  87. * Collection of drawings
  88. *
  89. * @var PHPExcel_Worksheet_BaseDrawing[]
  90. */
  91. private $_drawingCollection = null;
  92. /**
  93. * Collection of Chart objects
  94. *
  95. * @var PHPExcel_Chart[]
  96. */
  97. private $_chartCollection = array();
  98. /**
  99. * Worksheet title
  100. *
  101. * @var string
  102. */
  103. private $_title;
  104. /**
  105. * Sheet state
  106. *
  107. * @var string
  108. */
  109. private $_sheetState;
  110. /**
  111. * Page setup
  112. *
  113. * @var PHPExcel_Worksheet_PageSetup
  114. */
  115. private $_pageSetup;
  116. /**
  117. * Page margins
  118. *
  119. * @var PHPExcel_Worksheet_PageMargins
  120. */
  121. private $_pageMargins;
  122. /**
  123. * Page header/footer
  124. *
  125. * @var PHPExcel_Worksheet_HeaderFooter
  126. */
  127. private $_headerFooter;
  128. /**
  129. * Sheet view
  130. *
  131. * @var PHPExcel_Worksheet_SheetView
  132. */
  133. private $_sheetView;
  134. /**
  135. * Protection
  136. *
  137. * @var PHPExcel_Worksheet_Protection
  138. */
  139. private $_protection;
  140. /**
  141. * Collection of styles
  142. *
  143. * @var PHPExcel_Style[]
  144. */
  145. private $_styles = array();
  146. /**
  147. * Conditional styles. Indexed by cell coordinate, e.g. 'A1'
  148. *
  149. * @var array
  150. */
  151. private $_conditionalStylesCollection = array();
  152. /**
  153. * Is the current cell collection sorted already?
  154. *
  155. * @var boolean
  156. */
  157. private $_cellCollectionIsSorted = false;
  158. /**
  159. * Collection of breaks
  160. *
  161. * @var array
  162. */
  163. private $_breaks = array();
  164. /**
  165. * Collection of merged cell ranges
  166. *
  167. * @var array
  168. */
  169. private $_mergeCells = array();
  170. /**
  171. * Collection of protected cell ranges
  172. *
  173. * @var array
  174. */
  175. private $_protectedCells = array();
  176. /**
  177. * Autofilter Range and selection
  178. *
  179. * @var PHPExcel_Worksheet_AutoFilter
  180. */
  181. private $_autoFilter = NULL;
  182. /**
  183. * Freeze pane
  184. *
  185. * @var string
  186. */
  187. private $_freezePane = '';
  188. /**
  189. * Show gridlines?
  190. *
  191. * @var boolean
  192. */
  193. private $_showGridlines = true;
  194. /**
  195. * Print gridlines?
  196. *
  197. * @var boolean
  198. */
  199. private $_printGridlines = false;
  200. /**
  201. * Show row and column headers?
  202. *
  203. * @var boolean
  204. */
  205. private $_showRowColHeaders = true;
  206. /**
  207. * Show summary below? (Row/Column outline)
  208. *
  209. * @var boolean
  210. */
  211. private $_showSummaryBelow = true;
  212. /**
  213. * Show summary right? (Row/Column outline)
  214. *
  215. * @var boolean
  216. */
  217. private $_showSummaryRight = true;
  218. /**
  219. * Collection of comments
  220. *
  221. * @var PHPExcel_Comment[]
  222. */
  223. private $_comments = array();
  224. /**
  225. * Active cell. (Only one!)
  226. *
  227. * @var string
  228. */
  229. private $_activeCell = 'A1';
  230. /**
  231. * Selected cells
  232. *
  233. * @var string
  234. */
  235. private $_selectedCells = 'A1';
  236. /**
  237. * Cached highest column
  238. *
  239. * @var string
  240. */
  241. private $_cachedHighestColumn = 'A';
  242. /**
  243. * Cached highest row
  244. *
  245. * @var int
  246. */
  247. private $_cachedHighestRow = 1;
  248. /**
  249. * Right-to-left?
  250. *
  251. * @var boolean
  252. */
  253. private $_rightToLeft = false;
  254. /**
  255. * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'
  256. *
  257. * @var array
  258. */
  259. private $_hyperlinkCollection = array();
  260. /**
  261. * Data validation objects. Indexed by cell coordinate, e.g. 'A1'
  262. *
  263. * @var array
  264. */
  265. private $_dataValidationCollection = array();
  266. /**
  267. * Tab color
  268. *
  269. * @var PHPExcel_Style_Color
  270. */
  271. private $_tabColor;
  272. /**
  273. * Dirty flag
  274. *
  275. * @var boolean
  276. */
  277. private $_dirty = true;
  278. /**
  279. * Hash
  280. *
  281. * @var string
  282. */
  283. private $_hash = null;
  284. /**
  285. * Create a new worksheet
  286. *
  287. * @param PHPExcel $pParent
  288. * @param string $pTitle
  289. */
  290. public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
  291. {
  292. // Set parent and title
  293. $this->_parent = $pParent;
  294. $this->setTitle($pTitle, FALSE);
  295. $this->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VISIBLE);
  296. $this->_cellCollection = PHPExcel_CachedObjectStorageFactory::getInstance($this);
  297. // Set page setup
  298. $this->_pageSetup = new PHPExcel_Worksheet_PageSetup();
  299. // Set page margins
  300. $this->_pageMargins = new PHPExcel_Worksheet_PageMargins();
  301. // Set page header/footer
  302. $this->_headerFooter = new PHPExcel_Worksheet_HeaderFooter();
  303. // Set sheet view
  304. $this->_sheetView = new PHPExcel_Worksheet_SheetView();
  305. // Drawing collection
  306. $this->_drawingCollection = new ArrayObject();
  307. // Chart collection
  308. $this->_chartCollection = new ArrayObject();
  309. // Protection
  310. $this->_protection = new PHPExcel_Worksheet_Protection();
  311. // Default row dimension
  312. $this->_defaultRowDimension = new PHPExcel_Worksheet_RowDimension(NULL);
  313. // Default column dimension
  314. $this->_defaultColumnDimension = new PHPExcel_Worksheet_ColumnDimension(NULL);
  315. $this->_autoFilter = new PHPExcel_Worksheet_AutoFilter(NULL, $this);
  316. }
  317. /**
  318. * Disconnect all cells from this PHPExcel_Worksheet object,
  319. * typically so that the worksheet object can be unset
  320. *
  321. */
  322. public function disconnectCells() {
  323. $this->_cellCollection->unsetWorksheetCells();
  324. $this->_cellCollection = null;
  325. // detach ourself from the workbook, so that it can then delete this worksheet successfully
  326. $this->_parent = null;
  327. }
  328. /**
  329. * Return the cache controller for the cell collection
  330. *
  331. * @return PHPExcel_CachedObjectStorage_xxx
  332. */
  333. public function getCellCacheController() {
  334. return $this->_cellCollection;
  335. } // function getCellCacheController()
  336. /**
  337. * Get array of invalid characters for sheet title
  338. *
  339. * @return array
  340. */
  341. public static function getInvalidCharacters()
  342. {
  343. return self::$_invalidCharacters;
  344. }
  345. /**
  346. * Check sheet title for valid Excel syntax
  347. *
  348. * @param string $pValue The string to check
  349. * @return string The valid string
  350. * @throws Exception
  351. */
  352. private static function _checkSheetTitle($pValue)
  353. {
  354. // Some of the printable ASCII characters are invalid: * : / \ ? [ ]
  355. if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
  356. throw new Exception('Invalid character found in sheet title');
  357. }
  358. // Maximum 31 characters allowed for sheet title
  359. if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) {
  360. throw new Exception('Maximum 31 characters allowed in sheet title.');
  361. }
  362. return $pValue;
  363. }
  364. /**
  365. * Get collection of cells
  366. *
  367. * @param boolean $pSorted Also sort the cell collection?
  368. * @return PHPExcel_Cell[]
  369. */
  370. public function getCellCollection($pSorted = true)
  371. {
  372. if ($pSorted) {
  373. // Re-order cell collection
  374. return $this->sortCellCollection();
  375. }
  376. if ($this->_cellCollection !== NULL) {
  377. return $this->_cellCollection->getCellList();
  378. }
  379. return array();
  380. }
  381. /**
  382. * Sort collection of cells
  383. *
  384. * @return PHPExcel_Worksheet
  385. */
  386. public function sortCellCollection()
  387. {
  388. if ($this->_cellCollection !== NULL) {
  389. return $this->_cellCollection->getSortedCellList();
  390. }
  391. return array();
  392. }
  393. /**
  394. * Get collection of row dimensions
  395. *
  396. * @return PHPExcel_Worksheet_RowDimension[]
  397. */
  398. public function getRowDimensions()
  399. {
  400. return $this->_rowDimensions;
  401. }
  402. /**
  403. * Get default row dimension
  404. *
  405. * @return PHPExcel_Worksheet_RowDimension
  406. */
  407. public function getDefaultRowDimension()
  408. {
  409. return $this->_defaultRowDimension;
  410. }
  411. /**
  412. * Get collection of column dimensions
  413. *
  414. * @return PHPExcel_Worksheet_ColumnDimension[]
  415. */
  416. public function getColumnDimensions()
  417. {
  418. return $this->_columnDimensions;
  419. }
  420. /**
  421. * Get default column dimension
  422. *
  423. * @return PHPExcel_Worksheet_ColumnDimension
  424. */
  425. public function getDefaultColumnDimension()
  426. {
  427. return $this->_defaultColumnDimension;
  428. }
  429. /**
  430. * Get collection of drawings
  431. *
  432. * @return PHPExcel_Worksheet_BaseDrawing[]
  433. */
  434. public function getDrawingCollection()
  435. {
  436. return $this->_drawingCollection;
  437. }
  438. /**
  439. * Get collection of charts
  440. *
  441. * @return PHPExcel_Chart[]
  442. */
  443. public function getChartCollection()
  444. {
  445. return $this->_chartCollection;
  446. }
  447. /**
  448. * Add chart
  449. *
  450. * @param PHPExcel_Chart $pChart
  451. * @param int|null $iChartIndex Index where chart should go (0,1,..., or null for last)
  452. * @return PHPExcel_Chart
  453. * @throws Exception
  454. */
  455. public function addChart(PHPExcel_Chart $pChart = null, $iChartIndex = null)
  456. {
  457. $pChart->setWorksheet($this);
  458. if (is_null($iChartIndex)) {
  459. $this->_chartCollection[] = $pChart;
  460. } else {
  461. // Insert the chart at the requested index
  462. array_splice($this->_chartCollection, $iChartIndex, 0, array($pChart));
  463. }
  464. return $pChart;
  465. }
  466. /**
  467. * Return the count of charts on this worksheet
  468. *
  469. * @return int The number of charts
  470. * @throws Exception
  471. */
  472. public function getChartCount()
  473. {
  474. return count($this->_chartCollection);
  475. }
  476. /**
  477. * Get a chart by its index position
  478. *
  479. * @param string $index Chart index position
  480. * @return false|PHPExcel_Chart
  481. * @throws Exception
  482. */
  483. public function getChartByIndex($index = null)
  484. {
  485. $chartCount = count($this->_chartCollection);
  486. if ($chartCount == 0) {
  487. return false;
  488. }
  489. if (is_null($index)) {
  490. $index = --$chartCount;
  491. }
  492. if (!isset($this->_chartCollection[$index])) {
  493. return false;
  494. }
  495. return $this->_chartCollection[$index];
  496. }
  497. /**
  498. * Return an array of the names of charts on this worksheet
  499. *
  500. * @return string[] The names of charts
  501. * @throws Exception
  502. */
  503. public function getChartNames()
  504. {
  505. $chartNames = array();
  506. foreach($this->_chartCollection as $chart) {
  507. $chartNames[] = $chart->getName();
  508. }
  509. return $chartNames;
  510. }
  511. /**
  512. * Get a chart by name
  513. *
  514. * @param string $chartName Chart name
  515. * @return false|PHPExcel_Chart
  516. * @throws Exception
  517. */
  518. public function getChartByName($chartName = '')
  519. {
  520. $chartCount = count($this->_chartCollection);
  521. if ($chartCount == 0) {
  522. return false;
  523. }
  524. foreach($this->_chartCollection as $index => $chart) {
  525. if ($chart->getName() == $chartName) {
  526. return $this->_chartCollection[$index];
  527. }
  528. }
  529. return false;
  530. }
  531. /**
  532. * Refresh column dimensions
  533. *
  534. * @return PHPExcel_Worksheet
  535. */
  536. public function refreshColumnDimensions()
  537. {
  538. $currentColumnDimensions = $this->getColumnDimensions();
  539. $newColumnDimensions = array();
  540. foreach ($currentColumnDimensions as $objColumnDimension) {
  541. $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
  542. }
  543. $this->_columnDimensions = $newColumnDimensions;
  544. return $this;
  545. }
  546. /**
  547. * Refresh row dimensions
  548. *
  549. * @return PHPExcel_Worksheet
  550. */
  551. public function refreshRowDimensions()
  552. {
  553. $currentRowDimensions = $this->getRowDimensions();
  554. $newRowDimensions = array();
  555. foreach ($currentRowDimensions as $objRowDimension) {
  556. $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
  557. }
  558. $this->_rowDimensions = $newRowDimensions;
  559. return $this;
  560. }
  561. /**
  562. * Calculate worksheet dimension
  563. *
  564. * @return string String containing the dimension of this worksheet
  565. */
  566. public function calculateWorksheetDimension()
  567. {
  568. // Return
  569. return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow();
  570. }
  571. /**
  572. * Calculate worksheet data dimension
  573. *
  574. * @return string String containing the dimension of this worksheet that actually contain data
  575. */
  576. public function calculateWorksheetDataDimension()
  577. {
  578. // Return
  579. return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow();
  580. }
  581. /**
  582. * Calculate widths for auto-size columns
  583. *
  584. * @param boolean $calculateMergeCells Calculate merge cell width
  585. * @return PHPExcel_Worksheet;
  586. */
  587. public function calculateColumnWidths($calculateMergeCells = false)
  588. {
  589. // initialize $autoSizes array
  590. $autoSizes = array();
  591. foreach ($this->getColumnDimensions() as $colDimension) {
  592. if ($colDimension->getAutoSize()) {
  593. $autoSizes[$colDimension->getColumnIndex()] = -1;
  594. }
  595. }
  596. // There is only something to do if there are some auto-size columns
  597. if (!empty($autoSizes)) {
  598. // build list of cells references that participate in a merge
  599. $isMergeCell = array();
  600. foreach ($this->getMergeCells() as $cells) {
  601. foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
  602. $isMergeCell[$cellReference] = true;
  603. }
  604. }
  605. // loop through all cells in the worksheet
  606. foreach ($this->getCellCollection(false) as $cellID) {
  607. $cell = $this->getCell($cellID);
  608. if (isset($autoSizes[$cell->getColumn()])) {
  609. // Determine width if cell does not participate in a merge
  610. if (!isset($isMergeCell[$cell->getCoordinate()])) {
  611. // Calculated value
  612. $cellValue = $cell->getCalculatedValue();
  613. // To formatted string
  614. $cellValue = PHPExcel_Style_NumberFormat::toFormattedString($cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
  615. $autoSizes[$cell->getColumn()] = max(
  616. (float)$autoSizes[$cell->getColumn()],
  617. (float)PHPExcel_Shared_Font::calculateColumnWidth(
  618. $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
  619. $cellValue,
  620. $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
  621. $this->getDefaultStyle()->getFont()
  622. )
  623. );
  624. }
  625. }
  626. }
  627. // adjust column widths
  628. foreach ($autoSizes as $columnIndex => $width) {
  629. if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth();
  630. $this->getColumnDimension($columnIndex)->setWidth($width);
  631. }
  632. }
  633. return $this;
  634. }
  635. /**
  636. * Get parent
  637. *
  638. * @return PHPExcel
  639. */
  640. public function getParent() {
  641. return $this->_parent;
  642. }
  643. /**
  644. * Re-bind parent
  645. *
  646. * @param PHPExcel $parent
  647. * @return PHPExcel_Worksheet
  648. */
  649. public function rebindParent(PHPExcel $parent) {
  650. $namedRanges = $this->_parent->getNamedRanges();
  651. foreach ($namedRanges as $namedRange) {
  652. $parent->addNamedRange($namedRange);
  653. }
  654. $this->_parent->removeSheetByIndex(
  655. $this->_parent->getIndex($this)
  656. );
  657. $this->_parent = $parent;
  658. return $this;
  659. }
  660. /**
  661. * Get title
  662. *
  663. * @return string
  664. */
  665. public function getTitle()
  666. {
  667. return $this->_title;
  668. }
  669. /**
  670. * Set title
  671. *
  672. * @param string $pValue String containing the dimension of this worksheet
  673. * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should
  674. * be updated to reflect the new sheet name.
  675. * This should be left as the default true, unless you are
  676. * certain that no formula cells on any worksheet contain
  677. * references to this worksheet
  678. * @return PHPExcel_Worksheet
  679. */
  680. public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
  681. {
  682. // Is this a 'rename' or not?
  683. if ($this->getTitle() == $pValue) {
  684. return $this;
  685. }
  686. // Syntax check
  687. self::_checkSheetTitle($pValue);
  688. // Old title
  689. $oldTitle = $this->getTitle();
  690. if ($this->getParent()) {
  691. // Is there already such sheet name?
  692. if ($this->getParent()->sheetNameExists($pValue)) {
  693. // Use name, but append with lowest possible integer
  694. if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
  695. $pValue = PHPExcel_Shared_String::Substring($pValue,0,29);
  696. }
  697. $i = 1;
  698. while ($this->getParent()->sheetNameExists($pValue . ' ' . $i)) {
  699. ++$i;
  700. if ($i == 10) {
  701. if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
  702. $pValue = PHPExcel_Shared_String::Substring($pValue,0,28);
  703. }
  704. } elseif ($i == 100) {
  705. if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
  706. $pValue = PHPExcel_Shared_String::Substring($pValue,0,27);
  707. }
  708. }
  709. }
  710. $altTitle = $pValue . ' ' . $i;
  711. return $this->setTitle($altTitle,$updateFormulaCellReferences);
  712. }
  713. }
  714. // Set title
  715. $this->_title = $pValue;
  716. $this->_dirty = true;
  717. if ($this->getParent()) {
  718. // New title
  719. $newTitle = $this->getTitle();
  720. if ($updateFormulaCellReferences)
  721. PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->getParent(), $oldTitle, $newTitle);
  722. }
  723. return $this;
  724. }
  725. /**
  726. * Get sheet state
  727. *
  728. * @return string Sheet state (visible, hidden, veryHidden)
  729. */
  730. public function getSheetState() {
  731. return $this->_sheetState;
  732. }
  733. /**
  734. * Set sheet state
  735. *
  736. * @param string $value Sheet state (visible, hidden, veryHidden)
  737. * @return PHPExcel_Worksheet
  738. */
  739. public function setSheetState($value = PHPExcel_Worksheet::SHEETSTATE_VISIBLE) {
  740. $this->_sheetState = $value;
  741. return $this;
  742. }
  743. /**
  744. * Get page setup
  745. *
  746. * @return PHPExcel_Worksheet_PageSetup
  747. */
  748. public function getPageSetup()
  749. {
  750. return $this->_pageSetup;
  751. }
  752. /**
  753. * Set page setup
  754. *
  755. * @param PHPExcel_Worksheet_PageSetup $pValue
  756. * @return PHPExcel_Worksheet
  757. */
  758. public function setPageSetup(PHPExcel_Worksheet_PageSetup $pValue)
  759. {
  760. $this->_pageSetup = $pValue;
  761. return $this;
  762. }
  763. /**
  764. * Get page margins
  765. *
  766. * @return PHPExcel_Worksheet_PageMargins
  767. */
  768. public function getPageMargins()
  769. {
  770. return $this->_pageMargins;
  771. }
  772. /**
  773. * Set page margins
  774. *
  775. * @param PHPExcel_Worksheet_PageMargins $pValue
  776. * @return PHPExcel_Worksheet
  777. */
  778. public function setPageMargins(PHPExcel_Worksheet_PageMargins $pValue)
  779. {
  780. $this->_pageMargins = $pValue;
  781. return $this;
  782. }
  783. /**
  784. * Get page header/footer
  785. *
  786. * @return PHPExcel_Worksheet_HeaderFooter
  787. */
  788. public function getHeaderFooter()
  789. {
  790. return $this->_headerFooter;
  791. }
  792. /**
  793. * Set page header/footer
  794. *
  795. * @param PHPExcel_Worksheet_HeaderFooter $pValue
  796. * @return PHPExcel_Worksheet
  797. */
  798. public function setHeaderFooter(PHPExcel_Worksheet_HeaderFooter $pValue)
  799. {
  800. $this->_headerFooter = $pValue;
  801. return $this;
  802. }
  803. /**
  804. * Get sheet view
  805. *
  806. * @return PHPExcel_Worksheet_HeaderFooter
  807. */
  808. public function getSheetView()
  809. {
  810. return $this->_sheetView;
  811. }
  812. /**
  813. * Set sheet view
  814. *
  815. * @param PHPExcel_Worksheet_SheetView $pValue
  816. * @return PHPExcel_Worksheet
  817. */
  818. public function setSheetView(PHPExcel_Worksheet_SheetView $pValue)
  819. {
  820. $this->_sheetView = $pValue;
  821. return $this;
  822. }
  823. /**
  824. * Get Protection
  825. *
  826. * @return PHPExcel_Worksheet_Protection
  827. */
  828. public function getProtection()
  829. {
  830. return $this->_protection;
  831. }
  832. /**
  833. * Set Protection
  834. *
  835. * @param PHPExcel_Worksheet_Protection $pValue
  836. * @return PHPExcel_Worksheet
  837. */
  838. public function setProtection(PHPExcel_Worksheet_Protection $pValue)
  839. {
  840. $this->_protection = $pValue;
  841. $this->_dirty = true;
  842. return $this;
  843. }
  844. /**
  845. * Get highest worksheet column
  846. *
  847. * @return string Highest column name
  848. */
  849. public function getHighestColumn()
  850. {
  851. return $this->_cachedHighestColumn;
  852. }
  853. /**
  854. * Get highest worksheet column that contains data
  855. *
  856. * @return string Highest column name that contains data
  857. */
  858. public function getHighestDataColumn()
  859. {
  860. return $this->_cellCollection->getHighestColumn();
  861. }
  862. /**
  863. * Get highest worksheet row
  864. *
  865. * @return int Highest row number
  866. */
  867. public function getHighestRow()
  868. {
  869. return $this->_cachedHighestRow;
  870. }
  871. /**
  872. * Get highest worksheet row that contains data
  873. *
  874. * @return string Highest row number that contains data
  875. */
  876. public function getHighestDataRow()
  877. {
  878. return $this->_cellCollection->getHighestRow();
  879. }
  880. /**
  881. * Get highest worksheet column and highest row that have cell records
  882. *
  883. * @return array Highest column name and highest row number
  884. */
  885. public function getHighestRowAndColumn()
  886. {
  887. return $this->_cellCollection->getHighestRowAndColumn();
  888. }
  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 bool $returnCell Return the worksheet (false, default) or the cell (true)
  895. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
  896. */
  897. public function setCellValue($pCoordinate = 'A1', $pValue = null, $returnCell = false)
  898. {
  899. $cell = $this->getCell($pCoordinate)->setValue($pValue);
  900. return ($returnCell) ? $cell : $this;
  901. }
  902. /**
  903. * Set a cell value by using numeric cell coordinates
  904. *
  905. * @param string $pColumn Numeric column coordinate of the cell
  906. * @param string $pRow Numeric row coordinate of the cell
  907. * @param mixed $pValue Value of the cell
  908. * @param bool $returnCell Return the worksheet (false, default) or the cell (true)
  909. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
  910. */
  911. public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $returnCell = false)
  912. {
  913. $cell = $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow)->setValue($pValue);
  914. return ($returnCell) ? $cell : $this;
  915. }
  916. /**
  917. * Set a cell value
  918. *
  919. * @param string $pCoordinate Coordinate of the cell
  920. * @param mixed $pValue Value of the cell
  921. * @param string $pDataType Explicit data type
  922. * @param bool $returnCell Return the worksheet (false, default) or the cell (true)
  923. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
  924. */
  925. public function setCellValueExplicit($pCoordinate = 'A1', $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
  926. {
  927. // Set value
  928. $cell = $this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);
  929. return ($returnCell) ? $cell : $this;
  930. }
  931. /**
  932. * Set a cell value by using numeric cell coordinates
  933. *
  934. * @param string $pColumn Numeric column coordinate of the cell
  935. * @param string $pRow Numeric row coordinate of the cell
  936. * @param mixed $pValue Value of the cell
  937. * @param string $pDataType Explicit data type
  938. * @param bool $returnCell Return the worksheet (false, default) or the cell (true)
  939. * @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
  940. */
  941. public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
  942. {
  943. $cell = $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow)->setValueExplicit($pValue, $pDataType);
  944. return ($returnCell) ? $cell : $this;
  945. }
  946. /**
  947. * Get cell at a specific coordinate
  948. *
  949. * @param string $pCoordinate Coordinate of the cell
  950. * @throws Exception
  951. * @return PHPExcel_Cell Cell that was found
  952. */
  953. public function getCell($pCoordinate = 'A1')
  954. {
  955. // Check cell collection
  956. if ($this->_cellCollection->isDataSet($pCoordinate)) {
  957. return $this->_cellCollection->getCacheData($pCoordinate);
  958. }
  959. // Worksheet reference?
  960. if (strpos($pCoordinate, '!') !== false) {
  961. $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
  962. return $this->getParent()->getSheetByName($worksheetReference[0])->getCell($worksheetReference[1]);
  963. }
  964. // Named range?
  965. if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
  966. (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
  967. $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
  968. if ($namedRange !== NULL) {
  969. $pCoordinate = $namedRange->getRange();
  970. return $namedRange->getWorksheet()->getCell($pCoordinate);
  971. }
  972. }
  973. // Uppercase coordinate
  974. $pCoordinate = strtoupper($pCoordinate);
  975. if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
  976. throw new Exception('Cell coordinate can not be a range of cells.');
  977. } elseif (strpos($pCoordinate,'$') !== false) {
  978. throw new Exception('Cell coordinate must not be absolute.');
  979. } else {
  980. // Create new cell object
  981. // Coordinates
  982. $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
  983. $cell = $this->_cellCollection->addCacheData($pCoordinate,new PHPExcel_Cell($aCoordinates[0], $aCoordinates[1], null, PHPExcel_Cell_DataType::TYPE_NULL, $this));
  984. $this->_cellCollectionIsSorted = false;
  985. if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($aCoordinates[0]))
  986. $this->_cachedHighestColumn = $aCoordinates[0];
  987. $this->_cachedHighestRow = max($this->_cachedHighestRow,$aCoordinates[1]);
  988. // Cell needs appropriate xfIndex
  989. $rowDimensions = $this->getRowDimensions();
  990. $columnDimensions = $this->getColumnDimensions();
  991. if ( isset($rowDimensions[$aCoordinates[1]]) && $rowDimensions[$aCoordinates[1]]->getXfIndex() !== null ) {
  992. // then there is a row dimension with explicit style, assign it to the cell
  993. $cell->setXfIndex($rowDimensions[$aCoordinates[1]]->getXfIndex());
  994. } else if ( isset($columnDimensions[$aCoordinates[0]]) ) {
  995. // then there is a column dimension, assign it to the cell
  996. $cell->setXfIndex($columnDimensions[$aCoordinates[0]]->getXfIndex());
  997. } else {
  998. // set to default index
  999. $cell->setXfIndex(0);
  1000. }
  1001. return $cell;
  1002. }
  1003. }
  1004. /**
  1005. * Get cell at a specific coordinate by using numeric cell coordinates
  1006. *
  1007. * @param string $pColumn Numeric column coordinate of the cell
  1008. * @param string $pRow Numeric row coordinate of the cell
  1009. * @return PHPExcel_Cell Cell that was found
  1010. */
  1011. public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
  1012. {
  1013. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
  1014. $coordinate = $columnLetter . $pRow;
  1015. if (!$this->_cellCollection->isDataSet($coordinate)) {
  1016. $cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType::TYPE_NULL, $this));
  1017. $this->_cellCollectionIsSorted = false;
  1018. if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
  1019. $this->_cachedHighestColumn = $columnLetter;
  1020. $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
  1021. return $cell;
  1022. }
  1023. return $this->_cellCollection->getCacheData($coordinate);
  1024. }
  1025. /**
  1026. * Cell at a specific coordinate exists?
  1027. *
  1028. * @param string $pCoordinate Coordinate of the cell
  1029. * @throws Exception
  1030. * @return boolean
  1031. */
  1032. public function cellExists($pCoordinate = 'A1')
  1033. {
  1034. // Worksheet reference?
  1035. if (strpos($pCoordinate, '!') !== false) {
  1036. $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
  1037. return $this->getParent()->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
  1038. }
  1039. // Named range?
  1040. if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
  1041. (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
  1042. $namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
  1043. if ($namedRange !== NULL) {
  1044. $pCoordinate = $namedRange->getRange();
  1045. if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
  1046. if (!$namedRange->getLocalOnly()) {
  1047. return $namedRange->getWorksheet()->cellExists($pCoordinate);
  1048. } else {
  1049. throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
  1050. }
  1051. }
  1052. }
  1053. }
  1054. // Uppercase coordinate
  1055. $pCoordinate = strtoupper($pCoordinate);
  1056. if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
  1057. throw new Exception('Cell coordinate can not be a range of cells.');
  1058. } elseif (strpos($pCoordinate,'$') !== false) {
  1059. throw new Exception('Cell coordinate must not be absolute.');
  1060. } else {
  1061. // Coordinates
  1062. $aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
  1063. // Cell exists?
  1064. return $this->_cellCollection->isDataSet($pCoordinate);
  1065. }
  1066. }
  1067. /**
  1068. * Cell at a specific coordinate by using numeric cell coordinates exists?
  1069. *
  1070. * @param string $pColumn Numeric column coordinate of the cell
  1071. * @param string $pRow Numeric row coordinate of the cell
  1072. * @return boolean
  1073. */
  1074. public function cellExistsByColumnAndRow($pColumn = 0, $pRow = 1)
  1075. {
  1076. return $this->cellExists(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
  1077. }
  1078. /**
  1079. * Get row dimension at a specific row
  1080. *
  1081. * @param int $pRow Numeric index of the row
  1082. * @return PHPExcel_Worksheet_RowDimension
  1083. */
  1084. public function getRowDimension($pRow = 1)
  1085. {
  1086. // Found
  1087. $found = null;
  1088. // Get row dimension
  1089. if (!isset($this->_rowDimensions[$pRow])) {
  1090. $this->_rowDimensions[$pRow] = new PHPExcel_Worksheet_RowDimension($pRow);
  1091. $this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
  1092. }
  1093. return $this->_rowDimensions[$pRow];
  1094. }
  1095. /**
  1096. * Get column dimension at a specific column
  1097. *
  1098. * @param string $pColumn String index of the column
  1099. * @return PHPExcel_Worksheet_ColumnDimension
  1100. */
  1101. public function getColumnDimension($pColumn = 'A')
  1102. {
  1103. // Uppercase coordinate
  1104. $pColumn = strtoupper($pColumn);
  1105. // Fetch dimensions
  1106. if (!isset($this->_columnDimensions[$pColumn])) {
  1107. $this->_columnDimensions[$pColumn] = new PHPExcel_Worksheet_ColumnDimension($pColumn);
  1108. if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($pColumn))
  1109. $this->_cachedHighestColumn = $pColumn;
  1110. }
  1111. return $this->_columnDimensions[$pColumn];
  1112. }
  1113. /**
  1114. * Get column dimension at a specific column by using numeric cell coordinates
  1115. *
  1116. * @param string $pColumn Numeric column coordinate of the cell
  1117. * @return PHPExcel_Worksheet_ColumnDimension
  1118. */
  1119. public function getColumnDimensionByColumn($pColumn = 0)
  1120. {
  1121. return $this->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($pColumn));
  1122. }
  1123. /**
  1124. * Get styles
  1125. *
  1126. * @return PHPExcel_Style[]
  1127. */
  1128. public function getStyles()
  1129. {
  1130. return $this->_styles;
  1131. }
  1132. /**
  1133. * Get default style of workbork.
  1134. *
  1135. * @deprecated
  1136. * @return PHPExcel_Style
  1137. * @throws Exception
  1138. */
  1139. public function getDefaultStyle()
  1140. {
  1141. return $this->_parent->getDefaultStyle();
  1142. }
  1143. /**
  1144. * Set default style - should only be used by PHPExcel_IReader implementations!
  1145. *
  1146. * @deprecated
  1147. * @param PHPExcel_Style $pValue
  1148. * @throws Exception
  1149. * @return PHPExcel_Worksheet
  1150. */
  1151. public function setDefaultStyle(PHPExcel_Style $pValue)
  1152. {
  1153. $this->_parent->getDefaultStyle()->applyFromArray(array(
  1154. 'font' => array(
  1155. 'name' => $pValue->getFont()->getName(),
  1156. 'size' => $pValue->getFont()->getSize(),
  1157. ),
  1158. ));
  1159. return $this;
  1160. }
  1161. /**
  1162. * Get style for cell
  1163. *
  1164. * @param string $pCellCoordinate Cell coordinate to get style for
  1165. * @return PHPExcel_Style
  1166. * @throws Exception
  1167. */
  1168. public function getStyle($pCellCoordinate = 'A1')
  1169. {
  1170. // set this sheet as active
  1171. $this->_parent->setActiveSheetIndex($this->_parent->getIndex($this));
  1172. // set cell coordinate as active
  1173. $this->setSelectedCells($pCellCoordinate);
  1174. return $this->_parent->getCellXfSupervisor();
  1175. }
  1176. /**
  1177. * Get conditional styles for a cell
  1178. *
  1179. * @param string $pCoordinate
  1180. * @return PHPExcel_Style_Conditional[]
  1181. */
  1182. public function getConditionalStyles($pCoordinate = 'A1')
  1183. {
  1184. if (!isset($this->_conditionalStylesCollection[$pCoordinate])) {
  1185. $this->_conditionalStylesCollection[$pCoordinate] = array();
  1186. }
  1187. return $this->_conditionalStylesCollection[$pCoordinate];
  1188. }
  1189. /**
  1190. * Do conditional styles exist for this cell?
  1191. *
  1192. * @param string $pCoordinate
  1193. * @return boolean
  1194. */
  1195. public function conditionalStylesExists($pCoordinate = 'A1')
  1196. {
  1197. if (isset($this->_conditionalStylesCollection[$pCoordinate])) {
  1198. return true;
  1199. }
  1200. return false;
  1201. }
  1202. /**
  1203. * Removes conditional styles for a cell
  1204. *
  1205. * @param string $pCoordinate
  1206. * @return PHPExcel_Worksheet
  1207. */
  1208. public function removeConditionalStyles($pCoordinate = 'A1')
  1209. {
  1210. unset($this->_conditionalStylesCollection[$pCoordinate]);
  1211. return $this;
  1212. }
  1213. /**
  1214. * Get collection of conditional styles
  1215. *
  1216. * @return array
  1217. */
  1218. public function getConditionalStylesCollection()
  1219. {
  1220. return $this->_conditionalStylesCollection;
  1221. }
  1222. /**
  1223. * Set conditional styles
  1224. *
  1225. * @param $pCoordinate string E.g. 'A1'
  1226. * @param $pValue PHPExcel_Style_Conditional[]
  1227. * @return PHPExcel_Worksheet
  1228. */
  1229. public function setConditionalStyles($pCoordinate = 'A1', $pValue)
  1230. {
  1231. $this->_conditionalStylesCollection[$pCoordinate] = $pValue;
  1232. return $this;
  1233. }
  1234. /**
  1235. * Get style for cell by using numeric cell coordinates
  1236. *
  1237. * @param int $pColumn Numeric column coordinate of the cell
  1238. * @param int $pRow Numeric row coordinate of the cell
  1239. * @return PHPExcel_Style
  1240. */
  1241. public function getStyleByColumnAndRow($pColumn = 0, $pRow = 1)
  1242. {
  1243. return $this->getStyle(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
  1244. }
  1245. /**
  1246. * Set shared cell style to a range of cells
  1247. *
  1248. * Please note that this will overwrite existing cell styles for cells in range!
  1249. *
  1250. * @deprecated
  1251. * @param PHPExcel_Style $pSharedCellStyle Cell style to share
  1252. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
  1253. * @throws Exception
  1254. * @return PHPExcel_Worksheet
  1255. */
  1256. public function setSharedStyle(PHPExcel_Style $pSharedCellStyle = null, $pRange = '')
  1257. {
  1258. $this->duplicateStyle($pSharedCellStyle, $pRange);
  1259. return $this;
  1260. }
  1261. /**
  1262. * Duplicate cell style to a range of cells
  1263. *
  1264. * Please note that this will overwrite existing cell styles for cells in range!
  1265. *
  1266. * @param PHPExcel_Style $pCellStyle Cell style to duplicate
  1267. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
  1268. * @throws Exception
  1269. * @return PHPExcel_Worksheet
  1270. */
  1271. public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
  1272. {
  1273. // make sure we have a real style and not supervisor
  1274. $style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
  1275. // Add the style to the workbook if necessary
  1276. $workbook = $this->_parent;
  1277. if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
  1278. // there is already such cell Xf in our collection
  1279. $xfIndex = $existingStyle->getIndex();
  1280. } else {
  1281. // we don't have such a cell Xf, need to add
  1282. $workbook->addCellXf($pCellStyle);
  1283. $xfIndex = $pCellStyle->getIndex();
  1284. }
  1285. // Uppercase coordinate
  1286. $pRange = strtoupper($pRange);
  1287. // Is it a cell range or a single cell?
  1288. $rangeA = '';
  1289. $rangeB = '';
  1290. if (strpos($pRange, ':') === false) {
  1291. $rangeA = $pRange;
  1292. $rangeB = $pRange;
  1293. } else {
  1294. list($rangeA, $rangeB) = explode(':', $pRange);
  1295. }
  1296. // Calculate range outer borders
  1297. $rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
  1298. $rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
  1299. // Translate column into index
  1300. $rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]) - 1;
  1301. $rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]) - 1;
  1302. // Make sure we can loop upwards on rows and columns
  1303. if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
  1304. $tmp = $rangeStart;
  1305. $rangeStart = $rangeEnd;
  1306. $rangeEnd = $tmp;
  1307. }
  1308. // Loop through cells and apply styles
  1309. for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
  1310. for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
  1311. $this->getCell(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
  1312. }
  1313. }
  1314. return $this;
  1315. }
  1316. /**
  1317. * Duplicate conditional style to a range of cells
  1318. *
  1319. * Please note that this will overwrite existing cell styles for cells in range!
  1320. *
  1321. * @param array of PHPExcel_Style_Conditional $pCellStyle Cell style to duplicate
  1322. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
  1323. * @throws Exception
  1324. * @return PHPExcel_Worksheet
  1325. */
  1326. public function duplicateConditionalStyle(array $pCellStyle = null, $pRange = '')
  1327. {
  1328. foreach($pCellStyle as $cellStyle) {
  1329. if (!is_a($cellStyle,'PHPExcel_Style_Conditional')) {
  1330. throw new Exception('Style is not a conditional style');
  1331. }
  1332. }
  1333. // Uppercase coordinate
  1334. $pRange = strtoupper($pRange);
  1335. // Is it a cell range or a single cell?
  1336. $rangeA = '';
  1337. $rangeB = '';
  1338. if (strpos($pRange, ':') === false) {
  1339. $rangeA = $pRange;
  1340. $rangeB = $pRange;
  1341. } else {
  1342. list($rangeA, $rangeB) = explode(':', $pRange);
  1343. }
  1344. // Calculate range outer borders
  1345. $rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
  1346. $rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
  1347. // Translate column into index
  1348. $rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]) - 1;
  1349. $rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]) - 1;
  1350. // Make sure we can loop upwards on rows and columns
  1351. if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
  1352. $tmp = $rangeStart;
  1353. $rangeStart = $rangeEnd;
  1354. $rangeEnd = $tmp;
  1355. }
  1356. // Loop through cells and apply styles
  1357. for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
  1358. for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
  1359. $this->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($col) . $row, $pCellStyle);
  1360. }
  1361. }
  1362. return $this;
  1363. }
  1364. /**
  1365. * Duplicate cell style array to a range of cells
  1366. *
  1367. * Please note that this will overwrite existing cell styles for cells in range,
  1368. * if they are in the styles array. For example, if you decide to set a range of
  1369. * cells to font bold, only include font bold in the styles array.
  1370. *
  1371. * @deprecated
  1372. * @param array $pStyles Array containing style information
  1373. * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
  1374. * @param boolean $pAdvanced Advanced mode for setting borders.
  1375. * @throws Exception
  1376. * @return PHPExcel_Worksheet
  1377. */
  1378. public function duplicateStyleArray($pStyles = null, $pRange = '', $pAdvanced = true)
  1379. {
  1380. $this->getStyle($pRange)->applyFromArray($pStyles, $pAdvanced);
  1381. return $this;
  1382. }
  1383. /**
  1384. * Set break on a cell
  1385. *
  1386. * @param string $pCell Cell coordinate (e.g. A1)
  1387. * @param int $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
  1388. * @throws Exception
  1389. * @return PHPExcel_Worksheet
  1390. */
  1391. public function setBreak($pCell = 'A1', $pBreak = PHPExcel_Worksheet::BREAK_NONE)
  1392. {
  1393. // Uppercase coordinate
  1394. $pCell = strtoupper($pCell);
  1395. if ($pCell != '') {
  1396. $this->_breaks[$pCell] = $pBreak;
  1397. } else {
  1398. throw new Exception('No cell coordinate specified.');
  1399. }
  1400. return $this;
  1401. }
  1402. /**
  1403. * Set break on a cell by using numeric cell coordinates
  1404. *
  1405. * @param integer $pColumn Numeric column coordinate of the cell
  1406. * @param integer $pRow Numeric row coordinate of the cell
  1407. * @param integer $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
  1408. * @throws Exception
  1409. * @return PHPExcel_Worksheet
  1410. */
  1411. public function setBreakByColumnAndRow($pColumn = 0, $pRow = 1, $pBreak = PHPExcel_Worksheet::BREAK_NONE)
  1412. {
  1413. return $this->setBreak(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pBreak);
  1414. }
  1415. /**
  1416. * Get breaks
  1417. *
  1418. * @return array[]
  1419. */
  1420. public function getBreaks()
  1421. {
  1422. return $this->_breaks;
  1423. }
  1424. /**
  1425. * Set merge on a cell range
  1426. *
  1427. * @param string $pRange Cell range (e.g. A1:E1)
  1428. * @throws Exception
  1429. * @return PHPExcel_Worksheet
  1430. */
  1431. public function mergeCells($pRange = 'A1:A1')
  1432. {
  1433. // Uppercase coordinate
  1434. $pRange = strtoupper($pRange);
  1435. if (strpos($pRange,':') !== false) {
  1436. $this->_mergeCells[$pRange] = $pRange;
  1437. // make sure cells are created
  1438. // get the cells in the range
  1439. $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
  1440. // create upper left cell if it does not already exist
  1441. $upperLeft = $aReferences[0];
  1442. if (!$this->cellExists($upperLeft)) {
  1443. $this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
  1444. }
  1445. // create or blank out the rest of the cells in the range
  1446. $count = count($aReferences);
  1447. for ($i = 1; $i < $count; $i++) {
  1448. $this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
  1449. }
  1450. } else {
  1451. throw new Exception('Merge must be set on a range of cells.');
  1452. }
  1453. return $this;
  1454. }
  1455. /**
  1456. * Set merge on a cell range by using numeric cell coordinates
  1457. *
  1458. * @param int $pColumn1 Numeric column coordinate of the first cell
  1459. * @param int $pRow1 Numeric row coordinate of the first cell
  1460. * @param int $pColumn2 Numeric column coordinate of the last cell
  1461. * @param int $pRow2 Numeric row coordinate of the last cell
  1462. * @throws Exception
  1463. * @return PHPExcel_Worksheet
  1464. */
  1465. public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
  1466. {
  1467. $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
  1468. return $this->mergeCells($cellRange);
  1469. }
  1470. /**
  1471. * Remove merge on a cell range
  1472. *
  1473. * @param string $pRange Cell range (e.g. A1:E1)
  1474. * @throws Exception
  1475. * @return PHPExcel_Worksheet
  1476. */
  1477. public function unmergeCells($pRange = 'A1:A1')
  1478. {
  1479. // Uppercase coordinate
  1480. $pRange = strtoupper($pRange);
  1481. if (strpos($pRange,':') !== false) {
  1482. if (isset($this->_mergeCells[$pRange])) {
  1483. unset($this->_mergeCells[$pRange]);
  1484. } else {
  1485. throw new Exception('Cell range ' . $pRange . ' not known as merged.');
  1486. }
  1487. } else {
  1488. throw new Exception('Merge can only be removed from a range of cells.');
  1489. }
  1490. return $this;
  1491. }
  1492. /**
  1493. * Remove merge on a cell range by using numeric cell coordinates
  1494. *
  1495. * @param int $pColumn1 Numeric column coordinate of the first cell
  1496. * @param int $pRow1 Numeric row coordinate of the first cell
  1497. * @param int $pColumn2 Numeric column coordinate of the last cell
  1498. * @param int $pRow2 Numeric row coordinate of the last cell
  1499. * @throws Exception
  1500. * @return PHPExcel_Worksheet
  1501. */
  1502. public function unmergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
  1503. {
  1504. $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
  1505. return $this->unmergeCells($cellRange);
  1506. }
  1507. /**
  1508. * Get merge cells array.
  1509. *
  1510. * @return array[]
  1511. */
  1512. public function getMergeCells()
  1513. {
  1514. return $this->_mergeCells;
  1515. }
  1516. /**
  1517. * Set merge cells array for the entire sheet. Use instead mergeCells() to merge
  1518. * a single cell range.
  1519. *
  1520. * @param array
  1521. */
  1522. public function setMergeCells($pValue = array())
  1523. {
  1524. $this->_mergeCells = $pValue;
  1525. return $this;
  1526. }
  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);
  1540. if (!$pAlreadyHashed) {
  1541. $pPassword = PHPExcel_Shared_PasswordHasher::hashPassword($pPassword);
  1542. }
  1543. $this->_protectedCells[$pRange] = $pPassword;
  1544. return $this;
  1545. }
  1546. /**
  1547. * Set protection on a cell range by using numeric cell coordinates
  1548. *
  1549. * @param int $pColumn1 Numeric column coordinate of the first cell
  1550. * @param int $pRow1 Numeric row coordinate of the first cell
  1551. * @param int $pColumn2 Numeric column coordinate of the last cell
  1552. * @param int $pRow2 Numeric row coordinate of the last cell
  1553. * @param string $pPassword Password to unlock the protection
  1554. * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
  1555. * @throws Exception
  1556. * @return PHPExcel_Worksheet
  1557. */
  1558. public function protectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
  1559. {
  1560. $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
  1561. return $this->protectCells($cellRange, $pPassword, $pAlreadyHashed);
  1562. }
  1563. /**
  1564. * Remove protection on a cell range
  1565. *
  1566. * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
  1567. * @throws Exception
  1568. * @return PHPExcel_Worksheet
  1569. */
  1570. public function unprotectCells($pRange = 'A1')
  1571. {
  1572. // Uppercase coordinate
  1573. $pRange = strtoupper($pRange);
  1574. if (isset($this->_protectedCells[$pRange])) {
  1575. unset($this->_protectedCells[$pRange]);
  1576. } else {
  1577. throw new Exception('Cell range ' . $pRange . ' not known as protected.');
  1578. }
  1579. return $this;
  1580. }
  1581. /**
  1582. * Remove protection on a cell range by using numeric cell coordinates
  1583. *
  1584. * @param int $pColumn1 Numeric column coordinate of the first cell
  1585. * @param int $pRow1 Numeric row coordinate of the first cell
  1586. * @param int $pColumn2 Numeric column coordinate of the last cell
  1587. * @param int $pRow2 Numeric row coordinate of the last cell
  1588. * @param string $pPassword Password to unlock the protection
  1589. * @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
  1590. * @throws Exception
  1591. * @return PHPExcel_Worksheet
  1592. */
  1593. public function unprotectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
  1594. {
  1595. $cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
  1596. return $this->unprotectCells($cellRange, $pPassword, $pAlreadyHashed);
  1597. }
  1598. /**
  1599. * Get protected cells
  1600. *
  1601. * @return array[]
  1602. */
  1603. public function getProtectedCells()
  1604. {
  1605. return $this->_protectedCells;
  1606. }
  1607. /**
  1608. * Get Autofilter
  1609. *
  1610. * @return PHPExcel_Worksheet_AutoFilter
  1611. */
  1612. public function getAutoFilter()
  1613. {
  1614. return $this->_autoFilter;
  1615. }
  1616. /**
  1617. * Set AutoFilter
  1618. *
  1619. * @param PHPExcel_Worksheet_AutoFilter|string $pValue
  1620. * A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility
  1621. * @throws Exception
  1622. * @return PHPExcel_Worksheet
  1623. */
  1624. public function setAutoFilter($pValue)
  1625. {
  1626. if (is_string($pValue)) {
  1627. $this->_autoFilter->setRange($pValue);
  1628. } elseif(is_object($pValue) && ($pValue instanceof PHPExcel_Worksheet_AutoFilter)) {
  1629. $this->_autoFilter = $pValue;
  1630. }
  1631. return $this;
  1632. }
  1633. /**
  1634. * Set Autofilter Range by using numeric cell coordinates
  1635. *
  1636. * @param int $pColumn1 Numeric column coordinate of the first cell
  1637. * @param int $pRow1 Numeric row coordinate of the first cell
  1638. * @param int $pColumn2 Numeric column coordinate of the second cell
  1639. * @param int $pRow2 Numeric row coordinate of the second cell
  1640. * @throws Exception
  1641. * @return PHPExcel_Worksheet
  1642. */
  1643. public function setAutoFilterByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
  1644. {
  1645. return $this->setAutoFilter(
  1646. PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1
  1647. . ':' .
  1648. PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2
  1649. );
  1650. }
  1651. /**
  1652. * Remove autofilter
  1653. *
  1654. * @return PHPExcel_Worksheet
  1655. */
  1656. public function removeAutoFilter()
  1657. {
  1658. $this->_autoFilter->setRange(NULL);
  1659. return $this;
  1660. }
  1661. /**
  1662. * Get Freeze Pane
  1663. *
  1664. * @return string
  1665. */
  1666. public function getFreezePane()
  1667. {
  1668. return $this->_freezePane;
  1669. }
  1670. /**
  1671. * Freeze Pane
  1672. *
  1673. * @param string $pCell Cell (i.e. A2)
  1674. * Examples:
  1675. * A2 will freeze the rows above cell A2 (i.e row 1)
  1676. * B1 will freeze the columns to the left of cell B1 (i.e column A)
  1677. * B2 will freeze the rows above and to the left of cell A2
  1678. * (i.e row 1 and column A)
  1679. * @throws Exception
  1680. * @return PHPExcel_Worksheet
  1681. */
  1682. public function freezePane($pCell = '')
  1683. {
  1684. // Uppercase coordinate
  1685. $pCell = strtoupper($pCell);
  1686. if (strpos($pCell,':') === false && strpos($pCell,',') === false) {
  1687. $this->_freezePane = $pCell;
  1688. } else {
  1689. throw new Exception('Freeze pane can not be set on a range of cells.');
  1690. }
  1691. return $this;
  1692. }
  1693. /**
  1694. * Freeze Pane by using numeric cell coordinates
  1695. *
  1696. * @param int $pColumn Numeric column coordinate of the cell
  1697. * @param int $pRow Numeric row coordinate of the cell
  1698. * @throws Exception

Large files files are truncated, but you can click here to view the full file