package com.yihu.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import com.common.json.JSONArray;
import com.common.json.JSONObject;







import jxl.Workbook;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExportExcel {
	public static void initExpExl(HttpServletResponse response,String exlName,JSONObject json,List<String> title,List<String> keyList) throws Exception{
		Date date = new Date();
		SimpleDateFormat f = new SimpleDateFormat("yyyyMMddHHmmss");
		String time = f.format(date);
		String fileName = time +".xls";
		String destination = FileUtil.getTomcatLogDir() +fileName;
		File file = null;
		System.out.println("destination="+destination);
		try{
			String sb = json.isNull("rows") ? "" : json.getString("rows");
			if (!"".equals(sb) ) {
				file = new File(destination);
				if (!file.exists()) {
					file.createNewFile();
				}
				WritableWorkbook wwb = Workbook.createWorkbook(file);
				
				WritableFont font1= new WritableFont(WritableFont.TIMES,12,WritableFont.NO_BOLD); 
				 WritableCellFormat format1=new WritableCellFormat(font1); 
				 format1.setAlignment(jxl.format.Alignment.CENTRE); 
				format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
				
				WritableSheet wsheet = wwb.createSheet(exlName, 0);
			
				
				exportExpExl(wsheet, json,title,keyList);
				
				
				// 设置输出的格式
				response.reset();
				response.setContentType("application/x-download;charset=UTF-8");
				response.addHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + "\"");
			
				wwb.write();
				wwb.close();
				FileInputStream in = new FileInputStream(file);
				OutputStream out = response.getOutputStream();
				byte[] size = new byte[1024 * 10];
				int i = 0;
				while ((i = in.read(size, 0, size.length)) != -1) {
					out.write(size, 0, i);
				}
				in.close();
				out.flush();
				out.close();
				
			} else {
				if (sb.equals("")) {
					PrintWriter out = response.getWriter();
					out.print("暂无相关数据");
					out.flush();
					out.close();
				} else {
					PrintWriter out = response.getWriter();
					out.print(sb);
					out.flush();
					out.close();
				}
			}
		}catch(Exception e){
			throw(e);
		}finally{
			try {
				file.delete();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
	}
	public static void initExpExlMandD(HttpServletResponse response,String exlName,String exlName1,JSONObject json,JSONObject json1,List<String> title,List<String> keyList) throws Exception{
		Date date = new Date();
		SimpleDateFormat f = new SimpleDateFormat("yyyyMMddHHmmss");
		String time = f.format(date);
		String fileName = time +".xls";
		String destination = FileUtil.getTomcatLogDir() +fileName;
		File file = null;
		System.out.println("destination="+destination);
		try{
			String sb = json.isNull("rows") ? "" : json.getString("rows");
			if (!"".equals(sb) ) {
				file = new File(destination);
				if (!file.exists()) {
					file.createNewFile();
				}
				WritableWorkbook wwb = Workbook.createWorkbook(file);
				
				WritableFont font1= new WritableFont(WritableFont.TIMES,12,WritableFont.NO_BOLD); 
				 WritableCellFormat format1=new WritableCellFormat(font1); 
				 format1.setAlignment(jxl.format.Alignment.CENTRE); 
				format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
				
				WritableSheet wsheet = wwb.createSheet(exlName, 0);
				WritableSheet wsheet1 = wwb.createSheet(exlName1, 0);
				
				exportExpExl(wsheet, json,title,keyList);
				exportExpExl(wsheet1, json1,title,keyList);
				
				// 设置输出的格式
				response.reset();
				response.setContentType("application/x-download;charset=UTF-8");
				response.addHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + "\"");
			
				wwb.write();
				wwb.close();
				FileInputStream in = new FileInputStream(file);
				OutputStream out = response.getOutputStream();
				byte[] size = new byte[1024 * 10];
				int i = 0;
				while ((i = in.read(size, 0, size.length)) != -1) {
					out.write(size, 0, i);
				}
				in.close();
				out.flush();
				out.close();
				
			} else {
				if (sb.equals("")) {
					PrintWriter out = response.getWriter();
					out.print("暂无相关数据");
					out.flush();
					out.close();
				} else {
					PrintWriter out = response.getWriter();
					out.print(sb);
					out.flush();
					out.close();
				}
			}
		}catch(Exception e){
			throw(e);
		}finally{
			try {
				file.delete();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
	}


	public static void exportExpExl(WritableSheet wsheet,JSONObject json,List<String> titleList,List<String> keyList) throws Exception{
		JSONArray arry=json.getJSONArray("rows");
		int i=0;
		for (String string : titleList) {
			
			wsheet.addCell(new jxl.write.Label(i, 0, string));
			wsheet.setColumnView(0, 15);
			i++;
		}
		
		int nI = 1;
		if (null != arry && arry.length() > 0) {
			for (int j = 0; j < arry.length(); j++) {
				JSONObject jsonObject = arry.getJSONObject(j);
				
				for (int k = 0; k < keyList.size(); k++) {
					String vlaue = jsonObject.optString(keyList.get(k));
					if(isNumber(vlaue)){
						wsheet.addCell(new jxl.write.Number(k, nI,Double.valueOf(vlaue)));
					}else{						
						wsheet.addCell(new jxl.write.Label(k, nI,jsonObject.optString(keyList.get(k))));
					}
				}
				
				nI++;
			}

		}
	}
	
	
	
	public static boolean isEmpty(Object obj) {
		if (obj == null)
			return true;
		
		if (obj instanceof String) {
			return (obj == null || obj.toString().trim().length() == 0);
		}
		if (obj instanceof Long) {
			return (obj == null || (Long) obj == 0);
		}
		if (obj instanceof Integer) {
			return (obj == null || (Integer) obj == 0);
		}
		return obj == null;
	}
	
	/**
	 * 判断是否为数字
	 * @param str
	 * @return
	 */
	private static boolean isNumeric(String str){
		  for (int i = 0; i < str.length(); i++){
		   System.out.println(str.charAt(i));
		   if (!Character.isDigit(str.charAt(i))){
		    return false;
		   }
		  }
		  return true;
		 }

	/** 
     * 判断字符串是否数值  
     * @param str 
     * @return true:是数值 ；false：不是数值  
     * @author:WD_SUHUAFU 
     */  
	private static boolean isNumber(String str) {
		Pattern pattern = Pattern.compile("^[0-9]+(.[0-9]*)?$");
		Matcher match = pattern.matcher(str);
		return match.matches();
	}
	

	
	/*static String getValue(JSONObject obj, String key){
		try{
			return obj.getString(key);
		}catch(JSONException e) {
			return "";
		}
	}*/
	
	static String getMapValue(Map<String, Object> obj, String key){
		
		if(obj.get(key)!=null){
			return obj.get(key).toString();
			
		}else{
			return "";
		}
		
		
		
	}
}