PhpOffice\PhpSpreadsheet\Cell\AddressHelper Class Reference

Collaboration diagram for PhpOffice\PhpSpreadsheet\Cell\AddressHelper:

Static Public Member Functions
static	convertToA1 (string $address, int $currentRowNumber=1, int $currentColumnNumber=1)
	Converts an R1C1 format cell address to an A1 format cell address. More...
static	convertFormulaToA1 (string $formula, int $currentRowNumber=1, int $currentColumnNumber=1)
	Converts a formula that uses R1C1/SpreadsheetXML format cell address to an A1 format cell address. More...
static	convertToR1C1 (string $address, ?int $currentRowNumber=null, ?int $currentColumnNumber=null)
	Converts an A1 format cell address to an R1C1 format cell address. More...

Data Fields
const	R1C1_COORDINATE_REGEX = '/(R((?:\[-?\d*\])|(?:\d*))?)(C((?:\[-?\d*\])|(?:\d*))?)/i'

Static Protected Member Functions
static	convertSpreadsheetMLFormula (string $formula)

Detailed Description

Definition at line 7 of file AddressHelper.php.

Member Function Documentation

convertFormulaToA1()

static PhpOffice\PhpSpreadsheet\Cell\AddressHelper::convertFormulaToA1 ( string  $formula, int  $currentRowNumber = 1, int  $currentColumnNumber = 1  )

static

Converts a formula that uses R1C1/SpreadsheetXML format cell address to an A1 format cell address.

Definition at line 71 of file AddressHelper.php.

     : string {
        if (substr($formula, 0, 3) == 'of:') {
            // We have an old-style SpreadsheetML Formula
            return self::convertSpreadsheetMLFormula($formula);
        }
 
        //    Convert R1C1 style references to A1 style references (but only when not quoted)
        $temp = explode('"', $formula);
        $key = false;
        foreach ($temp as &$value) {
            //    Only replace in alternate array entries (i.e. non-quoted blocks)
            if ($key = !$key) {
                preg_match_all(self::R1C1_COORDINATE_REGEX, $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
                //    Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
                //        through the formula from left to right. Reversing means that we work right to left.through
                //        the formula
                $cellReferences = array_reverse($cellReferences);
                //    Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
                //        then modify the formula to use that new reference
                foreach ($cellReferences as $cellReference) {
                    $A1CellReference = self::convertToA1($cellReference[0][0], $currentRowNumber, $currentColumnNumber);
                    $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
                }
            }
        }
        unset($value);
 
        //    Then rebuild the formula string
        return implode('"', $temp);
    }

convertSpreadsheetMLFormula()

static PhpOffice\PhpSpreadsheet\Cell\AddressHelper::convertSpreadsheetMLFormula ( string  $formula )

staticprotected

Definition at line 52 of file AddressHelper.php.

                                                                          : string
    {
        $formula = substr($formula, 3);
        $temp = explode('"', $formula);
        $key = false;
        foreach ($temp as &$value) {
            //    Only replace in alternate array entries (i.e. non-quoted blocks)
            if ($key = !$key) {
                $value = str_replace(['[.', ':.', ']'], ['', ':', ''], $value);
            }
        }
        unset($value);
 
        return implode('"', $temp);
    }

References $key.

convertToA1()

static PhpOffice\PhpSpreadsheet\Cell\AddressHelper::convertToA1 ( string  $address, int  $currentRowNumber = 1, int  $currentColumnNumber = 1  )

static

Converts an R1C1 format cell address to an A1 format cell address.

Definition at line 14 of file AddressHelper.php.

     : string {
        $validityCheck = preg_match('/^(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))$/i', $address, $cellReference);
 
        if ($validityCheck === 0) {
            throw new Exception('Invalid R1C1-format Cell Reference');
        }
 
        $rowReference = $cellReference[2];
        //    Empty R reference is the current row
        if ($rowReference === '') {
            $rowReference = (string) $currentRowNumber;
        }
        //    Bracketed R references are relative to the current row
        if ($rowReference[0] === '[') {
            $rowReference = $currentRowNumber + (int) trim($rowReference, '[]');
        }
        $columnReference = $cellReference[4];
        //    Empty C reference is the current column
        if ($columnReference === '') {
            $columnReference = (string) $currentColumnNumber;
        }
        //    Bracketed C references are relative to the current column
        if (is_string($columnReference) && $columnReference[0] === '[') {
            $columnReference = $currentColumnNumber + (int) trim($columnReference, '[]');
        }
 
        if ($columnReference <= 0 || $rowReference <= 0) {
            throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
        }
        $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;
 
        return $A1CellReference;
    }

Referenced by PhpOffice\PhpSpreadsheet\Calculation\LookupRef\Helpers\convertR1C1().

Here is the caller graph for this function:

convertToR1C1()

static PhpOffice\PhpSpreadsheet\Cell\AddressHelper::convertToR1C1 ( string  $address, ?int  $currentRowNumber = null, ?int  $currentColumnNumber = null  )

static

Converts an A1 format cell address to an R1C1 format cell address.

If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.

Definition at line 110 of file AddressHelper.php.

     : string {
        $validityCheck = preg_match(Coordinate::A1_COORDINATE_REGEX, $address, $cellReference);
 
        if ($validityCheck === 0) {
            throw new Exception('Invalid A1-format Cell Reference');
        }
 
        $columnId = Coordinate::columnIndexFromString($cellReference['col_ref']);
        if ($cellReference['absolute_col'] === '$') {
            // Column must be absolute address
            $currentColumnNumber = null;
        }
 
        $rowId = (int) $cellReference['row_ref'];
        if ($cellReference['absolute_row'] === '$') {
            // Row must be absolute address
            $currentRowNumber = null;
        }
 
        if ($currentRowNumber !== null) {
            if ($rowId === $currentRowNumber) {
                $rowId = '';
            } else {
                $rowId = '[' . ($rowId - $currentRowNumber) . ']';
            }
        }
 
        if ($currentColumnNumber !== null) {
            if ($columnId === $currentColumnNumber) {
                $columnId = '';
            } else {
                $columnId = '[' . ($columnId - $currentColumnNumber) . ']';
            }
        }
 
        $R1C1Address = "R{$rowId}C{$columnId}";
 
        return $R1C1Address;
    }

Field Documentation

R1C1_COORDINATE_REGEX

const PhpOffice\PhpSpreadsheet\Cell\AddressHelper::R1C1_COORDINATE_REGEX = '/(R((?:\[-?\d*\])|(?:\d*))?)(C((?:\[-?\d*\])|(?:\d*))?)/i'

Definition at line 9 of file AddressHelper.php.

---

The documentation for this class was generated from the following file:

• libs/composer/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/AddressHelper.php