package com.yc.service.new38type;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import com.yc.action.grid.GridUtils;
import net.sf.json.JSONArray;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import com.yc.entity.Json38;
import com.yc.service.BaseService;
import com.yc.utils.Page;


@Service("type38Impl")
public class Type38Impl extends BaseService implements Type38Ifc{
	
	//获得总记录数
	public static final String COUNT = "set nocount on; select count(formId) from _sysStatis";
	//统计设计获取总记录数
	public static final String COUNTS = "set nocount on; select count(formId) from _sysStatisDetail";
	//private static final String DELETE_PANEL_DATA = "delete _sysStatis where formid in ? and StatisID in ?";

	@Override
	public List<Map<String, Object>> selecttype(int formid, String usercode, String statisIds) {
		// TODO Auto-generated method stub
		String statisidList="";
		if(StringUtils.isNotBlank(statisIds)){
			statisidList=" and isnull(StatisID,'') in ( select list from getinstr('"+statisIds+"')) ";
		}
		String sql="set nocount on; select formID,StatisID,StatisName,activedyn,usercode from _sysStatis where formid="+formid+" and  isnull(usercode,'') in ('',"+"'"+usercode+"'"+") "+statisidList+" order by usercode asc ,StatisID asc";
		return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public int getCount() {
		// TODO Auto-generated method stub
		SqlRowSet rs = this.jdbcTemplate.queryForRowSet(COUNT);
		if(rs.next()){
			return rs.getInt(1);
		}
		return 0;
		
	}
	@Override
	public int getCount(int formId,String StatisName) {
		// TODO Auto-generated method stub
		String filter = "";
		if(formId != -1 && StatisName != null){
			filter = " statisname like '"+StatisName+"%' and formId like '"+formId+"%'";
		}else if(formId == -1 && StatisName != null){
			filter = " statisname like '"+StatisName+"%'";
		}else{
			filter = " formId like '"+formId+"%'";
		}
		String sql = "set nocount on; select count(formId) from _sysStatis where "+filter;
		SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
		if(rs.next()){
			return rs.getInt(1);
		}
		return 0;
		
	}
	@Override
	public void addPanelData(String sql) {
		//String sql="insert into _sysStatis([formid],[statisid],[statisname],[initcondition],[indexstr],[detailflag],[havingstr],[crossfield],[activedyn],[usercode]) values(?,?,?,?,?,?,?,?,?,?)";
		// TODO Auto-generated method stub
		this.jdbcTemplate.update(sql);
		//,new Object[] { FormID,StatisID,StatisName,initcondition,indexstr,detailflag,havingstr,CrossField,activedyn,UserCode }
		
	}
	@Override
	public void updatePanelData(String sql) {
		// TODO Auto-generated method stub
		this.jdbcTemplate.update(sql);
		
	}
	@Override
	public void delPanelData(String sql) {
		// TODO Auto-generated method stub
		this.jdbcTemplate.update(sql);
		
	}
	@Override
	public List<Map<String, Object>> getAllPanelDatas(int curPage, int pageSize) {
		
		// TODO Auto-generated method stub
		String sql = "set nocount on; select top "+pageSize+" * from _sysStatis where (StatisName not in (select top "
				+(pageSize*(curPage-1))+" StatisName from _sysStatis order by formid)) order by formid";
	
	return this.jdbcTemplate.queryForList(sql);
	
	}
	@Override
	public List<Map<String, Object>> getAllPanelDatas(int curPage,
			int pageSize, int formId,String StatisName) {
		// TODO Auto-generated method stub
		String filter = "";
		if(formId != -1 && StatisName != null){
			filter = " statisname like '"+StatisName+"%' and formId like '"+formId+"%'";
		}else if(formId == -1 && StatisName != null){
			filter = " statisname like '"+StatisName+"%'";
		}else{
			filter = " formId like '"+formId+"%'";
		}

		String sql = "set nocount on; select top "+pageSize+" * from _sysStatis where (StatisName not in (select top "
					+(pageSize*(curPage-1))+" StatisName from _sysStatis where "+filter+" order by formid))" +
							" and("+filter+") order by formid";
		return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public List<Map<String, Object>> getAllActivedPanelDatas() {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public List<Map<String, Object>> getPaneDatasByUser(String userCode) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public Map<String, Object> getPanelData(int formid) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public int getShowRowNumber(int formid) {
		// TODO Auto-generated method stub
		return 0;
	}
	@Override
	public List<Map<String, Object>> getshujulogin(String name) {
		// TODO Auto-generated method stub
		return null;
	}
	/*
	 * 
	 * (non-Javadoc)系统设置的方法下面
	 * @see com.yc.service.new38type.Type38service#getCounts()
	 */
	@Override
	public int getCounts() {
		// TODO Auto-generated method stub
		SqlRowSet rs = this.jdbcTemplate.queryForRowSet(COUNTS);
		if(rs.next()){
			return rs.getInt(1);
		}
		return 0;
	}
	@Override
	public int getCounts(int formId, String Statisid) {
		// TODO Auto-generated method stub
		String filter = "";
		if(formId != -1 && Statisid != null){
			filter = " Statisid like '"+Statisid+"%' and formId like '"+formId+"%'";
		}else if(formId == -1 && Statisid != null){
			filter = " Statisid like '"+Statisid+"%'";
		}else{
			filter = " formId like '"+formId+"%'";
		}
		String sql = "set nocount on; select count(formId) from _sysStatisDetail where "+filter;
		SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
		if(rs.next()){
			return rs.getInt(1);
		}
		return 0;
	}
	@Override
	public List<Map<String, Object>> getAllPanelDatass(int curPage, int pageSize,String tj) {
		// TODO Auto-generated method stub
		String sql = "set nocount on; select top "+pageSize+" * from _sysStatisDetail where (FieldCaption not in (select top "
				+(pageSize*(curPage-1))+" FieldCaption from _sysStatisDetail order by formid))"+tj+" order by formid";
	
	return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public List<Map<String, Object>> getAllPanelDatass(int curPage,
			int pageSize, int formId, String Statisid) {
		// TODO Auto-generated method stub
		String filter = "";
		if(formId != -1 && Statisid != null){
			filter = " Statisid like '"+Statisid+"%' and formId like '"+formId+"%'";
		}else if(formId == -1 && Statisid != null){
			filter = " Statisid like '"+Statisid+"%'";
		}else{
			filter = " formId like '"+formId+"%'";
		}

		String sql = "set nocount on; select top "+pageSize+" * from _sysStatisDetail where (FieldCaption not in (select top "
					+(pageSize*(curPage-1))+" FieldCaption from _sysStatisDetail where "+filter+" order by formid))" +
							" and("+filter+") order by formid";
		return this.jdbcTemplate.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> selectzdy(String sql) {
		// TODO Auto-generated method stub
		return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public List<Map<String, Object>> girddata(String sql) {
		// TODO Auto-generated method stub
		return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public void updatePanelDatas(Json38 data, String saisid, String formid,String usercode) {
		// TODO Auto-generated method stub
		JSONArray json = JSONArray.fromObject(data.getData());  
		Map<String,Object>map=null;
		String sql="";
		for(int i =0;i<json.size();i++){
			map=(Map<String, Object>) json.get(i);
			String fieldalias=(map.get("fieldalias")==null||map.get("fieldalias").equals(""))?"''":"'"+(String)map.get("fieldalias")+"'";
			String  fieldcaption =(map.get("fieldcaption")==null||map.get("fieldcaption").equals(""))?"''":"'"+(String)map.get("fieldcaption")+"'";
			int    displayyn=(map.get("displayyn")==null||map.get("displayyn").equals(""))?0:Integer.parseInt(map.get("displayyn")+"");
			String  statistype= (map.get("statistype")==null||map.get("statistype").equals(""))?"''":(String)map.get("statistype");
			if(displayyn==1){
				if(statistype.equals("自定义")){
					sql+="   INSERT INTO _sysStatisDetail([FormID],[StatisID],[FieldID],[StatisType],[fieldalias],[FieldCaption],[Sequence],[DisplayWidth],[displayformat],[cellAlign],[SumField],[FuncLinkName],[displayYN],[isFilterZero],[jionFlag],[jionFlagGroup],[conFlag],[modfvalues],[modfvalues2])"
							+ " values("+"'"+formid+"'"+","+"'"+saisid+"'"+","+"'"+(map.get("fieldid")+"").replaceAll("'", "''")+"'"+","+"'"+statistype+"'"+","+fieldalias+","+fieldcaption+","+"'"+(map.get("sequence")==null?0:map.get("sequence")+"")+"'"+","+"'"+(map.get("displaywidth")==null?0:map.get("displaywidth")+"")+"'"+","+"'"+(String)map.get("displayformat")+"'"+","+"'"+(String)map.get("cellalign")+"'"+","+(map.get("sumfield")==null||map.get("sumfield").equals("")?0:map.get("sumfield")+"")+","+"'"+(map.get("funclinkname")==null?"":(String)map.get("funclinkname"))+"'"+","+"'"+displayyn+""+"'"+","+"'"+(map.get("isfilterzero")==null?0:map.get("isfilterzero")+"")+"'"+","+"'"+((String)map.get("jionflag")==null?"":(String)map.get("jionflag"))+"'"+","+"'"+(map.get("jionflaggroup")==null?0:map.get("jionflaggroup")+"")+"'"+","+"'"+((String)map.get("conflag")==null?"":(String)map.get("conflag"))+"'"+","+"'"+(String)map.get("modfvalues")+""+"'"+","+"'"+(String)map.get("modfvalues2")+""+"'"+")"+"  ";
				}else{
					sql+="   INSERT INTO _sysStatisDetail([FormID],[StatisID],[FieldID],[StatisType],[fieldalias],[FieldCaption],[Sequence],[DisplayWidth],[displayformat],[cellAlign],[SumField],[FuncLinkName],[displayYN],[isFilterZero],[jionFlag],[jionFlagGroup],[conFlag],[modfvalues],[modfvalues2])"
							+ " values("+"'"+formid+"'"+","+"'"+saisid+"'"+","+"'"+(String)map.get("fieldid")+"'"+","+"'"+statistype+"'"+","+fieldalias+","+fieldcaption+","+"'"+(map.get("sequence")==null?0:map.get("sequence")+"")+"'"+","+"'"+(map.get("displaywidth")==null?0:map.get("displaywidth")+"")+"'"+","+"'"+(String)map.get("displayformat")+"'"+","+"'"+(String)map.get("cellalign")+"'"+","+(map.get("sumfield")==null||map.get("sumfield").equals("")?0:map.get("sumfield")+"")+","+"'"+(map.get("funclinkname")==null?"":(String)map.get("funclinkname"))+"'"+","+"'"+displayyn+""+"'"+","+"'"+(map.get("isfilterzero")==null?0:map.get("isfilterzero")+"")+"'"+","+"'"+((String)map.get("jionflag")==null?"":(String)map.get("jionflag"))+"'"+","+"'"+(map.get("jionflaggroup")==null?0:map.get("jionflaggroup")+"")+"'"+","+"'"+((String)map.get("conflag")==null?"":(String)map.get("conflag"))+"'"+","+"'"+map.get("modfvalues")+""+"'"+","+"'"+map.get("modfvalues2")+""+"'"+")"+"  ";
				}
		
		}
		}
		sql="set nocount on; INSERT INTO _sysStatis ([FormID],[StatisID],[StatisName],[activedyn],[usercode])values("+"'"+formid+"'"+","+"'"+saisid+"'"+","+"'"+saisid+"'"+",'1',"+"'"+usercode+"'"+")   "+sql;
		 this.jdbcTemplate.update(sql);
	}
	@Override
	public void PanelDatas(Json38 data, String saisid, String formid) {
		// TODO Auto-generated method stub
		JSONArray json = JSONArray.fromObject(data.getData());  
		Map<String,Object>map=null;
		String sql="set nocount on; ";
		for(int i =0;i<json.size();i++){
			map=(Map<String, Object>) json.get(i);
			String fieldalias=(map.get("fieldalias")==null||map.get("fieldalias").equals(""))?"''":"'"+GridUtils.prossRowSetDataType_String(map,"fieldalias")+"'";
			String  fieldcaption =(map.get("fieldcaption")==null||map.get("fieldcaption").equals(""))?"''":"'"+GridUtils.prossRowSetDataType_StringByAddTwoQuotes(map,"fieldcaption")+"'";
			int    displayyn=(map.get("displayyn")==null||map.get("displayyn").equals(""))?0:GridUtils.prossRowSetDataType_Int(map,"displayyn");
			String  statistype= (map.get("statistype")==null||map.get("statistype").equals(""))?"''":GridUtils.prossRowSetDataType_String(map,"statistype");
			if(displayyn==1){
				String sqlselect="";
				if(statistype.equals("自定义")){
					 sqlselect="select * from _sysStatisDetail where formid="+formid+" and statisid="+"'"+saisid+"'"+" and fieldid="+"'"+(map.get("fieldid")+"").replaceAll("'", "''")+"'"+" ";
				 
	
				}else{
					sqlselect="select * from _sysStatisDetail where formid="+formid+" and statisid="+"'"+saisid+"'"+" and fieldid="+"'"+(String)map.get("fieldid")+"'"+" ";
				}
				List<Map<String, Object>> list=this.jdbcTemplate.queryForList(sqlselect);
			if(list.size()!=0){
				
			}else{	
				if(statistype.equals("自定义")){
					sql+="   INSERT INTO _sysStatisDetail([FormID],[StatisID],[FieldID],[StatisType],[fieldalias],[FieldCaption],[Sequence],[DisplayWidth],[displayformat],[cellAlign],[SumField],[FuncLinkName],[displayYN],[isFilterZero],[jionFlag],[jionFlagGroup],[conFlag],[modfvalues],[modfvalues2])"
							+ " values("+"'"+formid+"'"+","+"'"+saisid+"'"+","+"'"+(map.get("fieldid")+"").replaceAll("'", "''")+"'"+","+"'"+statistype+"'"+","+fieldalias+","+fieldcaption+","+"'"+(map.get("sequence")==null?0:map.get("sequence")+"")+"'"+","+"'"+(map.get("displaywidth")==null?0:map.get("displaywidth")+"")+"'"+","+"'"+(String)map.get("displayformat")+"'"+","+"'"+(String)map.get("cellalign")+"'"+","+(map.get("sumfield")==null||map.get("sumfield").equals("")?0:map.get("sumfield")+"")+","+"'"+(map.get("funclinkname")==null?"":(String)map.get("funclinkname"))+"'"+","+"'"+displayyn+""+"'"+","+"'"+(map.get("isfilterzero")==null?0:map.get("isfilterzero")+"")+"'"+","+"'"+((String)map.get("jionflag")==null?"":(String)map.get("jionflag"))+"'"+","+"'"+(map.get("jionflaggroup")==null?0:map.get("jionflaggroup")+"")+"'"+","+"'"+((String)map.get("conflag")==null?"":(String)map.get("conflag"))+"'"+","+"'"+(String)map.get("modfvalues")+"'"+","+"'"+(String)map.get("modfvalues2")+""+"'"+")"+"  ";
				}else{
					sql+="   INSERT INTO _sysStatisDetail([FormID],[StatisID],[FieldID],[StatisType],[fieldalias],[FieldCaption],[Sequence],[DisplayWidth],[displayformat],[cellAlign],[SumField],[FuncLinkName],[displayYN],[isFilterZero],[jionFlag],[jionFlagGroup],[conFlag],[modfvalues],[modfvalues2])"
							+ " values("+"'"+formid+"'"+","+"'"+saisid+"'"+","+"'"+(String)map.get("fieldid")+"'"+","+"'"+statistype+"'"+","+fieldalias+","+fieldcaption+","+"'"+(map.get("sequence")==null?0:map.get("sequence")+"")+"'"+","+"'"+(map.get("displaywidth")==null?0:map.get("displaywidth")+"")+"'"+","+"'"+(String)map.get("displayformat")+"'"+","+"'"+(String)map.get("cellalign")+"'"+","+(map.get("sumfield")==null||map.get("sumfield").equals("")?0:map.get("sumfield")+"")+","+"'"+(map.get("funclinkname")==null?"":(String)map.get("funclinkname"))+"'"+","+"'"+displayyn+""+"'"+","+"'"+(map.get("isfilterzero")==null?0:map.get("isfilterzero")+"")+"'"+","+"'"+((String)map.get("jionflag")==null?"":(String)map.get("jionflag"))+"'"+","+"'"+(map.get("jionflaggroup")==null?0:map.get("jionflaggroup")+"")+"'"+","+"'"+((String)map.get("conflag")==null?"":(String)map.get("conflag"))+"'"+","+"'"+(String)map.get("modfvalues")+"'"+","+"'"+(String)map.get("modfvalues2")+""+"'"+")"+"  ";
				}
			}
		
		}
		}
		//sql="DELETE from _sysStatisDetail where StatisID="+"'"+saisid+"'"+" and formid="+formid+"      "+sql;
		 this.jdbcTemplate.update(sql);
	}
    HSSFDataFormat  formatd= null;
    Map<String,HSSFCellStyle> cellStyle=new HashMap<String,HSSFCellStyle>();//由于CellStyle创建不能过多，由主要是格式输 出，所以以格式为key，CellStyle为值的map保存来调用避免多次创建相同的对象。
   public  HSSFWorkbook writeExcel(HttpServletResponse response,String filename, Page page, String[] title,String[] filed,String[] panel_title,String[] panel_filed,List panel,String titles,String local) throws ParseException {
       List datas=page.getData();
	if(datas == null) {
           throw new IllegalArgumentException("写excel流需要List参数!");
       }
       try {
           HSSFWorkbook wb = new HSSFWorkbook();  
           HSSFSheet ws= wb.createSheet("sheet1");
             formatd= wb.createDataFormat();
           int rowNum = 0;    //要写的行
           int tempNum=0;
           //写标题
           setCell(titles, ws,0,5,null);
           
           //--end
           rowNum++;
           if(panel!=null){
           //1,写面板
           if(panel_title != null) {
       	
       	String[] s=local.split(";");
       	//按行排序再输出
       	TreeSet<Pov> set=new TreeSet<Pov>();
       	for(int k=0;k<s.length;k++){
       	    String[] ss=s[k].split("#")[1].split(",");//位置
       	     set.add(new Type38Impl().new Pov(Integer.parseInt(ss[0]),s[k],panel_title[k],panel_filed[k]));
       	}
       	int samRow=0;
       	int col=0;
       	 Iterator<Pov> it = set.iterator();
                while (it.hasNext()) {
                    Pov p=it.next();
                    int row=rowNum+p.getNum();//行
                    if(samRow!=row) col=0;
                    //写入标题
                    setCell(p.getTitle()+":", ws,row,col==-1?0:col,null);
                    int rcol=col+1;
                    putRowC(wb,ws, row, panel.get(0),new String[]{p.getFild()},rcol);    //压一行到sheet
                    tempNum=row;
                    col=rcol+1;
                    samRow=row;
                } 
           }

           if(panel_title != null)
                rowNum=tempNum+4;
           }
           
           //---------格线内容
           if(title != null) {
               putRow(ws, rowNum, title);//压入标题
               rowNum += 1;
           }
           
           double total=0;//统计总数
           int rowcount=0;
           for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
               putRowC(wb,ws, rowNum, datas.get(i),filed,0);    //压一行到sheet
               rowcount++;
           }
           String ex=page.getExcelTitle().replaceAll("\n", "");
           if(!"".equalsIgnoreCase(ex)){
           Map<String,ExcelPO> param=new HashMap<String,ExcelPO>();
          String[] tem=ex.split(";");
          for(String k:tem){
              String[] ss=k.split("#");
              ExcelPO po =  new Type38Impl().new ExcelPO();  
              po.setKey(ss[0]);
              po.setFum(ss[1]);
              param.put(ss[0], po);
          }
        
           getAmount(datas,param);
    
           //输出统计数据
           
           for(Entry<String, ExcelPO> en:param.entrySet()){
       	ExcelPO po=en.getValue();
       	String[] vo=po.getFum().split("\\|");//1,null,5,数字格式;
       	int type=Integer.parseInt(vo[0]);//类别
       	String format=getNumFormat(vo[3]);//数字格式
       	if(type<5){
       	    total=prossFumal(rowcount, po, type); 
       	    
       	}else{//自定义统计
       	    String[] newstr=vo[1].split(":")[1].replace("(^\\s*)|(\\s*$)","").split(" ");
			String polish="";
			for(int k=0;k<newstr.length;k++){
			    
				String p=null;
			  	 Pattern pt = Pattern.compile("[+\\-\\*/]");
					java.util.regex.Matcher propsMatcher = pt.matcher(newstr[k]);
					while (propsMatcher.find()) {
					    p=propsMatcher.group();
					   }
				String num=null;
				 pt = Pattern.compile("\\d");// //取数字
				 propsMatcher = pt.matcher(newstr[k]);
				while (propsMatcher.find()) {
				    num=propsMatcher.group();
				   }
				
				
				if(k<newstr.length-1){
					if(p!=null){
					polish+=p+",";
				}	
				else if(num!=null){
				polish+=newstr[k]+",";	
           			}else{
           			
           			double ts=prossFumal(rowcount, po, 1); 
           			polish+=(ts==0?0+" ":ts)+",";
           			}
           			}
           				else{
           					if(p!=null){
           							polish+=p;
           						}
           						else if(num!=null){
           							polish+=newstr[k]+",";	
           						}
           					else{
           					   
           					double ts= prossFumal(rowcount, po, 1); 
           					     polish+=ts==0?0+" ":ts;	
           				}
           				}
           			}
			total=calculate(polish.split(","));//计算得到值
       	    
       	}
       	if(!"".equalsIgnoreCase(format)){
       	    
       	 
       	 setCellDouble(total, ws,rowNum+1,Integer.parseInt(vo[2]),getCellStyle(format,wb));
       	}else
       	    setCellDouble(total, ws,rowNum+1,Integer.parseInt(vo[2]),null);
           }
           }
           //--------end
           
           return wb;
          // FileOutputStream fileOut = new FileOutputStream(file);
         //  wb.write(fileOut);
         //  fileOut.flush();
         //  fileOut.close();    //一定要关闭, 否则没有保存Excel
           //----生成流直接输出
          
           
           //-----------
     
       } catch (Exception e) {
          
           System.out.println(" write file i/o exception!, cause by: "+e.getMessage());
           return null;
       }
   }
   private HSSFCellStyle getCellStyle(String format,HSSFWorkbook wb) {
	if(cellStyle.containsKey(format)) {
	//cellStyle=wb.createCellStyle();
	    return cellStyle.get(format);
	}
	else {
	    HSSFCellStyle  style=wb.createCellStyle();
	    style.setDataFormat(formatd.getFormat(format.replaceAll("~", "-")));
	    cellStyle.put(format, style);
	    return cellStyle.get(format);
	}
   }
   class Pov implements Comparable {
	private int num;
	private String cont;
	private String title;
	private String fild;
	
	public String getFild() {
	    return fild;
	}
	public void setFild(String fild) {
	    this.fild = fild;
	}
	public String getTitle() {
	    return title;
	}
	public void setTitle(String title) {
	    this.title = title;
	}
	public Pov(int n,String con,String t,String f){
	   this.num=n;
	   this.cont=con;
	   this.title=t;
	   this.fild=f;
      }
	public int getNum() {
	    return num;
	}
	public void setNum(int num) {
	    this.num = num;
	}
	public String getCont() {
	    return cont;
	}
	public void setCont(String cont) {
	    this.cont = cont;
	}
	 public int compareTo(Object o) {
	     Pov ss = (Pov) o;
	        //int result = num < ss.num ? 1 : (num == ss.num ? 0 : -1);//降序
	        int result = num > ss.num ? 1 : (num == ss.num ? 0 : -1);//升序
	        if (result == 0) {
	            result = cont.compareTo(ss.cont);
	        }
	        return result;
	    }
   }
   private static double calculate(String[] RPolishArray) {
	double result = 0;
	double[] tempArray = new double[100];
	int tempNum = -1;
	for(int i = 0;i < RPolishArray.length;i++){
	    Pattern pt = Pattern.compile("\\d");
		java.util.regex.Matcher propsMatcher = pt.matcher(RPolishArray[i]);
		String p=null;
		while (propsMatcher.find()) {
		    p=propsMatcher.group();
		   }
		if(p!=null){
			tempNum++;
			tempArray[tempNum] = new Double(RPolishArray[i]);
		}else{
		    
			if(RPolishArray[i].equalsIgnoreCase("+")){
			    
				
					result = (tempArray[tempNum-1] *1) + (tempArray[tempNum] * 1);
					tempNum--;
					tempArray[tempNum] = result;
			}
			else if(RPolishArray[i].equalsIgnoreCase("-")){
					result = (tempArray[tempNum-1] *1) - (tempArray[tempNum] * 1);
					tempNum--;
					tempArray[tempNum] = result;
					
			}else if(RPolishArray[i].equalsIgnoreCase("*")){
					result = (tempArray[tempNum-1] *1) * (tempArray[tempNum] * 1);
					tempNum--;
					tempArray[tempNum] = result;
					
			}else if(RPolishArray[i].equalsIgnoreCase("/")){
				if(tempArray[tempNum] * 1==0){//被除数为0的情况
				   tempArray[tempNum]=0;
				  }else{
					result = (tempArray[tempNum-1] *1) / (tempArray[tempNum] * 1);
					tempArray[tempNum] = result;
				  }
					tempNum--;
					
			}
		}
	}
	result = tempArray[tempNum];
	return result;	
   }

   private static double prossFumal(int rowcount, ExcelPO po, int type) {
	double total;
	switch(type){
		case 1://汇总
		      total=po.getNum();
			break;
		case 2://计数
			total= rowcount;
			break;
		case 3://平均
		    total =po.getNum()/rowcount;
			break;
		case 4://百分比
		    total= po.getNum()*100;
			break;
		default:
			total= 0;
	}
	return total;
   }
   
   public static void getAmount(List selectList, Map<String,ExcelPO> param) {  
	
	       if (selectList!=null) {  
	           if (selectList.size() > 0) {  
	               Iterator list = selectList.iterator();  
	               while(list.hasNext()) {  
	                   Map map = (Map)list.next();
	                   for (String key:param.keySet()) {  
	                     
	                       ExcelPO num =param.get(key);  
	                       Object obj = map.get(key);  
	                       if (obj != null&&!"".equalsIgnoreCase(String.valueOf(obj))) { 
	                	   try{
	                	       //存在有格式化的情况，所以这里需要做转换 44,555.55
	                	       String temp=procHy(String.valueOf(obj));     
	                           num.setNum(num.getNum()+new Double(temp.replaceAll(",", "")));  
	                	   }catch(Exception e){
	                	       num.setNum(num.getNum()+0);  
	                	   }
	                       }  
	                        
	                   }  
	               }  
	           }  
	       }  
	}  
class ExcelPO{
   private double num;//总数
   private int col;//所在列
   private String key ;//键
   private String fum;//统计公式类型
   
   public String getFum() {
       return fum;
   }
   public void setFum(String fum) {
       this.fum = fum;
   }
   public double getNum() {
       return num;
   }
   public void setNum(double num) {
       this.num = num;
   }
   public int getCol() {
       return col;
   }
   public void setCol(int col) {
       this.col = col;
   }
   public String getKey() {
       return key;
   }
   public void setKey(String key) {
       this.key = key;
   }
   
   
}
   private static void setCell(String titles, HSSFSheet ws,int rowindex,int cellindex, HSSFCellStyle cellStyle ) {
	HSSFRow rows=ws.getRow(rowindex);
	if( rows==null)
	    rows=ws.createRow(rowindex);
	HSSFCell cell = rows.createCell(cellindex);
	cell.setCellValue(titles);
	if(cellStyle!=null)
	  cell.setCellStyle(cellStyle);    
	
   }
   private static void setCellDouble(double d, HSSFSheet ws,int rowindex,int cellindex, HSSFCellStyle cellStyle ) {
	HSSFRow rows=ws.getRow(rowindex);
	if( rows==null)
	    rows=ws.createRow(rowindex);
	HSSFCell cell = rows.createCell(cellindex);
	if(cellStyle!=null)
	 	  cell.setCellStyle(cellStyle);
	cell.setCellValue(d);
 
	
    }
   private static void putRow(HSSFSheet ws, int rowNum, Object[] cells)  {
       for(int j=0; j<cells.length; j++) {//写一行
           setCell(""+cells[j],ws,rowNum,j,null);
       }
   }
   private  void putRowC(HSSFWorkbook wb,HSSFSheet ws, int rowNum, Object cells,String[] filed,int j)  {
       HashMap<String,Object> map=(HashMap<String,Object>)cells;
      boolean flg=false;
      if(j>0) flg=true;
       for(String s:filed){
           String[] temp=s.split(";");

              if(map.containsKey(temp[0].toLowerCase()+"_expr")&&"0".equalsIgnoreCase(map.get(temp[0].toLowerCase()+"_expr")+"")){
              map.put(temp[0], "*");
              }
           
           if(map.get(temp[0])!=null&&!"".equals(map.get(temp[0]))&&temp.length>1){//表示有格式
       	
       	  if(temp[1].startsWith("0")||temp[1].startsWith(",0")||temp[1].startsWith(",0.")||temp[1].startsWith("0.")){

       	            //格式化数字 
       	        	    String format = getNumFormat(temp[1]); 
       	        	    
       	        	    if((String.valueOf(map.get(temp[0]))).matches("^(-?\\d+\\.\\d+|\\d+)$")){
       	           	     Double d=Double.parseDouble(String.valueOf(map.get(temp[0])));    
       	           	
       	                     
       	                       setCellDouble(d,ws,rowNum,j,getCellStyle(format, wb));
       	           	 }else{ 
       	           	  String sd= ""+((map.get(temp[0])==null||"null".equals(map.get(temp[0])))?"":map.get(temp[0]));
       	                  String st=procFont(procHy(sd));
       	                  if(!"".equalsIgnoreCase(st)&&st.matches("^(-?\\d+\\.\\d+|\\d+)$"))
       	                      setCellDouble(Double.parseDouble(st),ws,rowNum,j,getCellStyle(format, wb));
       	                  else
       	                      setCell(st.replaceAll("&nbsp;", " "),ws,rowNum,j,getCellStyle(format, wb));    
       	           	 } 
       	    
       	}else{
       	  //格式化日期 
       	    SimpleDateFormat formatDate = new SimpleDateFormat(temp[1].replaceAll("~", "-"));
     	           try {
		    formatDate.parse(map.get(temp[0]).toString());
		    String st=formatDate.format(map.get(temp[0]));
	                 setCell(st,ws,rowNum,j,null);  
		} catch (ParseException e) {
		    // TODO Auto-generated catch block
		    e.printStackTrace();
		}
     	         
       	}
           }else{
         //普通字符     
       	// String format = getNumFormat(temp[1]); 
           String sd= ""+((map.get(temp[0])==null||"null".equals(map.get(temp[0])))?"":map.get(temp[0]));
           String st=procFont(procHy(sd));
           setCell(st.replaceAll("&nbsp;", " "),ws,rowNum,j,null);
           }
           if(!flg)  j++; 
       }
   }
private static String procHy(String str){ //去掉有超链接内容只返回内容
   
   String regex="<(\\S*?) [^>]*>.*?</\\1>|<.*? />";        
   //String regex = "<a.*>(.*)</a>";
   Pattern pt=Pattern.compile(regex);
   Matcher mt=pt.matcher(str.toLowerCase());//.replaceAll("<A", "<a").replaceAll("A>", "a>")
   while(mt.find())
   {
       
        String s2=">.*?</(a|font)>";//标题部分
         Pattern pt2=Pattern.compile(s2);
         Matcher mt2=pt2.matcher(mt.group());
         while(mt2.find())
           {
             String tm=mt2.group().replaceAll(">|</a>|</font>","");
         return tm;
           }
                 
   }
   return str;
   
}
private static String procFont(String str){ //去掉有字体设置内容只返回内容
   
   String regex="<font.*?/font>";        
   //String regex = "<a.*>(.*)</a>";
   Pattern pt=Pattern.compile(regex);
   Matcher mt=pt.matcher(str);
   while(mt.find())
   {
       
        String s2=">.*?</font>";//标题部分
         Pattern pt2=Pattern.compile(s2);
         Matcher mt2=pt2.matcher(mt.group());
         while(mt2.find())
           {
         return mt2.group().replaceAll(">|</font>","");
           }
                 
   }
   return str;
   
}
   private static String getNumFormat(String temp) {
	if(temp==null||"".equalsIgnoreCase(temp)) return null;
	int indx=temp.indexOf(".");//,0.00
	String format=null;
	if(indx>0){//有小数位
	//有千位
	if(temp.startsWith(","))
	    format=temp.replace(",0.", "#,###0."); 
	else
	    format=temp.replace("0.", "###0."); 
	}else{//整数
	    if(temp.startsWith(","))
		format=temp.replace(",0", "#,###0"); 
	   else
	       format=temp.replace("0", "#");
	}
	return format;
   }
	
}