See Release Notes
Long Term Support Release
<?php namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;> use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Styles as StyleReader;use PhpOffice\PhpSpreadsheet\Style\Conditional;> use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalDataBar; use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; > use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormattingRuleExtension; use SimpleXMLElement; > use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormatValueObject; > use PhpOffice\PhpSpreadsheet\Style\Style as Style;class ConditionalStyles> use stdClass;{> /** @var Worksheet */private $worksheet;> /** @var SimpleXMLElement */private $worksheetXml;> /** private $dxfs; > * @var array > */ public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml, array $dxfs = []) > private $ns; { > $this->worksheet = $workSheet; > /** @var array */$this->worksheetXml = $worksheetXml; $this->dxfs = $dxfs; } public function load(): void { $this->setConditionalStyles( $this->worksheet,< $this->readConditionalStyles($this->worksheetXml)> $this->readConditionalStyles($this->worksheetXml), > $this->worksheetXml->extLst > ); > } > > public function loadFromExt(StyleReader $styleReader): void > { > $this->ns = $this->worksheetXml->getNamespaces(true); > $this->setConditionalsFromExt( > $this->readConditionalsFromExt($this->worksheetXml->extLst, $styleReader)); }< private function readConditionalStyles($xmlSheet)> private function setConditionalsFromExt(array $conditionals): void > { > foreach ($conditionals as $conditionalRange => $cfRules) { > ksort($cfRules); > // Priority is used as the key for sorting; but may not start at 0, > // so we use array_values to reset the index after sorting. > $this->worksheet->getStyle($conditionalRange) > ->setConditionalStyles(array_values($cfRules)); > } > } > > private function readConditionalsFromExt(SimpleXMLElement $extLst, StyleReader $styleReader): array{ $conditionals = [];< foreach ($xmlSheet->conditionalFormatting as $conditional) { < foreach ($conditional->cfRule as $cfRule) {> > if (isset($extLst->ext['uri']) && (string) $extLst->ext['uri'] === '{78C0D931-6437-407d-A8EE-F0AAD7539E65}') { > $conditionalFormattingRuleXml = $extLst->ext->children($this->ns['x14']); > if (!$conditionalFormattingRuleXml->conditionalFormattings) { > return []; > } > > foreach ($conditionalFormattingRuleXml->children($this->ns['x14']) as $extFormattingXml) { > $extFormattingRangeXml = $extFormattingXml->children($this->ns['xm']); > if (!$extFormattingRangeXml->sqref) { > continue; > } > > $sqref = (string) $extFormattingRangeXml->sqref; > $extCfRuleXml = $extFormattingXml->cfRule; > > $attributes = $extCfRuleXml->attributes(); > if (!$attributes) { > continue; > } > $conditionType = (string) $attributes->type;if (< ((string) $cfRule['type'] == Conditional::CONDITION_NONE < || (string) $cfRule['type'] == Conditional::CONDITION_CELLIS < || (string) $cfRule['type'] == Conditional::CONDITION_CONTAINSTEXT < || (string) $cfRule['type'] == Conditional::CONDITION_CONTAINSBLANKS < || (string) $cfRule['type'] == Conditional::CONDITION_NOTCONTAINSBLANKS < || (string) $cfRule['type'] == Conditional::CONDITION_EXPRESSION) < && isset($this->dxfs[(int) ($cfRule['dxfId'])])> !Conditional::isValidConditionType($conditionType) || > $conditionType === Conditional::CONDITION_DATABAR) {> continue; $conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule; > } } > } > $priority = (int) $attributes->priority; } > > $conditional = $this->readConditionalRuleFromExt($extCfRuleXml, $attributes); return $conditionals; > $cfStyle = $this->readStyleFromExt($extCfRuleXml, $styleReader); } > $conditional->setStyle($cfStyle); > $conditionals[$sqref][$priority] = $conditional; private function setConditionalStyles(Worksheet $worksheet, array $conditionals): void > } { > } foreach ($conditionals as $ref => $cfRules) { > ksort($cfRules); > return $conditionals; $conditionalStyles = $this->readStyleRules($cfRules); > } > // Extract all cell references in $ref > private function readConditionalRuleFromExt(SimpleXMLElement $cfRuleXml, SimpleXMLElement $attributes): Conditional $cellBlocks = explode(' ', str_replace('$', '', strtoupper($ref))); > { foreach ($cellBlocks as $cellBlock) { > $conditionType = (string) $attributes->type; $worksheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles); > $operatorType = (string) $attributes->operator; } > } > $operands = []; } > foreach ($cfRuleXml->children($this->ns['xm']) as $cfRuleOperandsXml) { > $operands[] = (string) $cfRuleOperandsXml; private function readStyleRules($cfRules) > } { > $conditionalStyles = []; > $conditional = new Conditional(); foreach ($cfRules as $cfRule) { > $conditional->setConditionType($conditionType); $objConditional = new Conditional(); > $conditional->setOperatorType($operatorType); $objConditional->setConditionType((string) $cfRule['type']); > if ( $objConditional->setOperatorType((string) $cfRule['operator']); > $conditionType === Conditional::CONDITION_CONTAINSTEXT || > $conditionType === Conditional::CONDITION_NOTCONTAINSTEXT || if ((string) $cfRule['text'] != '') { > $conditionType === Conditional::CONDITION_BEGINSWITH || $objConditional->setText((string) $cfRule['text']); > $conditionType === Conditional::CONDITION_ENDSWITH || } > $conditionType === Conditional::CONDITION_TIMEPERIOD > ) { if (isset($cfRule['stopIfTrue']) && (int) $cfRule['stopIfTrue'] === 1) { > $conditional->setText(array_pop($operands) ?? ''); $objConditional->setStopIfTrue(true); > } } > $conditional->setConditions($operands); > if (count($cfRule->formula) > 1) { > return $conditional; foreach ($cfRule->formula as $formula) { > } $objConditional->addCondition((string) $formula); > } > private function readStyleFromExt(SimpleXMLElement $extCfRuleXml, StyleReader $styleReader): Style } else { > { $objConditional->addCondition((string) $cfRule->formula); > $cfStyle = new Style(false, true); } > if ($extCfRuleXml->dxf) { $objConditional->setStyle(clone $this->dxfs[(int) ($cfRule['dxfId'])]); > $styleXML = $extCfRuleXml->dxf->children(); $conditionalStyles[] = $objConditional; > } > if ($styleXML->borders) { > $styleReader->readBorderStyle($cfStyle->getBorders(), $styleXML->borders); return $conditionalStyles; > } } > if ($styleXML->fill) { } > $styleReader->readFillStyle($cfStyle->getFill(), $styleXML->fill); > } > } > > return $cfStyle; > } > > private function readConditionalStyles(SimpleXMLElement $xmlSheet): array > { > $conditionals = []; > foreach ($xmlSheet->conditionalFormatting as $conditional) { > foreach ($conditional->cfRule as $cfRule) { > if (Conditional::isValidConditionType((string) $cfRule['type']) && isset($this->dxfs[(int) ($cfRule['dxfId'])])) { > $conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule; > } elseif ((string) $cfRule['type'] == Conditional::CONDITION_DATABAR) {< private function setConditionalStyles(Worksheet $worksheet, array $conditionals): void> private function setConditionalStyles(Worksheet $worksheet, array $conditionals, SimpleXMLElement $xmlExtLst): void< foreach ($conditionals as $ref => $cfRules) {> foreach ($conditionals as $cellRangeReference => $cfRules) {< $conditionalStyles = $this->readStyleRules($cfRules);> $conditionalStyles = $this->readStyleRules($cfRules, $xmlExtLst);< // Extract all cell references in $ref < $cellBlocks = explode(' ', str_replace('$', '', strtoupper($ref)));> // Extract all cell references in $cellRangeReference > $cellBlocks = explode(' ', str_replace('$', '', strtoupper($cellRangeReference)));< private function readStyleRules($cfRules)> private function readStyleRules(array $cfRules, SimpleXMLElement $extLst): array> $conditionalFormattingRuleExtensions = ConditionalFormattingRuleExtension::parseExtLstXml($extLst);>> } elseif ((string) $cfRule['timePeriod'] != '') { > $objConditional->setText((string) $cfRule['timePeriod']);< if (count($cfRule->formula) > 1) { < foreach ($cfRule->formula as $formula) { < $objConditional->addCondition((string) $formula);> if (count($cfRule->formula) >= 1) { > foreach ($cfRule->formula as $formulax) { > $formula = (string) $formulax; > if ($formula === 'TRUE') { > $objConditional->addCondition(true); > } elseif ($formula === 'FALSE') { > $objConditional->addCondition(false); > } else { > $objConditional->addCondition($formula); > }< $objConditional->addCondition((string) $cfRule->formula);> $objConditional->addCondition('');> > if (isset($cfRule->dataBar)) { > $objConditional->setDataBar( > $this->readDataBarOfConditionalRule($cfRule, $conditionalFormattingRuleExtensions) // @phpstan-ignore-line > ); > } else {> } >> } > > /** > * @param SimpleXMLElement|stdClass $cfRule > */ > private function readDataBarOfConditionalRule($cfRule, array $conditionalFormattingRuleExtensions): ConditionalDataBar > { > $dataBar = new ConditionalDataBar(); > //dataBar attribute > if (isset($cfRule->dataBar['showValue'])) { > $dataBar->setShowValue((bool) $cfRule->dataBar['showValue']); > } > > //dataBar children > //conditionalFormatValueObjects > $cfvoXml = $cfRule->dataBar->cfvo; > $cfvoIndex = 0; > foreach ((count($cfvoXml) > 1 ? $cfvoXml : [$cfvoXml]) as $cfvo) { > if ($cfvoIndex === 0) { > $dataBar->setMinimumConditionalFormatValueObject(new ConditionalFormatValueObject((string) $cfvo['type'], (string) $cfvo['val'])); > } > if ($cfvoIndex === 1) { > $dataBar->setMaximumConditionalFormatValueObject(new ConditionalFormatValueObject((string) $cfvo['type'], (string) $cfvo['val'])); > } > ++$cfvoIndex; > } > > //color > if (isset($cfRule->dataBar->color)) { > $dataBar->setColor((string) $cfRule->dataBar->color['rgb']); > } > //extLst > $this->readDataBarExtLstOfConditionalRule($dataBar, $cfRule, $conditionalFormattingRuleExtensions); > > return $dataBar; > } > > /** > * @param SimpleXMLElement|stdClass $cfRule > */ > private function readDataBarExtLstOfConditionalRule(ConditionalDataBar $dataBar, $cfRule, array $conditionalFormattingRuleExtensions): void > { > if (isset($cfRule->extLst)) { > $ns = $cfRule->extLst->getNamespaces(true); > foreach ((count($cfRule->extLst) > 0 ? $cfRule->extLst->ext : [$cfRule->extLst->ext]) as $ext) { > $extId = (string) $ext->children($ns['x14'])->id; > if (isset($conditionalFormattingRuleExtensions[$extId]) && (string) $ext['uri'] === '{B025F937-C7B1-47D3-B67F-A62EFF666E3E}') { > $dataBar->setConditionalFormattingRuleExt($conditionalFormattingRuleExtensions[$extId]); > } > } > }