wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Cell / Cell.php

Cell.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php

797 lines 22.1 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Cell;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
7	use PhpOffice\PhpSpreadsheet\Collection\Cells;
8	use PhpOffice\PhpSpreadsheet\Exception;
9	use PhpOffice\PhpSpreadsheet\RichText\RichText;
10	use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;
11	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
12	use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\CellStyleAssessor;
13	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
14	use PhpOffice\PhpSpreadsheet\Style\Style;
15	use PhpOffice\PhpSpreadsheet\Worksheet\Table;
16	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
17
18	class Cell
19	{
20	/**
21	* Value binder to use.
22	*
23	* @var IValueBinder
24	*/
25	private static $valueBinder;
26
27	/**
28	* Value of the cell.
29	*
30	* @var mixed
31	*/
32	private $value;
33
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	/**
47	* Type of the cell data.
48	*
49	* @var string
50	*/
51	private $dataType;
52
53	/**
54	* The collection of cells that this cell belongs to (i.e. The Cell Collection for the parent Worksheet).
55	*
56	* @var ?Cells
57	*/
58	private $parent;
59
60	/**
61	* Index to the cellXf reference for the styling of this cell.
62	*
63	* @var int
64	*/
65	private $xfIndex = 0;
66
67	/**
68	* Attributes of the formula.
69	*
70	* @var ?array
71	*/
72	private $formulaAttributes;
73
74	/** @var IgnoredErrors */
75	private $ignoredErrors;
76
77	/**
78	* Update the cell into the cell collection.
79	*
80	* @return $this
81	*/
82	public function updateInCollection(): self
83	{
84	$parent = $this->parent;
85	if ($parent === null) {
86	throw new Exception('Cannot update when cell is not bound to a worksheet');
87	}
88	$parent->update($this);
89
90	return $this;
91	}
92
93	public function detach(): void
94	{
95	$this->parent = null;
96	}
97
98	public function attach(Cells $parent): void
99	{
100	$this->parent = $parent;
101	}
102
103	/**
104	* Create a new Cell.
105	*
106	* @param mixed $value
107	*/
108	public function __construct($value, ?string $dataType, Worksheet $worksheet)
109	{
110	// Initialise cell value
111	$this->value = $value;
112
113	// Set worksheet cache
114	$this->parent = $worksheet->getCellCollection();
115
116	// Set datatype?
117	if ($dataType !== null) {
118	if ($dataType == DataType::TYPE_STRING2) {
119	$dataType = DataType::TYPE_STRING;
120	}
121	$this->dataType = $dataType;
122	} elseif (self::getValueBinder()->bindValue($this, $value) === false) {
123	throw new Exception('Value could not be bound to cell.');
124	}
125	$this->ignoredErrors = new IgnoredErrors();
126	}
127
128	/**
129	* Get cell coordinate column.
130	*
131	* @return string
132	*/
133	public function getColumn()
134	{
135	$parent = $this->parent;
136	if ($parent === null) {
137	throw new Exception('Cannot get column when cell is not bound to a worksheet');
138	}
139
140	return $parent->getCurrentColumn();
141	}
142
143	/**
144	* Get cell coordinate row.
145	*
146	* @return int
147	*/
148	public function getRow()
149	{
150	$parent = $this->parent;
151	if ($parent === null) {
152	throw new Exception('Cannot get row when cell is not bound to a worksheet');
153	}
154
155	return $parent->getCurrentRow();
156	}
157
158	/**
159	* Get cell coordinate.
160	*
161	* @return string
162	*/
163	public function getCoordinate()
164	{
165	$parent = $this->parent;
166	if ($parent !== null) {
167	$coordinate = $parent->getCurrentCoordinate();
168	} else {
169	$coordinate = null;
170	}
171	if ($coordinate === null) {
172	throw new Exception('Coordinate no longer exists');
173	}
174
175	return $coordinate;
176	}
177
178	/**
179	* Get cell value.
180	*
181	* @return mixed
182	*/
183	public function getValue()
184	{
185	return $this->value;
186	}
187
188	/**
189	* Get cell value with formatting.
190	*/
191	public function getFormattedValue(): string
192	{
193	return (string) NumberFormat::toFormattedString(
194	$this->getCalculatedValue(),
195	(string) $this->getStyle()->getNumberFormat()->getFormatCode()
196	);
197	}
198
199	/**
200	* @param mixed $oldValue
201	* @param mixed $newValue
202	*/
203	protected static function updateIfCellIsTableHeader(?Worksheet $workSheet, self $cell, $oldValue, $newValue): void
204	{
205	if (StringHelper::strToLower($oldValue ?? '') === StringHelper::strToLower($newValue ?? '') \|\| $workSheet === null) {
206	return;
207	}
208
209	foreach ($workSheet->getTableCollection() as $table) {
210	/** @var Table $table */
211	if ($cell->isInRange($table->getRange())) {
212	$rangeRowsColumns = Coordinate::getRangeBoundaries($table->getRange());
213	if ($cell->getRow() === (int) $rangeRowsColumns[0][1]) {
214	Table\Column::updateStructuredReferences($workSheet, $oldValue, $newValue);
215	}
216
217	return;
218	}
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 Exception
231	*
232	* @return $this
233	*/
234	public function setValue($value, ?IValueBinder $binder = null): self
235	{
236	$binder ??= self::getValueBinder();
237	if (!$binder->bindValue($this, $value)) {
238	throw new Exception('Value could not be bound to cell.');
239	}
240
241	return $this;
242	}
243
244	/**
245	* Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder).
246	*
247	* @param mixed $value Value
248	* @param string $dataType Explicit data type, see DataType::TYPE_*
249	* Note that PhpSpreadsheet does not validate that the value and datatype are consistent, in using this
250	* method, then it is your responsibility as an end-user developer to validate that the value and
251	* the datatype match.
252	* If you do mismatch value and datatype, then the value you enter may be changed to match the datatype
253	* that you specify.
254	*
255	* @return Cell
256	*/
257	public function setValueExplicit($value, string $dataType = DataType::TYPE_STRING)
258	{
259	$oldValue = $this->value;
260
261	// set the value according to data type
262	switch ($dataType) {
263	case DataType::TYPE_NULL:
264	$this->value = null;
265
266	break;
267	case DataType::TYPE_STRING2:
268	$dataType = DataType::TYPE_STRING;
269	// no break
270	case DataType::TYPE_STRING:
271	// Synonym for string
272	case DataType::TYPE_INLINE:
273	// Rich text
274	$this->value = DataType::checkString($value);
275
276	break;
277	case DataType::TYPE_NUMERIC:
278	if (is_string($value) && !is_numeric($value)) {
279	throw new Exception('Invalid numeric value for datatype Numeric');
280	}
281	$this->value = 0 + $value;
282
283	break;
284	case DataType::TYPE_FORMULA:
285	$this->value = (string) $value;
286
287	break;
288	case DataType::TYPE_BOOL:
289	$this->value = (bool) $value;
290
291	break;
292	case DataType::TYPE_ISO_DATE:
293	$this->value = SharedDate::convertIsoDate($value);
294	$dataType = DataType::TYPE_NUMERIC;
295
296	break;
297	case DataType::TYPE_ERROR:
298	$this->value = DataType::checkErrorCode($value);
299
300	break;
301	default:
302	throw new Exception('Invalid datatype: ' . $dataType);
303	}
304
305	// set the datatype
306	$this->dataType = $dataType;
307
308	$this->updateInCollection();
309	$cellCoordinate = $this->getCoordinate();
310	self::updateIfCellIsTableHeader($this->getParent()->getParent(), $this, $oldValue, $value); // @phpstan-ignore-line
311
312	return $this->getParent()->get($cellCoordinate); // @phpstan-ignore-line
313	}
314
315	public const CALCULATE_DATE_TIME_ASIS = 0;
316	public const CALCULATE_DATE_TIME_FLOAT = 1;
317	public const CALCULATE_TIME_FLOAT = 2;
318
319	/** @var int */
320	private static $calculateDateTimeType = self::CALCULATE_DATE_TIME_ASIS;
321
322	public static function getCalculateDateTimeType(): int
323	{
324	return self::$calculateDateTimeType;
325	}
326
327	public static function setCalculateDateTimeType(int $calculateDateTimeType): void
328	{
329	switch ($calculateDateTimeType) {
330	case self::CALCULATE_DATE_TIME_ASIS:
331	case self::CALCULATE_DATE_TIME_FLOAT:
332	case self::CALCULATE_TIME_FLOAT:
333	self::$calculateDateTimeType = $calculateDateTimeType;
334
335	break;
336	default:
337	throw new \PhpOffice\PhpSpreadsheet\Calculation\Exception("Invalid value $calculateDateTimeType for calculated date time type");
338	}
339	}
340
341	/**
342	* Convert date, time, or datetime from int to float if desired.
343	*
344	* @param mixed $result
345	*
346	* @return mixed
347	*/
348	private function convertDateTimeInt($result)
349	{
350	if (is_int($result)) {
351	if (self::$calculateDateTimeType === self::CALCULATE_TIME_FLOAT) {
352	if (SharedDate::isDateTime($this, $result, false)) {
353	$result = (float) $result;
354	}
355	} elseif (self::$calculateDateTimeType === self::CALCULATE_DATE_TIME_FLOAT) {
356	if (SharedDate::isDateTime($this, $result, true)) {
357	$result = (float) $result;
358	}
359	}
360	}
361
362	return $result;
363	}
364
365	/**
366	* Get calculated cell value.
367	*
368	* @param bool $resetLog Whether the calculation engine logger should be reset or not
369	*
370	* @return mixed
371	*/
372	public function getCalculatedValue(bool $resetLog = true)
373	{
374	if ($this->dataType === DataType::TYPE_FORMULA) {
375	try {
376	$index = $this->getWorksheet()->getParentOrThrow()->getActiveSheetIndex();
377	$selected = $this->getWorksheet()->getSelectedCells();
378	$result = Calculation::getInstance(
379	$this->getWorksheet()->getParent()
380	)->calculateCellValue($this, $resetLog);
381	$result = $this->convertDateTimeInt($result);
382	$this->getWorksheet()->setSelectedCells($selected);
383	$this->getWorksheet()->getParentOrThrow()->setActiveSheetIndex($index);
384	// We don't yet handle array returns
385	if (is_array($result)) {
386	while (is_array($result)) {
387	$result = array_shift($result);
388	}
389	}
390	} catch (Exception $ex) {
391	if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
392	return $this->calculatedValue; // Fallback for calculations referencing external files.
393	} elseif (preg_match('/[Uu]ndefined (name\|offset: 2\|array key 2)/', $ex->getMessage()) === 1) {
394	return ExcelError::NAME();
395	}
396
397	throw new \PhpOffice\PhpSpreadsheet\Calculation\Exception(
398	$this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage(),
399	$ex->getCode(),
400	$ex
401	);
402	}
403
404	if ($result === '#Not Yet Implemented') {
405	return $this->calculatedValue; // Fallback if calculation engine does not support the formula.
406	}
407
408	return $result;
409	} elseif ($this->value instanceof RichText) {
410	return $this->value->getPlainText();
411	}
412
413	return $this->convertDateTimeInt($this->value);
414	}
415
416	/**
417	* Set old calculated value (cached).
418	*
419	* @param mixed $originalValue Value
420	*/
421	public function setCalculatedValue($originalValue): self
422	{
423	if ($originalValue !== null) {
424	$this->calculatedValue = (is_numeric($originalValue)) ? (float) $originalValue : $originalValue;
425	}
426
427	return $this->updateInCollection();
428	}
429
430	/**
431	* Get old calculated value (cached)
432	* This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
433	* create the original spreadsheet file.
434	* Note that this value is not guaranteed to reflect the actual calculated value because it is
435	* possible that auto-calculation was disabled in the original spreadsheet, and underlying data
436	* values used by the formula have changed since it was last calculated.
437	*
438	* @return mixed
439	*/
440	public function getOldCalculatedValue()
441	{
442	return $this->calculatedValue;
443	}
444
445	/**
446	* Get cell data type.
447	*/
448	public function getDataType(): string
449	{
450	return $this->dataType;
451	}
452
453	/**
454	* Set cell data type.
455	*
456	* @param string $dataType see DataType::TYPE_*
457	*/
458	public function setDataType($dataType): self
459	{
460	if ($dataType == DataType::TYPE_STRING2) {
461	$dataType = DataType::TYPE_STRING;
462	}
463	$this->dataType = $dataType;
464
465	return $this->updateInCollection();
466	}
467
468	/**
469	* Identify if the cell contains a formula.
470	*/
471	public function isFormula(): bool
472	{
473	return $this->dataType === DataType::TYPE_FORMULA && $this->getStyle()->getQuotePrefix() === false;
474	}
475
476	/**
477	* Does this cell contain Data validation rules?
478	*/
479	public function hasDataValidation(): bool
480	{
481	if (!isset($this->parent)) {
482	throw new Exception('Cannot check for data validation when cell is not bound to a worksheet');
483	}
484
485	return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
486	}
487
488	/**
489	* Get Data validation rules.
490	*/
491	public function getDataValidation(): DataValidation
492	{
493	if (!isset($this->parent)) {
494	throw new Exception('Cannot get data validation for cell that is not bound to a worksheet');
495	}
496
497	return $this->getWorksheet()->getDataValidation($this->getCoordinate());
498	}
499
500	/**
501	* Set Data validation rules.
502	*/
503	public function setDataValidation(?DataValidation $dataValidation = null): self
504	{
505	if (!isset($this->parent)) {
506	throw new Exception('Cannot set data validation for cell that is not bound to a worksheet');
507	}
508
509	$this->getWorksheet()->setDataValidation($this->getCoordinate(), $dataValidation);
510
511	return $this->updateInCollection();
512	}
513
514	/**
515	* Does this cell contain valid value?
516	*/
517	public function hasValidValue(): bool
518	{
519	$validator = new DataValidator();
520
521	return $validator->isValid($this);
522	}
523
524	/**
525	* Does this cell contain a Hyperlink?
526	*/
527	public function hasHyperlink(): bool
528	{
529	if (!isset($this->parent)) {
530	throw new Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
531	}
532
533	return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
534	}
535
536	/**
537	* Get Hyperlink.
538	*/
539	public function getHyperlink(): Hyperlink
540	{
541	if (!isset($this->parent)) {
542	throw new Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
543	}
544
545	return $this->getWorksheet()->getHyperlink($this->getCoordinate());
546	}
547
548	/**
549	* Set Hyperlink.
550	*/
551	public function setHyperlink(?Hyperlink $hyperlink = null): self
552	{
553	if (!isset($this->parent)) {
554	throw new Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
555	}
556
557	$this->getWorksheet()->setHyperlink($this->getCoordinate(), $hyperlink);
558
559	return $this->updateInCollection();
560	}
561
562	/**
563	* Get cell collection.
564	*
565	* @return ?Cells
566	*/
567	public function getParent()
568	{
569	return $this->parent;
570	}
571
572	/**
573	* Get parent worksheet.
574	*/
575	public function getWorksheet(): Worksheet
576	{
577	$parent = $this->parent;
578	if ($parent !== null) {
579	$worksheet = $parent->getParent();
580	} else {
581	$worksheet = null;
582	}
583
584	if ($worksheet === null) {
585	throw new Exception('Worksheet no longer exists');
586	}
587
588	return $worksheet;
589	}
590
591	public function getWorksheetOrNull(): ?Worksheet
592	{
593	$parent = $this->parent;
594	if ($parent !== null) {
595	$worksheet = $parent->getParent();
596	} else {
597	$worksheet = null;
598	}
599
600	return $worksheet;
601	}
602
603	/**
604	* Is this cell in a merge range.
605	*/
606	public function isInMergeRange(): bool
607	{
608	return (bool) $this->getMergeRange();
609	}
610
611	/**
612	* Is this cell the master (top left cell) in a merge range (that holds the actual data value).
613	*/
614	public function isMergeRangeValueCell(): bool
615	{
616	if ($mergeRange = $this->getMergeRange()) {
617	$mergeRange = Coordinate::splitRange($mergeRange);
618	[$startCell] = $mergeRange[0];
619
620	return $this->getCoordinate() === $startCell;
621	}
622
623	return false;
624	}
625
626	/**
627	* If this cell is in a merge range, then return the range.
628	*
629	* @return false\|string
630	*/
631	public function getMergeRange()
632	{
633	foreach ($this->getWorksheet()->getMergeCells() as $mergeRange) {
634	if ($this->isInRange($mergeRange)) {
635	return $mergeRange;
636	}
637	}
638
639	return false;
640	}
641
642	/**
643	* Get cell style.
644	*/
645	public function getStyle(): Style
646	{
647	return $this->getWorksheet()->getStyle($this->getCoordinate());
648	}
649
650	/**
651	* Get cell style.
652	*/
653	public function getAppliedStyle(): Style
654	{
655	if ($this->getWorksheet()->conditionalStylesExists($this->getCoordinate()) === false) {
656	return $this->getStyle();
657	}
658	$range = $this->getWorksheet()->getConditionalRange($this->getCoordinate());
659	if ($range === null) {
660	return $this->getStyle();
661	}
662
663	$matcher = new CellStyleAssessor($this, $range);
664
665	return $matcher->matchConditions($this->getWorksheet()->getConditionalStyles($this->getCoordinate()));
666	}
667
668	/**
669	* Re-bind parent.
670	*/
671	public function rebindParent(Worksheet $parent): self
672	{
673	$this->parent = $parent->getCellCollection();
674
675	return $this->updateInCollection();
676	}
677
678	/**
679	* Is cell in a specific range?
680	*
681	* @param string $range Cell range (e.g. A1:A1)
682	*/
683	public function isInRange(string $range): bool
684	{
685	[$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($range);
686
687	// Translate properties
688	$myColumn = Coordinate::columnIndexFromString($this->getColumn());
689	$myRow = $this->getRow();
690
691	// Verify if cell is in range
692	return ($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
693	($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow);
694	}
695
696	/**
697	* Compare 2 cells.
698	*
699	* @param Cell $a Cell a
700	* @param Cell $b Cell b
701	*
702	* @return int Result of comparison (always -1 or 1, never zero!)
703	*/
704	public static function compareCells(self $a, self $b): int
705	{
706	if ($a->getRow() < $b->getRow()) {
707	return -1;
708	} elseif ($a->getRow() > $b->getRow()) {
709	return 1;
710	} elseif (Coordinate::columnIndexFromString($a->getColumn()) < Coordinate::columnIndexFromString($b->getColumn())) {
711	return -1;
712	}
713
714	return 1;
715	}
716
717	/**
718	* Get value binder to use.
719	*/
720	public static function getValueBinder(): IValueBinder
721	{
722	if (self::$valueBinder === null) {
723	self::$valueBinder = new DefaultValueBinder();
724	}
725
726	return self::$valueBinder;
727	}
728
729	/**
730	* Set value binder to use.
731	*/
732	public static function setValueBinder(IValueBinder $binder): void
733	{
734	self::$valueBinder = $binder;
735	}
736
737	/**
738	* Implement PHP __clone to create a deep clone, not just a shallow copy.
739	*/
740	public function __clone()
741	{
742	$vars = get_object_vars($this);
743	foreach ($vars as $propertyName => $propertyValue) {
744	if ((is_object($propertyValue)) && ($propertyName !== 'parent')) {
745	$this->$propertyName = clone $propertyValue;
746	} else {
747	$this->$propertyName = $propertyValue;
748	}
749	}
750	}
751
752	/**
753	* Get index to cellXf.
754	*/
755	public function getXfIndex(): int
756	{
757	return $this->xfIndex;
758	}
759
760	/**
761	* Set index to cellXf.
762	*/
763	public function setXfIndex(int $indexValue): self
764	{
765	$this->xfIndex = $indexValue;
766
767	return $this->updateInCollection();
768	}
769
770	public function setFormulaAttributes(?array $attributes): self
771	{
772	$this->formulaAttributes = $attributes;
773
774	return $this;
775	}
776
777	public function getFormulaAttributes(): ?array
778	{
779	return $this->formulaAttributes;
780	}
781
782	/**
783	* Convert to string.
784	*
785	* @return string
786	*/
787	public function __toString()
788	{
789	return (string) $this->getValue();
790	}
791
792	public function getIgnoredErrors(): IgnoredErrors
793	{
794	return $this->ignoredErrors;
795	}
796	}
797