PageRenderTime 73ms CodeModel.GetById 38ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/PHPExcel/Worksheet.php

https://github.com/yuweijun/blog
PHP | 2961 lines | 1729 code | 226 blank | 1006 comment | 165 complexity | b70946cdabaeb08f0614cbbeeff57953 MD5 | raw file

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

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

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