來(lái)源于：https://www.iteye.com/blog/271788203-491052
首先需要一個(gè)JXL包掰茶，下載地址：http://download.csdn.net/source/292830?
1顷链、生成EXCEL需要手動(dòng)寫(xiě)查詢(xún)語(yǔ)句把ORACLE數(shù)據(jù)庫(kù)中的數(shù)據(jù)查詢(xún)出來(lái)，再通過(guò)操作寫(xiě)到EXCEL文件里面焚刚。?
2点弯、通過(guò)EXCEL把數(shù)據(jù)讀取到ORACLE，同樣需要去讀取EXCEL工作薄里面的內(nèi)容矿咕，再通過(guò)INSERT語(yǔ)句去插入數(shù)據(jù)庫(kù)操作抢肛。
示例：?
包括從Excel讀取數(shù)據(jù),生成新的Excel狼钮，以及修改Excel
Java代碼?
package?common.util;???
import?jxl.*;???
import?jxl.format.UnderlineStyle;???
import?jxl.write.*;???
import?jxl.write.Number;???
import?jxl.write.Boolean;???
import?java.io.*;???
/**?
*?Created?by?IntelliJ?IDEA.?
*?User:?xl?
*?Date:?2005-7-17?
*?Time:?9:33:22?
*?To?change?this?template?use?File?|?Settings?|?File?Templates.?
*/???
public?class?ExcelHandle???
{???
public?ExcelHandle()???
{???
}???
/**?
*?讀取Excel?
*?
*?@param?filePath?
*/???
public?static?void?readExcel(String?filePath)???
{???
try???
{???
InputStream?is?=new?FileInputStream(filePath);???
Workbook?rwb?=?Workbook.getWorkbook(is);???
//Sheet?st?=?rwb.getSheet("0")這里有兩種方法獲取sheet表,1為名字，而為下標(biāo)捡絮，從0開(kāi)始???
Sheet?st?=?rwb.getSheet("original");???
Cell?c00?=?st.getCell(0,0);???
//通用的獲取cell值的方式,返回字符串???
String?strc00?=?c00.getContents();???
//獲得cell具體類(lèi)型值的方式???
if(c00.getType()?==?CellType.LABEL)???
{???
LabelCell?labelc00?=?(LabelCell)c00;???
strc00?=?labelc00.getString();???
}???
//輸出???
System.out.println(strc00);???
//關(guān)閉???
rwb.close();???
}???
catch(Exception?e)???
{???
e.printStackTrace();???
}???
}???
/**?
*?輸出Excel?
*?
*?@param?os?
*/???
public?static?void?writeExcel(OutputStream?os)???
{???
try???
{???
/**?
*?只能通過(guò)API提供的工廠(chǎng)方法來(lái)創(chuàng)建Workbook燃领，而不能使用WritableWorkbook的構(gòu)造函數(shù)，?
*?因?yàn)轭?lèi)WritableWorkbook的構(gòu)造函數(shù)為protected類(lèi)型?
*?method(1)直接從目標(biāo)文件中讀取WritableWorkbook?wwb?=?Workbook.createWorkbook(new?File(targetfile));?
*?method(2)如下實(shí)例所示?將WritableWorkbook直接寫(xiě)入到輸出流?
*/???
WritableWorkbook?wwb?=?Workbook.createWorkbook(os);???
//創(chuàng)建Excel工作表?指定名稱(chēng)和位置???
WritableSheet?ws?=?wwb.createSheet("Test?Sheet?1",0);???
//**************往工作表中添加數(shù)據(jù)*****************???
//1.添加Label對(duì)象???
Label?label?=new?Label(0,0,"this?is?a?label?test");???
ws.addCell(label);???
//添加帶有字型Formatting對(duì)象???
WritableFont?wf?=new?WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);???
WritableCellFormat?wcf?=new?WritableCellFormat(wf);???
Label?labelcf?=new?Label(1,0,"this?is?a?label?test",wcf);???
ws.addCell(labelcf);???
//添加帶有字體顏色的Formatting對(duì)象???
WritableFont?wfc?=new?WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,???
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);???
WritableCellFormat?wcfFC?=new?WritableCellFormat(wfc);???
Label?labelCF?=new?Label(1,0,"This?is?a?Label?Cell",wcfFC);???
ws.addCell(labelCF);???
//2.添加Number對(duì)象???
Number?labelN?=new?Number(0,1,3.1415926);???
ws.addCell(labelN);???
//添加帶有formatting的Number對(duì)象???
NumberFormat?nf?=new?NumberFormat("#.##");???
WritableCellFormat?wcfN?=new?WritableCellFormat(nf);???
Number?labelNF?=new?jxl.write.Number(1,1,3.1415926,wcfN);???
ws.addCell(labelNF);???
//3.添加Boolean對(duì)象???
Boolean?labelB?=new?jxl.write.Boolean(0,2,false);???
ws.addCell(labelB);???
//4.添加DateTime對(duì)象???
jxl.write.DateTime?labelDT?=new?jxl.write.DateTime(0,3,new?java.util.Date());???
ws.addCell(labelDT);???
//添加帶有formatting的DateFormat對(duì)象???
DateFormat?df?=new?DateFormat("dd?MM?yyyy?hh:mm:ss");???
WritableCellFormat?wcfDF?=new?WritableCellFormat(df);???
DateTime?labelDTF?=new?DateTime(1,3,new?java.util.Date(),wcfDF);???
ws.addCell(labelDTF);???
//添加圖片對(duì)象,jxl只支持png格式圖片???
File?image?=new?File("f:\\2.png");???
WritableImage?wimage?=new?WritableImage(0,1,2,2,image);???
ws.addImage(wimage);???
//寫(xiě)入工作表???
wwb.write();???
wwb.close();???
}???
catch(Exception?e)???
{???
e.printStackTrace();???
}???
}???
/**???
*?拷貝后,進(jìn)行修改,其中file1為被copy對(duì)象锦援，file2為修改后創(chuàng)建的對(duì)象???
*?盡單元格原有的格式化修飾是不能去掉的，我們還是可以將新的單元格修飾加上去剥悟，???
*?以使單元格的內(nèi)容以不同的形式表現(xiàn)???
*@param?file1???
*@param?file2???
*/???
public?static?void?modifyExcel(File?file1,File?file2)???
{???
try???
{???
Workbook?rwb?=?Workbook.getWorkbook(file1);???
WritableWorkbook?wwb?=?Workbook.createWorkbook(file2,rwb);//copy???
WritableSheet?ws?=?wwb.getSheet(0);???
WritableCell?wc?=?ws.getWritableCell(0,0);???
//判斷單元格的類(lèi)型,做出相應(yīng)的轉(zhuǎn)換???
if(wc.getType?==?CellType.LABEL)???
{???
Label?label?=?(Label)wc;???
label.setString("The?value?has?been?modified");???
}???
wwb.write();???
wwb.close();???
rwb.close();???
}???
catch(Exception?e)???
{???
e.printStackTrace();???
}???
}???
//測(cè)試???
public?static?void?main(String[]?args)???
{???
try???
{???
//讀Excel???
ExcelHandle.readExcel("f:/testRead.xls");???
//輸出Excel???
File?fileWrite?=new?File("f:/testWrite.xls");???
fileWrite.createNewFile();???
OutputStream?os?=new?FileOutputStream(fileWrite);???
ExcelHandle.writeExcel(os);???
//修改Excel???
ExcelHandle.modifyExcel(new?file(""),new?File(""));???
}???
catch(Exception?e)???
{???
e.printStackTrace();???
}???
}???
}???
?2.在jsp中做相關(guān)測(cè)試灵寺，創(chuàng)建一個(gè)writeExcel.jsp
Java代碼?
<%???
response.reset();//清除Buffer???
response.setContentType("application/vnd.ms-excel");???
File?fileWrite?=new?File("f:/testWrite.xls");???
fileWrite.createNewFile();???
new?FileOutputStream(fileWrite);???
ExcelHandle.writeExcel(new?FileOutputStream(fileWrite));???
%>???
在IE中瀏覽writeExcel.jsp就可以動(dòng)態(tài)生成Excel文檔了，其中response.setContentType("application/vnd.ms-excel");語(yǔ)句必須要区岗，才能確保不亂碼略板，在jsp中輸入<%@page?contentType="application/vnd.ms-excel;charset=GBK"%>不行。???
在IE中瀏覽writeExcel.jsp就可以動(dòng)態(tài)生成Excel文檔了慈缔，其中response.setContentType("application/vnd.ms-excel");語(yǔ)句必須要叮称，才能確保不亂碼，在jsp中輸入<%@page contentType="application/vnd.ms-excel;charset=GBK"%>不行藐鹤。
