visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Cell / Cell.php

Cell.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.11.0, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php

699 lines 16.4 KB

Raw Download Zip

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