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