ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Cell.php
Go to the documentation of this file.
1<?php
2
4
12
13class Cell
14{
20 private static $valueBinder;
21
27 private $value;
28
40
46 private $dataType;
47
53 private $parent;
54
60 private $xfIndex = 0;
61
66
72 public function updateInCollection()
73 {
74 $this->parent->update($this);
75
76 return $this;
77 }
78
79 public function detach(): void
80 {
81 // @phpstan-ignore-next-line
82 $this->parent = null;
83 }
84
85 public function attach(Cells $parent): void
86 {
87 $this->parent = $parent;
88 }
89
96 public function __construct($pValue, $pDataType, Worksheet $pSheet)
97 {
98 // Initialise cell value
99 $this->value = $pValue;
100
101 // Set worksheet cache
102 $this->parent = $pSheet->getCellCollection();
103
104 // Set datatype?
105 if ($pDataType !== null) {
106 if ($pDataType == DataType::TYPE_STRING2) {
107 $pDataType = DataType::TYPE_STRING;
108 }
109 $this->dataType = $pDataType;
110 } elseif (!self::getValueBinder()->bindValue($this, $pValue)) {
111 throw new Exception('Value could not be bound to cell.');
112 }
113 }
114
120 public function getColumn()
121 {
122 return $this->parent->getCurrentColumn();
123 }
124
130 public function getRow()
131 {
132 return $this->parent->getCurrentRow();
133 }
134
140 public function getCoordinate()
141 {
142 return $this->parent->getCurrentCoordinate();
143 }
144
150 public function getValue()
151 {
152 return $this->value;
153 }
154
160 public function getFormattedValue()
161 {
162 return (string) NumberFormat::toFormattedString(
163 $this->getCalculatedValue(),
164 $this->getStyle()
165 ->getNumberFormat()->getFormatCode()
166 );
167 }
168
178 public function setValue($pValue)
179 {
180 if (!self::getValueBinder()->bindValue($this, $pValue)) {
181 throw new Exception('Value could not be bound to cell.');
182 }
183
184 return $this;
185 }
186
195 public function setValueExplicit($pValue, $pDataType)
196 {
197 // set the value according to data type
198 switch ($pDataType) {
200 $this->value = $pValue;
201
202 break;
204 $pDataType = DataType::TYPE_STRING;
205 // no break
207 // Synonym for string
209 // Rich text
210 $this->value = DataType::checkString($pValue);
211
212 break;
214 if (is_string($pValue) && !is_numeric($pValue)) {
215 throw new Exception('Invalid numeric value for datatype Numeric');
216 }
217 $this->value = 0 + $pValue;
218
219 break;
221 $this->value = (string) $pValue;
222
223 break;
225 $this->value = (bool) $pValue;
226
227 break;
229 $this->value = DataType::checkErrorCode($pValue);
230
231 break;
232 default:
233 throw new Exception('Invalid datatype: ' . $pDataType);
234
235 break;
236 }
237
238 // set the datatype
239 $this->dataType = $pDataType;
240
241 return $this->updateInCollection();
242 }
243
251 public function getCalculatedValue($resetLog = true)
252 {
253 if ($this->dataType == DataType::TYPE_FORMULA) {
254 try {
255 $index = $this->getWorksheet()->getParent()->getActiveSheetIndex();
256 $selected = $this->getWorksheet()->getSelectedCells();
258 $this->getWorksheet()->getParent()
259 )->calculateCellValue($this, $resetLog);
260 $this->getWorksheet()->setSelectedCells($selected);
261 $this->getWorksheet()->getParent()->setActiveSheetIndex($index);
262 // We don't yet handle array returns
263 if (is_array($result)) {
264 while (is_array($result)) {
265 $result = array_shift($result);
266 }
267 }
268 } catch (Exception $ex) {
269 if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
270 return $this->calculatedValue; // Fallback for calculations referencing external files.
271 } elseif (preg_match('/[Uu]ndefined (name|offset: 2|array key 2)/', $ex->getMessage()) === 1) {
272 return \PhpOffice\PhpSpreadsheet\Calculation\Functions::NAME();
273 }
274
275 throw new \PhpOffice\PhpSpreadsheet\Calculation\Exception(
276 $this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage()
277 );
278 }
279
280 if ($result === '#Not Yet Implemented') {
281 return $this->calculatedValue; // Fallback if calculation engine does not support the formula.
282 }
283
284 return $result;
285 } elseif ($this->value instanceof RichText) {
286 return $this->value->getPlainText();
287 }
288
289 return $this->value;
290 }
291
299 public function setCalculatedValue($pValue)
300 {
301 if ($pValue !== null) {
302 $this->calculatedValue = (is_numeric($pValue)) ? (float) $pValue : $pValue;
303 }
304
305 return $this->updateInCollection();
306 }
307
318 public function getOldCalculatedValue()
319 {
321 }
322
328 public function getDataType()
329 {
330 return $this->dataType;
331 }
332
340 public function setDataType($pDataType)
341 {
342 if ($pDataType == DataType::TYPE_STRING2) {
343 $pDataType = DataType::TYPE_STRING;
344 }
345 $this->dataType = $pDataType;
346
347 return $this->updateInCollection();
348 }
349
355 public function isFormula()
356 {
357 return $this->dataType == DataType::TYPE_FORMULA;
358 }
359
365 public function hasDataValidation()
366 {
367 if (!isset($this->parent)) {
368 throw new Exception('Cannot check for data validation when cell is not bound to a worksheet');
369 }
370
371 return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
372 }
373
379 public function getDataValidation()
380 {
381 if (!isset($this->parent)) {
382 throw new Exception('Cannot get data validation for cell that is not bound to a worksheet');
383 }
384
385 return $this->getWorksheet()->getDataValidation($this->getCoordinate());
386 }
387
395 public function setDataValidation(?DataValidation $pDataValidation = null)
396 {
397 if (!isset($this->parent)) {
398 throw new Exception('Cannot set data validation for cell that is not bound to a worksheet');
399 }
400
401 $this->getWorksheet()->setDataValidation($this->getCoordinate(), $pDataValidation);
402
403 return $this->updateInCollection();
404 }
405
411 public function hasValidValue()
412 {
413 $validator = new DataValidator();
414
415 return $validator->isValid($this);
416 }
417
423 public function hasHyperlink()
424 {
425 if (!isset($this->parent)) {
426 throw new Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
427 }
428
429 return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
430 }
431
437 public function getHyperlink()
438 {
439 if (!isset($this->parent)) {
440 throw new Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
441 }
442
443 return $this->getWorksheet()->getHyperlink($this->getCoordinate());
444 }
445
453 public function setHyperlink(?Hyperlink $pHyperlink = null)
454 {
455 if (!isset($this->parent)) {
456 throw new Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
457 }
458
459 $this->getWorksheet()->setHyperlink($this->getCoordinate(), $pHyperlink);
460
461 return $this->updateInCollection();
462 }
463
469 public function getParent()
470 {
471 return $this->parent;
472 }
473
479 public function getWorksheet()
480 {
481 return $this->parent->getParent();
482 }
483
489 public function isInMergeRange()
490 {
491 return (bool) $this->getMergeRange();
492 }
493
499 public function isMergeRangeValueCell()
500 {
501 if ($mergeRange = $this->getMergeRange()) {
502 $mergeRange = Coordinate::splitRange($mergeRange);
503 [$startCell] = $mergeRange[0];
504 if ($this->getCoordinate() === $startCell) {
505 return true;
506 }
507 }
508
509 return false;
510 }
511
517 public function getMergeRange()
518 {
519 foreach ($this->getWorksheet()->getMergeCells() as $mergeRange) {
520 if ($this->isInRange($mergeRange)) {
521 return $mergeRange;
522 }
523 }
524
525 return false;
526 }
527
533 public function getStyle()
534 {
535 return $this->getWorksheet()->getStyle($this->getCoordinate());
536 }
537
544 {
545 $this->parent = $parent->getCellCollection();
546
547 return $this->updateInCollection();
548 }
549
557 public function isInRange($pRange)
558 {
559 [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($pRange);
560
561 // Translate properties
562 $myColumn = Coordinate::columnIndexFromString($this->getColumn());
563 $myRow = $this->getRow();
564
565 // Verify if cell is in range
566 return ($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
567 ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow);
568 }
569
578 public static function compareCells(self $a, self $b)
579 {
580 if ($a->getRow() < $b->getRow()) {
581 return -1;
582 } elseif ($a->getRow() > $b->getRow()) {
583 return 1;
584 } elseif (Coordinate::columnIndexFromString($a->getColumn()) < Coordinate::columnIndexFromString($b->getColumn())) {
585 return -1;
586 }
587
588 return 1;
589 }
590
596 public static function getValueBinder()
597 {
598 if (self::$valueBinder === null) {
599 self::$valueBinder = new DefaultValueBinder();
600 }
601
602 return self::$valueBinder;
603 }
604
608 public static function setValueBinder(IValueBinder $binder): void
609 {
610 self::$valueBinder = $binder;
611 }
612
616 public function __clone()
617 {
618 $vars = get_object_vars($this);
619 foreach ($vars as $key => $value) {
620 if ((is_object($value)) && ($key != 'parent')) {
621 $this->$key = clone $value;
622 } else {
623 $this->$key = $value;
624 }
625 }
626 }
627
633 public function getXfIndex()
634 {
635 return $this->xfIndex;
636 }
637
645 public function setXfIndex($pValue)
646 {
647 $this->xfIndex = $pValue;
648
649 return $this->updateInCollection();
650 }
651
659 public function setFormulaAttributes($pAttributes)
660 {
661 $this->formulaAttributes = $pAttributes;
662
663 return $this;
664 }
665
669 public function getFormulaAttributes()
670 {
672 }
673
679 public function __toString()
680 {
681 return (string) $this->getValue();
682 }
683}
$result
An exception for terminatinating execution or to throw for unit testing.
static getInstance(?Spreadsheet $spreadsheet=null)
Get an instance of this class.
setXfIndex($pValue)
Set index to cellXf.
Definition: Cell.php:645
getDataValidation()
Get Data validation rules.
Definition: Cell.php:379
static compareCells(self $a, self $b)
Compare 2 cells.
Definition: Cell.php:578
getFormattedValue()
Get cell value with formatting.
Definition: Cell.php:160
updateInCollection()
Update the cell into the cell collection.
Definition: Cell.php:72
getWorksheet()
Get parent worksheet.
Definition: Cell.php:479
getParent()
Get cell collection.
Definition: Cell.php:469
getMergeRange()
If this cell is in a merge range, then return the range.
Definition: Cell.php:517
__toString()
Convert to string.
Definition: Cell.php:679
__clone()
Implement PHP __clone to create a deep clone, not just a shallow copy.
Definition: Cell.php:616
setDataType($pDataType)
Set cell data type.
Definition: Cell.php:340
hasHyperlink()
Does this cell contain a Hyperlink?
Definition: Cell.php:423
hasDataValidation()
Does this cell contain Data validation rules?
Definition: Cell.php:365
getColumn()
Get cell coordinate column.
Definition: Cell.php:120
rebindParent(Worksheet $parent)
Re-bind parent.
Definition: Cell.php:543
isFormula()
Identify if the cell contains a formula.
Definition: Cell.php:355
isInRange($pRange)
Is cell in a specific range?
Definition: Cell.php:557
getCoordinate()
Get cell coordinate.
Definition: Cell.php:140
setValueExplicit($pValue, $pDataType)
Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the ...
Definition: Cell.php:195
isMergeRangeValueCell()
Is this cell the master (top left cell) in a merge range (that holds the actual data value).
Definition: Cell.php:499
static setValueBinder(IValueBinder $binder)
Set value binder to use.
Definition: Cell.php:608
__construct($pValue, $pDataType, Worksheet $pSheet)
Create a new Cell.
Definition: Cell.php:96
getValue()
Get cell value.
Definition: Cell.php:150
getHyperlink()
Get Hyperlink.
Definition: Cell.php:437
static getValueBinder()
Get value binder to use.
Definition: Cell.php:596
setHyperlink(?Hyperlink $pHyperlink=null)
Set Hyperlink.
Definition: Cell.php:453
getFormulaAttributes()
Get the formula attributes.
Definition: Cell.php:669
getStyle()
Get cell style.
Definition: Cell.php:533
setCalculatedValue($pValue)
Set old calculated value (cached).
Definition: Cell.php:299
getOldCalculatedValue()
Get old calculated value (cached) This returns the value last calculated by MS Excel or whichever spr...
Definition: Cell.php:318
getRow()
Get cell coordinate row.
Definition: Cell.php:130
$formulaAttributes
Attributes of the formula.
Definition: Cell.php:65
isInMergeRange()
Is this cell in a merge range.
Definition: Cell.php:489
getDataType()
Get cell data type.
Definition: Cell.php:328
setValue($pValue)
Set cell value.
Definition: Cell.php:178
hasValidValue()
Does this cell contain valid value?
Definition: Cell.php:411
getCalculatedValue($resetLog=true)
Get calculated cell value.
Definition: Cell.php:251
setDataValidation(?DataValidation $pDataValidation=null)
Set Data validation rules.
Definition: Cell.php:395
getXfIndex()
Get index to cellXf.
Definition: Cell.php:633
setFormulaAttributes($pAttributes)
Set the formula attributes.
Definition: Cell.php:659
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
static rangeBoundaries($pRange)
Calculate range boundaries.
Definition: Coordinate.php:187
static checkErrorCode($pValue)
Check a value that it is a valid error code.
Definition: DataType.php:75
static checkString($pValue)
Check a string that it satisfies Excel requirements.
Definition: DataType.php:52
Validate a cell value according to its validation rules.
static toFormattedString($value, $format, $callBack=null)
Convert a value in a pre-defined format to a PHP string.
$key
Definition: croninfo.php:18
$index
Definition: metadata.php:60