Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]

http://blog.csdn.net/evangel_z/article/details/7332535

在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下。(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^)

         呵呵,首先我们来导出EXCEL格式的文件吧。现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI!我们先去Apache的大本营下载POI的jar包:http://poi.apache.org/ ,我这里使用的是3.0.2版本。

        将3个jar包导入到classpath下,什么?忘了怎么导包?不会吧!好,我们来写一个导出Excel的实用类(所谓实用,是指基本不用怎么修改就可以在实际项目中直接使用的!)。我一直强调做类也好,做方法也好,一定要通用性和灵活性强。下面这个类就算基本贯彻了我的这种思想。那么,熟悉许老师风格的人应该知道,这时候该要甩出一长串代码了。没错,大伙请看:

Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]
import java.util.Date;  
  
public class Student  
{  
    private long id;  
    private String name;  
    private int age;  
    private boolean sex;  
    private Date birthday;  
  
    public Student()  
    {  
    }  
  
    public Student(long id, String name, int age, boolean sex, Date birthday)  
    {  
        this.id = id;  
        this.name = name;  
        this.age = age;  
        this.sex = sex;  
        this.birthday = birthday;  
    }  
  
    public long getId()  
    {  
        return id;  
    }  
  
    public void setId(long id)  
    {  
        this.id = id;  
    }  
  
    public String getName()  
    {  
        return name;  
    }  
  
    public void setName(String name)  
    {  
        this.name = name;  
    }  
  
    public int getAge()  
    {  
        return age;  
    }  
  
    public void setAge(int age)  
    {  
        this.age = age;  
    }  
  
    public boolean getSex()  
    {  
        return sex;  
    }  
  
    public void setSex(boolean sex)  
    {  
        this.sex = sex;  
    }  
  
    public Date getBirthday()  
    {  
        return birthday;  
    }  
  
    public void setBirthday(Date birthday)  
    {  
        this.birthday = birthday;  
    }  
  
}  
import java.util.Date;

public class Student
{
    private long id;
    private String name;
    private int age;
    private boolean sex;
    private Date birthday;

    public Student()
    {
    }

    public Student(long id, String name, int age, boolean sex, Date birthday)
    {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.birthday = birthday;
    }

    public long getId()
    {
        return id;
    }

    public void setId(long id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public int getAge()
    {
        return age;
    }

    public void setAge(int age)
    {
        this.age = age;
    }

    public boolean getSex()
    {
        return sex;
    }

    public void setSex(boolean sex)
    {
        this.sex = sex;
    }

    public Date getBirthday()
    {
        return birthday;
    }

    public void setBirthday(Date birthday)
    {
        this.birthday = birthday;
    }

}[java] view plaincopyprint?
public class Book  
{  
    private int bookId;  
    private String name;  
    private String author;  
    private float price;  
    private String isbn;  
    private String pubName;  
    private byte[] preface;  
  
    public Book()  
    {  
    }  
  
    public Book(int bookId, String name, String author, float price,  
            String isbn, String pubName, byte[] preface)  
    {  
        this.bookId = bookId;  
        this.name = name;  
        this.author = author;  
        this.price = price;  
        this.isbn = isbn;  
        this.pubName = pubName;  
        this.preface = preface;  
    }  
  
    public int getBookId()  
    {  
        return bookId;  
    }  
  
    public void setBookId(int bookId)  
    {  
        this.bookId = bookId;  
    }  
  
    public String getName()  
    {  
        return name;  
    }  
  
    public void setName(String name)  
    {  
        this.name = name;  
    }  
  
    public String getAuthor()  
    {  
        return author;  
    }  
  
    public void setAuthor(String author)  
    {  
        this.author = author;  
    }  
  
    public float getPrice()  
    {  
        return price;  
    }  
  
    public void setPrice(float price)  
    {  
        this.price = price;  
    }  
  
    public String getIsbn()  
    {  
        return isbn;  
    }  
  
