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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body