tablepress / libraries / vendor / PhpSpreadsheet / Cell / DataValidator.php

DataValidator.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Cell/DataValidator.php

160 lines 4.7 KB

Raw Download Zip

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