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

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

240 lines 7.2 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\Information\ErrorValue;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
8	use TablePress\PhpOffice\PhpSpreadsheet\Cell\AddressRange;
9	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
10	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
11	use TablePress\PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;
12	use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
13
14	class RowColumnInformation
15	{
16	/**
17	* Test if cellAddress is null or whitespace string.
18	*
19	* @param null\|mixed[]\|string $cellAddress A reference to a range of cells
20	*/
21	private static function cellAddressNullOrWhitespace($cellAddress): bool
22	{
23	return $cellAddress === null \|\| (!is_array($cellAddress) && trim($cellAddress) === '');
24	}
25
26	private static function cellColumn(?Cell $cell): int
27	{
28	return ($cell !== null) ? Coordinate::columnIndexFromString($cell->getColumn()) : 1;
29	}
30
31	/**
32	* COLUMN.
33	*
34	* Returns the column number of the given cell reference
35	* If the cell reference is a range of cells, COLUMN returns the column numbers of each column
36	* in the reference as a horizontal array.
37	* If cell reference is omitted, and the function is being called through the calculation engine,
38	* then it is assumed to be the reference of the cell in which the COLUMN function appears;
39	* otherwise this function returns 1.
40	*
41	* Excel Function:
42	* =COLUMN([cellAddress])
43	*
44	* @param null\|mixed[]\|string $cellAddress A reference to a range of cells for which you want the column numbers
45	*
46	* @return int\|int[]\|string
47	*/
48	public static function COLUMN($cellAddress = null, ?Cell $cell = null)
49	{
50	if (self::cellAddressNullOrWhitespace($cellAddress)) {
51	return self::cellColumn($cell);
52	}
53
54	if (is_array($cellAddress)) {
55	foreach ($cellAddress as $columnKey => $value) {
56	$columnKey = (string) preg_replace('/[^a-z]/i', '', $columnKey);
57
58	return Coordinate::columnIndexFromString($columnKey);
59	}
60
61	return self::cellColumn($cell);
62	}
63
64	$cellAddress = $cellAddress ?? '';
65	if ($cell != null) {
66	[,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
67	[,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
68	}
69	[, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
70	$cellAddress ??= '';
71
72	if (str_contains($cellAddress, ':')) {
73	[$startAddress, $endAddress] = explode(':', $cellAddress);
74	$startAddress = (string) preg_replace('/[^a-z]/i', '', $startAddress);
75	$endAddress = (string) preg_replace('/[^a-z]/i', '', $endAddress);
76
77	return range(
78	Coordinate::columnIndexFromString($startAddress),
79	Coordinate::columnIndexFromString($endAddress)
80	);
81	}
82
83	$cellAddress = (string) preg_replace('/[^a-z]/i', '', $cellAddress);
84
85	try {
86	return Coordinate::columnIndexFromString($cellAddress);
87	} catch (SpreadsheetException $exception) {
88	return ExcelError::NAME();
89	}
90	}
91
92	/**
93	* COLUMNS.
94	*
95	* Returns the number of columns in an array or reference.
96	*
97	* Excel Function:
98	* =COLUMNS(cellAddress)
99	*
100	* @param null\|mixed[]\|string $cellAddress An array or array formula, or a reference to a range of cells
101	* for which you want the number of columns
102	*
103	* @return int\|string The number of columns in cellAddress, or a string if arguments are invalid
104	*/
105	public static function COLUMNS($cellAddress = null)
106	{
107	if (self::cellAddressNullOrWhitespace($cellAddress)) {
108	return 1;
109	}
110	if (is_string($cellAddress) && ErrorValue::isError($cellAddress, true)) {
111	return $cellAddress;
112	}
113	if (!is_array($cellAddress)) {
114	return ExcelError::VALUE();
115	}
116
117	reset($cellAddress);
118	$isMatrix = (is_numeric(key($cellAddress)));
119	[$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
120
121	if ($isMatrix) {
122	return $rows;
123	}
124
125	return $columns;
126	}
127
128	/**
129	* @return int\|string
130	*/
131	private static function cellRow(?Cell $cell)
132	{
133	return ($cell !== null) ? self::convert0ToName($cell->getRow()) : 1;
134	}
135
136	/**
137	* @param int\|string $result
138	* @return int\|string
139	*/
140	private static function convert0ToName($result)
141	{
142	if (is_int($result) && ($result <= 0 \|\| $result > AddressRange::MAX_ROW)) {
143	return ExcelError::NAME();
144	}
145
146	return $result;
147	}
148
149	/**
150	* ROW.
151	*
152	* Returns the row number of the given cell reference
153	* If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference
154	* as a vertical array.
155	* If cell reference is omitted, and the function is being called through the calculation engine,
156	* then it is assumed to be the reference of the cell in which the ROW function appears;
157	* otherwise this function returns 1.
158	*
159	* Excel Function:
160	* =ROW([cellAddress])
161	*
162	* @param null\|mixed[][]\|string $cellAddress A reference to a range of cells for which you want the row numbers
163	*
164	* @return int\|mixed[]\|string
165	*/
166	public static function ROW($cellAddress = null, ?Cell $cell = null)
167	{
168	if (self::cellAddressNullOrWhitespace($cellAddress)) {
169	return self::cellRow($cell);
170	}
171
172	if (is_array($cellAddress)) {
173	foreach ($cellAddress as $rowKey => $rowValue) {
174	foreach ($rowValue as $columnKey => $cellValue) {
175	return (int) preg_replace('/\D/', '', $rowKey);
176	}
177	}
178
179	return self::cellRow($cell);
180	}
181
182	$cellAddress = $cellAddress ?? '';
183	if ($cell !== null) {
184	[,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
185	[,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
186	}
187	[, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
188	$cellAddress ??= '';
189	if (str_contains($cellAddress, ':')) {
190	[$startAddress, $endAddress] = explode(':', $cellAddress);
191	$startAddress = (int) (string) preg_replace('/\D/', '', $startAddress);
192	$endAddress = (int) (string) preg_replace('/\D/', '', $endAddress);
193
194	return array_map(
195	fn ($value): array => [$value],
196	range($startAddress, $endAddress)
197	);
198	}
199	[$cellAddress] = explode(':', $cellAddress);
200
201	return self::convert0ToName((int) preg_replace('/\D/', '', $cellAddress));
202	}
203
204	/**
205	* ROWS.
206	*
207	* Returns the number of rows in an array or reference.
208	*
209	* Excel Function:
210	* =ROWS(cellAddress)
211	*
212	* @param null\|mixed[]\|string $cellAddress An array or array formula, or a reference to a range of cells
213	* for which you want the number of rows
214	*
215	* @return int\|string The number of rows in cellAddress, or a string if arguments are invalid
216	*/
217	public static function ROWS($cellAddress = null)
218	{
219	if (self::cellAddressNullOrWhitespace($cellAddress)) {
220	return 1;
221	}
222	if (is_string($cellAddress) && ErrorValue::isError($cellAddress, true)) {
223	return $cellAddress;
224	}
225	if (!is_array($cellAddress)) {
226	return ExcelError::VALUE();
227	}
228
229	reset($cellAddress);
230	$isMatrix = (is_numeric(key($cellAddress)));
231	[$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
232
233	if ($isMatrix) {
234	return $columns;
235	}
236
237	return $rows;
238	}
239	}
240