Coordinate::sortCellReferenceArray() - Code Metrics - Inspection of "PHPStan Level 2" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 42761f...d02352 )

by Adrien

created 2021-04-04 13:12 UTC

Coordinate::sortCellReferenceArray() A

↳ Parent: Coordinate

Complexity

Conditions	2
Paths	2

Size

Total Lines	11
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	7
CRAP Score	2

Importance

Changes

Metric	Value
cc	2
eloc	6
nc	2
nop	1
dl	0
loc	11
ccs	7
cts	7
cp	1
crap	2
rs	10
c	0
b	0
f	0

<?php

namespace PhpOffice\PhpSpreadsheet\Cell;

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * Helper class to manipulate cell coordinates.
 *
 * Columns indexes and rows are always based on 1, **not** on 0. This match the behavior
 * that Excel users are used to, and also match the Excel functions `COLUMN()` and `ROW()`.
 */
abstract class Coordinate
{
    /**
     * Default range variable constant.
     *
     * @var string
     */
    const DEFAULT_RANGE = 'A1:A1';

    /**
     * Coordinate from string.
     *
     * @param string $pCoordinateString eg: 'A1'
     *
     * @return array{0: string, 1: string} Array containing column and row (indexes 0 and 1)
     */
    public static function coordinateFromString($pCoordinateString)
    {
        if (preg_match('/^([$]?[A-Z]{1,3})([$]?\\d{1,7})$/', $pCoordinateString, $matches)) {
            return [$matches[1], $matches[2]];
        } elseif (self::coordinateIsRange($pCoordinateString)) {
            throw new Exception('Cell coordinate string can not be a range of cells');
        } elseif ($pCoordinateString == '') {
            throw new Exception('Cell coordinate can not be zero-length string');
        }

        throw new Exception('Invalid cell coordinate ' . $pCoordinateString);
    }

    /**
     * Get indexes from a string coordinates.
     *
     * @param string $coordinates eg: 'A1', '$B$12'
     *
     * @return array{0: int, 1: int} Array containing column index and row index (indexes 0 and 1)
     */
    public static function indexesFromString(string $coordinates): array
    {
        [$col, $row] = self::coordinateFromString($coordinates);

        return [
            self::columnIndexFromString(ltrim($col, '$')),
            (int) ltrim($row, '$'),
        ];
    }

    /**
     * Checks if a coordinate represents a range of cells.
     *
     * @param string $coord eg: 'A1' or 'A1:A2' or 'A1:A2,C1:C2'
     *
     * @return bool Whether the coordinate represents a range of cells
     */
    public static function coordinateIsRange($coord)
    {
        return (strpos($coord, ':') !== false) || (strpos($coord, ',') !== false);
    }

