Offset.php

Go to the documentation of this file.

<?php
 
namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
 
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
 
class Offset
{
    public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $pCell = null)
    {
        $rows = Functions::flattenSingleValue($rows);
        $columns = Functions::flattenSingleValue($columns);
        $height = Functions::flattenSingleValue($height);
        $width = Functions::flattenSingleValue($width);
 
        if ($cellAddress === null || $cellAddress === '') {
            return 0;
        }
 
        if (!is_object($pCell)) {
            return Functions::REF();
        }
 
        [$cellAddress, $pSheet] = self::extractWorksheet($cellAddress, $pCell);
 
        $startCell = $endCell = $cellAddress;
        if (strpos($cellAddress, ':')) {
            [$startCell, $endCell] = explode(':', $cellAddress);
        }
        [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell);
        [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell);
 
        $startCellRow += $rows;
        $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1;
        $startCellColumn += $columns;
 
        if (($startCellRow <= 0) || ($startCellColumn < 0)) {
            return Functions::REF();
        }
 
        $endCellColumn = self::adjustEndCellColumnForWidth($endCellColumn, $width, $startCellColumn, $columns);
        $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
 
        $endCellRow = self::adustEndCellRowForHeight($height, $startCellRow, $rows, $endCellRow);
 
        if (($endCellRow <= 0) || ($endCellColumn < 0)) {
            return Functions::REF();
        }
        $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
 
        $cellAddress = "{$startCellColumn}{$startCellRow}";
        if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
            $cellAddress .= ":{$endCellColumn}{$endCellRow}";
        }
 
        return self::extractRequiredCells($pSheet, $cellAddress);
    }
 
    private static function extractRequiredCells(?Worksheet $pSheet, string $cellAddress)
    {
        return Calculation::getInstance($pSheet !== null ? $pSheet->getParent() : null)
            ->extractCellRange($cellAddress, $pSheet, false);
    }
 
    private static function extractWorksheet($cellAddress, Cell $pCell): array
    {
        $sheetName = '';
        if (strpos($cellAddress, '!') !== false) {
            [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
            $sheetName = trim($sheetName, "'");
        }
 
        $pSheet = ($sheetName !== '')
            ? $pCell->getWorksheet()->getParent()->getSheetByName($sheetName)
            : $pCell->getWorksheet();
 
        return [$cellAddress, $pSheet];
    }
 
    private static function adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns)
    {
        $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
        if (($width !== null) && (!is_object($width))) {
            $endCellColumn = $startCellColumn + (int) $width - 1;
        } else {
            $endCellColumn += (int) $columns;
        }
 
        return $endCellColumn;
    }
 
    private static function adustEndCellRowForHeight($height, int $startCellRow, $rows, $endCellRow): int
    {
        if (($height !== null) && (!is_object($height))) {
            $endCellRow = $startCellRow + (int) $height - 1;
        } else {
            $endCellRow += (int) $rows;
        }
 
        return $endCellRow;
    }
}