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
|
* @author vincent
*/
@Slf4j
public class ExcelPoiUtils {
public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
public static <T> List<T> readExcel(File file, Class<T> clazz) throws Exception {
List<T> list = Lists.newArrayList();
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
List<String> rowFirst = Lists.newArrayList();
Row rowTitle = sheet.getRow(0);
if (Objects.nonNull(rowTitle)) {
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (Objects.nonNull(cell)) {
String cellValue = cell.getStringCellValue();
rowFirst.add(cellValue);
}
}
}
log.info(String.format("RowFirst：%s...... \n", Arrays.toString(rowFirst.toArray())));
* Class<T> clazz：需要转换为具体的实体类。
* 此方法为获取 clazz（实体类）中的全部字段属性，与读取到的 excel 表头字段进行匹配，返回一个 Map<columnIndex,{columnIndex,name,field}>
* columnIndex：为 clazz（实体类）字段属性在 excel 表头字段中的对应位置。
* name：为自定义注解 @ExcelTransfer 中的 value 值（如：@ExcelTransfer(value = "字符串标题")）；如果没有设置自定义注解，则为 clazz（实体类）字段属性名。
* field：为 clazz（实体类）字段属性。
*/
List<Field> allFieldsList = FieldUtils.getAllFieldsList(clazz);
Map<Integer, ClazzFields> mapClazzFields = allFieldsList.stream()
.map(field -> {
if (field.isAnnotationPresent(ExcelTransfer.class)) {
ExcelTransfer annotation = field.getAnnotation(ExcelTransfer.class);
String annotationVal = annotation.value();
return new ClazzFields(rowFirst.indexOf(annotationVal), annotationVal, field);
}
return new ClazzFields(rowFirst.indexOf(field.getName()), field.getName(), field);
})
.collect(Collectors.toMap(ClazzFields::getColumnIndex, Function.identity(), (v1, v2) -> v1));
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
T instance = clazz.newInstance();
Row rowData = sheet.getRow(rowNum);
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
ClazzFields clazzFields = mapClazzFields.get(cellNum);
log.info(String.format("RowNum - CellNum：[ %s - %s ]......", (rowNum + 1), (cellNum + 1)));
Cell cell = rowData.getCell(cellNum);
if (Objects.nonNull(cell)) {
* 匹配每个单元格数据类型，把读取到的单元格的内容全部转换为 String 类型
*/
String cellValue = cellTypeConvertToString(cell);
log.info(String.format("CellValue Convert To String：%s", cellValue));
if (Objects.isNull(clazzFields)) {
log.warn("The DtoField does not match the excel table field......\n");
continue;
}
Field dtoField = clazzFields.getField();
Field field = instance.getClass().getDeclaredField(dtoField.getName());
field.setAccessible(true);
Object o = fieldTypeConvertTo(dtoField, cellValue);
field.set(instance, o);
log.info(String.format("DtoFieldType：%s，DtoFieldValue：%s......\n", dtoField.getType(), o));
}
}
list.add(instance);
}
return list;
}
private static Map<Class, BiFunction<String, String, Object>> MAP;
static {
MAP = Maps.newHashMap();
MAP.put(String.class, (s1, s2) -> s1);
MAP.put(Integer.class, (s1, s2) -> Integer.valueOf(s1));
MAP.put(Byte.class, (s1, s2) -> Byte.valueOf(s1));
MAP.put(Short.class, (s1, s2) -> Short.valueOf(s1));
MAP.put(Long.class, (s1, s2) -> Long.valueOf(s1));
MAP.put(Float.class, (s1, s2) -> Float.valueOf(s1));
MAP.put(Double.class, (s1, s2) -> Double.valueOf(s1));
MAP.put(Boolean.class, (s1, s2) -> Boolean.valueOf(s1));
MAP.put(BigDecimal.class, (s1, s2) -> new BigDecimal(s1));
MAP.put(Date.class, CheckedFunction2.<String, String, Object>of(DateUtils::parseDate).unchecked());
MAP.put(int.class, (s1, s2) -> Integer.valueOf(s1));
MAP.put(byte.class, (s1, s2) -> Byte.valueOf(s1));
MAP.put(short.class, (s1, s2) -> Short.valueOf(s1));
MAP.put(long.class, (s1, s2) -> Long.valueOf(s1));
MAP.put(float.class, (s1, s2) -> Float.valueOf(s1));
MAP.put(double.class, (s1, s2) -> Double.valueOf(s1));
MAP.put(boolean.class, (s1, s2) -> Boolean.valueOf(s1));
}
* 方法一：表驱动
* 把单元格的值转换成相对于的类型值
*
* @param field
* @param value
* @return
*/
private static Object fieldTypeConvertTo(Field field, String value) {
if (!MAP.containsKey(field.getType())) {
return null;
}
String format = Optional.ofNullable(field.getAnnotation(ExcelTransfer.class))
.map(ExcelTransfer::paseDate)
.orElse(YYYY_MM_DD_HH_MM_SS);
if (field.getType() == Date.class) {
log.info(String.format("If CellFieldType is date, the pattern of CellField is %s ......", format));
}
log.info(String.format("CellFieldType（String） Convert To DtoFieldType（%s）......", field.getType()));
return MAP.get(field.getType()).apply(value, format);
}
* 第二种方法：if else ...
* 把单元格的值转换成相对于的类型值
*
* @param field
* @param value
* @return
*/
private static Object fieldTypeConvertTo2(Field field, String value) {
if (!MAP.containsKey(field.getType())) {
return null;
}
String format = Optional.ofNullable(field.getAnnotation(ExcelTransfer.class))
.map(ExcelTransfer::paseDate)
.orElse(YYYY_MM_DD_HH_MM_SS);
if (ClassUtils.isPrimitiveOrWrapper(field.getType())) {
try {
Method valueOf = field.getType().getDeclaredMethod("valueOf");
return valueOf.invoke(null, value);
} catch (Exception e) {
e.printStackTrace();
}
}
if (field.getType() == Date.class) {
try {
return DateUtils.parseDate(value, format);
} catch (ParseException e) {
e.printStackTrace();
}
}
if (field.getType() == BigDecimal.class) {
return new BigDecimal(value);
}
return null;
}
* 方法一：switch case ...
* excel 单元格转换为 String 类型
*
* @param cell
* @return
*/
public static String cellTypeConvertToString(Cell cell) {
String cellValue = "";
switch (cell.getCellTypeEnum()) {
case BLANK:
log.info("CellType：【BLANK】");
break;
case STRING:
log.info("CellType：【STRING】");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
log.info("CellType：【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
log.info("CellType：【NUMERIC Convert to DATE】");
cellValue = DateFormatUtils.format(cell.getDateCellValue(), YYYY_MM_DD_HH_MM_SS);
} else {
log.info("CellType：【NUMERIC Convert to STRING】");
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
}
break;
case ERROR:
log.info("CellType：【ERROR】=> Get the cell error...");
break;
}
return cellValue;
}
* 方法二：模式匹配
*
* @param cell
* @return
*/
public static String cellTypeConvertToStringPatternMatching(Cell cell) {
return API.Match(cell.getCellTypeEnum()).of(
Case($(s -> s == CellType.BLANK), () -> ""),
Case($(s -> s == CellType.STRING), cell::getStringCellValue),
Case($(s -> s == CellType.BOOLEAN), () -> String.valueOf(cell.getBooleanCellValue())),
Case($(s -> s == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)), () -> DateFormatUtils.format(cell.getDateCellValue(), YYYY_MM_DD_HH_MM_SS)),
Case($(s -> s == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)), () -> {
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}),
Case($(s -> s == CellType.ERROR), () -> {
log.info("【ERROR】：Get the cell error...");
return "";
})
);
}
}
@Data
class ClazzFields {
* 列的位置
*/
private Integer columnIndex;
* 如果有自定义注解，则为 @ExcelTransfer 中的 value 值（如：@ExcelTransfer(value = "字符串标题")）；
* 如果没有设置自定义注解，则为 clazz（实体类）字段属性名
*/
private String name;
* clazz 实体类字段属性
*/
private Field field;
public ClazzFields() {
}
public ClazzFields(Integer columnIndex, String name, Field field) {
this.columnIndex = columnIndex;
this.name = name;
this.field = field;
}
}
|
