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
+ "]";
}
}
