PageRenderTime 226ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 0ms

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

https://bitbucket.org/izubizarreta/https-bitbucket.org-bityvip-alpes
PHP | 972 lines | 447 code | 114 blank | 411 comment | 68 complexity | 16fd9ae93014177891ffe3965b00ca30 MD5 | raw file
Possible License(s): GPL-2.0, BSD-3-Clause, LGPL-2.1, MIT, LGPL-3.0, LGPL-2.0, JSON
  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_Cell
  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_Cell
  29. *
  30. * @category PHPExcel
  31. * @package PHPExcel_Cell
  32. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  33. */
  34. class PHPExcel_Cell
  35. {
  36. /**
  37. * Default range variable constant
  38. *
  39. * @var string
  40. */
  41. const DEFAULT_RANGE = 'A1:A1';
  42. /**
  43. * Value binder to use
  44. *
  45. * @var PHPExcel_Cell_IValueBinder
  46. */
  47. private static $_valueBinder = NULL;
  48. /**
  49. * Column of the cell
  50. *
  51. * @var string
  52. */
  53. private $_column;
  54. /**
  55. * Row of the cell
  56. *
  57. * @var int
  58. */
  59. private $_row;
  60. /**
  61. * Value of the cell
  62. *
  63. * @var mixed
  64. */
  65. private $_value;
  66. /**
  67. * Calculated value of the cell (used for caching)
  68. * This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
  69. * create the original spreadsheet file.
  70. * Note that this value is not guaranteed to reflect the actual calculated value because it is
  71. * possible that auto-calculation was disabled in the original spreadsheet, and underlying data
  72. * values used by the formula have changed since it was last calculated.
  73. *
  74. * @var mixed
  75. */
  76. private $_calculatedValue = NULL;
  77. /**
  78. * Type of the cell data
  79. *
  80. * @var string
  81. */
  82. private $_dataType;
  83. /**
  84. * Parent worksheet
  85. *
  86. * @var PHPExcel_Worksheet
  87. */
  88. private $_parent;
  89. /**
  90. * Index to cellXf
  91. *
  92. * @var int
  93. */
  94. private $_xfIndex;
  95. /**
  96. * Attributes of the formula
  97. *
  98. */
  99. private $_formulaAttributes;
  100. /**
  101. * Send notification to the cache controller
  102. *
  103. * @return void
  104. **/
  105. public function notifyCacheController() {
  106. $this->_parent->getCellCacheController()->updateCacheData($this);
  107. return $this;
  108. }
  109. public function detach() {
  110. $this->_parent = NULL;
  111. }
  112. public function attach($parent) {
  113. $this->_parent = $parent;
  114. }
  115. /**
  116. * Create a new Cell
  117. *
  118. * @param string $pColumn
  119. * @param int $pRow
  120. * @param mixed $pValue
  121. * @param string $pDataType
  122. * @param PHPExcel_Worksheet $pSheet
  123. * @throws PHPExcel_Exception
  124. */
  125. public function __construct($pColumn = 'A', $pRow = 1, $pValue = NULL, $pDataType = NULL, PHPExcel_Worksheet $pSheet = NULL)
  126. {
  127. // Initialise cell coordinate
  128. $this->_column = strtoupper($pColumn);
  129. $this->_row = $pRow;
  130. // Initialise cell value
  131. $this->_value = $pValue;
  132. // Set worksheet
  133. $this->_parent = $pSheet;
  134. // Set datatype?
  135. if ($pDataType !== NULL) {
  136. if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
  137. $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
  138. $this->_dataType = $pDataType;
  139. } else {
  140. if (!self::getValueBinder()->bindValue($this, $pValue)) {
  141. throw new PHPExcel_Exception("Value could not be bound to cell.");
  142. }
  143. }
  144. // set default index to cellXf
  145. $this->_xfIndex = 0;
  146. }
  147. /**
  148. * Get cell coordinate column
  149. *
  150. * @return string
  151. */
  152. public function getColumn()
  153. {
  154. return $this->_column;
  155. }
  156. /**
  157. * Get cell coordinate row
  158. *
  159. * @return int
  160. */
  161. public function getRow()
  162. {
  163. return $this->_row;
  164. }
  165. /**
  166. * Get cell coordinate
  167. *
  168. * @return string
  169. */
  170. public function getCoordinate()
  171. {
  172. return $this->_column . $this->_row;
  173. }
  174. /**
  175. * Get cell value
  176. *
  177. * @return mixed
  178. */
  179. public function getValue()
  180. {
  181. return $this->_value;
  182. }
  183. /**
  184. * Get cell value with formatting
  185. *
  186. * @return string
  187. */
  188. public function getFormattedValue()
  189. {
  190. return (string) PHPExcel_Style_NumberFormat::toFormattedString(
  191. $this->getCalculatedValue(),
  192. $this->_parent->getParent()->getCellXfByIndex($this->getXfIndex())
  193. ->getNumberFormat()->getFormatCode()
  194. );
  195. }
  196. /**
  197. * Set cell value
  198. *
  199. * Sets the value for a cell, automatically determining the datatype using the value binder
  200. *
  201. * @param mixed $pValue Value
  202. * @return PHPExcel_Cell
  203. * @throws PHPExcel_Exception
  204. */
  205. public function setValue($pValue = NULL)
  206. {
  207. if (!self::getValueBinder()->bindValue($this, $pValue)) {
  208. throw new PHPExcel_Exception("Value could not be bound to cell.");
  209. }
  210. return $this;
  211. }
  212. /**
  213. * Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder)
  214. *
  215. * @param mixed $pValue Value
  216. * @param string $pDataType Explicit data type
  217. * @return PHPExcel_Cell
  218. * @throws PHPExcel_Exception
  219. */
  220. public function setValueExplicit($pValue = NULL, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
  221. {
  222. // set the value according to data type
  223. switch ($pDataType) {
  224. case PHPExcel_Cell_DataType::TYPE_STRING2:
  225. $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
  226. case PHPExcel_Cell_DataType::TYPE_STRING:
  227. case PHPExcel_Cell_DataType::TYPE_NULL:
  228. case PHPExcel_Cell_DataType::TYPE_INLINE:
  229. $this->_value = PHPExcel_Cell_DataType::checkString($pValue);
  230. break;
  231. case PHPExcel_Cell_DataType::TYPE_NUMERIC:
  232. $this->_value = (float)$pValue;
  233. break;
  234. case PHPExcel_Cell_DataType::TYPE_FORMULA:
  235. $this->_value = (string)$pValue;
  236. break;
  237. case PHPExcel_Cell_DataType::TYPE_BOOL:
  238. $this->_value = (bool)$pValue;
  239. break;
  240. case PHPExcel_Cell_DataType::TYPE_ERROR:
  241. $this->_value = PHPExcel_Cell_DataType::checkErrorCode($pValue);
  242. break;
  243. default:
  244. throw new PHPExcel_Exception('Invalid datatype: ' . $pDataType);
  245. break;
  246. }
  247. // set the datatype
  248. $this->_dataType = $pDataType;
  249. return $this->notifyCacheController();
  250. }
  251. /**
  252. * Get calculated cell value
  253. *
  254. * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling
  255. *
  256. * @return mixed
  257. * @throws PHPExcel_Exception
  258. */
  259. public function getCalculatedValue($resetLog = TRUE)
  260. {
  261. // echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().'<br />';
  262. if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) {
  263. try {
  264. // echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value<br />';
  265. $result = PHPExcel_Calculation::getInstance()->calculateCellValue($this,$resetLog);
  266. // echo $this->getCoordinate().' calculation result is '.$result.'<br />';
  267. } catch ( Exception $ex ) {
  268. if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->_calculatedValue !== NULL)) {
  269. // echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />';
  270. return $this->_calculatedValue; // Fallback for calculations referencing external files.
  271. }
  272. // echo 'Calculation Exception: '.$ex->getMessage().'<br />';
  273. $result = '#N/A';
  274. throw(
  275. new PHPExcel_Exception(
  276. $this->getParent()->getTitle().'!'.$this->getCoordinate().' -> '.$ex->getMessage()
  277. )
  278. );
  279. }
  280. if ($result === '#Not Yet Implemented') {
  281. // echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />';
  282. return $this->_calculatedValue; // Fallback if calculation engine does not support the formula.
  283. }
  284. // echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().'<br />';
  285. return $result;
  286. }
  287. // if ($this->_value === NULL) {
  288. // echo 'Cell '.$this->getCoordinate().' has no value, formula or otherwise<br />';
  289. // return NULL;
  290. // }
  291. // echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />';
  292. return $this->_value;
  293. }
  294. /**
  295. * Set old calculated value (cached)
  296. *
  297. * @param mixed $pValue Value
  298. * @return PHPExcel_Cell
  299. */
  300. public function setCalculatedValue($pValue = NULL)
  301. {
  302. if ($pValue !== NULL) {
  303. $this->_calculatedValue = (is_numeric($pValue)) ? (float) $pValue : $pValue;
  304. }
  305. return $this->notifyCacheController();
  306. }
  307. /**
  308. * Get old calculated value (cached)
  309. * This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
  310. * create the original spreadsheet file.
  311. * Note that this value is not guaranteed to refelect the actual calculated value because it is
  312. * possible that auto-calculation was disabled in the original spreadsheet, and underlying data
  313. * values used by the formula have changed since it was last calculated.
  314. *
  315. * @return mixed
  316. */
  317. public function getOldCalculatedValue()
  318. {
  319. return $this->_calculatedValue;
  320. }
  321. /**
  322. * Get cell data type
  323. *
  324. * @return string
  325. */
  326. public function getDataType()
  327. {
  328. return $this->_dataType;
  329. }
  330. /**
  331. * Set cell data type
  332. *
  333. * @param string $pDataType
  334. * @return PHPExcel_Cell
  335. */
  336. public function setDataType($pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
  337. {
  338. if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
  339. $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
  340. $this->_dataType = $pDataType;
  341. return $this->notifyCacheController();
  342. }
  343. /**
  344. * Does this cell contain Data validation rules?
  345. *
  346. * @return boolean
  347. * @throws PHPExcel_Exception
  348. */
  349. public function hasDataValidation()
  350. {
  351. if (!isset($this->_parent)) {
  352. throw new PHPExcel_Exception('Cannot check for data validation when cell is not bound to a worksheet');
  353. }
  354. return $this->_parent->dataValidationExists($this->getCoordinate());
  355. }
  356. /**
  357. * Get Data validation rules
  358. *
  359. * @return PHPExcel_Cell_DataValidation
  360. * @throws PHPExcel_Exception
  361. */
  362. public function getDataValidation()
  363. {
  364. if (!isset($this->_parent)) {
  365. throw new PHPExcel_Exception('Cannot get data validation for cell that is not bound to a worksheet');
  366. }
  367. return $this->_parent->getDataValidation($this->getCoordinate());
  368. }
  369. /**
  370. * Set Data validation rules
  371. *
  372. * @param PHPExcel_Cell_DataValidation $pDataValidation
  373. * @return PHPExcel_Cell
  374. * @throws PHPExcel_Exception
  375. */
  376. public function setDataValidation(PHPExcel_Cell_DataValidation $pDataValidation = NULL)
  377. {
  378. if (!isset($this->_parent)) {
  379. throw new PHPExcel_Exception('Cannot set data validation for cell that is not bound to a worksheet');
  380. }
  381. $this->_parent->setDataValidation($this->getCoordinate(), $pDataValidation);
  382. return $this->notifyCacheController();
  383. }
  384. /**
  385. * Does this cell contain a Hyperlink?
  386. *
  387. * @return boolean
  388. * @throws PHPExcel_Exception
  389. */
  390. public function hasHyperlink()
  391. {
  392. if (!isset($this->_parent)) {
  393. throw new PHPExcel_Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
  394. }
  395. return $this->_parent->hyperlinkExists($this->getCoordinate());
  396. }
  397. /**
  398. * Get Hyperlink
  399. *
  400. * @return PHPExcel_Cell_Hyperlink
  401. * @throws PHPExcel_Exception
  402. */
  403. public function getHyperlink()
  404. {
  405. if (!isset($this->_parent)) {
  406. throw new PHPExcel_Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
  407. }
  408. return $this->_parent->getHyperlink($this->getCoordinate());
  409. }
  410. /**
  411. * Set Hyperlink
  412. *
  413. * @param PHPExcel_Cell_Hyperlink $pHyperlink
  414. * @return PHPExcel_Cell
  415. * @throws PHPExcel_Exception
  416. */
  417. public function setHyperlink(PHPExcel_Cell_Hyperlink $pHyperlink = NULL)
  418. {
  419. if (!isset($this->_parent)) {
  420. throw new PHPExcel_Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
  421. }
  422. $this->_parent->setHyperlink($this->getCoordinate(), $pHyperlink);
  423. return $this->notifyCacheController();
  424. }
  425. /**
  426. * Get parent worksheet
  427. *
  428. * @return PHPExcel_Worksheet
  429. */
  430. public function getParent() {
  431. return $this->_parent;
  432. }
  433. /**
  434. * Re-bind parent
  435. *
  436. * @param PHPExcel_Worksheet $parent
  437. * @return PHPExcel_Cell
  438. */
  439. public function rebindParent(PHPExcel_Worksheet $parent) {
  440. $this->_parent = $parent;
  441. return $this->notifyCacheController();
  442. }
  443. /**
  444. * Is cell in a specific range?
  445. *
  446. * @param string $pRange Cell range (e.g. A1:A1)
  447. * @return boolean
  448. */
  449. public function isInRange($pRange = 'A1:A1')
  450. {
  451. list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
  452. // Translate properties
  453. $myColumn = self::columnIndexFromString($this->getColumn());
  454. $myRow = $this->getRow();
  455. // Verify if cell is in range
  456. return (($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
  457. ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow)
  458. );
  459. }
  460. /**
  461. * Coordinate from string
  462. *
  463. * @param string $pCoordinateString
  464. * @return array Array containing column and row (indexes 0 and 1)
  465. * @throws PHPExcel_Exception
  466. */
  467. public static function coordinateFromString($pCoordinateString = 'A1')
  468. {
  469. if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
  470. return array($matches[1],$matches[2]);
  471. } elseif ((strpos($pCoordinateString,':') !== FALSE) || (strpos($pCoordinateString,',') !== FALSE)) {
  472. throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
  473. } elseif ($pCoordinateString == '') {
  474. throw new PHPExcel_Exception('Cell coordinate can not be zero-length string');
  475. }
  476. throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString);
  477. }
  478. /**
  479. * Make string row, column or cell coordinate absolute
  480. *
  481. * @param string $pCoordinateString e.g. 'A' or '1' or 'A1'
  482. * Note that this value can be a row or column reference as well as a cell reference
  483. * @return string Absolute coordinate e.g. '$A' or '$1' or '$A$1'
  484. * @throws PHPExcel_Exception
  485. */
  486. public static function absoluteReference($pCoordinateString = 'A1')
  487. {
  488. if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
  489. // Split out any worksheet name from the reference
  490. $worksheet = '';
  491. $cellAddress = explode('!',$pCoordinateString);
  492. if (count($cellAddress) > 1) {
  493. list($worksheet,$pCoordinateString) = $cellAddress;
  494. }
  495. if ($worksheet > '') $worksheet .= '!';
  496. // Create absolute coordinate
  497. if (ctype_digit($pCoordinateString)) {
  498. return $worksheet . '$' . $pCoordinateString;
  499. } elseif (ctype_alpha($pCoordinateString)) {
  500. return $worksheet . '$' . strtoupper($pCoordinateString);
  501. }
  502. return $worksheet . self::absoluteCoordinate($pCoordinateString);
  503. }
  504. throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
  505. }
  506. /**
  507. * Make string coordinate absolute
  508. *
  509. * @param string $pCoordinateString e.g. 'A1'
  510. * @return string Absolute coordinate e.g. '$A$1'
  511. * @throws PHPExcel_Exception
  512. */
  513. public static function absoluteCoordinate($pCoordinateString = 'A1')
  514. {
  515. if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
  516. // Split out any worksheet name from the coordinate
  517. $worksheet = '';
  518. $cellAddress = explode('!',$pCoordinateString);
  519. if (count($cellAddress) > 1) {
  520. list($worksheet,$pCoordinateString) = $cellAddress;
  521. }
  522. if ($worksheet > '') $worksheet .= '!';
  523. // Create absolute coordinate
  524. list($column, $row) = self::coordinateFromString($pCoordinateString);
  525. $column = ltrim($column,'$');
  526. $row = ltrim($row,'$');
  527. return $worksheet . '$' . $column . '$' . $row;
  528. }
  529. throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
  530. }
  531. /**
  532. * Split range into coordinate strings
  533. *
  534. * @param string $pRange e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
  535. * @return array Array containg one or more arrays containing one or two coordinate strings
  536. * e.g. array('B4','D9') or array(array('B4','D9'),array('H2','O11'))
  537. * or array('B4')
  538. */
  539. public static function splitRange($pRange = 'A1:A1')
  540. {
  541. // Ensure $pRange is a valid range
  542. if(empty($pRange)) {
  543. $pRange = self::DEFAULT_RANGE;
  544. }
  545. $exploded = explode(',', $pRange);
  546. $counter = count($exploded);
  547. for ($i = 0; $i < $counter; ++$i) {
  548. $exploded[$i] = explode(':', $exploded[$i]);
  549. }
  550. return $exploded;
  551. }
  552. /**
  553. * Build range from coordinate strings
  554. *
  555. * @param array $pRange Array containg one or more arrays containing one or two coordinate strings
  556. * @return string String representation of $pRange
  557. * @throws PHPExcel_Exception
  558. */
  559. public static function buildRange($pRange)
  560. {
  561. // Verify range
  562. if (!is_array($pRange) || empty($pRange) || !is_array($pRange[0])) {
  563. throw new PHPExcel_Exception('Range does not contain any information');
  564. }
  565. // Build range
  566. $imploded = array();
  567. $counter = count($pRange);
  568. for ($i = 0; $i < $counter; ++$i) {
  569. $pRange[$i] = implode(':', $pRange[$i]);
  570. }
  571. $imploded = implode(',', $pRange);
  572. return $imploded;
  573. }
  574. /**
  575. * Calculate range boundaries
  576. *
  577. * @param string $pRange Cell range (e.g. A1:A1)
  578. * @return array Range coordinates array(Start Cell, End Cell)
  579. * where Start Cell and End Cell are arrays (Column Number, Row Number)
  580. */
  581. public static function rangeBoundaries($pRange = 'A1:A1')
  582. {
  583. // Ensure $pRange is a valid range
  584. if(empty($pRange)) {
  585. $pRange = self::DEFAULT_RANGE;
  586. }
  587. // Uppercase coordinate
  588. $pRange = strtoupper($pRange);
  589. // Extract range
  590. if (strpos($pRange, ':') === FALSE) {
  591. $rangeA = $rangeB = $pRange;
  592. } else {
  593. list($rangeA, $rangeB) = explode(':', $pRange);
  594. }
  595. // Calculate range outer borders
  596. $rangeStart = self::coordinateFromString($rangeA);
  597. $rangeEnd = self::coordinateFromString($rangeB);
  598. // Translate column into index
  599. $rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
  600. $rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);
  601. return array($rangeStart, $rangeEnd);
  602. }
  603. /**
  604. * Calculate range dimension
  605. *
  606. * @param string $pRange Cell range (e.g. A1:A1)
  607. * @return array Range dimension (width, height)
  608. */
  609. public static function rangeDimension($pRange = 'A1:A1')
  610. {
  611. // Calculate range outer borders
  612. list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
  613. return array( ($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1) );
  614. }
  615. /**
  616. * Calculate range boundaries
  617. *
  618. * @param string $pRange Cell range (e.g. A1:A1)
  619. * @return array Range coordinates array(Start Cell, End Cell)
  620. * where Start Cell and End Cell are arrays (Column ID, Row Number)
  621. */
  622. public static function getRangeBoundaries($pRange = 'A1:A1')
  623. {
  624. // Ensure $pRange is a valid range
  625. if(empty($pRange)) {
  626. $pRange = self::DEFAULT_RANGE;
  627. }
  628. // Uppercase coordinate
  629. $pRange = strtoupper($pRange);
  630. // Extract range
  631. if (strpos($pRange, ':') === FALSE) {
  632. $rangeA = $rangeB = $pRange;
  633. } else {
  634. list($rangeA, $rangeB) = explode(':', $pRange);
  635. }
  636. return array( self::coordinateFromString($rangeA), self::coordinateFromString($rangeB));
  637. }
  638. /**
  639. * Column index from string
  640. *
  641. * @param string $pString
  642. * @return int Column index (base 1 !!!)
  643. * @throws Exception
  644. */
  645. public static function columnIndexFromString($pString = 'A')
  646. {
  647. // Using a lookup cache adds a slight memory overhead, but boosts speed
  648. // caching using a static within the method is faster than a class static,
  649. // though it's additional memory overhead
  650. static $_indexCache = array();
  651. if (isset($_indexCache[$pString]))
  652. return $_indexCache[$pString];
  653. // It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array rather than use ord()
  654. // and make it case insensitive to get rid of the strtoupper() as well. Because it's a static, there's no significant
  655. // memory overhead either
  656. static $_columnLookup = array(
  657. 'A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12, 'M' => 13,
  658. 'N' => 14, 'O' => 15, 'P' => 16, 'Q' => 17, 'R' => 18, 'S' => 19, 'T' => 20, 'U' => 21, 'V' => 22, 'W' => 23, 'X' => 24, 'Y' => 25, 'Z' => 26,
  659. 'a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5, 'f' => 6, 'g' => 7, 'h' => 8, 'i' => 9, 'j' => 10, 'k' => 11, 'l' => 12, 'm' => 13,
  660. 'n' => 14, 'o' => 15, 'p' => 16, 'q' => 17, 'r' => 18, 's' => 19, 't' => 20, 'u' => 21, 'v' => 22, 'w' => 23, 'x' => 24, 'y' => 25, 'z' => 26
  661. );
  662. // We also use the language construct isset() rather than the more costly strlen() function to match the length of $pString
  663. // for improved performance
  664. if (isset($pString{0})) {
  665. if (!isset($pString{1})) {
  666. $_indexCache[$pString] = $_columnLookup[$pString];
  667. return $_indexCache[$pString];
  668. } elseif(!isset($pString{2})) {
  669. $_indexCache[$pString] = $_columnLookup[$pString{0}] * 26 + $_columnLookup[$pString{1}];
  670. return $_indexCache[$pString];
  671. } elseif(!isset($pString{3})) {
  672. $_indexCache[$pString] = $_columnLookup[$pString{0}] * 676 + $_columnLookup[$pString{1}] * 26 + $_columnLookup[$pString{2}];
  673. return $_indexCache[$pString];
  674. }
  675. }
  676. throw new PHPExcel_Exception("Column string index can not be " . ((isset($pString{0})) ? "longer than 3 characters" : "empty"));
  677. }
  678. /**
  679. * String from columnindex
  680. *
  681. * @param int $pColumnIndex Column index (base 0 !!!)
  682. * @return string
  683. */
  684. public static function stringFromColumnIndex($pColumnIndex = 0)
  685. {
  686. // Using a lookup cache adds a slight memory overhead, but boosts speed
  687. // caching using a static within the method is faster than a class static,
  688. // though it's additional memory overhead
  689. static $_indexCache = array();
  690. if (!isset($_indexCache[$pColumnIndex])) {
  691. // Determine column string
  692. if ($pColumnIndex < 26) {
  693. $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
  694. } elseif ($pColumnIndex < 702) {
  695. $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
  696. chr(65 + $pColumnIndex % 26);
  697. } else {
  698. $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
  699. chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
  700. chr(65 + $pColumnIndex % 26);
  701. }
  702. }
  703. return $_indexCache[$pColumnIndex];
  704. }
  705. /**
  706. * Extract all cell references in range
  707. *
  708. * @param string $pRange Range (e.g. A1 or A1:C10 or A1:E10 A20:E25)
  709. * @return array Array containing single cell references
  710. */
  711. public static function extractAllCellReferencesInRange($pRange = 'A1') {
  712. // Returnvalue
  713. $returnValue = array();
  714. // Explode spaces
  715. $cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
  716. foreach ($cellBlocks as $cellBlock) {
  717. // Single cell?
  718. if (strpos($cellBlock,':') === FALSE && strpos($cellBlock,',') === FALSE) {
  719. $returnValue[] = $cellBlock;
  720. continue;
  721. }
  722. // Range...
  723. $ranges = self::splitRange($cellBlock);
  724. foreach($ranges as $range) {
  725. // Single cell?
  726. if (!isset($range[1])) {
  727. $returnValue[] = $range[0];
  728. continue;
  729. }
  730. // Range...
  731. list($rangeStart, $rangeEnd) = $range;
  732. list($startCol, $startRow) = sscanf($rangeStart,'%[A-Z]%d');
  733. list($endCol, $endRow) = sscanf($rangeEnd,'%[A-Z]%d');
  734. $endCol++;
  735. // Current data
  736. $currentCol = $startCol;
  737. $currentRow = $startRow;
  738. // Loop cells
  739. while ($currentCol != $endCol) {
  740. while ($currentRow <= $endRow) {
  741. $returnValue[] = $currentCol.$currentRow;
  742. ++$currentRow;
  743. }
  744. ++$currentCol;
  745. $currentRow = $startRow;
  746. }
  747. }
  748. }
  749. // Sort the result by column and row
  750. $sortKeys = array();
  751. foreach (array_unique($returnValue) as $coord) {
  752. list($column,$row) = sscanf($coord,'%[A-Z]%d');
  753. $sortKeys[sprintf('%3s%09d',$column,$row)] = $coord;
  754. }
  755. ksort($sortKeys);
  756. // Return value
  757. return array_values($sortKeys);
  758. }
  759. /**
  760. * Compare 2 cells
  761. *
  762. * @param PHPExcel_Cell $a Cell a
  763. * @param PHPExcel_Cell $a Cell b
  764. * @return int Result of comparison (always -1 or 1, never zero!)
  765. */
  766. public static function compareCells(PHPExcel_Cell $a, PHPExcel_Cell $b)
  767. {
  768. if ($a->_row < $b->_row) {
  769. return -1;
  770. } elseif ($a->_row > $b->_row) {
  771. return 1;
  772. } elseif (self::columnIndexFromString($a->_column) < self::columnIndexFromString($b->_column)) {
  773. return -1;
  774. } else {
  775. return 1;
  776. }
  777. }
  778. /**
  779. * Get value binder to use
  780. *
  781. * @return PHPExcel_Cell_IValueBinder
  782. */
  783. public static function getValueBinder() {
  784. if (self::$_valueBinder === NULL) {
  785. self::$_valueBinder = new PHPExcel_Cell_DefaultValueBinder();
  786. }
  787. return self::$_valueBinder;
  788. }
  789. /**
  790. * Set value binder to use
  791. *
  792. * @param PHPExcel_Cell_IValueBinder $binder
  793. * @throws Exception
  794. */
  795. public static function setValueBinder(PHPExcel_Cell_IValueBinder $binder = NULL) {
  796. if ($binder === NULL) {
  797. throw new Exception("A PHPExcel_Cell_IValueBinder is required for PHPExcel to function correctly.");
  798. }
  799. self::$_valueBinder = $binder;
  800. }
  801. /**
  802. * Implement PHP __clone to create a deep clone, not just a shallow copy.
  803. */
  804. public function __clone() {
  805. $vars = get_object_vars($this);
  806. foreach ($vars as $key => $value) {
  807. if ((is_object($value)) && ($key != '_parent')) {
  808. $this->$key = clone $value;
  809. } else {
  810. $this->$key = $value;
  811. }
  812. }
  813. }
  814. /**
  815. * Get index to cellXf
  816. *
  817. * @return int
  818. */
  819. public function getXfIndex()
  820. {
  821. return $this->_xfIndex;
  822. }
  823. /**
  824. * Set index to cellXf
  825. *
  826. * @param int $pValue
  827. * @return PHPExcel_Cell
  828. */
  829. public function setXfIndex($pValue = 0)
  830. {
  831. $this->_xfIndex = $pValue;
  832. return $this->notifyCacheController();
  833. }
  834. /**
  835. * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling
  836. */
  837. public function setFormulaAttributes($pAttributes)
  838. {
  839. $this->_formulaAttributes = $pAttributes;
  840. return $this;
  841. }
  842. /**
  843. * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling
  844. */
  845. public function getFormulaAttributes()
  846. {
  847. return $this->_formulaAttributes;
  848. }
  849. /**
  850. * Convert to string
  851. *
  852. * @return string
  853. */
  854. public function __toString()
  855. {
  856. return (string) $this->getValue();
  857. }
  858. }