-
Notifications
You must be signed in to change notification settings - Fork 7
/
ExcelCalculateConfig.java
400 lines (349 loc) · 13.8 KB
/
ExcelCalculateConfig.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
package com.wsbxd.excel.formula.calculation.common.config;
import com.wsbxd.excel.formula.calculation.common.exception.ExcelException;
import com.wsbxd.excel.formula.calculation.common.field.annotation.ExcelField;
import com.wsbxd.excel.formula.calculation.common.field.enums.ExcelFieldTypeEnum;
import com.wsbxd.excel.formula.calculation.common.field.enums.ExcelIdTypeEnum;
import com.wsbxd.excel.formula.calculation.common.config.enums.ExcelCalculateTypeEnum;
import com.wsbxd.excel.formula.calculation.common.function.DefaultFunctionImpl;
import com.wsbxd.excel.formula.calculation.common.interfaces.IFunction;
import com.wsbxd.excel.formula.calculation.common.util.ExcelStrUtil;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* description: Excel 计算配置
*
* @author chenhaoxuan
* @version 1.0
* @date 2021/2/27 11:11
*/
public class ExcelCalculateConfig {
/**
* 不带页签数字行单元格匹配
*/
private final static Pattern CELL_NUMBER_PATTERN = Pattern.compile("[A-Z]+\\d+");
/**
* 不带页签UUID行单元格匹配
*/
private final static Pattern CELL_UUID_PATTERN = Pattern.compile("[A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}");
/**
* 带页签数字行单元格匹配
*/
public final static Pattern SHEET_CELL_NUMBER_PATTERN = Pattern.compile("('[^\\\\/?*\\[\\]]+?'![A-Z]+\\d+|[^\\\\/?*\\[\\]():,+-]+?![A-Z]+\\d+|[A-Z]+\\d+)");
/**
* 带页签UUID行单元格匹配
*/
public final static Pattern SHEET_CELL_UUID_PATTERN = Pattern.compile("('[^\\\\/?*\\[\\]]+?'![A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}|[^\\\\/?*\\[\\]():,+-]+?![A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}|[A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12})");
/**
* 单行列单元格匹配
*/
private final static Pattern COLUMN_CELL_PATTERN = Pattern.compile("(?![A-Z]+\\()([A-Z]+)");
/**
* 返回不带页签数字行单元格匹配
*/
private final static Pattern RETURN_CELL_NUMBER_PATTERN = Pattern.compile("^[A-Z]+\\d+=");
/**
* 返回不带页签UUID行单元格匹配
*/
private final static Pattern RETURN_CELL_UUID_PATTERN = Pattern.compile("^[A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}=");
/**
* 返回带页签数字行单元格匹配
*/
public final static Pattern RETURN_SHEET_CELL_NUMBER_PATTERN = Pattern.compile("^('[^\\\\/?*\\[\\]]+?'![A-Z]+\\d+|[^\\\\/?*\\[\\]():,+-]+?![A-Z]+\\d+|[A-Z]+\\d+)=");
/**
* 返回带页签UUID行单元格匹配
*/
public final static Pattern RETURN_SHEET_CELL_UUID_PATTERN = Pattern.compile("^('[^\\\\/?*\\[\\]]+?'![A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}|[^\\\\/?*\\[\\]():,+-]+?![A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}|[A-Z]+[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12})=");
/**
* 返回单行列单元格匹配
*/
public final static Pattern RETURN_COLUMN_PATTERN = Pattern.compile("^(?![A-Z]+\\()([A-Z]+)=");
/**
* 唯一标识字段
*/
private Field idField;
/**
* 唯一标识类型枚举
*/
private ExcelIdTypeEnum excelIdTypeEnum;
/**
* 单元格类型字符集合字段
*/
private Field cellTypesField;
/**
* 页签字段
*/
private Field sheetField;
/**
* 排序字段
*/
private Field sortField;
/**
* 列字段集合
*/
private List<Field> columnFieldList;
/**
* Excel 计算类型
*/
private ExcelCalculateTypeEnum calculateType;
/**
* 函数实现
*/
private IFunction functionImpl;
/**
* 函数和函数名称 Map
*/
private Map<String, Method> nameFunctionMap;
/**
* 函数调用
*
* @param functionName 函数名称
* @param valueList 函数参数
* @return 计算结果
*/
public String functionCalculate(String functionName, List<String> valueList) {
Method method = nameFunctionMap.get(functionName);
try {
return (String) method.invoke(this.functionImpl, valueList);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
throw new ExcelException("函数调用失败,函数名称:" + functionName);
}
/**
* 处理方法实现类
*
* @param functionImplClass 方法实现类
*/
private void handleFunctionImpl(Class<? extends IFunction> functionImplClass) {
try {
this.functionImpl = functionImplClass.newInstance();
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
this.nameFunctionMap = new HashMap<>();
for (Method method : functionImplClass.getDeclaredMethods()) {
this.nameFunctionMap.put(method.getName(), method);
}
}
/**
* 不用注解创建 Excel 实体类 数据属性
*
* @param clazz excel
* @param idFieldName id字段名称
* @param excelIdTypeEnum id类型
* @param sheetFieldName 工作表字段名称
* @param sortFieldName 排序字段名称
* @param columnFieldNameList 列字段名称集合
*/
public ExcelCalculateConfig(Class<?> clazz, String idFieldName, ExcelIdTypeEnum excelIdTypeEnum, String sheetFieldName, String sortFieldName, List<String> columnFieldNameList) {
this(clazz, idFieldName, excelIdTypeEnum, sheetFieldName, sortFieldName, columnFieldNameList, DefaultFunctionImpl.class);
}
/**
* 不用注解创建 Excel 实体类 数据属性
*
* @param clazz excel
* @param idFieldName id字段名称
* @param excelIdTypeEnum id类型
* @param sheetFieldName 工作表字段名称
* @param sortFieldName 排序字段名称
* @param columnFieldNameList 列字段名称集合
* @param functionImplClass 函数实现类
*/
public ExcelCalculateConfig(Class<?> clazz, String idFieldName, ExcelIdTypeEnum excelIdTypeEnum, String sheetFieldName, String sortFieldName, List<String> columnFieldNameList, Class<? extends IFunction> functionImplClass) {
try {
this.idField = clazz.getDeclaredField(idFieldName);
this.excelIdTypeEnum = excelIdTypeEnum;
this.sortField = clazz.getDeclaredField(sortFieldName);
if (ExcelStrUtil.isNotBlank(sheetFieldName)) {
//如果不是工作簿计算是用不到工作表名称的
this.sheetField = clazz.getDeclaredField(sheetFieldName);
}
this.columnFieldList = new ArrayList<>();
for (String column : columnFieldNameList) {
this.columnFieldList.add(clazz.getDeclaredField(column));
}
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
handleFunctionImpl(functionImplClass);
}
/**
* 用注释创建 Excel 实体类 数据属性
*
* @param clazz 数据类
*/
public ExcelCalculateConfig(Class<?> clazz) {
this(clazz, DefaultFunctionImpl.class);
}
/**
* 用注释创建 Excel 实体类 数据属性
*
* @param clazz 数据类
* @param functionImplClass 函数实现类
*/
public ExcelCalculateConfig(Class<?> clazz, Class<? extends IFunction> functionImplClass) {
for (Field field : clazz.getDeclaredFields()) {
field.setAccessible(true);
ExcelField excelField = field.getAnnotation(ExcelField.class);
if (null != excelField) {
if (ExcelFieldTypeEnum.CELL.name().equals(excelField.value().name())) {
if (null == this.columnFieldList) {
this.columnFieldList = new ArrayList<>();
}
this.columnFieldList.add(field);
} else if (ExcelFieldTypeEnum.ID.name().equals(excelField.value().name())) {
this.idField = field;
this.excelIdTypeEnum = excelField.idType();
} else if (ExcelFieldTypeEnum.CELL_TYPES.name().equals(excelField.value().name())) {
this.cellTypesField = field;
} else if (ExcelFieldTypeEnum.SORT.name().equals(excelField.value().name())) {
this.sortField = field;
} else if (ExcelFieldTypeEnum.SHEET.name().equals(excelField.value().name())) {
this.sheetField = field;
}
}
}
handleFunctionImpl(functionImplClass);
}
/**
* 获取返回单元格匹配
*
* @return 单元格匹配 Pattern
*/
public Pattern getReturnCellPattern() {
if (null == this.calculateType) {
throw new ExcelException("请设置 Excel 计算类型");
}
if (ExcelCalculateTypeEnum.BOOK.equals(this.calculateType)) {
if (ExcelIdTypeEnum.NUMBER.equals(excelIdTypeEnum)) {
return RETURN_SHEET_CELL_NUMBER_PATTERN;
} else if (ExcelIdTypeEnum.UUID.equals(this.excelIdTypeEnum)) {
return RETURN_SHEET_CELL_UUID_PATTERN;
}
} else if (ExcelCalculateTypeEnum.SHEET.equals(calculateType)) {
if (ExcelIdTypeEnum.NUMBER.equals(this.excelIdTypeEnum)) {
return RETURN_CELL_NUMBER_PATTERN;
} else if (ExcelIdTypeEnum.UUID.equals(this.excelIdTypeEnum)) {
return RETURN_CELL_UUID_PATTERN;
}
} else if (ExcelCalculateTypeEnum.ROW.equals(this.calculateType)) {
return RETURN_COLUMN_PATTERN;
}
return null;
}
/**
* 根据公式获取单元格字符串集合
*
* @param formula 公式
* @return 单元格字符串集合
*/
public List<String> getCellStrListByFormula(String formula) {
if (null == this.calculateType) {
throw new ExcelException("请设置 Excel 计算类型");
}
List<String> cellStrList = new ArrayList<>();
Matcher matcher = null;
if (ExcelCalculateTypeEnum.BOOK.equals(this.calculateType)) {
if (ExcelIdTypeEnum.NUMBER.equals(excelIdTypeEnum)) {
matcher = SHEET_CELL_NUMBER_PATTERN.matcher(formula);
} else if (ExcelIdTypeEnum.UUID.equals(excelIdTypeEnum)) {
matcher = SHEET_CELL_UUID_PATTERN.matcher(formula);
}
} else if (ExcelCalculateTypeEnum.SHEET.equals(this.calculateType)) {
if (ExcelIdTypeEnum.NUMBER.equals(excelIdTypeEnum)) {
matcher = CELL_NUMBER_PATTERN.matcher(formula);
} else if (ExcelIdTypeEnum.UUID.equals(excelIdTypeEnum)) {
matcher = CELL_UUID_PATTERN.matcher(formula);
}
} else if (ExcelCalculateTypeEnum.ROW.equals(this.calculateType)) {
matcher = COLUMN_CELL_PATTERN.matcher(formula);
}
while (null != matcher && matcher.find()) {
cellStrList.add(matcher.group());
}
return cellStrList;
}
public Field getIdField() {
return idField;
}
public void setIdField(Field idField) {
this.idField = idField;
}
public ExcelIdTypeEnum getExcelIdTypeEnum() {
return excelIdTypeEnum;
}
public void setExcelIdTypeEnum(ExcelIdTypeEnum excelIdTypeEnum) {
this.excelIdTypeEnum = excelIdTypeEnum;
}
public Field getCellTypesField() {
return cellTypesField;
}
public void setCellTypesField(Field cellTypesField) {
this.cellTypesField = cellTypesField;
}
public Field getSheetField() {
return sheetField;
}
public void setSheetField(Field sheetField) {
this.sheetField = sheetField;
}
public Field getSortField() {
return sortField;
}
public void setSortField(Field sortField) {
this.sortField = sortField;
}
public List<Field> getColumnFieldList() {
return columnFieldList;
}
public void setColumnFieldList(List<Field> columnFieldList) {
this.columnFieldList = columnFieldList;
}
public ExcelCalculateTypeEnum getCalculateType() {
return calculateType;
}
public void setCalculateType(ExcelCalculateTypeEnum calculateType) {
this.calculateType = calculateType;
}
public Map<String, Method> getNameFunctionMap() {
return nameFunctionMap;
}
public void setNameFunctionMap(Map<String, Method> nameFunctionMap) {
this.nameFunctionMap = nameFunctionMap;
}
public IFunction getFunctionImpl() {
return functionImpl;
}
public void setFunctionImpl(IFunction functionImpl) {
this.functionImpl = functionImpl;
}
public ExcelCalculateConfig(Field idField, ExcelIdTypeEnum excelIdTypeEnum, Field cellTypesField, Field sheetField, Field sortField, List<Field> columnFieldList, ExcelCalculateTypeEnum calculateType, IFunction functionImpl) {
this.idField = idField;
this.excelIdTypeEnum = excelIdTypeEnum;
this.cellTypesField = cellTypesField;
this.sheetField = sheetField;
this.sortField = sortField;
this.columnFieldList = columnFieldList;
this.calculateType = calculateType;
this.functionImpl = functionImpl;
}
@Override
public String toString() {
return "ExcelDataProperties{" +
"idField=" + idField +
", excelIdTypeEnum=" + excelIdTypeEnum +
", cellTypesField=" + cellTypesField +
", sheetField=" + sheetField +
", sortField=" + sortField +
", columnFieldList=" + columnFieldList +
", calculateType=" + calculateType +
'}';
}
}