wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Cell / AddressHelper.php

AddressHelper.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/AddressHelper.php

178 lines 6.6 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Cell;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use 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()
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] === '[') {
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 = 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 (substr($formula, 0, 3) == '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	$validityCheck = preg_match(Coordinate::A1_COORDINATE_REGEX, $address, $cellReference);
140
141	if ($validityCheck === 0) {
142	throw new Exception('Invalid A1-format Cell Reference');
143	}
144
145	if ($cellReference['col'][0] === '$') {
146	// Column must be absolute address
147	$currentColumnNumber = null;
148	}
149	$columnId = Coordinate::columnIndexFromString(ltrim($cellReference['col'], '$'));
150
151	if ($cellReference['row'][0] === '$') {
152	// Row must be absolute address
153	$currentRowNumber = null;
154	}
155	$rowId = (int) ltrim($cellReference['row'], '$');
156
157	if ($currentRowNumber !== null) {
158	if ($rowId === $currentRowNumber) {
159	$rowId = '';
160	} else {
161	$rowId = '[' . ($rowId - $currentRowNumber) . ']';
162	}
163	}
164
165	if ($currentColumnNumber !== null) {
166	if ($columnId === $currentColumnNumber) {
167	$columnId = '';
168	} else {
169	$columnId = '[' . ($columnId - $currentColumnNumber) . ']';
170	}
171	}
172
173	$R1C1Address = "R{$rowId}C{$columnId}";
174
175	return $R1C1Address;
176	}
177	}
178