|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* @name: DataValidationHandler |
|
4
|
|
|
* @author: JiaMeng <[email protected]> |
|
5
|
|
|
* @file: DataValidationHandler.php |
|
6
|
|
|
* @Date: 2025/01/XX |
|
7
|
|
|
*/ |
|
8
|
|
|
namespace tinymeng\spreadsheet\Excel\Handler; |
|
9
|
|
|
|
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
|
11
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
|
12
|
|
|
use tinymeng\spreadsheet\Util\WorkSheetHelper; |
|
13
|
|
|
|
|
14
|
|
|
class DataValidationHandler |
|
15
|
|
|
{ |
|
16
|
|
|
/** |
|
17
|
|
|
* 应用数据验证到指定列 |
|
18
|
|
|
* @param Worksheet $worksheet 工作表对象 |
|
19
|
|
|
* @param string $fieldName 字段名 |
|
20
|
|
|
* @param array $config 验证配置 |
|
21
|
|
|
* @param int $fieldIndex 字段在field数组中的索引 |
|
22
|
|
|
* @param int $startRow 起始行（数据开始行） |
|
23
|
|
|
* @param int $endRow 结束行（数据结束行，为0时表示应用到整列） |
|
24
|
|
|
* @param bool $isTemplate 是否为模板（无数据） |
|
25
|
|
|
*/ |
|
26
|
|
|
public static function applyValidation( |
|
27
|
|
|
Worksheet $worksheet, |
|
28
|
|
|
string $fieldName, |
|
|
|
|
|
|
29
|
|
|
array $config, |
|
30
|
|
|
int $fieldIndex, |
|
31
|
|
|
int $startRow, |
|
32
|
|
|
int $endRow = 0, |
|
33
|
|
|
bool $isTemplate = false |
|
34
|
|
|
) { |
|
35
|
|
|
$colLetter = WorkSheetHelper::cellName($fieldIndex); |
|
36
|
|
|
|
|
37
|
|
|
// 创建数据验证对象 |
|
38
|
|
|
$validation = new DataValidation(); |
|
39
|
|
|
|
|
40
|
|
|
// 设置验证类型 |
|
41
|
|
|
self::setValidationType($validation, $config); |
|
42
|
|
|
|
|
43
|
|
|
// 设置操作符和范围（对于数值、日期、时间类型） |
|
44
|
|
|
self::setValidationOperator($validation, $config); |
|
45
|
|
|
|
|
46
|
|
|
// 设置输入提示信息 |
|
47
|
|
|
self::setPromptMessage($validation, $config); |
|
48
|
|
|
|
|
49
|
|
|
// 设置错误提示信息 |
|
50
|
|
|
self::setErrorMessage($validation, $config); |
|
51
|
|
|
|
|
52
|
|
|
// 是否允许空白 |
|
53
|
|
|
$validation->setAllowBlank(isset($config['allowBlank']) ? $config['allowBlank'] : false); |
|
54
|
|
|
|
|
55
|
|
|
// 确定结束行：优先使用配置中的行范围 |
|
56
|
|
|
$finalEndRow = self::calculateEndRow($config, $startRow, $endRow, $isTemplate); |
|
57
|
|
|
|
|
58
|
|
|
// 应用验证到指定范围 |
|
59
|
|
|
$cellRange = self::calculateCellRange($colLetter, $startRow, $finalEndRow, $worksheet); |
|
60
|
|
|
$worksheet->setDataValidation($cellRange, $validation); |
|
61
|
|
|
} |
|
62
|
|
|
|
|
63
|
|
|
/** |
|
64
|
|
|
* 设置验证类型 |
|
65
|
|
|
* @param DataValidation $validation |
|
66
|
|
|
* @param array $config |
|
67
|
|
|
*/ |
|
68
|
|
|
private static function setValidationType(DataValidation $validation, array $config) |
|
69
|
|
|
{ |
|
70
|
|
|
$type = $config['type'] ?? 'list'; |
|
71
|
|
|
switch ($type) { |
|
72
|
|
|
case 'list': |
|
73
|
|
|
$validation->setType(DataValidation::TYPE_LIST); |
|
74
|
|
|
if (isset($config['options']) && is_array($config['options'])) { |
|
75
|
|
|
// 选项列表，使用逗号分隔，需要转义包含逗号的选项 |
|
76
|
|
|
$options = array_map(function($option) { |
|
77
|
|
|
// 如果选项包含逗号或引号，需要用引号包裹并转义内部引号 |
|
78
|
|
|
if (strpos($option, ',') !== false || strpos($option, '"') !== false) { |
|
79
|
|
|
return '"' . str_replace('"', '""', $option) . '"'; |
|
80
|
|
|
} |
|
81
|
|
|
return $option; |
|
82
|
|
|
}, $config['options']); |
|
83
|
|
|
$formula = '"' . implode(',', $options) . '"'; |
|
84
|
|
|
$validation->setFormula1($formula); |
|
85
|
|
|
} elseif (isset($config['formula'])) { |
|
86
|
|
|
// 使用公式引用范围（如 "=$A$1:$A$10"） |
|
87
|
|
|
$validation->setFormula1($config['formula']); |
|
88
|
|
|
} |
|
89
|
|
|
// 是否显示下拉箭头 |
|
90
|
|
|
$validation->setShowDropDown(!isset($config['showDropDown']) || $config['showDropDown'] !== false); |
|
91
|
|
|
break; |
|
92
|
|
|
case 'whole': |
|
93
|
|
|
$validation->setType(DataValidation::TYPE_WHOLE); |
|
94
|
|
|
break; |
|
95
|
|
|
case 'decimal': |
|
96
|
|
|
$validation->setType(DataValidation::TYPE_DECIMAL); |
|
97
|
|
|
break; |
|
98
|
|
|
case 'date': |
|
99
|
|
|
$validation->setType(DataValidation::TYPE_DATE); |
|
100
|
|
|
break; |
|
101
|
|
|
case 'time': |
|
102
|
|
|
$validation->setType(DataValidation::TYPE_TIME); |
|
103
|
|
|
break; |
|
104
|
|
|
case 'textLength': |
|
105
|
|
|
$validation->setType(DataValidation::TYPE_TEXTLENGTH); |
|
106
|
|
|
break; |
|
107
|
|
|
case 'custom': |
|
108
|
|
|
$validation->setType(DataValidation::TYPE_CUSTOM); |
|
109
|
|
|
if (isset($config['formula'])) { |
|
110
|
|
|
$validation->setFormula1($config['formula']); |
|
111
|
|
|
} |
|
112
|
|
|
break; |
|
113
|
|
|
default: |
|
114
|
|
|
$validation->setType(DataValidation::TYPE_NONE); |
|
115
|
|
|
} |
|
116
|
|
|
} |
|
117
|
|
|
|
|
118
|
|
|
/** |
|
119
|
|
|
* 设置验证操作符和范围 |
|
120
|
|
|
* @param DataValidation $validation |
|
121
|
|
|
* @param array $config |
|
122
|
|
|
*/ |
|
123
|
|
|
private static function setValidationOperator(DataValidation $validation, array $config) |
|
124
|
|
|
{ |
|
125
|
|
|
$type = $config['type'] ?? 'list'; |
|
126
|
|
|
|
|
127
|
|
|
if (!in_array($type, ['whole', 'decimal', 'date', 'time', 'textLength'])) { |
|
128
|
|
|
return; |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
$operator = $config['operator'] ?? 'between'; |
|
132
|
|
|
switch ($operator) { |
|
133
|
|
|
case 'between': |
|
134
|
|
|
$validation->setOperator(DataValidation::OPERATOR_BETWEEN); |
|
135
|
|
|
if (isset($config['min'])) { |
|
136
|
|
|
$validation->setFormula1($config['min']); |
|
137
|
|
|
} |
|
138
|
|
|
if (isset($config['max'])) { |
|
139
|
|
|
$validation->setFormula2($config['max']); |
|
140
|
|
|
} |
|
141
|
|
|
break; |
|
142
|
|
|
case 'notBetween': |
|
143
|
|
|
$validation->setOperator(DataValidation::OPERATOR_NOTBETWEEN); |
|
144
|
|
|
if (isset($config['min'])) { |
|
145
|
|
|
$validation->setFormula1($config['min']); |
|
146
|
|
|
} |
|
147
|
|
|
if (isset($config['max'])) { |
|
148
|
|
|
$validation->setFormula2($config['max']); |
|
149
|
|
|
} |
|
150
|
|
|
break; |
|
151
|
|
|
case 'equal': |
|
152
|
|
|
$validation->setOperator(DataValidation::OPERATOR_EQUAL); |
|
153
|
|
|
if (isset($config['value'])) { |
|
154
|
|
|
$validation->setFormula1($config['value']); |
|
155
|
|
|
} |
|
156
|
|
|
break; |
|
157
|
|
|
case 'notEqual': |
|
158
|
|
|
$validation->setOperator(DataValidation::OPERATOR_NOTEQUAL); |
|
159
|
|
|
if (isset($config['value'])) { |
|
160
|
|
|
$validation->setFormula1($config['value']); |
|
161
|
|
|
} |
|
162
|
|
|
break; |
|
163
|
|
|
case 'greaterThan': |
|
164
|
|
|
$validation->setOperator(DataValidation::OPERATOR_GREATERTHAN); |
|
165
|
|
|
if (isset($config['value'])) { |
|
166
|
|
|
$validation->setFormula1($config['value']); |
|
167
|
|
|
} |
|
168
|
|
|
break; |
|
169
|
|
|
case 'lessThan': |
|
170
|
|
|
$validation->setOperator(DataValidation::OPERATOR_LESSTHAN); |
|
171
|
|
|
if (isset($config['value'])) { |
|
172
|
|
|
$validation->setFormula1($config['value']); |
|
173
|
|
|
} |
|
174
|
|
|
break; |
|
175
|
|
|
case 'greaterThanOrEqual': |
|
176
|
|
|
$validation->setOperator(DataValidation::OPERATOR_GREATERTHANOREQUAL); |
|
177
|
|
|
if (isset($config['value'])) { |
|
178
|
|
|
$validation->setFormula1($config['value']); |
|
179
|
|
|
} |
|
180
|
|
|
break; |
|
181
|
|
|
case 'lessThanOrEqual': |
|
182
|
|
|
$validation->setOperator(DataValidation::OPERATOR_LESSTHANOREQUAL); |
|
183
|
|
|
if (isset($config['value'])) { |
|
184
|
|
|
$validation->setFormula1($config['value']); |
|
185
|
|
|
} |
|
186
|
|
|
break; |
|
187
|
|
|
} |
|
188
|
|
|
} |
|
189
|
|
|
|
|
190
|
|
|
/** |
|
191
|
|
|
* 设置输入提示信息 |
|
192
|
|
|
* @param DataValidation $validation |
|
193
|
|
|
* @param array $config |
|
194
|
|
|
*/ |
|
195
|
|
|
private static function setPromptMessage(DataValidation $validation, array $config) |
|
196
|
|
|
{ |
|
197
|
|
|
if (isset($config['promptTitle']) || isset($config['promptMessage'])) { |
|
198
|
|
|
$validation->setPromptTitle($config['promptTitle'] ?? ''); |
|
199
|
|
|
$validation->setPrompt($config['promptMessage'] ?? ''); |
|
200
|
|
|
$validation->setShowInputMessage(isset($config['showInputMessage']) ? $config['showInputMessage'] : true); |
|
201
|
|
|
} |
|
202
|
|
|
} |
|
203
|
|
|
|
|
204
|
|
|
/** |
|
205
|
|
|
* 设置错误提示信息 |
|
206
|
|
|
* @param DataValidation $validation |
|
207
|
|
|
* @param array $config |
|
208
|
|
|
*/ |
|
209
|
|
|
private static function setErrorMessage(DataValidation $validation, array $config) |
|
210
|
|
|
{ |
|
211
|
|
|
if (isset($config['errorTitle']) || isset($config['errorMessage'])) { |
|
212
|
|
|
$validation->setErrorTitle($config['errorTitle'] ?? '输入错误'); |
|
213
|
|
|
$validation->setError($config['errorMessage'] ?? '输入值无效'); |
|
214
|
|
|
|
|
215
|
|
|
// 设置错误样式 |
|
216
|
|
|
$errorStyle = $config['errorStyle'] ?? 'stop'; |
|
217
|
|
|
switch ($errorStyle) { |
|
218
|
|
|
case 'stop': |
|
219
|
|
|
$validation->setErrorStyle(DataValidation::STYLE_STOP); |
|
220
|
|
|
break; |
|
221
|
|
|
case 'warning': |
|
222
|
|
|
$validation->setErrorStyle(DataValidation::STYLE_WARNING); |
|
223
|
|
|
break; |
|
224
|
|
|
case 'information': |
|
225
|
|
|
$validation->setErrorStyle(DataValidation::STYLE_INFORMATION); |
|
226
|
|
|
break; |
|
227
|
|
|
default: |
|
228
|
|
|
$validation->setErrorStyle(DataValidation::STYLE_STOP); |
|
229
|
|
|
} |
|
230
|
|
|
|
|
231
|
|
|
$validation->setShowErrorMessage(isset($config['showErrorMessage']) ? $config['showErrorMessage'] : true); |
|
232
|
|
|
} |
|
233
|
|
|
} |
|
234
|
|
|
|
|
235
|
|
|
/** |
|
236
|
|
|
* 计算结束行 |
|
237
|
|
|
* @param array $config |
|
238
|
|
|
* @param int $startRow |
|
239
|
|
|
* @param int $endRow |
|
240
|
|
|
* @param bool $isTemplate |
|
241
|
|
|
* @return int |
|
242
|
|
|
*/ |
|
243
|
|
|
private static function calculateEndRow(array $config, int $startRow, int $endRow, bool $isTemplate): int |
|
244
|
|
|
{ |
|
245
|
|
|
$finalEndRow = $endRow; |
|
246
|
|
|
if (isset($config['data_end_row']) && $config['data_end_row'] > 0) { |
|
247
|
|
|
// 配置中直接指定了结束行 |
|
248
|
|
|
$finalEndRow = $config['data_end_row']; |
|
249
|
|
|
} elseif (isset($config['data_row_count']) && $config['data_row_count'] > 0) { |
|
250
|
|
|
// 配置中指定了行数（从起始行开始计算） |
|
251
|
|
|
$finalEndRow = $startRow + $config['data_row_count']; |
|
252
|
|
|
} elseif ($endRow == 0 && $isTemplate) { |
|
253
|
|
|
// 模板导出时，如果没有配置行范围，默认应用到后续100行 |
|
254
|
|
|
$finalEndRow = $startRow + 100; |
|
255
|
|
|
} |
|
256
|
|
|
return $finalEndRow; |
|
257
|
|
|
} |
|
258
|
|
|
|
|
259
|
|
|
/** |
|
260
|
|
|
* 计算单元格范围 |
|
261
|
|
|
* @param string $colLetter |
|
262
|
|
|
* @param int $startRow |
|
263
|
|
|
* @param int $finalEndRow |
|
264
|
|
|
* @param Worksheet $worksheet |
|
265
|
|
|
* @return string |
|
266
|
|
|
*/ |
|
267
|
|
|
private static function calculateCellRange(string $colLetter, int $startRow, int $finalEndRow, Worksheet $worksheet): string |
|
268
|
|
|
{ |
|
269
|
|
|
if ($finalEndRow > 0 && $finalEndRow >= $startRow) { |
|
270
|
|
|
// 应用到指定行范围 |
|
271
|
|
|
return $colLetter . $startRow . ':' . $colLetter . $finalEndRow; |
|
272
|
|
|
} else { |
|
273
|
|
|
// 应用到整列（从数据开始行到工作表最后一行） |
|
274
|
|
|
return $colLetter . $startRow . ':' . $colLetter . $worksheet->getHighestRow(); |
|
275
|
|
|
} |
|
276
|
|
|
} |
|
277
|
|
|
} |
|
278
|
|
|
|
|
279
|
|
|
|
majiameng /
spreadsheet-php
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.