nfTlWNl rn Ѻq$³*L ZR]_-H#GP~ wz9SͰ 0QEe 6\`Lٔ*ܝIc3ٿ ZmWNj ܸr+A֔|4W٤D̲#cڒr_(TD]ͫ\]*uQlfwhvĘ4v塑L_v xV8UE &-Hq.qXxe\`Lٔ*ܝIcO*a[/Mgnl\`Lٔ*ܝIc82zHwy ewذzY+g2=l`MpŞlc—d#>jSQV7 v;1F?%r :2<-|7_UW Ƿ;@&J9$F6ezʝ%?B$~=u3|Y%˜DPn0G(Ur_Y+ƈu̙-cE @5^ВǖXvL-DOW =]9 hRg"RwNiH ت3x2=:)ܿ0Nt!GDxxoj{Q{h߁a7%bFi (Bh@<ٴ6{&|N_}L/d ѻQ7UEiT N30eC_Q6&'$#X'X:^ fO6ڿ1u4AWH߿!!!r]g\)EU?>Mf:E>+4Wtj$ yvw:t hz^be3"i4pgGIx/Ec`VGÄLVMkW9=Ki7F%[&[qǖXvL-DOWr;3~ Bƕ45)0ZWOC&*X ~ s_& tiRP_}eY`Gu|^Ryk:4-a;ZxMo\t%3-xV}XHO{U.8n;Lhz`&.`pQS 4iUcGWK9a[eJ;j*i} L AOÉM>4b^BFwnUid/}*"R̗<(m 3X *V>P- BKtfgc(d d4Y_),-'!ń=^6;*ҫjFJ,M>Pc)ZA?7kޢ_BFH6zI#G+c-nDY- Bq{&L,1ž՗1=d1s˫KInm Ϣe5JPysҋ"s=O6 ~)vsqL;'nj% -ʝ: |NNTNiq+B~GW辚]lW{=r # ы}jM| *M%v1 aTJ Xv XcvCp;܏k%6Y<C$]b$.73>v ^$GBkYEX^k<|M<QJ *] [^7W@q׸y뀜B}{_sZ:,auyo=5/ }Hlt8{%gUV-p$$j`o20jzFXvvaIz`8s5i V{/uľHk9m^x_9GB/@T $7I8+ Q[;= tWlJ'@Grd8UAB.|JIl!t' KM@BeW}I>H~gR b2}>A4Ld1zr& !3cixq/f 7`tpݠʝDȏO-B,t*GK2^0wz1$00[ZY-heCwR a_7tgW>*ZèíyhϸS>cj !s5j ,Li0JC rH@, ?Cu%'i N=`xy}DWr&<p/qtSx+&P|{u ~(@o *wDAa;\Q-I^aϓ[&jvHxOOH ']M yADiDÒI[D.T(?$PV.*]S"1D+0S;q-q2!+ZA L~74˗G#etSZ\)3{Mk@ncN!XDkzT|g'Z6ohlÏ&S pN X<$NB_lQ9\Mݷ[4<" '+7[7 u,GHM%qO": u@ H;~O}1 d233tgX9Udx)^y mܻ@(d}V9QplzʴY+M1RĮs_QPoLj~| 1'nu!C~{;PWKf dW-=uz(}(`e|1$nH?q],© jب8\UOߢs\'4f(sEy9}E06 N:TG/0 ORWAuu'gVZSf$dڙnF 4|nfa^^g|r}O$R.vWIQk84kEpsSDQ\ki&9V-RreW ݋7Ȥ;e/̤xgͧf&߃ $pH)&d} ĢQ$3S nngiwF ⺱-h ?h q ]/1[oEAH.w;D䑛Xry@_o-"kZs*MyhȈ#Uo$uN-R~1~uh6w෋@DAb _WAuu'g5/)i Vˊqa!Kahr!o-"kZs*'AҐTe>K)5y$TD] $SpvXdx_HeR> F{sIZC-ae3Uonal::CONDITION_ENDSWITH: // Expression is RIGHT(,LEN(""))="" case Conditional::CONDITION_CONTAINSBLANKS: // Expression is LEN(TRIM())=0 case Conditional::CONDITION_NOTCONTAINSBLANKS: // Expression is LEN(TRIM())>0 case Conditional::CONDITION_CONTAINSERRORS: // Expression is ISERROR() case Conditional::CONDITION_NOTCONTAINSERRORS: // Expression is NOT(ISERROR()) case Conditional::CONDITION_TIMEPERIOD: // Expression varies, depending on specified timePeriod value, e.g. // Yesterday FLOOR(,1)=TODAY()-1 // Today FLOOR(,1)=TODAY() // Tomorrow FLOOR(,1)=TODAY()+1 // Last 7 Days AND(TODAY()-FLOOR(,1)<=6,FLOOR(,1)<=TODAY()) case Conditional::CONDITION_EXPRESSION: return $this->processExpression($conditional); } return false; } /** * @param mixed $value * * @return float|int|string */ protected function wrapValue($value) { if (!is_numeric($value)) { if (is_bool($value)) { return $value ? 'TRUE' : 'FALSE'; } elseif ($value === null) { return 'NULL'; } return '"' . $value . '"'; } return $value; } /** * @return float|int|string */ protected function wrapCellValue() { return $this->wrapValue($this->cell->getCalculatedValue()); } /** * @return float|int|string */ protected function conditionCellAdjustment(array $matches) { $column = $matches[6]; $row = $matches[7]; if (strpos($column, '$') === false) { $column = Coordinate::columnIndexFromString($column); $column += $this->cellColumn - $this->referenceColumn; $column = Coordinate::stringFromColumnIndex($column); } if (strpos($row, '$') === false) { $row += $this->cellRow - $this->referenceRow; } if (!empty($matches[4])) { $worksheet = $this->worksheet->getParentOrThrow()->getSheetByName(trim($matches[4], "'")); if ($worksheet === null) { return $this->wrapValue(null); } return $this->wrapValue( $worksheet ->getCell(str_replace('$', '', "{$column}{$row}")) ->getCalculatedValue() ); } return $this->wrapValue( $this->worksheet ->getCell(str_replace('$', '', "{$column}{$row}")) ->getCalculatedValue() ); } protected function cellConditionCheck(string $condition): string { $splitCondition = explode(Calculation::FORMULA_STRING_QUOTE, $condition); $i = false; foreach ($splitCondition as &$value) { // Only count/replace in alternating array entries (ie. not in quoted strings) $i = $i === false; if ($i) { $value = (string) preg_replace_callback( '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/i', [$this, 'conditionCellAdjustment'], $value ); } } unset($value); // Then rebuild the condition string to return it return implode(Calculation::FORMULA_STRING_QUOTE, $splitCondition); } protected function adjustConditionsForCellReferences(array $conditions): array { return array_map( [$this, 'cellConditionCheck'], $conditions ); } protected function processOperatorComparison(Conditional $conditional): bool { if (array_key_exists($conditional->getOperatorType(), self::COMPARISON_RANGE_OPERATORS)) { return $this->processRangeOperator($conditional); } $operator = self::COMPARISON_OPERATORS[$conditional->getOperatorType()]; $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions()); $expression = sprintf('%s%s%s', (string) $this->wrapCellValue(), $operator, (string) array_pop($conditions)); return $this->evaluateExpression($expression); } protected function processRangeOperator(Conditional $conditional): bool { $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions()); sort($conditions); $expression = sprintf( (string) preg_replace( '/\bA1\b/i', (string) $this->wrapCellValue(), self::COMPARISON_RANGE_OPERATORS[$conditional->getOperatorType()] ), ...$conditions ); return $this->evaluateExpression($expression); } protected function processDuplicatesComparison(Conditional $conditional): bool { $worksheetName = $this->cell->getWorksheet()->getTitle(); $expression = sprintf( self::COMPARISON_DUPLICATES_OPERATORS[$conditional->getConditionType()], $worksheetName, $this->conditionalRange, $this->cellConditionCheck($this->cell->getCalculatedValue()) ); return $this->evaluateExpression($expression); } protected function processExpression(Conditional $conditional): bool { $conditions = $this->adjustConditionsForCellReferences($conditional->getConditions()); $expression = array_pop($conditions); $expression = (string) preg_replace( '/\b' . $this->referenceCell . '\b/i', (string) $this->wrapCellValue(), $expression ); return $this->evaluateExpression($expression); } protected function evaluateExpression(string $expression): bool { $expression = "={$expression}"; try { $this->engine->flushInstance(); $result = (bool) $this->engine->calculateFormula($expression); } catch (Exception $e) { return false; } return $result; } }