excel读写

excel表格读写

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.format.CellFormat;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 ExcelDemo {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.getStringDate(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("src/goodslist.xls");List<Goods> goods = new ExcelDemo().read(f);new ExcelDemo().createExcel(goods, new File("E:\\A"));}}