jxl操作excel文檔

下面是一些利用jxl操作excel的方法,這個class不能直接用,只是一個模板,涉及到一些像是excel存儲格格式,字體,顏色,存儲格大小,文檔名稱之類的一些零星方法...  

  /** * 產生Excel * @param conn 資料庫連結 * @param form 報價單查詢Form * @return byte[] excel內容 */ public byte[] getExcel(Connection conn, QuotationForm form) { //初始化,字節流 ByteArrayOutputStream baos = new ByteArrayOutputStream(); WritableWorkbook workbook = null; //引用WritableWorkbook workbook = Workbook.createWorkbook(baos); //引用WritableSheet,sheet名 WritableSheet sheet = workbook.createSheet("報價單", 0); //設置單元格的寬度 sheet.setColumnView(0, 10); sheet.setColumnView(1, 12); sheet.setColumnView(2, 15); sheet.setColumnView(3, 10); //詳細資料字體 WritableFont detail = new WritableFont(WritableFont.createFont("新細明體"),10,WritableFont.NO_BOLD,false); WritableCellFormat cellformat = new WritableCellFormat(detail); //紅色字體 WritableFont redInfo = new WritableFont(WritableFont.createFont("新細明體"),10,WritableFont.NO_BOLD,false, jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); WritableCellFormat redcellformat = new WritableCellFormat(redInfo); //粉色字體 WritableFont pinkInfo = new WritableFont(WritableFont.createFont("新細明體"),10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.PINK); WritableCellFormat pinkcellformat = new WritableCellFormat(pinkInfo); //樣式(居中) cellformat.setAlignment(jxl.format.Alignment.CENTRE); redcellformat.setAlignment(jxl.format.Alignment.CENTRE); pinkcellformat.setAlignment(jxl.format.Alignment.CENTRE); //大標題 WritableFont title = new WritableFont(WritableFont.createFont("新細明體"), 20, WritableFont.BOLD,false); WritableCellFormat cellformat_title = new WritableCellFormat(title); cellformat_title.setAlignment(Alignment.CENTRE); //小標題 WritableFont sub_title = new WritableFont(WritableFont.createFont("新細明體"),12,WritableFont.BOLD,false); WritableCellFormat cellfornat_subtitle = new WritableCellFormat(sub_title); cellfornat_subtitle.setAlignment(Alignment.CENTRE); //左對齊 WritableCellFormat cellformat_left = new WritableCellFormat(sub_title); cellformat_left.setAlignment(Alignment.LEFT); //右對齊 WritableCellFormat cellformat_right = new WritableCellFormat(detail); cellformat_right.setAlignment(Alignment.RIGHT); //存儲格樣式,保留兩位小數 NumberFormat scale2format = new NumberFormat("#0.00"); WritableCellFormat numbercellformat_scale2 = new WritableCellFormat(detail,scale2format); numbercellformat_scale2.setAlignment(Alignment.RIGHT); int num = 0; //表頭 sheet.mergeCells(0, num, 14, num); sheet.addCell( new jxl.write.Label( 0, num, "報 價 單 查 詢", cellformat_title ) ) ; num ++; //表頭 sheet.mergeCells(0, num, 14, num); sheet.addCell(new jxl.write.Label(0, num, "當前查詢條件:", cellformat_left)); num ++; ........ //資料(居中對齊) sheet.addCell(new jxl.write.Label(0, num, form.getQuoid(),cellformat)); //右對齊 sheet.addCell(new jxl.write.Number(1, num, Integer.parseInt(quom), cellformat_right)); //保留兩位小數點的數字存儲格 sheet.addCell(new jxl.write.Number(5, num, Double.valueOf(avgm).doubleValue(), numbercellformat_scale2)); //粉紅色文字的存儲格 sheet.addCell(new jxl.write.Label(9, ++ num, stonename, pinkcellformat)); //寫入 workbook.write(); //關閉 workbook.close(); //傳回 return baos.toByteArray(); }

 

加下下面這段JSP代碼就完整了,你要作的就是修改一下上面那個java類的具體內容,直接調用下面這個jsp就可以了:

 

<%-- //2008/08/20 jason 加入國際化支援 /** * <P> Title:匯出excel </P> * <P> Description:匯出excel </P> * <P> Copyright:Copyright (c) 2008/08/11 </P> * <P> Company:Everunion Tech. Ltd. </P> * @author Jason.h * @version 0.2 Original Design from design document. */ --%><%@page contentType="application/octet-stream; charset=UTF-8"%><%@page import="java.sql.*,java.lang.*,java.text.*,java.util.*,java.io.*"%><% //緩存不清除(否則文件址直接開啟時會出現找不到文件的例外,不可省略) response.setHeader("Pragma", "cache"); response.setHeader("Cache-Control", "cache"); //response.setHeader("Pragma", "no-cache"); //response.setHeader("Cache-Control", "no-cache"); //取得語言環境 String ctylgn = ((Locale)session.getAttribute("org.apache.struts.action.LOCALE")).toString(); String alertMes = ""; //如果是繁體中文 if ( "zh_TW".equals(ctylgn) ) alertMes = "此檔案已刪除!"; //如果是簡體中文 else if ( "zh_CN".equals(ctylgn) ) alertMes = "此档案已删除!"; //如果是英文 else if ( "en".equals(ctylgn) ) alertMes = "This file has been deleted!"; //取得文件名 String filename = (String)request.getAttribute("filename"); //設定頭部 response.setContentType("application/octet-stream"); //設定下載檔案名稱 response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO-8859-1")); //取得流物件 byte[] data = (byte[])request.getAttribute("ExcelDATA"); ByteArrayInputStream in = new ByteArrayInputStream(data); try { //建立流傳輸物件 java.io.OutputStream os = response.getOutputStream(); //輸出 int read = -1; byte buf[] = new byte[2048]; int k = 0; //寫入 while ( (read = ((InputStream)in).read(buf)) != -1 ) { os.write(buf, 0, read); } //寫入 os.flush(); } //例外 catch ( Exception e ) { e.printStackTrace(); System.out.println(alertMes); } //關閉 finally { //關閉 //os.flush(); //os.close(); in.close(); in = null; //此段程式用於防止程式拋出"getOutputStream() has already been called for this response"例外 response.flushBuffer(); out.clear(); out = pageContext.pushBody(); } %>

 

附: 需用到的jar包是jxl.jar