/*
* 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");
}
}