|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Functions; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Cell; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Validations; |
|
11
|
|
|
use 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 mixed $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 mixed $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 mixed $height The height, in number of rows, that you want the returned reference to be. |
|
39
|
|
|
* Height must be a positive number. |
|
40
|
|
|
* @param mixed $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|string An array containing a cell or range of cells, or a string on error |
|
44
|
8 |
|
*/ |
|
45
|
|
|
public static function OFFSET(?string $cellAddress = null, mixed $rows = 0, mixed $columns = 0, mixed $height = null, mixed $width = null, ?Cell $cell = null): string|array |
|
46
|
8 |
|
{ |
|
47
|
8 |
|
$rows = Functions::flattenSingleValue($rows); |
|
48
|
8 |
|
$columns = Functions::flattenSingleValue($columns); |
|
49
|
8 |
|
$height = Functions::flattenSingleValue($height); |
|
50
|
|
|
$width = Functions::flattenSingleValue($width); |
|
51
|
8 |
|
|
|
52
|
1 |
|
if ($cellAddress === null || $cellAddress === '') { |
|
53
|
|
|
return ExcelError::VALUE(); |
|
54
|
|
|
} |
|
55
|
7 |
|
|
|
56
|
1 |
|
if (!is_object($cell)) { |
|
57
|
|
|
return ExcelError::REF(); |
|
58
|
|
|
} |
|
59
|
6 |
|
$sheet = $cell->getParent()?->getParent(); // worksheet |
|
60
|
|
|
if ($sheet !== null) { |
|
61
|
6 |
|
$cellAddress = Validations::definedNameToCoordinate($cellAddress, $sheet); |
|
62
|
6 |
|
} |
|
63
|
3 |
|
|
|
64
|
|
|
[$cellAddress, $worksheet] = self::extractWorksheet($cellAddress, $cell); |
|
65
|
6 |
|
|
|
66
|
6 |
|
$startCell = $endCell = $cellAddress; |
|
67
|
|
|
if (strpos($cellAddress, ':')) { |
|
68
|
6 |
|
[$startCell, $endCell] = explode(':', $cellAddress); |
|
69
|
6 |
|
} |
|
70
|
6 |
|
[$startCellColumn, $startCellRow] = Coordinate::indexesFromString($startCell); |
|
71
|
|
|
[, $endCellRow, $endCellColumn] = Coordinate::indexesFromString($endCell); |
|
72
|
6 |
|
|
|
73
|
1 |
|
$startCellRow += $rows; |
|
74
|
|
|
$startCellColumn += $columns - 1; |
|
75
|
|
|
|
|
76
|
6 |
|
if (($startCellRow <= 0) || ($startCellColumn < 0)) { |
|
77
|
6 |
|
return ExcelError::REF(); |
|
78
|
|
|
} |
|
79
|
6 |
|
|
|
80
|
|
|
$endCellColumn = self::adjustEndCellColumnForWidth($endCellColumn, $width, $startCellColumn, $columns); |
|
81
|
6 |
|
$startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1); |
|
82
|
1 |
|
|
|
83
|
|
|
$endCellRow = self::adustEndCellRowForHeight($height, $startCellRow, $rows, $endCellRow); |
|
84
|
6 |
|
|
|
85
|
|
|
if (($endCellRow <= 0) || ($endCellColumn < 0)) { |
|
86
|
6 |
|
return ExcelError::REF(); |
|
87
|
6 |
|
} |
|
88
|
3 |
|
$endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1); |
|
89
|
|
|
|
|
90
|
|
|
$cellAddress = "{$startCellColumn}{$startCellRow}"; |
|
91
|
6 |
|
if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) { |
|
92
|
|
|
$cellAddress .= ":{$endCellColumn}{$endCellRow}"; |
|
93
|
|
|
} |
|
94
|
6 |
|
|
|
95
|
|
|
return self::extractRequiredCells($worksheet, $cellAddress); |
|
96
|
6 |
|
} |
|
97
|
6 |
|
|
|
98
|
|
|
private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress): array |
|
99
|
|
|
{ |
|
100
|
6 |
|
return Calculation::getInstance($worksheet !== null ? $worksheet->getParent() : null) |
|
101
|
|
|
->extractCellRange($cellAddress, $worksheet, false); |
|
102
|
6 |
|
} |
|
103
|
|
|
|
|
104
|
6 |
|
private static function extractWorksheet(?string $cellAddress, Cell $cell): array |
|
105
|
6 |
|
{ |
|
106
|
6 |
|
$cellAddress = self::assessCellAddress($cellAddress ?? '', $cell); |
|
107
|
6 |
|
|
|
108
|
|
|
$sheetName = ''; |
|
109
|
|
|
if (str_contains($cellAddress, '!')) { |
|
110
|
6 |
|
[$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true, true); |
|
111
|
6 |
|
} |
|
112
|
|
|
|
|
113
|
|
|
$worksheet = ($sheetName !== '') |
|
114
|
6 |
|
? $cell->getWorksheet()->getParentOrThrow()->getSheetByName($sheetName) |
|
115
|
|
|
: $cell->getWorksheet(); |
|
116
|
|
|
|
|
117
|
6 |
|
return [$cellAddress, $worksheet]; |
|
118
|
|
|
} |
|
119
|
6 |
|
|
|
120
|
6 |
|
private static function assessCellAddress(string $cellAddress, Cell $cell): string |
|
121
|
|
|
{ |
|
122
|
|
|
if (preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/mui', $cellAddress) !== false) { |
|
123
|
6 |
|
$cellAddress = Functions::expandDefinedName($cellAddress, $cell); |
|
124
|
|
|
} |
|
125
|
|
|
|
|
126
|
6 |
|
return $cellAddress; |
|
127
|
|
|
} |
|
128
|
6 |
|
|
|
129
|
6 |
|
private static function adjustEndCellColumnForWidth(string $endCellColumn, mixed $width, int $startCellColumn, mixed $columns): int |
|
130
|
3 |
|
{ |
|
131
|
|
|
$endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1; |
|
132
|
6 |
|
if (($width !== null) && (!is_object($width))) { |
|
133
|
|
|
$endCellColumn = $startCellColumn + (int) $width - 1; |
|
134
|
|
|
} else { |
|
135
|
6 |
|
$endCellColumn += (int) $columns; |
|
136
|
|
|
} |
|
137
|
|
|
|
|
138
|
6 |
|
return $endCellColumn; |
|
139
|
|
|
} |
|
140
|
6 |
|
|
|
141
|
3 |
|
private static function adustEndCellRowForHeight(mixed $height, int $startCellRow, mixed $rows, mixed $endCellRow): int |
|
142
|
|
|
{ |
|
143
|
6 |
|
if (($height !== null) && (!is_object($height))) { |
|
144
|
|
|
$endCellRow = $startCellRow + (int) $height - 1; |
|
145
|
|
|
} else { |
|
146
|
6 |
|
$endCellRow += (int) $rows; |
|
147
|
|
|
} |
|
148
|
|
|
|
|
149
|
|
|
return $endCellRow; |
|
150
|
|
|
} |
|
151
|
|
|
} |
|
152
|
|
|
|
PHPOffice /
PhpSpreadsheet
