Proyectos de Subversion Moodle

Rev

| Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1441 ariadna 1 
<?php
2 
 
3 
namespace PhpOffice\PhpSpreadsheet\Cell;
4 
 
5 
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6 
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
7 
use PhpOffice\PhpSpreadsheet\Exception;
8 
 
9 
/**
10 
 * Validate a cell value according to its validation rules.
11 
 */
12 
class DataValidator
13 
{
14 
    /**
15 
     * Does this cell contain valid value?
16 
     *
17 
     * @param Cell $cell Cell to check the value
18 
     */
19 
    public function isValid(Cell $cell): bool
20 
    {
21 
        if (!$cell->hasDataValidation() || $cell->getDataValidation()->getType() === DataValidation::TYPE_NONE) {
22 
            return true;
23 
        }
24 
 
25 
        $cellValue = $cell->getValue();
26 
        $dataValidation = $cell->getDataValidation();
27 
 
28 
        if (!$dataValidation->getAllowBlank() && ($cellValue === null || $cellValue === '')) {
29 
            return false;
30 
        }
31 
 
32 
        $returnValue = false;
33 
        $type = $dataValidation->getType();
34 
        if ($type === DataValidation::TYPE_LIST) {
35 
            $returnValue = $this->isValueInList($cell);
36 
        } elseif ($type === DataValidation::TYPE_WHOLE) {
37 
            if (!is_numeric($cellValue) || fmod((float) $cellValue, 1) != 0) {
38 
                $returnValue = false;
39 
            } else {
40 
                $returnValue = $this->numericOperator($dataValidation, (int) $cellValue, $cell);
41 
            }
42 
        } elseif ($type === DataValidation::TYPE_DECIMAL || $type === DataValidation::TYPE_DATE || $type === DataValidation::TYPE_TIME) {
43 
            if (!is_numeric($cellValue)) {
44 
                $returnValue = false;
45 
            } else {
46 
                $returnValue = $this->numericOperator($dataValidation, (float) $cellValue, $cell);
47 
            }
48 
        } elseif ($type === DataValidation::TYPE_TEXTLENGTH) {
49 
            $returnValue = $this->numericOperator($dataValidation, mb_strlen($cell->getValueString()), $cell);
50 
        }
51 
 
52 
        return $returnValue;
53 
    }
54 
 
55 
    private const TWO_FORMULAS = [DataValidation::OPERATOR_BETWEEN, DataValidation::OPERATOR_NOTBETWEEN];
56 
 
57 
    private static function evaluateNumericFormula(mixed $formula, Cell $cell): mixed
58 
    {
59 
        if (!is_numeric($formula)) {
60 
            $calculation = Calculation::getInstance($cell->getWorksheet()->getParent());
61 
 
62 
            try {
63 
                $result = $calculation
64 
                    ->calculateFormula("=$formula", $cell->getCoordinate(), $cell);
65 
                while (is_array($result)) {
66 
                    $result = array_pop($result);
67 
                }
68 
                $formula = $result;
69 
            } catch (Exception) {
70 
                // do nothing
71 
            }
72 
        }
73 
 
74 
        return $formula;
75 
    }
76 
 
77 
    private function numericOperator(DataValidation $dataValidation, int|float $cellValue, Cell $cell): bool
78 
    {
79 
        $operator = $dataValidation->getOperator();
80 
        $formula1 = self::evaluateNumericFormula(
81 
            $dataValidation->getFormula1(),
82 
            $cell
83 
        );
84 
 
85 
        $formula2 = 0;
86 
        if (in_array($operator, self::TWO_FORMULAS, true)) {
87 
            $formula2 = self::evaluateNumericFormula(
88 
                $dataValidation->getFormula2(),
89 
                $cell
90 
            );
91 
        }
92 
 
93 
        return match ($operator) {
94 
            DataValidation::OPERATOR_BETWEEN => $cellValue >= $formula1 && $cellValue <= $formula2,
95 
            DataValidation::OPERATOR_NOTBETWEEN => $cellValue < $formula1 || $cellValue > $formula2,
96 
            DataValidation::OPERATOR_EQUAL => $cellValue == $formula1,
97 
            DataValidation::OPERATOR_NOTEQUAL => $cellValue != $formula1,
98 
            DataValidation::OPERATOR_LESSTHAN => $cellValue < $formula1,
99 
            DataValidation::OPERATOR_LESSTHANOREQUAL => $cellValue <= $formula1,
100 
            DataValidation::OPERATOR_GREATERTHAN => $cellValue > $formula1,
101 
            DataValidation::OPERATOR_GREATERTHANOREQUAL => $cellValue >= $formula1,
102 
            default => false,
103 
        };
104 
    }
105 
 
106 
    /**
107 
     * Does this cell contain valid value, based on list?
108 
     *
109 
     * @param Cell $cell Cell to check the value
110 
     */
111 
    private function isValueInList(Cell $cell): bool
112 
    {
113 
        $cellValueString = $cell->getValueString();
114 
        $dataValidation = $cell->getDataValidation();
115 
 
116 
        $formula1 = $dataValidation->getFormula1();
117 
        if (!empty($formula1)) {
118 
            // inline values list
119 
            if ($formula1[0] === '"') {
120 
                return in_array(strtolower($cellValueString), explode(',', strtolower(trim($formula1, '"'))), true);
121 
            }
122 
            $calculation = Calculation::getInstance($cell->getWorksheet()->getParent());
123 
 
124 
            try {
125 
                $result = $calculation->calculateFormula("=$formula1", $cell->getCoordinate(), $cell);
126 
                $result = is_array($result) ? Functions::flattenArray($result) : [$result];
127 
                foreach ($result as $oneResult) {
128 
                    if (is_scalar($oneResult) && strcasecmp((string) $oneResult, $cellValueString) === 0) {
129 
                        return true;
130 
                    }
131 
                }
132 
            } catch (Exception) {
133 
                // do nothing
134 
            }
135 
 
136 
            return false;
137 
        }
138 
 
139 
        return true;
140 
    }
141 
}