Java Excel是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为是使用Java编写的,所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。
搭建环境 : 将下载后的文件解包,得到jxl.jar,放入classpath,安装就完成了。
目前最新版本是jexcelapi_2_6_12 jxl常用方法介绍:
File file = new File(this.getExcelPath()); // 构建Workbook对象, 只读Workbook对象 // 直接从本地文件创建
Workbook Workbook rwb = Workbook.getWorkbook(file);//从file对象中创建Workbook 读取Excel //
InputStream is = new FileInputStream(this.ExcelPath); //
Workbook rwb = Workbook.getWorkbook(is); // 从输入流创建Workbook
Sheet sheet = rwb.getSheet("Sheet1");//由指定sheet名获得工作簿表单对象 //
Sheet sheet = rwb.getSheet(0);//由指定sheet序号获得工作簿表单对象,从0开始
String[] sheetname = rwb.getSheetNames();//获取所有的sheet名称,返回一个String数组
Sheet[] sheets = rwb.getSheets();//获取所有的sheet对象,返回一个Sheet数组
int cmn = sheet.getColumns();//获取表格总列数
int rows = sheet.getRows();//获取表格的总行数
LabelCell label = (LabelCell) sheet.getCell(0, 0);//第一个参数代表列 第二个参数代表行
String value = label.getString();//获取单元格数据
rwb.close();//关闭工作薄 流 //1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
ws.addCell(labelC); //添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true); jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF); //添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(1, 0, "This is a Label Cell", wcfFC);
ws.addCell(labelCF); //2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN); //添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF); //3.添加Boolean对象
jxl.write.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对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
1.生成新的Excel工作薄 与读取Excel工作表相似,首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象,这里要注意的是,只能通过API提供的工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,因为类WritableWorkbook的构造函数为protected类型。示例代码片段如下:view plaincopy to clipboardprint?
import java.io.*;
import jxl.*;
import jxl.write.*;
… … … …
try { //构建Workbook对象, 只读Workbook对象
//Method 1:创建可写入的Excel工作薄
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
//Method 2:将WritableWorkbook直接写入到输出流 /* OutputStream os = new FileOutputStream(targetfile); jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); */ }
catch (Exception e) { e.printStackTrace(); }
import java.io.*;
import jxl.*;
import jxl.write.*;
… … … …
try { //构建Workbook对象, 只读Workbook对象 //Method 1:创建可写入的Excel工作薄 jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile)); //Method 2:将WritableWorkbook直接写入到输出流 /* OutputStream os = new FileOutputStream(targetfile); jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); */ }
catch (Exception e) { e.printStackTrace(); }
2.修改Excel //创建只读的Excel工作薄的对象
jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(sourcefile)); //创建可写入的Excel工作薄对象 jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile), rw);
//读取第一张工作表
jxl.write.WritableSheet ws = wwb.getSheet(0);
//获得第一个单元格对象
jxl.write.WritableCell wc = ws.getWritableCell(0, 0);
//判断单元格的类型, 做出相应的转化
if(wc.getType() == CellType.LABEL) {
Label l = (Label)wc; l.setString("The value has been modified.");
}
//写入Excel对象
wwb.write();
//关闭可写入的Excel对象
wwb.close();
//关闭只读的Excel对象
rw.close();