[Summary view]

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

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