[Summary view]

Differences Between: [Versions 402 and 403]

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