# tablepress/3.4/libraries/vendor/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php

TablePress – Tables in WordPress made easy, version 3.4. 377 lines.

- Page: https://pluginprobe.com/plugins/tablepress/3.4/code/libraries/vendor/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php
- Raw: https://pluginprobe.com/plugins/tablepress/3.4/raw/libraries/vendor/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php
- Modified: 2026-09-30T03:50:46+00:00

Line numbers below start at 1. Link to a line or a range by appending a fragment to the
page URL, for example `https://pluginprobe.com/plugins/tablepress/3.4/code/libraries/vendor/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php#L10-L20`.

```php
<?php

namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands;

use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Table;
use Stringable;

final class StructuredReference implements Operand
{
	public const NAME = 'Structured Reference';

	private const OPEN_BRACE = '[';
	private const CLOSE_BRACE = ']';

	private const ITEM_SPECIFIER_ALL = '#All';
	private const ITEM_SPECIFIER_HEADERS = '#Headers';
	private const ITEM_SPECIFIER_DATA = '#Data';
	private const ITEM_SPECIFIER_TOTALS = '#Totals';
	private const ITEM_SPECIFIER_THIS_ROW = '#This Row';

	private const ITEM_SPECIFIER_ROWS_SET = [
		self::ITEM_SPECIFIER_ALL,
		self::ITEM_SPECIFIER_HEADERS,
		self::ITEM_SPECIFIER_DATA,
		self::ITEM_SPECIFIER_TOTALS,
	];

	private const TABLE_REFERENCE = '/([\p{L}_\\\][\p{L}\p{N}\._]+)?(\[(?:[^\]\[]+|(?R))*+\])/miu';

	private string $value;

	private string $tableName;

	private Table $table;

	private string $reference;

	private ?int $headersRow;

	private int $firstDataRow;

	private int $lastDataRow;

	private ?int $totalsRow;

	/** @var mixed[] */
	private array $columns;

	public function __construct(string $structuredReference)
	{
		$this->value = $structuredReference;
	}

	/** @param string[] $matches */
	public static function fromParser(string $formula, int $index, array $matches): self
	{
		$val = $matches[0];

		$srCount = substr_count($val, self::OPEN_BRACE)
			- substr_count($val, self::CLOSE_BRACE);
		while ($srCount > 0) {
			$srIndex = strlen($val);
			$srStringRemainder = (string) substr($formula, $index + $srIndex);
			$closingPos = strpos($srStringRemainder, self::CLOSE_BRACE);
			if ($closingPos === false) {
				throw new Exception("Formula Error: No closing ']' to match opening '['");
			}
			$srStringRemainder = (string) substr($srStringRemainder, 0, $closingPos + 1);
			--$srCount;
			if (str_contains($srStringRemainder, self::OPEN_BRACE)) {
				++$srCount;
			}
			$val .= $srStringRemainder;
		}

		return new self($val);
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	public function parse(Cell $cell): string
	{
		$this->getTableStructure($cell);
		$cellRange = ($this->isRowReference()) ? $this->getRowReference($cell) : $this->getColumnReference();
		$sheetName = '';
		$worksheet = $this->table->getWorksheet();
		if ($worksheet !== null && $worksheet !== $cell->getWorksheet()) {
			$sheetName = "'" . $worksheet->getTitle() . "'!";
		}

		return $sheetName . $cellRange;
	}

	private function isRowReference(): bool
	{
		return str_contains($this->value, '[@')
			|| str_contains($this->value, '[' . self::ITEM_SPECIFIER_THIS_ROW . ']');
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	private function getTableStructure(Cell $cell): void
	{
		preg_match(self::TABLE_REFERENCE, $this->value, $matches);

		$this->tableName = $matches[1];
		$this->table = ($this->tableName === '')
			? $this->getTableForCell($cell)
			: $this->getTableByName($cell);
		$this->reference = $matches[2];
		$tableRange = Coordinate::getRangeBoundaries($this->table->getRange());

		$this->headersRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] : null;
		$this->firstDataRow = ($this->table->getShowHeaderRow()) ? (int) $tableRange[0][1] + 1 : $tableRange[0][1];
		$this->totalsRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] : null;
		$this->lastDataRow = ($this->table->getShowTotalsRow()) ? (int) $tableRange[1][1] - 1 : $tableRange[1][1];

		$cellParam = $cell;
		$worksheet = $this->table->getWorksheet();
		if ($worksheet !== null && $worksheet !== $cell->getWorksheet()) {
			$cellParam = $worksheet->getCell('A1');
		}
		$this->columns = $this->getColumns($cellParam, $tableRange);
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	private function getTableForCell(Cell $cell): Table
	{
		$tables = $cell->getWorksheet()->getTableCollection();
		foreach ($tables as $table) {
			/** @var Table $table */
			$range = $table->getRange();
			if ($cell->isInRange($range) === true) {
				$this->tableName = $table->getName();

				return $table;
			}
		}

		throw new Exception('Table for Structured Reference cannot be identified');
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	private function getTableByName(Cell $cell): Table
	{
		$table = $cell->getWorksheet()->getTableByName($this->tableName);

		if ($table === null) {
			$spreadsheet = $cell->getWorksheet()->getParent();
			if ($spreadsheet !== null) {
				$table = $spreadsheet->getTableByName($this->tableName);
			}
		}

		if ($table === null) {
			throw new Exception("Table {$this->tableName} for Structured Reference cannot be located");
		}

		return $table;
	}

	/**
	 * @param array{array{string, int}, array{string, int}} $tableRange
	 *
	 * @return mixed[]
	 */
	private function getColumns(Cell $cell, array $tableRange): array
	{
		$worksheet = $cell->getWorksheet();
		$cellReference = $cell->getCoordinate();

		$columns = [];
		$lastColumn = StringHelper::stringIncrement($tableRange[1][0]);
		for ($column = $tableRange[0][0]; $column !== $lastColumn; StringHelper::stringIncrement($column)) {
			/** @var string $column */
			$columns[$column] = $worksheet
				->getCell($column . ($this->headersRow ?? ($this->firstDataRow - 1)))
				->getCalculatedValue();
		}

		$worksheet->getCell($cellReference);

		return $columns;
	}

	private function getRowReference(Cell $cell): string
	{
		$reference = str_replace("\u{a0}", ' ', $this->reference);
		/** @var string $reference */
		$reference = str_replace('[' . self::ITEM_SPECIFIER_THIS_ROW . '],', '', $reference);

		foreach ($this->columns as $columnId => $columnNamex) {
			/** @var string $columnNamex */
			$columnName = str_replace("\u{a0}", ' ', $columnNamex);
			$reference = $this->adjustRowReference($columnName, $reference, $cell, $columnId);
		}

		return $this->validateParsedReference(trim($reference, '[]@, '));
	}

	private function adjustRowReference(string $columnName, string $reference, Cell $cell, string $columnId): string
	{
		if ($columnName !== '') {
			$cellReference = $columnId . $cell->getRow();
			$pattern1 = '/\[' . preg_quote($columnName, '/') . '\]/miu';
			$pattern2 = '/@' . preg_quote($columnName, '/') . '/miu';
			if (preg_match($pattern1, $reference) === 1) {
				$reference = preg_replace($pattern1, $cellReference, $reference);
			} elseif (preg_match($pattern2, $reference) === 1) {
				$reference = preg_replace($pattern2, $cellReference, $reference);
			}
			/** @var string $reference */
		}

		return $reference;
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	private function getColumnReference(): string
	{
		$reference = str_replace("\u{a0}", ' ', $this->reference);
		$startRow = ($this->totalsRow === null) ? $this->lastDataRow : $this->totalsRow;
		$endRow = ($this->headersRow === null) ? $this->firstDataRow : $this->headersRow;

		[$startRow, $endRow] = $this->getRowsForColumnReference($reference, $startRow, $endRow);
		$reference = $this->getColumnsForColumnReference($reference, $startRow, $endRow);

		$reference = trim($reference, '[]@, ');
		if (substr_count($reference, ':') > 1) {
			$cells = explode(':', $reference);
			$firstCell = array_shift($cells);
			$lastCell = array_pop($cells);
			$reference = "{$firstCell}:{$lastCell}";
		}

		return $this->validateParsedReference($reference);
	}

	/**
	 * @throws Exception
	 * @throws \TablePress\PhpOffice\PhpSpreadsheet\Exception
	 */
	private function validateParsedReference(string $reference): string
	{
		if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . ':' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) {
			if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/miu', $reference) !== 1) {
				throw new Exception(
					"Invalid Structured Reference {$this->reference} {$reference}",
					Exception::CALCULATION_ENGINE_PUSH_TO_STACK
				);
			}
		}

		return $reference;
	}

	private function fullData(int $startRow, int $endRow): string
	{
		$columns = array_keys($this->columns);
		$firstColumn = array_shift($columns);
		$lastColumn = (empty($columns)) ? $firstColumn : array_pop($columns);

		return "{$firstColumn}{$startRow}:{$lastColumn}{$endRow}";
	}

	private function getMinimumRow(string $reference): int
	{
		switch ($reference) {
									case self::ITEM_SPECIFIER_ALL:
									case self::ITEM_SPECIFIER_HEADERS:
										return $this->headersRow ?? $this->firstDataRow;
									case self::ITEM_SPECIFIER_DATA:
										return $this->firstDataRow;
									case self::ITEM_SPECIFIER_TOTALS:
										return $this->totalsRow ?? $this->lastDataRow;
									default:
										return $this->headersRow ?? $this->firstDataRow;
								}
	}

	private function getMaximumRow(string $reference): int
	{
		switch ($reference) {
									case self::ITEM_SPECIFIER_HEADERS:
										return $this->headersRow ?? $this->firstDataRow;
									case self::ITEM_SPECIFIER_DATA:
										return $this->lastDataRow;
									case self::ITEM_SPECIFIER_ALL:
									case self::ITEM_SPECIFIER_TOTALS:
										return $this->totalsRow ?? $this->lastDataRow;
									default:
										return $this->totalsRow ?? $this->lastDataRow;
								}
	}

	public function value(): string
	{
		return $this->value;
	}

	/**
	 * @return array<int, int>
	 */
	private function getRowsForColumnReference(string &$reference, int $startRow, int $endRow): array
	{
		$rowsSelected = false;
		foreach (self::ITEM_SPECIFIER_ROWS_SET as $rowReference) {
			$pattern = '/\[' . $rowReference . '\]/mui';
			if (preg_match($pattern, $reference) === 1) {
				if (($rowReference === self::ITEM_SPECIFIER_HEADERS) && ($this->table->getShowHeaderRow() === false)) {
					throw new Exception(
						'Table Headers are Hidden, and should not be Referenced',
						Exception::CALCULATION_ENGINE_PUSH_TO_STACK
					);
				}
				$rowsSelected = true;
				$startRow = min($startRow, $this->getMinimumRow($rowReference));
				$endRow = max($endRow, $this->getMaximumRow($rowReference));
				$reference = preg_replace($pattern, '', $reference) ?? '';
			}
		}
		if ($rowsSelected === false) {
			// If there isn't any Special Item Identifier specified, then the selection defaults to data rows only.
			$startRow = $this->firstDataRow;
			$endRow = $this->lastDataRow;
		}

		return [$startRow, $endRow];
	}

	private function getColumnsForColumnReference(string $reference, int $startRow, int $endRow): string
	{
		$columnsSelected = false;
		foreach ($this->columns as $columnId => $columnNamex) {
			/** @var ?string $columnNamex */
			$columnName = str_replace("\u{a0}", ' ', $columnNamex ?? '');
			$cellFrom = "{$columnId}{$startRow}";
			$cellTo = "{$columnId}{$endRow}";
			$cellReference = ($cellFrom === $cellTo) ? $cellFrom : "{$cellFrom}:{$cellTo}";
			$pattern = '/\[' . preg_quote($columnName, '/') . '\]/mui';
			if (preg_match($pattern, $reference) === 1) {
				$columnsSelected = true;
				$reference = preg_replace($pattern, $cellReference, $reference);
			}
			/** @var string $reference */
		}
		if ($columnsSelected === false) {
			return $this->fullData($startRow, $endRow);
		}

		return $reference;
	}

	public function __toString(): string
	{
		return $this->value;
	}
}

```