下面是一些利用jxl操作excel的方法,這個class不能直接用,只是一個模板,涉及到一些像是excel存儲格格式,字體,顏色,存儲格大小,文檔名稱之類的一些零星方法...
加下下面這段JSP代碼就完整了,你要作的就是修改一下上面那個java類的具體內容,直接調用下面這個jsp就可以了
下面附上jxl.jar包。
/**
* 產生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包。