对Excel文件的读取、写入、拷贝

package com.java;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.servlet.ServletException;

import jxl.Cell;
import jxl.CellType;
import jxl.Image;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtil {

	public static void main(String[] args) throws ServletException, 
			RowsExceededException, WriteException, BiffException, IOException {
		//写入excel
		String fileName = "F:"+File.separator+"write.xls";
		writeExcel(fileName);

		//读取excel
		readExcel(fileName);
		String targetfile = "F:"+File.separator+"copy.xls";
		copyExcel(fileName, targetfile);
	}
	/**
	 * 读取excel
	 * @param fileName
	 */
	public static void readExcel(String fileName){
		Workbook rwb;
		try {
			//excel源文件
			FileInputStream is = new FileInputStream(fileName);
			rwb = Workbook.getWorkbook(is);
			int sheetNum = rwb.getNumberOfSheets();
			for (int i = 0; i < sheetNum; i++) {
				Sheet st = rwb.getSheet(i);
				int rows=st.getRows();
	            int cols=st.getColumns();
	            //获取某一列的所有单元格，返回的是单元格对象数组
	            Cell[] rowCs = st.getColumn(0);
	            //获取某一行的所有单元格，返回的是单元格对象数组
	            Cell[] columns = st.getRow(0);
	            //Cell getCell(int column, int row)	            
	            System.out.println("当前工作表的名字:"+st.getName());
	            System.out.println("总行数:"+rows);
	            System.out.println("总列数:"+cols);
	            for (int j = 0; j < rows; j++) {
	            	Cell[] cells = st.getRow(j);
	            	for (Cell c : cells) {
	            		System.out.print(c.getContents());
	            		System.out.print("\t");
					}
	            	System.out.println();	            	
				}
	            // 获得sheet所包含的图片数
	            int imgNum = st.getNumberOfImages();
	            //循环sheet的所有图片
	            for (int j = 0; j < imgNum; j++) {
	            	Image image = st.getDrawing(j);
	            	byte[] imageData = image.getImageData();
	            	String imgName = image.getImageFile().getName().trim()
                    						+ ".jpg";
	            	File file = new File("F:"+File.separator + imgName);

	            	FileOutputStream os = new FileOutputStream(file);// 建立一个上传文件的输出流
	                os.write(imageData, 0, imageData.length);// 将文件写入服务器
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 写入excel
	 * @param fileName
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void writeExcel(String fileName) 
									throws RowsExceededException, WriteException{
		WritableWorkbook wwb = null;
		try {
			//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(new File(fileName));

			if(wwb!=null){
				//创建一个可写入的工作表    
				//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
				WritableSheet ws0 = wwb.createSheet("范例", 0);
				//下面开始添加单元格
				for (int i = 0; i < 10; i++) {
					for(int j=0;j<5;j++){
						//这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
						Label labelC = new Label(j, i, "第"+(i+1)+"行，第"+(j+1)+"列");
						//将生成的单元格添加到工作表中    
						ws0.addCell(labelC);
					}
				}
				WritableSheet ws = wwb.createSheet("各种类型写入", 1);
				//添加 Label 对象
				Label labelC = new Label(0, 0, "This is a Label cell");
				ws.addCell(labelC);

				//添加带有字型 Formatting 的对象
				WritableFont wf = new WritableFont(WritableFont.TIMES, 18,
							WritableFont.BOLD, true);
				WritableCellFormat wcfF = new WritableCellFormat(wf);
				Label labelCF = new Label(1, 0, "This is a Label Cell", wcfF);
				ws.addCell(labelCF);

				// 添加带有字体颜色 Formatting 的对象
				WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
							WritableFont.NO_BOLD, false,
							UnderlineStyle.NO_UNDERLINE, Colour.RED);
				WritableCellFormat wcfFC = new WritableCellFormat(wfc);
				Label labelCFC = new Label(2, 0, "This is a Label Cell", wcfFC);
				ws.addCell(labelCFC);

				//添加 Number 对象
				Number labelN = new Number(0, 1, 3.1415926);
				ws.addCell(labelN);

				// 添加带有 formatting 的 Number 对象
				NumberFormat nf = new NumberFormat("#.##");
				WritableCellFormat wcfN = new WritableCellFormat(nf);
				Number labelNF = new Number(1, 1, 3.1415926, wcfN);
				ws.addCell(labelNF);

				//添加 Boolean 对象
				Boolean labelB = new Boolean(0, 2, false);
				ws.addCell(labelB);

				//添加 DateTime 对象
				DateTime labelDT = new DateTime(0, 3, new java.util.Date());
				ws.addCell(labelDT);

				// 添加带有 formatting 的 DateFormat 对象
				DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
				WritableCellFormat wcfDF = new WritableCellFormat(df);
				DateTime labelDTF = new DateTime(1, 3, new java.util.Date(),wcfDF);
				ws.addCell(labelDTF);
				//插入图片
				File imgFile = new File("G:"+File.separator+"12.png"); 
				insertImg(ws, 0, 4, 4, 9, imgFile);
			}
			//从内存中写入文件中    
			wwb.write();
			wwb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}		
	}
	/**
	 * 拷贝excel
	 * @param sourcefile
	 * @param targetfile
	 * @throws IOException
	 * @throws BiffException
	 * @throws WriteException
	 */
	public static void copyExcel(String sourcefile,String targetfile) 
						throws IOException,BiffException, WriteException{
		// 创建只读的 Excel 工作薄的对象
		Workbook rw = Workbook.getWorkbook(new File(sourcefile));
		// 创建可写入的 Excel 工作薄对象
		WritableWorkbook  wwb = Workbook.createWorkbook(new File(targetfile), rw);

		// 读取第一张工作表
		WritableSheet ws = wwb.getSheet(0);
		// 获得第一个单元格对象
		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();
	}
	/**往Excel中插入图片 
	* @param dataSheet   待插入的工作表 
	* @param col 图片从该列开始 
	* @param row 图片从该行开始 
	* @param width 图片所占的列数 
	* @param height 图片所占的行数 
	* @param imgFile 要插入的图片文件 
	*/ 
	public static void insertImg(WritableSheet dataSheet, int col, int row, 
			int width,int height, File imgFile){    
		WritableImage img = new WritableImage(col, row, width, height, imgFile);    
		dataSheet.addImage(img);    
	}
}

jexcelapi
[url]http://www.andykhan.com/jexcelapi/[/url]