|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Worksheet; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\AddressRange; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\CellAddress; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\CellRange; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException; |
|
9
|
|
|
|
|
10
|
|
|
class Validations |
|
11
|
|
|
{ |
|
12
|
|
|
/** |
|
13
|
|
|
* Validate a cell address. |
|
14
|
|
|
* |
|
15
|
|
|
* @param null|array{0: int, 1: int}|CellAddress|string $cellAddress Coordinate of the cell as a string, eg: 'C5'; |
|
16
|
|
|
* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. |
|
17
|
|
|
*/ |
|
18
|
10081 |
|
public static function validateCellAddress(null|CellAddress|string|array $cellAddress): string |
|
19
|
|
|
{ |
|
20
|
10081 |
|
if (is_string($cellAddress)) { |
|
21
|
10075 |
|
[$worksheet, $address] = Worksheet::extractSheetTitle($cellAddress, true); |
|
22
|
|
|
// if (!empty($worksheet) && $worksheet !== $this->getTitle()) { |
|
23
|
|
|
// throw new Exception('Reference is not for this worksheet'); |
|
24
|
|
|
// } |
|
25
|
|
|
|
|
26
|
10075 |
|
return empty($worksheet) ? strtoupper("$address") : $worksheet . '!' . strtoupper("$address"); |
|
27
|
|
|
} |
|
28
|
|
|
|
|
29
|
4061 |
|
if (is_array($cellAddress)) { |
|
30
|
1098 |
|
$cellAddress = CellAddress::fromColumnRowArray($cellAddress); |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
4061 |
|
return (string) $cellAddress; |
|
34
|
|
|
} |
|
35
|
|
|
|
|
36
|
|
|
/** |
|
37
|
|
|
* Validate a cell address or cell range. |
|
38
|
|
|
* |
|
39
|
|
|
* @param AddressRange<CellAddress>|AddressRange<int>|AddressRange<string>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|CellAddress|int|string $cellRange Coordinate of the cells as a string, eg: 'C5:F12'; |
|
40
|
|
|
* or as an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 12]), |
|
41
|
|
|
* or as a CellAddress or AddressRange object. |
|
42
|
|
|
*/ |
|
43
|
10068 |
|
public static function validateCellOrCellRange(AddressRange|CellAddress|int|string|array $cellRange): string |
|
44
|
|
|
{ |
|
45
|
10068 |
|
if (is_string($cellRange) || is_numeric($cellRange)) { |
|
46
|
|
|
// Convert a single column reference like 'A' to 'A:A', |
|
47
|
|
|
// a single row reference like '1' to '1:1' |
|
48
|
10068 |
|
$cellRange = (string) preg_replace('/^([A-Z]+|\d+)$/', '${1}:${1}', (string) $cellRange); |
|
49
|
65 |
|
} elseif (is_object($cellRange) && $cellRange instanceof CellAddress) { |
|
50
|
1 |
|
$cellRange = new CellRange($cellRange, $cellRange); |
|
51
|
|
|
} |
|
52
|
|
|
|
|
53
|
10068 |
|
return self::validateCellRange($cellRange); |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
private const SETMAXROW = '${1}1:${2}' . AddressRange::MAX_ROW; |
|
57
|
|
|
private const SETMAXCOL = 'A${1}:' . AddressRange::MAX_COLUMN . '${2}'; |
|
58
|
|
|
|
|
59
|
|
|
/** |
|
60
|
|
|
* Convert Column ranges like 'A:C' to 'A1:C1048576' |
|
61
|
|
|
* or Row ranges like '1:3' to 'A1:XFD3'. |
|
62
|
|
|
*/ |
|
63
|
|
|
public static function convertWholeRowColumn(?string $addressRange): string |
|
64
|
|
|
{ |
|
65
|
|
|
return (string) preg_replace( |
|
66
|
10147 |
|
['/^([A-Z]+):([A-Z]+)$/i', '/^(\\d+):(\\d+)$/'], |
|
67
|
|
|
[self::SETMAXROW, self::SETMAXCOL], |
|
68
|
10147 |
|
$addressRange ?? '' |
|
69
|
10146 |
|
); |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
/** |
|
73
|
10146 |
|
* Validate a cell range. |
|
74
|
10146 |
|
* |
|
75
|
10146 |
|
* @param AddressRange<CellAddress>|AddressRange<int>|AddressRange<string>|array{0: int, 1: int, 2: int, 3: int}|array{0: int, 1: int}|string $cellRange Coordinate of the cells as a string, eg: 'C5:F12'; |
|
76
|
10146 |
|
* or as an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 12]), |
|
77
|
10146 |
|
* or as an AddressRange object. |
|
78
|
|
|
*/ |
|
79
|
10146 |
|
public static function validateCellRange(AddressRange|string|array $cellRange): string |
|
80
|
|
|
{ |
|
81
|
|
|
if (is_string($cellRange)) { |
|
82
|
73 |
|
[$worksheet, $addressRange] = Worksheet::extractSheetTitle($cellRange, true); |
|
83
|
69 |
|
|
|
84
|
69 |
|
// Convert Column ranges like 'A:C' to 'A1:C1048576' |
|
85
|
13 |
|
// or Row ranges like '1:3' to 'A1:XFD3' |
|
86
|
13 |
|
$addressRange = self::convertWholeRowColumn($addressRange); |
|
87
|
|
|
|
|
88
|
13 |
|
return empty($worksheet) ? strtoupper($addressRange) : $worksheet . '!' . strtoupper($addressRange); |
|
89
|
58 |
|
} |
|
90
|
57 |
|
|
|
91
|
57 |
|
if (is_array($cellRange)) { |
|
92
|
|
|
switch (count($cellRange)) { |
|
93
|
57 |
|
case 4: |
|
94
|
|
|
$from = [$cellRange[0], $cellRange[1]]; |
|
95
|
1 |
|
$to = [$cellRange[2], $cellRange[3]]; |
|
96
|
|
|
|
|
97
|
68 |
|
break; |
|
98
|
|
|
case 2: |
|
99
|
|
|
$from = [$cellRange[0], $cellRange[1]]; |
|
100
|
72 |
|
$to = [$cellRange[0], $cellRange[1]]; |
|
101
|
|
|
|
|
102
|
|
|
break; |
|
103
|
10067 |
|
default: |
|
104
|
|
|
throw new SpreadsheetException('CellRange array length must be 2 or 4'); |
|
105
|
|
|
} |
|
106
|
10067 |
|
$cellRange = new CellRange(CellAddress::fromColumnRowArray($from), CellAddress::fromColumnRowArray($to)); |
|
107
|
|
|
} |
|
108
|
10067 |
|
|
|
109
|
10067 |
|
return (string) $cellRange; |
|
110
|
10067 |
|
} |
|
111
|
7 |
|
|
|
112
|
1 |
|
public static function definedNameToCoordinate(string $coordinate, Worksheet $worksheet): string |
|
113
|
|
|
{ |
|
114
|
|
|
// Uppercase coordinate |
|
115
|
|
|
$coordinate = strtoupper($coordinate); |
|
116
|
10067 |
|
// Eliminate leading equal sign |
|
117
|
|
|
$testCoordinate = (string) preg_replace('/^=/', '', $coordinate); |
|
118
|
|
|
$defined = $worksheet->getParentOrThrow()->getDefinedName($testCoordinate, $worksheet); |
|
119
|
|
|
if ($defined !== null) { |
|
120
|
|
|
if ($defined->getWorksheet() === $worksheet && !$defined->isFormula()) { |
|
121
|
|
|
$coordinate = (string) preg_replace('/^=/', '', $defined->getValue()); |
|
122
|
|
|
} |
|
123
|
|
|
} |
|
124
|
|
|
|
|
125
|
|
|
return $coordinate; |
|
126
|
|
|
} |
|
127
|
|
|
} |
|
128
|
|
|
|
PHPOffice /
PhpSpreadsheet