    /**
     * Make string row, column or cell coordinate absolute.
     *
     * @param string $pCoordinateString e.g. 'A' or '1' or 'A1'
     *                    Note that this value can be a row or column reference as well as a cell reference
     *
     * @return string Absolute coordinate        e.g. '$A' or '$1' or '$A$1'
     */
    public static function absoluteReference($pCoordinateString)
    {
        if (self::coordinateIsRange($pCoordinateString)) {
            throw new Exception('Cell coordinate string can not be a range of cells');
        }

        // Split out any worksheet name from the reference
        [$worksheet, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
        if ($worksheet > '') {
            $worksheet .= '!';
        }

        // Create absolute coordinate
        if (ctype_digit($pCoordinateString)) {
            return $worksheet . '$' . $pCoordinateString;
        } elseif (ctype_alpha($pCoordinateString)) {
            return $worksheet . '$' . strtoupper($pCoordinateString);
        }

        return $worksheet . self::absoluteCoordinate($pCoordinateString);
    }

    /**
     * Make string coordinate absolute.
     *
     * @param string $pCoordinateString e.g. 'A1'
     *
     * @return string Absolute coordinate        e.g. '$A$1'
     */
    public static function absoluteCoordinate($pCoordinateString)
    {
        if (self::coordinateIsRange($pCoordinateString)) {
            throw new Exception('Cell coordinate string can not be a range of cells');
        }

        // Split out any worksheet name from the coordinate
        [$worksheet, $pCoordinateString] = Worksheet::extractSheetTitle($pCoordinateString, true);
        if ($worksheet > '') {
            $worksheet .= '!';
        }

        // Create absolute coordinate
        [$column, $row] = self::coordinateFromString($pCoordinateString);
        $column = ltrim($column, '$');
        $row = ltrim($row, '$');

        return $worksheet . '$' . $column . '$' . $row;
    }

    /**
     * Split range into coordinate strings.
     *
     * @param string $pRange e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
     *
     * @return array Array containing one or more arrays containing one or two coordinate strings
     *                                e.g. ['B4','D9'] or [['B4','D9'], ['H2','O11']]
     *                                        or ['B4']
     */
    public static function splitRange($pRange)
    {
        // Ensure $pRange is a valid range
        if (empty($pRange)) {
            $pRange = self::DEFAULT_RANGE;
        }

        $exploded = explode(',', $pRange);
        $counter = count($exploded);
        for ($i = 0; $i < $counter; ++$i) {
            $exploded[$i] = explode(':', $exploded[$i]);
        }

        return $exploded;
    }

    /**
     * Build range from coordinate strings.
     *
     * @param array $pRange Array containg one or more arrays containing one or two coordinate strings
     *
     * @return string String representation of $pRange
     */
    public static function buildRange(array $pRange)
    {
        // Verify range
        if (empty($pRange) || !is_array($pRange[0])) {
            throw new Exception('Range does not contain any information');
        }

        // Build range
        $counter = count($pRange);
        for ($i = 0; $i < $counter; ++$i) {
            $pRange[$i] = implode(':', $pRange[$i]);
        }

        return implode(',', $pRange);
    }

    /**
     * Calculate range boundaries.
     *
     * @param string $pRange Cell range (e.g. A1:A1)
     *
     * @return array Range coordinates [Start Cell, End Cell]
     *                    where Start Cell and End Cell are arrays (Column Number, Row Number)
     */
    public static function rangeBoundaries($pRange)
    {
        // Ensure $pRange is a valid range
        if (empty($pRange)) {
            $pRange = self::DEFAULT_RANGE;
        }

        // Uppercase coordinate
        $pRange = strtoupper($pRange);

        // Extract range
        if (strpos($pRange, ':') === false) {
            $rangeA = $rangeB = $pRange;
        } else {
            [$rangeA, $rangeB] = explode(':', $pRange);
        }

        // Calculate range outer borders
        $rangeStart = self::coordinateFromString($rangeA);
        $rangeEnd = self::coordinateFromString($rangeB);

        // Translate column into index
        $rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
        $rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);

        return [$rangeStart, $rangeEnd];
    }

    /**
     * Calculate range dimension.
     *
     * @param string $pRange Cell range (e.g. A1:A1)
     *
     * @return array Range dimension (width, height)
     */
    public static function rangeDimension($pRange)
    {
        // Calculate range outer borders
        [$rangeStart, $rangeEnd] = self::rangeBoundaries($pRange);

        return [($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1)];
    }

    /**
     * Calculate range boundaries.
     *
     * @param string $pRange Cell range (e.g. A1:A1)
     *
     * @return array Range coordinates [Start Cell, End Cell]
     *                    where Start Cell and End Cell are arrays [Column ID, Row Number]
     */
    public static function getRangeBoundaries($pRange)
    {
        // Ensure $pRange is a valid range
        if (empty($pRange)) {
            $pRange = self::DEFAULT_RANGE;
        }

        // Uppercase coordinate
        $pRange = strtoupper($pRange);

        // Extract range
        if (strpos($pRange, ':') === false) {
            $rangeA = $rangeB = $pRange;
        } else {
            [$rangeA, $rangeB] = explode(':', $pRange);
        }

        return [self::coordinateFromString($rangeA), self::coordinateFromString($rangeB)];
    }

