Differences Between: [Versions 400 and 403] [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 /** @return mixed */ 95 private static function extractRequiredCells(?Worksheet $worksheet, string $cellAddress) 96 { 97 return Calculation::getInstance($worksheet !== null ? $worksheet->getParent() : null) 98 ->extractCellRange($cellAddress, $worksheet, false); 99 } 100 101 private static function extractWorksheet(?string $cellAddress, Cell $cell): array 102 { 103 $cellAddress = self::assessCellAddress($cellAddress ?? '', $cell); 104 105 $sheetName = ''; 106 if (strpos($cellAddress, '!') !== false) { 107 [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true); 108 $sheetName = trim($sheetName, "'"); 109 } 110 111 $worksheet = ($sheetName !== '') 112 ? $cell->getWorksheet()->getParentOrThrow()->getSheetByName($sheetName) 113 : $cell->getWorksheet(); 114 115 return [$cellAddress, $worksheet]; 116 } 117 118 private static function assessCellAddress(string $cellAddress, Cell $cell): string 119 { 120 if (preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/mui', $cellAddress) !== false) { 121 $cellAddress = Functions::expandDefinedName($cellAddress, $cell); 122 } 123 124 return $cellAddress; 125 } 126 127 /** 128 * @param mixed $width 129 * @param mixed $columns 130 */ 131 private static function adjustEndCellColumnForWidth(string $endCellColumn, $width, int $startCellColumn, $columns): int 132 { 133 $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1; 134 if (($width !== null) && (!is_object($width))) { 135 $endCellColumn = $startCellColumn + (int) $width - 1; 136 } else { 137 $endCellColumn += (int) $columns; 138 } 139 140 return $endCellColumn; 141 } 142 143 /** 144 * @param mixed $height 145 * @param mixed $rows 146 * @param mixed $endCellRow 147 */ 148 private static function adustEndCellRowForHeight($height, int $startCellRow, $rows, $endCellRow): int 149 { 150 if (($height !== null) && (!is_object($height))) { 151 $endCellRow = $startCellRow + (int) $height - 1; 152 } else { 153 $endCellRow += (int) $rows; 154 } 155 156 return $endCellRow; 157 } 158 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body