    public void setIsbn(String isbn)  
    {  
        this.isbn = isbn;  
    }  
  
    public String getPubName()  
    {  
        return pubName;  
    }  
  
    public void setPubName(String pubName)  
    {  
        this.pubName = pubName;  
    }  
  
    public byte[] getPreface()  
    {  
        return preface;  
    }  
  
    public void setPreface(byte[] preface)  
    {  
        this.preface = preface;  
    }  
}  
public class Book
{
    private int bookId;
    private String name;
    private String author;
    private float price;
    private String isbn;
    private String pubName;
    private byte[] preface;

    public Book()
    {
    }

    public Book(int bookId, String name, String author, float price,
            String isbn, String pubName, byte[] preface)
    {
        this.bookId = bookId;
        this.name = name;
        this.author = author;
        this.price = price;
        this.isbn = isbn;
        this.pubName = pubName;
        this.preface = preface;
    }

    public int getBookId()
    {
        return bookId;
    }

    public void setBookId(int bookId)
    {
        this.bookId = bookId;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public String getAuthor()
    {
        return author;
    }

    public void setAuthor(String author)
    {
        this.author = author;
    }

    public float getPrice()
    {
        return price;
    }

    public void setPrice(float price)
    {
        this.price = price;
    }

    public String getIsbn()
    {
        return isbn;
    }

    public void setIsbn(String isbn)
    {
        this.isbn = isbn;
    }

    public String getPubName()
    {
        return pubName;
    }

    public void setPubName(String pubName)
    {
        this.pubName = pubName;
    }

    public byte[] getPreface()
    {
        return preface;
    }

    public void setPreface(byte[] preface)
    {
        this.preface = preface;
    }
}
Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]

 

 

上面这两个类一目了然,就是两个简单的javabean风格的类。再看下面真正的重点类:

 

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
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
import java.io.BufferedInputStream; 
import java.io.FileInputStream; 
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.InvocationTargetException; 
import java.lang.reflect.Method; 
import java.text.SimpleDateFormat; 
import java.util.ArrayList; 
import java.util.Collection; 
import java.util.Date; 
import java.util.Iterator; 
import java.util.List; 
import java.util.regex.Matcher; 
import java.util.regex.Pattern; 
   
import javax.swing.JOptionPane; 
   
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFCellStyle; 
import org.apache.poi.hssf.usermodel.HSSFClientAnchor; 
import org.apache.poi.hssf.usermodel.HSSFComment; 
import org.apache.poi.hssf.usermodel.HSSFFont; 
import org.apache.poi.hssf.usermodel.HSSFPatriarch; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFSheet; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.hssf.util.HSSFColor; 
   
/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息,注明出处!
 
 * @author leno
 * @version v1.0
 * @param <T>
 *            应用泛型,代表任意一个符合javabean风格的类
 *            注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */ 
public class ExportExcel<T> 
    public void exportExcel(Collection<T> dataset, OutputStream out) 
    
        exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd"); 
    
   
    public void exportExcel(String[] headers, Collection<T> dataset, 
            OutputStream out) 
    
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd"); 
    
   
    public void exportExcel(String[] headers, Collection<T> dataset, 
            OutputStream out, String pattern) 
    
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern); 
    
   
    /**
     * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
     */ 
    @SuppressWarnings("unchecked"
    public void exportExcel(String title, String[] headers, 
            Collection<T> dataset, OutputStream out, String pattern) 
    
        // 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook(); 
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title); 
        // 设置表格默认列宽度为15个字节  
        sheet.setDefaultColumnWidth((short) 15); 
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle(); 
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); 
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
        style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        // 生成一个字体  
        HSSFFont font = workbook.createFont(); 
        font.setColor(HSSFColor.VIOLET.index); 
        font.setFontHeightInPoints((short) 12); 
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
        // 把字体应用到当前的样式  
        style.setFont(font); 
        // 生成并设置另一个样式  
        HSSFCellStyle style2 = workbook.createCellStyle(); 
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN); 
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN); 
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        // 生成另一个字体  
        HSSFFont font2 = workbook.createFont(); 
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 
        // 把字体应用到当前的样式  
        style2.setFont(font2); 
   
        // 声明一个画图的*管理器  
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
        // 定义注释的大小和位置,详见文档  
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0
                0, 0, 0, (short) 4, 2, (short) 6, 5)); 
        // 设置注释内容  
        comment.setString(new HSSFRichTextString("可以在POI中添加注释!")); 
        // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.  
        comment.setAuthor("leno"); 
   
        // 产生表格标题行  
        HSSFRow row = sheet.createRow(0); 
        for (short i = 0; i < headers.length; i++) 
        
            HSSFCell cell = row.createCell(i); 
            cell.setCellStyle(style); 
            HSSFRichTextString text = new HSSFRichTextString(headers[i]); 
            cell.setCellValue(text); 
        
   
        // 遍历集合数据,产生数据行  
        Iterator<T> it = dataset.iterator(); 
        int index = 0
        while (it.hasNext()) 
        
            index++; 
            row = sheet.createRow(index); 
            T t = (T) it.next(); 
            // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值  
            Field[] fields = t.getClass().getDeclaredFields(); 
            for (short i = 0; i < fields.length; i++) 
            
                HSSFCell cell = row.createCell(i); 
                cell.setCellStyle(style2); 
                Field field = fields[i]; 
                String fieldName = field.getName(); 
                String getMethodName = "get" 
                        + fieldName.substring(0, 1).toUpperCase() 
                        + fieldName.substring(1); 
                try 
                
                    Class tCls = t.getClass(); 
                    Method getMethod = tCls.getMethod(getMethodName, 
                            new Class[] 
                            {}); 
                    Object value = getMethod.invoke(t, new Object[] 
                    {}); 
                    // 判断值的类型后进行强制类型转换  
                    String textValue = null
                    // if (value instanceof Integer) {  
                    // int intValue = (Integer) value;  
                    // cell.setCellValue(intValue);  
                    // } else if (value instanceof Float) {  
                    // float fValue = (Float) value;  
                    // textValue = new HSSFRichTextString(  
                    // String.valueOf(fValue));  
                    // cell.setCellValue(textValue);  
                    // } else if (value instanceof Double) {  
                    // double dValue = (Double) value;  
                    // textValue = new HSSFRichTextString(  
                    // String.valueOf(dValue));  
                    // cell.setCellValue(textValue);  
                    // } else if (value instanceof Long) {  
                    // long longValue = (Long) value;  
                    // cell.setCellValue(longValue);  
                    // }  
                    if (value instanceof Boolean) 
                    
                        boolean bValue = (Boolean) value; 
                        textValue = "男"
                        if (!bValue) 
                        
                            textValue = "女"
                        
                    
                    else if (value instanceof Date) 
                    
                        Date date = (Date) value; 
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern); 
                        textValue = sdf.format(date); 
                    
                    else if (value instanceof byte[]) 
                    
                        // 有图片时,设置行高为60px;  
                        row.setHeightInPoints(60); 
                        // 设置图片所在列宽度为80px,注意这里单位的一个换算  
                        sheet.setColumnWidth(i, (short) (35.7 * 80)); 
                        // sheet.autoSizeColumn(i);  
                        byte[] bsValue = (byte[]) value; 
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0
                                1023, 255, (short) 6, index, (short) 6, index); 
                        anchor.setAnchorType(2); 
                        patriarch.createPicture(anchor, workbook.addPicture( 
                                bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); 
                    
                    else 
                    
                        // 其它数据类型都当作字符串简单处理  
                        textValue = value.toString(); 
                    
                    // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成  
                    if (textValue != null
                    
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$"); 
                        Matcher matcher = p.matcher(textValue); 
                        if (matcher.matches()) 
                        
                            // 是数字当作double处理  
                            cell.setCellValue(Double.parseDouble(textValue)); 
                        
                        else 
                        
                            HSSFRichTextString richString = new HSSFRichTextString( 
                                    textValue); 
                            HSSFFont font3 = workbook.createFont(); 
                            font3.setColor(HSSFColor.BLUE.index); 
                            richString.applyFont(font3); 
                            cell.setCellValue(richString); 
                        
                    
                
                catch (SecurityException e) 
                
                    e.printStackTrace(); 
                
                catch (NoSuchMethodException e) 
                
                    e.printStackTrace(); 
                
                catch (IllegalArgumentException e) 
                
                    e.printStackTrace(); 
                
                catch (IllegalAccessException e) 
                
                    e.printStackTrace(); 
                
                catch (InvocationTargetException e) 
                
                    e.printStackTrace(); 
                
                finally 
                
                    // 清理资源  
                
            
        
        try 
        
            workbook.write(out); 
        
        catch (IOException e) 
        
            e.printStackTrace(); 
        
    
   
    public static void main(String[] args) 
    
        // 测试学生  
        ExportExcel<Student> ex = new ExportExcel<Student>(); 
        String[] headers = 
        { "学号", "姓名", "年龄", "性别", "出生日期" }; 
        List<Student> dataset = new ArrayList<Student>(); 
        dataset.add(new Student(10000001, "张三", 20, true, new Date())); 
        dataset.add(new Student(20000002, "李四", 24, false, new Date())); 
        dataset.add(new Student(30000003, "王五", 22, true, new Date())); 
        // 测试图书  
        ExportExcel<Book> ex2 = new ExportExcel<Book>(); 
        String[] headers2 = 
        { "图书编号", "图书名称", "图书作者", "图书价格", "图书ISBN", "图书出版社", "封面图片" }; 
        List<Book> dataset2 = new ArrayList<Book>(); 
        try 
        
            BufferedInputStream bis = new BufferedInputStream( 
                    new FileInputStream("V://book.bmp")); 
            byte[] buf = new byte[bis.available()]; 
            while ((bis.read(buf)) != -1
            
                //  
            
            dataset2.add(new Book(1, "jsp", "leno", 300.33f, "1234567"
                    "清华出版社", buf)); 
            dataset2.add(new Book(2, "java编程思想", "brucl", 300.33f, "1234567"
                    "阳光出版社", buf)); 
            dataset2.add(new Book(3, "DOM艺术", "lenotang", 300.33f, "1234567"
                    "清华出版社", buf)); 
            dataset2.add(new Book(4, "c++经典", "leno", 400.33f, "1234567"
                    "清华出版社", buf)); 
            dataset2.add(new Book(5, "c#入门", "leno", 300.33f, "1234567"
                    "汤春秀出版社", buf)); 
   
            OutputStream out = new FileOutputStream("E://a.xls"); 
            OutputStream out2 = new FileOutputStream("E://b.xls"); 
            ex.exportExcel(headers, dataset, out); 
            ex2.exportExcel(headers2, dataset2, out2); 
            out.close(); 
            JOptionPane.showMessageDialog(null, "导出成功!"); 
            System.out.println("excel导出成功!"); 
        
        catch (FileNotFoundException e) 
        
            e.printStackTrace(); 
        
        catch (IOException e) 
        
            e.printStackTrace(); 
        
    
import java.io.BufferedInputStream;
import java.io.FileInputStream;
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.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
 
import javax.swing.JOptionPane;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
 
/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息,注明出处!
 *
 * @author leno
 * @version v1.0
 * @param <T>
 *            应用泛型,代表任意一个符合javabean风格的类
 *            注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class ExportExcel<T>
{
    public void exportExcel(Collection<T> dataset, OutputStream out)
    {
        exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
    }
 
    public void exportExcel(String[] headers, Collection<T> dataset,
            OutputStream out)
    {
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
    }
 
    public void exportExcel(String[] headers, Collection<T> dataset,
            OutputStream out, String pattern)
    {
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);
    }
 
    /**
     * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
     */
    @SuppressWarnings("unchecked")
    public void exportExcel(String title, String[] headers,
            Collection<T> dataset, OutputStream out, String pattern)
    {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
 
        // 声明一个画图的*管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
        // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("leno");
 
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++)
        {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
 
        // 遍历集合数据,产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext())
        {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++)
            {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try
                {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName,
                            new Class[]
                            {});
                    Object value = getMethod.invoke(t, new Object[]
                    {});
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // if (value instanceof Integer) {
                    // int intValue = (Integer) value;
                    // cell.setCellValue(intValue);
                    // } else if (value instanceof Float) {
                    // float fValue = (Float) value;
                    // textValue = new HSSFRichTextString(
                    // String.valueOf(fValue));
                    // cell.setCellValue(textValue);
                    // } else if (value instanceof Double) {
                    // double dValue = (Double) value;
                    // textValue = new HSSFRichTextString(
                    // String.valueOf(dValue));
                    // cell.setCellValue(textValue);
                    // } else if (value instanceof Long) {
                    // long longValue = (Long) value;
                    // cell.setCellValue(longValue);
                    // }
                    if (value instanceof Boolean)
                    {
                        boolean bValue = (Boolean) value;
                        textValue = "男";
                        if (!bValue)
                        {
                            textValue = "女";
                        }
                    }
                    else if (value instanceof Date)
                    {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    }
                    else if (value instanceof byte[])
                    {
                        // 有图片时,设置行高为60px;
                        row.setHeightInPoints(60);
                        // 设置图片所在列宽度为80px,注意这里单位的一个换算
                        sheet.setColumnWidth(i, (short) (35.7 * 80));
                        // sheet.autoSizeColumn(i);
                        byte[] bsValue = (byte[]) value;
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                                1023, 255, (short) 6, index, (short) 6, index);
                        anchor.setAnchorType(2);
                        patriarch.createPicture(anchor, workbook.addPicture(
                                bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    else
                    {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }
                    // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
                    if (textValue != null)
                    {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches())
                        {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        }
                        else
                        {
                            HSSFRichTextString richString = new HSSFRichTextString(
                                    textValue);
                            HSSFFont font3 = workbook.createFont();
                            font3.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    }
                }
                catch (SecurityException e)
                {
                    e.printStackTrace();
                }
                catch (NoSuchMethodException e)
                {
                    e.printStackTrace();
                }
                catch (IllegalArgumentException e)
                {
                    e.printStackTrace();
                }
                catch (IllegalAccessException e)
                {
                    e.printStackTrace();
                }
                catch (InvocationTargetException e)
                {
                    e.printStackTrace();
                }
                finally
                {
                    // 清理资源
                }
            }
        }
        try
        {
            workbook.write(out);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
 
    public static void main(String[] args)
    {
        // 测试学生
        ExportExcel<Student> ex = new ExportExcel<Student>();
        String[] headers =
        { "学号", "姓名", "年龄", "性别", "出生日期" };
        List<Student> dataset = new ArrayList<Student>();
        dataset.add(new Student(10000001, "张三", 20, true, new Date()));
        dataset.add(new Student(20000002, "李四", 24, false, new Date()));
        dataset.add(new Student(30000003, "王五", 22, true, new Date()));
        // 测试图书
        ExportExcel<Book> ex2 = new ExportExcel<Book>();
        String[] headers2 =
        { "图书编号", "图书名称", "图书作者", "图书价格", "图书ISBN", "图书出版社", "封面图片" };
        List<Book> dataset2 = new ArrayList<Book>();
        try
        {
            BufferedInputStream bis = new BufferedInputStream(
                    new FileInputStream("V://book.bmp"));
            byte[] buf = new byte[bis.available()];
            while ((bis.read(buf)) != -1)
            {
                //
            }
            dataset2.add(new Book(1, "jsp", "leno", 300.33f, "1234567",
                    "清华出版社", buf));
            dataset2.add(new Book(2, "java编程思想", "brucl", 300.33f, "1234567",
                    "阳光出版社", buf));
            dataset2.add(new Book(3, "DOM艺术", "lenotang", 300.33f, "1234567",
                    "清华出版社", buf));
            dataset2.add(new Book(4, "c++经典", "leno", 400.33f, "1234567",
                    "清华出版社", buf));
            dataset2.add(new Book(5, "c#入门", "leno", 300.33f, "1234567",
                    "汤春秀出版社", buf));
 
            OutputStream out = new FileOutputStream("E://a.xls");
            OutputStream out2 = new FileOutputStream("E://b.xls");
            ex.exportExcel(headers, dataset, out);
            ex2.exportExcel(headers2, dataset2, out2);
            out.close();
            JOptionPane.showMessageDialog(null, "导出成功!");
            System.out.println("excel导出成功!");
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
}

 

 

        写完之后,如果您不是用eclipse工具生成的Servlet,千万别忘了在web.xml上注册这个Servelt。而且同样的,拷贝一张小巧的图书图片命名为book.jpg放置到当前WEB根目录的/WEB-INF/下。部署好web工程,用浏览器访问Servlet看下效果吧!是不是下载成功了。呵呵,您可以将下载到本地的excel报表用打印机打印出来,这样您就大功告成了。完事了我们就思考:我们发现,我们做的方法,不管是本地调用,还是在WEB服务器端用Servlet调用;不管是输出学生列表,还是图书列表信息,代码都几乎一样,而且这些数据我们很容器结合后台的DAO操作数据库动态获取。恩,类和方法的通用性和灵活性开始有点感觉了。好啦,祝您学习愉快!

补充:

Java导出Excel弹出下载框

        将ExportExcel类的main方法改成public void test(),OutputStream out = new FileOutputStream("E://a.xls");这边可以对应Servlet适当改下路径,Servlet代码如下:

 

Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]
public class ExcelServlet extends HttpServlet {  
    public void doGet(HttpServletRequest request, HttpServletResponse response)  
            throws ServletException, IOException {  
        (new ExportExcel()).test();  
        String str = "a.xls";  
        //String path = request.getSession().getServletContext().getRealPath(str);   
        download("E://a.xls", response);  
    }  
    private void download(String path, HttpServletResponse response) {  
        try {  
            // path是指欲下载的文件的路径。   
            File file = new File(path);  
            // 取得文件名。   
            String filename = file.getName();  
            // 以流的形式下载文件。   
            InputStream fis = new BufferedInputStream(new FileInputStream(path));  
            byte[] buffer = new byte[fis.available()];  
            fis.read(buffer);  
            fis.close();  
            // 清空response   
            response.reset();  
            // 设置response的Header   
            response.addHeader("Content-Disposition", "attachment;filename="  
                    + new String(filename.getBytes()));  
            response.addHeader("Content-Length", "" + file.length());  
            OutputStream toClient = new BufferedOutputStream(  
                    response.getOutputStream());  
            response.setContentType("application/vnd.ms-excel;charset=gb2312");  
            toClient.write(buffer);  
            toClient.flush();  
            toClient.close();  
        } catch (IOException ex) {  
            ex.printStackTrace();  
        }  
    }  
}  
public class ExcelServlet extends HttpServlet {
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        (new ExportExcel()).test();
        String str = "a.xls";
        //String path = request.getSession().getServletContext().getRealPath(str);
        download("E://a.xls", response);
    }
    private void download(String path, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(
                    response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}
Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]

 

 

Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转],布布扣,bubuko.com

Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 [转]

上一篇:面向对象——java


下一篇:Python基础测试与笔记(二)