java excel api 使用
author:Rubberlink: 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 blocke.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>
页:
[1]