[Summary view]

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

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