readandwriteexcel.java

java相关的j2me,j2se的一些相关资料

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.Vector;
import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.format.PageOrientation;
import jxl.format.Pattern;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public class ReadAndWriteExcel {
    
    public ReadAndWriteExcel() {
    }
    
    public static void main(String args[]){
//////////////////////读excel文件
        Workbook workbook = null;  //
        Sheet sheet;        //
        Cell  cell;         //
        try {
            File f = new File("jxlrwtest.xls");  //取得文件
            InputStream fi = new FileInputStream("jxlrwtest.xls");
            workbook = Workbook.getWorkbook(fi); //从输入流创建一个Workbook
            sheet = workbook.getSheet(0);  //获取第一张工作表
            int rows = sheet.getRows(); //获取工作表的行数
            int cols = sheet.getColumns(); //获取工作表的列数
            for(int i=0;i<rows;i++){
                for(int j=0;j<cols;j++){
                    cell = sheet.getCell(j,i);  //先列后行,获取单元格
                    System.out.print(i+"行,"+j+"列="+cell.getContents()+"    ");  //获取单元格内容
                }
                System.out.println();
            }
        } catch (IOException ex) { } 
          catch (BiffException ex){ }
//////////////////////////写excel文件
//利用已经创建的Excel工作薄创建新的可写入的Excel工作薄,已创建的工作表的数据会写入新的Excel工作簿
        try {
            WritableWorkbook writeWorkbook = Workbook.createWorkbook(new File("Book2.xls"),workbook);
            writeWorkbook.createSheet("我创建的sheet",0);
            jxl.write.WritableSheet writeSheet = writeWorkbook.getSheet(0); //读取第一张工作表
            jxl.write.WritableCell wc = writeSheet.getWritableCell(0, 0);     //获取第一个单元格对象
            if(wc.getType() == CellType.LABEL){ //判断单元格类型
                Label l = (Label)wc;
                l.setString("我的 最爱"); //设置单元格数据
            }
            writeWorkbook.write(); //将数据写入单元格
            writeWorkbook.close();
        } catch (IOException ex) { }
          catch (WriteException ex) { }
////////////////写一个新的Excel文件.
        try {
            File f = new File("newExcel.xls");
            if(f.exists()==false){f.createNewFile();} else{f.delete();f.createNewFile();}  //创建文件和删除文件
            WritableWorkbook wbook = Workbook.createWorkbook(f);  //创建一个可写的excel文档.
            wbook.createSheet("销售记录明细",0);    //创建一个新的工作表
            WritableSheet writableSheet = wbook.getSheet(0);   //取得工作表(销售记录明细)
            
            String title[] = new String[]{"大区","城市","店面名称","手机","日期","产品ID","产品名称","售出数量"};
            WritableFont font = new WritableFont(WritableFont.COURIER,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE);
            WritableCellFormat wcfFC= new WritableCellFormat(font);    //用于设置单元格的样式,包括字体和颜色等.                    
            wcfFC.setBackground(Colour.TEAL,Pattern.SOLID);  //设置单元格颜色
            wcfFC.setAlignment(Alignment.CENTRE);    //设置单元格字显示位置
            wcfFC.setBorder(jxl.format.Border.BOTTOM,BorderLineStyle.HAIR); //设置边框
            wcfFC.setOrientation(Orientation.PLUS_45);  //单元格字显示角度
            for(int i=0;i<title.length;i++){
                Label l = new Label(i,0,title[i]);
                l.setCellFormat(wcfFC);     //设置单元格的样式/格式
                writableSheet.addCell(l);
            }
            
            writableSheet.mergeCells(0,1,1,2); //合并单元格
            Label la = new Label(0,1,"合并的单元格"); //合并后,必须以起始单元格开始写数据.
            
            WritableCellFeatures features = new WritableCellFeatures();
            Vector v =  new Vector();
            v.add("下拉数据1");
            v.add("下拉数据2");
            v.add("下拉数据3");
            v.add("下拉数据4");
            v.add("下拉数据5");
            v.add("下拉数据6");
            features.setDataValidationList(v);
            la.setCellFeatures(features);            
            writableSheet.addCell(la); //必须先合并单元格,再写数据,不能先写数据,再合并单元格.
            
            WritableImage image = new WritableImage(1,1,5,1,new File("s.png"));//在第2列第1行存放图像，图像占5列1行大小.
            writableSheet.addImage(image);//添加图像
            
            WritableHyperlink link = new WritableHyperlink(6,6,new URL("http://www.sina.com.cn")); //第7列7行，超链接
            link.setDescription("新浪网站");  //超链接显示名
            writableSheet.addHyperlink(link); //添加
            
            wbook.write();   //必须使用此,才能将数据写入excel文件.
            wbook.close();
        } catch (FileNotFoundException ex) { } 
          catch (IOException ex) { } 
          catch(WriteException ex){ }
    }
}