    /**
     * Column index from string.
     *
     * @param string $pString eg 'A'
     *
     * @return int Column index (A = 1)
     */
    public static function columnIndexFromString($pString)
    {
        //    Using a lookup cache adds a slight memory overhead, but boosts speed
        //    caching using a static within the method is faster than a class static,
        //        though it's additional memory overhead
        static $indexCache = [];

        if (isset($indexCache[$pString])) {
            return $indexCache[$pString];
        }
        //    It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array rather than use ord()
        //        and make it case insensitive to get rid of the strtoupper() as well. Because it's a static, there's no significant
        //        memory overhead either
        static $columnLookup = [
            '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,
            '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,
            '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,
            '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,
        ];

        //    We also use the language construct isset() rather than the more costly strlen() function to match the length of $pString
        //        for improved performance
        if (isset($pString[0])) {
            if (!isset($pString[1])) {
                $indexCache[$pString] = $columnLookup[$pString];

                return $indexCache[$pString];
            } elseif (!isset($pString[2])) {
                $indexCache[$pString] = $columnLookup[$pString[0]] * 26 + $columnLookup[$pString[1]];

                return $indexCache[$pString];
            } elseif (!isset($pString[3])) {
                $indexCache[$pString] = $columnLookup[$pString[0]] * 676 + $columnLookup[$pString[1]] * 26 + $columnLookup[$pString[2]];

                return $indexCache[$pString];
            }
        }

        throw new Exception('Column string index can not be ' . ((isset($pString[0])) ? 'longer than 3 characters' : 'empty'));
    }

    /**
     * String from column index.
     *
     * @param int $columnIndex Column index (A = 1)
     *
     * @return string
     */
    public static function stringFromColumnIndex($columnIndex)
    {
        static $indexCache = [];

        if (!isset($indexCache[$columnIndex])) {
            $indexValue = $columnIndex;
            $base26 = null;
            do {
                $characterValue = ($indexValue % 26) ?: 26;
                $indexValue = ($indexValue - $characterValue) / 26;
                $base26 = chr($characterValue + 64) . ($base26 ?: '');
            } while ($indexValue > 0);
            $indexCache[$columnIndex] = $base26;
        }

        return $indexCache[$columnIndex];
    }

    /**
     * Extract all cell references in range, which may be comprised of multiple cell ranges.
     *
     * @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'
     *
     * @return array Array containing single cell references
     */
    public static function extractAllCellReferencesInRange($cellRange): array
    {
        [$ranges, $operators] = self::getCellBlocksFromRangeString($cellRange);

        $cells = [];
        foreach ($ranges as $range) {
            $cells[] = self::getReferencesForCellBlock($range);
        }

        $cells = self::processRangeSetOperators($operators, $cells);

        if (empty($cells)) {
            return [];
        }

        $cellList = array_merge(...$cells);
        $cellList = self::sortCellReferenceArray($cellList);

        return $cellList;
    }

    private static function processRangeSetOperators(array $operators, array $cells): array
    {
        $operatorCount = count($operators);
        for ($offset = 0; $offset < $operatorCount; ++$offset) {
            $operator = $operators[$offset];
            if ($operator !== ' ') {
                continue;
            }

            $cells[$offset] = array_intersect($cells[$offset], $cells[$offset + 1]);
            unset($operators[$offset], $cells[$offset + 1]);
            $operators = array_values($operators);
            $cells = array_values($cells);
            --$offset;
            --$operatorCount;
        }

        return $cells;
    }

    private static function sortCellReferenceArray(array $cellList): array
    {
        //    Sort the result by column and row
        $sortKeys = [];
        foreach ($cellList as $coord) {
            [$column, $row] = sscanf($coord, '%[A-Z]%d');
            $sortKeys[sprintf('%3s%09d', $column, $row)] = $coord;
        }
        ksort($sortKeys);

        return array_values($sortKeys);
    }

    /**
     * Get all cell references for an individual cell block.
     *
     * @param string $cellBlock A cell range e.g. A4:B5
     *
     * @return array All individual cells in that range
     */
    private static function getReferencesForCellBlock($cellBlock)
    {
        $returnValue = [];

        // Single cell?
        if (!self::coordinateIsRange($cellBlock)) {
            return (array) $cellBlock;
        }

        // Range...
        $ranges = self::splitRange($cellBlock);
        foreach ($ranges as $range) {
            // Single cell?
            if (!isset($range[1])) {
                $returnValue[] = $range[0];

                continue;
            }

            // Range...
            [$rangeStart, $rangeEnd] = $range;
            [$startColumn, $startRow] = self::coordinateFromString($rangeStart);
            [$endColumn, $endRow] = self::coordinateFromString($rangeEnd);
            $startColumnIndex = self::columnIndexFromString($startColumn);
            $endColumnIndex = self::columnIndexFromString($endColumn);
            ++$endColumnIndex;

            // Current data
            $currentColumnIndex = $startColumnIndex;
            $currentRow = $startRow;

            self::validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow);

            // Loop cells
            while ($currentColumnIndex < $endColumnIndex) {
                while ($currentRow <= $endRow) {
                    $returnValue[] = self::stringFromColumnIndex($currentColumnIndex) . $currentRow;
                    ++$currentRow;
                }
                ++$currentColumnIndex;
                $currentRow = $startRow;
            }
        }

