Java POI 導出EXCEL經典實現 Java導出Excel

2019-11-14 23:58:53

字體：大中小

來源：轉載

供稿：網友

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的實用類（所謂實用，是指基本不用怎么修改就可以在實際項目中直接使用的！）。我一直強調做類也好，做方法也好，一定要通用性和靈活性強。下面這個類就算基本貫徹了我的這種思想。那么，熟悉許老師風格的人應該知道，這時候該要甩出一長串代碼了。沒錯，大伙請看：

 1 import java.util.Date; 2  3 public class Student 4 { 5     PRivate long id; 6     private String name; 7     private int age; 8     private boolean sex; 9     private Date birthday;10 11     public Student()12     {13     }14 15     public Student(long id, String name, int age, boolean sex, Date birthday)16     {17         this.id = id;18         this.name = name;19         this.age = age;20         this.sex = sex;21         this.birthday = birthday;22     }23 24     public long getId()25     {26         return id;27     }28 29     public void setId(long id)30     {31         this.id = id;32     }33 34     public String getName()35     {36         return name;37     }38 39     public void setName(String name)40     {41         this.name = name;42     }43 44     public int getAge()45     {46         return age;47     }48 49     public void setAge(int age)50     {51         this.age = age;52     }53 54     public boolean getSex()55     {56         return sex;57     }58 59     public void setSex(boolean sex)60     {61         this.sex = sex;62     }63 64     public Date getBirthday()65     {66         return birthday;67     }68 69     public void setBirthday(Date birthday)70     {71         this.birthday = birthday;72     }73 74 }

 1 public class Book 2 { 3     private int bookId; 4     private String name; 5     private String author; 6     private float price; 7     private String isbn; 8     private String pubName; 9     private byte[] preface;10 11     public Book()12     {13     }14 15     public Book(int bookId, String name, String author, float price,16             String isbn, String pubName, byte[] preface)17     {18         this.bookId = bookId;19         this.name = name;20         this.author = author;21         this.price = price;22         this.isbn = isbn;23         this.pubName = pubName;24         this.preface = preface;25     }26 27     public int getBookId()28     {29         return bookId;30     }31 32     public void setBookId(int bookId)33     {34         this.bookId = bookId;35     }36 37     public String getName()38     {39         return name;40     }41 42     public void setName(String name)43     {44         this.name = name;45     }46 47     public String getAuthor()48     {49         return author;50     }51 52     public void setAuthor(String author)53     {54         this.author = author;55     }56 57     public float getPrice()58     {59         return price;60     }61 62     public void setPrice(float price)63     {64         this.price = price;65     }66 67     public String getIsbn()68     {69         return isbn;70     }71 72     public void setIsbn(String isbn)73     {74         this.isbn = isbn;75     }76 77     public String getPubName()78     {79         return pubName;80     }81 82     public void setPubName(String pubName)83     {84         this.pubName = pubName;85     }86 87     public byte[] getPreface()88     {89         return preface;90     }91 92     public void setPreface(byte[] preface)93     {94         this.preface = preface;95     }96 }

上面這兩個類一目了然，就是兩個簡單的javabean風格的類。再看下面真正的重點類：

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操作數據庫動態獲取。恩，類和方法的通用性和靈活性開始有點感覺了。好啦，祝您學習愉快！

