[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   9  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  10  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  11  
  12  class Offset
  13  {
  14      /**
  15       * OFFSET.
  16       *
  17       * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
  18       * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
  19       * the number of columns to be returned.
  20       *
  21       * Excel Function:
  22       *        =OFFSET(cellAddress, rows, cols, [height], [width])
  23       *
  24       * @param null|string $cellAddress The reference from which you want to base the offset.
  25       *                                     Reference must refer to a cell or range of adjacent cells;
  26       *                                     otherwise, OFFSET returns the #VALUE! error value.
  27       * @param mixed $rows The number of rows, up or down, that you want the upper-left cell to refer to.
  28       *                        Using 5 as the rows argument specifies that the upper-left cell in the
  29       *                        reference is five rows below reference. Rows can be positive (which means
  30       *                        below the starting reference) or negative (which means above the starting
  31       *                        reference).
  32       * @param mixed $columns The number of columns, to the left or right, that you want the upper-left cell
  33       *                           of the result to refer to. Using 5 as the cols argument specifies that the
  34       *                           upper-left cell in the reference is five columns to the right of reference.
  35       *                           Cols can be positive (which means to the right of the starting reference)
  36       *                           or negative (which means to the left of the starting reference).
  37       * @param mixed $height The height, in number of rows, that you want the returned reference to be.
  38       *                          Height must be a positive number.
  39       * @param mixed $width The width, in number of columns, that you want the returned reference to be.
  40       *                         Width must be a positive number.
  41       *
  42       * @return array|int|string An array containing a cell or range of cells, or a string on error
  43       */
  44      public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $cell = null)
  45      {
  46          $rows = Functions::flattenSingleValue($rows);
  47          $columns = Functions::flattenSingleValue($columns);
  48          $height = Functions::flattenSingleValue($height);
  49          $width = Functions::flattenSingleValue($width);
  50  
  51          if ($cellAddress === null || $cellAddress === '') {
  52              return ExcelError::VALUE();
  53          }
  54  
  55          if (!is_object($cell)) {
  56              return ExcelError::REF();
  57          }
  58  
  59          [$cellAddress, $worksheet] = self::extractWorksheet($cellAddress, $cell);
  60  
  61          $startCell = $endCell = $cellAddress;
  62          if (strpos($cellAddress, ':')) {
  63              [$startCell, $endCell] = explode(':', $cellAddress);
  64          }
  65          [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell);
  66          [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell);
  67  
  68          $startCellRow += $rows;
  69          $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1;
  70          $startCellColumn += $columns;
  71  
  72          if (($startCellRow <= 0) || ($startCellColumn < 0)) {
  73              return ExcelError::REF();
  74          }
  75  
  76          $endCellColumn = self::adjustEndCellColumnForWidth($endCellColumn, $width, $startCellColumn, $columns);
  77          $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
  78  
  79          $endCellRow = self::adustEndCellRowForHeight($height, $startCellRow, $rows, $endCellRow);
  80  
  81          if (($endCellRow <= 0) || ($endCellColumn < 0)) {
  82              return ExcelError::REF();
  83          }
  84          $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
  85  
  86          $cellAddress = "{$startCellColumn}{$startCellRow}";
  87          if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
  88              $cellAddress .= ":{$endCellColumn}{$endCellRow}";
  89          }
  90  
  91          return self::extractRequiredCells($worksheet, $cellAddress);
  92      }
  93  
  94      private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress)
  95      {
  96          return Calculation::getInstance($worksheet !== null ? $worksheet->getParent() : null)
  97              ->extractCellRange($cellAddress, $worksheet, false);
  98      }
  99  
 100      private static function extractWorksheet($cellAddress, Cell $cell): array
 101      {
 102          $cellAddress = self::assessCellAddress($cellAddress, $cell);
 103  
 104          $sheetName = '';
 105          if (strpos($cellAddress, '!') !== false) {
 106              [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 107              $sheetName = trim($sheetName, "'");
 108          }
 109  
 110          $worksheet = ($sheetName !== '')
 111              ? $cell->getWorksheet()->getParent()->getSheetByName($sheetName)
 112              : $cell->getWorksheet();
 113  
 114          return [$cellAddress, $worksheet];
 115      }
 116  
 117      private static function assessCellAddress(string $cellAddress, Cell $cell): string
 118      {
 119          if (preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/mui', $cellAddress) !== false) {
 120              $cellAddress = Functions::expandDefinedName($cellAddress, $cell);
 121          }
 122  
 123          return $cellAddress;
 124      }
 125  
 126      private static function adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns)
 127      {
 128          $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
 129          if (($width !== null) && (!is_object($width))) {
 130              $endCellColumn = $startCellColumn + (int) $width - 1;
 131          } else {
 132              $endCellColumn += (int) $columns;
 133          }
 134  
 135          return $endCellColumn;
 136      }
 137  
 138      private static function adustEndCellRowForHeight($height, int $startCellRow, $rows, $endCellRow): int
 139      {
 140          if (($height !== null) && (!is_object($height))) {
 141              $endCellRow = $startCellRow + (int) $height - 1;
 142          } else {
 143              $endCellRow += (int) $rows;
 144          }
 145  
 146          return $endCellRow;
 147      }
 148  }