[Summary view]

   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 \OpenSpout\Common\Exception\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  }