See Release Notes
Long Term Support Release
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]
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 * Coordinate from string. 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) 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 * Get indexes from a string coordinates. 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 277 if (isset($indexCache[$columnAddress])) { 278 return $indexCache[$columnAddress]; 279 } 280 // It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array 281 // rather than use ord() and make it case insensitive to get rid of the strtoupper() as well. 282 // Because it's a static, there's no significant memory overhead either. 283 static $columnLookup = [ 284 'A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 285 'K' => 11, 'L' => 12, 'M' => 13, 'N' => 14, 'O' => 15, 'P' => 16, 'Q' => 17, 'R' => 18, 'S' => 19, 286 'T' => 20, 'U' => 21, 'V' => 22, 'W' => 23, 'X' => 24, 'Y' => 25, 'Z' => 26, 287 'a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5, 'f' => 6, 'g' => 7, 'h' => 8, 'i' => 9, 'j' => 10, 288 'k' => 11, 'l' => 12, 'm' => 13, 'n' => 14, 'o' => 15, 'p' => 16, 'q' => 17, 'r' => 18, 's' => 19, 289 't' => 20, 'u' => 21, 'v' => 22, 'w' => 23, 'x' => 24, 'y' => 25, 'z' => 26, 290 ]; 291 292 // We also use the language construct isset() rather than the more costly strlen() function to match the 293 // length of $columnAddress for improved performance 294 if (isset($columnAddress[0])) { 295 if (!isset($columnAddress[1])) { 296 $indexCache[$columnAddress] = $columnLookup[$columnAddress]; 297 298 return $indexCache[$columnAddress]; 299 } elseif (!isset($columnAddress[2])) { 300 $indexCache[$columnAddress] = $columnLookup[$columnAddress[0]] * 26 301 + $columnLookup[$columnAddress[1]]; 302 303 return $indexCache[$columnAddress]; 304 } elseif (!isset($columnAddress[3])) { 305 $indexCache[$columnAddress] = $columnLookup[$columnAddress[0]] * 676 306 + $columnLookup[$columnAddress[1]] * 26 307 + $columnLookup[$columnAddress[2]]; 308 309 return $indexCache[$columnAddress]; 310 } 311 } 312 313 throw new Exception( 314 'Column string index can not be ' . ((isset($columnAddress[0])) ? 'longer than 3 characters' : 'empty') 315 ); 316 } 317 318 /** 319 * String from column index. 320 * 321 * @param int $columnIndex Column index (A = 1) 322 * 323 * @return string 324 */ 325 public static function stringFromColumnIndex($columnIndex) 326 { 327 static $indexCache = []; 328 static $lookupCache = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 329 330 if (!isset($indexCache[$columnIndex])) { 331 $indexValue = $columnIndex; 332 $base26 = ''; 333 do { 334 $characterValue = ($indexValue % 26) ?: 26; 335 $indexValue = ($indexValue - $characterValue) / 26; 336 $base26 = $lookupCache[$characterValue] . $base26; 337 } while ($indexValue > 0); 338 $indexCache[$columnIndex] = $base26; 339 } 340 341 return $indexCache[$columnIndex]; 342 } 343 344 /** 345 * Extract all cell references in range, which may be comprised of multiple cell ranges. 346 * 347 * @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' 348 * 349 * @return array Array containing single cell references 350 */ 351 public static function extractAllCellReferencesInRange($cellRange): array 352 { 353 if (substr_count($cellRange, '!') > 1) { 354 throw new Exception('3-D Range References are not supported'); 355 } 356 357 [$worksheet, $cellRange] = Worksheet::extractSheetTitle($cellRange, true); 358 $quoted = ''; 359 if ($worksheet > '') { 360 $quoted = Worksheet::nameRequiresQuotes($worksheet) ? "'" : ''; 361 if (substr($worksheet, 0, 1) === "'" && substr($worksheet, -1, 1) === "'") { 362 $worksheet = substr($worksheet, 1, -1); 363 } 364 $worksheet = str_replace("'", "''", $worksheet); 365 } 366 [$ranges, $operators] = self::getCellBlocksFromRangeString($cellRange); 367 368 $cells = []; 369 foreach ($ranges as $range) { 370 $cells[] = self::getReferencesForCellBlock($range); 371 } 372 373 $cells = self::processRangeSetOperators($operators, $cells); 374 375 if (empty($cells)) { 376 return []; 377 } 378 379 $cellList = array_merge(...$cells); 380 381 return array_map( 382 function ($cellAddress) use ($worksheet, $quoted) { 383 return ($worksheet !== '') ? "{$quoted}{$worksheet}{$quoted}!{$cellAddress}" : $cellAddress; 384 }, 385 self::sortCellReferenceArray($cellList) 386 ); 387 } 388 389 private static function processRangeSetOperators(array $operators, array $cells): array 390 { 391 $operatorCount = count($operators); 392 for ($offset = 0; $offset < $operatorCount; ++$offset) { 393 $operator = $operators[$offset]; 394 if ($operator !== ' ') { 395 continue; 396 } 397 398 $cells[$offset] = array_intersect($cells[$offset], $cells[$offset + 1]); 399 unset($operators[$offset], $cells[$offset + 1]); 400 $operators = array_values($operators); 401 $cells = array_values($cells); 402 --$offset; 403 --$operatorCount; 404 } 405 406 return $cells; 407 } 408 409 private static function sortCellReferenceArray(array $cellList): array 410 { 411 // Sort the result by column and row 412 $sortKeys = []; 413 foreach ($cellList as $coordinate) { 414 sscanf($coordinate, '%[A-Z]%d', $column, $row); 415 $key = (--$row * 16384) + self::columnIndexFromString($column); 416 $sortKeys[$key] = $coordinate; 417 } 418 ksort($sortKeys); 419 420 return array_values($sortKeys); 421 } 422 423 /** 424 * Get all cell references for an individual cell block. 425 * 426 * @param string $cellBlock A cell range e.g. A4:B5 427 * 428 * @return array All individual cells in that range 429 */ 430 private static function getReferencesForCellBlock($cellBlock) 431 { 432 $returnValue = []; 433 434 // Single cell? 435 if (!self::coordinateIsRange($cellBlock)) { 436 return (array) $cellBlock; 437 } 438 439 // Range... 440 $ranges = self::splitRange($cellBlock); 441 foreach ($ranges as $range) { 442 // Single cell? 443 if (!isset($range[1])) { 444 $returnValue[] = $range[0]; 445 446 continue; 447 } 448 449 // Range... 450 [$rangeStart, $rangeEnd] = $range; 451 [$startColumn, $startRow] = self::coordinateFromString($rangeStart); 452 [$endColumn, $endRow] = self::coordinateFromString($rangeEnd); 453 $startColumnIndex = self::columnIndexFromString($startColumn); 454 $endColumnIndex = self::columnIndexFromString($endColumn); 455 ++$endColumnIndex; 456 457 // Current data 458 $currentColumnIndex = $startColumnIndex; 459 $currentRow = $startRow; 460 461 self::validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow); 462 463 // Loop cells 464 while ($currentColumnIndex < $endColumnIndex) { 465 while ($currentRow <= $endRow) { 466 $returnValue[] = self::stringFromColumnIndex($currentColumnIndex) . $currentRow; 467 ++$currentRow; 468 } 469 ++$currentColumnIndex; 470 $currentRow = $startRow; 471 } 472 } 473 474 return $returnValue; 475 } 476 477 /** 478 * Convert an associative array of single cell coordinates to values to an associative array 479 * of cell ranges to values. Only adjacent cell coordinates with the same 480 * value will be merged. If the value is an object, it must implement the method getHashCode(). 481 * 482 * For example, this function converts: 483 * 484 * [ 'A1' => 'x', 'A2' => 'x', 'A3' => 'x', 'A4' => 'y' ] 485 * 486 * to: 487 * 488 * [ 'A1:A3' => 'x', 'A4' => 'y' ] 489 * 490 * @param array $coordinateCollection associative array mapping coordinates to values 491 * 492 * @return array associative array mapping coordinate ranges to valuea 493 */ 494 public static function mergeRangesInCollection(array $coordinateCollection) 495 { 496 $hashedValues = []; 497 $mergedCoordCollection = []; 498 499 foreach ($coordinateCollection as $coord => $value) { 500 if (self::coordinateIsRange($coord)) { 501 $mergedCoordCollection[$coord] = $value; 502 503 continue; 504 } 505 506 [$column, $row] = self::coordinateFromString($coord); 507 $row = (int) (ltrim($row, '$')); 508 $hashCode = $column . '-' . (is_object($value) ? $value->getHashCode() : $value); 509 510 if (!isset($hashedValues[$hashCode])) { 511 $hashedValues[$hashCode] = (object) [ 512 'value' => $value, 513 'col' => $column, 514 'rows' => [$row], 515 ]; 516 } else { 517 $hashedValues[$hashCode]->rows[] = $row; 518 } 519 } 520 521 ksort($hashedValues); 522 523 foreach ($hashedValues as $hashedValue) { 524 sort($hashedValue->rows); 525 $rowStart = null; 526 $rowEnd = null; 527 $ranges = []; 528 529 foreach ($hashedValue->rows as $row) { 530 if ($rowStart === null) { 531 $rowStart = $row; 532 $rowEnd = $row; 533 } elseif ($rowEnd === $row - 1) { 534 $rowEnd = $row; 535 } else { 536 if ($rowStart == $rowEnd) { 537 $ranges[] = $hashedValue->col . $rowStart; 538 } else { 539 $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd; 540 } 541 542 $rowStart = $row; 543 $rowEnd = $row; 544 } 545 } 546 547 if ($rowStart !== null) { 548 if ($rowStart == $rowEnd) { 549 $ranges[] = $hashedValue->col . $rowStart; 550 } else { 551 $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd; 552 } 553 } 554 555 foreach ($ranges as $range) { 556 $mergedCoordCollection[$range] = $hashedValue->value; 557 } 558 } 559 560 return $mergedCoordCollection; 561 } 562 563 /** 564 * Get the individual cell blocks from a range string, removing any $ characters. 565 * then splitting by operators and returning an array with ranges and operators. 566 * 567 * @param string $rangeString 568 * 569 * @return array[] 570 */ 571 private static function getCellBlocksFromRangeString($rangeString) 572 { 573 $rangeString = str_replace('$', '', strtoupper($rangeString)); 574 575 // split range sets on intersection (space) or union (,) operators 576 $tokens = preg_split('/([ ,])/', $rangeString, -1, PREG_SPLIT_DELIM_CAPTURE); 577 /** @phpstan-ignore-next-line */ 578 $split = array_chunk($tokens, 2); 579 $ranges = array_column($split, 0); 580 $operators = array_column($split, 1); 581 582 return [$ranges, $operators]; 583 } 584 585 /** 586 * Check that the given range is valid, i.e. that the start column and row are not greater than the end column and 587 * row. 588 * 589 * @param string $cellBlock The original range, for displaying a meaningful error message 590 * @param int $startColumnIndex 591 * @param int $endColumnIndex 592 * @param int $currentRow 593 * @param int $endRow 594 */ 595 private static function validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow): void 596 { 597 if ($startColumnIndex >= $endColumnIndex || $currentRow > $endRow) { 598 throw new Exception('Invalid range: "' . $cellBlock . '"'); 599 } 600 } 601 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body