package com.huatek.busi.common.util;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.huatek.busi.common.annotation.ExcelAnnotation;

/**
 * @ClassName: ExcelExport
 * @FullClassPath: com.frame.core.utils.ExcelExport
 * @Description: execl导出
 * @author: Arno
 * @date: Nov 9, 2017 9:44:26 AM
 * @version: 1.0
 */

public class ExcelExport<T> {
	private Logger logger = LoggerFactory.getLogger(getClass());
	private int m_iMaxRow = 65535;  // 单个sheet可写入的最大记录行数
	
	/**
	 * 
	 * @param title
	 *            标题
	 * @param dataset
	 *            集合
	 * @param out
	 *            输出流
	 */
	public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
		try {
			Iterator<T> its = dataset.iterator();
			if (dataset == null || !its.hasNext() || title == null || out == null) {
				throw new Exception("传入的数据不对！");
			}

			T ts = (T) its.next();
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为30个字节
//			sheet.setDefaultColumnWidth(30);
			// 生成一个样式
			HSSFCellStyle style = workbook.createCellStyle();
			// 设置标题样式
			style = ExcelStyle.setHeadStyle(workbook, style);
			// 生成并设置主体样式
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2 = ExcelStyle.setbodyStyle(workbook, style2);
			// 得到所有字段

			Field filed[] = ts.getClass().getDeclaredFields();
			// 标题
			List<String> exportfieldtile = new ArrayList<String>();
			// 导出的字段
			List<String> fiedName = new ArrayList<String>();
			// 遍历整个filed
			for (int i = 0; i < filed.length; i++) {
				Field f = filed[i];
				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
				// 如果设置了annottion
				if (exa != null) {
					String exprot = exa.exportName();
					// 添加到标题
					exportfieldtile.add(exprot);
					// 添加到需要导出的字段
					fiedName.add(f.getName());
				}
			}
			// 产生表格标题行
			HSSFRow row = sheet.createRow(0);
			for (int i = 0; i < exportfieldtile.size(); i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style);
				HSSFRichTextString text = new HSSFRichTextString(exportfieldtile.get(i));
				cell.setCellValue(text);
			}

