package xin.glue.ui.common;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import xin.glue.ui.common.entity.MergeCell;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class OutExcelCommon {

	/**
	 * 导出Excel(简单表格,不含合并行列)
	 * @param jsonStr 结果集json格式字符串
	 * @param fileName 文件路径
	 * @param sheetName 工作表名称
	 * @param title 首行列名
	 * @param colRef 列对应字段
	 * @throws FileNotFoundException,WriteException, IOException,Exception 
	 */
	public static void outExcel(String jsonStr,String fileName,String sheetName,String[] title,String[] colRef) throws FileNotFoundException,WriteException, IOException,Exception  {
		int startIndex =0;
		int endIndex =0;
		int index =0;
		Label label = null;
		WritableWorkbook book = null;
		WritableSheet sheet = null;
		try {
			deleteFiles(fileName);
			File excelFile = new File(fileName+sheetName+".xls");
			// 如果文件存在就删除它
			if (excelFile.exists())
				excelFile.delete();
			// 打开文件
			book = Workbook.createWorkbook(excelFile);
			
			JSONArray array = JSONArray.parseArray(jsonStr);
			int maxCnt = 65530;//每个工作表格显示65530条数据（注：jxl导出excel表格一个工作表最多可以存储65536条）
			int avg = array.size() /  maxCnt ;
			int avgCnt = array.size() %  maxCnt == 0 ? avg : avg+1 ;
			//avgCnt = array.size() == 0 ? 1 : avgCnt;
			for(int e = 0; e < avgCnt; e++){
				// 生成工作表，参数0表示这是第一页
				sheet = book.createSheet(sheetName+e, e);
				// 合并单元格
				//sheet.mergeCells(5, 5, 6, 6);
				// 文字样式
				jxl.write.WritableFont wfc = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
				jxl.write.WritableFont wfc2 = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

				jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat(wfc2);

				// 设置单元格样式
				wcfFC.setBackground(jxl.format.Colour.GRAY_25);// 单元格颜色
				wcfFC.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中
				wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
				wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中
				wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
				index = 0;
				
				// 构造表格首行
				String[] titles = title[0].split("\\^");
				for (int i =0;i<titles.length;i++) {
					// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
					label = new Label(i, 0, titles[i], wcfFC);
					// 将定义好的单元格添加到工作表中
					sheet.addCell(label);
					sheet.setColumnView(i, 20); // 设置列的宽度
				}
				index++;
				
				// 构造表格数据行
				String[] colRefs = colRef[0].split("\\^");
				startIndex = e * maxCnt;
				endIndex = (startIndex + maxCnt) > array.size() ? array.size() : (startIndex + maxCnt) ;
				for(int i = startIndex; i < endIndex;i++){
					JSONObject jobj = (JSONObject)array.get(i);
					for (int j =0;j<colRefs.length;j++) {
						if(jobj.getString(colRefs[j]) == null){
							//若无数据，返回空
							label = new Label(j, index, "",wcfFC2);
						}else{
							label = new Label(j, index, jobj.getString(colRefs[j]),wcfFC2);
						}
						sheet.addCell(label);
					}
					index++;
				}
			}
			// 写入数据并关闭文件
			book.write();
			book.close();
		} catch (FileNotFoundException e) {
			throw e;
		} catch (WriteException e) {
			book.write();
			book.close();
			throw e;
		} catch (IOException e) {
			book.write();
			book.close();
			throw e;
		} catch (Exception e) {
			book.write();
			book.close();
			throw e;
		}
	}

	/**
	 * 导出Excel(复杂表格,含合并行列)
	 * @param jsonStr 结果集json格式字符串
	 * @param fileName 文件路径
	 * @param sheetName 工作表名称
	 * @param title 首行列名
	 * @param colRef 列对应字段
	 * @param jsonMerges 合并行列json格式字符串(每个合并的行列必须对应一个MergeCell对象)
	 * @throws Exception 
	 */
	public static void outExcel(String jsonStr,String fileName,String sheetName,String[] title,String[] colRef,String jsonMerges) throws Exception{
		int index =0;
		int startIndex =0;
		int endIndex =0;
		Label label = null;
		WritableWorkbook book = null;
		WritableSheet sheet = null;
		try {
			deleteFiles(fileName);
			File excelFile = new File(fileName+sheetName+".xls");
			// 如果文件存在就删除它
			if (excelFile.exists())
				excelFile.delete();
			// 打开文件
			book = Workbook.createWorkbook(excelFile);
			JSONArray array = JSONArray.parseArray(jsonStr);
			int maxCnt = 65530;//每个工作表格显示65530条数据（注：jxl导出excel表格一个工作表最多可以存储65536条）
			int avg = array.size() /  maxCnt ;
			int avgCnt = array.size() %  maxCnt == 0 ? avg : avg+1 ;
			//avgCnt = array.size() == 0 ? 1 : avgCnt;
			for(int e = 0; e < avgCnt; e++){
				// 生成工作表，参数0表示这是第一页
				sheet = book.createSheet(sheetName+e, e);
				// 合并单元格
				//sheet.mergeCells(5, 5, 6, 6);
				// 文字样式
				jxl.write.WritableFont wfc = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
				jxl.write.WritableFont wfc2 = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

				jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat(wfc2);

				// 设置单元格样式
				wcfFC.setBackground(jxl.format.Colour.GRAY_25);// 单元格颜色
				wcfFC.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中
				wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
				wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中
				wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
				index =0;
				
				// 构造表格首行
				String[] ts = title[0].split("\\|");
				String[] titles;
				for (int i =0;i<ts.length;i++) {
					titles= ts[i].split("\\^");
					for (int j =0;j<titles.length;j++) {
						// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
						label = new Label(j, i, titles[j], wcfFC);
						// 将定义好的单元格添加到工作表中
						sheet.addCell(label);
						sheet.setColumnView(j, 20); // 设置列的宽度
					}
					index++;
				}
				
				//合并单元格
				//合： 第1列第1行  到 第4列第1行 
				//sheet.mergeCells(0, 0, 3, 0);
				List<MergeCell> cellsList=JSON.parseArray(jsonMerges,MergeCell.class);
				for (MergeCell mergeCell : cellsList) {
					if("x".equals(mergeCell.getName())){
						sheet.mergeCells(mergeCell.getX(), mergeCell.getY(), mergeCell.getX()+mergeCell.getCnt()-1, mergeCell.getY());   
					}else{
						sheet.mergeCells(mergeCell.getX(), mergeCell.getY(), mergeCell.getX(), mergeCell.getY()+mergeCell.getCnt()-1);   
					}
				}
				
				// 构造表格数据行
				String[] colRefs = colRef[0].split("\\^");	
				startIndex = e * maxCnt;
				endIndex = (startIndex + maxCnt) > array.size() ? array.size() : (startIndex + maxCnt) ;
				for(int i = startIndex; i <endIndex;i++){
					JSONObject jobj = (JSONObject)array.get(i);
					for (int j =0;j<colRefs.length;j++) {
						if(jobj.getString(colRefs[j]) == null){
							//若无数据，返回空
							label = new Label(j, index, "",wcfFC2);
						}else{
							label = new Label(j, index, jobj.getString(colRefs[j]),wcfFC2);
						}
						sheet.addCell(label);
					}
					index++;
				}
			}
			book.write();
			book.close();
		} catch (FileNotFoundException e) {
			throw e;
		} catch (WriteException e) {
			book.write();
			book.close();
			throw e;
		} catch (IOException e) {
			book.write();
			book.close();
			throw e;
		} catch (Exception e) {
			book.write();
			book.close();
			throw e;
		}
	}
	

    //删除文件夹下不是当天的文件
    public static void deleteFiles(String path){
    	SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
    	java.util.Date date=new java.util.Date();  
    	String str=sdf.format(date);  
    	File file = new File(path);
    	if(!file.exists()){
    		file.mkdirs();
    	}
    	File[] list = file.listFiles();
        for (int i = 0; i < list.length; i++) {
        	date = new Date(list[i].lastModified());
            if(!str.equals(sdf.format(date))){
            	list[i].delete();
            }
        }
    }
}