java excel api 使用

author:Rubber
link: http://www.ibm.com/developerworks/cn/java/l-javaExcel/


自己实践了一下，writeWorkBook()写一个新的excel，updateWorkBook()更新原来excel，readWorkBook()读取。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Test1 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		new Test1().updateWorkBook();
	}

	public void readWorkBook(){
		Workbook rwb = null;
		try {
			InputStream is = new FileInputStream(new File("Book1.xls"));
			rwb = Workbook.getWorkbook(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		//获取第一张Sheet表
		Sheet rs = rwb.getSheet(0);
		//获取第一行，第一列的值
		Cell c00 = rs.getCell(0, 0);
		String strc00 = c00.getContents();
		//获取第一行，第二列的值
		Cell c10 = rs.getCell(1, 0);
		String strc10 = c10.getContents();
		//获取第二行，第二列的值
		Cell c11 = rs.getCell(1, 1);
		String strc11 = c11.getContents();
		System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());
		System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());
		System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());

		System.out.println(rs.getColumns());
		System.out.println(rwb.getVersion());
		rwb.close();
	}

	public void updateWorkBook(){
		try {
			Workbook wb = Workbook.getWorkbook(new File("Book1.xls"));
			WritableWorkbook wwb = Workbook.createWorkbook(new File("Book1.xls"), wb);

			//读取第一张工作表
			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对象
			wb.close();

		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void writeWorkBook(){
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(new File("Book2.xls"));
			WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);

			//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(2, 0, "This is a Label Cell", wcfFC);
			ws.addCell(labelCFC);
			//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);

			//写入Exel工作表
			wwb.write();
			//关闭Excel工作薄对象
			wwb.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}

	}
}

把excel直接输出到网页，让客户端直接调office来打开
Test.java

import java.io.OutputStream;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.WritableFont;

public class Test {
	public void writeExcel(OutputStream os) throws Exception {
		jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
		jxl.write.WritableSheet ws = wwb.createSheet("TestSheet1", 0);
		jxl.write.Label labelC = new jxl.write.Label(0, 0, "我爱中国");
		ws.addCell(labelC);
		jxl.write.WritableFont wfc = new jxl.write.WritableFont(
				WritableFont.ARIAL, 20, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				wfc);
		wcfFC.setBackground(jxl.format.Colour.RED);
		labelC = new jxl.write.Label(6, 0, "中国爱我", wcfFC);
		ws.addCell(labelC);
		// 写入Exel工作表
		wwb.write();
		// 关闭Excel工作薄对象
		wwb.close();
	}

}

showexcel.jsp

<%@ page language="java"  pageEncoding="UTF-8"%>
<%@ page import="chuck.jexcel.Test" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>


    <title>index</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    

	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>

  <body>
   <%
   	response.setContentType("application/vnd.ms-excel");
	new Test().writeExcel(response.getOutputStream());
	out.clear();
	out = pageContext.pushBody();
   %>
  </body>
</html>