excelcellbase.java

关于excl操作的类,数据输出到excel,并有一定格式.

/**
 * EXCEL基础控制类
 * 
 * @Editor    
 * 
 */

import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Format;
import jxl.write.DateTime;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import jxl.write.Formula;
import jxl.FormulaCell;

public class ExcelCellBase {
	private jxl.format.CellFormat       cellformat;        // 单元格格式
	private jxl.CellType                celltype;          // 单元格类型
	private jxl.Cell                    realcell;          // 单元格
	private jxl.format.Font             font;              // 字体
	private String                      value;             // 字符型值
	private int                         boldweight;       // 粗体号
	private jxl.format.Colour           fcolour;          // 字体颜色
	private String                      fontname;         // 字体名
	private String                      contents;         // 单元格内容 
	private int                         pointsize;        // 字体镑值
	private jxl.format.UnderlineStyle    underline;       // 下划线 
	private boolean                     italic;          // 斜体
	private boolean                     ishidden;        // 是否隐藏
	private jxl.format.Alignment         align;           // 水平对齐
	private jxl.format.VerticalAlignment valign;          //垂直对齐
	private BorderLineStyle              boardlinestyle1;  //左边框线形 
	private BorderLineStyle              boardlinestyle2;  //右边框线形 
	private BorderLineStyle              boardlinestyle3;  //下边框线形 
	private BorderLineStyle              boardlinestyle4;  //上边框线形 
    private boolean                     wrap;             //是否自动换行 
	
	public ExcelCellBase() {
		super();
	}

    public void setValue(String newvalue){         
    	 value=newvalue;
    }
	public void setWrap(boolean newwrap){        
		 wrap=newwrap;
	}
	public String getValue(){                      
		 return value;
	}
	public void setPointSize(int newpointsize){   
		 pointsize=newpointsize;
	}
	public int getPointSize(){                    
		 return pointsize;
	}
	public int getValignValue(jxl.format.VerticalAlignment valign){ 
		 return valign.getValue();
	}
	public jxl.format.VerticalAlignment getValign(){ 
		 return valign;
	}
	public jxl.format.Alignment getAlign(){ 
		 return align;
	}
	public int getAlignValue(jxl.format.Alignment align){ 
		 return align.getValue();
	}
	public boolean getItalic(){ 
		 return italic;
	}
	public void setItalic(boolean newboolean){ 
		 italic=newboolean;
		  
	}
	public int getBoldweight(){ 
		 return boldweight;
	}
	public void setBoldweight(int newboldweight){ 
		boldweight=newboldweight;
	}
    
