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

import java.util.ArrayList;


import java.util.List;
import java.util.Map;

import jxl.write.WritableCellFormat;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;



public class SqlSheet implements SheetFace {
	Logger logger=Logger.getLogger(SqlSheet.class);
	
	private JdbcTemplate jdbcTemplate;
	private String sql;
	private String[] titleNames;
	private String[] sqlTitleFileds;
	private int[] colTypes;
	boolean isSeq;
	private WritableCellFormat cellFormatValue;
	public WritableCellFormat getCellFormatValue() {
		return cellFormatValue;
	}
	public void setCellFormatValue(WritableCellFormat cellFormatValue) {
		this.cellFormatValue = cellFormatValue;
	}
	public String[] getTitleNames() {
		return titleNames;
	}
	public void setTitleNames(String[] titleNames) {
		this.titleNames = titleNames;
	}
	public String[] getSqlTitleFileds() {
		return sqlTitleFileds;
	}
	public void setSqlTitleFileds(String[] sqlTitleFileds) {
		this.sqlTitleFileds = sqlTitleFileds;
	}
	public boolean isSeq() {
		return isSeq;
	}
	public void setSeq(boolean isSeq) {
		this.isSeq = isSeq;
	}
	public String getSql() {
		return sql;
	}
	public void setSql(String sql) {
		this.sql = sql;
	}
	public int[] getColTypes() {
		return colTypes;
	}
	public void setColTypes(int[] colTypes) {
		this.colTypes = colTypes;
	}
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public List<CellForm> getSheetList() {
		if(this.getCellFormatValue()==null){
			this.setCellFormatValue(CellFormat.BODY_CHAR_FLOAT);
		}
		List<CellForm> list=new ArrayList<CellForm>();
		List<Map<String, Object>> queryList=this.getJdbcTemplate().queryForList(this.getSql());
		for(int i=0;i<this.getTitleNames().length;i++){
			CellForm cellForm=new CellForm();
			cellForm.setCol(i);
			cellForm.setRow(0);
			cellForm.setHeaderformat(CellFormat.TITLE_HEADER);
			cellForm.setType(CellFormat.EXECL_CELL_DATA_TYPE_OF_STRING);
			cellForm.setCellValue(this.getTitleNames()[i]);
			list.add(cellForm);
		}
		for(int i=1;i<queryList.size()+1;i++){
			Map map=queryList.get(i-1);
			for(int j=0;j<this.getColTypes().length;j++){
				if(this.getColTypes()[j]==CellFormat.EXECL_CELL_DATA_TYPE_OF_STRING){
					CellForm cellForm=new CellForm();
					cellForm.setCol(j);
					cellForm.setRow(i);
					cellForm.setHeaderformat(this.getCellFormatValue());
					cellForm.setType(CellFormat.EXECL_CELL_DATA_TYPE_OF_STRING);
					cellForm.setCellValue(String.valueOf(map.get(this.getSqlTitleFileds()[j])));
					list.add(cellForm);
				}else{
					CellForm cellForm=new CellForm();
					cellForm.setCol(j);
					cellForm.setRow(i);
					cellForm.setHeaderformat(this.getCellFormatValue());
					cellForm.setType(CellFormat.EXECL_CELL_DATA_TYPE_OF_NUMBER);
					cellForm.setCellValue(String.valueOf((java.math.BigDecimal)map.get(this.getSqlTitleFileds()[j])));
					list.add(cellForm);
				}
			}
		}
		return list;
		
	}

}
