[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
   4  
   5  use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Styles as StyleReader;
   6  use PhpOffice\PhpSpreadsheet\Style\Conditional;
   7  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalDataBar;
   8  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormattingRuleExtension;
   9  use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormatValueObject;
  10  use PhpOffice\PhpSpreadsheet\Style\Style as Style;
  11  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  12  use SimpleXMLElement;
  13  use stdClass;
  14  
  15  class ConditionalStyles
  16  {
  17      /** @var Worksheet */
  18      private $worksheet;
  19  
  20      /** @var SimpleXMLElement */
  21      private $worksheetXml;
  22  
  23      /**
  24       * @var array
  25       */
  26      private $ns;
  27  
  28      /** @var array */
  29      private $dxfs;
  30  
  31      public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml, array $dxfs = [])
  32      {
  33          $this->worksheet = $workSheet;
  34          $this->worksheetXml = $worksheetXml;
  35          $this->dxfs = $dxfs;
  36      }
  37  
  38      public function load(): void
  39      {
  40          $this->setConditionalStyles(
  41              $this->worksheet,
  42              $this->readConditionalStyles($this->worksheetXml),
  43              $this->worksheetXml->extLst
  44          );
  45      }
  46  
  47      public function loadFromExt(StyleReader $styleReader): void
  48      {
  49          $this->ns = $this->worksheetXml->getNamespaces(true);
  50          $this->setConditionalsFromExt(
  51              $this->readConditionalsFromExt($this->worksheetXml->extLst, $styleReader)
  52          );
  53      }
  54  
  55      private function setConditionalsFromExt(array $conditionals): void
  56      {
  57          foreach ($conditionals as $conditionalRange => $cfRules) {
  58              ksort($cfRules);
  59              // Priority is used as the key for sorting; but may not start at 0,
  60              // so we use array_values to reset the index after sorting.
  61              $this->worksheet->getStyle($conditionalRange)
  62                  ->setConditionalStyles(array_values($cfRules));
  63          }
  64      }
  65  
  66      private function readConditionalsFromExt(SimpleXMLElement $extLst, StyleReader $styleReader): array
  67      {
  68          $conditionals = [];
  69  
  70          if (isset($extLst->ext['uri']) && (string) $extLst->ext['uri'] === '{78C0D931-6437-407d-A8EE-F0AAD7539E65}') {
  71              $conditionalFormattingRuleXml = $extLst->ext->children($this->ns['x14']);
  72              if (!$conditionalFormattingRuleXml->conditionalFormattings) {
  73                  return [];
  74              }
  75  
  76              foreach ($conditionalFormattingRuleXml->children($this->ns['x14']) as $extFormattingXml) {
  77                  $extFormattingRangeXml = $extFormattingXml->children($this->ns['xm']);
  78                  if (!$extFormattingRangeXml->sqref) {
  79                      continue;
  80                  }
  81  
  82                  $sqref = (string) $extFormattingRangeXml->sqref;
  83                  $extCfRuleXml = $extFormattingXml->cfRule;
  84  
  85                  $attributes = $extCfRuleXml->attributes();
  86                  if (!$attributes) {
  87                      continue;
  88                  }
  89                  $conditionType = (string) $attributes->type;
  90                  if (
  91                      !Conditional::isValidConditionType($conditionType) ||
  92                      $conditionType === Conditional::CONDITION_DATABAR
  93                  ) {
  94                      continue;
  95                  }
  96  
  97                  $priority = (int) $attributes->priority;
  98  
  99                  $conditional = $this->readConditionalRuleFromExt($extCfRuleXml, $attributes);
 100                  $cfStyle = $this->readStyleFromExt($extCfRuleXml, $styleReader);
 101                  $conditional->setStyle($cfStyle);
 102                  $conditionals[$sqref][$priority] = $conditional;
 103              }
 104          }
 105  
 106          return $conditionals;
 107      }
 108  
 109      private function readConditionalRuleFromExt(SimpleXMLElement $cfRuleXml, SimpleXMLElement $attributes): Conditional
 110      {
 111          $conditionType = (string) $attributes->type;
 112          $operatorType = (string) $attributes->operator;
 113  
 114          $operands = [];
 115          foreach ($cfRuleXml->children($this->ns['xm']) as $cfRuleOperandsXml) {
 116              $operands[] = (string) $cfRuleOperandsXml;
 117          }
 118  
 119          $conditional = new Conditional();
 120          $conditional->setConditionType($conditionType);
 121          $conditional->setOperatorType($operatorType);
 122          if (
 123              $conditionType === Conditional::CONDITION_CONTAINSTEXT ||
 124              $conditionType === Conditional::CONDITION_NOTCONTAINSTEXT ||
 125              $conditionType === Conditional::CONDITION_BEGINSWITH ||
 126              $conditionType === Conditional::CONDITION_ENDSWITH ||
 127              $conditionType === Conditional::CONDITION_TIMEPERIOD
 128          ) {
 129              $conditional->setText(array_pop($operands) ?? '');
 130          }
 131          $conditional->setConditions($operands);
 132  
 133          return $conditional;
 134      }
 135  
 136      private function readStyleFromExt(SimpleXMLElement $extCfRuleXml, StyleReader $styleReader): Style
 137      {
 138          $cfStyle = new Style(false, true);
 139          if ($extCfRuleXml->dxf) {
 140              $styleXML = $extCfRuleXml->dxf->children();
 141  
 142              if ($styleXML->borders) {
 143                  $styleReader->readBorderStyle($cfStyle->getBorders(), $styleXML->borders);
 144              }
 145              if ($styleXML->fill) {
 146                  $styleReader->readFillStyle($cfStyle->getFill(), $styleXML->fill);
 147              }
 148          }
 149  
 150          return $cfStyle;
 151      }
 152  
 153      private function readConditionalStyles(SimpleXMLElement $xmlSheet): array
 154      {
 155          $conditionals = [];
 156          foreach ($xmlSheet->conditionalFormatting as $conditional) {
 157              foreach ($conditional->cfRule as $cfRule) {
 158                  if (Conditional::isValidConditionType((string) $cfRule['type']) && isset($this->dxfs[(int) ($cfRule['dxfId'])])) {
 159                      $conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule;
 160                  } elseif ((string) $cfRule['type'] == Conditional::CONDITION_DATABAR) {
 161                      $conditionals[(string) $conditional['sqref']][(int) ($cfRule['priority'])] = $cfRule;
 162                  }
 163              }
 164          }
 165  
 166          return $conditionals;
 167      }
 168  
 169      private function setConditionalStyles(Worksheet $worksheet, array $conditionals, SimpleXMLElement $xmlExtLst): void
 170      {
 171          foreach ($conditionals as $cellRangeReference => $cfRules) {
 172              ksort($cfRules);
 173              $conditionalStyles = $this->readStyleRules($cfRules, $xmlExtLst);
 174  
 175              // Extract all cell references in $cellRangeReference
 176              $cellBlocks = explode(' ', str_replace('$', '', strtoupper($cellRangeReference)));
 177              foreach ($cellBlocks as $cellBlock) {
 178                  $worksheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles);
 179              }
 180          }
 181      }
 182  
 183      private function readStyleRules(array $cfRules, SimpleXMLElement $extLst): array
 184      {
 185          $conditionalFormattingRuleExtensions = ConditionalFormattingRuleExtension::parseExtLstXml($extLst);
 186          $conditionalStyles = [];
 187  
 188          foreach ($cfRules as $cfRule) {
 189              $objConditional = new Conditional();
 190              $objConditional->setConditionType((string) $cfRule['type']);
 191              $objConditional->setOperatorType((string) $cfRule['operator']);
 192  
 193              if ((string) $cfRule['text'] != '') {
 194                  $objConditional->setText((string) $cfRule['text']);
 195              } elseif ((string) $cfRule['timePeriod'] != '') {
 196                  $objConditional->setText((string) $cfRule['timePeriod']);
 197              }
 198  
 199              if (isset($cfRule['stopIfTrue']) && (int) $cfRule['stopIfTrue'] === 1) {
 200                  $objConditional->setStopIfTrue(true);
 201              }
 202  
 203              if (count($cfRule->formula) >= 1) {
 204                  foreach ($cfRule->formula as $formulax) {
 205                      $formula = (string) $formulax;
 206                      if ($formula === 'TRUE') {
 207                          $objConditional->addCondition(true);
 208                      } elseif ($formula === 'FALSE') {
 209                          $objConditional->addCondition(false);
 210                      } else {
 211                          $objConditional->addCondition($formula);
 212                      }
 213                  }
 214              } else {
 215                  $objConditional->addCondition('');
 216              }
 217  
 218              if (isset($cfRule->dataBar)) {
 219                  $objConditional->setDataBar(
 220                      $this->readDataBarOfConditionalRule($cfRule, $conditionalFormattingRuleExtensions) // @phpstan-ignore-line
 221                  );
 222              } else {
 223                  $objConditional->setStyle(clone $this->dxfs[(int) ($cfRule['dxfId'])]);
 224              }
 225  
 226              $conditionalStyles[] = $objConditional;
 227          }
 228  
 229          return $conditionalStyles;
 230      }
 231  
 232      /**
 233       * @param SimpleXMLElement|stdClass $cfRule
 234       */
 235      private function readDataBarOfConditionalRule($cfRule, array $conditionalFormattingRuleExtensions): ConditionalDataBar
 236      {
 237          $dataBar = new ConditionalDataBar();
 238          //dataBar attribute
 239          if (isset($cfRule->dataBar['showValue'])) {
 240              $dataBar->setShowValue((bool) $cfRule->dataBar['showValue']);
 241          }
 242  
 243          //dataBar children
 244          //conditionalFormatValueObjects
 245          $cfvoXml = $cfRule->dataBar->cfvo;
 246          $cfvoIndex = 0;
 247          foreach ((count($cfvoXml) > 1 ? $cfvoXml : [$cfvoXml]) as $cfvo) {
 248              if ($cfvoIndex === 0) {
 249                  $dataBar->setMinimumConditionalFormatValueObject(new ConditionalFormatValueObject((string) $cfvo['type'], (string) $cfvo['val']));
 250              }
 251              if ($cfvoIndex === 1) {
 252                  $dataBar->setMaximumConditionalFormatValueObject(new ConditionalFormatValueObject((string) $cfvo['type'], (string) $cfvo['val']));
 253              }
 254              ++$cfvoIndex;
 255          }
 256  
 257          //color
 258          if (isset($cfRule->dataBar->color)) {
 259              $dataBar->setColor((string) $cfRule->dataBar->color['rgb']);
 260          }
 261          //extLst
 262          $this->readDataBarExtLstOfConditionalRule($dataBar, $cfRule, $conditionalFormattingRuleExtensions);
 263  
 264          return $dataBar;
 265      }
 266  
 267      /**
 268       * @param SimpleXMLElement|stdClass $cfRule
 269       */
 270      private function readDataBarExtLstOfConditionalRule(ConditionalDataBar $dataBar, $cfRule, array $conditionalFormattingRuleExtensions): void
 271      {
 272          if (isset($cfRule->extLst)) {
 273              $ns = $cfRule->extLst->getNamespaces(true);
 274              foreach ((count($cfRule->extLst) > 0 ? $cfRule->extLst->ext : [$cfRule->extLst->ext]) as $ext) {
 275                  $extId = (string) $ext->children($ns['x14'])->id;
 276                  if (isset($conditionalFormattingRuleExtensions[$extId]) && (string) $ext['uri'] === '{B025F937-C7B1-47D3-B67F-A62EFF666E3E}') {
 277                      $dataBar->setConditionalFormattingRuleExt($conditionalFormattingRuleExtensions[$extId]);
 278                  }
 279              }
 280          }
 281      }
 282  }