ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Coordinate.php
Go to the documentation of this file.
1<?php
2
4
7
14abstract 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
23 const DEFAULT_RANGE = 'A1:A1';
24
32 public static function coordinateFromString($pCoordinateString)
33 {
34 if (preg_match(self::A1_COORDINATE_REGEX, $pCoordinateString, $matches)) {
35 return [$matches['absolute_col'] . $matches['col_ref'], $matches['absolute_row'] . $matches['row_ref']];
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
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
69 public static function coordinateIsRange($coord)
70 {
71 return (strpos($coord, ':') !== false) || (strpos($coord, ',') !== false);
72 }
73
82 public static function absoluteReference($pCoordinateString)
83 {
84 if (self::coordinateIsRange($pCoordinateString)) {
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, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
90 if ($worksheet > '') {
91 $worksheet .= '!';
92 }
93
94 // Create absolute coordinate
95 if (ctype_digit($pCoordinateString)) {
96 return $worksheet . '$' . $pCoordinateString;
97 } elseif (ctype_alpha($pCoordinateString)) {
98 return $worksheet . '$' . strtoupper($pCoordinateString);
99 }
100
101 return $worksheet . self::absoluteCoordinate($pCoordinateString);
102 }
103
111 public static function absoluteCoordinate($pCoordinateString)
112 {
113 if (self::coordinateIsRange($pCoordinateString)) {
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, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
119 if ($worksheet > '') {
120 $worksheet .= '!';
121 }
122
123 // Create absolute coordinate
124 [$column, $row] = self::coordinateFromString($pCoordinateString);
125 $column = ltrim($column, '$');
126 $row = ltrim($row, '$');
127
128 return $worksheet . '$' . $column . '$' . $row;
129 }
130
140 public static function splitRange($pRange)
141 {
142 // Ensure $pRange is a valid range
143 if (empty($pRange)) {
144 $pRange = self::DEFAULT_RANGE;
145 }
146
147 $exploded = explode(',', $pRange);
148 $counter = count($exploded);
149 for ($i = 0; $i < $counter; ++$i) {
150 $exploded[$i] = explode(':', $exploded[$i]);
151 }
152
153 return $exploded;
154 }
155
163 public static function buildRange(array $pRange)
164 {
165 // Verify range
166 if (empty($pRange) || !is_array($pRange[0])) {
167 throw new Exception('Range does not contain any information');
168 }
169
170 // Build range
171 $counter = count($pRange);
172 for ($i = 0; $i < $counter; ++$i) {
173 $pRange[$i] = implode(':', $pRange[$i]);
174 }
175
176 return implode(',', $pRange);
177 }
178
187 public static function rangeBoundaries($pRange)
188 {
189 // Ensure $pRange is a valid range
190 if (empty($pRange)) {
191 $pRange = self::DEFAULT_RANGE;
192 }
193
194 // Uppercase coordinate
195 $pRange = strtoupper($pRange);
196
197 // Extract range
198 if (strpos($pRange, ':') === false) {
199 $rangeA = $rangeB = $pRange;
200 } else {
201 [$rangeA, $rangeB] = explode(':', $pRange);
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
222 public static function rangeDimension($pRange)
223 {
224 // Calculate range outer borders
225 [$rangeStart, $rangeEnd] = self::rangeBoundaries($pRange);
226
227 return [($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1)];
228 }
229
238 public static function getRangeBoundaries($pRange)
239 {
240 // Ensure $pRange is a valid range
241 if (empty($pRange)) {
242 $pRange = self::DEFAULT_RANGE;
243 }
244
245 // Uppercase coordinate
246 $pRange = strtoupper($pRange);
247
248 // Extract range
249 if (strpos($pRange, ':') === false) {
250 $rangeA = $rangeB = $pRange;
251 } else {
252 [$rangeA, $rangeB] = explode(':', $pRange);
253 }
254
255 return [self::coordinateFromString($rangeA), self::coordinateFromString($rangeB)];
256 }
257
265 public static function columnIndexFromString($pString)
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[$pString])) {
273 return $indexCache[$pString];
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 $pString
286 // for improved performance
287 if (isset($pString[0])) {
288 if (!isset($pString[1])) {
289 $indexCache[$pString] = $columnLookup[$pString];
290
291 return $indexCache[$pString];
292 } elseif (!isset($pString[2])) {
293 $indexCache[$pString] = $columnLookup[$pString[0]] * 26 + $columnLookup[$pString[1]];
294
295 return $indexCache[$pString];
296 } elseif (!isset($pString[3])) {
297 $indexCache[$pString] = $columnLookup[$pString[0]] * 676 + $columnLookup[$pString[1]] * 26 + $columnLookup[$pString[2]];
298
299 return $indexCache[$pString];
300 }
301 }
302
303 throw new Exception('Column string index can not be ' . ((isset($pString[0])) ? 'longer than 3 characters' : 'empty'));
304 }
305
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
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
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
463 public static function mergeRangesInCollection(array $pCoordCollection)
464 {
465 $hashedValues = [];
466 $mergedCoordCollection = [];
467
468 foreach ($pCoordCollection 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
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
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}
An exception for terminatinating execution or to throw for unit testing.
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static absoluteCoordinate($pCoordinateString)
Make string coordinate absolute.
Definition: Coordinate.php:111
static getCellBlocksFromRangeString($rangeString)
Get the individual cell blocks from a range string, removing any $ characters.
Definition: Coordinate.php:540
static sortCellReferenceArray(array $cellList)
Definition: Coordinate.php:379
static getRangeBoundaries($pRange)
Calculate range boundaries.
Definition: Coordinate.php:238
static coordinateFromString($pCoordinateString)
Coordinate from string.
Definition: Coordinate.php:32
static coordinateIsRange($coord)
Checks if a coordinate represents a range of cells.
Definition: Coordinate.php:69
static processRangeSetOperators(array $operators, array $cells)
Definition: Coordinate.php:359
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
Definition: Coordinate.php:52
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static mergeRangesInCollection(array $pCoordCollection)
Convert an associative array of single cell coordinates to values to an associative array of cell ran...
Definition: Coordinate.php:463
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
static validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow)
Check that the given range is valid, i.e.
Definition: Coordinate.php:564
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
static buildRange(array $pRange)
Build range from coordinate strings.
Definition: Coordinate.php:163
static rangeDimension($pRange)
Calculate range dimension.
Definition: Coordinate.php:222
static getReferencesForCellBlock($cellBlock)
Get all cell references for an individual cell block.
Definition: Coordinate.php:399
static rangeBoundaries($pRange)
Calculate range boundaries.
Definition: Coordinate.php:187
static extractAllCellReferencesInRange($cellRange)
Extract all cell references in range, which may be comprised of multiple cell ranges.
Definition: Coordinate.php:338
static absoluteReference($pCoordinateString)
Make string row, column or cell coordinate absolute.
Definition: Coordinate.php:82
$i
Definition: disco.tpl.php:19
$row
echo;exit;}function LogoutNotification($SessionID){ global $ilDB;$q="SELECT session_id, data FROM usr_session WHERE expires > (\w+)\|/" PREG_SPLIT_NO_EMPTY PREG_SPLIT_DELIM_CAPTURE