View Code - 20260721

RowColumnInformation.php | 20260721_154032.php Initial Bulk Import

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class RowColumnInformation
{
    /**
     * Test if cellAddress is null or whitespace string.
     *
     * @param null|mixed[]|string $cellAddress A reference to a range of cells
     */
    private static function cellAddressNullOrWhitespace($cellAddress): bool
    {
        return $cellAddress === null || (!is_array($cellAddress) && trim($cellAddress) === '');
    }

private static function cellColumn(?Cell $cell): int
    {
        return ($cell !== null) ? Coordinate::columnIndexFromString($cell->getColumn()) : 1;
    }

/**
     * COLUMN.
     *
     * Returns the column number of the given cell reference
     *     If the cell reference is a range of cells, COLUMN returns the column numbers of each column
     *        in the reference as a horizontal array.
     *     If cell reference is omitted, and the function is being called through the calculation engine,
     *        then it is assumed to be the reference of the cell in which the COLUMN function appears;
     *        otherwise this function returns 1.
     *
     * Excel Function:
     *        =COLUMN([cellAddress])
     *
     * @param null|mixed[]|string $cellAddress A reference to a range of cells for which you want the column numbers
     *
     * @return int|int[]|string
     */
    public static function COLUMN($cellAddress = null, ?Cell $cell = null): int|string|array
    {
        if (self::cellAddressNullOrWhitespace($cellAddress)) {
            return self::cellColumn($cell);
        }

if (is_array($cellAddress)) {
            foreach ($cellAddress as $columnKey => $value) {
                $columnKey = (string) preg_replace('/[^a-z]/i', '', $columnKey);

return Coordinate::columnIndexFromString($columnKey);
            }

return self::cellColumn($cell);
        }

$cellAddress = $cellAddress ?? '';
        if ($cell != null) {
            [,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
            [,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
        }
        [, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
        $cellAddress ??= '';

if (str_contains($cellAddress, ':')) {
            [$startAddress, $endAddress] = explode(':', $cellAddress);
            $startAddress = (string) preg_replace('/[^a-z]/i', '', $startAddress);
            $endAddress = (string) preg_replace('/[^a-z]/i', '', $endAddress);

return range(
                Coordinate::columnIndexFromString($startAddress),
                Coordinate::columnIndexFromString($endAddress)
            );
        }

$cellAddress = (string) preg_replace('/[^a-z]/i', '', $cellAddress);

try {
            return Coordinate::columnIndexFromString($cellAddress);
        } catch (SpreadsheetException) {
            return ExcelError::NAME();
        }
    }

/**
     * COLUMNS.
     *
     * Returns the number of columns in an array or reference.
     *
     * Excel Function:
     *        =COLUMNS(cellAddress)
     *
     * @param null|mixed[]|string $cellAddress An array or array formula, or a reference to a range of cells
     *                                          for which you want the number of columns
     *
     * @return int|string The number of columns in cellAddress, or a string if arguments are invalid
     */
    public static function COLUMNS($cellAddress = null)
    {
        if (self::cellAddressNullOrWhitespace($cellAddress)) {
            return 1;
        }
        if (is_string($cellAddress) && ErrorValue::isError($cellAddress, true)) {
            return $cellAddress;
        }
        if (!is_array($cellAddress)) {
            return ExcelError::VALUE();
        }

reset($cellAddress);
        $isMatrix = (is_numeric(key($cellAddress)));
        [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);

if ($isMatrix) {
            return $rows;
        }

return $columns;
    }

private static function cellRow(?Cell $cell): int|string
    {
        return ($cell !== null) ? self::convert0ToName($cell->getRow()) : 1;
    }

private static function convert0ToName(int|string $result): int|string
    {
        if (is_int($result) && ($result <= 0 || $result > 1048576)) {
            return ExcelError::NAME();
        }

return $result;
    }

/**
     * ROW.
     *
     * Returns the row number of the given cell reference
     *     If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference
     *        as a vertical array.
     *     If cell reference is omitted, and the function is being called through the calculation engine,
     *        then it is assumed to be the reference of the cell in which the ROW function appears;
     *        otherwise this function returns 1.
     *
     * Excel Function:
     *        =ROW([cellAddress])
     *
     * @param null|mixed[][]|string $cellAddress A reference to a range of cells for which you want the row numbers
     *
     * @return int|mixed[]|string
     */
    public static function ROW($cellAddress = null, ?Cell $cell = null): int|string|array
    {
        if (self::cellAddressNullOrWhitespace($cellAddress)) {
            return self::cellRow($cell);
        }

if (is_array($cellAddress)) {
            foreach ($cellAddress as $rowKey => $rowValue) {
                foreach ($rowValue as $columnKey => $cellValue) {
                    return (int) preg_replace('/\D/', '', $rowKey);
                }
            }

return self::cellRow($cell);
        }

$cellAddress = $cellAddress ?? '';
        if ($cell !== null) {
            [,, $sheetName] = Helpers::extractWorksheet($cellAddress, $cell);
            [,, $cellAddress] = Helpers::extractCellAddresses($cellAddress, true, $cell->getWorksheet(), $sheetName);
        }
        [, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
        $cellAddress ??= '';
        if (str_contains($cellAddress, ':')) {
            [$startAddress, $endAddress] = explode(':', $cellAddress);
            $startAddress = (int) (string) preg_replace('/\D/', '', $startAddress);
            $endAddress = (int) (string) preg_replace('/\D/', '', $endAddress);

return array_map(
                fn ($value): array => [$value],
                range($startAddress, $endAddress)
            );
        }
        [$cellAddress] = explode(':', $cellAddress);

return self::convert0ToName((int) preg_replace('/\D/', '', $cellAddress));
    }

/**
     * ROWS.
     *
     * Returns the number of rows in an array or reference.
     *
     * Excel Function:
     *        =ROWS(cellAddress)
     *
     * @param null|mixed[]|string $cellAddress An array or array formula, or a reference to a range of cells
     *                                          for which you want the number of rows
     *
     * @return int|string The number of rows in cellAddress, or a string if arguments are invalid
     */
    public static function ROWS($cellAddress = null)
    {
        if (self::cellAddressNullOrWhitespace($cellAddress)) {
            return 1;
        }
        if (is_string($cellAddress) && ErrorValue::isError($cellAddress, true)) {
            return $cellAddress;
        }
        if (!is_array($cellAddress)) {
            return ExcelError::VALUE();
        }

reset($cellAddress);
        $isMatrix = (is_numeric(key($cellAddress)));
        [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);

if ($isMatrix) {
            return $columns;
        }

return $rows;
    }
}