ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Offset.php
Go to the documentation of this file.
1 <?php
2 
4 
10 
11 class Offset
12 {
43  public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $pCell = null)
44  {
47  $height = Functions::flattenSingleValue($height);
48  $width = Functions::flattenSingleValue($width);
49 
50  if ($cellAddress === null || $cellAddress === '') {
51  return 0;
52  }
53 
54  if (!is_object($pCell)) {
55  return Functions::REF();
56  }
57 
58  [$cellAddress, $pSheet] = self::extractWorksheet($cellAddress, $pCell);
59 
60  $startCell = $endCell = $cellAddress;
61  if (strpos($cellAddress, ':')) {
62  [$startCell, $endCell] = explode(':', $cellAddress);
63  }
64  [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell);
65  [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell);
66 
67  $startCellRow += $rows;
68  $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1;
69  $startCellColumn += $columns;
70 
71  if (($startCellRow <= 0) || ($startCellColumn < 0)) {
72  return Functions::REF();
73  }
74 
75  $endCellColumn = self::adjustEndCellColumnForWidth($endCellColumn, $width, $startCellColumn, $columns);
76  $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
77 
78  $endCellRow = self::adustEndCellRowForHeight($height, $startCellRow, $rows, $endCellRow);
79 
80  if (($endCellRow <= 0) || ($endCellColumn < 0)) {
81  return Functions::REF();
82  }
83  $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
84 
85  $cellAddress = "{$startCellColumn}{$startCellRow}";
86  if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
87  $cellAddress .= ":{$endCellColumn}{$endCellRow}";
88  }
89 
90  return self::extractRequiredCells($pSheet, $cellAddress);
91  }
92 
93  private static function extractRequiredCells(?Worksheet $pSheet, string $cellAddress)
94  {
95  return Calculation::getInstance($pSheet !== null ? $pSheet->getParent() : null)
96  ->extractCellRange($cellAddress, $pSheet, false);
97  }
98 
99  private static function extractWorksheet($cellAddress, Cell $pCell): array
100  {
101  $sheetName = '';
102  if (strpos($cellAddress, '!') !== false) {
103  [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
104  $sheetName = trim($sheetName, "'");
105  }
106 
107  $pSheet = ($sheetName !== '')
108  ? $pCell->getWorksheet()->getParent()->getSheetByName($sheetName)
109  : $pCell->getWorksheet();
110 
111  return [$cellAddress, $pSheet];
112  }
113 
114  private static function adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns)
115  {
116  $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
117  if (($width !== null) && (!is_object($width))) {
118  $endCellColumn = $startCellColumn + (int) $width - 1;
119  } else {
120  $endCellColumn += (int) $columns;
121  }
122 
123  return $endCellColumn;
124  }
125 
126  private static function adustEndCellRowForHeight($height, int $startCellRow, $rows, $endCellRow): int
127  {
128  if (($height !== null) && (!is_object($height))) {
129  $endCellRow = $startCellRow + (int) $height - 1;
130  } else {
131  $endCellRow += (int) $rows;
132  }
133 
134  return $endCellRow;
135  }
136 }
static extractRequiredCells(?Worksheet $pSheet, string $cellAddress)
Definition: Offset.php:93
static extractSheetTitle($pRange, $returnRange=false)
Extract worksheet title from range.
Definition: Worksheet.php:2719
static OFFSET($cellAddress=null, $rows=0, $columns=0, $height=null, $width=null, ?Cell $pCell=null)
OFFSET.
Definition: Offset.php:43
static adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns)
Definition: Offset.php:114
static adustEndCellRowForHeight($height, int $startCellRow, $rows, $endCellRow)
Definition: Offset.php:126
extractCellRange(&$pRange='A1', ?Worksheet $pSheet=null, $resetLog=true)
Extract range values.
static getInstance(?Spreadsheet $spreadsheet=null)
Get an instance of this class.
$rows
Definition: xhr_table.php:10
static coordinateFromString($pCoordinateString)
Coordinate from string.
Definition: Coordinate.php:32
static extractWorksheet($cellAddress, Cell $pCell)
Definition: Offset.php:99
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:649
if(! $in) $columns
Definition: Utf8Test.php:45
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
getWorksheet()
Get parent worksheet.
Definition: Cell.php:479