tablepress
/
libraries
/
vendor
/
PhpSpreadsheet
/
Calculation
/
Engine
/
Operands
/
StructuredReference.php
StructuredReference.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php
| 1 | <?php |
| 2 | |
| 3 | namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands; |
| 4 | |
| 5 | use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
| 6 | use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception; |
| 7 | use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell; |
| 8 | use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
| 9 | use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
| 10 | use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Table; |
| 11 | use Stringable; |
| 12 | |
| 13 | final class StructuredReference implements Operand |
| 14 | { |
| 15 | public const NAME = 'Structured Reference'; |
| 16 | |
| 17 | private const OPEN_BRACE = '['; |
| 18 | private const CLOSE_BRACE = ']'; |
| 19 | |
| 20 | private const ITEM_SPECIFIER_ALL = '#All'; |
| 21 | private const ITEM_SPECIFIER_HEADERS = '#Headers'; |
| 22 | private const ITEM_SPECIFIER_DATA = '#Data'; |
| 23 | private const ITEM_SPECIFIER_TOTALS = '#Totals'; |
| 24 | private const ITEM_SPECIFIER_THIS_ROW = '#This Row'; |
| 25 | |
| 26 | private const ITEM_SPECIFIER_ROWS_SET = [ |
| 27 | self::ITEM_SPECIFIER_ALL, |
| 28 | self::ITEM_SPECIFIER_HEADERS, |
| 29 | self::ITEM_SPECIFIER_DATA, |
| 30 | self::ITEM_SPECIFIER_TOTALS, |
| 31 | ]; |
| 32 | |
| 33 | private const TABLE_REFERENCE = '/([\p{L}_\\\][\p{L}\p{N}\._]+)?(\[(?:[^\]\[]+|(?R))*+\])/miu'; |
| 34 | |
| 35 | private string $value; |
| 36 | |
| 37 | private string $tableName; |
| 38 | |
| 39 | private Table $table; |
| 40 | |
| 41 | private string $reference; |
| 42 | |
| 43 | private ?int $headersRow; |
| 44 | |
| 45 | private int $firstDataRow; |
| 46 | |
| 47 | private int $lastDataRow; |
| 48 | |
| 49 | private ?int $totalsRow; |
| 50 | |
| 51 | /** @var mixed[] */ |
| 52 | private array $columns; |
| 53 | |
| 54 | public function __construct(string $structuredReference) |
| 55 | { |
| 56 | $this->value = $structuredReference; |
| 57 | } |
| 58 | |
| 59 | /** @param string[] $matches */ |
| 60 | public static function fromParser(string $formula, int $index, array $matches): self |
| 61 | { |
| 62 | $val = $matches[0]; |
| 63 | |
| 64 | $srCount = substr_count($val, self::OPEN_BRACE) |
| 65 | - substr_count($val, self::CLOSE_BRACE); |
| 66 | while ($srCount > 0) { |
| 67 | $srIndex = strlen($val); |
| 68 | $srStringRemainder = (string) substr($formula, $index + $srIndex); |
| 69 | $closingPos = strpos($srStringRemainder, self::CLOSE_BRACE); |
| 70 | if ($closingPos === false) { |
| 71 | throw new Exception("Formula Error: No closing ']' to match opening '['"); |
| 72 | } |
| 73 | $srStringRemainder = (string) substr($srStringRemainder, 0, $closingPos + 1); |
| 74 | --$srCount; |
| 75 | if (str_contains($srStringRemainder, self::OPEN_BRACE)) { |
| 76 | ++$srCount; |
| 77 | } |
| 78 | $val .= $srStringRemainder; |
| 79 | } |
| 80 | |
| 81 | return new self($val); |
| 82 | } |
| 83 | |
| 84 | /** |
| 85 | * @throws Exception |
| 86 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 87 | */ |
| 88 | public function parse(Cell $cell): string |
| 89 | { |
| 90 | $this->getTableStructure($cell); |
| 91 | $cellRange = ($this->isRowReference()) ? $this->getRowReference($cell) : $this->getColumnReference(); |
| 92 | $sheetName = ''; |
| 93 | $worksheet = $this->table->getWorksheet(); |
| 94 | if ($worksheet !== null && $worksheet !== $cell->getWorksheet()) { |
| 95 | $sheetName = "'" . $worksheet->getTitle() . "'!"; |
| 96 | } |
| 97 | |
| 98 | return $sheetName . $cellRange; |
| 99 | } |
| 100 | |
| 101 | private function isRowReference(): bool |
| 102 | { |
| 103 | return str_contains($this->value, '[@') |
| 104 | || str_contains($this->value, '[' . self::ITEM_SPECIFIER_THIS_ROW . ']'); |
| 105 | } |
| 106 | |
| 107 | /** |
| 108 | * @throws Exception |
| 109 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 110 | */ |
| 111 | private function getTableStructure(Cell $cell): void |
| 112 | { |
| 113 | preg_match(self::TABLE_REFERENCE, $this->value, $matches); |
| 114 | |
| 115 | $this->tableName = $matches[1]; |
| 116 | $this->table = ($this->tableName === '') |
| 117 | ? $this->getTableForCell($cell) |
| 118 | : $this->getTableByName($cell); |
| 119 | $this->reference = $matches[2]; |
| 120 | $tableRange = Coordinate::getRangeBoundaries($this->table->getRange()); |
| 121 | |
| 122 | $this->headersRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] : null; |
| 123 | $this->firstDataRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] + 1 : $tableRange[0][1]; |
| 124 | $this->totalsRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] : null; |
| 125 | $this->lastDataRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] - 1 : $tableRange[1][1]; |
| 126 | |
| 127 | $cellParam = $cell; |
| 128 | $worksheet = $this->table->getWorksheet(); |
| 129 | if ($worksheet !== null && $worksheet !== $cell->getWorksheet()) { |
| 130 | $cellParam = $worksheet->getCell('A1'); |
| 131 | } |
| 132 | $this->columns = $this->getColumns($cellParam, $tableRange); |
| 133 | } |
| 134 | |
| 135 | /** |
| 136 | * @throws Exception |
| 137 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 138 | */ |
| 139 | private function getTableForCell(Cell $cell): Table |
| 140 | { |
| 141 | $tables = $cell->getWorksheet()->getTableCollection(); |
| 142 | foreach ($tables as $table) { |
| 143 | /** @var Table $table */ |
| 144 | $range = $table->getRange(); |
| 145 | if ($cell->isInRange($range) === true) { |
| 146 | $this->tableName = $table->getName(); |
| 147 | |
| 148 | return $table; |
| 149 | } |
| 150 | } |
| 151 | |
| 152 | throw new Exception('Table for Structured Reference cannot be identified'); |
| 153 | } |
| 154 | |
| 155 | /** |
| 156 | * @throws Exception |
| 157 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 158 | */ |
| 159 | private function getTableByName(Cell $cell): Table |
| 160 | { |
| 161 | $table = $cell->getWorksheet()->getTableByName($this->tableName); |
| 162 | |
| 163 | if ($table === null) { |
| 164 | $spreadsheet = $cell->getWorksheet()->getParent(); |
| 165 | if ($spreadsheet !== null) { |
| 166 | $table = $spreadsheet->getTableByName($this->tableName); |
| 167 | } |
| 168 | } |
| 169 | |
| 170 | if ($table === null) { |
| 171 | throw new Exception("Table {$this->tableName} for Structured Reference cannot be located"); |
| 172 | } |
| 173 | |
| 174 | return $table; |
| 175 | } |
| 176 | |
| 177 | /** |
| 178 | * @param array{array{string, int}, array{string, int}} $tableRange |
| 179 | * |
| 180 | * @return mixed[] |
| 181 | */ |
| 182 | private function getColumns(Cell $cell, array $tableRange): array |
| 183 | { |
| 184 | $worksheet = $cell->getWorksheet(); |
| 185 | $cellReference = $cell->getCoordinate(); |
| 186 | |
| 187 | $columns = []; |
| 188 | $lastColumn = StringHelper::stringIncrement($tableRange[1][0]); |
| 189 | for ($column = $tableRange[0][0]; $column !== $lastColumn; StringHelper::stringIncrement($column)) { |
| 190 | /** @var string $column */ |
| 191 | $columns[$column] = $worksheet |
| 192 | ->getCell($column . ($this->headersRow ?? ($this->firstDataRow - 1))) |
| 193 | ->getCalculatedValue(); |
| 194 | } |
| 195 | |
| 196 | $worksheet->getCell($cellReference); |
| 197 | |
| 198 | return $columns; |
| 199 | } |
| 200 | |
| 201 | private function getRowReference(Cell $cell): string |
| 202 | { |
| 203 | $reference = str_replace("\u{a0}", ' ', $this->reference); |
| 204 | /** @var string $reference */ |
| 205 | $reference = str_replace('[' . self::ITEM_SPECIFIER_THIS_ROW . '],', '', $reference); |
| 206 | |
| 207 | foreach ($this->columns as $columnId => $columnNamex) { |
| 208 | /** @var string $columnNamex */ |
| 209 | $columnName = str_replace("\u{a0}", ' ', $columnNamex); |
| 210 | $reference = $this->adjustRowReference($columnName, $reference, $cell, $columnId); |
| 211 | } |
| 212 | |
| 213 | return $this->validateParsedReference(trim($reference, '[]@, ')); |
| 214 | } |
| 215 | |
| 216 | private function adjustRowReference(string $columnName, string $reference, Cell $cell, string $columnId): string |
| 217 | { |
| 218 | if ($columnName !== '') { |
| 219 | $cellReference = $columnId . $cell->getRow(); |
| 220 | $pattern1 = '/\[' . preg_quote($columnName, '/') . '\]/miu'; |
| 221 | $pattern2 = '/@' . preg_quote($columnName, '/') . '/miu'; |
| 222 | if (preg_match($pattern1, $reference) === 1) { |
| 223 | $reference = preg_replace($pattern1, $cellReference, $reference); |
| 224 | } elseif (preg_match($pattern2, $reference) === 1) { |
| 225 | $reference = preg_replace($pattern2, $cellReference, $reference); |
| 226 | } |
| 227 | /** @var string $reference */ |
| 228 | } |
| 229 | |
| 230 | return $reference; |
| 231 | } |
| 232 | |
| 233 | /** |
| 234 | * @throws Exception |
| 235 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 236 | */ |
| 237 | private function getColumnReference(): string |
| 238 | { |
| 239 | $reference = str_replace("\u{a0}", ' ', $this->reference); |
| 240 | $startRow = ($this->totalsRow === null) ? $this->lastDataRow : $this->totalsRow; |
| 241 | $endRow = ($this->headersRow === null) ? $this->firstDataRow : $this->headersRow; |
| 242 | |
| 243 | [$startRow, $endRow] = $this->getRowsForColumnReference($reference, $startRow, $endRow); |
| 244 | $reference = $this->getColumnsForColumnReference($reference, $startRow, $endRow); |
| 245 | |
| 246 | $reference = trim($reference, '[]@, '); |
| 247 | if (substr_count($reference, ':') > 1) { |
| 248 | $cells = explode(':', $reference); |
| 249 | $firstCell = array_shift($cells); |
| 250 | $lastCell = array_pop($cells); |
| 251 | $reference = "{$firstCell}:{$lastCell}"; |
| 252 | } |
| 253 | |
| 254 | return $this->validateParsedReference($reference); |
| 255 | } |
| 256 | |
| 257 | /** |
| 258 | * @throws Exception |
| 259 | * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception |
| 260 | */ |
| 261 | private function validateParsedReference(string $reference): string |
| 262 | { |
| 263 | if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . ':' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) { |
| 264 | if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) { |
| 265 | throw new Exception( |
| 266 | "Invalid Structured Reference {$this->reference} {$reference}", |
| 267 | Exception::CALCULATION_ENGINE_PUSH_TO_STACK |
| 268 | ); |
| 269 | } |
| 270 | } |
| 271 | |
| 272 | return $reference; |
| 273 | } |
| 274 | |
| 275 | private function fullData(int $startRow, int $endRow): string |
| 276 | { |
| 277 | $columns = array_keys($this->columns); |
| 278 | $firstColumn = array_shift($columns); |
| 279 | $lastColumn = (empty($columns)) ? $firstColumn : array_pop($columns); |
| 280 | |
| 281 | return "{$firstColumn}{$startRow}:{$lastColumn}{$endRow}"; |
| 282 | } |
| 283 | |
| 284 | private function getMinimumRow(string $reference): int |
| 285 | { |
| 286 | switch ($reference) { |
| 287 | case self::ITEM_SPECIFIER_ALL: |
| 288 | case self::ITEM_SPECIFIER_HEADERS: |
| 289 | return $this->headersRow ?? $this->firstDataRow; |
| 290 | case self::ITEM_SPECIFIER_DATA: |
| 291 | return $this->firstDataRow; |
| 292 | case self::ITEM_SPECIFIER_TOTALS: |
| 293 | return $this->totalsRow ?? $this->lastDataRow; |
| 294 | default: |
| 295 | return $this->headersRow ?? $this->firstDataRow; |
| 296 | } |
| 297 | } |
| 298 | |
| 299 | private function getMaximumRow(string $reference): int |
| 300 | { |
| 301 | switch ($reference) { |
| 302 | case self::ITEM_SPECIFIER_HEADERS: |
| 303 | return $this->headersRow ?? $this->firstDataRow; |
| 304 | case self::ITEM_SPECIFIER_DATA: |
| 305 | return $this->lastDataRow; |
| 306 | case self::ITEM_SPECIFIER_ALL: |
| 307 | case self::ITEM_SPECIFIER_TOTALS: |
| 308 | return $this->totalsRow ?? $this->lastDataRow; |
| 309 | default: |
| 310 | return $this->totalsRow ?? $this->lastDataRow; |
| 311 | } |
| 312 | } |
| 313 | |
| 314 | public function value(): string |
| 315 | { |
| 316 | return $this->value; |
| 317 | } |
| 318 | |
| 319 | /** |
| 320 | * @return array<int, int> |
| 321 | */ |
| 322 | private function getRowsForColumnReference(string &$reference, int $startRow, int $endRow): array |
| 323 | { |
| 324 | $rowsSelected = false; |
| 325 | foreach (self::ITEM_SPECIFIER_ROWS_SET as $rowReference) { |
| 326 | $pattern = '/\[' . $rowReference . '\]/mui'; |
| 327 | if (preg_match($pattern, $reference) === 1) { |
| 328 | if (($rowReference === self::ITEM_SPECIFIER_HEADERS) && ($this->table->getShowHeaderRow() === false)) { |
| 329 | throw new Exception( |
| 330 | 'Table Headers are Hidden, and should not be Referenced', |
| 331 | Exception::CALCULATION_ENGINE_PUSH_TO_STACK |
| 332 | ); |
| 333 | } |
| 334 | $rowsSelected = true; |
| 335 | $startRow = min($startRow, $this->getMinimumRow($rowReference)); |
| 336 | $endRow = max($endRow, $this->getMaximumRow($rowReference)); |
| 337 | $reference = preg_replace($pattern, '', $reference) ?? ''; |
| 338 | } |
| 339 | } |
| 340 | if ($rowsSelected === false) { |
| 341 | // If there isn't any Special Item Identifier specified, then the selection defaults to data rows only. |
| 342 | $startRow = $this->firstDataRow; |
| 343 | $endRow = $this->lastDataRow; |
| 344 | } |
| 345 | |
| 346 | return [$startRow, $endRow]; |
| 347 | } |
| 348 | |
| 349 | private function getColumnsForColumnReference(string $reference, int $startRow, int $endRow): string |
| 350 | { |
| 351 | $columnsSelected = false; |
| 352 | foreach ($this->columns as $columnId => $columnNamex) { |
| 353 | /** @var ?string $columnNamex */ |
| 354 | $columnName = str_replace("\u{a0}", ' ', $columnNamex ?? ''); |
| 355 | $cellFrom = "{$columnId}{$startRow}"; |
| 356 | $cellTo = "{$columnId}{$endRow}"; |
| 357 | $cellReference = ($cellFrom === $cellTo) ? $cellFrom : "{$cellFrom}:{$cellTo}"; |
| 358 | $pattern = '/\[' . preg_quote($columnName, '/') . '\]/mui'; |
| 359 | if (preg_match($pattern, $reference) === 1) { |
| 360 | $columnsSelected = true; |
| 361 | $reference = preg_replace($pattern, $cellReference, $reference); |
| 362 | } |
| 363 | /** @var string $reference */ |
| 364 | } |
| 365 | if ($columnsSelected === false) { |
| 366 | return $this->fullData($startRow, $endRow); |
| 367 | } |
| 368 | |
| 369 | return $reference; |
| 370 | } |
| 371 | |
| 372 | public function __toString(): string |
| 373 | { |
| 374 | return $this->value; |
| 375 | } |
| 376 | } |
| 377 |