	public ExcelCellBase getcellAttribute(ExcelCellBase cellAttribute, jxl.Cell cell) throws NullPointerException{
		 realcell = cell;
		 cellformat=cell.getCellFormat();
		 celltype=cell.getType();
		 font=cellformat.getFont();
		 boldweight=font.getBoldWeight();
		 fcolour=font.getColour();
		 fontname=font.getName();
		 ishidden =cell.isHidden();
         contents  =cell.getContents();
         pointsize=font.getPointSize();
		 underline=font.getUnderlineStyle();
		 italic=font.isItalic();
		 align=cellformat.getAlignment();
		 valign=cellformat.getVerticalAlignment();
		 value=cell.getContents();
		 boardlinestyle1 =cellformat.getBorder(Border.LEFT);
		 boardlinestyle2 =cellformat.getBorder(Border.RIGHT);
		 boardlinestyle3 =cellformat.getBorder(Border.BOTTOM);
		 boardlinestyle4 =cellformat.getBorder(Border.TOP);
		 wrap =cellformat.getWrap();
		 return cellAttribute;
	} 
	public void mergerCell(WritableSheet writesheet,jxl.Sheet readsheet){

			try {
					jxl.Range range[] = readsheet.getMergedCells();
					int rangelen = range.length;
					for (int i = 0; i < rangelen;i++){
						jxl.Cell celltop = range[i].getTopLeft() ; 
						Cell cellbottom = range[i].getBottomRight() ;
						writesheet.mergeCells(celltop.getColumn(),celltop.getRow(),cellbottom.getColumn(),cellbottom.getRow());
				   }
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
	}
	public WritableSheet setcellAttribute(WritableSheet writesheet,ExcelCellBase cellattribute,Cell cell,
			int column,int row){		
		 
		    try{	
		        
		         WritableFont.FontName fname=jxl.write.WritableFont.createFont(fontname);
				 
				 if((cellattribute.celltype==CellType.STRING_FORMULA)||
				    (cellattribute.celltype==CellType.NUMBER_FORMULA)
				    ||(cellattribute.celltype==CellType.BOOLEAN_FORMULA)||
				    (cellattribute.celltype==CellType.DATE_FORMULA)||
				    (cellattribute.celltype==CellType.FORMULA_ERROR))
				 {
					FormulaCell nfc = (FormulaCell) realcell;
					Formula f = new Formula(column,row, nfc.getFormula());
				    writesheet.addCell(f);
				 }	             
			     if(cellattribute.celltype==CellType.LABEL) {             
			        if (cellattribute.boldweight > 400 ){
			            jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.BOLD,italic,underline,fcolour);
			            jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
			            wcfFC.setAlignment(align);			  
						wcfFC.setVerticalAlignment(valign);
						wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						wcfFC.setBorder(Border.TOP,boardlinestyle4);
						wcfFC.setWrap(wrap);
			            jxl.write.Label labelCF = new jxl.write.Label(column, row,value , wcfFC);				  
			            writesheet.addCell(labelCF);

			        }else{
				        jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.NO_BOLD,italic,underline,fcolour);
				        jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				        wcfFC.setAlignment(align);	
						wcfFC.setVerticalAlignment(valign);
						wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						wcfFC.setBorder(Border.TOP,boardlinestyle4);
						wcfFC.setWrap(wrap);
						
				        jxl.write.Label labelCF = new jxl.write.Label(column, row,value , wcfFC);					
				        writesheet.addCell(labelCF);

			        }
		       }          
		       else if(cellattribute.celltype== CellType.DATE){
			         if (cellattribute.boldweight > 400 ){
				         DateTime dt = (DateTime) cell;
				         java.util.Date datevalue = dt.getDate();
				         jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.BOLD,italic,underline,fcolour);
				         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				         wcfFC.setAlignment(align);				  
						 wcfFC.setVerticalAlignment(valign);
						 wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						 wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						 wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						 wcfFC.setBorder(Border.TOP,boardlinestyle4);
						 wcfFC.setWrap(wrap);
				         jxl.write.DateTime dateCF = new jxl.write.DateTime(column, row,datevalue , wcfFC);				  
				         writesheet.addCell(dateCF);

			         }else{
				         DateTime dt = (DateTime) cell;
				         java.util.Date datevalue = dt.getDate();
				         jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.NO_BOLD,italic,underline,fcolour);
				         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				         wcfFC.setAlignment(align);				  
						 wcfFC.setVerticalAlignment(valign);
						 wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						 wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						 wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						 wcfFC.setBorder(Border.TOP,boardlinestyle4);
						 wcfFC.setWrap(wrap);
				         jxl.write.DateTime dateCF = new jxl.write.DateTime(column, row,datevalue , wcfFC);				  
				         writesheet.addCell(dateCF);

			         }
			   }
		       else if(cellattribute.celltype==CellType.NUMBER){				     
			         if (cellattribute.boldweight > 400 ){
				         double numvalue = Double.parseDouble( value);				   
				         jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.BOLD,italic,underline,fcolour);
				         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				         wcfFC.setAlignment(align);					 
						 wcfFC.setVerticalAlignment(valign);
						 wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						 wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						 wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						 wcfFC.setBorder(Border.TOP,boardlinestyle4);
						 wcfFC.setWrap(wrap);
		                 jxl.write.Number labelCF = new jxl.write.Number(column, row,numvalue , wcfFC);					
				         writesheet.addCell(labelCF);
			         }else{
				         double numvalue = Double.parseDouble( value);				   
				         jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.NO_BOLD,italic,underline,fcolour);
				         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				         wcfFC.setAlignment(align);					
						 wcfFC.setVerticalAlignment(valign);
						 wcfFC.setBorder(Border.LEFT,boardlinestyle1);
						 wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
						 wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
						 wcfFC.setBorder(Border.TOP,boardlinestyle4);
						 wcfFC.setWrap(wrap);

				         jxl.write.Number labelCF = new jxl.write.Number(column, row,numvalue , wcfFC);					
				         writesheet.addCell(labelCF);

			         }
		      }else {
			       if (cellattribute.boldweight > 400 ){
				      jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.BOLD,italic,underline,fcolour);
				      jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				      wcfFC.setAlignment(align);				  
					  wcfFC.setVerticalAlignment(valign);
					  wcfFC.setBorder(Border.LEFT,boardlinestyle1);
					  wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
					  wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
					  wcfFC.setBorder(Border.TOP,boardlinestyle4);
					  wcfFC.setWrap(wrap);

				      jxl.write.Label labelCF = new jxl.write.Label(column, row,value , wcfFC);				  
				      writesheet.addCell(labelCF);

			       }else{
				      jxl.write.WritableFont wfc=new jxl.write.WritableFont(fname,pointsize, jxl.write.WritableFont.NO_BOLD,italic,underline,fcolour);
			 	      jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				      wcfFC.setAlignment(align);					
					  wcfFC.setVerticalAlignment(valign);
					  wcfFC.setBorder(Border.LEFT,boardlinestyle1);
					  wcfFC.setBorder(Border.RIGHT,boardlinestyle2);
					  wcfFC.setBorder(Border.BOTTOM,boardlinestyle3);
					  wcfFC.setBorder(Border.TOP,boardlinestyle4);
					  wcfFC.setWrap(wrap);
				  
                      jxl.write.Label labelCF = new jxl.write.Label(column, row,value , wcfFC);					
				      writesheet.addCell(labelCF);

			       }
		      }
	     }
	     catch (Exception e){
		    e.printStackTrace();
	     }
	     return writesheet;
    }
}