tablepress / libraries / vendor / PhpSpreadsheet / Cell / AddressHelper.php

AddressHelper.php in TablePress – Tables in WordPress made easy 3.3.4, at libraries/vendor/PhpSpreadsheet/Cell/AddressHelper.php

176 lines 5.7 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Cell;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use TablePress\PhpOffice\PhpSpreadsheet\Exception;
8
9	class AddressHelper
10	{
11	public const R1C1_COORDINATE_REGEX = '/(R((?:\[-?\d\])\|(?:\d))?)(C((?:\[-?\d\])\|(?:\d))?)/i';
12
13	/** @return string[] */
14	public static function getRowAndColumnChars(): array
15	{
16	$rowChar = 'R';
17	$colChar = 'C';
18	if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_EXCEL) {
19	$rowColChars = Calculation::localeFunc('*RC');
20	if (mb_strlen($rowColChars) === 2) {
21	$rowChar = mb_substr($rowColChars, 0, 1);
22	$colChar = mb_substr($rowColChars, 1, 1);
23	}
24	}
25
26	return [$rowChar, $colChar];
27	}
28
29	/**
30	* Converts an R1C1 format cell address to an A1 format cell address.
31	*/
32	public static function convertToA1(
33	string $address,
34	int $currentRowNumber = 1,
35	int $currentColumnNumber = 1,
36	bool $useLocale = true
37	): string {
38	[$rowChar, $colChar] = $useLocale ? self::getRowAndColumnChars() : ['R', 'C'];
39	$regex = '/^(' . $rowChar . '(\[?[-+]?\d\]?))(' . $colChar . '(\[?[-+]?\d\]?))$/i';
40	$validityCheck = preg_match($regex, $address, $cellReference);
41
42	if (empty($validityCheck)) {
43	throw new Exception('Invalid R1C1-format Cell Reference');
44	}
45
46	$rowReference = $cellReference[2];
47	// Empty R reference is the current row
48	if ($rowReference === '') {
49	$rowReference = (string) $currentRowNumber;
50	}
51	// Bracketed R references are relative to the current row
52	if ($rowReference[0] === '[') {
53	$rowReference = $currentRowNumber + (int) trim($rowReference, '[]');
54	}
55	$columnReference = $cellReference[4];
56	// Empty C reference is the current column
57	if ($columnReference === '') {
58	$columnReference = (string) $currentColumnNumber;
59	}
60	// Bracketed C references are relative to the current column
61	if (is_string($columnReference) && $columnReference[0] === '[') { // @phpstan-ignore-line
62	$columnReference = $currentColumnNumber + (int) trim($columnReference, '[]');
63	}
64	$columnReference = (int) $columnReference;
65
66	if ($columnReference <= 0 \|\| $rowReference <= 0) {
67	throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
68	}
69	$A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
70
71	return $A1CellReference;
72	}
73
74	protected static function convertSpreadsheetMLFormula(string $formula): string
75	{
76	$formula = (string) substr($formula, 3);
77	$temp = explode('"', $formula);
78	$key = false;
79	foreach ($temp as &$value) {
80	// Only replace in alternate array entries (i.e. non-quoted blocks)
81	$key = $key === false;
82	if ($key) {
83	$value = str_replace(['[.', ':.', ']'], ['', ':', ''], $value);
84	}
85	}
86	unset($value);
87
88	return implode('"', $temp);
89	}
90
91	/**
92	* Converts a formula that uses R1C1/SpreadsheetXML format cell address to an A1 format cell address.
93	*/
94	public static function convertFormulaToA1(
95	string $formula,
96	int $currentRowNumber = 1,
97	int $currentColumnNumber = 1
98	): string {
99	if (str_starts_with($formula, 'of:')) {
100	// We have an old-style SpreadsheetML Formula
101	return self::convertSpreadsheetMLFormula($formula);
102	}
103
104	// Convert R1C1 style references to A1 style references (but only when not quoted)
105	$temp = explode('"', $formula);
106	$key = false;
107	foreach ($temp as &$value) {
108	// Only replace in alternate array entries (i.e. non-quoted blocks)
109	$key = $key === false;
110	if ($key) {
111	preg_match_all(self::R1C1_COORDINATE_REGEX, $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
112	// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
113	// through the formula from left to right. Reversing means that we work right to left.through
114	// the formula
115	$cellReferences = array_reverse($cellReferences);
116	// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
117	// then modify the formula to use that new reference
118	foreach ($cellReferences as $cellReference) {
119	$A1CellReference = self::convertToA1($cellReference[0][0], $currentRowNumber, $currentColumnNumber, false);
120	$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
121	}
122	}
123	}
124	unset($value);
125
126	// Then rebuild the formula string
127	return implode('"', $temp);
128	}
129
130	/**
131	* Converts an A1 format cell address to an R1C1 format cell address.
132	* If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.
133	*/
134	public static function convertToR1C1(
135	string $address,
136	?int $currentRowNumber = null,
137	?int $currentColumnNumber = null
138	): string {
139	if (1 !== preg_match(Coordinate::A1_COORDINATE_REGEX, $address, $cellReference)) {
140	throw new Exception('Invalid A1-format Cell Reference');
141	}
142
143	if ($cellReference['col'][0] === '$') {
144	// Column must be absolute address
145	$currentColumnNumber = null;
146	}
147	$columnId = Coordinate::columnIndexFromString(ltrim($cellReference['col'], '$'));
148
149	if ($cellReference['row'][0] === '$') {
150	// Row must be absolute address
151	$currentRowNumber = null;
152	}
153	$rowId = (int) ltrim($cellReference['row'], '$');
154
155	if ($currentRowNumber !== null) {
156	if ($rowId === $currentRowNumber) {
157	$rowId = '';
158	} else {
159	$rowId = '[' . ($rowId - $currentRowNumber) . ']';
160	}
161	}
162
163	if ($currentColumnNumber !== null) {
164	if ($columnId === $currentColumnNumber) {
165	$columnId = '';
166	} else {
167	$columnId = '[' . ($columnId - $currentColumnNumber) . ']';
168	}
169	}
170
171	$R1C1Address = "R{$rowId}C{$columnId}";
172
173	return $R1C1Address;
174	}
175	}
176