Edit File - File Manager

Edit File: DataValidations.php

<?php

namespace PhpOffice\PhpSpreadsheet\Reader\Xml;

use PhpOffice\PhpSpreadsheet\Cell\AddressHelper;
use PhpOffice\PhpSpreadsheet\Cell\AddressRange;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use SimpleXMLElement;

class DataValidations
{
    private const OPERATOR_MAPPINGS = [
        'between' => 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);
            }
        }
    }
}

Back to File Manager