[Summary view]

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