jxl 对Excel的读写改操作
package cn.com.jyangzi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* jxl 读取
* @author Michael sun
*/
public class JxlTest {
/**
* 读取 excel 文件
* @param filePath
* @throws Exception
*/
private void readExcel(String filePath) throws Exception {
InputStream is = null;
Workbook workbook = null;
try {
is = new FileInputStream(filePath);
workbook = Workbook.getWorkbook(is);
// sheet row column 下标都是从0开始的
Sheet sheet = workbook.getSheet(0);
int column = sheet.getColumns();
int row = sheet.getRows();
System.out.println("共有" + row + "行，" + column + "列数据");
for(int r=0;r<row;r++)
{
for(int c=0;c<column;c++)
{
// A1是字符
Cell cell = sheet.getCell(c, r);
System.out.print("A1 type:" + cell.getType());
//if (cell.getType().equals(CellType.LABEL)) {
System.out.print(" " + cell.getContents());
//}
}
System.out.println("");
}
// // A1是字符
// Cell cellA1 = sheet.getCell(0, 0);
// System.out.println("A1 type:" + cellA1.getType());
// if (cellA1.getType().equals(CellType.LABEL)) {
// System.out.println("A1 content:" + cellA1.getContents());
// }
//
// // B1是数字
// Cell cellB1 = sheet.getCell(1, 0);
// System.out.println("B1 type:" + cellB1.getType());
// if (cellB1.getType().equals(CellType.NUMBER)) {
// NumberCell numberCell = (NumberCell) cellB1;
// double douval = numberCell.getValue();
// System.out.println("B1 value:" + douval);
// }
//
// // C1是日期
// Cell cellC1 = sheet.getCell(2, 0);
// System.out.println("C1 type:" + cellC1.getType());
// if (cellC1.getType().equals(CellType.DATE)) {
// DateCell dateCell = (DateCell) cellC1;
// Date date =dateCell.getDate();
// System.out.println("C1 date:" + date);
// }
// 操作完成时，关闭对象，释放占用的内存空间
workbook.close();
is.close();
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (is != null) {
is.close();
}
}
}
/**
* 写入 excel 文件
* @param filePath
* @throws Exception
*/
private void writeExcel(String filePath) throws Exception {
OutputStream os = null;
try {
System.out.println("ddd");
// 构建Workbook对象
os = new FileOutputStream(filePath);
WritableWorkbook wwb = Workbook.createWorkbook(os);
// 构建Excel sheet
WritableSheet sheet = wwb.createSheet("test write sheet", 0);
// 设置标题格式
WritableFont wfTitle = new jxl.write.WritableFont(
WritableFont.ARIAL, 18, WritableFont.BOLD, true);
WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);
// 设置水平对齐方式
wcfTitle.setAlignment(Alignment.CENTRE);
// 设置垂直对齐方式
wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
// 设置是否自动换行
wcfTitle.setWrap(true);
// 合并A1->C2
sheet.mergeCells(0, 0, 2, 1);
Label titleCell = new Label(0, 0, "Title Cell ", wcfTitle);
sheet.addCell(titleCell);
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLUE);
WritableCellFormat wcf = new WritableCellFormat(wf);
// A3
Label labelCell = new Label(0, 2, "Label Cell ");
sheet.addCell(labelCell);
// B3
Label labelCellFmt = new Label(1, 2,
"Label Cell with WritableCellFormat ", wcf);
sheet.addCell(labelCellFmt);
// A4 添加jxl.write.Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 3, 3.1415926);
sheet.addCell(labelN);
// B4 添加Number对象 jxl.write.NumberFormat
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = new WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 3, 3.1415926,
wcfN);
sheet.addCell(labelNF);
// A5 添加jxl.write.Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 4, true);
sheet.addCell(labelB);
// A6 添加 jxl.write.DateTime对象
jxl.write.DateTime labelDT =
new jxl.write.DateTime(0, 5, new Date());
sheet.addCell(labelDT);
// B6 添加DateTime对象 jxl.write.DateFormat
jxl.write.DateFormat df = new jxl.write.DateFormat(
"yyyy-MM-dd HH:mm:ss");
WritableCellFormat wcfDF = new WritableCellFormat(df);
jxl.write.DateTime labelDTF =
new jxl.write.DateTime(1, 5, new Date(), wcfDF);
sheet.addCell(labelDTF);
//先调用write();再调用close();
wwb.write();
wwb.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != os) {
os.close();
}
}
}
/**
* 插入图片
* @param filePath
* @throws Exception
*/
private void writeImg(String filePath) throws Exception {
OutputStream os = null;
try {
String imgPath = "K:\\网厅项目文件\\网厅\\需求\\IMG_0537.png";
os = new FileOutputStream(filePath);
WritableWorkbook wwb = Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet("write img", 0);
File imgFile = new File(imgPath);
// WritableImage(col, row, width, height, imgFile);
WritableImage image = new WritableImage(2, 1, 8, 20, imgFile);
ws.addImage(image);
wwb.write();
wwb.close();
} catch (Exception e) {
System.out.println(e);
} finally {
if (null != os) {
os.close();
}
}
}
/**
* 更新Excel
* @param filePath
*/
private void doUpdate(String filePath) {
try {
// 获得原Excel文件
Workbook wb = Workbook.getWorkbook(new File(filePath));
// 打开一个文件的副本，并且指定数据写回到原文件
WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath),wb);
// 对第一个工作簿的A1 更新
WritableSheet wsheet0 = wwb.getSheet(0);
WritableCell wc00 = wsheet0.getWritableCell(0, 0);
if (wc00.getType() == CellType.LABEL) {
Label label00 = (Label) wc00;
label00.setString("updata data");
}
// 添加一个工作表
WritableSheet sheet = wwb.createSheet("新增工作簿", 1);
// 写入一些测试数据
sheet.addCell(new Label(0, 0, "test data"));
// 关闭工作薄对象
wwb.write();
wwb.close();
wb.close();
} catch (Exception e) {
System.out.println(e);
}
}
//----------------------------main()方法-----------------------------
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
JxlTest jxltest = new JxlTest();
/**
* 读文件
*/
String filePath = "K:\\网厅项目文件\\网厅\\需求\\read.xls";
jxltest.readExcel(filePath);
/**
* 写文件
*/
String filePath2 = "K:\\网厅项目文件\\网厅\\需求\\write.xls";
jxltest.writeExcel(filePath2);
/**
* 插入图片
*/
String filePath3 = "K:\\网厅项目文件\\网厅\\需求\\img.xls";
jxltest.writeImg(filePath3);
/**
*更新Excel
*/
String filePath4 = "K:\\网厅项目文件\\网厅\\需求\\read.xls";
jxltest.doUpdate(filePath4);
}
}
页:
[1]