1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
<?php
namespace PhpOffice\PhpSpreadsheet\Cell;
use PhpOffice\PhpSpreadsheet\Exception;
class AddressHelper
{
/**
* Converts an R1C1 format cell address to an A1 format cell address.
*/
public static function convertToA1(
string $address,
int $currentRowNumber = 1,
int $currentColumnNumber = 1
): string {
$validityCheck = preg_match('/^(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))$/i', $address, $cellReference);
if ($validityCheck === 0) {
throw new Exception('Invalid R1C1-format Cell Reference');
}
$rowReference = $cellReference[2];
// Empty R reference is the current row
if ($rowReference === '') {
$rowReference = (string) $currentRowNumber;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] === '[') {
$rowReference = $currentRowNumber + trim($rowReference, '[]');
}
$columnReference = $cellReference[4];
// Empty C reference is the current column
if ($columnReference === '') {
$columnReference = (string) $currentColumnNumber;
}
// Bracketed C references are relative to the current column
if (is_string($columnReference) && $columnReference[0] === '[') {
$columnReference = $currentColumnNumber + trim($columnReference, '[]');
}
if ($columnReference <= 0 || $rowReference <= 0) {
throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
}
$A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
return $A1CellReference;
}
/**
* Converts a formula that uses R1C1 format cell address to an A1 format cell address.
*/
public static function convertFormulaToA1(
string $formula,
int $currentRowNumber = 1,
int $currentColumnNumber = 1
): string {
if (substr($formula, 0, 3) == 'of:') {
$formula = substr($formula, 3);
$temp = explode('"', $formula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
$value = str_replace(['[.', '.', ']'], '', $value);
}
}
} else {
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $formula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$A1CellReference = self::convertToA1($cellReference[0][0], $currentRowNumber, $currentColumnNumber);
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
}
unset($value);
// Then rebuild the formula string
$formula = implode('"', $temp);
return $formula;
}
/**
* Converts an A1 format cell address to an R1C1 format cell address.
* If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.
*/
public static function convertToR1C1(
string $address,
?int $currentRowNumber = null,
?int $currentColumnNumber = null
): string {
$validityCheck = preg_match('/^\$?([A-Z]{1,3})\$?(\d{1,7})$/i', $address, $cellReference);
if ($validityCheck === 0) {
throw new Exception('Invalid A1-format Cell Reference');
}
$columnId = Coordinate::columnIndexFromString($cellReference[1]);
$rowId = (int) $cellReference[2];
if ($currentRowNumber !== null) {
if ($rowId === $currentRowNumber) {
$rowId = '';
} else {
$rowId = '[' . ($rowId - $currentRowNumber) . ']';
}
}
if ($currentColumnNumber !== null) {
if ($columnId === $currentColumnNumber) {
$columnId = '';
} else {
$columnId = '[' . ($columnId - $currentColumnNumber) . ']';
}
}
$R1C1Address = "R{$rowId}C{$columnId}";
return $R1C1Address;
}
}
|
