?http://www.iteye.com/topic/611329
前2天后臺系統需要生成報表，正好抽時間復習了一下之前做過的JAVA生成EXCEL，下面介紹POI 和JXL 生成報表的2種方式。
1.jxl 生成報表
?
Java代碼 ?
package?excel; ????import?java.io.FileOutputStream; ??import?java.io.OutputStream; ??import?java.text.SimpleDateFormat; ??import?java.util.Date; ????import?jxl.Workbook; ??import?jxl.format.Alignment; ??import?jxl.format.Border; ??import?jxl.format.BorderLineStyle; ??import?jxl.format.CellFormat; ??import?jxl.write.Label; ??import?jxl.write.WritableCellFormat; ??import?jxl.write.WritableFont; ??import?jxl.write.WritableSheet; ??import?jxl.write.WritableWorkbook; ??????? ????public?class?jxlCreate?{ ????????????????public?static?void?main(String[]?args)?{ ?????????????????????String[]?title?=?{"編號","產品名稱","產品價格","產品數量","生產日期","產地","是否出口"};??? ??????????try?{??? ????????????????????????????long?start?=?System.currentTimeMillis();??? ????????????????????????????String?filePath?=?"c:\\test.xls";??? ????????????????????????????WritableWorkbook?wwb;??? ????????????????????????????OutputStream?os?=?new?FileOutputStream(filePath);??? ??????????????wwb=Workbook.createWorkbook(os);???? ?????????????? ??????????????WritableSheet?sheet?=?wwb.createSheet("產品清單",?0);??? ??????????????Label?label;??? ??????????????for(int?i=0;i<title.length;i++){??? ??????????????????????????????????????????????????????label?=?new?Label(i,0,title[i]);??? ?????????????????? ??????????????????sheet.addCell(label);??? ??????????????}??? ????????????????????????????? ???? ????????????????????????????jxl.write.Number?number?=?new?jxl.write.Number(0,1,20071001);??? ??????????????sheet.addCell(number);??? ????????????????????????????label?=?new?Label(1,1,"金鴿瓜子");??? ??????????????sheet.addCell(label);??? ???????????????????? ??????????????jxl.write.NumberFormat?nf?=?new?jxl.write.NumberFormat("#.##");??? ??????????????jxl.write.WritableCellFormat?wcf?=?new?jxl.write.WritableCellFormat(nf);??? ????????????????????????????jxl.write.Number?nb?=?new?jxl.write.Number(2,1,2.45,wcf);??? ??????????????sheet.addCell(nb);??? ????????????????????????????jxl.write.Number?numb?=?new?jxl.write.Number(3,1,200);??? ??????????????sheet.addCell(numb);??? ??????????????????? ??????????????SimpleDateFormat?sdf?=?new?SimpleDateFormat("yyyy-MM-dd");??? ??????????????String?newdate?=?sdf.format(new?Date());??? ????????????????????????????label?=?new?Label(4,1,newdate);??? ??????????????sheet.addCell(label);??? ????????????????????????????label?=?new?Label(5,1,"陜西西安");??? ??????????????sheet.addCell(label);??? ?????????????????? ??????????????jxl.write.Boolean?bool?=?new?jxl.write.Boolean(6,1,true);??? ??????????????sheet.addCell(bool);??? ????????????????? ???? ??????????????sheet.mergeCells(0,3,2,3);??? ??????????????label?=?new?Label(0,3,"合并了三個單元格");??? ??????????????sheet.addCell(label);??? ?????????????????? ???? ??????????????CellFormat?cf?=?wwb.getSheet(0).getCell(1,?0).getCellFormat();??? ??????????????WritableCellFormat?wc?=?new?WritableCellFormat();??? ????????????????????????????wc.setAlignment(Alignment.CENTRE);??? ????????????????????????????wc.setBorder(Border.ALL,?BorderLineStyle.THIN);??? ????????????????????????????wc.setBackground(jxl.format.Colour.RED);??? ??????????????label?=?new?Label(1,5,"字體",wc);??? ??????????????sheet.addCell(label);??? ???? ????????????????????????????jxl.write.WritableFont?wfont?=?new?jxl.write.WritableFont(WritableFont.createFont("隸書"),20);??? ??????????????WritableCellFormat?font?=?new?WritableCellFormat(wfont);??? ??????????????label?=?new?Label(2,6,"隸書",font);??? ??????????????sheet.addCell(label);??? ????????????????? ????????????????????????????wwb.write();??? ????????????????????????????wwb.close();??? ??????????????long?end?=?System.currentTimeMillis();??? ??????????????System.out.println("----完成該操作共用的時間是:"+(end-start)/1000);??? ??????????}?catch?(Exception?e)?{??? ??????????????System.out.println("---出現異常---");??? ??????????????e.printStackTrace();??? ??????????}??? ????????} ????}??
package excel;import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/*********************************************************************** * * jxlCreate.java * @copyright Copyright: 2009-2012 * @creator 周輝<br/> * @create-time Mar 9, 2010 1:35:19 PM * @revision $Id: * ***********************************************************************/
public class jxlCreate {/*** @param args*/public static void main(String[] args) {// 準備設置excel工作表的標題 String[] title = {"編號","產品名稱","產品價格","產品數量","生產日期","產地","是否出口"}; try { // 獲得開始時間 long start = System.currentTimeMillis(); // 輸出的excel的路徑 String filePath = "c:\\test.xls"; // 創建Excel工作薄 WritableWorkbook wwb; // 新建立一個jxl文件,即在C盤下生成test.xls OutputStream os = new FileOutputStream(filePath); wwb=Workbook.createWorkbook(os); // 添加第一個工作表并設置第一個Sheet的名字 WritableSheet sheet = wwb.createSheet("產品清單", 0); Label label; for(int i=0;i<title.length;i++){ // Label(x,y,z)其中x代表單元格的第x+1列，第y+1行, 單元格的內容是y // 在Label對象的子對象中指明單元格的位置和內容 label = new Label(i,0,title[i]); // 將定義好的單元格添加到工作表中 sheet.addCell(label); } // 下面是填充數據 /* * 保存數字到單元格，需要使用jxl.write.Number * 必須使用其完整路徑，否則會出現錯誤 * */ // 填充產品編號 jxl.write.Number number = new jxl.write.Number(0,1,20071001); sheet.addCell(number); // 填充產品名稱 label = new Label(1,1,"金鴿瓜子"); sheet.addCell(label); /* * 定義對于顯示金額的公共格式 * jxl會自動實現四舍五入 * 例如 2.456會被格式化為2.46,2.454會被格式化為2.45 * */ jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf); // 填充產品價格 jxl.write.Number nb = new jxl.write.Number(2,1,2.45,wcf); sheet.addCell(nb); // 填充產品數量 jxl.write.Number numb = new jxl.write.Number(3,1,200); sheet.addCell(numb); /* * 定義顯示日期的公共格式 * 如:yyyy-MM-dd hh:mm * */ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String newdate = sdf.format(new Date()); // 填充出產日期 label = new Label(4,1,newdate); sheet.addCell(label); // 填充產地 label = new Label(5,1,"陜西西安"); sheet.addCell(label); /* * 顯示布爾值 * */ jxl.write.Boolean bool = new jxl.write.Boolean(6,1,true); sheet.addCell(bool); /* * 合并單元格 * 通過writablesheet.mergeCells(int x,int y,int m,int n);來實現的 * 表示將從第x+1列，y+1行到m+1列，n+1行合并 * * */ sheet.mergeCells(0,3,2,3); label = new Label(0,3,"合并了三個單元格"); sheet.addCell(label); /* * * 定義公共字體格式 * 通過獲取一個字體的樣式來作為模板 * 首先通過web.getSheet(0)獲得第一個sheet * 然后取得第一個sheet的第二列，第一行也就是"產品名稱"的字體 * */ CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat(); WritableCellFormat wc = new WritableCellFormat(); // 設置居中 wc.setAlignment(Alignment.CENTRE); // 設置邊框線 wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 設置單元格的背景顏色 wc.setBackground(jxl.format.Colour.RED); label = new Label(1,5,"字體",wc); sheet.addCell(label); // 設置字體 jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隸書"),20); WritableCellFormat font = new WritableCellFormat(wfont); label = new Label(2,6,"隸書",font); sheet.addCell(label); // 寫入數據 wwb.write(); // 關閉文件 wwb.close(); long end = System.currentTimeMillis(); System.out.println("----完成該操作共用的時間是:"+(end-start)/1000); } catch (Exception e) { System.out.println("---出現異常---"); e.printStackTrace(); } }}
?2.POI 生成
?
Java代碼 ?
package?excel; ????import?java.io.FileOutputStream; ??import?java.text.SimpleDateFormat; ??import?java.util.Date; ????import?org.apache.poi.hssf.usermodel.HSSFWorkbook; ??import?org.apache.poi.ss.usermodel.Cell; ??import?org.apache.poi.ss.usermodel.CellStyle; ??import?org.apache.poi.ss.usermodel.DataFormat; ??import?org.apache.poi.ss.usermodel.Row; ??import?org.apache.poi.ss.usermodel.Sheet; ??import?org.apache.poi.ss.usermodel.Workbook; ??import?org.apache.poi.ss.util.CellRangeAddress; ??????????? ????public?class?poiCreate?{ ????????????????public?static?void?main(String[]?args)?throws?Exception?{ ????????????????????Workbook?wb?=?new?HSSFWorkbook(); ??????????DataFormat?format?=?wb.createDataFormat(); ??????????CellStyle?style; ????????????????????Sheet?sheet1?=?wb.createSheet("產品清單"); ??????????String[]?title?=?{"編號","產品名稱","產品價格","產品數量","生產日期","產地","是否出口"}; ??????????int?i=0; ????????????????????Row?row?=?sheet1.createRow((short)0); ????????????????????for?(String??s:title){ ??????????????Cell?cell?=?row.createCell(i); ??????????????cell.setCellValue(s); ??????????????i++; ??????????} ??????????Row?row1?=?sheet1.createRow((short)1); ????????????????????row1.createCell(0).setCellValue(20071001); ??????????row1.createCell(1).setCellValue("金鴿瓜子"); ????????????????????Cell?cell2=row1.createCell(2); ????????????????????cell2.setCellValue(2.45); ??????????style?=?wb.createCellStyle(); ??????????style.setDataFormat(format.getFormat("#.##")); ????????????????????cell2.setCellStyle(style); ????????????????????row1.createCell(3).setCellValue(200); ???????????????????????SimpleDateFormat?sdf?=?new?SimpleDateFormat("yyyy-MM-dd");??? ??????????String?newdate?=?sdf.format(new?Date());? ????????????????????row1.createCell(4).setCellValue(newdate); ??????????row1.createCell(5).setCellValue("陜西西安"); ????????????? ??????????row1.createCell(6).setCellValue(true); ????????????????? ??????????Row?row2?=?sheet1.createRow((short)?2); ???????????Cell?cell3?=?row2.createCell((short)?0); ???????????cell3.setCellValue("合并了三個單元格"); ??????????sheet1.addMergedRegion(new?CellRangeAddress(2,2,0,2)); ?????????? ??????????FileOutputStream?fileOut?=?new?FileOutputStream("d:\\test.xls"); ??????????wb.write(fileOut); ??????????fileOut.close(); ??????????} ????}??
package excel;import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;/*********************************************************************** * * poiCreate.java * @copyright Copyright: 2009-2012 * @creator 周輝<br/> * @create-time Mar 9, 2010 2:27:52 PM * @revision $Id: * ***********************************************************************/
public class poiCreate {/*** @param args*/public static void main(String[] args) throws Exception {//創建一個EXCELWorkbook wb = new HSSFWorkbook();DataFormat format = wb.createDataFormat();CellStyle style;//創建一個SHEETSheet sheet1 = wb.createSheet("產品清單");String[] title = {"編號","產品名稱","產品價格","產品數量","生產日期","產地","是否出口"};int i=0;//創建一行Row row = sheet1.createRow((short)0);//填充標題for (String s:title){Cell cell = row.createCell(i);cell.setCellValue(s);i++;}Row row1 = sheet1.createRow((short)1);//下面是填充數據row1.createCell(0).setCellValue(20071001);row1.createCell(1).setCellValue("金鴿瓜子");//創建一個單元格子Cell cell2=row1.createCell(2);// 填充產品價格cell2.setCellValue(2.45);style = wb.createCellStyle();style.setDataFormat(format.getFormat("#.##"));//設定樣式cell2.setCellStyle(style);// 填充產品數量row1.createCell(3).setCellValue(200);/* * 定義顯示日期的公共格式 * 如:yyyy-MM-dd hh:mm * */SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String newdate = sdf.format(new Date()); // 填充出產日期 row1.createCell(4).setCellValue(newdate);row1.createCell(5).setCellValue("陜西西安");/* * 顯示布爾值 * */ row1.createCell(6).setCellValue(true);/* * 合并單元格 * 通過writablesheet.mergeCells(int x,int y,int m,int n);來實現的 * 表示將first row, last row,first column,last column* * */ Row row2 = sheet1.createRow((short) 2);Cell cell3 = row2.createCell((short) 0);cell3.setCellValue("合并了三個單元格");sheet1.addMergedRegion(new CellRangeAddress(2,2,0,2));FileOutputStream fileOut = new FileOutputStream("d:\\test.xls");wb.write(fileOut);fileOut.close();}}
?? 上面代碼2中方式生成 2張報表，涉及到基本生成報表中的幾種單元格類型。
?? POI 用的JAR poi-3.6-20091214.jar?? jxl 用到的jar? jxl-2.6.jar
?? 2 種方式都相對比較好用，個人推薦使用POI （apache的項目）
???? 相關參考資料可以去官方網站查看
??? http://poi.apache.org/spreadsheet/quick-guide.html
?
?
總結
以上是生活随笔為你收集整理的poi jxl 生成EXCEL 报表的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
