Cell.Class.php 32 KB

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