import jxl.*;

import jxl.format.UnderlineStyle;

import jxl.write.*;

import jxl.write.Number;

import jxl.write.Boolean;

import java.io.*;

class excel {

public excel() {

}

/**

* 讀取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為名字，2為下標，從0開始

Sheet st = rwb.getSheet(0);

Cell c00 = st.getCell(1, 0);

// 通用的獲取cell值的方式,返回字串

String strc00 = c00.getContents();

// 獲得cell具體型別值的方式

if (c00.getType() == CellType.LABEL) {

LabelCell labelc00 = (LabelCell) c00;

strc00 = labelc00.getString();

}

// 輸出

System.out.println(strc00);

// 關閉

rwb.close();

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 輸出excel

*

* @param os

*/

public static void writeexcel(OutputStream os) {

try {

/**

* 只能透過api提供的工廠方法來建立workbook，而不能使用WritableWorkbook的建構函式，

* 因為類WritableWorkbook的建構函式為protected型別

* method(1)直接從目標檔案中讀取WritableWorkbook wwb =

* workbook.createWorkbook(new file(targetfile)); method(2)如下例項所示

* 將WritableWorkbook直接寫入到輸出流

*

*/

WritableWorkbook wwb = Workbook.createWorkbook(os);

// 建立excel工作表 指定名稱和位置

WritableSheet ws = wwb.createSheet("test sheet 1", 0);

WritableSheet ws2 = wwb.createSheet("test sheet 2", 1);

// **************往工作表中新增資料*****************

// 1.新增label物件

Label label = new Label(0, 0, "this is a label test");

ws.addCell(label);

Label label2 = new Label(0, 0, "this is a label test2");

ws2.addCell(label2);

// 新增帶有字型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);

// 新增帶有字型顏色的formatting物件

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物件

Number Labeln = new Number(0, 1, 3.1415926);

ws.addCell(Labeln);

// 新增帶有formatting的number物件

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物件

Boolean Labelb = new jxl.write.Boolean(0, 2, false);

ws.addCell(Labelb);

// 4.新增datetime物件

jxl.write.DateTime Labeldt = new jxl.write.DateTime(0, 3,

new java.util.Date());

ws.addCell(Labeldt);

// 新增帶有formatting的dateformat物件

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);

// 新增圖片物件,jxl只支援png格式圖片

// File image = new File("f:\\2.png");

// WritableImage wimage = new WritableImage(0,1,2,2,image);

// ws.addImage(wimage);

// 寫入工作表

wwb.write();

wwb.close();

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 複製後,進行修改,其中File1為被copy物件，File2為修改後建立的物件

* 盡單元格原有的格式化修飾是不能去掉的，我們還是可以將新的單元格修飾加上去， 以使單元格的內容以不同的形式表現

*

* @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);

// 判斷單元格的型別,做出相應的轉換