package cn.com.capcloud.core.execl.xls;

import java.io.File;


import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExeclFileObject {
	jxl.write.WritableWorkbook wwb;
	private JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}


	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}


	/**
	 * 输出流
	 */
	public ExeclFileObject(OutputStream os){
		try {
			this.setWwb(jxl.Workbook.createWorkbook(os));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 根据要输出的文件初始化
	 */
	public ExeclFileObject(File f){
		try {
			this.setWwb(jxl.Workbook.createWorkbook(f));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 根据模版文件初始化execl文件
	 */
	public ExeclFileObject(File inFile,File outFile){
		try {
			jxl.Workbook workBook=jxl.Workbook.getWorkbook(inFile);
			this.setWwb(jxl.Workbook.createWorkbook(outFile,workBook));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建sheet
	 */
	public WritableSheet createSheet(int sheetIndex,String sheetName) {
		return this.getWwb().createSheet(sheetName, sheetIndex);
	}
	
	/**
	 * 删除sheet
	 */
	public void removeSheet(int sheetIndex){
		this.getWwb().removeSheet(sheetIndex);
	}
	
	public void removeSheet(String sheetName){
		for(int i=0;i<this.getWwb().getSheets().length;i++){
			if(this.getWwb().getSheet(i).getName().equals(sheetName)){
				this.removeSheet(i);
			}
		}
	}
	
	/**
	 * 把List<CellForm>的内容写到sheet中
	 */
	public void writeSheet(List<CellForm> list,String sheetName,int sheetIndex) 
		throws RowsExceededException, NumberFormatException, WriteException{
		WritableSheet writableSheet=null;
		if(this.getWwb().getSheet(sheetName)!=null){
			writableSheet=this.getWwb().getSheet(sheetName);
		}else{
			writableSheet=this.createSheet(sheetIndex, sheetName);
		}
		for(int i=0;i<list.size();i++){
			if(list.get(i).getType()==CellFormat.EXECL_CELL_DATA_TYPE_OF_NUMBER){
				writableSheet.addCell(
					new jxl.write.Number(list.get(i).getCol(),list.get(i).getRow(),
						Double.parseDouble(list.get(i).getCellValue()),list.get(i).getHeaderformat()));
			}else{
				writableSheet.addCell(						
					new jxl.write.Label(list.get(i).getCol(),list.get(i).getRow(),
						list.get(i).getCellValue(),list.get(i).getHeaderformat()));
			}
		}
	}
	/**
	 * 根据sheet块写sheet，如sheet名称不存在，则新建sheet
	 */
	public void writeSheet(SheetFace sheet,String sheetName,int sheetIndex) 
		throws RowsExceededException, NumberFormatException, WriteException{
		WritableSheet writableSheet=null;
		if(this.getWwb().getSheet(sheetName)!=null){
			writableSheet=this.getWwb().getSheet(sheetName);
		}else{
			writableSheet=this.createSheet(sheetIndex, sheetName);
		}
		List<CellForm> list=sheet.getSheetList();
		for(int i=0;i<list.size();i++){
			if(list.get(i).getType()==CellFormat.EXECL_CELL_DATA_TYPE_OF_NUMBER){
				writableSheet.addCell(
					new jxl.write.Number(list.get(i).getCol(),list.get(i).getRow(),
						Double.parseDouble(list.get(i).getCellValue()),list.get(i).getHeaderformat()));
			}else{
				writableSheet.addCell(						
					new jxl.write.Label(list.get(i).getCol(),list.get(i).getRow(),
						list.get(i).getCellValue(),list.get(i).getHeaderformat()));
			}
		}
	}
	
	public void writeCol(ColFace col,String sheetName,int sheetIndex) throws RowsExceededException, NumberFormatException, WriteException{
		WritableSheet sheet=this.getWwb().getSheet(sheetName);
		this.writeSheet(col.getCellFormList(sheet,this.jdbcTemplate), sheetName, sheetIndex);
	}
	/**
	 * 通过模版copy sheet
	 */
	public void copySheet(String srcSheetName,String newSheetName,int newSheetIndex){
		this.getWwb().copySheet(srcSheetName, newSheetName, newSheetIndex);
	}
	/**
	 * 关闭execl文件，在关闭时，才真正的输出
	 */
	public void CloseFile(){
		try {
			this.getWwb().write();
			this.getWwb().close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public jxl.write.WritableWorkbook getWwb() {
		return wwb;
	}
	public void setWwb(jxl.write.WritableWorkbook wwb) {
		this.wwb = wwb;
	}
}
