Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Cell;
   4  
   5  use PhpOffice\PhpSpreadsheet\Exception;
   6  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   7  
   8  /**
   9   * Helper class to manipulate cell coordinates.
  10   *
  11   * Columns indexes and rows are always based on 1, **not** on 0. This match the behavior
  12   * that Excel users are used to, and also match the Excel functions `COLUMN()` and `ROW()`.
  13   */
  14  abstract class Coordinate
  15  {
  16      /**
  17       * Default range variable constant.
  18       *
  19       * @var string
  20       */
  21      const DEFAULT_RANGE = 'A1:A1';
  22  
  23      /**
  24       * Coordinate from string.
  25       *
  26       * @param string $pCoordinateString eg: 'A1'
  27       *
  28       * @return string[] Array containing column and row (indexes 0 and 1)
  29       */
  30      public static function coordinateFromString($pCoordinateString)
  31      {
  32          if (preg_match('/^([$]?[A-Z]{1,3})([$]?\\d{1,7})$/', $pCoordinateString, $matches)) {
  33              return [$matches[1], $matches[2]];
  34          } elseif (self::coordinateIsRange($pCoordinateString)) {
  35              throw new Exception('Cell coordinate string can not be a range of cells');
  36          } elseif ($pCoordinateString == '') {
  37              throw new Exception('Cell coordinate can not be zero-length string');
  38          }
  39  
  40          throw new Exception('Invalid cell coordinate ' . $pCoordinateString);
  41      }
  42  
  43      /**
  44       * Checks if a coordinate represents a range of cells.
  45       *
  46       * @param string $coord eg: 'A1' or 'A1:A2' or 'A1:A2,C1:C2'
  47       *
  48       * @return bool Whether the coordinate represents a range of cells
  49       */
  50      public static function coordinateIsRange($coord)
  51      {
  52          return (strpos($coord, ':') !== false) || (strpos($coord, ',') !== false);
  53      }
  54  
  55      /**
  56       * Make string row, column or cell coordinate absolute.
  57       *
  58       * @param string $pCoordinateString e.g. 'A' or '1' or 'A1'
  59       *                    Note that this value can be a row or column reference as well as a cell reference
  60       *
  61       * @return string Absolute coordinate        e.g. '$A' or '$1' or '$A$1'
  62       */
  63      public static function absoluteReference($pCoordinateString)
  64      {
  65          if (self::coordinateIsRange($pCoordinateString)) {
  66              throw new Exception('Cell coordinate string can not be a range of cells');
  67          }
  68  
  69          // Split out any worksheet name from the reference
  70          [$worksheet, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
  71          if ($worksheet > '') {
  72              $worksheet .= '!';
  73          }
  74  
  75          // Create absolute coordinate
  76          if (ctype_digit($pCoordinateString)) {
  77              return $worksheet . '$' . $pCoordinateString;
  78          } elseif (ctype_alpha($pCoordinateString)) {
  79              return $worksheet . '$' . strtoupper($pCoordinateString);
  80          }
  81  
  82          return $worksheet . self::absoluteCoordinate($pCoordinateString);
  83      }
  84  
  85      /**
  86       * Make string coordinate absolute.
  87       *
  88       * @param string $pCoordinateString e.g. 'A1'
  89       *
  90       * @return string Absolute coordinate        e.g. '$A$1'
  91       */
  92      public static function absoluteCoordinate($pCoordinateString)
  93      {
  94          if (self::coordinateIsRange($pCoordinateString)) {
  95              throw new Exception('Cell coordinate string can not be a range of cells');
  96          }
  97  
  98          // Split out any worksheet name from the coordinate
  99          [$worksheet, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
 100          if ($worksheet > '') {
 101              $worksheet .= '!';
 102          }
 103  
 104          // Create absolute coordinate
 105          [$column, $row] = self::coordinateFromString($pCoordinateString);
 106          $column = ltrim($column, '$');
 107          $row = ltrim($row, '$');
 108  
 109          return $worksheet . '$' . $column . '$' . $row;
 110      }
 111  
 112      /**
 113       * Split range into coordinate strings.
 114       *
 115       * @param string $pRange e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
 116       *
 117       * @return array Array containing one or more arrays containing one or two coordinate strings
 118       *                                e.g. ['B4','D9'] or [['B4','D9'], ['H2','O11']]
 119       *                                        or ['B4']
 120       */
 121      public static function splitRange($pRange)
 122      {
 123          // Ensure $pRange is a valid range
 124          if (empty($pRange)) {
 125              $pRange = self::DEFAULT_RANGE;
 126          }
 127  
 128          $exploded = explode(',', $pRange);
 129          $counter = count($exploded);
 130          for ($i = 0; $i < $counter; ++$i) {
 131              $exploded[$i] = explode(':', $exploded[$i]);
 132          }
 133  
 134          return $exploded;
 135      }
 136  
 137      /**
 138       * Build range from coordinate strings.
 139       *
 140       * @param array $pRange Array containg one or more arrays containing one or two coordinate strings
 141       *
 142       * @return string String representation of $pRange
 143       */
 144      public static function buildRange(array $pRange)
 145      {
 146          // Verify range
 147          if (empty($pRange) || !is_array($pRange[0])) {
 148              throw new Exception('Range does not contain any information');
 149          }
 150  
 151          // Build range
 152          $counter = count($pRange);
 153          for ($i = 0; $i < $counter; ++$i) {
 154              $pRange[$i] = implode(':', $pRange[$i]);
 155          }
 156  
 157          return implode(',', $pRange);
 158      }
 159  
 160      /**
 161       * Calculate range boundaries.
 162       *
 163       * @param string $pRange Cell range (e.g. A1:A1)
 164       *
 165       * @return array Range coordinates [Start Cell, End Cell]
 166       *                    where Start Cell and End Cell are arrays (Column Number, Row Number)
 167       */
 168      public static function rangeBoundaries($pRange)
 169      {
 170          // Ensure $pRange is a valid range
 171          if (empty($pRange)) {
 172              $pRange = self::DEFAULT_RANGE;
 173          }
 174  
 175          // Uppercase coordinate
 176          $pRange = strtoupper($pRange);
 177  
 178          // Extract range
 179          if (strpos($pRange, ':') === false) {
 180              $rangeA = $rangeB = $pRange;
 181          } else {
 182              [$rangeA, $rangeB] = explode(':', $pRange);
 183          }
 184  
 185          // Calculate range outer borders
 186          $rangeStart = self::coordinateFromString($rangeA);
 187          $rangeEnd = self::coordinateFromString($rangeB);
 188  
 189          // Translate column into index
 190          $rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
 191          $rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);
 192  
 193          return [$rangeStart, $rangeEnd];
 194      }
 195  
 196      /**
 197       * Calculate range dimension.
 198       *
 199       * @param string $pRange Cell range (e.g. A1:A1)
 200       *
 201       * @return array Range dimension (width, height)
 202       */
 203      public static function rangeDimension($pRange)
 204      {
 205          // Calculate range outer borders
 206          [$rangeStart, $rangeEnd] = self::rangeBoundaries($pRange);
 207  
 208          return [($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1)];
 209      }
 210  
 211      /**
 212       * Calculate range boundaries.
 213       *
 214       * @param string $pRange Cell range (e.g. A1:A1)
 215       *
 216       * @return array Range coordinates [Start Cell, End Cell]
 217       *                    where Start Cell and End Cell are arrays [Column ID, Row Number]
 218       */
 219      public static function getRangeBoundaries($pRange)
 220      {
 221          // Ensure $pRange is a valid range
 222          if (empty($pRange)) {
 223              $pRange = self::DEFAULT_RANGE;
 224          }
 225  
 226          // Uppercase coordinate
 227          $pRange = strtoupper($pRange);
 228  
 229          // Extract range
 230          if (strpos($pRange, ':') === false) {
 231              $rangeA = $rangeB = $pRange;
 232          } else {
 233              [$rangeA, $rangeB] = explode(':', $pRange);
 234          }
 235  
 236          return [self::coordinateFromString($rangeA), self::coordinateFromString($rangeB)];
 237      }
 238  
 239      /**
 240       * Column index from string.
 241       *
 242       * @param string $pString eg 'A'
 243       *
 244       * @return int Column index (A = 1)
 245       */
 246      public static function columnIndexFromString($pString)
 247      {
 248          //    Using a lookup cache adds a slight memory overhead, but boosts speed
 249          //    caching using a static within the method is faster than a class static,
 250          //        though it's additional memory overhead
 251          static $indexCache = [];
 252  
 253          if (isset($indexCache[$pString])) {
 254              return $indexCache[$pString];
 255          }
 256          //    It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array rather than use ord()
 257          //        and make it case insensitive to get rid of the strtoupper() as well. Because it's a static, there's no significant
 258          //        memory overhead either
 259          static $columnLookup = [
 260              'A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12, 'M' => 13,
 261              'N' => 14, 'O' => 15, 'P' => 16, 'Q' => 17, 'R' => 18, 'S' => 19, 'T' => 20, 'U' => 21, 'V' => 22, 'W' => 23, 'X' => 24, 'Y' => 25, 'Z' => 26,
 262              'a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5, 'f' => 6, 'g' => 7, 'h' => 8, 'i' => 9, 'j' => 10, 'k' => 11, 'l' => 12, 'm' => 13,
 263              'n' => 14, 'o' => 15, 'p' => 16, 'q' => 17, 'r' => 18, 's' => 19, 't' => 20, 'u' => 21, 'v' => 22, 'w' => 23, 'x' => 24, 'y' => 25, 'z' => 26,
 264          ];
 265  
 266          //    We also use the language construct isset() rather than the more costly strlen() function to match the length of $pString
 267          //        for improved performance
 268          if (isset($pString[0])) {
 269              if (!isset($pString[1])) {
 270                  $indexCache[$pString] = $columnLookup[$pString];
 271  
 272                  return $indexCache[$pString];
 273              } elseif (!isset($pString[2])) {
 274                  $indexCache[$pString] = $columnLookup[$pString[0]] * 26 + $columnLookup[$pString[1]];
 275  
 276                  return $indexCache[$pString];
 277              } elseif (!isset($pString[3])) {
 278                  $indexCache[$pString] = $columnLookup[$pString[0]] * 676 + $columnLookup[$pString[1]] * 26 + $columnLookup[$pString[2]];
 279  
 280                  return $indexCache[$pString];
 281              }
 282          }
 283  
 284          throw new Exception('Column string index can not be ' . ((isset($pString[0])) ? 'longer than 3 characters' : 'empty'));
 285      }
 286  
 287      /**
 288       * String from column index.
 289       *
 290       * @param int $columnIndex Column index (A = 1)
 291       *
 292       * @return string
 293       */
 294      public static function stringFromColumnIndex($columnIndex)
 295      {
 296          static $indexCache = [];
 297  
 298          if (!isset($indexCache[$columnIndex])) {
 299              $indexValue = $columnIndex;
 300              $base26 = null;
 301              do {
 302                  $characterValue = ($indexValue % 26) ?: 26;
 303                  $indexValue = ($indexValue - $characterValue) / 26;
 304                  $base26 = chr($characterValue + 64) . ($base26 ?: '');
 305              } while ($indexValue > 0);
 306              $indexCache[$columnIndex] = $base26;
 307          }
 308  
 309          return $indexCache[$columnIndex];
 310      }
 311  
 312      /**
 313       * Extract all cell references in range, which may be comprised of multiple cell ranges.
 314       *
 315       * @param string $cellRange Range: e.g. 'A1' or 'A1:C10' or 'A1:E10,A20:E25' or 'A1:E5 C3:G7' or 'A1:C1,A3:C3 B1:C3'
 316       *
 317       * @return array Array containing single cell references
 318       */
 319      public static function extractAllCellReferencesInRange($cellRange): array
 320      {
 321          [$ranges, $operators] = self::getCellBlocksFromRangeString($cellRange);
 322  
 323          $cells = [];
 324          foreach ($ranges as $range) {
 325              $cells[] = self::getReferencesForCellBlock($range);
 326          }
 327  
 328          $cells = self::processRangeSetOperators($operators, $cells);
 329  
 330          if (empty($cells)) {
 331              return [];
 332          }
 333  
 334          $cellList = array_merge(...$cells);
 335          $cellList = self::sortCellReferenceArray($cellList);
 336  
 337          return $cellList;
 338      }
 339  
 340      private static function processRangeSetOperators(array $operators, array $cells): array
 341      {
 342          for ($offset = 0; $offset < count($operators); ++$offset) {
 343              $operator = $operators[$offset];
 344              if ($operator !== ' ') {
 345                  continue;
 346              }
 347  
 348              $cells[$offset] = array_intersect($cells[$offset], $cells[$offset + 1]);
 349              unset($operators[$offset], $cells[$offset + 1]);
 350              $operators = array_values($operators);
 351              $cells = array_values($cells);
 352              --$offset;
 353          }
 354  
 355          return $cells;
 356      }
 357  
 358      private static function sortCellReferenceArray(array $cellList): array
 359      {
 360          //    Sort the result by column and row
 361          $sortKeys = [];
 362          foreach ($cellList as $coord) {
 363              [$column, $row] = sscanf($coord, '%[A-Z]%d');
 364              $sortKeys[sprintf('%3s%09d', $column, $row)] = $coord;
 365          }
 366          ksort($sortKeys);
 367  
 368          return array_values($sortKeys);
 369      }
 370  
 371      /**
 372       * Get all cell references for an individual cell block.
 373       *
 374       * @param string $cellBlock A cell range e.g. A4:B5
 375       *
 376       * @return array All individual cells in that range
 377       */
 378      private static function getReferencesForCellBlock($cellBlock)
 379      {
 380          $returnValue = [];
 381  
 382          // Single cell?
 383          if (!self::coordinateIsRange($cellBlock)) {
 384              return (array) $cellBlock;
 385          }
 386  
 387          // Range...
 388          $ranges = self::splitRange($cellBlock);
 389          foreach ($ranges as $range) {
 390              // Single cell?
 391              if (!isset($range[1])) {
 392                  $returnValue[] = $range[0];
 393  
 394                  continue;
 395              }
 396  
 397              // Range...
 398              [$rangeStart, $rangeEnd] = $range;
 399              [$startColumn, $startRow] = self::coordinateFromString($rangeStart);
 400              [$endColumn, $endRow] = self::coordinateFromString($rangeEnd);
 401              $startColumnIndex = self::columnIndexFromString($startColumn);
 402              $endColumnIndex = self::columnIndexFromString($endColumn);
 403              ++$endColumnIndex;
 404  
 405              // Current data
 406              $currentColumnIndex = $startColumnIndex;
 407              $currentRow = $startRow;
 408  
 409              self::validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow);
 410  
 411              // Loop cells
 412              while ($currentColumnIndex < $endColumnIndex) {
 413                  while ($currentRow <= $endRow) {
 414                      $returnValue[] = self::stringFromColumnIndex($currentColumnIndex) . $currentRow;
 415                      ++$currentRow;
 416                  }
 417                  ++$currentColumnIndex;
 418                  $currentRow = $startRow;
 419              }
 420          }
 421  
 422          return $returnValue;
 423      }
 424  
 425      /**
 426       * Convert an associative array of single cell coordinates to values to an associative array
 427       * of cell ranges to values.  Only adjacent cell coordinates with the same
 428       * value will be merged.  If the value is an object, it must implement the method getHashCode().
 429       *
 430       * For example, this function converts:
 431       *
 432       *    [ 'A1' => 'x', 'A2' => 'x', 'A3' => 'x', 'A4' => 'y' ]
 433       *
 434       * to:
 435       *
 436       *    [ 'A1:A3' => 'x', 'A4' => 'y' ]
 437       *
 438       * @param array $pCoordCollection associative array mapping coordinates to values
 439       *
 440       * @return array associative array mapping coordinate ranges to valuea
 441       */
 442      public static function mergeRangesInCollection(array $pCoordCollection)
 443      {
 444          $hashedValues = [];
 445          $mergedCoordCollection = [];
 446  
 447          foreach ($pCoordCollection as $coord => $value) {
 448              if (self::coordinateIsRange($coord)) {
 449                  $mergedCoordCollection[$coord] = $value;
 450  
 451                  continue;
 452              }
 453  
 454              [$column, $row] = self::coordinateFromString($coord);
 455              $row = (int) (ltrim($row, '$'));
 456              $hashCode = $column . '-' . (is_object($value) ? $value->getHashCode() : $value);
 457  
 458              if (!isset($hashedValues[$hashCode])) {
 459                  $hashedValues[$hashCode] = (object) [
 460                      'value' => $value,
 461                      'col' => $column,
 462                      'rows' => [$row],
 463                  ];
 464              } else {
 465                  $hashedValues[$hashCode]->rows[] = $row;
 466              }
 467          }
 468  
 469          ksort($hashedValues);
 470  
 471          foreach ($hashedValues as $hashedValue) {
 472              sort($hashedValue->rows);
 473              $rowStart = null;
 474              $rowEnd = null;
 475              $ranges = [];
 476  
 477              foreach ($hashedValue->rows as $row) {
 478                  if ($rowStart === null) {
 479                      $rowStart = $row;
 480                      $rowEnd = $row;
 481                  } elseif ($rowEnd === $row - 1) {
 482                      $rowEnd = $row;
 483                  } else {
 484                      if ($rowStart == $rowEnd) {
 485                          $ranges[] = $hashedValue->col . $rowStart;
 486                      } else {
 487                          $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
 488                      }
 489  
 490                      $rowStart = $row;
 491                      $rowEnd = $row;
 492                  }
 493              }
 494  
 495              if ($rowStart !== null) {
 496                  if ($rowStart == $rowEnd) {
 497                      $ranges[] = $hashedValue->col . $rowStart;
 498                  } else {
 499                      $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
 500                  }
 501              }
 502  
 503              foreach ($ranges as $range) {
 504                  $mergedCoordCollection[$range] = $hashedValue->value;
 505              }
 506          }
 507  
 508          return $mergedCoordCollection;
 509      }
 510  
 511      /**
 512       * Get the individual cell blocks from a range string, removing any $ characters.
 513       *      then splitting by operators and returning an array with ranges and operators.
 514       *
 515       * @param string $rangeString
 516       *
 517       * @return array[]
 518       */
 519      private static function getCellBlocksFromRangeString($rangeString)
 520      {
 521          $rangeString = str_replace('$', '', strtoupper($rangeString));
 522  
 523          // split range sets on intersection (space) or union (,) operators
 524          $tokens = preg_split('/([ ,])/', $rangeString, -1, PREG_SPLIT_DELIM_CAPTURE);
 525          // separate the range sets and the operators into arrays
 526          $split = array_chunk($tokens, 2);
 527          $ranges = array_column($split, 0);
 528          $operators = array_column($split, 1);
 529  
 530          return [$ranges, $operators];
 531      }
 532  
 533      /**
 534       * Check that the given range is valid, i.e. that the start column and row are not greater than the end column and
 535       * row.
 536       *
 537       * @param string $cellBlock The original range, for displaying a meaningful error message
 538       * @param int $startColumnIndex
 539       * @param int $endColumnIndex
 540       * @param int $currentRow
 541       * @param int $endRow
 542       */
 543      private static function validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow): void
 544      {
 545          if ($startColumnIndex >= $endColumnIndex || $currentRow > $endRow) {
 546              throw new Exception('Invalid range: "' . $cellBlock . '"');
 547          }
 548      }
 549  }