'=', Conditional::OPERATOR_GREATERTHAN => '>', Conditional::OPERATOR_GREATERTHANOREQUAL => '>=', Conditional::OPERATOR_LESSTHAN => '<', Conditional::OPERATOR_LESSTHANOREQUAL => '<=', Conditional::OPERATOR_NOTEQUAL => '<>', ]; public const COMPARISON_RANGE_OPERATORS = [ Conditional::OPERATOR_BETWEEN => 'IF(AND(A1>=%s,A1<=%s),TRUE,FALSE)', Conditional::OPERATOR_NOTBETWEEN => 'IF(AND(A1>=%s,A1<=%s),FALSE,TRUE)', ]; public const COMPARISON_DUPLICATES_OPERATORS = [ Conditional::CONDITION_DUPLICATES => "COUNTIF('%s'!%s,%s)>1", Conditional::CONDITION_UNIQUE => "COUNTIF('%s'!%s,%s)=1", ]; protected Cell $cell; protected int $cellRow; protected Worksheet $worksheet; protected int $cellColumn; protected string $conditionalRange; protected string $referenceCell; protected int $referenceRow; protected int $referenceColumn; protected Calculation $engine; public function __construct(Cell $cell, string $conditionalRange) { $this->cell = $cell; $this->worksheet = $cell->getWorksheet(); [$this->cellColumn, $this->cellRow] = Coordinate::indexesFromString($this->cell->getCoordinate()); $this->setReferenceCellForExpressions($conditionalRange); $this->engine = Calculation::getInstance($this->worksheet->getParent()); } protected function setReferenceCellForExpressions(string $conditionalRange): void { $conditionalRange = Coordinate::splitRange(str_replace('$', '', strtoupper($conditionalRange))); [$this->referenceCell] = $conditionalRange[0]; [$this->referenceColumn, $this->referenceRow] = Coordinate::indexesFromString($this->referenceCell); // Convert our conditional range to an absolute conditional range, so it can be used "pinned" in formulae $rangeSets = []; foreach ($conditionalRange as $rangeSet) { $absoluteRangeSet = array_map( [Coordinate::class, 'absoluteCoordinate'], $rangeSet ); $rangeSets[] = implode(':', $absoluteRangeSet); } $this->conditionalRange = implode(',', $rangeSets); } public function evaluateConditional(Conditional $conditional): bool { // Some calculations may modify the stored cell; so reset it before every evaluation. $cellColumn = Coordinate::stringFromColumnIndex($this->cellColumn); $cellAddress = "{$cellColumn}{$this->cellRow}"; $this->cell = $this->worksheet->getCell($cellAddress); return match ($conditional->getConditionType()) { Conditional::CONDITION_CELLIS => $this->processOperatorComparison($conditional), Conditional::CONDITION_DUPLICATES, Conditional::CONDITION_UNIQUE => $this->processDuplicatesComparison($conditional), // Expression is NOT(ISERROR(SEARCH("",))) Conditional::CONDITION_CONTAINSTEXT, // Expression is ISERROR(SEARCH("",)) Conditional::CONDITION_NOTCONTAINSTEXT, // Expression is LEFT(,LEN(""))="" Conditional::CONDITION_BEGINSWITH, // Expression is RIGHT(,LEN(""))="" Conditional::CONDITION_ENDSWITH, // Expression is LEN(TRIM())=0 Conditional::CONDITION_CONTAINSBLANKS, // Expression is LEN(TRIM())>0 Conditional::CONDITION_NOTCONTAINSBLANKS, // Expression is ISERROR() Conditional::CONDITION_CONTAINSERRORS, // Expression is NOT(ISERROR()) Conditional::CONDITION_NOTCONTAINSERRORS, // 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()) Conditional::CONDITION_TIMEPERIOD, Conditional::CONDITION_EXPRESSION => $this->processExpression($conditional), default => false, }; } protected function wrapValue(mixed $value): float|int|string { if (!is_numeric($value)) { if (is_bool($value)) { return $value ? 'TRUE' : 'FALSE'; } elseif ($value === null) { return 'NULL'; } return '"' . $value . '"'; } return $value; } protected function wrapCellValue(): float|int|string { return $this->wrapValue($this->cell->getCalculatedValue()); } protected function conditionCellAdjustment(array $matches): float|int|string { $column = $matches[6]; $row = $matches[7]; if (!str_contains($column, '$')) { $column = Coordinate::columnIndexFromString($column); $column += $this->cellColumn - $this->referenceColumn; $column = Coordinate::stringFromColumnIndex($column); } if (!str_contains($row, '$')) { $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) { return false; } return $result; } }