导出就是将List转化为Excel(listToExcel)
导入就是将Excel转化为List(excelToList)
导入导出中会出现各种各样的问题,比如:数据源为空、有重复行等,我自定义了一个ExcelException异常类,用来处理这些问题。
ExcelException类
package common.tool.excel;public class ExcelException extends Exception { public ExcelException() { } public ExcelException(String message) { super(message); } public ExcelException(Throwable cause) { super(cause); } public ExcelException(String message, Throwable cause) { super(message, cause); }}
- 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
- 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
下面就是该文的主角ExcelUtil登场了,作为一个工具类,其内的所有方法都是静态的,方便使用。
ExcelUitl类
/** * @author : WH * @group : tgb8 * @Date : 2014-1-2 下午9:13:21 * @Comments : 导入导出Excel工具类 * @Version : 1.0.0 */public class ExcelUtil { /** * @MethodName : listToExcel * @Description : 导出Excel(可以导出到本地文件系统,也可以导出到浏览器,可自定义工作表大小) * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * 如果需要的是引用对象的属性,则英文属性使用类似于EL表达式的格式 * 如:list中存放的都是student,student中又有college属性,而我们需要学院名称,则可以这样写 * fieldMap.put("college.collegeName","学院名称") * @param sheetName 工作表的名称 * @param sheetSize 每个工作表中记录的最大个数 * @param out 导出流 * @throws ExcelException */ public static <T> void listToExcel ( List<T> list , LinkedHashMap<String,String> fieldMap, String sheetName, int sheetSize, OutputStream out ) throws ExcelException{ if(list.size()==0 || list==null){ throw new ExcelException("数据源中没有任何数据"); } if(sheetSize>65535 || sheetSize<1){ sheetSize=65535; } WritableWorkbook wwb; try { wwb = Workbook.createWorkbook(out); double sheetNum=Math.ceil(list.size()/new Integer(sheetSize).doubleValue()); for(int i=0; i<sheetNum; i++){ if(1==sheetNum){ WritableSheet sheet=wwb.createSheet(sheetName, i); fillSheet(sheet, list, fieldMap, 0, list.size()-1); }else{ WritableSheet sheet=wwb.createSheet(sheetName+(i+1), i); int firstIndex=i*sheetSize; int lastIndex=(i+1)*sheetSize-1>list.size()-1 ? list.size()-1 : (i+1)*sheetSize-1; fillSheet(sheet, list, fieldMap, firstIndex, lastIndex); } } wwb.write(); wwb.close(); }catch (Exception e) { e.printStackTrace(); if(e instanceof ExcelException){ throw (ExcelException)e; }else{ throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcel * @Description : 导出Excel(可以导出到本地文件系统,也可以导出到浏览器,工作表大小为2003支持的最大值) * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param out 导出流 * @throws ExcelException */ public static <T> void listToExcel ( List<T> list , LinkedHashMap<String,String> fieldMap, String sheetName, OutputStream out ) throws ExcelException{ listToExcel(list, fieldMap, sheetName, 65535, out); } /** * @MethodName : listToExcel * @Description : 导出Excel(导出到浏览器,可以自定义工作表的大小) * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param sheetSize 每个工作表中记录的最大个数 * @param response 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcel ( List<T> list , LinkedHashMap<String,String> fieldMap, String sheetName, int sheetSize, HttpServletResponse response ) throws ExcelException{ String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" ); try { OutputStream out=response.getOutputStream(); listToExcel(list, fieldMap, sheetName, sheetSize,out ); } catch (Exception e) { e.printStackTrace(); if(e instanceof ExcelException){ throw (ExcelException)e; }else{ throw new ExcelException("导出Excel失败"); } } } /** * @MethodName : listToExcel * @Description : 导出Excel(导出到浏览器,工作表的大小是2003支持的最大值) * @param list 数据源 * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 * @param response 使用response可以导出到浏览器 * @throws ExcelException */ public static <T> void listToExcel ( List<T> list , LinkedHashMap<String,String> fieldMap, String sheetName, HttpServletResponse response ) throws ExcelException{ listToExcel(list, fieldMap, sheetName, 65535, response); } /** * @MethodName : excelToList * @Description : 将Excel转化为List * @param in :承载着Excel的输入流 * @param sheetIndex :要导入的工作表序号 * @param entityClass :List中对象的类型(Excel中的每一行都要转化为该类型的对象) * @param fieldMap :Excel中的中文列头和类的英文属性的对应关系Map * @param uniqueFields :指定业务主键组合(即复合主键),这些列的组合不能重复 * @return :List * @throws ExcelException */ public static <T> List<T> excelToList( InputStream in, String sheetName, Class<T> entityClass, LinkedHashMap<String, String> fieldMap, String[] uniqueFields ) throws ExcelException{ List<T> resultList=new ArrayList<T>(); try { Workbook wb=Workbook.getWorkbook(in); Sheet sheet=wb.getSheet(sheetName); int realRows=0; for(int i=0;i<sheet.getRows();i++){ int nullCols=0; for(int j=0;j<sheet.getColumns();j++){ Cell currentCell=sheet.getCell(j,i); if(currentCell==null || "".equals(currentCell.getContents().toString())){ nullCols++; } } if(nullCols==sheet.getColumns()){ break; }else{ realRows++; } } if(realRows<=1){ throw new ExcelException("Excel文件中没有任何数据"); } Cell[] firstRow=sheet.getRow(0); String[] excelFieldNames=new String[firstRow.length]; for(int i=0;i<firstRow.length;i++){ excelFieldNames[i]=firstRow[i].getContents().toString().trim(); } boolean isExist=true; List<String> excelFieldList=Arrays.asList(excelFieldNames); for(String cnName : fieldMap.keySet()){ if(!excelFieldList.contains(cnName)){ isExist=false; break; } } if(!isExist){ throw new ExcelException("Excel中缺少必要的字段,或字段名称有误"); } LinkedHashMap<String, Integer> colMap=new LinkedHashMap<String, Integer>(); for(int i=0;i<excelFieldNames.length;i++){ colMap.put(excelFieldNames[i], firstRow[i].getColumn()); } Cell[][] uniqueCells=new Cell[uniqueFields.length][]; for(int i=0;i<uniqueFields.length;i++){ int col=colMap.get(uniqueFields[i]); uniqueCells[i]=sheet.getColumn(col); } for(int i=1;i<realRows;i++){ int nullCols=0; for(int j=0;j<uniqueFields.length;j++){ String currentContent=uniqueCells[j][i].getContents(); Cell sameCell=sheet.findCell(currentContent, uniqueCells[j][i].getColumn(), uniqueCells[j][i].getRow()+1, uniqueCells[j][i].getColumn(), uniqueCells[j][realRows-1].getRow(), true); if(sameCell!=null){ nullCols++; } } if(nullCols==uniqueFields.length){ throw new ExcelException("Excel中有重复行,请检查"); } } for(int i=1;i<realRows;i++){ T entity=entityClass.newInstance(); for(Entry<String, String> entry : fieldMap.entrySet()){ String cnNormalName=entry.getKey(); String enNormalName=entry.getValue(); int col=colMap.get(cnNormalName); String content=sheet.getCell(col, i).getContents().toString().trim(); setFieldValueByName(enNormalName, content, entity); } resultList.add(entity); } } catch(Exception e){ e.printStackTrace(); if(e instanceof ExcelException){ throw (ExcelException)e; }else{ e.printStackTrace(); throw new ExcelException("导入Excel失败"); } } return resultList; } /** * @MethodName : getFieldValueByName * @Description : 根据字段名获取字段值 * @param fieldName 字段名 * @param o 对象 * @return 字段值 */ private static Object getFieldValueByName(String fieldName, Object o) throws Exception{ Object value=null; Field field=getFieldByName(fieldName, o.getClass()); if(field !=null){ field.setAccessible(true); value=field.get(o); }else{ throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName); } return value; } /** * @MethodName : getFieldByName * @Description : 根据字段名获取字段 * @param fieldName 字段名 * @param clazz 包含该字段的类 * @return 字段 */ private static Field getFieldByName(String fieldName, Class<?> clazz){ Field[] selfFields=clazz.getDeclaredFields(); for(Field field : selfFields){ if(field.getName().equals(fieldName)){ return field; } } Class<?> superClazz=clazz.getSuperclass(); if(superClazz!=null && superClazz !=Object.class){ return getFieldByName(fieldName, superClazz); } return null; } /** * @MethodName : getFieldValueByNameSequence * @Description : * 根据带路径或不带路径的属性名获取属性值 * 即接受简单属性名,如userName等,又接受带路径的属性名,如student.department.name等 * * @param fieldNameSequence 带路径的属性名或简单属性名 * @param o 对象 * @return 属性值 * @throws Exception */ private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception{ Object value=null; String[] attributes=fieldNameSequence.split("\\."); if(attributes.length==1){ value=getFieldValueByName(fieldNameSequence, o); }else{ Object fieldObj=getFieldValueByName(attributes[0], o); String subFieldNameSequence=fieldNameSequence.substring(fieldNameSequence.indexOf(".")+1); value=getFieldValueByNameSequence(subFieldNameSequence, fieldObj); } return value; } /** * @MethodName : setFieldValueByName * @Description : 根据字段名给对象的字段赋值 * @param fieldName 字段名 * @param fieldValue 字段值 * @param o 对象 */ private static void setFieldValueByName(String fieldName,Object fieldValue,Object o) throws Exception{ Field field=getFieldByName(fieldName, o.getClass()); if(field!=null){ field.setAccessible(true); Class<?> fieldType = field.getType(); if (String.class == fieldType) { field.set(o, String.valueOf(fieldValue)); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { field.set(o, Integer.parseInt(fieldValue.toString())); } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) { field.set(o, Long.valueOf(fieldValue.toString())); } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) { field.set(o, Float.valueOf(fieldValue.toString())); } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) { field.set(o, Short.valueOf(fieldValue.toString())); } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { field.set(o, Double.valueOf(fieldValue.toString())); } else if (Character.TYPE == fieldType) { if ((fieldValue!= null) && (fieldValue.toString().length() > 0)) { field.set(o, Character .valueOf(fieldValue.toString().charAt(0))); } }else if(Date.class==fieldType){ field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString())); }else{ field.set(o, fieldValue); } }else{ throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName); } } /** * @MethodName : setColumnAutoSize * @Description : 设置工作表自动列宽和首行加粗 * @param ws */ private static void setColumnAutoSize(WritableSheet ws,int extraWith){ for(int i=0;i<ws.getColumns();i++){ int colWith=0; for(int j=0;j<ws.getRows();j++){ String content=ws.getCell(i,j).getContents().toString(); int cellWith=content.length(); if(colWith<cellWith){ colWith=cellWith; } } ws.setColumnView(i, colWith+extraWith); } } /** * @MethodName : fillSheet * @Description : 向工作表中填充数据 * @param sheet 工作表 * @param list 数据源 * @param fieldMap 中英文字段对应关系的Map * @param firstIndex 开始索引 * @param lastIndex 结束索引 */ private static <T> void fillSheet( WritableSheet sheet, List<T> list, LinkedHashMap<String,String> fieldMap, int firstIndex, int lastIndex )throws Exception{ String[] enFields=new String[fieldMap.size()]; String[] cnFields=new String[fieldMap.size()]; int count=0; for(Entry<String,String> entry:fieldMap.entrySet()){ enFields[count]=entry.getKey(); cnFields[count]=entry.getValue(); count++; } for(int i=0;i<cnFields.length;i++){ Label label=new Label(i,0,cnFields[i]); sheet.addCell(label); } int rowNo=1; for(int index=firstIndex;index<=lastIndex;index++){ T item=list.get(index); for(int i=0;i<enFields.length;i++){ Object objValue=getFieldValueByNameSequence(enFields[i], item); String fieldValue=objValue==null ? "" : objValue.toString(); Label label =new Label(i,rowNo,fieldValue); sheet.addCell(label); } rowNo++; } setColumnAutoSize(sheet, 5); }}
- 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
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 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
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
导入数据:
@Action public void showInputSalesData() { { FileOutputStream fos = null try { jProgressBar_data.setValue(0) Date startDate = jIDatePanel_startDate.getDate() Date endDate = jIDatePanel_endDate.getDate() if (startDate == null || endDate == null) { AppMain.showError("请选择日期") return } String start = Pub.formatDate(startDate) String end = Pub.formatDate(endDate) jProgressBar_data.setMinimum(0) List<K3SalesInvoice> datas = EJBClientRemote.getK3CommonSessionBeanRemote().findAllSalesInvoice(start, end) System.out.print("datas.size()==" + datas.size()) jProgressBar_data.setMaximum(datas.size() - 1) if (datas == null || datas.size() <= 0) { AppMain.showError("没有数据") return } FileDialog dialog = new FileDialog() int index = dialog.showSaveDialog(this) if (index == 1) { return } String filename = dialog.getSelectedFile().getAbsolutePath() if (!filename.endsWith("xls")) { filename += ".xls" } File file = new File(filename) LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>() fieldMap.put("fdate", "日期") fieldMap.put("fbillno", "单号") fieldMap.put("accuontNO", "客户代码") fieldMap.put("currency", "币别") fieldMap.put("fExchangeRate", "汇率") fieldMap.put("fTaxRate", "税率") fieldMap.put("materialCode", "物料代码") fieldMap.put("number", "数量") fieldMap.put("price", "单价") fieldMap.put("amount", "金额") fieldMap.put("voucher", "制单") fieldMap.put("department", "部门") fieldMap.put("salesMan", "业务员") fieldMap.put("subjects", "往来科目") fieldMap.put("note", "摘要") if (!file.exists()) { //file.mkdirs() file.createNewFile() } fos = new FileOutputStream(file) //设置监听 ExcelUtil.setOnProgressListener(this) ExcelUtil.listToExcel(datas, fieldMap, null, fos) } catch (IOException ex) { Logger.getLogger(K3DataInputAndOutput_Panel.class.getName()).log(Level.SEVERE, null, ex) } catch (FileNotFoundException ex) { Logger.getLogger(K3DataInputAndOutput_Panel.class.getName()).log(Level.SEVERE, null, ex) } catch (ExcelException ex) { Logger.getLogger(K3DataInputAndOutput_Panel.class.getName()).log(Level.SEVERE, null, ex) } finally { try { if (fos != null) { fos.close() } } catch (IOException ex) { Logger.getLogger(K3DataInputAndOutput_Panel.class.getName()).log(Level.SEVERE, null, ex) } } } }
- 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
- 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
该工具类有4个重载的导出方法和1个导入方法,大家可以根据实际情况进行选择。
总结
导入和导出方法都是通过传一个fieldMap参数(类的英文属性和Excel的中文列头的对应关系)来连接实体类和Excel的
导出的时候可以选择导出到本地文件系统或导出到浏览器,也可以自定义每个工作表的大小
导入的时候可以自定义业务主键组合uniqueFields,这样就可以检测Excel中是否有重复行了