從Excel讀取數(shù)據(jù)生成新的Excel以及修改Excel-

1、Java Excel是一開放源碼項目,通過它Java開發(fā)人員可以讀取Excel文件的內(nèi)容、創(chuàng)建新的Excel文件、更新已經(jīng)存在的Excel文件。使用該API非Windows操作系統(tǒng)也可以通過純Java應(yīng)用來處理Excel數(shù)據(jù)表。因為是使用Java編寫的,所以我們在Web應(yīng)用中可以通過JSP、Servlet來調(diào)用API實現(xiàn)對Excel數(shù)據(jù)表的訪問。提供以下功能:從Excel 95、97、2000等格式的文件中讀取數(shù)據(jù);讀取Excel公式(可以讀取Excel 97以后的公式;生成Excel數(shù)據(jù)表(格式為Excel 97;支持字體、數(shù)字、日期的格式化;支持單元格的陰影操作,以及顏色操作;修改已經(jīng)存在

2、的數(shù)據(jù)表;能夠讀取圖表信息1.應(yīng)用示例:包括從Excel讀取數(shù)據(jù),生成新的Excel,以及修改Excelpackage common.util;import jxl.*;/* 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 ExcelHandlepublic ExcelHandle(/* 讀取Excel* param filePath*/public st

3、atic void readExcel(String filePathtryInputStream is = new FileInputStream(filePath;Workbook rwb = Workbook.getWorkbook(is;/Sheet st = rwb.getSheet(0這里有兩種方法獲取sheet表,1為名字,而為下標,從0開始Sheet st = rwb.getSheet(original;Cell c00 = st.getCell(0,0;/通用的獲取cell值的方式,返回字符串String strc00 = c00.getContents(;/獲得cell具體

4、類型值的方式if(c00.getType( = CellType.LABELLabelCell labelc00 = (LabelCellc00;strc00 = labelc00.getString(;/輸出/關(guān)閉rwb.close(;catch(Exception ee.printStackTrace(;/* 輸出Excel* param os*/public static void writeExcel(OutputStream ostry/* 只能通過API提供的工廠方法來創(chuàng)建Workbook,而不能使用WritableWorkbook的構(gòu)造函數(shù), * 因為類WritableWorkb

5、ook的構(gòu)造函數(shù)為protected類型* method(1直接從目標文件中讀取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile;* method(2如下實例所示將WritableWorkbook直接寫入到輸出流*/WritableWorkbook wwb = Workbook.createWorkbook(os;/創(chuàng)建Excel工作表指定名稱和位置WritableSheet ws = wwb.createSheet(Test Sheet 1,0;/*往工作表中添加數(shù)據(jù)*/1.添加Label對象Label la

6、bel = new Label(0,0,this is a label test;ws.addCell(label;/添加帶有字型Formatting對象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;/添加帶有字體顏色的Formatti

7、ng對象WritableCellFormat wcfFC = new WritableCellFormat(wfc;Label labelCF = new Label(1,0,This is a Label Cell,wcfFC;ws.addCell(labelCF;/2.添加Number對象Number labelN = new Number(0,1,3.1415926;ws.addCell(labelN;/添加帶有formatting的Number對象NumberFormat nf = new NumberFormat(#.#;WritableCellFormat wcfN = new W

8、ritableCellFormat(nf;ws.addCell(labelNF;/3.添加Boolean對象ws.addCell(labelB;/4.添加DateTime對象ws.addCell(labelDT;/添加帶有formatting的DateFormat對象DateFormat df = new DateFormat(dd MM yyyy hh:mm:ss;WritableCellFormat wcfDF = new WritableCellFormat(df;ws.addCell(labelDTF;/添加圖片對象,jxl只支持png格式圖片F(xiàn)ile image = new File

9、(f:2.png;WritableImage wimage = new WritableImage(0,1,2,2,image;/0,1分別代表x,y.2,2代表寬和高占的單元格數(shù)ws.addImage(wimage;/寫入工作表wwb.write(;wwb.close(;catch(Exception ee.printStackTrace(;/* 拷貝后,進行修改,其中file1為被copy對象,file2為修改后創(chuàng)建的對象* 盡單元格原有的格式化修飾是不能去掉的,我們還是可以將新的單元格修飾加上去,* 以使單元格的內(nèi)容以不同的形式表現(xiàn)* param file1* param file2*/

10、public static void modifyExcel(File file1,File file2tryWorkbook rwb = Workbook.getWorkbook(file1;WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb;/copyWritableSheet ws = wwb.getSheet(0;WritableCell wc = ws.getWritableCell(0,0;/判斷單元格的類型,做出相應(yīng)的轉(zhuǎn)換if(wc.getType = CellType.LABELLabel label = (Labe

11、lwc;label.setString(The value has been modified;wwb.write(;wwb.close(;rwb.close(;catch(Exception ee.printStackTrace(;/測試public static void main(String argstry/讀ExcelExcelHandle.readExcel(f:/testRead.xls;/輸出ExcelFile fileWrite = new File(f:/testWrite.xls;fileWrite.createNewFile(;OutputStream os = new FileOutputStream(fileWrite;ExcelHandle.writeExcel(os;/修改ExcelExcelHandle.modifyExcel(new file(,new File(