Java和poi導出excel報表

一:poi jar下載地址:點擊打開鏈接:


二:工程截圖:


三:運行效果截圖:


四:源代碼:

Student.java:

package com.poi.bean;import java.util.Date;public class Student {private long id;// 學號private String name;// 姓名private int age;// 年齡private boolean sex;// 性別private Date birthday;// 出生日期public Student() {super();}public Student(long id, String name, int age, boolean sex, Date birthday) {super();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;}}

Book.java

package com.poi.bean;public class Book {private int bookId;// 圖書編號private String name;// 圖書名稱private String author;// 圖書作者private float price;// 圖書價格private String isbn;// 圖書ISBNprivate String pubName;// 圖書出版社private byte[] preface;// 封面圖片public Book() {super();}public Book(int bookId, String name, String author, float price,String isbn, String pubName, byte[] preface) {super();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;}}

ExportExcel.java

package com.poi.util;import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.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;import com.poi.bean.Student;/*** 利用開源組件POI3.0.2動態導出EXCEL文檔 轉載時請保留以下信息,注明出處!* * @author zhaoxinguo* @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"*/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("zhaoxinguo");//產生表格標題行HSSFRow row = sheet.createRow(0);for (int 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 (int 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 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));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 (Exception e) {e.printStackTrace();} finally{//清理資源}}}try {workbook.write(out);} catch (IOException e) {e.printStackTrace();}}public static void main(String[] args) throws Exception{//測試學生ExportExcel<Student> exportExcel = 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()));OutputStream out = new FileOutputStream("E://Student.xls");exportExcel.exportExcel(headers, dataset, out);out.close();JOptionPane.showMessageDialog(null, "導出成功");//測試圖書/*ExportExcel<Book> exportExcel2 = new ExportExcel<Book>();String[] headers2 = {"圖書編號", "圖書名稱", "圖書作者", "圖書價格", "圖書ISBN","圖書出版社", "封面圖片" };List<Book> dataset2 = new ArrayList<Book>();BufferedInputStream bis = new 	BufferedInputStream(new FileInputStream("D://book.jpg"));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 out2 = new FileOutputStream("E://Book.xls");exportExcel2.exportExcel(headers2, dataset2, out2);out2.close();JOptionPane.showMessageDialog(null, "導出成功");*/}
}

ExportServlet.java

package com.poi.servlet;import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import org.apache.log4j.Logger;import com.poi.bean.Book;
import com.poi.util.ExportExcel;
/*** * @author zhaoxinguo* 使用servlet導出動態生成的excel文件,數據可以來源于數據庫* 這樣,瀏覽器客戶端就可以訪問該servlet得到一份用java代碼動態生成的excel文件**/
public class ExportServlet extends HttpServlet {private static final long serialVersionUID = 1L;private Logger logger = Logger.getLogger(ExportServlet.class);public ExportServlet() {super();}protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {File file = new File(getServletContext().getRealPath("WEB-INF/book.jpg"));response.setContentType("octets/stream");response.addHeader("Content-Disposition", "attachment;filename=test.xls");//測試圖書ExportExcel<Book> ex = new ExportExcel<Book>();String[] headers = { "圖書編號", "圖書名稱", "圖書作者", "圖書價格", "圖書ISBN","圖書出版社", "封面圖片" };List<Book> dataset = new ArrayList<Book>();BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));byte[] buf = new byte[bis.available()];while ((bis.read(buf)) != -1) {// 將圖片數據存放到緩沖數組中}dataset.add(new Book(1, "jsp", "leno", 300.33f, "1234567", "清華出版社", buf));dataset.add(new Book(2, "java編程思想", "brucl", 300.33f, "1234567","陽光出版社", buf));dataset.add(new Book(3, "DOM藝術", "lenotang", 300.33f, "1234567","清華出版社", buf));dataset.add(new Book(4, "c++經典", "leno", 400.33f, "1234567", "清華出版社",buf));dataset.add(new Book(5, "c#入門", "leno", 300.33f, "1234567", "湯春秀出版社",buf));OutputStream out = response.getOutputStream();ex.exportExcel(headers, dataset, out);out.close();logger.info("excel導出成功!");}protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"><display-name>poi</display-name><welcome-file-list><welcome-file>index.html</welcome-file><welcome-file>index.htm</welcome-file><welcome-file>index.jsp</welcome-file><welcome-file>default.html</welcome-file><welcome-file>default.htm</welcome-file><welcome-file>default.jsp</welcome-file></welcome-file-list><servlet><description></description><display-name>ExportServlet</display-name><servlet-name>ExportServlet</servlet-name><servlet-class>com.poi.servlet.ExportServlet</servlet-class></servlet><servlet-mapping><servlet-name>ExportServlet</servlet-name><url-pattern>/ExportServlet</url-pattern></servlet-mapping>
</web-app>


本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/news/443546.shtml
繁體地址,請注明出處:http://hk.pswp.cn/news/443546.shtml
英文地址,請注明出處:http://en.pswp.cn/news/443546.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

matlab漢明碼psk,設計一個漢明碼編碼的2PSK調制的數字通信系統

漢明碼信道編碼的2psk調制數字通信系統設計一個采用2PSK調制的數字通信系統設計系統整體框圖及數學模型&#xff1b;產生離散二進制信源&#xff0c;進行信道編碼(漢明碼)&#xff0c;產生BPSK信號&#xff1b; 加入信道噪聲(高斯白噪聲)&#xff1b;BPSK信號相干解調&#xff…

sh.k7p.work/index.php,Laowang's Blogs

OpenDayLight(硼Boron版本)實戰開發入門OpenDayLight[1](簡寫為ODL)的硼Boron(0.5.0)版本于2016-09-16 這幾天剛剛發布。作為一款開源SDN網絡控制器&#xff0c;依托于強大的社區支持以及豐富的功能特性&#xff0c;ODL成為了目前主流的SDN網絡控制器開發平臺。不僅為開發者提供…

php接收不到ios值,php設置標簽后,ios收不到,安卓可以收到

通過下面代碼設置的標簽&#xff1a;$client->device()->addTags($registration_id, test);通過下面代碼推送的消息$result self::getClient()->push()->setPlatform([ios, android])->addTag([test])->setNotificationAlert($content)->options([time_t…

0+到10+隨機數+java,java代碼--實現隨機輸出10個隨機數,并顯示最大值,最小值

總結;對于length()屬性&#xff0c;還不是很熟悉。不會用它。package com.s.x;//隨機產生10個隨機數&#xff0c;并且顯示出最大值&#xff0c;最小值public class Love {public static void main(String[] args) {int a[] new int[10];int max, min;for (int i 0; i < 10…

oracle推送短信,ORACLE 10G如何實現發短信的服務?

CREATE OR REPLACE PROCEDURE SEND_MAIL(SUBJECT IN VARCHAR2,CONTENTSED IN VARCHAR2) ISEMAIL_SERVER VARCHAR2(30) : 10.1.200.6;SENDER_ADDRESS VARCHAR2(50) : testcz.com.cn;--發件地址RECEIVER_ADDRESS VARCHAR2(30); …

Java和iText導出pdf文檔

一&#xff1a;工程截圖&#xff1a; 二&#xff1a;項目運行截圖&#xff1a; 三&#xff1a;源代碼&#xff1a; Book.java package com.iText.bean;public class Book {private int bookId;// 圖書編號private String name;// 圖書名稱private String author;// 圖書作者pr…

oracle 12 ORA-01262,oracle物理dg安裝:方法二

本文記錄了物理dg的第二種安裝方法&#xff0c;使用rman duplicate from active database&#xff0c;不需要做備份文件。準備工作&#xff1a;1.兩臺虛擬機&#xff0c;主機名&#xff1a;n1, n2&#xff0c;操作系統&#xff1a;centos6.7&#xff0c;建好信任關系2.oracle d…

linux多進程原理,Linux進程調度

極簡模式假設我的系統只有一種調度算法cfs那么有個調度的隊列 cfs_rq所有running的進程都會 進入這個隊列&#xff0c;不在running 或者其他情況會出隊列&#xff0c;ok。則假設隊列控制的算法有以下。cfs_rq_enqueuecfs_rq_dequeuecfs_rq_pick所操作的是進程描述符 task_struc…

openwrt使用linux內核版本,降低OpenWRT的Linux內核版本

不久前&#xff0c;為了移植某驅動程序&#xff0c;筆者可謂絞盡腦汁&#xff0c;在4.1內核版本上&#xff0c;嘗試了很多次都沒能成功&#xff0c;后來仔細分析&#xff0c;才知道是內核版本過高導致的&#xff0c;本文給出降低內核版本的方法&#xff0c;具體編譯環境的搭建&…

Hibernate3.x,hibernate3.x,Hibernate3.x整合Spring3.x不能實現自動創建表結構的解決辦法:...

一&#xff1a;今天遇到一個詭異的問題&#xff0c;就是關于hibernate3.x實現表結構自動創建&#xff0c;一般我們在用Struts2&#xff0c;Hibernate3.x&#xff0c;Spring3.x搭建框架&#xff0c;尤其在開發階段都希望在啟動Web容器時就可以根據Bean實體自動創建數據表結構&am…

linux s t i a權限,關于Linux下s、t、i、a權限

關于Linux下s、t、i、a權限文件權限除了r、w、x外還有s、t、i、a權限&#xff1a;s&#xff1a;文件屬主和組設置SUID和GUID&#xff0c;文件在被設置了s權限后將以root身份執行。在設置s權限時文件屬主、屬組必須先設置相應的x權限&#xff0c;否則s權限并不能正真生效(c h m …

linux ssh禁止用戶訪問任何目錄,怎么限制遠程ssh用戶訪問特定的文件

比如我要實現以下目標&#xff0c;通過配置linux限制SSH用戶指定目錄user 1 只可以訪問 /Media, /Documents以及它的家目錄User 2 只可以訪問/Folder21, 以及它的家目錄,User 3 只可以訪問 /Documents, /Folder21 以及他的家目錄,ssh如何限制指定目錄2. 通過配置Linux權限限制S…

linux配置定時刪除日志文件,Linux使用shell腳本定時刪除歷史日志文件

Linux使用shell腳本定時刪除歷史日志文件,文件,小時,時間,目錄,腳本Linux使用shell腳本定時刪除歷史日志文件易采站長站&#xff0c;站長之家為您整理了Linux使用shell腳本定時刪除歷史日志文件的相關內容。1、tools目錄文件結構[rootwww tools]# tree tools/tools/├── bin│…

linux awk執行shell命令,awk調用shell命令

在awk內部可利用管道和getline函數來調用shell命令&#xff0c;并可得到返回的具體結果&#xff0c;進行相應處理。例子如下&#xff1a;1) {while ( ("ls" | getline) >0 )print}輸出當前目錄下的所有文件&#xff0c;并打印到標準輸出上。| 是管道&#xff0c;g…

linux添加啟動腳本文件夾,linux – 將腳本中的符號鏈接添加到rc.d文件夾中以在系統啟動期間啟動進程...

我正在使用fedora 15.我試圖添加MYSql守護進程在系統strtup期間啟動.我已經明白我必須將它添加到rc5.d,因為它是默認目標&是graphical.target.來自inittab&#xff1a;systemd uses ‘targets’ instead of runlevels. By default, there are two main targets:multi-user.…

org.apache.commons.fileupload.FileUploadBase$SizeLimitExceededException:

一&#xff1a;今天在使用struts2做文件上傳時出現了該異常&#xff1a; 警告: Unable to parse request org.apache.commons.fileupload.FileUploadBase$SizeLimitExceededException: the request was rejected because its size (5897994) exceeds the configured maximum (2…

linux天氣軟件,類似智能手機!Linux中安裝Conky天氣插件

如今&#xff0c;智能手機中很多都安裝相匹配外觀的天氣小插件&#xff0c;而對于喜歡操作系統平臺的用戶而言&#xff0c;可以在你的Linux桌面中擁有像智能手機一樣的天氣外觀。通過Flair Weather Conky可以將使用一個GUI工具Conky Manager在Linux中輕松地管理Conky。這里介紹…

linux go 安裝路徑,在Alpine Linux D的路徑中找不到已安裝的Go二進制文件

我有一個Go二進制文件&#xff0c;試圖在Alpine Docker映像上運行。這對于Docker Go二進制文件很好用。docker run -it alpine:3.3 shapk add --no-cache curlDOCKER_BUCKETget.docker.comDOCKER_VERSION1.9.1curl -fSL "https://${DOCKER_BUCKET}/builds/Linux/x86_64/do…

linux安裝下載中文包,linux下安裝中文包和字體

在虛擬機中使用中文輸入法和中文顯示使用的是rhel5的鏡像我把其鏡像掛載在/mnt/cdrom中&#xff0c;然后切換到/Server目錄下&#xff0c;安裝支持中文字體Mount /dev/cdrom /mnt/cdromCd /mnt/cdrom/serverrpm -ivh fonts-chinese-3.02-9.6.el5.noarch.rpmrpm -ivh fonts-ISO8…

Java-Jdbc,JDBC連接Oracle11g實例:

很長時間沒用Oracle數據庫了&#xff0c;今天在公司的電腦上裝了一個Oracle11g&#xff0c;安裝完成后&#xff0c;順便寫了個簡單的Jdbc連接Oracle的例子&#xff0c;現在記錄一下&#xff0c;方便以后查看&#xff1a; 例子很簡單&#xff0c;直接上代碼&#xff1a; (注意&…