簡單版無excel格式樣式設置及excel下載

  1 package com.howbuy.uaa.utils;  2   3 import java.io.BufferedInputStream;  4 import java.io.BufferedOutputStream;  5 import java.io.File;  6 import java.io.FileInputStream;  7 import java.io.IOException;  8 import java.io.InputStream;  9 import java.io.OutputStream; 10 import java.lang.reflect.Field; 11 import java.lang.reflect.InvocationTargetException; 12 import java.lang.reflect.Method; 13 import java.text.SimpleDateFormat; 14 import java.util.Collection; 15 import java.util.Date; 16 import java.util.Iterator; 17 import java.util.regex.Matcher; 18 import java.util.regex.Pattern; 19  20 import javax.servlet.http.HttpServletResponse; 21  22 import org.apache.poi.hssf.usermodel.HSSFCell; 23 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 24 import org.apache.poi.hssf.usermodel.HSSFFont; 25 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 26 import org.apache.poi.hssf.usermodel.HSSFRow; 27 import org.apache.poi.hssf.usermodel.HSSFSheet; 28 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 29  30  31 public class ExportExcel<T> { 32     public void exportExcel(String title,Collection<T> dataset, OutputStream out) { 33         exportExcel(title, null, dataset, out, "yyyy-MM-dd"); 34     } 35  36     public void exportExcel(String title,String[] headers, Collection<T> dataset, 37             OutputStream out) { 38         exportExcel(title, headers, dataset, out, "yyyy-MM-dd"); 39     } 40      41     @SuppressWarnings("unchecked") 42     public void exportExcel(String title, String[] headers,Collection<T> dataset, OutputStream out, String pattern) { 43         // 聲明一個工作薄 44         HSSFWorkbook workbook = new HSSFWorkbook(); 45         // 生成一個表格 46         HSSFSheet sheet = workbook.createSheet(title); 47         // 設置表格默認列寬度為15個字節 48         sheet.setDefaultColumnWidth((short) 15); 49         // 生成一個樣式 50         HSSFCellStyle style = workbook.createCellStyle(); 51         // 生成一個字體 52         HSSFFont font = workbook.createFont(); 53         font.setFontHeightInPoints((short) 12); 54         // 把字體應用到當前的樣式 55         style.setFont(font); 56         // 產生表格標題行 57         HSSFRow row = sheet.createRow(0); 58         for (short i = 0; i < headers.length; i++) { 59             HSSFCell cell = row.createCell(i); 60             cell.setCellStyle(style); 61             HSSFRichTextString text = new HSSFRichTextString(headers[i]); 62             cell.setCellValue(text); 63         } 64         // 遍歷集合數據，產生數據行 65         Iterator<T> it = dataset.iterator(); 66         int index = 0; 67         while (it.hasNext()) { 68             index++; 69             row = sheet.createRow(index); 70             T t = (T) it.next(); 71             // 利用反射，根據javabean屬性的先后順序，動態調用getXxx()方法得到屬性值 72             Field[] fields = t.getClass().getDeclaredFields(); 73             for (short i = 0; i < fields.length; i++) { 74                 HSSFCell cell = row.createCell(i); 75                 cell.setCellStyle(style); 76                 Field field = fields[i]; 77                 String fieldName = field.getName(); 78                 String getMethodName = "get" 79                         + fieldName.substring(0, 1).toUpperCase() 80                         + fieldName.substring(1); 81                 try { 82                     Class tCls = t.getClass(); 83                     Method getMethod = tCls.getMethod(getMethodName, 84                             new Class[] {}); 85                     Object value = getMethod.invoke(t, new Object[] {}); 86                     // 判斷值的類型后進行強制類型轉換 87                     String textValue = null; 88                      if (value instanceof Integer) { 89                          int intValue = (Integer) value; 90                          cell.setCellValue(intValue); 91                      } 92                      else if (value instanceof Long) { 93                          long longValue = (Long) value; 94                          cell.setCellValue(longValue); 95                      } 96                      else if (value instanceof Boolean) { 97                         boolean bValue = (Boolean) value; 98                         textValue = "1"; 99                         if (!bValue) {100                             textValue = "0";101                         }102                     } else if (value instanceof Date) {103                         Date date = (Date) value;104                         SimpleDateFormat sdf = new SimpleDateFormat(pattern);105                         textValue = sdf.format(date);106                     } else {107                         // 其它數據類型都當作字符串簡單處理108                         if(value == null)109                         {110                             textValue = "";111                         }112                         else {113                             textValue = value.toString();114                         }115                             116                     }117                     118                     if (textValue != null) {119                         Pattern p = Pattern.compile("^//d+(//.//d+)?$");120                         Matcher matcher = p.matcher(textValue);121                         if (matcher.matches()) {122                             // 是數字當作double處理123                             cell.setCellValue(Double.parseDouble(textValue));124                         } else {125                             HSSFRichTextString richString = new HSSFRichTextString(126                                     textValue);127                             richString.applyFont(font);128                             cell.setCellValue(richString);129                         }130                     }131                 } catch (SecurityException e) {132                     e.printStackTrace();133                 } catch (NoSuchMethodException e) {134                     e.printStackTrace();135                 } catch (IllegalArgumentException e) {136                     e.printStackTrace();137                 } catch (IllegalAccessException e) {138                     e.printStackTrace();139                 } catch (InvocationTargetException e) {140                     e.printStackTrace();141                 } finally {142                     // 清理資源143                 }144             }145 146         }147         try {148             workbook.write(out);149         } catch (IOException e) {150             e.printStackTrace();151         }152     }153     154     public void downloadExcel(String path, HttpServletResponse response) {155         try {156             // path是指欲下載的文件的路徑。157             File file = new File(path);158             String name = file.getName();159             // 取得文件名。160             String filename = java.net.URLEncoder.encode(name,"utf-8");161             // 清空response162             response.reset();163             // 設置response的Header164             response.addHeader("Content-Disposition", "attachment;filename="165                     + new String(filename.getBytes()));166             response.addHeader("Content-Length", "" + file.length());167             OutputStream toClient = new BufferedOutputStream(168                     response.getOutputStream());169             response.setContentType("application/ms-excel;charset=gb2312");170             // 以流的形式下載文件。171             InputStream fis = new BufferedInputStream(new FileInputStream(path));172             byte[] buffer = new byte[fis.available()];173             fis.read(buffer);174             fis.close();175             toClient.write(buffer);176             toClient.flush();177             toClient.close();178         } catch (IOException ex) {179             ex.printStackTrace();180         }181     }182 }

上一篇：TortoiseSVN使用簡介(轉)

下一篇：java發送http的get、post請求