利用JXL实现对于excel-2000/2003版本的文件进行读写操作

package com.softeem.excel;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

public class TypeTools {//工具类

	static SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");//日期转换格式
//	private static DateFormat format;
	
	public static int getInt(String s){//字符串转int
		if(Pattern.matches("^\\d*$",s)){
			return Integer.parseInt(s);
		}
		return 0;
	}
	
	public static double getDouble(String s){//字符转转double
		if(Pattern.matches("^\\d+\\.??\\d+$", s)){
			return Double.parseDouble(s);
		}
		return 0.0;
	}
	//"" != null
	public static Date getDate(String s)//字符串转Date
	{
		Date date = null;
		try {
			if(s != null && !"".equals(s)){
				date = sdf.parse(s);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}
	
	public static BigDecimal getBigDecimal(String s)//字符串转BigDecimal
	{
		if(Pattern.matches("^\\d+\\.??\\d+$", s)){
			return new BigDecimal(s);
		}
		return new BigDecimal("0.0");
	}
	public static String getSring(Date date){//日期转字符串
		if(date != null){
			return sdf.format(date);
		}
		return null;
		
	}
}


package com.softeem.excel;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 利用JXL实现对于excel-2000/2003版本的文件进行读写操作
 * @author mrchai
 */
public class ExcelTest {
	
	public List<Goods> read(File file){
		List<Goods> list = new ArrayList<>();
		Goods goods = null;
		Workbook workbook = null;
		try {
			//创建一个工作簿
			workbook = Workbook.getWorkbook(file);
			//获取所有表单对象
//			Sheet[] sheets = workbook.getSheets();
			//获取指定索引的表单
			Sheet sheet = workbook.getSheet(0);
			//获取指定名称的表单
//			Sheet sheet = workbook.getSheet("Sheet1");
			//获取总行数
			int rows = sheet.getRows();
			for (int i = 1; i < rows; i++) {
				goods = new Goods();
				String s1 = sheet.getCell(0, i).getContents();	//编号
				String s2 = sheet.getCell(1, i).getContents();	//商品名
				String s3 = sheet.getCell(2, i).getContents();	//单价
				String s4 = sheet.getCell(3, i).getContents();	//折扣
				String s5 = sheet.getCell(4, i).getContents();	//时间
				String s6 = sheet.getCell(5, i).getContents();	//库存
				
				goods.setGname(s2);
				goods.setGno(TypeTools.getInt(s1));
				goods.setPrice(TypeTools.getBigDecimal(s3));
				goods.setOffset(TypeTools.getDouble(s4));
				goods.setDate(TypeTools.getDate(s5));
				goods.setCount(TypeTools.getInt(s6));
				
				list.add(goods);
			}
		} catch (BiffException | IOException e) {
			e.printStackTrace();
		} finally{
			if(workbook != null) workbook.close();
		}
		
		return list;
	}

	public void createExcel(List<Goods> goods,File dir){
		//根据系统时间生成一个excel文件
		File file = new File(dir,System.currentTimeMillis()+".xls");
		WritableWorkbook wwb = null;
		try {
			//创建一个可写工作簿
			wwb = Workbook.createWorkbook(file);
			//获取一个可写的表单
			WritableSheet sheet = wwb.createSheet("商品信息表", 0);
			//创建单元格，指定列，行，文本内容
			Label c1 = new Label(0, 0, "编号");
			Label c2 = new Label(1, 0, "商品名");
			Label c3 = new Label(2, 0, "单价");
			Label c4 = new Label(3, 0, "折扣");
			Label c5 = new Label(4, 0, "上架时间");
			Label c6 = new Label(5, 0, "库存");
			//将单元格加入表单
			sheet.addCell(c1);
			sheet.addCell(c2);
			sheet.addCell(c3);
			sheet.addCell(c4);
			sheet.addCell(c5);
			sheet.addCell(c6);
			
			//添加数据
			for(int i = 0; i < goods.size();i++){
				c1 = new Label(0,i+1,goods.get(i).getGno()+"");
				c2 = new Label(1,i+1,goods.get(i).getGname());
				c3 = new Label(2,i+1,goods.get(i).getPrice().toString());
				c4 = new Label(3,i+1,goods.get(i).getOffset()+"");
				c5 = new Label(4,i+1,TypeTools.getSring(goods.get(i).getDate()));
				c6 = new Label(5,i+1,goods.get(i).getCount()+"");
				sheet.addCell(c1);
				sheet.addCell(c2);
				sheet.addCell(c3);
				sheet.addCell(c4);
				sheet.addCell(c5);
				sheet.addCell(c6);
			}
			
			//写入
			wwb.write();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally{
			try {
				if(wwb != null)wwb.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	public static void main(String[] args) {
		File f = new File("goodslist.xls");
		List<Goods> goods = new ExcelTest().read(f);//读取goodslist中的数据
		
		new ExcelTest().createExcel(goods, new File("D:\\Users"));//写入
	}
}


package com.softeem.excel;

import java.math.BigDecimal;
import java.util.Date;

public class Goods {
	private int gno;// id
	private String gname;// 商品名
	private BigDecimal price;// 单价
	private double offset;// 折扣率
	private Date date;// 日期
	private int count;// 库存

	public Goods() {
		// TODO Auto-generated constructor stub
	}

	public Goods(int gno, String gname, BigDecimal price, double offset,
			Date date, int count) {
		super();
		this.gno = gno;
		this.gname = gname;
		this.price = price;
		this.offset = offset;
		this.date = date;
		this.count = count;
	}

	public int getGno() {
		return gno;
	}

	public void setGno(int gno) {
		this.gno = gno;
	}

	public String getGname() {
		return gname;
	}

	public void setGname(String gname) {
		this.gname = gname;
	}

	public BigDecimal getPrice() {
		return price;
	}

	public void setPrice(BigDecimal price) {
		this.price = price;
	}

	public double getOffset() {
		return offset;
	}

	public void setOffset(double offset) {
		this.offset = offset;
	}

	public Date getDate() {
		return date;
	}

	public void setDate(Date date) {
		this.date = date;
	}

	public int getCount() {
		return count;
	}

	public void setCount(int count) {
		this.count = count;
	}

	@Override
	public String toString() {
		return "Goods [gno=" + gno + ", gname=" + gname + ", price=" + price
				+ ", offset=" + offset + ", date=" + date + ", count=" + count
				+ "]";
	}

}