Coordinate::rangeBoundaries() - Code Metrics - Inspection of "PHP 8.1 on Scrutinizer" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( dcb10e...d87ef3 )

by Adrien

created 2022-12-02 14:15 UTC

Coordinate::rangeBoundaries() B

↳ Parent: Coordinate

Complexity

Conditions	7
Paths	16

Size

Total Lines	36
Code Lines	18

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	17
CRAP Score	7.0084

Importance

Changes

Metric	Value
eloc	18
c	0
b	0
f	0
dl	0
loc	36
ccs	17
cts	18
cp	0.9444
rs	8.8333
cc	7
nc	16
nop	1
crap	7.0084

<?php

namespace PhpOffice\PhpSpreadsheet\Cell;

use PhpOffice\PhpSpreadsheet\Calculation\Functions;
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
{
    public const A1_COORDINATE_REGEX = '/^(?<col>\$?[A-Z]{1,3})(?<row>\$?\d{1,7})$/i';

    /**
     * Default range variable constant.
     *
     * @var string
     */
    const DEFAULT_RANGE = 'A1:A1';

    /**
     * Convert string coordinate to [0 => int column index, 1 => int row index].
     *
     * @param string $cellAddress eg: 'A1'
     *
     * @return array{0: string, 1: string} Array containing column and row (indexes 0 and 1)
     */
    public static function coordinateFromString($cellAddress): array
    {
        if (preg_match(self::A1_COORDINATE_REGEX, $cellAddress, $matches)) {
            return [$matches['col'], $matches['row']];
        } elseif (self::coordinateIsRange($cellAddress)) {
            throw new Exception('Cell coordinate string can not be a range of cells');
        } elseif ($cellAddress == '') {
            throw new Exception('Cell coordinate can not be zero-length string');
        }

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

    /**
     * Convert string coordinate to [0 => int column index, 1 => int row index, 2 => string column string].
     *
     * @param string $coordinates eg: 'A1', '$B$12'
     *
     * @return array{0: int, 1: int, 2: string} Array containing column and row index, and column string
     */
    public static function indexesFromString(string $coordinates): array
    {
        [$column, $row] = self::coordinateFromString($coordinates);
        $column = ltrim($column, '$');

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

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

    /**
     * Make string row, column or cell coordinate absolute.
     *
     * @param string $cellAddress 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($cellAddress)
    {
        if (self::coordinateIsRange($cellAddress)) {
            throw new Exception('Cell coordinate string can not be a range of cells');
        }

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

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

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

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

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

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

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

    /**
     * Split range into coordinate strings.
     *
     * @param string $range 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($range)
    {
        // Ensure $pRange is a valid range
        if (empty($range)) {
            $range = self::DEFAULT_RANGE;
        }

        $exploded = explode(',', $range);
        $counter = count($exploded);
        for ($i = 0; $i < $counter; ++$i) {
            // @phpstan-ignore-next-line
            $exploded[$i] = explode(':', $exploded[$i]);
        }

        return $exploded;
    }

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

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

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

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

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

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

        if (is_numeric($rangeA) && is_numeric($rangeB)) {
            $rangeA = 'A' . $rangeA;
            $rangeB = AddressRange::MAX_COLUMN . $rangeB;
        }

        if (ctype_alpha($rangeA) && ctype_alpha($rangeB)) {
            $rangeA = $rangeA . '1';
            $rangeB = $rangeB . AddressRange::MAX_ROW;
        }

        // 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 $range Cell range, Single Cell, Row/Column Range (e.g. A1:A1, B2, B:C, 2:3)
     *
     * @return array Range dimension (width, height)
     */
    public static function rangeDimension($range)
    {
        // Calculate range outer borders
        [$rangeStart, $rangeEnd] = self::rangeBoundaries($range);

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

    /**
     * Calculate range boundaries.
     *
     * @param string $range Cell range, Single Cell, Row/Column Range (e.g. A1:A1, B2, B:C, 2:3)
     *
     * @return array Range coordinates [Start Cell, End Cell]
     *                    where Start Cell and End Cell are arrays [Column ID, Row Number]
     */
    public static function getRangeBoundaries($range)
    {
        [$rangeA, $rangeB] = self::rangeBoundaries($range);

        return [
            [self::stringFromColumnIndex($rangeA[0]), $rangeA[1]],
            [self::stringFromColumnIndex($rangeB[0]), $rangeB[1]],
        ];
    }

    /**
     * Column index from string.
     *
     * @param string $columnAddress eg 'A'
     *
     * @return int Column index (A = 1)
     */
    public static function columnIndexFromString($columnAddress)
    {
        //    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[$columnAddress])) {
            return $indexCache[$columnAddress];
        }
        //    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 $columnAddress for improved performance
        if (isset($columnAddress[0])) {
            if (!isset($columnAddress[1])) {
                $indexCache[$columnAddress] = $columnLookup[$columnAddress];

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

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

                return $indexCache[$columnAddress];
            }
        }

        throw new Exception(
            'Column string index can not be ' . ((isset($columnAddress[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 = [];
        static $lookupCache = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ';

        if (!isset($indexCache[$columnIndex])) {
            $indexValue = $columnIndex;
            $base26 = '';
            do {
                $characterValue = ($indexValue % 26) ?: 26;
                $indexValue = ($indexValue - $characterValue) / 26;
                $base26 = $lookupCache[$characterValue] . $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
    {
        if (substr_count($cellRange, '!') > 1) {
            throw new Exception('3-D Range References are not supported');
        }

        [$worksheet, $cellRange] = Worksheet::extractSheetTitle($cellRange, true);
        $quoted = '';
        if ($worksheet > '') {
            $quoted = Worksheet::nameRequiresQuotes($worksheet) ? "'" : '';
            if (substr($worksheet, 0, 1) === "'" && substr($worksheet, -1, 1) === "'") {
                $worksheet = substr($worksheet, 1, -1);
            }
            $worksheet = str_replace("'", "''", $worksheet);
        }
        [$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);

        return array_map(
            function ($cellAddress) use ($worksheet, $quoted) {
                return ($worksheet !== '') ? "{$quoted}{$worksheet}{$quoted}!{$cellAddress}" : $cellAddress;
            },
            self::sortCellReferenceArray($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 $coordinate) {
            $column = '';
            $row = 0;
            sscanf($coordinate, '%[A-Z]%d', $column, $row);
            $key = (--$row * 16384) + self::columnIndexFromString((string) $column);
            $sortKeys[$key] = $coordinate;
        }
        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 $coordinateCollection associative array mapping coordinates to values
     *
     * @return array associative array mapping coordinate ranges to valuea
     */
    public static function mergeRangesInCollection(array $coordinateCollection)
    {
        $hashedValues = [];
        $mergedCoordCollection = [];

        foreach ($coordinateCollection 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);
        /** @phpstan-ignore-next-line */
        $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\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		* Convert string coordinate to [0 => int column index, 1 => int row index].
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	9981	public static function coordinateFromString($cellAddress): array
34		{
35	9981	if (preg_match(self::A1_COORDINATE_REGEX, $cellAddress, $matches)) {
36	9962	return [$matches['col'], $matches['row']];
37	21	} elseif (self::coordinateIsRange($cellAddress)) {
38	1	throw new Exception('Cell coordinate string can not be a range of cells');
39	20	} elseif ($cellAddress == '') {
40	2	throw new Exception('Cell coordinate can not be zero-length string');
41		}
42
43	18	throw new Exception('Invalid cell coordinate ' . $cellAddress);
44		}
45
46		/**
47		* Convert string coordinate to [0 => int column index, 1 => int row index, 2 => string column string].
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	9737	public static function indexesFromString(string $coordinates): array
54		{
55	9737	[$column, $row] = self::coordinateFromString($coordinates);
56	9732	$column = ltrim($column, '$');
57
58		return [
59	9732	self::columnIndexFromString($column),
60	9732	(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	9767	public static function coordinateIsRange($cellAddress)
73		{
74	9767	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	20	public static function absoluteReference($cellAddress)
86		{
87	20	if (self::coordinateIsRange($cellAddress)) {
88	1	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	19	[$worksheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
93	19	if ($worksheet > '') {
94	8	$worksheet .= '!';
95		}
96
97		// Create absolute coordinate
98	19	$cellAddress = "$cellAddress";
99	19	if (ctype_digit($cellAddress)) {
100	2	return $worksheet . '$' . $cellAddress;
101	17	} elseif (ctype_alpha($cellAddress)) {
102	2	return $worksheet . '$' . strtoupper($cellAddress);
103		}
104
105	15	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	206	public static function absoluteCoordinate($cellAddress)
116		{
117	206	if (self::coordinateIsRange($cellAddress)) {
118	1	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	205	[$worksheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
123	205	if ($worksheet > '') {
124	6	$worksheet .= '!';
125		}
126
127		// Create absolute coordinate
128	205	[$column, $row] = self::coordinateFromString($cellAddress);
129	205	$column = ltrim($column, '$');
130	205	$row = ltrim($row, '$');
131
132	205	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	1487	public static function splitRange($range)
145		{
146		// Ensure $pRange is a valid range
147	1487	if (empty($range)) {
148		$range = self::DEFAULT_RANGE;
149		}
150
151	1487	$exploded = explode(',', $range);
152	1487	$counter = count($exploded);
153	1487	for ($i = 0; $i < $counter; ++$i) {
154		// @phpstan-ignore-next-line
155	1487	$exploded[$i] = explode(':', $exploded[$i]);
156		}
157
158	1487	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	39	public static function buildRange(array $range)
169		{
170		// Verify range
171	39	if (empty($range) \|\| !is_array($range[0])) {
172	1	throw new Exception('Range does not contain any information');
173		}
174
175		// Build range
176	38	$counter = count($range);
177	38	for ($i = 0; $i < $counter; ++$i) {
178	38	$range[$i] = implode(':', $range[$i]);
179		}
180
181	38	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	617	public static function rangeBoundaries(string $range): array
193		{
194		// Ensure $pRange is a valid range
195	617	if (empty($range)) {
196		$range = self::DEFAULT_RANGE;
197		}
198
199		// Uppercase coordinate
200	617	$range = strtoupper($range);
201
202		// Extract range
203	617	if (strpos($range, ':') === false) {
204	58	$rangeA = $rangeB = $range;
205		} else {
206	604	[$rangeA, $rangeB] = explode(':', $range);
207		}
208
209	617	if (is_numeric($rangeA) && is_numeric($rangeB)) {
210	4	$rangeA = 'A' . $rangeA;
211	4	$rangeB = AddressRange::MAX_COLUMN . $rangeB;
212		}
213
214	617	if (ctype_alpha($rangeA) && ctype_alpha($rangeB)) {
215	4	$rangeA = $rangeA . '1';
216	4	$rangeB = $rangeB . AddressRange::MAX_ROW;
217		}
218
219		// Calculate range outer borders
220	617	$rangeStart = self::coordinateFromString($rangeA);
221	617	$rangeEnd = self::coordinateFromString($rangeB);
222
223		// Translate column into index
224	617	$rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
225	617	$rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);
226
227	617	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	133	public static function rangeDimension($range)
238		{
239		// Calculate range outer borders
240	133	[$rangeStart, $rangeEnd] = self::rangeBoundaries($range);
241
242	133	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	34	public static function getRangeBoundaries($range)
254		{
255	34	[$rangeA, $rangeB] = self::rangeBoundaries($range);
256
257		return [
258	34	[self::stringFromColumnIndex($rangeA[0]), $rangeA[1]],
259	34	[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	10056	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	10056	if (isset($indexCache[$columnAddress])) {
278	10043	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	292	if (isset($columnAddress[0])) {
295	291	if (!isset($columnAddress[1])) {
296	271	$indexCache[$columnAddress] = $columnLookup[$columnAddress];
297
298	271	return $indexCache[$columnAddress];
299	21	} elseif (!isset($columnAddress[2])) {
300	13	$indexCache[$columnAddress] = $columnLookup[$columnAddress[0]] * 26
301	13	+ $columnLookup[$columnAddress[1]];
302
303	13	return $indexCache[$columnAddress];
304	9	} elseif (!isset($columnAddress[3])) {
305	8	$indexCache[$columnAddress] = $columnLookup[$columnAddress[0]] * 676
306	8	+ $columnLookup[$columnAddress[1]] * 26
307	8	+ $columnLookup[$columnAddress[2]];
308
309	8	return $indexCache[$columnAddress];
310		}
311		}
312
313	2	throw new Exception(
314	2	'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	2696	public static function stringFromColumnIndex($columnIndex)
326		{
327		static $indexCache = [];
328		static $lookupCache = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ';
329
330	2696	if (!isset($indexCache[$columnIndex])) {
331	234	$indexValue = $columnIndex;
332	234	$base26 = '';
333		do {
334	234	$characterValue = ($indexValue % 26) ?: 26;
335	234	$indexValue = ($indexValue - $characterValue) / 26;
336	234	$base26 = $lookupCache[$characterValue] . $base26;
337	234	} while ($indexValue > 0);
338	234	$indexCache[$columnIndex] = $base26;
339		}
340
341	2696	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	5510	public static function extractAllCellReferencesInRange($cellRange): array
352		{
353	5510	if (substr_count($cellRange, '!') > 1) {
354		throw new Exception('3-D Range References are not supported');
355		}
356
357	5510	[$worksheet, $cellRange] = Worksheet::extractSheetTitle($cellRange, true);
358	5510	$quoted = '';
359	5510	if ($worksheet > '') {
360	4	$quoted = Worksheet::nameRequiresQuotes($worksheet) ? "'" : '';
361	4	if (substr($worksheet, 0, 1) === "'" && substr($worksheet, -1, 1) === "'") {
362	2	$worksheet = substr($worksheet, 1, -1);
363		}
364	4	$worksheet = str_replace("'", "''", $worksheet);
365		}
366	5510	[$ranges, $operators] = self::getCellBlocksFromRangeString($cellRange);
367
368	5510	$cells = [];
369	5510	foreach ($ranges as $range) {
370	5510	$cells[] = self::getReferencesForCellBlock($range);
371		}
372
373	5506	$cells = self::processRangeSetOperators($operators, $cells);
374
375	5506	if (empty($cells)) {
376		return [];
377		}
378
379	5506	$cellList = array_merge(...$cells);
380
381	5506	return array_map(
382	5506	function ($cellAddress) use ($worksheet, $quoted) {
383	5505	return ($worksheet !== '') ? "{$quoted}{$worksheet}{$quoted}!{$cellAddress}" : $cellAddress;
384		},
385	5506	self::sortCellReferenceArray($cellList)
386		);
387		}
388
389	5506	private static function processRangeSetOperators(array $operators, array $cells): array
390		{
391	5506	$operatorCount = count($operators);
392	5506	for ($offset = 0; $offset < $operatorCount; ++$offset) {
393	7	$operator = $operators[$offset];
394	7	if ($operator !== ' ') {
395	4	continue;
396		}
397
398	3	$cells[$offset] = array_intersect($cells[$offset], $cells[$offset + 1]);
399	3	unset($operators[$offset], $cells[$offset + 1]);
400	3	$operators = array_values($operators);
401	3	$cells = array_values($cells);
402	3	--$offset;
403	3	--$operatorCount;
404		}
405
406	5506	return $cells;
407		}
408
409	5506	private static function sortCellReferenceArray(array $cellList): array
410		{
411		// Sort the result by column and row
412	5506	$sortKeys = [];
413	5506	foreach ($cellList as $coordinate) {
414	5505	$column = '';
415	5505	$row = 0;
416	5505	sscanf($coordinate, '%[A-Z]%d', $column, $row);
417	5505	$key = (--$row * 16384) + self::columnIndexFromString((string) $column);
418	5505	$sortKeys[$key] = $coordinate;
419		}
420	5506	ksort($sortKeys);
421
422	5506	return array_values($sortKeys);
423		}
424
425		/**
426		* Get all cell references for an individual cell block.
427		*
428		* @param string $cellBlock A cell range e.g. A4:B5
429		*
430		* @return array All individual cells in that range
431		*/
432	5510	private static function getReferencesForCellBlock($cellBlock)
433		{
434	5510	$returnValue = [];
435
436		// Single cell?
437	5510	if (!self::coordinateIsRange($cellBlock)) {
438	5451	return (array) $cellBlock;
439		}
440
441		// Range...
442	958	$ranges = self::splitRange($cellBlock);
443	958	foreach ($ranges as $range) {
444		// Single cell?
445	958	if (!isset($range[1])) {
446		$returnValue[] = $range[0];
447
448		continue;
449		}
450
451		// Range...
452	958	[$rangeStart, $rangeEnd] = $range;
453	958	[$startColumn, $startRow] = self::coordinateFromString($rangeStart);
454	958	[$endColumn, $endRow] = self::coordinateFromString($rangeEnd);
455	958	$startColumnIndex = self::columnIndexFromString($startColumn);
456	958	$endColumnIndex = self::columnIndexFromString($endColumn);
457	958	++$endColumnIndex;
458
459		// Current data
460	958	$currentColumnIndex = $startColumnIndex;
461	958	$currentRow = $startRow;
462
463	958	self::validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow);
464
465		// Loop cells
466	954	while ($currentColumnIndex < $endColumnIndex) {
467	954	while ($currentRow <= $endRow) {
468	954	$returnValue[] = self::stringFromColumnIndex($currentColumnIndex) . $currentRow;
469	954	++$currentRow;
470		}
471	954	++$currentColumnIndex;
472	954	$currentRow = $startRow;
473		}
474		}
475
476	954	return $returnValue;
477		}
478
479		/**
480		* Convert an associative array of single cell coordinates to values to an associative array
481		* of cell ranges to values. Only adjacent cell coordinates with the same
482		* value will be merged. If the value is an object, it must implement the method getHashCode().
483		*
484		* For example, this function converts:
485		*
486		* [ 'A1' => 'x', 'A2' => 'x', 'A3' => 'x', 'A4' => 'y' ]
487		*
488		* to:
489		*
490		* [ 'A1:A3' => 'x', 'A4' => 'y' ]
491		*
492		* @param array $coordinateCollection associative array mapping coordinates to values
493		*
494		* @return array associative array mapping coordinate ranges to valuea
495		*/
496	11	public static function mergeRangesInCollection(array $coordinateCollection)
497		{
498	11	$hashedValues = [];
499	11	$mergedCoordCollection = [];
500
501	11	foreach ($coordinateCollection as $coord => $value) {
502	11	if (self::coordinateIsRange($coord)) {
503	2	$mergedCoordCollection[$coord] = $value;
504
505	2	continue;
506		}
507
508	10	[$column, $row] = self::coordinateFromString($coord);
509	10	$row = (int) (ltrim($row, '$'));
510	10	$hashCode = $column . '-' . (is_object($value) ? $value->getHashCode() : $value);
511
512	10	if (!isset($hashedValues[$hashCode])) {
513	10	$hashedValues[$hashCode] = (object) [
514		'value' => $value,
515		'col' => $column,
516		'rows' => [$row],
517		];
518		} else {
519	6	$hashedValues[$hashCode]->rows[] = $row;
520		}
521		}
522
523	11	ksort($hashedValues);
524
525	11	foreach ($hashedValues as $hashedValue) {
526	10	sort($hashedValue->rows);
527	10	$rowStart = null;
528	10	$rowEnd = null;
529	10	$ranges = [];
530
531	10	foreach ($hashedValue->rows as $row) {
532	10	if ($rowStart === null) {
533	10	$rowStart = $row;
534	10	$rowEnd = $row;
535	6	} elseif ($rowEnd === $row - 1) {
536	6	$rowEnd = $row;
537		} else {
538	1	if ($rowStart == $rowEnd) {
539		$ranges[] = $hashedValue->col . $rowStart;
540		} else {
541	1	$ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
542		}
543
544	1	$rowStart = $row;
545	1	$rowEnd = $row;
546		}
547		}
548
549	10	if ($rowStart !== null) {
550	10	if ($rowStart == $rowEnd) {
551	8	$ranges[] = $hashedValue->col . $rowStart;
552		} else {
553	5	$ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
554		}
555		}
556
557	10	foreach ($ranges as $range) {
558	10	$mergedCoordCollection[$range] = $hashedValue->value;
559		}
560		}
561
562	11	return $mergedCoordCollection;
563		}
564
565		/**
566		* Get the individual cell blocks from a range string, removing any $ characters.
567		* then splitting by operators and returning an array with ranges and operators.
568		*
569		* @param string $rangeString
570		*
571		* @return array[]
572		*/
573	5510	private static function getCellBlocksFromRangeString($rangeString)
574		{
575	5510	$rangeString = str_replace('$', '', strtoupper($rangeString));
576
577		// split range sets on intersection (space) or union (,) operators
578	5510	$tokens = preg_split('/([ ,])/', $rangeString, -1, PREG_SPLIT_DELIM_CAPTURE);
579		/** @phpstan-ignore-next-line */
580	5510	$split = array_chunk($tokens, 2);
581	5510	$ranges = array_column($split, 0);
582	5510	$operators = array_column($split, 1);
583
584	5510	return [$ranges, $operators];
585		}
586
587		/**
588		* Check that the given range is valid, i.e. that the start column and row are not greater than the end column and
589		* row.
590		*
591		* @param string $cellBlock The original range, for displaying a meaningful error message
592		* @param int $startColumnIndex
593		* @param int $endColumnIndex
594		* @param int $currentRow
595		* @param int $endRow
596		*/
597	958	private static function validateRange($cellBlock, $startColumnIndex, $endColumnIndex, $currentRow, $endRow): void
598		{
599	958	if ($startColumnIndex >= $endColumnIndex \|\| $currentRow > $endRow) {
600	4	throw new Exception('Invalid range: "' . $cellBlock . '"');
601		}
602		}
603		}
604

PHPOffice / PhpSpreadsheet

Push — master ( dcb10e...d87ef3 )

Coordinate::rangeBoundaries() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like