1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_Cell
37: {
38:
39: 40: 41: 42: 43:
44: const DEFAULT_RANGE = 'A1:A1';
45:
46: 47: 48: 49: 50:
51: private static $_valueBinder = NULL;
52:
53: 54: 55: 56: 57:
58: private $_value;
59:
60: 61: 62: 63: 64: 65: 66: 67: 68: 69:
70: private $_calculatedValue = NULL;
71:
72: 73: 74: 75: 76:
77: private $_dataType;
78:
79: 80: 81: 82: 83:
84: private $_parent;
85:
86: 87: 88: 89: 90:
91: private $_xfIndex;
92:
93: 94: 95: 96:
97: private $_formulaAttributes;
98:
99:
100: 101: 102: 103: 104:
105: public function notifyCacheController() {
106: $this->_parent->updateCacheData($this);
107:
108: return $this;
109: }
110:
111: public function detach() {
112: $this->_parent = NULL;
113: }
114:
115: public function attach(PHPExcel_CachedObjectStorage_CacheBase $parent) {
116:
117:
118: $this->_parent = $parent;
119: }
120:
121:
122: 123: 124: 125: 126: 127: 128: 129:
130: public function __construct($pValue = NULL, $pDataType = NULL, PHPExcel_Worksheet $pSheet = NULL)
131: {
132:
133: $this->_value = $pValue;
134:
135:
136: $this->_parent = $pSheet->getCellCacheController();
137:
138:
139: if ($pDataType !== NULL) {
140: if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
141: $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
142: $this->_dataType = $pDataType;
143: } else {
144: if (!self::getValueBinder()->bindValue($this, $pValue)) {
145: throw new PHPExcel_Exception("Value could not be bound to cell.");
146: }
147: }
148:
149:
150: $this->_xfIndex = 0;
151: }
152:
153: 154: 155: 156: 157:
158: public function getColumn()
159: {
160: return $this->_parent->getCurrentColumn();
161: }
162:
163: 164: 165: 166: 167:
168: public function getRow()
169: {
170: return $this->_parent->getCurrentRow();
171: }
172:
173: 174: 175: 176: 177:
178: public function getCoordinate()
179: {
180: return $this->_parent->getCurrentAddress();
181: }
182:
183: 184: 185: 186: 187:
188: public function getValue()
189: {
190: return $this->_value;
191: }
192:
193: 194: 195: 196: 197:
198: public function getFormattedValue()
199: {
200: return (string) PHPExcel_Style_NumberFormat::toFormattedString(
201: $this->getCalculatedValue(),
202: $this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex())
203: ->getNumberFormat()->getFormatCode()
204: );
205: }
206:
207: 208: 209: 210: 211: 212: 213: 214: 215:
216: public function setValue($pValue = NULL)
217: {
218: if (!self::getValueBinder()->bindValue($this, $pValue)) {
219: throw new PHPExcel_Exception("Value could not be bound to cell.");
220: }
221: return $this;
222: }
223:
224: 225: 226: 227: 228: 229: 230: 231:
232: public function setValueExplicit($pValue = NULL, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
233: {
234:
235: switch ($pDataType) {
236: case PHPExcel_Cell_DataType::TYPE_NULL:
237: $this->_value = $pValue;
238: break;
239: case PHPExcel_Cell_DataType::TYPE_STRING2:
240: $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
241: case PHPExcel_Cell_DataType::TYPE_STRING:
242: case PHPExcel_Cell_DataType::TYPE_INLINE:
243: $this->_value = PHPExcel_Cell_DataType::checkString($pValue);
244: break;
245: case PHPExcel_Cell_DataType::TYPE_NUMERIC:
246: $this->_value = (float)$pValue;
247: break;
248: case PHPExcel_Cell_DataType::TYPE_FORMULA:
249: $this->_value = (string)$pValue;
250: break;
251: case PHPExcel_Cell_DataType::TYPE_BOOL:
252: $this->_value = (bool)$pValue;
253: break;
254: case PHPExcel_Cell_DataType::TYPE_ERROR:
255: $this->_value = PHPExcel_Cell_DataType::checkErrorCode($pValue);
256: break;
257: default:
258: throw new PHPExcel_Exception('Invalid datatype: ' . $pDataType);
259: break;
260: }
261:
262:
263: $this->_dataType = $pDataType;
264:
265: return $this->notifyCacheController();
266: }
267:
268: 269: 270: 271: 272: 273: 274: 275: 276:
277: public function getCalculatedValue($resetLog = TRUE)
278: {
279:
280: if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) {
281: try {
282:
283: $result = PHPExcel_Calculation::getInstance(
284: $this->getWorksheet()->getParent()
285: )->calculateCellValue($this,$resetLog);
286:
287:
288: if (is_array($result)) {
289: while (is_array($result)) {
290: $result = array_pop($result);
291: }
292: }
293: } catch ( PHPExcel_Exception $ex ) {
294: if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->_calculatedValue !== NULL)) {
295:
296: return $this->_calculatedValue;
297: }
298:
299: $result = '#N/A';
300: throw new PHPExcel_Calculation_Exception(
301: $this->getWorksheet()->getTitle().'!'.$this->getCoordinate().' -> '.$ex->getMessage()
302: );
303: }
304:
305: if ($result === '#Not Yet Implemented') {
306:
307: return $this->_calculatedValue;
308: }
309:
310: return $result;
311: } elseif($this->_value instanceof PHPExcel_RichText) {
312:
313: return $this->_value->getPlainText();
314: }
315:
316: return $this->_value;
317: }
318:
319: 320: 321: 322: 323: 324:
325: public function setCalculatedValue($pValue = NULL)
326: {
327: if ($pValue !== NULL) {
328: $this->_calculatedValue = (is_numeric($pValue)) ? (float) $pValue : $pValue;
329: }
330:
331: return $this->notifyCacheController();
332: }
333:
334: 335: 336: 337: 338: 339: 340: 341: 342: 343:
344: public function getOldCalculatedValue()
345: {
346: return $this->_calculatedValue;
347: }
348:
349: 350: 351: 352: 353:
354: public function getDataType()
355: {
356: return $this->_dataType;
357: }
358:
359: 360: 361: 362: 363: 364:
365: public function setDataType($pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
366: {
367: if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
368: $pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
369:
370: $this->_dataType = $pDataType;
371:
372: return $this->notifyCacheController();
373: }
374:
375: 376: 377: 378: 379:
380: public function isFormula()
381: {
382: return $this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA;
383: }
384:
385: 386: 387: 388: 389: 390:
391: public function hasDataValidation()
392: {
393: if (!isset($this->_parent)) {
394: throw new PHPExcel_Exception('Cannot check for data validation when cell is not bound to a worksheet');
395: }
396:
397: return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
398: }
399:
400: 401: 402: 403: 404: 405:
406: public function getDataValidation()
407: {
408: if (!isset($this->_parent)) {
409: throw new PHPExcel_Exception('Cannot get data validation for cell that is not bound to a worksheet');
410: }
411:
412: return $this->getWorksheet()->getDataValidation($this->getCoordinate());
413: }
414:
415: 416: 417: 418: 419: 420: 421:
422: public function setDataValidation(PHPExcel_Cell_DataValidation $pDataValidation = NULL)
423: {
424: if (!isset($this->_parent)) {
425: throw new PHPExcel_Exception('Cannot set data validation for cell that is not bound to a worksheet');
426: }
427:
428: $this->getWorksheet()->setDataValidation($this->getCoordinate(), $pDataValidation);
429:
430: return $this->notifyCacheController();
431: }
432:
433: 434: 435: 436: 437: 438:
439: public function hasHyperlink()
440: {
441: if (!isset($this->_parent)) {
442: throw new PHPExcel_Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
443: }
444:
445: return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
446: }
447:
448: 449: 450: 451: 452: 453:
454: public function getHyperlink()
455: {
456: if (!isset($this->_parent)) {
457: throw new PHPExcel_Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
458: }
459:
460: return $this->getWorksheet()->getHyperlink($this->getCoordinate());
461: }
462:
463: 464: 465: 466: 467: 468: 469:
470: public function setHyperlink(PHPExcel_Cell_Hyperlink $pHyperlink = NULL)
471: {
472: if (!isset($this->_parent)) {
473: throw new PHPExcel_Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
474: }
475:
476: $this->getWorksheet()->setHyperlink($this->getCoordinate(), $pHyperlink);
477:
478: return $this->notifyCacheController();
479: }
480:
481: 482: 483: 484: 485:
486: public function getParent() {
487: return $this->_parent;
488: }
489:
490: 491: 492: 493: 494:
495: public function getWorksheet() {
496: return $this->_parent->getParent();
497: }
498:
499: 500: 501: 502: 503:
504: public function getStyle()
505: {
506: return $this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex());
507: }
508:
509: 510: 511: 512: 513: 514:
515: public function rebindParent(PHPExcel_Worksheet $parent) {
516: $this->_parent = $parent->getCellCacheController();
517:
518: return $this->notifyCacheController();
519: }
520:
521: 522: 523: 524: 525: 526:
527: public function isInRange($pRange = 'A1:A1')
528: {
529: list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
530:
531:
532: $myColumn = self::columnIndexFromString($this->getColumn());
533: $myRow = $this->getRow();
534:
535:
536: return (($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
537: ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow)
538: );
539: }
540:
541: 542: 543: 544: 545: 546: 547:
548: public static function coordinateFromString($pCoordinateString = 'A1')
549: {
550: if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
551: return array($matches[1],$matches[2]);
552: } elseif ((strpos($pCoordinateString,':') !== FALSE) || (strpos($pCoordinateString,',') !== FALSE)) {
553: throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
554: } elseif ($pCoordinateString == '') {
555: throw new PHPExcel_Exception('Cell coordinate can not be zero-length string');
556: }
557:
558: throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString);
559: }
560:
561: 562: 563: 564: 565: 566: 567: 568:
569: public static function absoluteReference($pCoordinateString = 'A1')
570: {
571: if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
572:
573: $worksheet = '';
574: $cellAddress = explode('!',$pCoordinateString);
575: if (count($cellAddress) > 1) {
576: list($worksheet,$pCoordinateString) = $cellAddress;
577: }
578: if ($worksheet > '') $worksheet .= '!';
579:
580:
581: if (ctype_digit($pCoordinateString)) {
582: return $worksheet . '$' . $pCoordinateString;
583: } elseif (ctype_alpha($pCoordinateString)) {
584: return $worksheet . '$' . strtoupper($pCoordinateString);
585: }
586: return $worksheet . self::absoluteCoordinate($pCoordinateString);
587: }
588:
589: throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
590: }
591:
592: 593: 594: 595: 596: 597: 598:
599: public static function absoluteCoordinate($pCoordinateString = 'A1')
600: {
601: if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
602:
603: $worksheet = '';
604: $cellAddress = explode('!',$pCoordinateString);
605: if (count($cellAddress) > 1) {
606: list($worksheet,$pCoordinateString) = $cellAddress;
607: }
608: if ($worksheet > '') $worksheet .= '!';
609:
610:
611: list($column, $row) = self::coordinateFromString($pCoordinateString);
612: $column = ltrim($column,'$');
613: $row = ltrim($row,'$');
614: return $worksheet . '$' . $column . '$' . $row;
615: }
616:
617: throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
618: }
619:
620: 621: 622: 623: 624: 625: 626: 627:
628: public static function splitRange($pRange = 'A1:A1')
629: {
630:
631: if(empty($pRange)) {
632: $pRange = self::DEFAULT_RANGE;
633: }
634:
635: $exploded = explode(',', $pRange);
636: $counter = count($exploded);
637: for ($i = 0; $i < $counter; ++$i) {
638: $exploded[$i] = explode(':', $exploded[$i]);
639: }
640: return $exploded;
641: }
642:
643: 644: 645: 646: 647: 648: 649:
650: public static function buildRange($pRange)
651: {
652:
653: if (!is_array($pRange) || empty($pRange) || !is_array($pRange[0])) {
654: throw new PHPExcel_Exception('Range does not contain any information');
655: }
656:
657:
658: $imploded = array();
659: $counter = count($pRange);
660: for ($i = 0; $i < $counter; ++$i) {
661: $pRange[$i] = implode(':', $pRange[$i]);
662: }
663: $imploded = implode(',', $pRange);
664:
665: return $imploded;
666: }
667:
668: 669: 670: 671: 672: 673: 674:
675: public static function rangeBoundaries($pRange = 'A1:A1')
676: {
677:
678: if(empty($pRange)) {
679: $pRange = self::DEFAULT_RANGE;
680: }
681:
682:
683: $pRange = strtoupper($pRange);
684:
685:
686: if (strpos($pRange, ':') === FALSE) {
687: $rangeA = $rangeB = $pRange;
688: } else {
689: list($rangeA, $rangeB) = explode(':', $pRange);
690: }
691:
692:
693: $rangeStart = self::coordinateFromString($rangeA);
694: $rangeEnd = self::coordinateFromString($rangeB);
695:
696:
697: $rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
698: $rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);
699:
700: return array($rangeStart, $rangeEnd);
701: }
702:
703: 704: 705: 706: 707: 708:
709: public static function rangeDimension($pRange = 'A1:A1')
710: {
711:
712: list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
713:
714: return array( ($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1) );
715: }
716:
717: 718: 719: 720: 721: 722: 723:
724: public static function getRangeBoundaries($pRange = 'A1:A1')
725: {
726:
727: if(empty($pRange)) {
728: $pRange = self::DEFAULT_RANGE;
729: }
730:
731:
732: $pRange = strtoupper($pRange);
733:
734:
735: if (strpos($pRange, ':') === FALSE) {
736: $rangeA = $rangeB = $pRange;
737: } else {
738: list($rangeA, $rangeB) = explode(':', $pRange);
739: }
740:
741: return array( self::coordinateFromString($rangeA), self::coordinateFromString($rangeB));
742: }
743:
744: 745: 746: 747: 748: 749:
750: public static function columnIndexFromString($pString = 'A')
751: {
752:
753:
754:
755: static $_indexCache = array();
756:
757: if (isset($_indexCache[$pString]))
758: return $_indexCache[$pString];
759:
760:
761:
762:
763: static $_columnLookup = array(
764: '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,
765: '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,
766: '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,
767: '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
768: );
769:
770:
771:
772: if (isset($pString{0})) {
773: if (!isset($pString{1})) {
774: $_indexCache[$pString] = $_columnLookup[$pString];
775: return $_indexCache[$pString];
776: } elseif(!isset($pString{2})) {
777: $_indexCache[$pString] = $_columnLookup[$pString{0}] * 26 + $_columnLookup[$pString{1}];
778: return $_indexCache[$pString];
779: } elseif(!isset($pString{3})) {
780: $_indexCache[$pString] = $_columnLookup[$pString{0}] * 676 + $_columnLookup[$pString{1}] * 26 + $_columnLookup[$pString{2}];
781: return $_indexCache[$pString];
782: }
783: }
784: throw new PHPExcel_Exception("Column string index can not be " . ((isset($pString{0})) ? "longer than 3 characters" : "empty"));
785: }
786:
787: 788: 789: 790: 791: 792:
793: public static function stringFromColumnIndex($pColumnIndex = 0)
794: {
795:
796:
797:
798: static $_indexCache = array();
799:
800: if (!isset($_indexCache[$pColumnIndex])) {
801:
802: if ($pColumnIndex < 26) {
803: $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
804: } elseif ($pColumnIndex < 702) {
805: $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
806: chr(65 + $pColumnIndex % 26);
807: } else {
808: $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
809: chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
810: chr(65 + $pColumnIndex % 26);
811: }
812: }
813: return $_indexCache[$pColumnIndex];
814: }
815:
816: 817: 818: 819: 820: 821:
822: public static function extractAllCellReferencesInRange($pRange = 'A1') {
823:
824: $returnValue = array();
825:
826:
827: $cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
828: foreach ($cellBlocks as $cellBlock) {
829:
830: if (strpos($cellBlock,':') === FALSE && strpos($cellBlock,',') === FALSE) {
831: $returnValue[] = $cellBlock;
832: continue;
833: }
834:
835:
836: $ranges = self::splitRange($cellBlock);
837: foreach($ranges as $range) {
838:
839: if (!isset($range[1])) {
840: $returnValue[] = $range[0];
841: continue;
842: }
843:
844:
845: list($rangeStart, $rangeEnd) = $range;
846: sscanf($rangeStart,'%[A-Z]%d', $startCol, $startRow);
847: sscanf($rangeEnd,'%[A-Z]%d', $endCol, $endRow);
848: $endCol++;
849:
850:
851: $currentCol = $startCol;
852: $currentRow = $startRow;
853:
854:
855: while ($currentCol != $endCol) {
856: while ($currentRow <= $endRow) {
857: $returnValue[] = $currentCol.$currentRow;
858: ++$currentRow;
859: }
860: ++$currentCol;
861: $currentRow = $startRow;
862: }
863: }
864: }
865:
866:
867: $sortKeys = array();
868: foreach (array_unique($returnValue) as $coord) {
869: sscanf($coord,'%[A-Z]%d', $column, $row);
870: $sortKeys[sprintf('%3s%09d',$column,$row)] = $coord;
871: }
872: ksort($sortKeys);
873:
874:
875: return array_values($sortKeys);
876: }
877:
878: 879: 880: 881: 882: 883: 884:
885: public static function compareCells(PHPExcel_Cell $a, PHPExcel_Cell $b)
886: {
887: if ($a->getRow() < $b->getRow()) {
888: return -1;
889: } elseif ($a->getRow() > $b->getRow()) {
890: return 1;
891: } elseif (self::columnIndexFromString($a->getColumn()) < self::columnIndexFromString($b->getColumn())) {
892: return -1;
893: } else {
894: return 1;
895: }
896: }
897:
898: 899: 900: 901: 902:
903: public static function getValueBinder() {
904: if (self::$_valueBinder === NULL) {
905: self::$_valueBinder = new PHPExcel_Cell_DefaultValueBinder();
906: }
907:
908: return self::$_valueBinder;
909: }
910:
911: 912: 913: 914: 915: 916:
917: public static function setValueBinder(PHPExcel_Cell_IValueBinder $binder = NULL) {
918: if ($binder === NULL) {
919: throw new PHPExcel_Exception("A PHPExcel_Cell_IValueBinder is required for PHPExcel to function correctly.");
920: }
921:
922: self::$_valueBinder = $binder;
923: }
924:
925: 926: 927:
928: public function __clone() {
929: $vars = get_object_vars($this);
930: foreach ($vars as $key => $value) {
931: if ((is_object($value)) && ($key != '_parent')) {
932: $this->$key = clone $value;
933: } else {
934: $this->$key = $value;
935: }
936: }
937: }
938:
939: 940: 941: 942: 943:
944: public function getXfIndex()
945: {
946: return $this->_xfIndex;
947: }
948:
949: 950: 951: 952: 953: 954:
955: public function setXfIndex($pValue = 0)
956: {
957: $this->_xfIndex = $pValue;
958:
959: return $this->notifyCacheController();
960: }
961:
962: 963: 964:
965: public function setFormulaAttributes($pAttributes)
966: {
967: $this->_formulaAttributes = $pAttributes;
968: return $this;
969: }
970:
971: 972: 973:
974: public function getFormulaAttributes()
975: {
976: return $this->_formulaAttributes;
977: }
978:
979: 980: 981: 982: 983:
984: public function __toString()
985: {
986: return (string) $this->getValue();
987: }
988:
989: }
990:
991: