/*
* Classname:
* Version information:
* Creator: chenjian
* Create Date: 2007-3-21下午06:00:23
* Copyright notice:
*/
package nc.ui.fch.bg.t80303;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import jxl.Cell;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 功能說明:
*
* @author chenjian 2007-3-21 下午06:00:22
*/
public class WriteDataToExcel {
/**
*
*/
public WriteDataToExcel() {
// TODO Auto-generated constructor stub
}
/**
* 功能: 向Excel裡寫資料
*
* @param sourceFile
* 從伺服器下載下傳下來的源檔案
* @param newFile
* copy源檔案得到的新檔案,用于寫入資料
* @throws Exception
* @author chenjian 2007-3-22 上午11:20:12
*/
public static void writeData(String sourceFile, String newFile,
ArrayList arr) {
Workbook wb = null;
WritableWorkbook wwb = null;
WritableSheet wsB = null;
try {
// 建立隻讀的Excel工作薄的對象
wb = Workbook.getWorkbook(new File(sourceFile));
// copy上面的Excel工作薄,建立新的可寫入的Excel工作薄對象
wwb = Workbook.createWorkbook(new File(newFile), wb);
// 讀取工作表--(注:工作表索引從0開始)
wsB = wwb.getSheet(0);
// 循環插入資料
for (int i = 0; i < arr.size(); i++) {
ArrayList rowArr = (ArrayList) arr.get(i);
for (int j = 0; j < rowArr.size(); j++) {
WritableCell wc = null;
// 以第一行所有的列為模闆,
wc = wsB.getWritableCell(j, 1);
WritableCellFormat wcFormat = null;
if (wc.getCellFormat() != null) {
// 獲得源單元格格式
wcFormat = new WritableCellFormat(wc.getCellFormat());
} else {
wcFormat = new WritableCellFormat();
}
wc = cloneCellWithValue(j, i + 1, rowArr.get(j),wcFormat );
wsB.addCell(wc);
}
}
// 寫入Excel對象
wwb.write();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 關閉可寫入的Excel對象
try {
if (null != wwb)
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 關閉隻讀的Excel對象
if (null != wb)
wb.close();
}
}
/**
*TODO功能 複制模闆單元格的類型,并判斷插入資料的類型,進行寫入資料 Administrator maoyulong 2007-4-9
* 下午03:52:04
*
* @param col
* @param row
* @param value
* @param wcFormat
* @return
*/
public static WritableCell cloneCellWithValue(int col, int row,
Object value, WritableCellFormat wcFormat) {
WritableCell wc = null;
// 判斷資料是否為STRING類型,是用LABLE形式插入,否則用NUMBER形式插入
if(value == null){
wc = new jxl.write.Blank(col, row, wcFormat);
}
else if (value instanceof String) {
jxl.write.Label label = new jxl.write.Label(col, row, value
.toString(), wcFormat);
wc = label;
}
else {
wc = new jxl.write.Number(col, row, new Double(value.toString())
.doubleValue(), wcFormat);
}
return wc;
}
/**
* 功能:
*
* @param args
* @author chenjian 2007-3-21 下午06:00:23
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
ArrayList q = new ArrayList();
ArrayList w = new ArrayList();
w.add("===");
w.add(new Double(10.00));
q.add(w);
new WriteDataToExcel().writeData("C:\\bgttemplet\\wbs.xls",
"C:\\bgtreport\\wbs.xls", q);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
一 JXL讀日期 會把 “2009-09-09” 讀為 “09-09-09”
使用以下方法解決
private String dateFromExcel(Cell cell) {
String data = cell.getContents();
if (data.toString().trim().equals("")) {
return null;
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if (cell.getType() == CellType.DATE) {
DateCell dc = (DateCell) cell;
data = sdf.format(dc.getDate());
} else {
data = data.toString().trim();
}
return data;
}
package com.jxl.study;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JXLUtil {
private static Workbook workbook;
private static WritableWorkbook writableWorkbook;
/**
* 功能:
* <p>
* 列印出Excel表裡的全部内容
* </p>
*
* @param Excel
* 所在的絕對路徑
*/
public static void readXLS(String filePatch) {
if (!filePatch.endsWith(".xls")) {
System.out.println("=======不是正确的xls格式,請核查==========");
return;
}// end if
File file = new File(filePatch);
// 檔案隻讀
file.setReadOnly();
try {
// 得到Excel檔案
workbook = Workbook.getWorkbook(file);
// Excel中的工作表 下表從0開始
Sheet sheet = workbook.getSheet(0);
int row = sheet.getRows(); // 工作表共有的行
for (int i = 0; i < row; i++) {
Cell[] cells = sheet.getRow(i); // 拿一行的内容
for (int j = 0; j < cells.length; j++) {
System.out.print(cells[j].getContents() + " ");
}
System.out.println();
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
}
// 還原檔案狀态
file.canWrite();
}// end function
/**
* 功能:
* <p>
* 項Excel檔案裡寫入内容
* </p>
*
* @param Excel檔案的絕對路徑
*/
public static void writeJxl(String filePatch) {
if (!filePatch.endsWith(".xls")) {
System.out.println("=======不是正确的xls格式,請核查==========");
return;
}// end if
try {
OutputStream os = new FileOutputStream(filePatch);
// 建立可寫簿
writableWorkbook = Workbook.createWorkbook(os);
// 建立工作表
WritableSheet ws = writableWorkbook.createSheet("sheet", 0);
// 建立一個内容 第一個整數為 列,第二個整數位 行
Label label;
for (int i = 0; i < 10; i++) {
for (int j = 0; j < 10; j++) {
if (i == 0 && j == 0) {
label = new Label(i, j, "");
} else if (j == 0) {
label = new Label(i, j, "第" + (i + 1) + "列");
} else if (i == 0) {
label = new Label(i, j, "第" + (j + 1) + "行");
} else {
label = new Label(i, j, "内容:" + i + "," + j);
}
ws.addCell(label);
}
}
writableWorkbook.write();
writableWorkbook.close();
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
public static void main(String[] args) {
writeJxl("c:\\2.xls");
}
}