[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
   4  
   5  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   6  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   7  use SimpleXMLElement;
   8  
   9  class DataValidations
  10  {
  11      /** @var Worksheet */
  12      private $worksheet;
  13  
  14      /** @var SimpleXMLElement */
  15      private $worksheetXml;
  16  
  17      public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml)
  18      {
  19          $this->worksheet = $workSheet;
  20          $this->worksheetXml = $worksheetXml;
  21      }
  22  
  23      public function load(): void
  24      {
  25          foreach ($this->worksheetXml->dataValidations->dataValidation as $dataValidation) {
  26              // Uppercase coordinate
  27              $range = strtoupper((string) $dataValidation['sqref']);
  28              $rangeSet = explode(' ', $range);
  29              foreach ($rangeSet as $range) {
  30                  if (preg_match('/^[A-Z]{1,3}\\d{1,7}/', $range, $matches) === 1) {
  31                      // Ensure left/top row of range exists, thereby
  32                      // adjusting high row/column.
  33                      $this->worksheet->getCell($matches[0]);
  34                  }
  35              }
  36          }
  37          foreach ($this->worksheetXml->dataValidations->dataValidation as $dataValidation) {
  38              // Uppercase coordinate
  39              $range = strtoupper((string) $dataValidation['sqref']);
  40              $rangeSet = explode(' ', $range);
  41              foreach ($rangeSet as $range) {
  42                  $stRange = $this->worksheet->shrinkRangeToFit($range);
  43  
  44                  // Extract all cell references in $range
  45                  foreach (Coordinate::extractAllCellReferencesInRange($stRange) as $reference) {
  46                      // Create validation
  47                      $docValidation = $this->worksheet->getCell($reference)->getDataValidation();
  48                      $docValidation->setType((string) $dataValidation['type']);
  49                      $docValidation->setErrorStyle((string) $dataValidation['errorStyle']);
  50                      $docValidation->setOperator((string) $dataValidation['operator']);
  51                      $docValidation->setAllowBlank(filter_var($dataValidation['allowBlank'], FILTER_VALIDATE_BOOLEAN));
  52                      // showDropDown is inverted (works as hideDropDown if true)
  53                      $docValidation->setShowDropDown(!filter_var($dataValidation['showDropDown'], FILTER_VALIDATE_BOOLEAN));
  54                      $docValidation->setShowInputMessage(filter_var($dataValidation['showInputMessage'], FILTER_VALIDATE_BOOLEAN));
  55                      $docValidation->setShowErrorMessage(filter_var($dataValidation['showErrorMessage'], FILTER_VALIDATE_BOOLEAN));
  56                      $docValidation->setErrorTitle((string) $dataValidation['errorTitle']);
  57                      $docValidation->setError((string) $dataValidation['error']);
  58                      $docValidation->setPromptTitle((string) $dataValidation['promptTitle']);
  59                      $docValidation->setPrompt((string) $dataValidation['prompt']);
  60                      $docValidation->setFormula1((string) $dataValidation->formula1);
  61                      $docValidation->setFormula2((string) $dataValidation->formula2);
  62                      $docValidation->setSqref($range);
  63                  }
  64              }
  65          }
  66      }
  67  }