        return $returnValue;
    }

    /**
     * Convert an associative array of single cell coordinates to values to an associative array
     * of cell ranges to values.  Only adjacent cell coordinates with the same
     * value will be merged.  If the value is an object, it must implement the method getHashCode().
     *
     * For example, this function converts:
     *
     *    [ 'A1' => 'x', 'A2' => 'x', 'A3' => 'x', 'A4' => 'y' ]
     *
     * to:
     *
     *    [ 'A1:A3' => 'x', 'A4' => 'y' ]
     *
     * @param array $pCoordCollection associative array mapping coordinates to values
     *
     * @return array associative array mapping coordinate ranges to valuea
     */
    public static function mergeRangesInCollection(array $pCoordCollection)
    {
        $hashedValues = [];
        $mergedCoordCollection = [];

        foreach ($pCoordCollection as $coord => $value) {
            if (self::coordinateIsRange($coord)) {
                $mergedCoordCollection[$coord] = $value;

                continue;
            }

            [$column, $row] = self::coordinateFromString($coord);
            $row = (int) (ltrim($row, '$'));
            $hashCode = $column . '-' . (is_object($value) ? $value->getHashCode() : $value);

            if (!isset($hashedValues[$hashCode])) {
                $hashedValues[$hashCode] = (object) [
                    'value' => $value,
                    'col' => $column,
                    'rows' => [$row],
                ];
            } else {
                $hashedValues[$hashCode]->rows[] = $row;
            }
        }

        ksort($hashedValues);

        foreach ($hashedValues as $hashedValue) {
            sort($hashedValue->rows);
            $rowStart = null;
            $rowEnd = null;
            $ranges = [];

            foreach ($hashedValue->rows as $row) {
                if ($rowStart === null) {
                    $rowStart = $row;
                    $rowEnd = $row;
                } elseif ($rowEnd === $row - 1) {
                    $rowEnd = $row;
                } else {
                    if ($rowStart == $rowEnd) {
                        $ranges[] = $hashedValue->col . $rowStart;
                    } else {
                        $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
                    }

                    $rowStart = $row;
                    $rowEnd = $row;
                }
            }

            if ($rowStart !== null) {
                if ($rowStart == $rowEnd) {
                    $ranges[] = $hashedValue->col . $rowStart;
                } else {
                    $ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
                }
            }

            foreach ($ranges as $range) {
                $mergedCoordCollection[$range] = $hashedValue->value;
            }
        }

        return $mergedCoordCollection;
    }

    /**
     * Get the individual cell blocks from a range string, removing any $ characters.
     *      then splitting by operators and returning an array with ranges and operators.
     *
     * @param string $rangeString
     *
     * @return array[]
     */
    private static function getCellBlocksFromRangeString($rangeString)
    {
        $rangeString = str_replace('$', '', strtoupper($rangeString));

        // split range sets on intersection (space) or union (,) operators
        $tokens = preg_split('/([ ,])/', $rangeString, -1, PREG_SPLIT_DELIM_CAPTURE);
        // separate the range sets and the operators into arrays
        $split = array_chunk($tokens, 2);
        $ranges = array_column($split, 0);
        $operators = array_column($split, 1);

        return [$ranges, $operators];
    }

    /**
     * Check that the given range is valid, i.e. that the start column and row are not greater than the end column and
     * row.
     *
     * @param string $cellBlock The original range, for displaying a meaningful error message
     * @param int $startColumnIndex
     * @param int $endColumnIndex
     * @param int $currentRow
     * @param int $endRow
     */
    private static function validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow): void
    {
        if ($startColumnIndex >= $endColumnIndex || $currentRow > $endRow) {
            throw new Exception('Invalid range: "' . $cellBlock . '"');
        }
    }
}


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

PHPOffice / PhpSpreadsheet

Push — master ( 42761f...d02352 )

Coordinate::sortCellReferenceArray() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like