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