nfTlWNl rn Ѻq$³*%yNk9SyEWZe2'ϋ@bLC $E\`Lٔ*ܝIc3ٿ ZmWNj ܸr+A֔|4W٤D̲#cڒr_(TD]ͫ\]*uQlfwhvĘ4v塑L_v xV8UE &-Hq.qXxe\`Lٔ*ܝIcWsTj6Жd;GU٪I"ve+V&~<_iLZ&l:35We697 L\Cd#gK7~d_Ợ śϖkdJGT/jzݡ)m<4w>ga뢖jAMH?b .llRv E%Wt >5 5X[f(ǾX%NM]n9 7獐j ;S9MM18ikx XaZ3@J!yҨZBCgHQ)\{Is|H9^1Ox˲7E'4xrrz 6m'&d?\۪l֭ɓߕGŦjA/V+?~qAcJ!yҨZН,ҝ:o~~Q 75n]X^,չXCGpqI$$t$̘8ɢ&ܑK-# naKْOVUS鈁&FS ]~,to,}d1B60n!\(p InN y<0 秘 d'>#b2B%zJOUi@hb/9y;_Hr;@?P;9IX !leKrO Z<p7nN y_ziy s\DlHCM.r0~7VeH>P{)pc! PD^v_(d-pgS(-, b]g<'yp㝽.͵.& b]g<' JMϊ z^Ǩo<BXi}#/4BⲊG =]HY":5ŎT7jI!|j$սT^e!сK;;B#-BU@*^OugkjMϱtJ% {&g@Ti/Iz[7K4,bI~}2 )B&iFW70XM[ -e>kf#dmԉ|I31Y@D:@j/vd'""M[r  ui2.i8Cn&28 KYI'嵡{AY &hZ3]tC$+DZVQef0#U͎9>M# x9GFTrsk7c*_L{5w#O^.J! j&P}Gm,nh5)b uG <8ET'ȏڢ0iGfPD7wQm- Z;_E0ˉ} Iu*C3֒4!f1ېGH"tt[DR@ u?tJyM ⵹c)cpy50t"iƺ(ޱwC7!`2aCyg@:M\3#K}+68gPyĜXD`1sA `K?RZKџ8~S8bH"݂qS#`.6YYT ((śI__a!E 6"7+xSr69 Z!T옎ѭ{Q(CkU״ML]5g_HO@b!qrj)]{N]EButC$+DZÃNsL9WjєaٳeΏVd?wyA-n`d1Cx"+;*Έ!6Gs]:trZg'.hK rr?׿/? Pis +fRXG#:@S5)݇ASD=P5# X&兓bew"|2E6K[5Yޔ\:e0$TbRp` ѷ3`=/;lAvūk@9cJz-wOy;=A.@{q є2 d$(nkCtRܭGTe#E"syD5y 6$c ( ͍eؽ1yq!-*7*t/n1oN:Q@q9۴v_{H#nMj>2Md(0ٗ;zr3؛Zv%6qOO#gҙg Iq]6.S_Րle*_8 ;P>s]3bo6J+|s;}<kB?"5!ucG崚&`󳲄 G崯]c۷p.!bѧR:*;'0AN2"J c8"v ߢm F|'e tľjt_ENy'#J:;-k+Awj]fR%W g*A_|]7 ^dr>K?FYItZi$-`eT\"YrW{{5twn&i 2i3 XsHM@_mmďq|΄!DJ6ӧ<$B-o%izM`!5Ӄ,lgɱ;iUT* ЯtRw o[o:(,uӪN; e ǧH[cr?jC /vɚ.:,\Gd2)/&ivbw~ThZ53Tɒ^&0 ='}{7:W^e-6c f*մphUu& )/|T),no%;Q2{u.=i& at֝Kp+J_7<:ضwlګ~ sw.~("~?G8OƹP-/姭礼 (^x,6Jj`\3&U,,]8ؚf bi!RggetRange(); if ($cell->isInRange($range) === true) { $this->tableName = $table->getName(); return $table; } } throw new Exception('Table for Structured Reference cannot be identified'); } /** * @throws Exception * @throws \PhpOffice\PhpSpreadsheet\Exception */ private function getTableByName(Cell $cell): Table { $table = $cell->getWorksheet()->getTableByName($this->tableName); if ($table === null) { throw new Exception("Table {$this->tableName} for Structured Reference cannot be located"); } return $table; } private function getColumns(Cell $cell, array $tableRange): array { $worksheet = $cell->getWorksheet(); $cellReference = $cell->getCoordinate(); $columns = []; $lastColumn = ++$tableRange[1][0]; for ($column = $tableRange[0][0]; $column !== $lastColumn; ++$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 => $columnName) { $columnName = str_replace("\u{a0}", ' ', $columnName); $reference = $this->adjustRowReference($columnName, $reference, $cell, $columnId); } /** @var string $reference */ 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 \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 \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; } 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; } return $this->totalsRow ?? $this->lastDataRow; } public function value(): string { return $this->value; } /** * @return array */ private function getRowsForColumnReference(string &$reference, int $startRow, int $endRow): array { $rowsSelected = false; foreach (self::ITEM_SPECIFIER_ROWS_SET as $rowReference) { $pattern = '/\[' . $rowReference . '\]/mui'; /** @var string $reference */ 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 => $columnName) { $columnName = str_replace("\u{a0}", ' ', $columnName); $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; } }