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 PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormatValueObject; class ConditionalStyles > use PhpOffice\PhpSpreadsheet\Style\Style as Style;{> use SimpleXMLElement; private $worksheet; > use stdClass;> /** @var Worksheet */private $worksheetXml;> /** @var SimpleXMLElement */> /** private $dxfs; > * @var array > */ public function __construct(Worksheet $workSheet, \SimpleXMLElement $worksheetXml, array $dxfs = []) > private $ns; { > $this->worksheet = $workSheet; > /** @var array */< public function __construct(Worksheet $workSheet, \SimpleXMLElement $worksheetXml, array $dxfs = [])> public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml, array $dxfs = [])$this->dxfs = $dxfs; }< public function load()> public function load(): void{> $selectedCells = $this->worksheet->getSelectedCells(); $this->setConditionalStyles( >$this->worksheet,< $this->readConditionalStyles($this->worksheetXml)> $this->readConditionalStyles($this->worksheetXml), > $this->worksheetXml->extLst > ); > > $this->worksheet->setSelectedCells($selectedCells); > } > > public function loadFromExt(StyleReader $styleReader): void > { > $selectedCells = $this->worksheet->getSelectedCells(); > > $this->ns = $this->worksheetXml->getNamespaces(true); > $this->setConditionalsFromExt( > $this->readConditionalsFromExt($this->worksheetXml->extLst, $styleReader));> } > $this->worksheet->setSelectedCells($selectedCells);< 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 = []; > > 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 ( > !Conditional::isValidConditionType($conditionType) || > $conditionType === Conditional::CONDITION_DATABAR > ) { > continue; > } > > $priority = (int) $attributes->priority; > > $conditional = $this->readConditionalRuleFromExt($extCfRuleXml, $attributes); > $cfStyle = $this->readStyleFromExt($extCfRuleXml, $styleReader); > $conditional->setStyle($cfStyle); > $conditionals[$sqref][$priority] = $conditional; > } > } > > return $conditionals; > } > > private function readConditionalRuleFromExt(SimpleXMLElement $cfRuleXml, SimpleXMLElement $attributes): Conditional > { > $conditionType = (string) $attributes->type; > $operatorType = (string) $attributes->operator; > > $operands = []; > foreach ($cfRuleXml->children($this->ns['xm']) as $cfRuleOperandsXml) { > $operands[] = (string) $cfRuleOperandsXml; > } > > $conditional = new Conditional(); > $conditional->setConditionType($conditionType); > $conditional->setOperatorType($operatorType); > if ( > $conditionType === Conditional::CONDITION_CONTAINSTEXT || > $conditionType === Conditional::CONDITION_NOTCONTAINSTEXT || > $conditionType === Conditional::CONDITION_BEGINSWITH || > $conditionType === Conditional::CONDITION_ENDSWITH || > $conditionType === Conditional::CONDITION_TIMEPERIOD > ) { > $conditional->setText(array_pop($operands) ?? ''); > } > $conditional->setConditions($operands); > > return $conditional; > } > > private function readStyleFromExt(SimpleXMLElement $extCfRuleXml, StyleReader $styleReader): Style > { > $cfStyle = new Style(false, true); > if ($extCfRuleXml->dxf) { > $styleXML = $extCfRuleXml->dxf->children(); > > if ($styleXML->borders) { > $styleReader->readBorderStyle($cfStyle->getBorders(), $styleXML->borders); > } > 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 (((string) $cfRule['type'] == Conditional::CONDITION_NONE < || (string) $cfRule['type'] == Conditional::CONDITION_CELLIS < || (string) $cfRule['type'] == Conditional::CONDITION_CONTAINSTEXT < || (string) $cfRule['type'] == Conditional::CONDITION_EXPRESSION) < && isset($this->dxfs[(int) ($cfRule['dxfId'])])) {> if (Conditional::isValidConditionType((string) $cfRule['type']) && (!isset($cfRule['dxfId']) || isset($this->dxfs[(int) ($cfRule['dxfId'])]))) { > $conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule; > } elseif ((string) $cfRule['type'] == Conditional::CONDITION_DATABAR) {$conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule; } } } return $conditionals; }< private function setConditionalStyles(Worksheet $worksheet, array $conditionals)> private function setConditionalStyles(Worksheet $worksheet, array $conditionals, SimpleXMLElement $xmlExtLst): void{< foreach ($conditionals as $ref => $cfRules) {> foreach ($conditionals as $cellRangeReference => $cfRules) {ksort($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)));foreach ($cellBlocks as $cellBlock) { $worksheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles); } } }< private function readStyleRules($cfRules)> private function readStyleRules(array $cfRules, SimpleXMLElement $extLst): array{> $conditionalFormattingRuleExtensions = ConditionalFormattingRuleExtension::parseExtLstXml($extLst);$conditionalStyles = [];>foreach ($cfRules as $cfRule) { $objConditional = new Conditional(); $objConditional->setConditionType((string) $cfRule['type']); $objConditional->setOperatorType((string) $cfRule['operator']);> $objConditional->setNoFormatSet(!isset($cfRule['dxfId']));if ((string) $cfRule['text'] != '') { $objConditional->setText((string) $cfRule['text']);> } elseif ((string) $cfRule['timePeriod'] != '') { } > $objConditional->setText((string) $cfRule['timePeriod']);if (isset($cfRule['stopIfTrue']) && (int) $cfRule['stopIfTrue'] === 1) { $objConditional->setStopIfTrue(true); }< 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); > }} } else {< $objConditional->addCondition((string) $cfRule->formula);> $objConditional->addCondition('');}> $objConditional->setStyle(clone $this->dxfs[(int) ($cfRule['dxfId'])]); > if (isset($cfRule->dataBar)) { $conditionalStyles[] = $objConditional; > $objConditional->setDataBar( } > $this->readDataBarOfConditionalRule($cfRule, $conditionalFormattingRuleExtensions) // @phpstan-ignore-line > ); return $conditionalStyles; > } elseif (isset($cfRule['dxfId'])) {}> } } >> } > > /** > * @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]); > } > } > }