tablepress / libraries / vendor / PhpSpreadsheet / Calculation / LookupRef / Offset.php

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

166 lines 6.4 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
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\Validations;
11	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
12
13	class Offset
14	{
15	/**
16	* OFFSET.
17	*
18	* Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
19	* The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
20	* the number of columns to be returned.
21	*
22	* Excel Function:
23	* =OFFSET(cellAddress, rows, cols, [height], [width])
24	*
25	* @param null\|string $cellAddress The reference from which you want to base the offset.
26	* Reference must refer to a cell or range of adjacent cells;
27	* otherwise, OFFSET returns the #VALUE! error value.
28	* @param int $rows The number of rows, up or down, that you want the upper-left cell to refer to.
29	* Using 5 as the rows argument specifies that the upper-left cell in the
30	* reference is five rows below reference. Rows can be positive (which means
31	* below the starting reference) or negative (which means above the starting
32	* reference).
33	* @param int $columns The number of columns, to the left or right, that you want the upper-left cell
34	* of the result to refer to. Using 5 as the cols argument specifies that the
35	* upper-left cell in the reference is five columns to the right of reference.
36	* Cols can be positive (which means to the right of the starting reference)
37	* or negative (which means to the left of the starting reference).
38	* @param ?int $height The height, in number of rows, that you want the returned reference to be.
39	* Height must be a positive number.
40	* @param ?int $width The width, in number of columns, that you want the returned reference to be.
41	* Width must be a positive number.
42	*
43	* @return array<mixed>\|string An array containing a cell or range of cells, or a string on error
44	*/
45	public static function OFFSET(?string $cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $cell = null)
46	{
47	/** @var int */
48	$rows = Functions::flattenSingleValue($rows);
49	/** @var int */
50	$columns = Functions::flattenSingleValue($columns);
51	/** @var int */
52	$height = Functions::flattenSingleValue($height);
53	/** @var int */
54	$width = Functions::flattenSingleValue($width);
55
56	if ($cellAddress === null \|\| $cellAddress === '') {
57	return ExcelError::VALUE();
58	}
59
60	if (!is_object($cell)) {
61	return ExcelError::REF();
62	}
63	$sheet = ($nullsafeVariable1 = $cell->getParent()) ? $nullsafeVariable1->getParent() : null; // worksheet
64	if ($sheet !== null) {
65	$cellAddress = Validations::definedNameToCoordinate($cellAddress, $sheet);
66	}
67
68	[$cellAddress, $worksheet] = self::extractWorksheet($cellAddress, $cell);
69
70	$startCell = $endCell = $cellAddress;
71	if (strpos($cellAddress, ':')) {
72	[$startCell, $endCell] = explode(':', $cellAddress);
73	}
74	[$startCellColumn, $startCellRow] = Coordinate::indexesFromString($startCell);
75	[, $endCellRow, $endCellColumn] = Coordinate::indexesFromString($endCell);
76
77	$startCellRow += $rows;
78	$startCellColumn += $columns - 1;
79
80	if (($startCellRow <= 0) \|\| ($startCellColumn < 0)) {
81	return ExcelError::REF();
82	}
83
84	$endCellColumn = self::adjustEndCellColumnForWidth($endCellColumn, $width, $startCellColumn, $columns);
85	$startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
86
87	$endCellRow = self::adjustEndCellRowForHeight($height, $startCellRow, $rows, $endCellRow);
88
89	if (($endCellRow <= 0) \|\| ($endCellColumn < 0)) {
90	return ExcelError::REF();
91	}
92	$endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
93
94	$cellAddress = "{$startCellColumn}{$startCellRow}";
95	if (($startCellColumn != $endCellColumn) \|\| ($startCellRow != $endCellRow)) {
96	$cellAddress .= ":{$endCellColumn}{$endCellRow}";
97	}
98
99	return self::extractRequiredCells($worksheet, $cellAddress);
100	}
101
102	/** @return mixed[] */
103	private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress): array
104	{
105	return Calculation::getInstance(($nullsafeVariable2 = $worksheet) ? $nullsafeVariable2->getParent() : null)
106	->extractCellRange($cellAddress, $worksheet, false);
107	}
108
109	/** @return array{string, ?Worksheet} */
110	private static function extractWorksheet(?string $cellAddress, Cell $cell): array
111	{
112	$cellAddress = self::assessCellAddress($cellAddress ?? '', $cell);
113
114	$sheetName = '';
115	if (str_contains($cellAddress, '!')) {
116	[$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true, true);
117	}
118
119	$worksheet = ($sheetName !== '')
120	? $cell->getWorksheet()->getParentOrThrow()->getSheetByName($sheetName)
121	: $cell->getWorksheet();
122
123	return [$cellAddress, $worksheet];
124	}
125
126	private static function assessCellAddress(string $cellAddress, Cell $cell): string
127	{
128	if (preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/mui', $cellAddress) !== false) {
129	$cellAddress = Functions::expandDefinedName($cellAddress, $cell);
130	}
131
132	return $cellAddress;
133	}
134
135	/**
136	* @param null\|object\|scalar $width
137	* @param scalar $columns
138	*/
139	private static function adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns): int
140	{
141	$endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
142	if (($width !== null) && (!is_object($width))) {
143	$endCellColumn = $startCellColumn + (int) $width - 1;
144	} else {
145	$endCellColumn += (int) $columns;
146	}
147
148	return $endCellColumn;
149	}
150
151	/**
152	* @param null\|object\|scalar $height
153	* @param scalar $rows
154	*/
155	private static function adjustEndCellRowForHeight($height, int $startCellRow, $rows, int $endCellRow): int
156	{
157	if (($height !== null) && (!is_object($height))) {
158	$endCellRow = $startCellRow + (int) $height - 1;
159	} else {
160	$endCellRow += (int) $rows;
161	}
162
163	return $endCellRow;
164	}
165	}
166