Java Excel

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类型。
示例代码片段如下

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();