			Iterator<T> it = dataset.iterator();
			int index = 0;
			// 循环整个集合
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				T t = (T) it.next();
				for (int k = 0; k < fiedName.size(); k++) {
					HSSFCell cell = row.createCell(k);
					cell.setCellStyle(style2);
					String fieldname = fiedName.get(k);
					String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
					Class<?> tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});

					String textValue = getValue(value);

					HSSFRichTextString richString = new HSSFRichTextString(textValue);
					cell.setCellValue(richString);
					sheet.autoSizeColumn(k);
				}

			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	
	/** 
	* @Title: exportExcel 
	* @Description: 导出execl,支持自动分Sheet导出
	* @createDate: Nov 9, 2017 1:20:46 PM
	* @param title
	* @param dataset
	* @return 
	*/	
	public HSSFWorkbook exportExcel(String title, Collection<T> dataset) {
		HSSFWorkbook workbook =  new HSSFWorkbook() ;
		int iPageIndex = 1;   // 当前sheet个数 
		int iMaxPage = dataset.size();   // 最大所需要的sheet数
		
		if( (iMaxPage % m_iMaxRow) == 0) {
			iMaxPage = (dataset.size() / m_iMaxRow);   // 计算可写的最大sheet页数
		}
		else {  
			iMaxPage = ((int)(dataset.size() / m_iMaxRow )) + 1;
		}
		logger.info("==> 预计共导出Sheet页[" + iMaxPage +"],数据条数:[" + dataset.size() + "]条.");
		try {
			Iterator<T> its = dataset.iterator();
			T ts = (T) its.next();
			
			Iterator<T> it = dataset.iterator();
			int index = 0;
			
			// 得到所有字段
			Field filed[] = ts.getClass().getDeclaredFields();
			
			// 标题
			List<String> exportfieldtile = new ArrayList<>();
			
			// 导出的字段
			List<String> fiedName = new ArrayList<>();
			String strTitle = title;
			
			while(iPageIndex <= iMaxPage && index < dataset.size())  
			{				
				if(dataset.size() > m_iMaxRow) {          // 记录数是否大于单个sheet的最大行数
					 strTitle = title + "(" + iPageIndex + ")";   // sheet的主标题格式: xxxx(n) 
				}				
				// 生成一个表格
				HSSFSheet sheet = workbook.createSheet(strTitle);
				
				// 设置表格默认列宽度为40个字节
				sheet.setDefaultColumnWidth(25);
				
				// 生成一个样式
				HSSFCellStyle style = workbook.createCellStyle();
				// 设置标题样式
				style = ExcelStyle.setHeadStyle(workbook, style);
				style.setAlignment(HorizontalAlignment.LEFT);
				// 生成正文样式
				HSSFCellStyle style2 = workbook.createCellStyle();
				// 设置正文样式
				style2 = ExcelStyle.setbodyStyle(workbook, style2);
				style2.setAlignment(HorizontalAlignment.LEFT);		
				if(iPageIndex == 1) {
					// 遍历整个filed
					for (int i = 0; i < filed.length; i++) {
						Field f = filed[i];
						ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
						// 如果设置了annottion
						if (exa != null) {
							String exprot = exa.exportName();
							// 添加到标题
							exportfieldtile.add(exprot);
							// 添加到需要导出的字段
							fiedName.add(f.getName());
						}
					}
				}
				
				// 产生表格标题行
				HSSFRow row = sheet.createRow(0);
				for (int i = 0; i < exportfieldtile.size(); i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(exportfieldtile.get(i));
					cell.setCellValue(text);
				}
				// 循环整个集合
				int idx = 0 ;
				while (it.hasNext()) 
				{
					index++;
					idx++;
					row = sheet.createRow(idx);
					T t = (T) it.next();
					for (int k = 0; k < fiedName.size(); k++) 
					{
						HSSFCell cell = row.createCell(k);
						style2.setWrapText(true);
						cell.setCellStyle(style2);
						String fieldname = fiedName.get(k);
						Object value = FieldUtil.getFieldValue(t, fieldname);
						String textValue = getValue(value);
						HSSFRichTextString richString = new HSSFRichTextString(textValue);
						cell.setCellValue(richString);
						/*处理中文宽度自适应问题，解决excel宽度超出255导出报错*/
//						int cloumWidth = textValue.getBytes().length*2*256;
//						if (cloumWidth > 65280) {
//							sheet.setColumnWidth(k, 65279);
//						} else {
//							sheet.setColumnWidth(k, cloumWidth);
//						}
					}
					// add by ramon 2019-04-18  对数据量大于65535条时，自动分sheet导出数据
					if( (idx % m_iMaxRow) == 0) {
						break;   // 跳出while去创建新的sheet
					}
				}
				logger.info("==> 当前数据已导出第[" + iPageIndex + "/" + iMaxPage+ "] sheet页完成, 导出数据[" + index + "]条.");
				iPageIndex ++ ;
			} // end while(iPageIndex < iMaxPage) 
			logger.info("==> 所有数据导出完成,共计导出[" + (iPageIndex -1) + "]Sheet页,总计数据[" + index + "]条.");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	}
    /**
     * @Title: exportExcel
     * @Description: 导出execl
     * @createDate: Nov 9, 2017 1:20:46 PM
     * @param title
     * @param dataset
     * @return
     */
    public HSSFWorkbook exportExcelMap(String title, List<Map<String,Object>> dataset) {
        HSSFWorkbook workbook =  new HSSFWorkbook() ;
		int iPageIndex = 1;   // 当前sheet个数 
		int iMaxPage = dataset.size();   // 最大所需要的sheet数
		
		if( (iMaxPage % m_iMaxRow) == 0) {
			iMaxPage = (dataset.size() / m_iMaxRow);   // 计算可写的最大sheet页数
		}
		else {  
			iMaxPage = ((int)(dataset.size() / m_iMaxRow )) + 1;
		}
		logger.info("==> 预计共导出Sheet页[" + iMaxPage +"],数据条数:[" + dataset.size() + "]条.");
        try {
            //Iterator<Map<String, Object>> its = dataset.iterator();
            //Map<String,Object> ts = its.next();
            
            Iterator<Map<String, Object>> pits = dataset.iterator();
            Map<String,Object> map = pits.next();
            
            // 标题
            List<String> exportfieldtile = new ArrayList<>();
            
            int index = 0;
            String strTitle = title;
            while(iPageIndex <= iMaxPage && index < dataset.size())  
			{				
				if(dataset.size() > m_iMaxRow) {          // 记录数是否大于单个sheet的最大行数
					 strTitle = title + "(" + iPageIndex + ")";   // sheet的主标题格式: xxxx(n) 
				}
				
	            // 生成一个表格
	            HSSFSheet sheet = workbook.createSheet(strTitle);
	            // 设置表格默认列宽度为15个字节
	            sheet.setDefaultColumnWidth(15);
	            // 生成一个样式
	            HSSFCellStyle style = workbook.createCellStyle();
	            // 设置标题样式
	            style = ExcelStyle.setHeadStyle(workbook, style);
	            // 生成并设置主体样式
	            HSSFCellStyle style2 = workbook.createCellStyle();
	            style2 = ExcelStyle.setbodyStyle(workbook, style2);
	            
	            // 得到所有字段	            
	            if(iPageIndex == 1) {
		            // 遍历整个filed
		            for (Map.Entry<String, Object> kv : map.entrySet()) {
		                //添加标题
		                exportfieldtile.add(kv.getKey());
		            }
	            }
	            // 产生表格标题行
	            HSSFRow row = sheet.createRow(0);
	            for (int i = 0; i < exportfieldtile.size(); i++) {
	                Cell cell = row.createCell(i);
	                cell.setCellStyle(style);
	                RichTextString text = new HSSFRichTextString(exportfieldtile.get(i));
	                cell.setCellValue(text);
	            }
	
	            int idx = 0;
	            while( idx < m_iMaxRow && !map.isEmpty()) {
	            //for (Map<String, Object> map : dataset) {
	                index ++;
	                idx ++;
	                row = sheet.createRow(idx);
	                for (int k = 0; k < exportfieldtile.size(); k++ ) {
	                    Cell cell = row.createCell(k);
	                    cell.setCellStyle(style2);
	                    Object value = map.get(exportfieldtile.get(k));
	                    String textValue = getValue(value);
	                    RichTextString richString = new HSSFRichTextString(textValue);
	                    cell.setCellValue(richString);
	                }
	                if(pits.hasNext()) {
	                	map = pits.next();
	                }	
	                else { 
	                	break;
	                }	
	                //map = pits.hasNext();
	                // add by ramon 2019-04-18  对数据大于65535条时，自动分sheet导出数据
					if( (idx % m_iMaxRow) == 0) {
						break;   // 跳出while去创建新的sheet
					}
	            }
				logger.info("==> 当前数据已导出第[" + iPageIndex + "/" + iMaxPage+ "] sheet页完成, 导出数据[" + index + "]条.");
				iPageIndex ++ ;
			} // end while(iPageIndex < iMaxPage) 
		    logger.info("==> 所有数据导出完成,共计导出[" + (iPageIndex -1) + "]Sheet页,总计数据[" + index + "]条.");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }
	
	/** 
	* @Title: exportExcel 
	* @Description: 导出execl 
	* @createDate: Nov 9, 2017 1:20:46 PM
	* @param title
	* @param dataset
	* @return 
	*/ 
	/*
	public HSSFWorkbook exportExcels(String title, Collection<T> dataset) {
		HSSFWorkbook workbook =  new HSSFWorkbook() ;
		try {
			Iterator<T> its = dataset.iterator();
			T ts = (T) its.next();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth(15);
			// 生成一个样式
			HSSFCellStyle style = workbook.createCellStyle();
			// 设置标题样式
			style = ExcelStyle.setHeadStyle(workbook, style);
			// 生成并设置主体样式
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2 = ExcelStyle.setbodyStyle(workbook, style2);
			// 得到所有字段

			Field filed[] = ts.getClass().getDeclaredFields();
			// 标题
			List<String> exportfieldtile = new ArrayList<>();
			// 导出的字段
			List<String> fiedName = new ArrayList<>();
			// 遍历整个filed
			for (int i = 0; i < filed.length; i++) {
				Field f = filed[i];
				ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
				// 如果设置了annottion
				if (exa != null) {
					String exprot = exa.exportName();
					// 添加到标题
					exportfieldtile.add(exprot);
					// 添加到需要导出的字段
					fiedName.add(f.getName());
				}
			}
			// 产生表格标题行
			HSSFRow row = sheet.createRow(0);
			for (int i = 0; i < exportfieldtile.size(); i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style);
				HSSFRichTextString text = new HSSFRichTextString(exportfieldtile.get(i));
				cell.setCellValue(text);
			}
			Iterator<T> it = dataset.iterator();
			int index = 0;
			// 循环整个集合
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				T t = (T) it.next();
				for (int k = 0; k < fiedName.size(); k++) {
					HSSFCell cell = row.createCell(k);
					cell.setCellStyle(style2);
					String fieldname = fiedName.get(k);
					String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);

					if(!Character.isUpperCase(fieldname.charAt(0)) && Character.isUpperCase(fieldname.charAt(1))){
						getMethodName = "get" + fieldname;
					}
										
					Class<?> tCls = t.getClass();
					Method getMethod = tCls.getDeclaredMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					String textValue = getValue(value);
					HSSFRichTextString richString = new HSSFRichTextString(textValue);
					cell.setCellValue(richString);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	} */
	
    /**
     * @Title: exportExcel
     * @Description: 导出execl
     * @createDate: Nov 9, 2017 1:20:46 PM
     * @param title
     * @param dataset
     * @return
     */
    public HSSFWorkbook exportExcelMaps(String title, List<Map<String,Object>> dataset) {
        HSSFWorkbook workbook =  new HSSFWorkbook() ;
        try {
            Iterator<Map<String, Object>> its = dataset.iterator();
            Map<String,Object> ts = its.next();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(title);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(15);
            // 生成一个样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置标题样式
            style = ExcelStyle.setHeadStyle(workbook, style);
            // 生成并设置主体样式
            HSSFCellStyle style2 = workbook.createCellStyle();
            style2 = ExcelStyle.setbodyStyle(workbook, style2);
            // 得到所有字段
            // 标题
            List<String> exportfieldtile = new ArrayList<>();
            // 遍历整个filed
            for (Map.Entry<String, Object> kv : ts.entrySet()) {
                //添加标题
                exportfieldtile.add(kv.getKey());
            }
            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < exportfieldtile.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);
                RichTextString text = new HSSFRichTextString(exportfieldtile.get(i));
                cell.setCellValue(text);
            }

            int index = 0;
            for (Map<String, Object> map : dataset) {
                index ++;
                row = sheet.createRow(index);
                for (int k = 0; k < exportfieldtile.size(); k++ ) {
                    Cell cell = row.createCell(k);
                    cell.setCellStyle(style2);
                    Object value = map.get(exportfieldtile.get(k));
                    String textValue = getValue(value);
                    RichTextString richString = new HSSFRichTextString(textValue);
                    cell.setCellValue(richString);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

	public String getValue(Object value) {
		String textValue = "";
		if (value == null) {
			return textValue;
		}
		if (value instanceof Boolean) {
			boolean bValue = (Boolean) value;
			textValue = "是";
			if (!bValue) {
				textValue = "否";
			}
		} else if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			textValue = sdf.format(date);
		} else if (value instanceof LocalDateTime) {
			textValue = ((LocalDateTime) value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
		} else
			textValue = value.toString();

		return textValue;
	}
}
