java & Excel

0. 下載 jxl
http://www.andykhan.com/jexcelapi/download.html
1. exl.java

package num;
import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import java.awt.Color;
import java.sql.*;
public class exl{
public static void writeExcel(OutputStream os) throws Exception {
Connection cn = null;
Statement stmt = null;
Connection cn1 = null;
Statement stmt1 = null;
ResultSet rs= null;
ResultSet rsemp= null;
int totalRecord = 0;
int rowCount = 0;
Class.forName("oracle.jdbc.driver.OracleDriver");
cn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:REP","a","pwd");
stmt = cn.createStatement();
...
rs = stmt.executeQuery(strqSql);
rs.next();
totalRecord=(new Integer(rs.getString("total_dept"))).intValue(); // 轉為數字
rs = null;
...
rs = stmt.executeQuery(strqSql);
rs.next();
totalRecord=totalRecord + (new Integer(rs.getString("total_emp"))).intValue();

rowCount=totalRecord/5;

jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); //建立Excel
jxl.write.WritableSheet ws = wwb.createSheet("資料表",0); //建立 Sheet
GregorianCalendar gc = new GregorianCalendar(); // 建立日期
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,20, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
// 設定字型與格式
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setAlignment(jxl.format.Alignment.CENTRE); //設定欄位資料置中
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //設定欄位邊框格式
// wcfFC.setVerticalAlignment(CENTRE);
jxl.write.Label labelC = new jxl.write.Label(0, 0, "天天 :" + (gc.get(GregorianCalendar.MONTH)+1) + " 明細表",wcfFC);
//設定第幾個欄位與資料內容
ws.mergeCells(0, 0, 4, 0); //合併儲存格
ws.addCell(labelC);
...
rs = stmt.executeQuery(strqSql);
int i=0;
int j=1;
while(rs.next())
{
wfc = new jxl.write.WritableFont(WritableFont.ARIAL,14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setBackground(jxl.format.Colour.YELLOW);
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
labelC = new jxl.write.Label(i, j, rs.getString("CODE") + " " + rs.getString("DEPT"),wcfFC);
ws.setColumnView(i,32); //設定欄寬
ws.addCell(labelC);
j=j+1;
if(j%rowCount==0)
{
i=i+1;
j=1;
}
...
rsemp = stmt1.executeQuery(strqSql);
while(rsemp.next())
{
String Ename=rsemp.getString("ENAME");
if (Ename.indexOf("_") != -1) // 若英文名含有 "_"
Ename=Ename.substring(0,Ename.indexOf("_")); //取出 "_" 前的英文字
wfc = new jxl.write.WritableFont(WritableFont.ARIAL,14, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfc);
if (!rsemp.getString("manager").equals("N") && !rsemp.getString("manager").equals("J")) // 某條件以上,將背景設定為粉紅色
wcfFC.setBackground(jxl.format.Colour.PINK); // 設定欄位背景顏色
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
labelC = new jxl.write.Label(i, j, rsemp.getString("EXT") + " " + rsemp.getString("CNAME") + " " +Ename,wcfFC);
ws.setRowView(j,385,false); // 設定列高
ws.addCell(labelC);
j=j+1;
if(j%rowCount==0)
{
i=i+1;
j=1;
}
}
}
SheetSettings settings = ws.getSettings();
settings.setFitHeight(1); // 設定預覽列印與列印成為一頁, 寬度
settings.setFitWidth(1); // 設定預覽列印與列印成為一頁, 高度
settings.setFitToPages(true);

//寫入Exel工作表
wwb.write();
//關閉Excel工作薄對像
wwb.close();
cn=null;
cn1=null;
}


//寫一個這樣的main方法測試一下。
public static void main(String[] args)throws Exception{
File f=new File("kk.xls");
f.createNewFile();
writeExcel(new FileOutputStream(f));
}
}



2 . 在 JSP 中
response.reset();
response.setContentType("application/vnd.ms-excel");
ExpTelExl.writeExcel(response.getOutputStream());
即可下載或打開 Excel.