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  Jackter 加入國際化支援
/**

 * <P> Title:匯出excel         	 				</P>
 
 * <P> Description:匯出excel        		    </P>
 
 * <P> Copyright:Copyright (c) 2008/08/11  		</P>
 
 * <P> Company:Everunion Tech. Ltd.             </P>
 
 * @author Jackter       
 
 * @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();
}
%>

下面附上jxl.jar包。