参考博客：
http://www.oschina.net/code/snippet_565430_15074
增加了多sheet，多列的自动合并。
修改了部分过时方法和导出逻辑。
优化了标题，导出信息等
先看下效果，如果正常导出是这样子：
自动合并后是：
动态图示例：
poi导出并不是一件很麻烦的事情，只是逻辑相对复杂，要考虑到各种情况。我把代码贴上，有用得到的可以帮助一下。
导出类：
复制代码
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
4581 package com.centit.njld.commons.testexcel; 2 3 import java.io.ByteArrayOutputStream; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6 import java.util.LinkedHashMap; 7 import java.util.List; 8 import java.util.Map.Entry; 9 import java.util.Set; 10 11 import org.apache.poi.hssf.usermodel.HSSFCell; 12 import org.apache.poi.hssf.usermodel.HSSFRow; 13 import org.apache.poi.hssf.usermodel.HSSFSheet; 14 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 15 import org.apache.poi.ss.usermodel.CellStyle; 16 import org.apache.poi.ss.usermodel.Font; 17 import org.apache.poi.ss.usermodel.IndexedColors; 18 import org.apache.poi.ss.util.CellRangeAddress; 19 20 import com.centit.njld.commons.testexcel.TestExcel_Reflection; 21 22 public class TestExcel_Export { 23 24 private static HSSFWorkbook wb; 25 26 private static CellStyle titleStyle; // 标题行样式 27 private static Font titleFont; // 标题行字体 28 private static CellStyle dateStyle; // 日期行样式 29 private static Font dateFont; // 日期行字体 30 private static CellStyle headStyle; // 表头行样式 31 private static Font headFont; // 表头行字体 32 private static CellStyle contentStyle; // 内容行样式 33 private static Font contentFont; // 内容行字体 34 35 /** 36 * 导出文件 37 */ 38 public static boolean export2File(ExcelExportData setInfo, 39 String outputExcelFileName) throws Exception { 40 return TestExcel_FileUtil.write(outputExcelFileName, export2ByteArray(setInfo), 41 true, true); 42 } 43 44 /** 45 * 导出到byte数组 46 */ 47 public static byte[] export2ByteArray(ExcelExportData setInfo) 48 throws Exception { 49 return export2Stream(setInfo).toByteArray(); 50 } 51 52 /** 53 * 导出到流 54 */ 55 public static ByteArrayOutputStream export2Stream(ExcelExportData setInfo) 56 throws Exception { 57 init(); 58 59 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); 60 61 Set<Entry<String, List<?>>> set = setInfo.getDataMap().entrySet(); 62 String[] sheetNames = new String[setInfo.getDataMap().size()]; 63 int sheetNameNum = 0; 64 for (Entry<String, List<?>> entry : set) { 65 sheetNames[sheetNameNum] = entry.getKey(); 66 sheetNameNum++; 67 } 68 HSSFSheet[] sheets = getSheets(setInfo.getDataMap().size(), sheetNames); 69 int sheetNum = 0; 70 int k = 0; 71 72 for (Entry<String, List<?>> entry : set) { 73 // Sheet 74 List<?> objs = entry.getValue(); 75 76 // 标题行 77 createTableTitleRow(setInfo, sheets, sheetNum); 78 79 // 日期行 80 createTableDateRow(setInfo, sheets, sheetNum); 81 82 // 表头 83 creatTableHeadRow(setInfo, sheets, sheetNum); 84 85 // 表体 86 String[] fieldNames = setInfo.getFieldNames().get(sheetNum); 87 88 int rowNum = 3; 89 for (Object obj : objs) { 90 HSSFRow contentRow = sheets[sheetNum].createRow(rowNum); 91 contentRow.setHeight((short) 300); 92 HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames().get(sheetNum).length); 93 int cellNum = 1; // 去掉一列序号，因此从1开始 94 if (fieldNames != null) { 95 for (int num = 0; num < fieldNames.length; num++) { 96 Object value = TestExcel_Reflection.invokeGetterMethod(obj,fieldNames[num]); 97 cells[cellNum].setCellValue(value == null ? "" : value.toString()); 98 cellNum++; 99 } 100 } 101 rowNum++; 102 } 103 104 k++; 105 String[] groupColumns = null; 106 if(setInfo.getGroupColumn().size()!=0){ 107 if(setInfo.getGroupColumn().size() >= k){ 108 groupColumns = setInfo.getGroupColumn().get(sheetNum); 109 } 110 } 111 112 if(groupColumns!=null){ 113 int n=0; 114 for (int i = 0; i < groupColumns.length; i++) { 115 116 String[] fieldName = setInfo.getFieldNames().get(sheetNum); 117 for (int j = 0; j < fieldName.length; j++) { 118 if(groupColumns[i].equals(fieldName[j])){ 119 j++; 120 n=j; 121 break; 122 } 123 } 124 int x = 0; 125 int y = 0; 126 int z = 3; 127 int m = objs.size(); 128 boolean flag = false; 129 Object val = null; 130 CellRangeAddress dateRange = null; 131 for (Object obj : objs) { 132 y++; 133 Object value = TestExcel_Reflection.invokeGetterMethod(obj,groupColumns[i]); 134 if(x==0){ 135 x++; 136 val=value; 137 }else if(val.toString().equals(value.toString())){ 138 x++; 139 if(m==y){ 140 dateRange = new CellRangeAddress(z, x+3, n, n); 141 sheets[sheetNum].addMergedRegion(dateRange); 142 } 143 }else{ 144 val=value; 145 if(flag){ 146 dateRange = new CellRangeAddress(z, x+3, n, n); 147 z=x+4; 148 x=x+1; 149 }else{ 150 dateRange = new CellRangeAddress(z, x+2, n, n); 151 z=x+3; 152 } 153 sheets[sheetNum].addMergedRegion(dateRange); 154 flag=true; 155 } 156 } 157 } 158 } 159 160 // CellRangeAddress dateRange = new CellRangeAddress(3, 10, 1, 1); 161 // sheets[sheetNum].addMergedRegion(dateRange); 162 // 163 // CellRangeAddress aa = new CellRangeAddress(11, 15, 1, 1); 164 // sheets[sheetNum].addMergedRegion(aa); 165 // 166 // CellRangeAddress bb = new CellRangeAddress(3, 5, 2, 2); 167 // sheets[sheetNum].addMergedRegion(bb); 168 169 // 170 // CellRangeAddress aaa = new CellRangeAddress(16, 18, 1, 1); 171 // sheets[sheetNum].addMergedRegion(aaa); 172 173 adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽 174 sheetNum++; 175 } 176 wb.write(outputStream); 177 return outputStream; 178 } 179 180 /** 181 * @Description: 初始化 182 */ 183 private static void init() { 184 wb = new HSSFWorkbook(); 185 186 titleFont = wb.createFont(); 187 titleStyle = wb.createCellStyle(); 188 dateStyle = wb.createCellStyle(); 189 dateFont = wb.createFont(); 190 headStyle = wb.createCellStyle(); 191 headFont = wb.createFont(); 192 contentStyle = wb.createCellStyle(); 193 contentFont = wb.createFont(); 194 195 initTitleCellStyle(); 196 initTitleFont(); 197 initDateCellStyle(); 198 initDateFont(); 199 initHeadCellStyle(); 200 initHeadFont(); 201 initContentCellStyle(); 202 initContentFont(); 203 } 204 205 /** 206 * @Description: 自动调整列宽 207 */ 208 private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum, 209 String[] fieldNames) { 210 for (int i = 0; i < fieldNames.length + 1; i++) { 211 sheets[sheetNum].autoSizeColumn(i, true); 212 } 213 } 214 215 /** 216 * @Description: 创建标题行(需合并单元格) 217 */ 218 private static void createTableTitleRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum) { 219 CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo.getFieldNames().get(sheetNum).length); 220 sheets[sheetNum].addMergedRegion(titleRange); 221 HSSFRow titleRow = sheets[sheetNum].createRow(0); 222 titleRow.setHeight((short) 800); 223 HSSFCell titleCell = titleRow.createCell(0); 224 titleCell.setCellStyle(titleStyle); 225 titleCell.setCellValue(setInfo.getTitles()[sheetNum]); 226 } 227 228 /** 229 * @Description: 创建日期行(需合并单元格) 230 */ 231 private static void createTableDateRow(ExcelExportData setInfo,HSSFSheet[] sheets, int sheetNum) { 232 CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo.getFieldNames().get(sheetNum).length); 233 sheets[sheetNum].addMergedRegion(dateRange); 234 HSSFRow dateRow = sheets[sheetNum].createRow(1); 235 dateRow.setHeight((short) 350); 236 HSSFCell dateCell = dateRow.createCell(0); 237 dateCell.setCellStyle(dateStyle); 238 dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date())); 239 } 240 241 /** 242 * @Description: 创建表头行(需合并单元格) 243 */ 244 private static void creatTableHeadRow(ExcelExportData setInfo, 245 HSSFSheet[] sheets, int sheetNum) { 246 // 表头 247 HSSFRow headRow = sheets[sheetNum].createRow(2); 248 headRow.setHeight((short) 350); 249 // 序号列 250 HSSFCell snCell = headRow.createCell(0); 251 snCell.setCellStyle(headStyle); 252 snCell.setCellValue("序号"); 253 // 列头名称 254 for (int num = 1, len = setInfo.getColumnNames().get(sheetNum).length; num <= len; num++) { 255 HSSFCell headCell = headRow.createCell(num); 256 headCell.setCellStyle(headStyle); 257 headCell.setCellValue(setInfo.getColumnNames().get(sheetNum)[num - 1]); 258 } 259 } 260 261 /** 262 * @Description: 创建所有的Sheet 263 */ 264 private static HSSFSheet[] getSheets(int num, String[] names) { 265 HSSFSheet[] sheets = new HSSFSheet[num]; 266 for (int i = 0; i < num; i++) { 267 sheets[i] = wb.createSheet(names[i]); 268 } 269 return sheets; 270 } 271 272 /** 273 * @Description: 创建内容行的每一列(附加一列序号) 274 */ 275 private static HSSFCell[] getCells(HSSFRow contentRow, int num) { 276 HSSFCell[] cells = new HSSFCell[num + 1]; 277 278 for (int i = 0, len = cells.length; i < len; i++) { 279 cells[i] = contentRow.createCell(i); 280 cells[i].setCellStyle(contentStyle); 281 } 282 283 // 设置序号列值，因为出去标题行和日期行，所有-2 284 cells[0].setCellValue(contentRow.getRowNum() - 2); 285 286 return cells; 287 } 288 289 /** 290 * @Description: 初始化标题行样式 291 */ 292 private static void initTitleCellStyle() { 293 titleStyle.setAlignment(CellStyle.ALIGN_CENTER); 294 titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 295 titleStyle.setFont(titleFont); 296 titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex()); 297 } 298 299 /** 300 * @Description: 初始化日期行样式 301 */ 302 private static void initDateCellStyle() { 303 dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); 304 dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 305 dateStyle.setFont(dateFont); 306 dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex()); 307 } 308 309 /** 310 * @Description: 初始化表头行样式 311 */ 312 private static void initHeadCellStyle() { 313 headStyle.setAlignment(CellStyle.ALIGN_CENTER); 314 headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 315 headStyle.setFont(headFont); 316 headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); 317 headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); 318 headStyle.setBorderBottom(CellStyle.BORDER_THIN); 319 headStyle.setBorderLeft(CellStyle.BORDER_THIN); 320 headStyle.setBorderRight(CellStyle.BORDER_THIN); 321 headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex()); 322 headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); 323 headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex()); 324 headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); 325 } 326 327 /** 328 * @Description: 初始化内容行样式 329 */ 330 private static void initContentCellStyle() { 331 contentStyle.setAlignment(CellStyle.ALIGN_CENTER); 332 contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 333 contentStyle.setFont(contentFont); 334 contentStyle.setBorderTop(CellStyle.BORDER_THIN); 335 contentStyle.setBorderBottom(CellStyle.BORDER_THIN); 336 contentStyle.setBorderLeft(CellStyle.BORDER_THIN); 337 contentStyle.setBorderRight(CellStyle.BORDER_THIN); 338 contentStyle.setTopBorderColor(IndexedColors.BLUE.getIndex()); 339 contentStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); 340 contentStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex()); 341 contentStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); 342 contentStyle.setWrapText(true); // 字段换行 343 } 344 345 /** 346 * @Description: 初始化标题行字体 347 */ 348 private static void initTitleFont() { 349 titleFont.setFontName("华文楷体"); 350 titleFont.setFontHeightInPoints((short) 20); 351 titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 352 titleFont.setCharSet(Font.DEFAULT_CHARSET); 353 titleFont.setColor(IndexedColors.BLUE_GREY.getIndex()); 354 } 355 356 /** 357 * @Description: 初始化日期行字体 358 */ 359 private static void initDateFont() { 360 dateFont.setFontName("隶书"); 361 dateFont.setFontHeightInPoints((short) 10); 362 dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 363 dateFont.setCharSet(Font.DEFAULT_CHARSET); 364 dateFont.setColor(IndexedColors.BLUE_GREY.getIndex()); 365 } 366 367 /** 368 * @Description: 初始化表头行字体 369 */ 370 private static void initHeadFont() { 371 headFont.setFontName("宋体"); 372 headFont.setFontHeightInPoints((short) 10); 373 headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 374 headFont.setCharSet(Font.DEFAULT_CHARSET); 375 headFont.setColor(IndexedColors.BLUE_GREY.getIndex()); 376 } 377 378 /** 379 * @Description: 初始化内容行字体 380 */ 381 private static void initContentFont() { 382 contentFont.setFontName("宋体"); 383 contentFont.setFontHeightInPoints((short) 10); 384 contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); 385 contentFont.setCharSet(Font.DEFAULT_CHARSET); 386 contentFont.setColor(IndexedColors.BLUE_GREY.getIndex()); 387 } 388 389 /** 390 * Excel导出数据类 391 * @author jimmy 392 */ 393 public static class ExcelExportData { 394 395 /** 396 * 导出数据 key:String 表示每个Sheet的名称 value:List<?> 表示每个Sheet里的所有数据行 397 */ 398 private LinkedHashMap<String, List<?>> dataMap; 399 400 /** 401 * 每个Sheet里的顶部大标题 402 */ 403 private String[] titles; 404 405 /** 406 * 单个sheet里的数据列标题 407 */ 408 private List<String[]> columnNames; 409 410 /** 411 * 单个sheet里每行数据的列对应的对象属性名称 412 */ 413 private List<String[]> fieldNames; 414 415 private List<String[]> groupColumn; 416 417 public List<String[]> getFieldNames() { 418 return fieldNames; 419 } 420 421 public void setFieldNames(List<String[]> fieldNames) { 422 this.fieldNames = fieldNames; 423 } 424 425 public String[] getTitles() { 426 return titles; 427 } 428 429 public void setTitles(String[] titles) { 430 this.titles = titles; 431 } 432 433 public List<String[]> getColumnNames() { 434 return columnNames; 435 } 436 437 public void setColumnNames(List<String[]> columnNames) { 438 this.columnNames = columnNames; 439 } 440 441 public LinkedHashMap<String, List<?>> getDataMap() { 442 return dataMap; 443 } 444 445 public void setDataMap(LinkedHashMap<String, List<?>> dataMap) { 446 this.dataMap = dataMap; 447 } 448 449 public List<String[]> getGroupColumn() { 450 return groupColumn; 451 } 452 453 public void setGroupColumn(List<String[]> groupColumn) { 454 this.groupColumn = groupColumn; 455 } 456 } 457 458 }
文件下载类：
复制代码
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
4181 package com.centit.njld.commons.testexcel; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.FilenameFilter; 7 import java.io.IOException; 8 import java.io.OutputStream; 9 import java.io.RandomAccessFile; 10 import java.util.ArrayList; 11 import java.util.Arrays; 12 import java.util.Collections; 13 import java.util.Comparator; 14 import java.util.List; 15 import java.util.concurrent.ArrayBlockingQueue; 16 import java.util.concurrent.ThreadPoolExecutor; 17 import java.util.concurrent.TimeUnit; 18 19 public class TestExcel_FileUtil { 20 21 /** 22 * 当前目录路径 23 */ 24 public static String currentWorkDir = System.getProperty("user.dir") + "\"; 25 26 /** 27 * 左填充 28 * 29 * @param str 30 * @param length 31 * @param ch 32 * @return 33 */ 34 public static String leftPad(String str, int length, char ch) { 35 if (str.length() >= length) { 36 return str; 37 } 38 char[] chs = new char[length]; 39 Arrays.fill(chs, ch); 40 char[] src = str.toCharArray(); 41 System.arraycopy(src, 0, chs, length - src.length, src.length); 42 return new String(chs); 43 44 } 45 46 /** 47 * 删除文件 48 * 49 * @param fileName 50 * 待删除的完整文件名 51 * @return 52 */ 53 public static boolean delete(String fileName) { 54 boolean result = false; 55 File f = new File(fileName); 56 if (f.exists()) { 57 result = f.delete(); 58 59 } else { 60 result = true; 61 } 62 return result; 63 } 64 65 /*** 66 * 递归获取指定目录下的所有的文件（不包括文件夹） 67 * 68 * @param obj 69 * @return 70 */ 71 public static ArrayList<File> getAllFiles(String dirPath) { 72 File dir = new File(dirPath); 73 74 ArrayList<File> files = new ArrayList<File>(); 75 76 if (dir.isDirectory()) { 77 File[] fileArr = dir.listFiles(); 78 for (int i = 0; i < fileArr.length; i++) { 79 File f = fileArr[i]; 80 if (f.isFile()) { 81 files.add(f); 82 } else { 83 files.addAll(getAllFiles(f.getPath())); 84 } 85 } 86 } 87 return files; 88 } 89 90 /** 91 * 获取指定目录下的所有文件(不包括子文件夹) 92 * 93 * @param dirPath 94 * @return 95 */ 96 public static ArrayList<File> getDirFiles(String dirPath) { 97 File path = new File(dirPath); 98 File[] fileArr = path.listFiles(); 99 ArrayList<File> files = new ArrayList<File>(); 100 101 for (File f : fileArr) { 102 if (f.isFile()) { 103 files.add(f); 104 } 105 } 106 return files; 107 } 108 109 /** 110 * 获取指定目录下特定文件后缀名的文件列表(不包括子文件夹) 111 * 112 * @param dirPath 113 * 目录路径 114 * @param suffix 115 * 文件后缀 116 * @return 117 */ 118 public static ArrayList<File> getDirFiles(String dirPath, 119 final String suffix) { 120 File path = new File(dirPath); 121 File[] fileArr = path.listFiles(new FilenameFilter() { 122 public boolean accept(File dir, String name) { 123 String lowerName = name.toLowerCase(); 124 String lowerSuffix = suffix.toLowerCase(); 125 if (lowerName.endsWith(lowerSuffix)) { 126 return true; 127 } 128 return false; 129 } 130 131 }); 132 ArrayList<File> files = new ArrayList<File>(); 133 134 for (File f : fileArr) { 135 if (f.isFile()) { 136 files.add(f); 137 } 138 } 139 return files; 140 } 141 142 /** 143 * 读取文件内容 144 * 145 * @param fileName 146 * 待读取的完整文件名 147 * @return 文件内容 148 * @throws IOException 149 */ 150 public static String read(String fileName) throws IOException { 151 File f = new File(fileName); 152 FileInputStream fs = new FileInputStream(f); 153 String result = null; 154 byte[] b = new byte[fs.available()]; 155 fs.read(b); 156 fs.close(); 157 result = new String(b); 158 return result; 159 } 160 161 /** 162 * 写文件 163 * 164 * @param fileName 165 * 目标文件名 166 * @param fileContent 167 * 写入的内容 168 * @return 169 * @throws IOException 170 */ 171 public static boolean write(String fileName, String fileContent) 172 throws IOException { 173 return write(fileName, fileContent, true, true); 174 } 175 176 /** 177 * 写文件 178 * 179 * @param fileName 180 * 完整文件名(类似：/usr/a/b/c/d.txt) 181 * @param fileContent 182 * 文件内容 183 * @param autoCreateDir 184 * 目录不存在时，是否自动创建(多级)目录 185 * @param autoOverWrite 186 * 目标文件存在时，是否自动覆盖 187 * @return 188 * @throws IOException 189 */ 190 public static boolean write(String fileName, String fileContent, 191 boolean autoCreateDir, boolean autoOverwrite) throws IOException { 192 return write(fileName, fileContent.getBytes(), autoCreateDir, 193 autoOverwrite); 194 } 195 196 /** 197 * 写文件 198 * 199 * @param fileName 200 * 完整文件名(类似：/usr/a/b/c/d.txt) 201 * @param contentBytes 202 * 文件内容的字节数组 203 * @param autoCreateDir 204 * 目录不存在时，是否自动创建(多级)目录 205 * @param autoOverWrite 206 * 目标文件存在时，是否自动覆盖 207 * @return 208 * @throws IOException 209 */ 210 public static boolean write(String fileName, byte[] contentBytes, 211 boolean autoCreateDir, boolean autoOverwrite) throws IOException { 212 boolean result = false; 213 if (autoCreateDir) { 214 createDirs(fileName); 215 } 216 if (autoOverwrite) { 217 delete(fileName); 218 } 219 File f = new File(fileName); 220 FileOutputStream fs = new FileOutputStream(f); 221 fs.write(contentBytes); 222 fs.flush(); 223 fs.close(); 224 result = true; 225 return result; 226 } 227 228 /** 229 * 追加内容到指定文件 230 * 231 * @param fileName 232 * @param fileContent 233 * @return 234 * @throws IOException 235 */ 236 public static boolean append(String fileName, String fileContent) 237 throws IOException { 238 boolean result = false; 239 File f = new File(fileName); 240 if (f.exists()) { 241 RandomAccessFile rFile = new RandomAccessFile(f, "rw"); 242 byte[] b = fileContent.getBytes(); 243 long originLen = f.length(); 244 rFile.setLength(originLen + b.length); 245 rFile.seek(originLen); 246 rFile.write(b); 247 rFile.close(); 248 } 249 result = true; 250 return result; 251 } 252 253 /** 254 * 拆分文件 255 * 256 * @param fileName 257 * 待拆分的完整文件名 258 * @param byteSize 259 * 按多少字节大小拆分 260 * @return 拆分后的文件名列表 261 * @throws IOException 262 */ 263 public List<String> splitBySize(String fileName, int byteSize) 264 throws IOException { 265 List<String> parts = new ArrayList<String>(); 266 File file = new File(fileName); 267 int count = (int) Math.ceil(file.length() / (double) byteSize); 268 int countLen = (count + "").length(); 269 ThreadPoolExecutor threadPool = new ThreadPoolExecutor(count, 270 count * 3, 1, TimeUnit.SECONDS, 271 new ArrayBlockingQueue<Runnable>(count * 2)); 272 273 for (int i = 0; i < count; i++) { 274 String partFileName = file.getPath() + "." 275 + leftPad((i + 1) + "", countLen, '0') + ".part"; 276 threadPool.execute(new SplitRunnable(byteSize, i * byteSize, 277 partFileName, file)); 278 parts.add(partFileName); 279 } 280 return parts; 281 } 282 283 /** 284 * 合并文件 285 * 286 * @param dirPath 287 * 拆分文件所在目录名 288 * @param partFileSuffix 289 * 拆分文件后缀名 290 * @param partFileSize 291 * 拆分文件的字节数大小 292 * @param mergeFileName 293 * 合并后的文件名 294 * @throws IOException 295 */ 296 public void mergePartFiles(String dirPath, String partFileSuffix, 297 int partFileSize, String mergeFileName) throws IOException { 298 ArrayList<File> partFiles = TestExcel_FileUtil.getDirFiles(dirPath, 299 partFileSuffix); 300 Collections.sort(partFiles, new FileComparator()); 301 302 RandomAccessFile randomAccessFile = new RandomAccessFile(mergeFileName, 303 "rw"); 304 randomAccessFile.setLength(partFileSize * (partFiles.size() - 1) 305 + partFiles.get(partFiles.size() - 1).length()); 306 randomAccessFile.close(); 307 308 ThreadPoolExecutor threadPool = new ThreadPoolExecutor( 309 partFiles.size(), partFiles.size() * 3, 1, TimeUnit.SECONDS, 310 new ArrayBlockingQueue<Runnable>(partFiles.size() * 2)); 311 312 for (int i = 0; i < partFiles.size(); i++) { 313 threadPool.execute(new MergeRunnable(i * partFileSize, 314 mergeFileName, partFiles.get(i))); 315 } 316 317 } 318 319 /** 320 * 根据文件名，比较文件 321 * 322 * @author yjmyzz@126.com 323 * 324 */ 325 private class FileComparator implements Comparator<File> { 326 public int compare(File o1, File o2) { 327 return o1.getName().compareToIgnoreCase(o2.getName()); 328 } 329 } 330 331 /** 332 * 创建(多级)目录 333 * 334 * @param filePath 335 * 完整的文件名(类似：/usr/a/b/c/d.xml) 336 */ 337 public static void createDirs(String filePath) { 338 File file = new File(filePath); 339 File parent = file.getParentFile(); 340 if (parent != null && !parent.exists()) { 341 parent.mkdirs(); 342 } 343 344 } 345 346 /** 347 * 分割处理Runnable 348 * 349 * @author yjmyzz@126.com 350 * 351 */ 352 private class SplitRunnable implements Runnable { 353 int byteSize; 354 String partFileName; 355 File originFile; 356 int startPos; 357 358 public SplitRunnable(int byteSize, int startPos, String partFileName, 359 File originFile) { 360 this.startPos = startPos; 361 this.byteSize = byteSize; 362 this.partFileName = partFileName; 363 this.originFile = originFile; 364 } 365 366 public void run() { 367 RandomAccessFile rFile; 368 OutputStream os; 369 try { 370 rFile = new RandomAccessFile(originFile, "r"); 371 byte[] b = new byte[byteSize]; 372 rFile.seek(startPos);// 移动指针到每“段”开头 373 int s = rFile.read(b); 374 os = new FileOutputStream(partFileName); 375 os.write(b, 0, s); 376 os.flush(); 377 os.close(); 378 } catch (IOException e) { 379 e.printStackTrace(); 380 } 381 } 382 } 383 384 /** 385 * 合并处理Runnable 386 * 387 * @author yjmyzz@126.com 388 * 389 */ 390 private class MergeRunnable implements Runnable { 391 long startPos; 392 String mergeFileName; 393 File partFile; 394 395 public MergeRunnable(long startPos, String mergeFileName, File partFile) { 396 this.startPos = startPos; 397 this.mergeFileName = mergeFileName; 398 this.partFile = partFile; 399 } 400 401 public void run() { 402 RandomAccessFile rFile; 403 try { 404 rFile = new RandomAccessFile(mergeFileName, "rw"); 405 rFile.seek(startPos); 406 FileInputStream fs = new FileInputStream(partFile); 407 byte[] b = new byte[fs.available()]; 408 fs.read(b); 409 fs.close(); 410 rFile.write(b); 411 rFile.close(); 412 } catch (IOException e) { 413 e.printStackTrace(); 414 } 415 } 416 } 417 418 }
反射类：
复制代码
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
3961 package com.centit.njld.commons.testexcel; 2 3 import java.lang.reflect.Field; 4 import java.lang.reflect.InvocationTargetException; 5 import java.lang.reflect.Method; 6 import java.lang.reflect.Modifier; 7 import java.lang.reflect.ParameterizedType; 8 import java.lang.reflect.Type; 9 import java.util.ArrayList; 10 import java.util.Collection; 11 import java.util.Date; 12 import java.util.List; 13 14 import org.apache.commons.beanutils.BeanUtils; 15 import org.apache.commons.beanutils.ConvertUtils; 16 import org.apache.commons.beanutils.PropertyUtils; 17 import org.apache.commons.beanutils.locale.converters.DateLocaleConverter; 18 import org.apache.commons.lang.StringUtils; 19 import org.apache.commons.logging.Log; 20 import org.apache.commons.logging.LogFactory; 21 import org.springframework.util.Assert; 22 23 /** 24 * 反射工具类. 25 * 26 * 提供访问私有变量,获取泛型类型Class, 提取集合中元素的属性, 转换字符串到对象等Util函数. 27 * 28 */ 29 public class TestExcel_Reflection{ 30 31 private static Log logger = LogFactory.getLog(TestExcel_Reflection.class); 32 33 static{ 34 DateLocaleConverter dc = new DateLocaleConverter(); 35 ConvertUtils.register(dc, Date.class); 36 } 37 38 /** 39 * 调用Getter方法. 40 */ 41 public static Object invokeGetterMethod(Object target, String propertyName){ 42 String getterMethodName = "get" + StringUtils.capitalize(propertyName); 43 return invokeMethod(target, getterMethodName, new Class[] {}, 44 new Object[] {}); 45 } 46 47 /** 48 * 调用Setter方法.使用value的Class来查找Setter方法. 49 */ 50 public static void invokeSetterMethod(Object target, String propertyName, 51 Object value){ 52 invokeSetterMethod(target, propertyName, value, null); 53 } 54 55 /** 56 * 调用Setter方法. 57 * 58 * @param propertyType 用于查找Setter方法,为空时使用value的Class替代. 59 */ 60 public static void invokeSetterMethod(Object target, String propertyName, 61 Object value, Class<?> propertyType){ 62 Class<?> type = propertyType != null ? propertyType : value.getClass(); 63 String setterMethodName = "set" + StringUtils.capitalize(propertyName); 64 invokeMethod(target, setterMethodName, new Class[] { type }, 65 new Object[] { value }); 66 } 67 68 /** 69 * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数. 70 */ 71 public static Object getFieldValue(final Object object, 72 final String fieldName){ 73 Field field = getDeclaredField(object, fieldName); 74 75 if (field == null){ 76 throw new IllegalArgumentException("Could not find field [" 77 + fieldName + "] on target [" + object + "]"); 78 } 79 80 makeAccessible(field); 81 82 Object result = null; 83 try{ 84 result = field.get(object); 85 } 86 catch (IllegalAccessException e){ 87 logger.error("不可能抛出的异常{}" + e.getMessage()); 88 } 89 return result; 90 } 91 92 /** 93 * 直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数. 94 */ 95 public static void setFieldValue(final Object object, 96 final String fieldName, final Object value){ 97 Field field = getDeclaredField(object, fieldName); 98 99 if (field == null){ 100 throw new IllegalArgumentException("Could not find field [" 101 + fieldName + "] on target [" + object + "]"); 102 } 103 104 makeAccessible(field); 105 106 try{ 107 field.set(object, value); 108 } 109 catch (IllegalAccessException e){ 110 logger.error("不可能抛出的异常:{}" + e.getMessage()); 111 } 112 } 113 114 /** 115 * 直接调用对象方法, 无视private/protected修饰符. 116 */ 117 public static Object invokeMethod(final Object object, 118 final String methodName, final Class<?>[] parameterTypes, 119 final Object[] parameters){ 120 Method method = getDeclaredMethod(object, methodName, parameterTypes); 121 if (method == null){ 122 throw new IllegalArgumentException("Could not find method [" 123 + methodName + "] parameterType " + parameterTypes 124 + " on target [" + object + "]"); 125 } 126 127 method.setAccessible(true); 128 129 try{ 130 return method.invoke(object, parameters); 131 } 132 catch (Exception e){ 133 throw convertReflectionExceptionToUnchecked(e); 134 } 135 } 136 137 /** 138 * 循环向上转型, 获取对象的DeclaredField. 139 * 140 * 如向上转型到Object仍无法找到, 返回null. 141 */ 142 protected static Field getDeclaredField(final Object object, 143 final String fieldName){ 144 Assert.notNull(object, "object不能为空"); 145 Assert.hasText(fieldName, "fieldName"); 146 for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass 147 .getSuperclass()){ 148 try{ 149 return superClass.getDeclaredField(fieldName); 150 } 151 catch (NoSuchFieldException e){// NOSONAR 152 // Field不在当前类定义,继续向上转型 153 } 154 } 155 return null; 156 } 157 158 /** 159 * 强行设置Field可访问. 160 */ 161 protected static void makeAccessible(final Field field){ 162 if (!Modifier.isPublic(field.getModifiers()) 163 || !Modifier.isPublic(field.getDeclaringClass().getModifiers())){ 164 field.setAccessible(true); 165 } 166 } 167 168 /** 169 * 循环向上转型, 获取对象的DeclaredMethod. 170 * 171 * 如向上转型到Object仍无法找到, 返回null. 172 */ 173 protected static Method getDeclaredMethod(Object object, String methodName, 174 Class<?>[] parameterTypes){ 175 Assert.notNull(object, "object不能为空"); 176 177 for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass 178 .getSuperclass()){ 179 try{ 180 return superClass.getDeclaredMethod(methodName, parameterTypes); 181 } 182 catch (NoSuchMethodException e){// NOSONAR 183 // Method不在当前类定义,继续向上转型 184 } 185 } 186 return null; 187 } 188 189 /** 190 * 通过反射, 获得Class定义中声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. eg. public UserDao 191 * extends HibernateDao<User> 192 * 193 * @param clazz The class to introspect 194 * @return the first generic declaration, or Object.class if cannot be 195 * determined 196 */ 197 @SuppressWarnings("unchecked") 198 public static <T> Class<T> getSuperClassGenricType(final Class clazz){ 199 return getSuperClassGenricType(clazz, 0); 200 } 201 202 /** 203 * 通过反射, 获得定义Class时声明的父类的泛型参数的类型. 如无法找到, 返回Object.class. 204 * 205 * 如public UserDao extends HibernateDao<User,Long> 206 * 207 * @param clazz clazz The class to introspect 208 * @param index the Index of the generic ddeclaration,start from 0. 209 * @return the index generic declaration, or Object.class if cannot be 210 * determined 211 */ 212 @SuppressWarnings("unchecked") 213 public static Class getSuperClassGenricType(final Class clazz, 214 final int index){ 215 Type genType = clazz.getGenericSuperclass(); 216 217 if (!(genType instanceof ParameterizedType)){ 218 logger.warn(clazz.getSimpleName() 219 + "'s superclass not ParameterizedType"); 220 return Object.class; 221 } 222 223 Type[] params = ((ParameterizedType) genType).getActualTypeArguments(); 224 225 if (index >= params.length || index < 0){ 226 logger.warn("Index: " + index + ", Size of " 227 + clazz.getSimpleName() + "'s Parameterized Type: " 228 + params.length); 229 return Object.class; 230 } 231 if (!(params[index] instanceof Class)){ 232 logger.warn(clazz.getSimpleName() 233 + " not set the actual class on superclass generic parameter"); 234 return Object.class; 235 } 236 237 return (Class) params[index]; 238 } 239 240 /** 241 * 提取集合中的对象的属性(通过getter函数), 组合成List. 242 * 243 * @param collection 来源集合. 244 * @param propertyName 要提取的属性名. 245 */ 246 @SuppressWarnings("unchecked") 247 public static List convertElementPropertyToList( 248 final Collection collection, final String propertyName){ 249 List list = new ArrayList(); 250 251 try{ 252 for (Object obj : collection){ 253 list.add(PropertyUtils.getProperty(obj, propertyName)); 254 } 255 } 256 catch (Exception e){ 257 throw convertReflectionExceptionToUnchecked(e); 258 } 259 260 return list; 261 } 262 263 /** 264 * 提取集合中的对象的属性(通过getter函数), 组合成由分割符分隔的字符串. 265 * 266 * @param collection 来源集合. 267 * @param propertyName 要提取的属性名. 268 * @param separator 分隔符. 269 */ 270 @SuppressWarnings("unchecked") 271 public static String convertElementPropertyToString( 272 final Collection collection, final String propertyName, 273 final String separator){ 274 List list = convertElementPropertyToList(collection, propertyName); 275 return StringUtils.join(list, separator); 276 } 277 278 /** 279 * 转换字符串到相应类型. 280 * 281 * @param value 待转换的字符串 282 * @param toType 转换目标类型 283 */ 284 @SuppressWarnings("unchecked") 285 public static <T> T convertStringToObject(String value, Class<T> toType){ 286 try{ 287 return (T) ConvertUtils.convert(value, toType); 288 } 289 catch (Exception e){ 290 throw convertReflectionExceptionToUnchecked(e); 291 } 292 } 293 294 /** 295 * 将反射时的checked exception转换为unchecked exception. 296 */ 297 public static RuntimeException convertReflectionExceptionToUnchecked( 298 Exception e){ 299 return convertReflectionExceptionToUnchecked(null, e); 300 } 301 302 public static RuntimeException convertReflectionExceptionToUnchecked( 303 String desc, Exception e){ 304 desc = (desc == null) ? "Unexpected Checked Exception." : desc; 305 if (e instanceof IllegalAccessException 306 || e instanceof IllegalArgumentException 307 || e instanceof NoSuchMethodException){ 308 return new IllegalArgumentException(desc, e); 309 } 310 else if (e instanceof InvocationTargetException){ 311 return new RuntimeException(desc, ((InvocationTargetException) e) 312 .getTargetException()); 313 } 314 else if (e instanceof RuntimeException){ 315 return (RuntimeException) e; 316 } 317 return new RuntimeException(desc, e); 318 } 319 320 public static final <T> T getNewInstance(Class<T> cls) 321 { 322 try{ 323 return cls.newInstance(); 324 } 325 catch (InstantiationException e){ 326 e.printStackTrace(); 327 } 328 catch (IllegalAccessException e){ 329 e.printStackTrace(); 330 } 331 return null; 332 } 333 334 /** 335 * 拷贝 source 指定的porperties 属性 到 dest中 336 * 337 * @return void 338 * @throws InvocationTargetException 339 * @throws IllegalAccessException 340 */ 341 public static void copyPorperties(Object dest, Object source, 342 String[] porperties) throws InvocationTargetException, 343 IllegalAccessException{ 344 for (String por : porperties){ 345 Object srcObj = invokeGetterMethod(source, por); 346 if (srcObj != null){ 347 try{ 348 BeanUtils.setProperty(dest, por, srcObj); 349 } 350 catch (IllegalArgumentException e){ 351 e.printStackTrace(); 352 } 353 catch (IllegalAccessException e){ 354 throw e; 355 } 356 catch (InvocationTargetException e){ 357 throw e; 358 } 359 } 360 } 361 } 362 363 /** 364 * 两者属性名一致时，拷贝source里的属性到dest里 365 * 366 * @return void 367 * @throws IllegalAccessException 368 * @throws InvocationTargetException 369 */ 370 @SuppressWarnings("unchecked") 371 public static void copyPorperties(Object dest, Object source) 372 throws IllegalAccessException, InvocationTargetException{ 373 Class srcCla = source.getClass(); 374 Field[] fsF = srcCla.getDeclaredFields(); 375 376 for (Field s : fsF){ 377 String name = s.getName(); 378 Object srcObj = invokeGetterMethod(source, name); 379 try{ 380 BeanUtils.setProperty(dest, name, srcObj); 381 } 382 catch (IllegalArgumentException e){ 383 e.printStackTrace(); 384 } 385 catch (IllegalAccessException e){ 386 throw e; 387 } 388 catch (InvocationTargetException e){ 389 throw e; 390 } 391 } 392 // BeanUtils.copyProperties(dest, orig); 393 } 394 395 396 }
测试数据模型类：
复制代码
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
681 package com.centit.njld.commons.testexcel; 2 public class TestExcel_Model { 3 4 public TestExcel_Model() { 5 super(); 6 } 7 8 public TestExcel_Model(String unit, String mtName, int jhsl, int qlsl, 9 int xhsl) { 10 super(); 11 this.unit = unit; 12 this.mtName = mtName; 13 this.jhsl = jhsl; 14 this.qlsl = qlsl; 15 this.xhsl = xhsl; 16 } 17 18 private String unit; 19 20 private String mtName; 21 22 private int jhsl; 23 24 private int qlsl; 25 26 private int xhsl; 27 28 public String getUnit() { 29 return unit; 30 } 31 32 public void setUnit(String unit) { 33 this.unit = unit; 34 } 35 36 public String getMtName() { 37 return mtName; 38 } 39 40 public void setMtName(String mtName) { 41 this.mtName = mtName; 42 } 43 44 public int getJhsl() { 45 return jhsl; 46 } 47 48 public void setJhsl(int jhsl) { 49 this.jhsl = jhsl; 50 } 51 52 public int getQlsl() { 53 return qlsl; 54 } 55 56 public void setQlsl(int qlsl) { 57 this.qlsl = qlsl; 58 } 59 60 public int getXhsl() { 61 return xhsl; 62 } 63 64 public void setXhsl(int xhsl) { 65 this.xhsl = xhsl; 66 } 67 68 }
测试方法
复制代码
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
871 package com.centit.njld.commons.testexcel; 2 3 import java.util.ArrayList; 4 import java.util.LinkedHashMap; 5 import java.util.List; 6 import java.util.Random; 7 8 import com.centit.njld.commons.testexcel.TestExcel_Export.ExcelExportData; 9 10 public class TestExcel_Main { 11 12 private static List<TestExcel_Model> getData1() { 13 List<TestExcel_Model> data = new ArrayList<TestExcel_Model>(); 14 Random rand = new Random(); 15 data.add(new TestExcel_Model("城南维护所", "a", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 16 data.add(new TestExcel_Model("城南维护所", "a", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 17 data.add(new TestExcel_Model("城南维护所", "a", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 18 data.add(new TestExcel_Model("城南维护所", "d", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 19 data.add(new TestExcel_Model("城南维护所", "d", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 20 data.add(new TestExcel_Model("城南维护所", "f", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 21 data.add(new TestExcel_Model("城南维护所", "g", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 22 data.add(new TestExcel_Model("城南维护所", "---以上是城南所---", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 23 24 data.add(new TestExcel_Model("什么鬼", "呵呵呵", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 25 data.add(new TestExcel_Model("什么鬼", "呵呵呵", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 26 data.add(new TestExcel_Model("什么鬼", "哈哈哈", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 27 data.add(new TestExcel_Model("什么鬼", "噗噗噗", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 28 data.add(new TestExcel_Model("什么鬼", "噗噗噗", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 29 data.add(new TestExcel_Model("什么鬼", "---以上是什么鬼---", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 30 31 data.add(new TestExcel_Model("000", "asasasa", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 32 data.add(new TestExcel_Model("000", "dfdfdfd", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 33 data.add(new TestExcel_Model("000", "gggggg", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 34 data.add(new TestExcel_Model("000", "vvvvvv", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 35 data.add(new TestExcel_Model("000", "888888", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 36 data.add(new TestExcel_Model("000", "---以上是000---", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 37 38 data.add(new TestExcel_Model("亮化所", "11111", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 39 data.add(new TestExcel_Model("亮化所", "11111", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 40 data.add(new TestExcel_Model("亮化所", "11111", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 41 data.add(new TestExcel_Model("亮化所", "---以上是亮化所---", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 42 43 return data; 44 } 45 46 private static List<TestExcel_Model> getData2() { 47 List<TestExcel_Model> data = new ArrayList<TestExcel_Model>(); 48 Random rand = new Random(); 49 data.add(new TestExcel_Model("城南维护所", "111111", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 50 data.add(new TestExcel_Model("城南维护所", "111111", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 51 data.add(new TestExcel_Model("城南维护所", "333333", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 52 data.add(new TestExcel_Model("城南维护所", "---以上是城南--",rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 53 54 data.add(new TestExcel_Model("亮化所", "---以上是亮化--", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 55 data.add(new TestExcel_Model("亮化所", "150W", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 56 data.add(new TestExcel_Model("亮化所", "22.2W", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 57 data.add(new TestExcel_Model("亮化所", "95W", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 58 data.add(new TestExcel_Model("亮化所", "95W", rand.nextInt(100),rand.nextInt(100),rand.nextInt(100))); 59 return data; 60 } 61 62 public static void main(String[] args) throws Exception { 63 List<String[]> columNames = new ArrayList<String[]>(); 64 columNames.add(new String[] { "部门", "材料名称","计划数量","请领数量","消耗数量" }); 65 columNames.add(new String[] { "部门", "材料名称","计划数量","请领数量","消耗数量" }); 66 List<String[]> fieldNames = new ArrayList<String[]>(); 67 fieldNames.add(new String[] { "unit", "mtName","jhsl","qlsl","xhsl" }); 68 fieldNames.add(new String[] { "unit", "mtName","jhsl","qlsl","xhsl" }); 69 List<String[]> groupNames = new ArrayList<String[]>(); 70 groupNames.add(new String[] { "unit", "mtName" }); 71 groupNames.add(new String[] { "unit", "mtName" }); 72 73 LinkedHashMap<String, List<?>> dataMap = new LinkedHashMap<String, List<?>>(); 74 dataMap.put("1-sheet", getData1()); 75 dataMap.put("2-sheet", getData2()); 76 77 ExcelExportData setInfo = new ExcelExportData(); 78 setInfo.setDataMap(dataMap); 79 setInfo.setFieldNames(fieldNames); 80 setInfo.setTitles(new String[] { "Name-1", "Name-2" }); 81 setInfo.setGroupColumn(groupNames); 82 setInfo.setColumnNames(columNames); 83 84 System.out.println(TestExcel_Export.export2File(setInfo, "D:/ywwwwwwwwwwwwwwwww.xls")); 85 } 86 87 }
最重要部分要看
转载于:https://www.cnblogs.com/zhengbn/p/5627081.html
最后
以上就是迷人黑猫最近收集整理的关于excel poi 文件导出，支持多sheet、多列自动合并。的全部内容，更多相关excel内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复