Coordinate::stringFromColumnIndex() - Code Metrics - Inspection of "Fix cell ranges causing coordinate merge error" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — develop ( 4e0344...49775b )

by Adrien

created 2018-01-22 03:26 UTC

Coordinate::stringFromColumnIndex() B

↳ Parent: Coordinate

Complexity

Conditions	5
Paths	2

Size

Total Lines	16
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	11
CRAP Score	5

Importance

Changes

Metric	Value
cc	5
eloc	11
nc	2
nop	1
dl	0
loc	16
ccs	11
cts	11
cp	1
crap	5
rs	8.8571
c	0
b	0
f	0

<?php

namespace PhpOffice\PhpSpreadsheet\Cell;

use PhpOffice\PhpSpreadsheet\Exception;

/**
 * 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'
     *
     * @throws Exception
     *
     * @return 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);
    }

    /**
     * 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
     *
     * @throws Exception
     *
     * @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 = '';
        $cellAddress = explode('!', $pCoordinateString);
        if (count($cellAddress) > 1) {
            list($worksheet, $pCoordinateString) = $cellAddress;
        }
        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'
     *
     * @throws Exception
     *
     * @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 = '';
        $cellAddress = explode('!', $pCoordinateString);
        if (count($cellAddress) > 1) {
            list($worksheet, $pCoordinateString) = $cellAddress;
        }
        if ($worksheet > '') {
            $worksheet .= '!';
        }

        // Create absolute coordinate
        list($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 containg one or more arrays containing one or two coordinate strings
     *                                e.g. array('B4','D9') or array(array('B4','D9'),array('H2','O11'))
     *                                        or array('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
     *
     * @throws Exception
     *
     * @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
        $imploded = [];

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

        return $imploded;
    }

    /**
     * Calculate range boundaries.
     *
     * @param string $pRange Cell range (e.g. A1:A1)
     *
     * @return array Range coordinates array(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 {
            list($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
        list($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 array(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 {
            list($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.
     *
     * @param string $pRange Range (e.g. A1 or A1:C10 or A1:E10 A20:E25)
     *
     * @return array Array containing single cell references
     */
    public static function extractAllCellReferencesInRange($pRange)
    {
        // Returnvalue
        $returnValue = [];

        // Explode spaces
        $cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
        foreach ($cellBlocks as $cellBlock) {
            // Single cell?
            if (!self::coordinateIsRange($cellBlock)) {
                $returnValue[] = $cellBlock;

                continue;
            }

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

                    continue;
                }

                // Range...
                list($rangeStart, $rangeEnd) = $range;
                sscanf($rangeStart, '%[A-Z]%d', $startCol, $startRow);

                sscanf($rangeEnd, '%[A-Z]%d', $endCol, $endRow);

                ++$endCol;

                // Current data
                $currentCol = $startCol;
                $currentRow = $startRow;

                // Loop cells
                while ($currentCol != $endCol) {
                    while ($currentRow <= $endRow) {
                        $returnValue[] = $currentCol . $currentRow;
                        ++$currentRow;
                    }
                    ++$currentCol;
                    $currentRow = $startRow;
                }
            }
        }

        //    Sort the result by column and row
        $sortKeys = [];
        foreach (array_unique($returnValue) as $coord) {
            sscanf($coord, '%[A-Z]%d', $column, $row);

            $sortKeys[sprintf('%3s%09d', $column, $row)] = $coord;
        }
        ksort($sortKeys);

        // Return value
        return array_values($sortKeys);
    }

    /**
     * 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;
            }

            list($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;
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Cell;
4
5		use PhpOffice\PhpSpreadsheet\Exception;
6
7		/**
8		* Helper class to manipulate cell coordinates.
9		*
10		* Columns indexes and rows are always based on 1, not on 0. This match the behavior
11		* that Excel users are used to, and also match the Excel functions `COLUMN()` and `ROW()`.
12		*/
13		abstract class Coordinate
14		{
15		/**
16		* Default range variable constant.
17		*
18		* @var string
19		*/
20		const DEFAULT_RANGE = 'A1:A1';
21
22		/**
23		* Coordinate from string.
24		*
25		* @param string $pCoordinateString eg: 'A1'
26		*
27		* @throws Exception
28		*
29		* @return string[] Array containing column and row (indexes 0 and 1)
30		*/
31	196	public static function coordinateFromString($pCoordinateString)
32		{
33	196	if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
34	193	return [$matches[1], $matches[2]];
35	4	} elseif (self::coordinateIsRange($pCoordinateString)) {
36	1	throw new Exception('Cell coordinate string can not be a range of cells');
37	3	} elseif ($pCoordinateString == '') {
38	1	throw new Exception('Cell coordinate can not be zero-length string');
39		}
40
41	2	throw new Exception('Invalid cell coordinate ' . $pCoordinateString);
42		}
43
44		/**
45		* Checks if a coordinate represents a range of cells.
46		*
47		* @param string $coord eg: 'A1' or 'A1:A2' or 'A1:A2,C1:C2'
48		*
49		* @return bool Whether the coordinate represents a range of cells
50		*/
51	189	public static function coordinateIsRange($coord)
52		{
53	189	return (strpos($coord, ':') !== false) \|\| (strpos($coord, ',') !== false);
54		}
55
56		/**
57		* Make string row, column or cell coordinate absolute.
58		*
59		* @param string $pCoordinateString e.g. 'A' or '1' or 'A1'
60		* Note that this value can be a row or column reference as well as a cell reference
61		*
62		* @throws Exception
63		*
64		* @return string Absolute coordinate e.g. '$A' or '$1' or '$A$1'
65		*/
66	21	public static function absoluteReference($pCoordinateString)
67		{
68	21	if (self::coordinateIsRange($pCoordinateString)) {
69	1	throw new Exception('Cell coordinate string can not be a range of cells');
70		}
71
72		// Split out any worksheet name from the reference
73	20	$worksheet = '';
74	20	$cellAddress = explode('!', $pCoordinateString);
75	20	if (count($cellAddress) > 1) {
76	8	list($worksheet, $pCoordinateString) = $cellAddress;
77		}
78	20	if ($worksheet > '') {
79	8	$worksheet .= '!';
80		}
81
82		// Create absolute coordinate
83	20	if (ctype_digit($pCoordinateString)) {
84	2	return $worksheet . '$' . $pCoordinateString;
85	18	} elseif (ctype_alpha($pCoordinateString)) {
86	2	return $worksheet . '$' . strtoupper($pCoordinateString);
87		}
88
89	16	return $worksheet . self::absoluteCoordinate($pCoordinateString);
90		}
91
92		/**
93		* Make string coordinate absolute.
94		*
95		* @param string $pCoordinateString e.g. 'A1'
96		*
97		* @throws Exception
98		*
99		* @return string Absolute coordinate e.g. '$A$1'
100		*/
101	32	public static function absoluteCoordinate($pCoordinateString)
102		{
103	32	if (self::coordinateIsRange($pCoordinateString)) {
104	1	throw new Exception('Cell coordinate string can not be a range of cells');
105		}
106
107		// Split out any worksheet name from the coordinate
108	31	$worksheet = '';
109	31	$cellAddress = explode('!', $pCoordinateString);
110	31	if (count($cellAddress) > 1) {
111	6	list($worksheet, $pCoordinateString) = $cellAddress;
112		}
113	31	if ($worksheet > '') {
114	6	$worksheet .= '!';
115		}
116
117		// Create absolute coordinate
118	31	list($column, $row) = self::coordinateFromString($pCoordinateString);
119	31	$column = ltrim($column, '$');
120	31	$row = ltrim($row, '$');
121
122	31	return $worksheet . '$' . $column . '$' . $row;
123		}
124
125		/**
126		* Split range into coordinate strings.
127		*
128		* @param string $pRange e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
129		*
130		* @return array Array containg one or more arrays containing one or two coordinate strings
131		* e.g. array('B4','D9') or array(array('B4','D9'),array('H2','O11'))
132		* or array('B4')
133		*/
134	115	public static function splitRange($pRange)
135		{
136		// Ensure $pRange is a valid range
137	115	if (empty($pRange)) {
138		$pRange = self::DEFAULT_RANGE;
139		}
140
141	115	$exploded = explode(',', $pRange);
142	115	$counter = count($exploded);
143	115	for ($i = 0; $i < $counter; ++$i) {
144	115	$exploded[$i] = explode(':', $exploded[$i]);
145		}
146
147	115	return $exploded;
148		}
149
150		/**
151		* Build range from coordinate strings.
152		*
153		* @param array $pRange Array containg one or more arrays containing one or two coordinate strings
154		*
155		* @throws Exception
156		*
157		* @return string String representation of $pRange
158		*/
159	19	public static function buildRange(array $pRange)
160		{
161		// Verify range
162	19	if (empty($pRange) \|\| !is_array($pRange[0])) {
163		throw new Exception('Range does not contain any information');
164		}
165
166		// Build range
167	19	$imploded = [];
		0 ignored issues – show Unused Code introduced 2017-12-03 09:09 UTC by Report Bug Copy Issue Report The assignment to `$imploded` is dead and can be removed. Loading history...
168	19	$counter = count($pRange);
169	19	for ($i = 0; $i < $counter; ++$i) {
170	19	$pRange[$i] = implode(':', $pRange[$i]);
171		}
172	19	$imploded = implode(',', $pRange);
173
174	19	return $imploded;
175		}
176
177		/**
178		* Calculate range boundaries.
179		*
180		* @param string $pRange Cell range (e.g. A1:A1)
181		*
182		* @return array Range coordinates array(Start Cell, End Cell)
183		* where Start Cell and End Cell are arrays (Column Number, Row Number)
184		*/
185	66	public static function rangeBoundaries($pRange)
186		{
187		// Ensure $pRange is a valid range
188	66	if (empty($pRange)) {
189		$pRange = self::DEFAULT_RANGE;
190		}
191
192		// Uppercase coordinate
193	66	$pRange = strtoupper($pRange);
194
195		// Extract range
196	66	if (strpos($pRange, ':') === false) {
197	2	$rangeA = $rangeB = $pRange;
198		} else {
199	64	list($rangeA, $rangeB) = explode(':', $pRange);
200		}
201
202		// Calculate range outer borders
203	66	$rangeStart = self::coordinateFromString($rangeA);
204	66	$rangeEnd = self::coordinateFromString($rangeB);
205
206		// Translate column into index
207	66	$rangeStart[0] = self::columnIndexFromString($rangeStart[0]);
208	66	$rangeEnd[0] = self::columnIndexFromString($rangeEnd[0]);
209
210	66	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	16	public static function rangeDimension($pRange)
221		{
222		// Calculate range outer borders
223	16	list($rangeStart, $rangeEnd) = self::rangeBoundaries($pRange);
224
225	16	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 array(Start Cell, End Cell)
234		* where Start Cell and End Cell are arrays (Column ID, Row Number)
235		*/
236	14	public static function getRangeBoundaries($pRange)
237		{
238		// Ensure $pRange is a valid range
239	14	if (empty($pRange)) {
240		$pRange = self::DEFAULT_RANGE;
241		}
242
243		// Uppercase coordinate
244	14	$pRange = strtoupper($pRange);
245
246		// Extract range
247	14	if (strpos($pRange, ':') === false) {
248	1	$rangeA = $rangeB = $pRange;
249		} else {
250	13	list($rangeA, $rangeB) = explode(':', $pRange);
251		}
252
253	14	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	199	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	199	static $indexCache = [];
269
270	199	if (isset($indexCache[$pString])) {
271	184	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	118	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	118	if (isset($pString[0])) {
286	116	if (!isset($pString[1])) {
287	105	$indexCache[$pString] = $columnLookup[$pString];
288
289	105	return $indexCache[$pString];
290	12	} elseif (!isset($pString[2])) {
291	9	$indexCache[$pString] = $columnLookup[$pString[0]] * 26 + $columnLookup[$pString[1]];
292
293	9	return $indexCache[$pString];
294	3	} elseif (!isset($pString[3])) {
295	2	$indexCache[$pString] = $columnLookup[$pString[0]] * 676 + $columnLookup[$pString[1]] * 26 + $columnLookup[$pString[2]];
296
297	2	return $indexCache[$pString];
298		}
299		}
300
301	3	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	162	public static function stringFromColumnIndex($columnIndex)
312		{
313	162	static $indexCache = [];
314
315	162	if (!isset($indexCache[$columnIndex])) {
316	115	$indexValue = $columnIndex;
317	115	$base26 = null;
318		do {
319	115	$characterValue = ($indexValue % 26) ?: 26;
320	115	$indexValue = ($indexValue - $characterValue) / 26;
321	115	$base26 = chr($characterValue + 64) . ($base26 ?: '');
322	115	} while ($indexValue > 0);
323	115	$indexCache[$columnIndex] = $base26;
324		}
325
326	162	return $indexCache[$columnIndex];
327		}
328
329		/**
330		* Extract all cell references in range.
331		*
332		* @param string $pRange Range (e.g. A1 or A1:C10 or A1:E10 A20:E25)
333		*
334		* @return array Array containing single cell references
335		*/
336	84	public static function extractAllCellReferencesInRange($pRange)
337		{
338		// Returnvalue
339	84	$returnValue = [];
340
341		// Explode spaces
342	84	$cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
343	84	foreach ($cellBlocks as $cellBlock) {
344		// Single cell?
345	84	if (!self::coordinateIsRange($cellBlock)) {
346	61	$returnValue[] = $cellBlock;
347
348	61	continue;
349		}
350
351		// Range...
352	76	$ranges = self::splitRange($cellBlock);
353	76	foreach ($ranges as $range) {
354		// Single cell?
355	76	if (!isset($range[1])) {
356		$returnValue[] = $range[0];
357
358		continue;
359		}
360
361		// Range...
362	76	list($rangeStart, $rangeEnd) = $range;
363	76	sscanf($rangeStart, '%[A-Z]%d', $startCol, $startRow);
		0 ignored issues – show Comprehensibility Best Practice introduced 2017-12-03 09:09 UTC by Report Bug Copy Issue Report The variable `$startRow` seems to be never defined. Loading history...
364	76	sscanf($rangeEnd, '%[A-Z]%d', $endCol, $endRow);
		0 ignored issues – show Comprehensibility Best Practice introduced 2017-12-03 09:09 UTC by Report Bug Copy Issue Report The variable `$endRow` seems to be never defined. Loading history...
365	76	++$endCol;
366
367		// Current data
368	76	$currentCol = $startCol;
369	76	$currentRow = $startRow;
370
371		// Loop cells
372	76	while ($currentCol != $endCol) {
373	76	while ($currentRow <= $endRow) {
374	76	$returnValue[] = $currentCol . $currentRow;
375	76	++$currentRow;
376		}
377	76	++$currentCol;
378	76	$currentRow = $startRow;
379		}
380		}
381		}
382
383		// Sort the result by column and row
384	84	$sortKeys = [];
385	84	foreach (array_unique($returnValue) as $coord) {
386	84	sscanf($coord, '%[A-Z]%d', $column, $row);
		0 ignored issues – show Comprehensibility Best Practice introduced 2017-12-03 09:09 UTC by Report Bug Copy Issue Report The variable `$row` seems to be never defined. Loading history...
387	84	$sortKeys[sprintf('%3s%09d', $column, $row)] = $coord;
388		}
389	84	ksort($sortKeys);
390
391		// Return value
392	84	return array_values($sortKeys);
393		}
394
395		/**
396		* Convert an associative array of single cell coordinates to values to an associative array
397		* of cell ranges to values. Only adjacent cell coordinates with the same
398		* value will be merged. If the value is an object, it must implement the method getHashCode().
399		*
400		* For example, this function converts:
401		*
402		* [ 'A1' => 'x', 'A2' => 'x', 'A3' => 'x', 'A4' => 'y' ]
403		*
404		* to:
405		*
406		* [ 'A1:A3' => 'x', 'A4' => 'y' ]
407		*
408		* @param array $pCoordCollection associative array mapping coordinates to values
409		*
410		* @return array associative array mapping coordinate ranges to valuea
411		*/
412	7	public static function mergeRangesInCollection(array $pCoordCollection)
413		{
414	7	$hashedValues = [];
415	7	$mergedCoordCollection = [];
416
417	7	foreach ($pCoordCollection as $coord => $value) {
418	7	if (self::coordinateIsRange($coord)) {
419	1	$mergedCoordCollection[$coord] = $value;
420
421	1	continue;
422		}
423
424	7	list($column, $row) = self::coordinateFromString($coord);
425	7	$row = (int) (ltrim($row, '$'));
426	7	$hashCode = $column . '-' . (is_object($value) ? $value->getHashCode() : $value);
427
428	7	if (!isset($hashedValues[$hashCode])) {
429	7	$hashedValues[$hashCode] = (object) [
430	7	'value' => $value,
431	7	'col' => $column,
432	7	'rows' => [$row],
433		];
434		} else {
435	7	$hashedValues[$hashCode]->rows[] = $row;
436		}
437		}
438
439	7	ksort($hashedValues);
440
441	7	foreach ($hashedValues as $hashedValue) {
442	7	sort($hashedValue->rows);
443	7	$rowStart = null;
444	7	$rowEnd = null;
445	7	$ranges = [];
446
447	7	foreach ($hashedValue->rows as $row) {
448	7	if ($rowStart === null) {
449	7	$rowStart = $row;
450	7	$rowEnd = $row;
451	3	} elseif ($rowEnd === $row - 1) {
452	3	$rowEnd = $row;
453		} else {
454	1	if ($rowStart == $rowEnd) {
455		$ranges[] = $hashedValue->col . $rowStart;
456		} else {
457	1	$ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
458		}
459
460	1	$rowStart = $row;
461	7	$rowEnd = $row;
462		}
463		}
464
465	7	if ($rowStart !== null) {
466	7	if ($rowStart == $rowEnd) {
467	6	$ranges[] = $hashedValue->col . $rowStart;
468		} else {
469	2	$ranges[] = $hashedValue->col . $rowStart . ':' . $hashedValue->col . $rowEnd;
470		}
471		}
472
473	7	foreach ($ranges as $range) {
474	7	$mergedCoordCollection[$range] = $hashedValue->value;
475		}
476		}
477
478	7	return $mergedCoordCollection;
479		}
480		}
481

PHPOffice / PhpSpreadsheet

Push — develop ( 4e0344...49775b )

Coordinate::stringFromColumnIndex() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like