|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Box\Spout\Reader\XLSX\Helper; |
|
4
|
|
|
|
|
5
|
|
|
use Box\Spout\Common\Exception\InvalidArgumentException; |
|
6
|
|
|
|
|
7
|
|
|
/** |
|
8
|
|
|
* Class CellHelper |
|
9
|
|
|
* This class provides helper functions when working with cells |
|
10
|
|
|
*/ |
|
11
|
|
|
class CellHelper |
|
12
|
|
|
{ |
|
13
|
|
|
// Using ord() is super slow... Using a pre-computed hash table instead. |
|
14
|
|
|
private static $columnLetterToIndexMapping = [ |
|
15
|
|
|
'A' => 0, 'B' => 1, 'C' => 2, 'D' => 3, 'E' => 4, 'F' => 5, 'G' => 6, |
|
16
|
|
|
'H' => 7, 'I' => 8, 'J' => 9, 'K' => 10, 'L' => 11, 'M' => 12, 'N' => 13, |
|
17
|
|
|
'O' => 14, 'P' => 15, 'Q' => 16, 'R' => 17, 'S' => 18, 'T' => 19, 'U' => 20, |
|
18
|
|
|
'V' => 21, 'W' => 22, 'X' => 23, 'Y' => 24, 'Z' => 25, |
|
19
|
|
|
]; |
|
20
|
|
|
|
|
21
|
|
|
/** |
|
22
|
|
|
* Returns the base 10 column index associated to the cell index (base 26). |
|
23
|
|
|
* Excel uses A to Z letters for column indexing, where A is the 1st column, |
|
24
|
|
|
* Z is the 26th and AA is the 27th. |
|
25
|
|
|
* The mapping is zero based, so that A1 maps to 0, B2 maps to 1, Z13 to 25 and AA4 to 26. |
|
26
|
|
|
* |
|
27
|
|
|
* @param string $cellIndex The Excel cell index ('A1', 'BC13', ...) |
|
28
|
|
|
* @throws \Box\Spout\Common\Exception\InvalidArgumentException When the given cell index is invalid |
|
29
|
|
|
* @return int |
|
30
|
|
|
*/ |
|
31
|
40 |
|
public static function getColumnIndexFromCellIndex($cellIndex) |
|
32
|
|
|
{ |
|
33
|
40 |
|
if (!self::isValidCellIndex($cellIndex)) { |
|
34
|
1 |
|
throw new InvalidArgumentException('Cannot get column index from an invalid cell index.'); |
|
35
|
|
|
} |
|
36
|
|
|
|
|
37
|
39 |
|
$columnIndex = 0; |
|
38
|
|
|
|
|
39
|
|
|
// Remove row information |
|
40
|
39 |
|
$columnLetters = preg_replace('/\d/', '', $cellIndex); |
|
41
|
|
|
|
|
42
|
|
|
// strlen() is super slow too... Using isset() is way faster and not too unreadable, |
|
43
|
|
|
// since we checked before that there are between 1 and 3 letters. |
|
44
|
39 |
|
$columnLength = isset($columnLetters[1]) ? (isset($columnLetters[2]) ? 3 : 2) : 1; |
|
45
|
|
|
|
|
46
|
|
|
// Looping over the different letters of the column is slower than this method. |
|
47
|
|
|
// Also, not using the pow() function because it's slooooow... |
|
48
|
|
|
switch ($columnLength) { |
|
49
|
39 |
|
case 1: |
|
50
|
35 |
|
$columnIndex = (self::$columnLetterToIndexMapping[$columnLetters]); |
|
51
|
35 |
|
break; |
|
52
|
4 |
|
case 2: |
|
53
|
3 |
|
$firstLetterIndex = (self::$columnLetterToIndexMapping[$columnLetters[0]] + 1) * 26; |
|
54
|
3 |
|
$secondLetterIndex = self::$columnLetterToIndexMapping[$columnLetters[1]]; |
|
55
|
3 |
|
$columnIndex = $firstLetterIndex + $secondLetterIndex; |
|
56
|
3 |
|
break; |
|
57
|
1 |
|
case 3: |
|
58
|
1 |
|
$firstLetterIndex = (self::$columnLetterToIndexMapping[$columnLetters[0]] + 1) * 676; |
|
59
|
1 |
|
$secondLetterIndex = (self::$columnLetterToIndexMapping[$columnLetters[1]] + 1) * 26; |
|
60
|
1 |
|
$thirdLetterIndex = self::$columnLetterToIndexMapping[$columnLetters[2]]; |
|
61
|
1 |
|
$columnIndex = $firstLetterIndex + $secondLetterIndex + $thirdLetterIndex; |
|
62
|
1 |
|
break; |
|
63
|
|
|
} |
|
64
|
|
|
|
|
65
|
39 |
|
return $columnIndex; |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
|
|
/** |
|
69
|
|
|
* Returns whether a cell index is valid, in an Excel world. |
|
70
|
|
|
* To be valid, the cell index should start with capital letters and be followed by numbers. |
|
71
|
|
|
* There can only be 3 letters, as there can only be 16,384 rows, which is equivalent to 'XFE'. |
|
72
|
|
|
* |
|
73
|
|
|
* @param string $cellIndex The Excel cell index ('A1', 'BC13', ...) |
|
74
|
|
|
* @return bool |
|
75
|
|
|
*/ |
|
76
|
40 |
|
protected static function isValidCellIndex($cellIndex) |
|
77
|
|
|
{ |
|
78
|
40 |
|
return (preg_match('/^[A-Z]{1,3}\d+$/', $cellIndex) === 1); |
|
79
|
|
|
} |
|
80
|
|
|
} |
|
81
|
|
|
|
box /
spout
