一、JExcelApi 可以从文件或者输入流进行读取操作。
基本步骤:
1.由文件或者输入流创建一个workbook;
2.由workbook的getSheet()方法创建一个工作表Sheet(两种方法,下标和名字,下标从0开始);
3.由Sheet的getCell(x,y)方法得到某个单元格,cell对象可以读取它的类型(getType)、内容(getContents)等。
读取excel表格内容的代码如下:
package com.test;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ExcelRead {
/**
* @param args
*/
public static void main(String[] args) {
File f = new File("F:/shar/test/test.xls");
try {
Workbook book = Workbook.getWorkbook(f);//
Sheet sheet = book.getSheet(0); // 获得第一个工作表对象
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i); // 获得单元格
System.out.print(cell.getContents() + " ");
//得到单元格的类型
//System.out.println(cell.getType());
}
System.out.print("\n");
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
二、创建excel表格的基本步骤:
1.创建一个WritableWorkbook对象(用Workbook的createWorkbook方法创建),要指定创建一个文件;
2.创建一个工作表WritableSheet(用workbook对象的createSheet方法创建),注意要是WritableSheet,说明可以对其写;
3.创建单元格,再将单元格加入到sheet里;
4.执行workbook的write()方法进行写操作最后关闭workbook。
创建表格的具体代码如下:
package com.test;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ExcelWrite {
/**
* @param args
* @throws IOException
* @throws WriteException
* @throws RowsExceededException
*/
public static void main(String[] args) throws IOException, RowsExceededException, WriteException {
// TODO Auto-generated method stub
WritableWorkbook workbook = Workbook.createWorkbook(new File("F:/shar/test/write1.xls"));
//生成第一页的工作表,参数为0说明是第一页
WritableSheet sheet = workbook.createSheet("第一页", 0);
//指明单元格的位置是第一行第一列,第一个参数为列
Label type = new Label(0,0,"通话类型");
Label poneNo = new Label(1,0,"对方号码");
Label addr = new Label(2,0,"通话地");
Label time = new Label(3,0,"通话时长");
//将单元格加到工作表中
sheet.addCell(type);
sheet.addCell(poneNo);
sheet.addCell(addr);
sheet.addCell(time);
//数字类型
//jxl.write.Number number = new jxl.write.Number(0,1,789.123);
Label type1 = new Label(0,1,"主叫");
Label poneNo1 = new Label(1,1,"1236456");
Label addr1 = new Label(2,1,"湖南株洲");
Label time1 = new Label(3,1,"25");
sheet.addCell(type1);
sheet.addCell(poneNo1);
sheet.addCell(addr1);
sheet.addCell(time1);
workbook.write();
workbook.close();
}
}
三、对原有的excel文件进行修改
基本步骤:
1.获得要修改的文件;
2.为要修改的文件创建一个副本;
3.对副本进行操作;
4.讲副本写到原有的文件中。
具体代码如下:
package com.test;
import java.io.File;
import java.io.IOException;
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 ExcelUpdate {
/**
* @param args
* @throws IOException
* @throws BiffException
* @throws WriteException
* @throws RowsExceededException
*/
public static void main(String[] args) throws BiffException, IOException, RowsExceededException, WriteException {
// TODO Auto-generated method stub
//获得文件
Workbook wb = Workbook.getWorkbook(new File("F:/shar/test/write.xls"));
//打开文件的一个副本,并且指定数据写回到原文件
WritableWorkbook workbook = Workbook.createWorkbook(new File("F:/shar/test/write.xls"),wb);
//添加一个工作表
WritableSheet sheet = workbook.createSheet("第二页", 1);
//添加一个单元格
Label label = new Label(0,0,"第二页测试数据");
sheet.addCell(label);
workbook.write();
workbook.close();
}
}
另可以设置单元格的字体以及对齐方式,代码如下:
package com.test;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class FontFormat {
/**
* @param args
* @throws IOException
* @throws WriteException
* @throws RowsExceededException
*/
public static void main(String[] args) throws IOException, RowsExceededException, WriteException {
// TODO Auto-generated method stub
WritableWorkbook workbook = Workbook.createWorkbook(new File("F:/shar/test/font.xls"));
//生成第一页的工作表,参数为0说明是第一页
WritableSheet sheet = workbook.createSheet("第一页", 0);
//设置字体格式(字体为TIMES,大小为16磅,加粗)
WritableFont font = new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
WritableCellFormat format = new WritableCellFormat(font);
//设置数据的对齐方式
//水平居中
format.setAlignment(Alignment.CENTRE);
//垂直居中
format.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置自动还行
format.setWrap(true);
//指明单元格的位置是第一行第一列,第一个参数为列
Label type = new Label(0,0,"通话类型",format);
//将单元格加到工作表中
sheet.addCell(type);
//数字类型
//jxl.write.Number number = new jxl.write.Number(0,1,789.123);
Label type1 = new Label(0,1,"主叫");
sheet.addCell(type1);
workbook.write();
workbook.close();
}
}