package org.jeecgframework.poi.excel;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;
import org.jeecgframework.poi.excel.entity.ComparatorExcelField;
import org.jeecgframework.poi.excel.entity.ExcelExportEntity;
import org.jeecgframework.poi.excel.entity.TemplateExportParams;
/**
 * Excel 导出根据模板导出
 * @author JueYue
 * @date 2013-10-17
 * @version 1.0
 */
public final class ExcelExportOfTemplateUtil {

	/**
	 * 导出文件通过模板解析
	 * 
	 * @param entity  导出参数类
	 * @param pojoClass 对应实体
	 * @param dataSet 实体集合
	 * @param map 模板集合
	 * @return
	 */
	public static Workbook exportExcel(TemplateExportParams params,
			Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
		return createSheetInUserModel2FileByTemplate(params, pojoClass,
				dataSet, map);
	}
	
	/**
	 * 导出文件通过模板解析只有模板,没有集合
	 * 
	  * @param entity  导出参数类
	 * @param pojoClass 对应实体
	 * @param map 模板集合
	 * @return
	 */
	public static Workbook exportExcel(TemplateExportParams params,
			Map<String, Object> map) {
		return createSheetInUserModel2FileByTemplate(params, null,
				null, map);
	}

	private static Workbook createSheetInUserModel2FileByTemplate(
			TemplateExportParams params, Class<?> pojoClass,
			Collection<?> dataSet, Map<String, Object> map) {
		// step 1. 判断模板的地址
		if (StringUtils.isEmpty(params.getTemplateUrl())) {
			return null;
		}
		Workbook wb = null;
		// step 2. 判断模板的Excel类型,解析模板
		try {
			wb = getCloneWorkBook(params);
			// step 3. 删除其他的sheet
			for (int i = wb.getNumberOfSheets()-1; i >= 0; i--) {
				if (i != params.getSheetNum()) {
					wb.removeSheetAt(i);
				}
			}
			if(StringUtils.isNotEmpty(params.getSheetName())){
				wb.setSheetName(0, params.getSheetName());
			}
			// step 4. 解析模板
			parseTemplate(wb.getSheetAt(0), map);
			if(dataSet != null){
				// step 5. 正常的数据填充
				addDataToSheet(params, pojoClass, dataSet, wb.getSheetAt(0), wb);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return wb;
	}
	/**
	 * 克隆excel防止操作原对象,workbook无法克隆,只能对excel进行克隆
	 * @param params 
	 * @throws Exception 
	 *@Author JueYue
	 *@date   2013-11-11
	 */
	private static Workbook getCloneWorkBook(TemplateExportParams params) throws Exception {
		Workbook wb = null;
		String path = Thread.currentThread().getContextClassLoader().getResource("").toURI().getPath();
		path = path.replace("WEB-INF/classes/","");
		path = path.replace("file:/","")+params.getTemplateUrl();
		FileInputStream fileis  = new FileInputStream(path);
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
	    byte[] buffer = new byte[1024];
	    int len;
	    while ((len = fileis.read(buffer)) > -1 ) {
	        baos.write(buffer, 0, len);
	    }
	    baos.flush();
	    InputStream is = new ByteArrayInputStream(baos.toByteArray()); 
	    wb = WorkbookFactory.create(is);
	    //关闭用到的流
		baos.close();
	    fileis.close();
	    is.close();
		return wb;
		
	}

	/**
	 * 往Sheet 填充正常数据
	 * 
	 * @param params
	 * @param pojoClass
	 * @param dataSet
	 * @param workbook
	 */
	private static void addDataToSheet(TemplateExportParams params,
			Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
			Workbook workbook) throws Exception {
		Drawing patriarch = sheet.createDrawingPatriarch();
		List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
		// 得到所有字段
		Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
		ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
		String targetId = null;
		if (etarget != null) {
			targetId = etarget.id();
		}
		getAllExcelField(targetId, fileds, excelParams, pojoClass, null);
		sortAllParams(excelParams);
		Iterator<?> its = dataSet.iterator();
		int index = sheet.getLastRowNum();
		while (its.hasNext()) {
			Object t = its.next();
			index += createCells(patriarch, index, t, excelParams, sheet,
					workbook);
		}
	}

	private static void parseTemplate(Sheet sheet, Map<String, Object> map)
			throws Exception {
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		while (rows.hasNext()) {
			row = rows.next();
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				setValueForCellByMap(row.getCell(i), map);
			}
		}
	}

	/**
	 * 给每个Cell通过解析方式set值
	 * 
	 * @param cell
	 * @param map
	 */
	private static void setValueForCellByMap(Cell cell, Map<String, Object> map)
			throws Exception {
		String oldString;
		try {//step 1. 判断这个cell里面是不是函数
			oldString = cell.getStringCellValue();
		} catch (Exception e) {
			return;
		}
		if(oldString!=null&&oldString.indexOf("{{") != -1){
			// setp 2. 判断是否含有解析函数
			String params;
			while (oldString.indexOf("{{") != -1) {
				params = oldString.substring(oldString.indexOf("{{")+2,
						oldString.indexOf("}}"));
				oldString = oldString.replace("{{"+params+"}}",
						getParamsValue(params.trim(), map));
			}
			cell.setCellValue(oldString);
		}
	}

	/**
	 * 获取参数值
	 * 
	 * @param params
	 * @param map
	 * @return
	 */
	private static String getParamsValue(String params, Map<String, Object> map)
			throws Exception {
		if (params.indexOf(".") != -1) {
			String[] paramsArr = params.split("\\.");
			return getValueDoWhile(map.get(paramsArr[0]), paramsArr, 1);
		}
		return map.containsKey(params)?map.get(params).toString():"";
	}

	/**
	 * 通过遍历过去对象值
	 * 
	 * @param object
	 * @param paramsArr
	 * @param index
	 * @return
	 * @throws Exception
	 * @throws InvocationTargetException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	@SuppressWarnings("rawtypes")
	private static String getValueDoWhile(Object object, String[] paramsArr,
			int index) throws Exception {
		if(object == null){return "";}
		if (object instanceof Map) {
			object = ((Map) object).get(paramsArr[index]);
		} else {
			object = ExcelPublicUtil.getMethod(paramsArr[index],
					object.getClass()).invoke(object, new Object[] {});
		}
		return (index == paramsArr.length - 1) ? (object == null?"":object.toString())
				: getValueDoWhile(object, paramsArr, ++index);
	}

	/**
	 * 对字段根据用户设置排序
	 */
	private static void sortAllParams(List<ExcelExportEntity> excelParams) {
		Collections.sort(excelParams, new ComparatorExcelField());
		for (ExcelExportEntity entity : excelParams) {
			if (entity.getList() != null) {
				Collections.sort(entity.getList(), new ComparatorExcelField());
			}
		}
	}

	/**
	 * 创建 最主要的 Cells
	 * 
	 * @param styles
	 * @throws Exception
	 */
	private static int createCells(Drawing patriarch, int index, Object t,
			List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook)
			throws Exception {
		ExcelExportEntity entity;
		Row row = sheet.createRow(index);
		row.setHeight((short) 350);
		int maxHeight = 1, cellNum = 0;
		for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
			entity = excelParams.get(k);
			if (entity.getList() != null) {
				Collection<?> list = (Collection<?>) entity.getGetMethod()
						.invoke(t, new Object[] {});
				int listC = 0;
				for (Object obj : list) {
					createListCells(patriarch, index + listC, cellNum, obj,
							entity.getList(), sheet, workbook);
					listC++;
				}
				cellNum += entity.getList().size();
				if (list != null && list.size() > maxHeight) {
					maxHeight = list.size();
				}
			} else {
				Object value = getCellValue(entity, t);
				if (entity.getType() != 2) {
					createStringCell(row, cellNum++, value.toString(), entity,
							workbook);
				} else {
					createImageCell(patriarch, entity, row, cellNum++,
							value == null ? "" : value.toString(), t, workbook);
				}
			}
		}
		// 合并需要合并的单元格
		cellNum = 0;
		for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
			entity = excelParams.get(k);
			if (entity.getList() != null) {
				cellNum += entity.getList().size();
			} else if (entity.isNeedMerge()) {
				sheet.addMergedRegion(new CellRangeAddress(index, index
						+ maxHeight - 1, cellNum, cellNum));
				cellNum++;
			}
		}
		return maxHeight;

	}

	/**
	 * 获取填如这个cell的值,提供一些附加功能
	 * 
	 * @param entity
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	private static Object getCellValue(ExcelExportEntity entity, Object obj)
			throws Exception {
		Object value = entity.getGetMethods() != null ? getFieldBySomeMethod(
				entity.getGetMethods(), obj) : entity.getGetMethod().invoke(
				obj, new Object[] {});
		// step 1 判断是不是日期,需不需要格式化
		if (StringUtils.isNotEmpty(entity.getExportFormat())
				&& StringUtils.isNotEmpty(entity.getDatabaseFormat())) {
			Date temp = null;
			if (value instanceof String) {
				SimpleDateFormat format = new SimpleDateFormat(
						entity.getDatabaseFormat());
				temp = format.parse(value.toString());
			} else if (value instanceof Date) {
				temp = (Date) value;
			}
			if (temp != null) {
				SimpleDateFormat format = new SimpleDateFormat(
						entity.getExportFormat());
				value = format.format(temp);
			}
		}
		return value == null ? "" : value.toString();
	}

	/**
	 * 创建List之后的各个Cells
	 * 
	 * @param styles
	 */
	private static void createListCells(Drawing patriarch, int index,
			int cellNum, Object obj, List<ExcelExportEntity> excelParams,
			Sheet sheet, Workbook workbook) throws Exception {
		ExcelExportEntity entity;
		Row row;
		if (sheet.getRow(index) == null) {
			row = sheet.createRow(index);
			row.setHeight((short) 350);
		} else {
			row = sheet.getRow(index);
		}
		for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
			entity = excelParams.get(k);
			Object value = getCellValue(entity, obj);
			if (entity.getType() != 2) {
				createStringCell(row, cellNum++,
						value == null ? "" : value.toString(), entity, workbook);
			} else {
				createImageCell(patriarch, entity, row, cellNum++,
						value == null ? "" : value.toString(), obj, workbook);
			}
		}
	}

	/**
	 * 多个反射获取值
	 * 
	 * @param list
	 * @param t
	 * @return
	 * @throws Exception
	 */
	private static Object getFieldBySomeMethod(List<Method> list, Object t)
			throws Exception {
		for (Method m : list) {
			if (t == null) {
				t = "";
				break;
			}
			t = m.invoke(t, new Object[] {});
		}
		return t;
	}

	/**
	 * 创建文本类型的Cell
	 * 
	 * @param row
	 * @param index
	 * @param text
	 * @param style
	 * @param entity
	 * @param workbook
	 */
	private static void createStringCell(Row row, int index, String text,
			ExcelExportEntity entity, Workbook workbook) {
		Cell cell = row.createCell(index);
		switch (entity.getType()) {
		case 1:
			RichTextString Rtext = workbook instanceof HSSFWorkbook ? new HSSFRichTextString(
					text) : new XSSFRichTextString(text);
			cell.setCellValue(Rtext);
			break;
		case 2:
			cell.setCellType(Cell.CELL_TYPE_FORMULA);
			cell.setCellFormula(entity.getCellFormula());
			break;
		}
	}

	/**
	 * 图片类型的Cell
	 * 
	 * @param patriarch
	 * 
	 * @param entity
	 * @param row
	 * @param i
	 * @param string
	 * @param obj
	 * @param workbook
	 * @throws Exception
	 */
	private static void createImageCell(Drawing patriarch,
			ExcelExportEntity entity, Row row, int i, String field,
			Object obj, Workbook workbook) throws Exception {
		if(StringUtils.isEmpty(field)){return;}
		row.setHeight((short) (50 * entity.getHeight()));
		row.createCell(i);
		ClientAnchor anchor = workbook instanceof HSSFWorkbook?
				new HSSFClientAnchor(0, 0, 0, 0, (short) i,
				row.getRowNum(), (short) (i + 1), row.getRowNum() + 1):
					new XSSFClientAnchor(0, 0, 0, 0, (short) i,
							row.getRowNum(), (short) (i + 1), row.getRowNum() + 1);
		if (entity.getExportImageType() == 1) {
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			BufferedImage bufferImg;
			try {
				String path = ExcelExportOfTemplateUtil.class.getClassLoader()
						.getResource("") + field;
				path = path.replace("WEB-INF/classes/", "");
				path = path.replace("file:/", "");
				bufferImg = ImageIO.read(new File(path));
				ImageIO.write(
						bufferImg,
						field.substring(field.indexOf(".") + 1,
								field.length()), byteArrayOut);
				patriarch.createPicture(
						anchor,
						row.getSheet()
								.getWorkbook()
								.addPicture(byteArrayOut.toByteArray(),
										Workbook.PICTURE_TYPE_JPEG));
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			byte[] value = (byte[]) (entity.getGetMethods() != null ? getFieldBySomeMethod(
					entity.getGetMethods(), obj) : entity.getGetMethod()
					.invoke(obj, new Object[] {}));
			if (value != null) {
				patriarch.createPicture(anchor, row.getSheet().getWorkbook()
						.addPicture(value, Workbook.PICTURE_TYPE_JPEG));
			}
		}

	}

	/**
	 * 获取需要导出的全部字段
	 * 
	 * @param targetId
	 *            目标ID
	 * @param filed
	 * @throws Exception
	 */
	private static void getAllExcelField(String targetId, Field[] fields,
			List<ExcelExportEntity> excelParams, Class<?> pojoClass,
			List<Method> getMethods) throws Exception {
		// 遍历整个filed
		ExcelExportEntity excelEntity;
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			// 先判断是不是collection,在判断是不是java自带对象,之后就是我们自己的对象了
			if (ExcelPublicUtil.isNotUserExcelUserThis(field, targetId)) {
				continue;
			}
			if (ExcelPublicUtil.isCollection(field.getType())) {
				ExcelCollection excel = field
						.getAnnotation(ExcelCollection.class);
				ParameterizedType pt = (ParameterizedType) field
						.getGenericType();
				Class<?> clz = (Class<?>) pt.getActualTypeArguments()[0];
				List<ExcelExportEntity> list = new ArrayList<ExcelExportEntity>();
				getExcelFieldList(targetId,
						ExcelPublicUtil.getClassFields(clz), clz, list, null);
				excelEntity = new ExcelExportEntity();
				excelEntity.setName(getExcelName(excel.exportName(), targetId));
				excelEntity
						.setOrderNum(getCellOrder(excel.orderNum(), targetId));
				excelEntity.setGetMethod(ExcelPublicUtil.getMethod(
						field.getName(), pojoClass));
				excelEntity.setList(list);
				excelParams.add(excelEntity);
			} else if (ExcelPublicUtil.isJavaClass(field)) {
				Excel excel = field.getAnnotation(Excel.class);
				excelEntity = new ExcelExportEntity();
				excelEntity.setType(excel.exportType());
				getExcelField(targetId, field, excelEntity, excel, pojoClass);
				if (getMethods != null) {
					List<Method> newMethods = new ArrayList<Method>();
					newMethods.addAll(getMethods);
					newMethods.add(excelEntity.getGetMethod());
					excelEntity.setGetMethods(newMethods);
				}
				excelParams.add(excelEntity);
			} else {
				List<Method> newMethods = new ArrayList<Method>();
				if (getMethods != null) {
					newMethods.addAll(getMethods);
				}
				newMethods.add(ExcelPublicUtil.getMethod(field.getName(),
						pojoClass));
				getAllExcelField(targetId,
						ExcelPublicUtil.getClassFields(field.getType()),
						excelParams, field.getType(), newMethods);
			}
		}
	}

	/**
	 * 判断在这个单元格显示的名称
	 * 
	 * @param exportName
	 * @param targetId
	 * @return
	 */
	private static String getExcelName(String exportName, String targetId) {
		if (exportName.indexOf(",") < 0) {
			return exportName;
		}
		String[] arr = exportName.split(",");
		for (String str : arr) {
			if (str.indexOf(targetId) != -1) {
				return str.split("_")[0];
			}
		}
		return null;
	}

	/**
	 * 获取这个字段的顺序
	 * 
	 * @param orderNum
	 * @param targetId
	 * @return
	 */
	private static int getCellOrder(String orderNum, String targetId) {
		if (isInteger(orderNum) || targetId == null) {
			return Integer.valueOf(orderNum);
		}
		String[] arr = orderNum.split(",");
		for (String str : arr) {
			if (str.indexOf(targetId) != -1) {
				return Integer.valueOf(str.split("_")[0]);
			}
		}
		return 0;
	}

	/**
	 * 判断字符串是否是整数
	 */
	private static boolean isInteger(String value) {
		try {
			Integer.parseInt(value);
			return true;
		} catch (NumberFormatException e) {
			return false;
		}
	}

	/**
	 * 
	 * @param targetId
	 * @param fields
	 * @param pojoClass
	 * @param list
	 * @param getMethods
	 * @throws Exception
	 */
	private static void getExcelFieldList(String targetId, Field[] fields,
			Class<?> pojoClass, List<ExcelExportEntity> list,
			List<Method> getMethods) throws Exception {
		ExcelExportEntity excelEntity;
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			if (ExcelPublicUtil.isNotUserExcelUserThis(field, targetId)) {
				continue;
			}
			if (ExcelPublicUtil.isJavaClass(field)) {
				Excel excel = field.getAnnotation(Excel.class);
				excelEntity = new ExcelExportEntity();
				getExcelField(targetId, field, excelEntity, excel, pojoClass);
				if (getMethods != null) {
					List<Method> newMethods = new ArrayList<Method>();
					newMethods.addAll(getMethods);
					newMethods.add(excelEntity.getGetMethod());
					excelEntity.setGetMethods(newMethods);
				}
				list.add(excelEntity);
			} else {
				List<Method> newMethods = new ArrayList<Method>();
				if (getMethods != null) {
					newMethods.addAll(getMethods);
				}
				newMethods.add(ExcelPublicUtil.getMethod(field.getName(),
						pojoClass));
				getExcelFieldList(targetId,
						ExcelPublicUtil.getClassFields(field.getType()),
						field.getType(), list, newMethods);
			}
		}
	}

	/**
	 * 
	 * @param targetId
	 * @param field
	 * @param excelEntity
	 * @param excel
	 * @param pojoClass
	 * @throws Exception
	 */
	private static void getExcelField(String targetId, Field field,
			ExcelExportEntity excelEntity, Excel excel, Class<?> pojoClass)
			throws Exception {
		excelEntity.setName(getExcelName(excel.exportName(), targetId));
		excelEntity.setWidth(excel.exportFieldWidth());
		excelEntity.setHeight(excel.exportFieldHeight());
		excelEntity.setNeedMerge(excel.needMerge());
		excelEntity.setOrderNum(getCellOrder(excel.orderNum(), targetId));
		excelEntity.setWrap(excel.isWrap());
		excelEntity.setExportImageType(excel.imageType());
		excelEntity.setType(excel.exportType());
		excelEntity.setCellFormula(excel.cellFormula());
		String fieldname = field.getName();
		excelEntity.setGetMethod(ExcelPublicUtil
				.getMethod(fieldname, pojoClass));
		if (excel.exportConvertSign() == 1 || excel.imExConvert() == 1) {
			StringBuffer getConvertMethodName = new StringBuffer("convertGet");
			getConvertMethodName
					.append(fieldname.substring(0, 1).toUpperCase());
			getConvertMethodName.append(fieldname.substring(1));
			Method getConvertMethod = pojoClass.getMethod(
					getConvertMethodName.toString(), new Class[] {});
			excelEntity.setGetMethod(getConvertMethod);
		}
	}

}