文件導入導出必須代碼
ExportExcel.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
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
|
/** * Copyright © 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved. */package com.thinkgem.jeesite.common.utils.excel; import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.text.DecimalFormat;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.List;import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Comment;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.slf4j.Logger;import org.slf4j.LoggerFactory; import com.google.common.collect.Lists;import com.thinkgem.jeesite.common.utils.DateUtils;import com.thinkgem.jeesite.common.utils.Encodes;import com.thinkgem.jeesite.common.utils.Reflections;import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;import com.thinkgem.jeesite.modules.sys.utils.DictUtils; /** * 導出Excel文件（導出“XLSX”格式，支持大數據量導出 @see org.apache.poi.ss.SpreadsheetVersion） * @author ThinkGem * @version 2013-04-21 */public class ExportExcel { private static Logger log = LoggerFactory.getLogger(ExportExcel.class); /** * 工作薄對象 */ private SXSSFWorkbook wb; /** * 工作表對象 */ private Sheet sheet; /** * 樣式列表 */ private Map<String, CellStyle> styles; /** * 當前行號 */ private int rownum; /** * 注解列表（Object[]{ ExcelField, Field/Method }） */ List<Object[]> annotationList = Lists.newArrayList(); /** * 字段map */ LinkedHashMap<String,String> fieldMap; HashMap<String, String> dictTypes; /** * 構造函數 * @param title 表格標題，傳“空值”，表示無標題 * @param cls 實體對象，通過annotation.ExportField獲取標題 */ public ExportExcel(String title, Class<?> cls){ this(title, cls, 1); } /** * 構造函數 * @param title 表格標題，傳“空值”，表示無標題 * @param fieldMap，獲取要導出的字段和字段標題 */ public ExportExcel(String title, LinkedHashMap<String,String> fieldMap){ this.fieldMap = fieldMap; dictTypes = new HashMap<String,String>(); // Initialize int colunm = 0; List<String> headerList = Lists.newArrayList(); for (String key : fieldMap.keySet()){ String t = fieldMap.get(key); HashMap<String, String> map = com.thinkgem.jeesite.common.utils.StringUtils.toMap(t, ";", "=", false); if(map.get("name") != null){ t = map.get("name"); } if(map.get("dictType") != null){ dictTypes.put(""+(colunm), map.get("dictType")); } colunm++; headerList.add(t); } initialize(title, headerList); } /** * 構造函數 * @param title 表格標題，傳“空值”，表示無標題 * @param cls 實體對象，通過annotation.ExportField獲取標題 * @param type 導出類型（1:導出數據；2：導出模板） * @param groups 導入分組 */ public ExportExcel(String title, Class<?> cls, int type, int... groups){ // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs){ ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==type)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, f}); break; } } } }else{ annotationList.add(new Object[]{ef, f}); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms){ ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==type)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, m}); break; } } } }else{ annotationList.add(new Object[]{ef, m}); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField)o1[0]).sort()).compareTo( new Integer(((ExcelField)o2[0]).sort())); }; }); // Initialize List<String> headerList = Lists.newArrayList(); for (Object[] os : annotationList){ String t = ((ExcelField)os[0]).title(); // 如果是導出，則去掉注釋 if (type==1){ String[] ss = StringUtils.split(t, "**", 2); if (ss.length==2){ t = ss[0]; } } headerList.add(t); } initialize(title, headerList); } /** * 構造函數 * @param title 表格標題，傳“空值”，表示無標題 * @param headers 表頭數組 */ public ExportExcel(String title, String[] headers) { initialize(title, Lists.newArrayList(headers)); } /** * 構造函數 * @param title 表格標題，傳“空值”，表示無標題 * @param headerList 表頭列表 */ public ExportExcel(String title, List<String> headerList) { initialize(title, headerList); } /** * 初始化函數 * @param title 表格標題，傳“空值”，表示無標題 * @param headerList 表頭列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)){ Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1)); } // Create header if (headerList == null){ throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length==2){ cell.setCellValue(ss[0]); Comment comment = this.sheet.createDrawingPatriarch().createCellComment( new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); }else{ cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i)*2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); } /** * 創建表格樣式 * @param wb 工作薄對象 * @return 樣式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data"));// style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; } /** * 添加一行 * @return 行對象 */ public Row addRow(){ return sheet.createRow(rownum++); } /** * 添加一個單元格 * @param row 添加的行 * @param column 添加列號 * @param val 添加值 * @return 單元格對象 */ public Cell addCell(Row row, int column, Object val){ return this.addCell(row, column, val, 0, Class.class); } /** * 添加一個單元格 * @param row 添加的行 * @param column 添加列號 * @param val 添加值 * @param align 對齊方式（1：靠左；2：居中；3：靠右） * @return 單元格對象 */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){ Cell cell = row.createCell(column); CellStyle style = styles.get("data"+(align>=1&&align<=3?align:"")); try { if (val == null){ cell.setCellValue(""); } else if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); } else if (val instanceof Long) { cell.setCellValue((Long) val); } else if (val instanceof Double) { cell.setCellValue(new DecimalFormat(".#####").format(val)); } else if (val instanceof Float) { cell.setCellValue((Float) val); } else if (val instanceof Date) { DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellValue(DateUtils.formatDateTime((Date)val)); } else { if (fieldType != Class.class){ cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val)); }else{ cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val)); } } } catch (Exception ex) { log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString()); cell.setCellValue(val.toString()); } cell.setCellStyle(style); return cell; } /** * 添加數據（通過annotation.ExportField添加數據） * @return list 數據列表 */ public <E> ExportExcel setDataList(List<E> list){ for (E e : list){ int colunm = 0; Row row = this.addRow(); StringBuilder sb = new StringBuilder(); if(e instanceof Map){ @SuppressWarnings("unchecked") Map<String,Object> map = (Map<String,Object>)e; for(String key : fieldMap.keySet()){ Object value = map.get(key); String columnDictType = dictTypes.get(colunm+""); if (StringUtils.isNotBlank(columnDictType)){ value = DictUtils.getDictLabel(value==null?"":value.toString(), columnDictType, ""); } this.addCell(row, colunm++, value == null ? "" : value.toString(), 0, String.class); sb.append(value + ", "); } } else{ for (Object[] os : annotationList){ ExcelField ef = (ExcelField)os[0]; Object val = null; // Get entity value try{ if (StringUtils.isNotBlank(ef.value())){ val = Reflections.invokeGetter(e, ef.value()); }else{ if (os[1] instanceof Field){ val = Reflections.invokeGetter(e, ((Field)os[1]).getName()); }else if (os[1] instanceof Method){ val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {}); } } // If is dict, get dict label if (StringUtils.isNotBlank(ef.dictType())){ val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), ""); } }catch(Exception ex) { // Failure to ignore log.info(ex.toString()); val = ""; } this.addCell(row, colunm++, val, ef.align(), ef.fieldType()); sb.append(val + ", "); } log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString()); } } return this; } /** * 輸出數據流 * @param os 輸出數據流 */ public ExportExcel write(OutputStream os) throws IOException{ wb.write(os); return this; } /** * 輸出到客戶端 * @param fileName 輸出文件名 */ public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{ response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName)); write(response.getOutputStream()); return this; } /** * 輸出到文件 * @param fileName 輸出文件名 */ public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{ FileOutputStream os = new FileOutputStream(name); this.write(os); return this; } /** * 清理臨時文件 */ public ExportExcel dispose(){ wb.dispose(); return this; }} |
導出測試
|
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
|
public static void main(String[] args) throws Throwable { List<String> headerList = Lists.newArrayList(); for (int i = 1; i <= 10; i++) { headerList.add("表頭"+i); } List<String> dataRowList = Lists.newArrayList(); for (int i = 1; i <= headerList.size(); i++) { dataRowList.add("數據"+i); } List<List<String>> dataList = Lists.newArrayList(); for (int i = 1; i <=1000000; i++) { dataList.add(dataRowList); } ExportExcel ee = new ExportExcel("表格標題", headerList); for (int i = 0; i < dataList.size(); i++) { Row row = ee.addRow(); for (int j = 0; j < dataList.get(i).size(); j++) { ee.addCell(row, j, dataList.get(i).get(j)); } } ee.writeFile("target/export.xlsx"); ee.dispose(); log.debug("Export success."); } |
ImportExcel.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
|
/** * Copyright © 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved. */package com.thinkgem.jeesite.common.utils.excel; import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.List; import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.web.multipart.MultipartFile; import com.google.common.collect.Lists;import com.thinkgem.jeesite.common.utils.Reflections;import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;import com.thinkgem.jeesite.modules.sys.utils.DictUtils; /** * 導入Excel文件（支持“XLS”和“XLSX”格式） * @author ThinkGem * @version 2013-03-10 */public class ImportExcel { private static Logger log = LoggerFactory.getLogger(ImportExcel.class); /** * 工作薄對象 */ private Workbook wb; /** * 工作表對象 */ private Sheet sheet; /** * 標題行號 */ private int headerNum; /** * 構造函數 * @param path 導入文件，讀取第一個工作表 * @param headerNum 標題行號，數據行號=標題行號+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException { this(new File(fileName), headerNum); } /** * 構造函數 * @param path 導入文件對象，讀取第一個工作表 * @param headerNum 標題行號，數據行號=標題行號+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException { this(file, headerNum, 0); } /** * 構造函數 * @param path 導入文件 * @param headerNum 標題行號，數據行號=標題行號+1 * @param sheetIndex 工作表編號 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(new File(fileName), headerNum, sheetIndex); } /** * 構造函數 * @param path 導入文件對象 * @param headerNum 標題行號，數據行號=標題行號+1 * @param sheetIndex 工作表編號 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(file.getName(), new FileInputStream(file), headerNum, sheetIndex); } /** * 構造函數 * @param file 導入文件對象 * @param headerNum 標題行號，數據行號=標題行號+1 * @param sheetIndex 工作表編號 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex); } /** * 構造函數 * @param path 導入文件對象 * @param headerNum 標題行號，數據行號=標題行號+1 * @param sheetIndex 工作表編號 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { if (StringUtils.isBlank(fileName)){ throw new RuntimeException("導入文檔為空!"); }else if(fileName.toLowerCase().endsWith("xls")){ this.wb = new HSSFWorkbook(is); }else if(fileName.toLowerCase().endsWith("xlsx")){ this.wb = new XSSFWorkbook(is); }else{ throw new RuntimeException("文檔格式不正確!"); } if (this.wb.getNumberOfSheets()<sheetIndex){ throw new RuntimeException("文檔中沒有工作表!"); } this.sheet = this.wb.getSheetAt(sheetIndex); this.headerNum = headerNum; log.debug("Initialize success."); } /** * 獲取行對象 * @param rownum * @return */ public Row getRow(int rownum){ return this.sheet.getRow(rownum); } /** * 獲取數據行號 * @return */ public int getDataRowNum(){ return headerNum+1; } /** * 獲取最后一個數據行號 * @return */ public int getLastDataRowNum(){ return this.sheet.getLastRowNum()+headerNum; } /** * 獲取最后一個列號 * @return */ public int getLastCellNum(){ return this.getRow(headerNum).getLastCellNum(); } /** * 獲取單元格值 * @param row 獲取的行 * @param column 獲取單元格列號 * @return 單元格值 */ public Object getCellValue(Row row, int column){ Object val = ""; try{ Cell cell = row.getCell(column); if (cell != null){ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ val = cell.getNumericCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){ val = cell.getStringCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){ val = cell.getCellFormula(); }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ val = cell.getBooleanCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){ val = cell.getErrorCellValue(); } } }catch (Exception e) { return val; } return val; } /** * 獲取導入數據列表 * @param cls 導入對象類型 * @param groups 導入分組 */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{ List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs){ ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==2)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, f}); break; } } } }else{ annotationList.add(new Object[]{ef, f}); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms){ ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==2)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, m}); break; } } } }else{ annotationList.add(new Object[]{ef, m}); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField)o1[0]).sort()).compareTo( new Integer(((ExcelField)o2[0]).sort())); }; }); //log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E)cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList){ Object val = this.getCellValue(row, column++); if (val != null){ ExcelField ef = (ExcelField)os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())){ val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field){ valType = ((Field)os[1]).getType(); }else if (os[1] instanceof Method){ Method method = ((Method)os[1]); if ("get".equals(method.getName().substring(0, 3))){ valType = method.getReturnType(); }else if("set".equals(method.getName().substring(0, 3))){ valType = ((Method)os[1]).getParameterTypes()[0]; } } //log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class){ String s = String.valueOf(val.toString()); if(StringUtils.endsWith(s, ".0")){ val = StringUtils.substringBefore(s, ".0"); }else{ val = String.valueOf(val.toString()); } }else if (valType == Integer.class){ val = Double.valueOf(val.toString()).intValue(); }else if (valType == Long.class){ val = Double.valueOf(val.toString()).longValue(); }else if (valType == Double.class){ val = Double.valueOf(val.toString()); }else if (valType == Float.class){ val = Float.valueOf(val.toString()); }else if (valType == Date.class){ val = DateUtil.getJavaDate((Double)val); }else{ if (ef.fieldType() != Class.class){ val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); }else{ val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value ["+i+","+column+"] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field){ Reflections.invokeSetter(e, ((Field)os[1]).getName(), val); }else if (os[1] instanceof Method){ String mthodName = ((Method)os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))){ mthodName = "set"+StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val}); } } sb.append(val+", "); } dataList.add(e); log.debug("Read success: ["+i+"] "+sb.toString()); } return dataList; } } |
導入測試
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
public static void main(String[] args) throws Throwable { ImportExcel ei = new ImportExcel("target/export.xlsx", 1); for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) { Row row = ei.getRow(i); for (int j = 0; j < ei.getLastCellNum(); j++) { Object val = ei.getCellValue(row, j); System.out.print(val+", "); } System.out.print("\n"); } } |
