tablepress / libraries / vendor / PhpSpreadsheet / Cell / Cell.php

Cell.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Cell/Cell.php

1,132 lines 32.3 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Cell;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception as CalculationException;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
9	use TablePress\PhpOffice\PhpSpreadsheet\Collection\Cells;
10	use TablePress\PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;
11	use TablePress\PhpOffice\PhpSpreadsheet\RichText\RichText;
12	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;
13	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
14	use TablePress\PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\CellStyleAssessor;
15	use TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
16	use TablePress\PhpOffice\PhpSpreadsheet\Style\Protection;
17	use TablePress\PhpOffice\PhpSpreadsheet\Style\Style;
18	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\BaseDrawing;
19	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Table;
20	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
21	use Stringable;
22
23	class Cell
24	{
25	/**
26	* Value binder to use.
27	*/
28	private static ?IValueBinder $valueBinder = null;
29	/**
30	* Value of the cell.
31	* @var mixed
32	*/
33	private $value;
34	/**
35	* Calculated value of the cell (used for caching)
36	* This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
37	* create the original spreadsheet file.
38	* Note that this value is not guaranteed to reflect the actual calculated value because it is
39	* possible that auto-calculation was disabled in the original spreadsheet, and underlying data
40	* values used by the formula have changed since it was last calculated.
41	*
42	* @var mixed
43	*/
44	private $calculatedValue;
45	/**
46	* Type of the cell data.
47	*/
48	private string $dataType;
49	/**
50	* The collection of cells that this cell belongs to (i.e. The Cell Collection for the parent Worksheet).
51	*/
52	private ?Cells $parent;
53	/**
54	* Index to the cellXf reference for the styling of this cell.
55	*/
56	private int $xfIndex = 0;
57	/**
58	* Attributes of the formula.
59	*
60	* @var null\|array<string, string>
61	*/
62	private ?array $formulaAttributes = null;
63	private IgnoredErrors $ignoredErrors;
64	/**
65	* Update the cell into the cell collection.
66	*
67	* @throws SpreadsheetException
68	*/
69	public function updateInCollection(): self
70	{
71	$parent = $this->parent;
72	if ($parent === null) {
73	throw new SpreadsheetException('Cannot update when cell is not bound to a worksheet');
74	}
75	$parent->update($this);
76
77	return $this;
78	}
79	public function detach(): void
80	{
81	$this->parent = null;
82	}
83	public function attach(Cells $parent): void
84	{
85	$this->parent = $parent;
86	}
87	/**
88	* Create a new Cell.
89	*
90	* @throws SpreadsheetException
91	* @param mixed $value
92	*/
93	public function __construct($value, ?string $dataType, Worksheet $worksheet)
94	{
95	// Initialise cell value
96	$this->value = $value;
97
98	// Set worksheet cache
99	$this->parent = $worksheet->getCellCollection();
100
101	// Set datatype?
102	if ($dataType !== null) {
103	if ($dataType == DataType::TYPE_STRING2) {
104	$dataType = DataType::TYPE_STRING;
105	}
106	$this->dataType = $dataType;
107	} else {
108	$valueBinder = (($nullsafeVariable1 = $worksheet->getParent()) ? $nullsafeVariable1->getValueBinder() : null) ?? self::getValueBinder();
109	if ($valueBinder->bindValue($this, $value) === false) {
110	throw new SpreadsheetException('Value could not be bound to cell.');
111	}
112	}
113	$this->ignoredErrors = new IgnoredErrors();
114	}
115	/**
116	* Get cell coordinate column.
117	*
118	* @throws SpreadsheetException
119	*/
120	public function getColumn(): string
121	{
122	$parent = $this->parent;
123	if ($parent === null) {
124	throw new SpreadsheetException('Cannot get column when cell is not bound to a worksheet');
125	}
126
127	return $parent->getCurrentColumn();
128	}
129	/**
130	* Get cell coordinate row.
131	*
132	* @throws SpreadsheetException
133	*/
134	public function getRow(): int
135	{
136	$parent = $this->parent;
137	if ($parent === null) {
138	throw new SpreadsheetException('Cannot get row when cell is not bound to a worksheet');
139	}
140
141	return $parent->getCurrentRow();
142	}
143	/**
144	* Get cell coordinate.
145	*
146	* @throws SpreadsheetException
147	*/
148	public function getCoordinate(): string
149	{
150	$parent = $this->parent;
151	if ($parent !== null) {
152	$coordinate = $parent->getCurrentCoordinate();
153	} else {
154	$coordinate = null;
155	}
156	if ($coordinate === null) {
157	throw new SpreadsheetException('Coordinate no longer exists');
158	}
159
160	return $coordinate;
161	}
162	/**
163	* Get cell value.
164	* @return mixed
165	*/
166	public function getValue()
167	{
168	return $this->value;
169	}
170	public function getValueString(): string
171	{
172	return StringHelper::convertToString($this->value, false);
173	}
174	/**
175	* Get cell value with formatting.
176	*/
177	public function getFormattedValue(): string
178	{
179	$currentCalendar = SharedDate::getExcelCalendar();
180	SharedDate::setExcelCalendar(
181	($nullsafeVariable2 = $this->getWorksheet()
182	->getParent()) ? $nullsafeVariable2->getExcelCalendar() : null
183	);
184
185	try {
186	$formattedValue = NumberFormat::toFormattedString(
187	$this->getCalculatedValueString(),
188	(string) $this->getStyle()
189	->getNumberFormat()
190	->getFormatCode(true)
191	);
192	} finally {
193	SharedDate::setExcelCalendar($currentCalendar);
194	}
195
196	return $formattedValue;
197	}
198	/**
199	* @param mixed $oldValue
200	* @param mixed $newValue
201	*/
202	protected static function updateIfCellIsTableHeader(?Worksheet $workSheet, self $cell, $oldValue, $newValue): void
203	{
204	$oldValue = StringHelper::convertToString($oldValue, false);
205	$newValue = StringHelper::convertToString($newValue, false);
206	if (StringHelper::strToLower($oldValue) === StringHelper::strToLower($newValue) \|\| $workSheet === null) {
207	return;
208	}
209
210	foreach ($workSheet->getTableCollection() as $table) {
211	/** @var Table $table */
212	if ($cell->isInRange($table->getRange())) {
213	$rangeRowsColumns = Coordinate::getRangeBoundaries($table->getRange());
214	if ($cell->getRow() === (int) $rangeRowsColumns[0][1]) {
215	Table\Column::updateStructuredReferences($workSheet, $oldValue, $newValue);
216	}
217
218	return;
219	}
220	}
221	}
222	/**
223	* Set cell value.
224	*
225	* Sets the value for a cell, automatically determining the datatype using the value binder
226	*
227	* @param mixed $value Value
228	* @param null\|IValueBinder $binder Value Binder to override the currently set Value Binder
229	*
230	* @throws SpreadsheetException
231	*/
232	public function setValue($value, ?IValueBinder $binder = null): self
233	{
234	if ($this->hadHyperlink) {
235	$this->clearHyperlink();
236	}
237	// Cells?->Worksheet?->Spreadsheet
238	$binder ??= (($nullsafeVariable3 = ($nullsafeVariable13 = ($nullsafeVariable17 = $this->parent) ? $nullsafeVariable17->getParent() : null) ? $nullsafeVariable13->getParent() : null) ? $nullsafeVariable3->getValueBinder() : null) ?? self::getValueBinder();
239	if (!$binder->bindValue($this, $value)) {
240	throw new SpreadsheetException('Value could not be bound to cell.');
241	}
242
243	return $this;
244	}
245	private bool $hadHyperlink = false;
246	/** @internal */
247	public function setHadHyperlink(bool $hadHyperlink): void
248	{
249	$this->hadHyperlink = $hadHyperlink;
250	}
251	private function clearHyperlink(): void
252	{
253	$worksheet = $this->getWorksheetOrNull();
254	if ($worksheet !== null) {
255	$coordinate = $this->getCoordinate();
256	$worksheet->setHyperlink($coordinate, null);
257	}
258	$this->hadHyperlink = false;
259	}
260	/**
261	* Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder).
262	*
263	* @param mixed $value Value
264	* @param string $dataType Explicit data type, see DataType::TYPE_*
265	* This parameter is currently optional (default = string).
266	* Omitting it is *DEPRECATED*, and the default will be removed in a future release.
267	* Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
268	* method, then it is your responsibility as an end-user developer to validate that the value and
269	* the datatype match.
270	* If you do mismatch value and datatype, then the value you enter may be changed to match the datatype
271	* that you specify.
272	*
273	* @throws SpreadsheetException
274	*/
275	public function setValueExplicit($value, string $dataType = DataType::TYPE_STRING): self
276	{
277	if ($this->hadHyperlink) {
278	$this->clearHyperlink();
279	}
280	$oldValue = $this->value;
281	$quotePrefix = false;
282
283	// set the value according to data type
284	switch ($dataType) {
285	case DataType::TYPE_NULL:
286	$this->value = null;
287
288	break;
289	case DataType::TYPE_STRING2:
290	$dataType = DataType::TYPE_STRING;
291	// no break
292	case DataType::TYPE_STRING:
293	// Synonym for string
294	if (is_string($value) && strlen($value) > 1 && $value[0] === '=') {
295	$quotePrefix = true;
296	}
297	// no break
298	case DataType::TYPE_INLINE:
299	// Rich text
300	$value2 = StringHelper::convertToString($value, true);
301	// Cells?->Worksheet?->Spreadsheet
302	$binder = ($nullsafeVariable4 = ($nullsafeVariable5 = ($nullsafeVariable14 = $this->parent) ? $nullsafeVariable14->getParent() : null) ? $nullsafeVariable5->getParent() : null) ? $nullsafeVariable4->getValueBinder() : null;
303	$preserveCr = false;
304	if ($binder !== null && method_exists($binder, 'getPreserveCr')) {
305	/** @var bool */
306	$preserveCr = $binder->getPreserveCr();
307	}
308	$this->value = DataType::checkString(($value instanceof RichText) ? $value : $value2, $preserveCr);
309
310	break;
311	case DataType::TYPE_NUMERIC:
312	if ($value !== null && !is_bool($value) && !is_numeric($value)) {
313	throw new SpreadsheetException('Invalid numeric value for datatype Numeric');
314	}
315	$this->value = 0 + $value;
316
317	break;
318	case DataType::TYPE_FORMULA:
319	$this->value = StringHelper::convertToString($value, true);
320
321	break;
322	case DataType::TYPE_BOOL:
323	$this->value = (bool) $value;
324
325	break;
326	case DataType::TYPE_ISO_DATE:
327	$this->value = SharedDate::convertIsoDate($value);
328	$dataType = DataType::TYPE_NUMERIC;
329
330	break;
331	case DataType::TYPE_DRAWING_IN_CELL:
332	if ($value instanceof BaseDrawing) {
333	$this->value = $value;
334	} else {
335	throw new SpreadsheetException('Item is not a drawing');
336	}
337
338	break;
339	case DataType::TYPE_ERROR:
340	$this->value = DataType::checkErrorCode($value);
341
342	break;
343	default:
344	throw new SpreadsheetException('Invalid datatype: ' . $dataType);
345	}
346
347	// set the datatype
348	$this->dataType = $dataType;
349
350	$this->updateInCollection();
351	$cellCoordinate = $this->getCoordinate();
352	self::updateIfCellIsTableHeader(($nullsafeVariable6 = $this->getParent()) ? $nullsafeVariable6->getParent() : null, $this, $oldValue, $value);
353	$worksheet = $this->getWorksheet();
354	$spreadsheet = $worksheet->getParent();
355	if (isset($spreadsheet) && $spreadsheet->getIndex($worksheet, true) >= 0) {
356	// Avoid Worksheet::getStyle() (selection + validation) unless quotePrefix must change.
357	$oldQuotePrefix = $spreadsheet->getCellXfByIndex($this->getXfIndex())->getQuotePrefix();
358	if ($oldQuotePrefix !== $quotePrefix) {
359	$originalSelected = $worksheet->getSelectedCells();
360	$activeSheetIndex = $spreadsheet->getActiveSheetIndex();
361	$this->getStyle()->setQuotePrefix($quotePrefix);
362	$worksheet->setSelectedCells($originalSelected);
363	if ($activeSheetIndex >= 0) {
364	$spreadsheet->setActiveSheetIndex($activeSheetIndex);
365	}
366	}
367	}
368
369	return (($nullsafeVariable7 = $this->getParent()) ? $nullsafeVariable7->get($cellCoordinate) : null) ?? $this;
370	}
371	public const CALCULATE_DATE_TIME_ASIS = 0;
372	public const CALCULATE_DATE_TIME_FLOAT = 1;
373	public const CALCULATE_TIME_FLOAT = 2;
374	private static int $calculateDateTimeType = self::CALCULATE_DATE_TIME_ASIS;
375	public static function getCalculateDateTimeType(): int
376	{
377	return self::$calculateDateTimeType;
378	}
379	/** @throws CalculationException */
380	public static function setCalculateDateTimeType(int $calculateDateTimeType): void
381	{
382	switch ($calculateDateTimeType) {
383	case self::CALCULATE_DATE_TIME_ASIS:
384	case self::CALCULATE_DATE_TIME_FLOAT:
385	case self::CALCULATE_TIME_FLOAT:
386	self::$calculateDateTimeType = $calculateDateTimeType;
387	break;
388	default:
389	throw new CalculationException("Invalid value $calculateDateTimeType for calculated date time type");
390	}
391	}
392	/**
393	* Convert date, time, or datetime from int to float if desired.
394	* @param mixed $result
395	* @return mixed
396	*/
397	private function convertDateTimeInt($result)
398	{
399	if (is_int($result)) {
400	if (self::$calculateDateTimeType === self::CALCULATE_TIME_FLOAT) {
401	if (SharedDate::isDateTime($this, $result, false)) {
402	$result = (float) $result;
403	}
404	} elseif (self::$calculateDateTimeType === self::CALCULATE_DATE_TIME_FLOAT) {
405	if (SharedDate::isDateTime($this, $result, true)) {
406	$result = (float) $result;
407	}
408	}
409	}
410
411	return $result;
412	}
413	/**
414	* Get calculated cell value converted to string.
415	*/
416	public function getCalculatedValueString(): string
417	{
418	$value = $this->getCalculatedValue();
419	while (is_array($value)) {
420	$value = array_shift($value);
421	}
422
423	return StringHelper::convertToString($value, false, '', true);
424	}
425	/**
426	* Get calculated cell value.
427	*
428	* @param bool $resetLog Whether the calculation engine logger should be reset or not
429	*
430	* @throws CalculationException
431	* @return mixed
432	*/
433	public function getCalculatedValue(bool $resetLog = true)
434	{
435	$title = 'unknown';
436	$oldAttributes = $this->formulaAttributes;
437	$oldAttributesT = $oldAttributes['t'] ?? '';
438	$coordinate = $this->getCoordinate();
439	$oldAttributesRef = $oldAttributes['ref'] ?? $coordinate;
440	$originalValue = $this->value;
441	$originalDataType = $this->dataType;
442	$this->formulaAttributes = [];
443	$spill = false;
444
445	if ($this->dataType === DataType::TYPE_FORMULA) {
446	try {
447	$currentCalendar = SharedDate::getExcelCalendar();
448	SharedDate::setExcelCalendar(($nullsafeVariable8 = $this->getWorksheet()->getParent()) ? $nullsafeVariable8->getExcelCalendar() : null);
449	$thisworksheet = $this->getWorksheet();
450	$index = $thisworksheet->getParentOrThrow()->getActiveSheetIndex();
451	$selected = $thisworksheet->getSelectedCells();
452	$title = $thisworksheet->getTitle();
453	$calculation = Calculation::getInstance($thisworksheet->getParent());
454	$result = $calculation->calculateCellValue($this, $resetLog);
455	$result = $this->convertDateTimeInt($result);
456	$thisworksheet->setSelectedCells($selected);
457	$thisworksheet->getParentOrThrow()->setActiveSheetIndex($index);
458	if (is_array($result) && $calculation->getInstanceArrayReturnType() !== Calculation::RETURN_ARRAY_AS_ARRAY) {
459	while (is_array($result)) {
460	$result = array_shift($result);
461	}
462	}
463	if (
464	!is_array($result)
465	&& $calculation->getInstanceArrayReturnType() === Calculation::RETURN_ARRAY_AS_ARRAY
466	&& $oldAttributesT === 'array'
467	&& ($oldAttributesRef === $coordinate \|\| $oldAttributesRef === "$coordinate:$coordinate")
468	) {
469	$result = [$result];
470	}
471	// if return_as_array for formula like '=sheet!cell'
472	if (is_array($result) && count($result) === 1) {
473	$resultKey = array_keys($result)[0];
474	$resultValue = $result[$resultKey];
475	if (is_int($resultKey) && is_array($resultValue) && count($resultValue) === 1) {
476	$resultKey2 = array_keys($resultValue)[0];
477	$resultValue2 = $resultValue[$resultKey2];
478	if (is_string($resultKey2) && !is_array($resultValue2) && preg_match('/[a-zA-Z]{1,3}/', $resultKey2) === 1) {
479	$result = $resultValue2;
480	}
481	}
482	}
483	$newColumn = $this->getColumn();
484	if (is_array($result)) {
485	$result = self::convertSpecialArray($result);
486	$this->formulaAttributes['t'] = 'array';
487	$this->formulaAttributes['ref'] = $maxCoordinate = $coordinate;
488	$newRow = $row = $this->getRow();
489	$column = $this->getColumn();
490	foreach ($result as $resultRow) {
491	if (is_array($resultRow)) {
492	$newColumn = $column;
493	foreach ($resultRow as $resultValue) {
494	if ($row !== $newRow \|\| $column !== $newColumn) {
495	$maxCoordinate = $newColumn . $newRow;
496	if ($thisworksheet->getCell($newColumn . $newRow)->getValue() !== null) {
497	if (!Coordinate::coordinateIsInsideRange($oldAttributesRef, $newColumn . $newRow)) {
498	$spill = true;
499
500	break;
501	}
502	}
503	}
504	/** @var string $newColumn */
505	StringHelper::stringIncrement($newColumn);
506	}
507	++$newRow;
508	} else {
509	if ($row !== $newRow \|\| $column !== $newColumn) {
510	$maxCoordinate = $newColumn . $newRow;
511	if ($thisworksheet->getCell($newColumn . $newRow)->getValue() !== null) {
512	if (!Coordinate::coordinateIsInsideRange($oldAttributesRef, $newColumn . $newRow)) {
513	$spill = true;
514	}
515	}
516	}
517	StringHelper::stringIncrement($newColumn);
518	}
519	if ($spill) {
520	break;
521	}
522	}
523	if (!$spill) {
524	$this->formulaAttributes['ref'] .= ":$maxCoordinate";
525	}
526	$thisworksheet->getCell($column . $row);
527	}
528	if (is_array($result)) {
529	if ($oldAttributes !== null && $calculation->getInstanceArrayReturnType() === Calculation::RETURN_ARRAY_AS_ARRAY) {
530	if (($oldAttributesT) === 'array') {
531	$thisworksheet = $this->getWorksheet();
532	$coordinate = $this->getCoordinate();
533	$ref = $oldAttributesRef;
534	if (preg_match('/^([A-Z]{1,3})([0-9]{1,7})(:([A-Z]{1,3})([0-9]{1,7}))?$/', $ref, $matches) === 1) {
535	if (isset($matches[5])) {
536	$minCol = $matches[1];
537	$minRow = (int) $matches[2];
538	$maxCol = $matches[4];
539	StringHelper::stringIncrement($maxCol);
540	$maxRow = (int) $matches[5];
541	for ($row = $minRow; $row <= $maxRow; ++$row) {
542	for ($col = $minCol; $col !== $maxCol; StringHelper::stringIncrement($col)) {
543	/** @var string $col */
544	if ("$col$row" !== $coordinate) {
545	$thisworksheet->getCell("$col$row")->setValue(null);
546	}
547	}
548	}
549	}
550	}
551	$thisworksheet->getCell($coordinate);
552	}
553	}
554	}
555	if ($spill) {
556	$result = ExcelError::SPILL();
557	}
558	if (is_array($result)) {
559	$newRow = $row = $this->getRow();
560	$newColumn = $column = $this->getColumn();
561	foreach ($result as $resultRow) {
562	if (is_array($resultRow)) {
563	$newColumn = $column;
564	foreach ($resultRow as $resultValue) {
565	if ($row !== $newRow \|\| $column !== $newColumn) {
566	$thisworksheet
567	->getCell($newColumn . $newRow)
568	->setValue($resultValue);
569	}
570	StringHelper::stringIncrement($newColumn);
571	}
572	++$newRow;
573	} else {
574	if ($row !== $newRow \|\| $column !== $newColumn) {
575	$thisworksheet->getCell($newColumn . $newRow)->setValue($resultRow);
576	}
577	StringHelper::stringIncrement($newColumn);
578	}
579	}
580	$thisworksheet->getCell($column . $row);
581	$this->value = $originalValue;
582	$this->dataType = $originalDataType;
583	}
584	} catch (SpreadsheetException $ex) {
585	SharedDate::setExcelCalendar($currentCalendar);
586	if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
587	return $this->calculatedValue; // Fallback for calculations referencing external files.
588	} elseif (preg_match('/[Uu]ndefined (name\|offset: 2\|array key 2)/', $ex->getMessage()) === 1) {
589	return ExcelError::NAME();
590	}
591
592	throw new CalculationException(
593	$title . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage(),
594	$ex->getCode(),
595	$ex
596	);
597	}
598	SharedDate::setExcelCalendar($currentCalendar);
599
600	if ($result === Functions::NOT_YET_IMPLEMENTED) {
601	$this->formulaAttributes = $oldAttributes;
602
603	return $this->calculatedValue; // Fallback if calculation engine does not support the formula.
604	}
605
606	return $result;
607	} elseif ($this->value instanceof RichText) {
608	return $this->value->getPlainText();
609	}
610
611	return $this->convertDateTimeInt($this->value);
612	}
613	/**
614	* Convert array like the following (preserve values, lose indexes):
615	* [
616	* rowNumber1 => [colLetter1 => value, colLetter2 => value ...],
617	* rowNumber2 => [colLetter1 => value, colLetter2 => value ...],
618	* ...
619	* ].
620	*
621	* @param mixed[] $array
622	*
623	* @return mixed[]
624	*/
625	private static function convertSpecialArray(array $array): array
626	{
627	$newArray = [];
628	foreach ($array as $rowIndex => $row) {
629	if (!is_int($rowIndex) \|\| $rowIndex <= 0 \|\| !is_array($row)) {
630	return $array;
631	}
632	$keys = array_keys($row);
633	$key0 = $keys[0] ?? '';
634	if (!is_string($key0)) {
635	return $array;
636	}
637	$newArray[] = array_values($row);
638	}
639
640	return $newArray;
641	}
642	/**
643	* Set old calculated value (cached).
644	*
645	* @param mixed $originalValue Value
646	*/
647	public function setCalculatedValue($originalValue, bool $tryNumeric = true): self
648	{
649	if ($originalValue !== null) {
650	$this->calculatedValue = ($tryNumeric && is_numeric($originalValue)) ? (0 + $originalValue) : $originalValue;
651	}
652
653	return $this->updateInCollection();
654	}
655	/**
656	* Get old calculated value (cached)
657	* This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
658	* create the original spreadsheet file.
659	* Note that this value is not guaranteed to reflect the actual calculated value because it is
660	* possible that auto-calculation was disabled in the original spreadsheet, and underlying data
661	* values used by the formula have changed since it was last calculated.
662	* @return mixed
663	*/
664	public function getOldCalculatedValue()
665	{
666	return $this->calculatedValue;
667	}
668	/**
669	* Get cell data type.
670	*/
671	public function getDataType(): string
672	{
673	return $this->dataType;
674	}
675	/**
676	* Set cell data type.
677	*
678	* @param string $dataType see DataType::TYPE_*
679	*/
680	public function setDataType(string $dataType): self
681	{
682	$this->setValueExplicit($this->value, $dataType);
683
684	return $this;
685	}
686	/**
687	* Identify if the cell contains a formula.
688	*/
689	public function isFormula(): bool
690	{
691	return $this->dataType === DataType::TYPE_FORMULA && $this->getStyle()->getQuotePrefix() === false;
692	}
693	/**
694	* Does this cell contain Data validation rules?
695	*
696	* @throws SpreadsheetException
697	*/
698	public function hasDataValidation(): bool
699	{
700	if (!isset($this->parent)) {
701	throw new SpreadsheetException('Cannot check for data validation when cell is not bound to a worksheet');
702	}
703
704	return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
705	}
706	/**
707	* Get Data validation rules.
708	*
709	* @throws SpreadsheetException
710	*/
711	public function getDataValidation(): DataValidation
712	{
713	if (!isset($this->parent)) {
714	throw new SpreadsheetException('Cannot get data validation for cell that is not bound to a worksheet');
715	}
716
717	return $this->getWorksheet()->getDataValidation($this->getCoordinate());
718	}
719	/**
720	* Set Data validation rules.
721	*
722	* @throws SpreadsheetException
723	*/
724	public function setDataValidation(?DataValidation $dataValidation = null): self
725	{
726	if (!isset($this->parent)) {
727	throw new SpreadsheetException('Cannot set data validation for cell that is not bound to a worksheet');
728	}
729
730	$this->getWorksheet()->setDataValidation($this->getCoordinate(), $dataValidation);
731
732	return $this->updateInCollection();
733	}
734	/**
735	* Does this cell contain valid value?
736	*/
737	public function hasValidValue(): bool
738	{
739	$validator = new DataValidator();
740
741	return $validator->isValid($this);
742	}
743	/**
744	* Does this cell contain a Hyperlink?
745	*
746	* @throws SpreadsheetException
747	*/
748	public function hasHyperlink(): bool
749	{
750	if (!isset($this->parent)) {
751	throw new SpreadsheetException('Cannot check for hyperlink when cell is not bound to a worksheet');
752	}
753
754	return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
755	}
756	/**
757	* Get Hyperlink.
758	*
759	* @throws SpreadsheetException
760	*/
761	public function getHyperlink(): Hyperlink
762	{
763	if (!isset($this->parent)) {
764	throw new SpreadsheetException('Cannot get hyperlink for cell that is not bound to a worksheet');
765	}
766
767	return $this->getWorksheet()
768	->getHyperlink($this->getCoordinate());
769	}
770	/**
771	* Set Hyperlink.
772	*
773	* @throws SpreadsheetException
774	*/
775	public function setHyperlink(?Hyperlink $hyperlink = null): self
776	{
777	if (!isset($this->parent)) {
778	throw new SpreadsheetException('Cannot set hyperlink for cell that is not bound to a worksheet');
779	}
780
781	$this->getWorksheet()
782	->setHyperlink($this->getCoordinate(), $hyperlink);
783
784	return $this->updateInCollection();
785	}
786	/**
787	* Get cell collection.
788	*/
789	public function getParent(): ?Cells
790	{
791	return $this->parent;
792	}
793	/**
794	* Get parent worksheet.
795	*
796	* @throws SpreadsheetException
797	*/
798	public function getWorksheet(): Worksheet
799	{
800	$parent = $this->parent;
801	if ($parent !== null) {
802	$worksheet = $parent->getParent();
803	} else {
804	$worksheet = null;
805	}
806
807	if ($worksheet === null) {
808	throw new SpreadsheetException('Worksheet no longer exists');
809	}
810
811	return $worksheet;
812	}
813	public function getWorksheetOrNull(): ?Worksheet
814	{
815	$parent = $this->parent;
816	if ($parent !== null) {
817	$worksheet = $parent->getParent();
818	} else {
819	$worksheet = null;
820	}
821
822	return $worksheet;
823	}
824	/**
825	* Is this cell in a merge range.
826	*/
827	public function isInMergeRange(): bool
828	{
829	return (bool) $this->getMergeRange();
830	}
831	/**
832	* Is this cell the master (top left cell) in a merge range (that holds the actual data value).
833	*/
834	public function isMergeRangeValueCell(): bool
835	{
836	if ($mergeRange = $this->getMergeRange()) {
837	$mergeRange = Coordinate::splitRange($mergeRange);
838	[$startCell] = $mergeRange[0];
839
840	return $this->getCoordinate() === $startCell;
841	}
842
843	return false;
844	}
845	/**
846	* If this cell is in a merge range, then return the range.
847	*
848	* @return false\|string
849	*/
850	public function getMergeRange()
851	{
852	foreach ($this->getWorksheet()->getMergeCells() as $mergeRange) {
853	if ($this->isInRange($mergeRange)) {
854	return $mergeRange;
855	}
856	}
857
858	return false;
859	}
860	/**
861	* Get cell style.
862	*/
863	public function getStyle(): Style
864	{
865	return $this->getWorksheet()->getStyle($this->getCoordinate());
866	}
867	/**
868	* Get cell style.
869	*/
870	public function getAppliedStyle(): Style
871	{
872	if ($this->getWorksheet()->conditionalStylesExists($this->getCoordinate()) === false) {
873	return $this->getStyle();
874	}
875	$range = $this->getWorksheet()->getConditionalRange($this->getCoordinate());
876	if ($range === null) {
877	return $this->getStyle();
878	}
879
880	$matcher = new CellStyleAssessor($this, $range);
881
882	return $matcher->matchConditions($this->getWorksheet()->getConditionalStyles($this->getCoordinate()));
883	}
884	/**
885	* Re-bind parent.
886	*/
887	public function rebindParent(Worksheet $parent): self
888	{
889	$this->parent = $parent->getCellCollection();
890
891	return $this->updateInCollection();
892	}
893	/**
894	* Is cell in a specific range?
895	*
896	* @param string $range Cell range (e.g. A1:A1)
897	*/
898	public function isInRange(string $range): bool
899	{
900	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);
901
902	// Translate properties
903	$myColumn = Coordinate::columnIndexFromString($this->getColumn());
904	$myRow = $this->getRow();
905
906	// Verify if cell is in range
907	return ($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn)
908	&& ($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow);
909	}
910	/**
911	* Compare 2 cells.
912	*
913	* @param Cell $a Cell a
914	* @param Cell $b Cell b
915	*
916	* @return int Result of comparison (always -1 or 1, never zero!)
917	*/
918	public static function compareCells(self $a, self $b): int
919	{
920	if ($a->getRow() < $b->getRow()) {
921	return -1;
922	} elseif ($a->getRow() > $b->getRow()) {
923	return 1;
924	} elseif (Coordinate::columnIndexFromString($a->getColumn()) < Coordinate::columnIndexFromString($b->getColumn())) {
925	return -1;
926	}
927
928	return 1;
929	}
930	/**
931	* Get value binder to use.
932	*/
933	public static function getValueBinder(): IValueBinder
934	{
935	if (self::$valueBinder === null) {
936	self::$valueBinder = new DefaultValueBinder();
937	}
938
939	return self::$valueBinder;
940	}
941	/**
942	* Set value binder to use.
943	*/
944	public static function setValueBinder(IValueBinder $binder): void
945	{
946	self::$valueBinder = $binder;
947	}
948	/**
949	* Implement PHP __clone to create a deep clone, not just a shallow copy.
950	*/
951	public function __clone()
952	{
953	$vars = get_object_vars($this);
954	foreach ($vars as $propertyName => $propertyValue) {
955	if ((is_object($propertyValue)) && ($propertyName !== 'parent')) {
956	$this->$propertyName = clone $propertyValue;
957	} else {
958	$this->$propertyName = $propertyValue;
959	}
960	}
961	}
962	/**
963	* Get index to cellXf.
964	*/
965	public function getXfIndex(): int
966	{
967	return $this->xfIndex;
968	}
969	/**
970	* Set index to cellXf.
971	*/
972	public function setXfIndex(int $indexValue): self
973	{
974	$this->xfIndex = $indexValue;
975
976	return $this->updateInCollection();
977	}
978	/**
979	* Set the XF index without triggering updateInCollection().
980	*
981	* This is intended for use by readers that will immediately follow with
982	* setValueExplicit(), avoiding a redundant cache write.
983	*
984	* @internal
985	*/
986	public function setXfIndexNoUpdate(int $indexValue): void
987	{
988	$this->xfIndex = $indexValue;
989	}
990	/**
991	* Set the formula attributes.
992	*
993	* @param null\|array<string, string> $attributes
994	*/
995	public function setFormulaAttributes(?array $attributes): self
996	{
997	$this->formulaAttributes = $attributes;
998
999	return $this;
1000	}
1001	/**
1002	* Get the formula attributes.
1003	*
1004	* @return null\|array<string, string>
1005	*/
1006	public function getFormulaAttributes()
1007	{
1008	return $this->formulaAttributes;
1009	}
1010	/**
1011	* Convert to string.
1012	*/
1013	public function __toString(): string
1014	{
1015	$retVal = $this->value;
1016
1017	return StringHelper::convertToString($retVal, false);
1018	}
1019	public function getIgnoredErrors(): IgnoredErrors
1020	{
1021	return $this->ignoredErrors;
1022	}
1023	public function isLocked(): bool
1024	{
1025	$protected = ($nullsafeVariable9 = ($nullsafeVariable10 = ($nullsafeVariable15 = $this->parent) ? $nullsafeVariable15->getParent() : null) ? $nullsafeVariable10->getProtection() : null) ? $nullsafeVariable9->getSheet() : null;
1026	if ($protected !== true) {
1027	return false;
1028	}
1029	$locked = $this->getStyle()->getProtection()->getLocked();
1030
1031	return $locked !== Protection::PROTECTION_UNPROTECTED;
1032	}
1033	public function isHiddenOnFormulaBar(): bool
1034	{
1035	if ($this->getDataType() !== DataType::TYPE_FORMULA) {
1036	return false;
1037	}
1038	$protected = ($nullsafeVariable11 = ($nullsafeVariable12 = ($nullsafeVariable16 = $this->parent) ? $nullsafeVariable16->getParent() : null) ? $nullsafeVariable12->getProtection() : null) ? $nullsafeVariable11->getSheet() : null;
1039	if ($protected !== true) {
1040	return false;
1041	}
1042	$hidden = $this->getStyle()->getProtection()->getHidden();
1043
1044	return $hidden !== Protection::PROTECTION_UNPROTECTED;
1045	}
1046	/**
1047	* Return cell $right positions to the right of this one.
1048	*/
1049	public function cursorRight(int $right = 1): self
1050	{
1051	$row = $this->getRow();
1052	$col = $this->getColumn();
1053	$colIndex = Coordinate::columnIndexFromString($col);
1054	$newCol = max(
1055	1,
1056	min($colIndex + $right, AddressRange::MAX_COLUMN_INT)
1057	);
1058	$newColStr = Coordinate::stringFromColumnIndex($newCol);
1059
1060	return $this->getWorksheet()->getCell("$newColStr$row");
1061	}
1062	/**
1063	* Return cell $left positions to the left of this one.
1064	*/
1065	public function cursorLeft(int $left = 1): self
1066	{
1067	return $this->cursorRight(-$left);
1068	}
1069	/**
1070	* Return cell $down positions below this one.
1071	*/
1072	public function cursorDown(int $down = 1): self
1073	{
1074	$row = $this->getRow();
1075	$col = $this->getColumn();
1076	$newRow = max(
1077	1,
1078	min($row + $down, AddressRange::MAX_ROW)
1079	);
1080
1081	return $this->getWorksheet()->getCell("$col$newRow");
1082	}
1083	/**
1084	* Return cell $up positions above this one.
1085	*/
1086	public function cursorUp(int $up = 1): self
1087	{
1088	return $this->cursorDown(-$up);
1089	}
1090	/**
1091	* Return cell at row $row in current column.
1092	*/
1093	public function cursorRow(int $row = 1): self
1094	{
1095	$col = $this->getColumn();
1096	$newRow = max(
1097	1,
1098	min($row, AddressRange::MAX_ROW)
1099	);
1100
1101	return $this->getWorksheet()->getCell("$col$newRow");
1102	}
1103	/**
1104	* Return cell at column $column in current row.
1105	*/
1106	public function cursorColumn(string $column = 'A'): self
1107	{
1108	$row = $this->getRow();
1109	$colIndex = Coordinate::columnIndexFromString($column);
1110	$newCol = max(
1111	1,
1112	min($colIndex, AddressRange::MAX_COLUMN_INT)
1113	);
1114	$newColStr = Coordinate::stringFromColumnIndex($newCol);
1115
1116	return $this->getWorksheet()->getCell("$newColStr$row");
1117	}
1118	/**
1119	* Return cell adjusted for Xls limits if applicable.
1120	*/
1121	public function cursorXlsLimits(): self
1122	{
1123	$row = min($this->getRow(), AddressRange::MAX_ROW_XLS);
1124	$column = $this->getColumn();
1125	$colIndex = Coordinate::columnIndexFromString($column);
1126	$newCol = min($colIndex, AddressRange::MAX_COLUMN_INT_XLS);
1127	$newColStr = Coordinate::stringFromColumnIndex($newCol);
1128
1129	return $this->getWorksheet()->getCell("$newColStr$row");
1130	}
1131	}
1132