[Summary view] [Print] [Text view]

   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  }