tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Internal / ExcelArrayPseudoFunctions.php

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

108 lines 4.2 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Internal;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
8	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
9	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
10	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
11
12	class ExcelArrayPseudoFunctions
13	{
14	/**
15	* @return mixed
16	*/
17	public static function single(string $cellReference, Cell $cell)
18	{
19	$worksheet = $cell->getWorksheet();
20
21	[$referenceWorksheetName, $referenceCellCoordinate] = Worksheet::extractSheetTitle($cellReference, true, true);
22	if (preg_match('/^([$]?[a-z]{1,3})([$]?([0-9]{1,7})):([$]?[a-z]{1,3})([$]?([0-9]{1,7}))$/i', "$referenceCellCoordinate", $matches) === 1) {
23	$ourRow = $cell->getRow();
24	$firstRow = (int) $matches[3];
25	$lastRow = (int) $matches[6];
26	if ($ourRow < $firstRow \|\| $ourRow > $lastRow \|\| $matches[1] !== $matches[4]) {
27	return ExcelError::VALUE();
28	}
29	$referenceCellCoordinate = $matches[1] . $ourRow;
30	}
31	$referenceCell = ($referenceWorksheetName === '')
32	? $worksheet->getCell((string) $referenceCellCoordinate)
33	: $worksheet->getParentOrThrow()
34	->getSheetByNameOrThrow((string) $referenceWorksheetName)
35	->getCell((string) $referenceCellCoordinate);
36
37	$result = $referenceCell->getCalculatedValue();
38	while (is_array($result)) {
39	$result = array_shift($result);
40	}
41
42	return $result;
43	}
44
45	/** @return array<mixed>\|string */
46	public static function anchorArray(string $cellReference, Cell $cell)
47	{
48	//$coordinate = $cell->getCoordinate();
49	$worksheet = $cell->getWorksheet();
50
51	[$referenceWorksheetName, $referenceCellCoordinate] = Worksheet::extractSheetTitle($cellReference, true, true);
52	$referenceCell = ($referenceWorksheetName === '')
53	? $worksheet->getCell((string) $referenceCellCoordinate)
54	: $worksheet->getParentOrThrow()
55	->getSheetByNameOrThrow((string) $referenceWorksheetName)
56	->getCell((string) $referenceCellCoordinate);
57
58	// We should always use the sizing for the array formula range from the referenced cell formula
59	//$referenceRange = null;
60	/*if ($referenceCell->isFormula() && $referenceCell->isArrayFormula()) {
61	$referenceRange = $referenceCell->arrayFormulaRange();
62	}*/
63
64	$calcEngine = Calculation::getInstance($worksheet->getParent());
65	$result = $calcEngine->calculateCellValue($referenceCell, false);
66	if (!is_array($result)) {
67	$result = ExcelError::REF();
68	}
69
70	// Ensure that our array result dimensions match the specified array formula range dimensions,
71	// from the referenced cell, expanding or shrinking it as necessary.
72	/*$result = Functions::resizeMatrix(
73	$result,
74	...Coordinate::rangeDimension($referenceRange ?? $coordinate)
75	);*/
76
77	// Set the result for our target cell (with spillage)
78	// But if we do write it, we get problems with #SPILL! Errors if the spreadsheet is saved
79	// TODO How are we going to identify and handle a #SPILL! or a #CALC! error?
80	// IOFactory::setLoading(true);
81	// $worksheet->fromArray(
82	// $result,
83	// null,
84	// $coordinate,
85	// true
86	// );
87	// IOFactory::setLoading(true);
88
89	// Calculate the array formula range that we should set for our target, based on our target cell coordinate
90	// [$col, $row] = Coordinate::indexesFromString($coordinate);
91	// $row += count($result) - 1;
92	// $col = Coordinate::stringFromColumnIndex($col + count($result[0]) - 1);
93	// $arrayFormulaRange = "{$coordinate}:{$col}{$row}";
94	// $formulaAttributes = ['t' => 'array', 'ref' => $arrayFormulaRange];
95
96	// Using fromArray() would reset the value for this cell with the calculation result
97	// as well as updating the spillage cells,
98	// so we need to restore this cell to its formula value, attributes, and datatype
99	// $cell = $worksheet->getCell($coordinate);
100	// $cell->setValueExplicit($value, DataType::TYPE_FORMULA, true, $arrayFormulaRange);
101	// $cell->setFormulaAttributes($formulaAttributes);
102
103	// $cell->updateInCollection();
104
105	return $result;
106	}
107	}
108