DataValidation::OPERATOR_BETWEEN, 'equal' => DataValidation::OPERATOR_EQUAL, 'greater' => DataValidation::OPERATOR_GREATERTHAN, 'greaterorequal' => DataValidation::OPERATOR_GREATERTHANOREQUAL, 'less' => DataValidation::OPERATOR_LESSTHAN, 'lessorequal' => DataValidation::OPERATOR_LESSTHANOREQUAL, 'notbetween' => DataValidation::OPERATOR_NOTBETWEEN, 'notequal' => DataValidation::OPERATOR_NOTEQUAL, ]; private const TYPE_MAPPINGS = [ 'textlength' => DataValidation::TYPE_TEXTLENGTH, ]; private int $thisRow = 0; private int $thisColumn = 0; private function replaceR1C1(array $matches): string { return AddressHelper::convertToA1($matches[0], $this->thisRow, $this->thisColumn, false); } public function loadDataValidations(SimpleXMLElement $worksheet, Spreadsheet $spreadsheet): void { $xmlX = $worksheet->children(Namespaces::URN_EXCEL); $sheet = $spreadsheet->getActiveSheet(); /** @var callable */ $pregCallback = [$this, 'replaceR1C1']; foreach ($xmlX->DataValidation as $dataValidation) { $cells = []; $validation = new DataValidation(); // set defaults $validation->setShowDropDown(true); $validation->setShowInputMessage(true); $validation->setShowErrorMessage(true); $validation->setShowDropDown(true); $this->thisRow = 1; $this->thisColumn = 1; foreach ($dataValidation as $tagName => $tagValue) { $tagValue = (string) $tagValue; $tagValueLower = strtolower($tagValue); switch ($tagName) { case 'Range': foreach (explode(',', $tagValue) as $range) { $cell = ''; if (preg_match('/^R(\d+)C(\d+):R(\d+)C(\d+)$/', (string) $range, $selectionMatches) === 1) { // range $firstCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2]) . $selectionMatches[1]; $cell = $firstCell . ':' . Coordinate::stringFromColumnIndex((int) $selectionMatches[4]) . $selectionMatches[3]; $this->thisRow = (int) $selectionMatches[1]; $this->thisColumn = (int) $selectionMatches[2]; $sheet->getCell($firstCell); } elseif (preg_match('/^R(\d+)C(\d+)$/', (string) $range, $selectionMatches) === 1) { // cell $cell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2]) . $selectionMatches[1]; $sheet->getCell($cell); $this->thisRow = (int) $selectionMatches[1]; $this->thisColumn = (int) $selectionMatches[2]; } elseif (preg_match('/^C(\d+)$/', (string) $range, $selectionMatches) === 1) { // column $firstCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[1]) . '1'; $cell = $firstCell . ':' . Coordinate::stringFromColumnIndex((int) $selectionMatches[1]) . ((string) AddressRange::MAX_ROW); $this->thisColumn = (int) $selectionMatches[1]; $sheet->getCell($firstCell); } elseif (preg_match('/^R(\d+)$/', (string) $range, $selectionMatches)) { // row $firstCell = 'A' . $selectionMatches[1]; $cell = $firstCell . ':' . AddressRange::MAX_COLUMN . $selectionMatches[1]; $this->thisRow = (int) $selectionMatches[1]; $sheet->getCell($firstCell); } $validation->setSqref($cell); $stRange = $sheet->shrinkRangeToFit($cell); $cells = array_merge($cells, Coordinate::extractAllCellReferencesInRange($stRange)); } break; case 'Type': $validation->setType(self::TYPE_MAPPINGS[$tagValueLower] ?? $tagValueLower); break; case 'Qualifier': $validation->setOperator(self::OPERATOR_MAPPINGS[$tagValueLower] ?? $tagValueLower); break; case 'InputTitle': $validation->setPromptTitle($tagValue); break; case 'InputMessage': $validation->setPrompt($tagValue); break; case 'InputHide': $validation->setShowInputMessage(false); break; case 'ErrorStyle': $validation->setErrorStyle($tagValueLower); break; case 'ErrorTitle': $validation->setErrorTitle($tagValue); break; case 'ErrorMessage': $validation->setError($tagValue); break; case 'ErrorHide': $validation->setShowErrorMessage(false); break; case 'ComboHide': $validation->setShowDropDown(false); break; case 'UseBlank': $validation->setAllowBlank(true); break; case 'CellRangeList': // FIXME missing FIXME break; case 'Min': case 'Value': $tagValue = (string) preg_replace_callback(AddressHelper::R1C1_COORDINATE_REGEX, $pregCallback, $tagValue); $validation->setFormula1($tagValue); break; case 'Max': $tagValue = (string) preg_replace_callback(AddressHelper::R1C1_COORDINATE_REGEX, $pregCallback, $tagValue); $validation->setFormula2($tagValue); break; } } foreach ($cells as $cell) { $sheet->getCell($cell)->setDataValidation(clone $validation); } } } }