package com.qxueyou.scc.base.util;

//import java.io.FileNotFoundException;
//import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
//import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;

import com.qxueyou.scc.base.model.ExcelExportParam;
//import com.qxueyou.scc.user.model.UserRegistration;
//import com.qxueyou.scc.user.model.UserRegistrationExport;

/**
 * 
 * 
 * @param <T>
 * 应用泛型，代表任意一个符合javabean风格的类
 * boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 */
public class ExcelExportUtils<T> {
	
	private static Logger log = LogManager.getLogger("ExcelExport");
	
	/**
	 * 设置标题样式,字体
	 * @param objStyle
	 * @param workbook
	 */
	private void initTitleCellStyle(HSSFCellStyle objStyle,HSSFWorkbook workbook){
		objStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		objStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		objStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		objStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		
		// 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到标题样式
        objStyle.setFont(font);
	}
	
	/**
	 * 设置导出内容样式
	 * @param objStyle
	 * @param workbook
	 */
	private void initContentCellStyle(HSSFCellStyle objStyle,HSSFWorkbook workbook){
		objStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		objStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		objStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		objStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		objStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	}
	
	/**
	 * 是否指定列宽
	 * @param sheet
	 * @param lstColumnWidth
	 */
	private void initColumnWidth(HSSFSheet sheet,List<Integer> lstColumnWidth){
		if(lstColumnWidth != null){
		    for(int i=0;i<lstColumnWidth.size();i++){
		    	sheet.setColumnWidth(i, lstColumnWidth.get(i));
		    }	
		}
	}
	
	/**
	 * 初始化导出标题
	 * @param row
	 * @param headers
	 * @param titleStyle
	 */
	private void initTitleRow(HSSFRow row,String[] headers,HSSFCellStyle titleStyle,List<String> lstColumn){
		for (int i = 0; i < headers.length && (null == lstColumn || i <= lstColumn.size()); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
	}
	
	/**
	 * 按照T，导出所有列内容
	 * @param objExcelExportParam
	 */
    public void exportExcel(ExcelExportParam<T> objExcelExportParam) {
    	this.exportExcel(objExcelExportParam,false);
    }
    
    /**
     * 指定列导出
     * @param objExcelExportParam
     */
    public void exportExcelByColomn(ExcelExportParam<T> objExcelExportParam) {
    	this.exportExcel(objExcelExportParam,true);
    }
    
	/**
	 * 导出excel实现
	 * @param objExcelExportParam
	 * @param exportByCol
	 */
	public void exportExcel(ExcelExportParam<T> objExcelExportParam,boolean exportByCol){
		//获取参数：
    	String title = objExcelExportParam.getTitle();
    	Collection<T>   colData = objExcelExportParam.getColData();
    	OutputStream out = objExcelExportParam.getOut();
    	String pattern = objExcelExportParam.getPattern();
    	if(null == pattern){
    		pattern = "yyyy-MM-dd";
    	}
    	boolean numberFormat = objExcelExportParam.getNumberFormat();
    	List<String> lstColumn = null;
        if(exportByCol){
        	lstColumn = objExcelExportParam.getLstColumn();
        }
        
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = null;
        // 生成一个表格
        if(StringUtils.isNotBlank(title)){
        	sheet = workbook.createSheet(title);
        }else{
        	sheet = workbook.createSheet("Sheet1");
        }
        // 设置表格默认列宽度为18个字节
        sheet.setDefaultColumnWidth(18);
        // 设置表格默认行高度
        sheet.setDefaultRowHeight((short)300);
        //是否指定列宽
        initColumnWidth(sheet,objExcelExportParam.getLstColumnWidth());
        // 标题样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        initTitleCellStyle(titleStyle,workbook);
        // 内容样式
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        initContentCellStyle(contentStyle,workbook);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        initTitleRow(row,objExcelExportParam.getHeaders(),titleStyle,lstColumn);

        // 遍历集合数据，产生数据行
        Iterator<T> it = colData.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            
            //默认给序号列
            HSSFCell cell = row.createCell(0);
    		cell.setCellStyle(contentStyle);
    		HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(index));
			HSSFFont font3 = workbook.createFont();
			richString.applyFont(font3);
			cell.setCellValue(richString);
            
            //指定列导出使用
            if(exportByCol){
            	for(int j = 0; j < lstColumn.size() ; j++ ){
            		doExportExcel(row,contentStyle,pattern,numberFormat,workbook,lstColumn.get(j),t,j+1);
            	}
            }else{
            	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                	if(!"serialVersionUID".equals(fields[i].getName())){
                		doExportExcel(row,contentStyle,pattern,numberFormat,workbook,fields[i].getName(),t,i);
                	}
                }
            }
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
        	
        }
	}
	
	private void doExportExcel(HSSFRow row,HSSFCellStyle contentStyle,String pattern,boolean numberFormat,HSSFWorkbook workbook,String fieldName,T t,int i){
		HSSFCell cell = row.createCell(i);
		cell.setCellStyle(contentStyle);
		//boolean类型默认使用get，而非is
		String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
		try {
			@SuppressWarnings("rawtypes")
			Class tCls = t.getClass();
			@SuppressWarnings("unchecked")
			Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
			Object value = getMethod.invoke(t, new Object[] {});
			// 格式化内容
			String textValue = "";
			if (value instanceof Date) {
				Date date = (Date) value;
				SimpleDateFormat sdf = new SimpleDateFormat(pattern);
				textValue = sdf.format(date);
			}else{
				if(value != null){
					textValue = String.valueOf(value);
				}
			}
			//利用正则表达式判断textValue是否全部由数字组成
			if (null != textValue) {
				Pattern p = Pattern.compile("^//d+(//.//d+)?$");
				Matcher matcher = p.matcher(textValue);
				if (matcher.matches() && numberFormat) {
					// 是数字当作double处理
					cell.setCellValue(Double.parseDouble(textValue));
				} else {
					HSSFRichTextString richString = new HSSFRichTextString(textValue);
					HSSFFont font3 = workbook.createFont();
					richString.applyFont(font3);
					cell.setCellValue(richString);
				}
			}
		} catch (SecurityException e) {
			log.error(e);
		} catch (NoSuchMethodException e) {
			log.error(e);
		} catch (IllegalArgumentException e) {
			log.error(e);
		} catch (IllegalAccessException e) {
			log.error(e);
		} catch (InvocationTargetException e) {
			log.error(e);
		} finally {
			
		}
	}
	
    public static void main(String[] args) {
    	System.out.println(new Date().getTime());
        /*// 测试学生
    	ExcelExportUtils<UserRegistrationExport> ex = new ExcelExportUtils<UserRegistrationExport>();
        String[] headers = { "序号","班级", "姓名", "性别" ,"报名码" ,"手机号" ,"其他联系方式","状态"};
        List<UserRegistrationExport> dataset = new ArrayList<UserRegistrationExport>();
        UserRegistrationExport u = new UserRegistrationExport();
        u.setClassName("高级JAVA工程师一班");
        u.setMobilePhone("13312886688");
        u.setSalesCode("123.34");
        u.setSex(false);
        u.setStatus(UserRegistration.STATUS_ACTIVE);
        u.setUserName("刘美幸子");
        dataset.add(u);
        ExcelExportParam<UserRegistrationExport> obj = new ExcelExportParam<UserRegistrationExport>();
        obj.setColData(dataset);
        obj.setHeaders(headers);
        
        try {
            OutputStream out = new FileOutputStream("D://testExportExcelAll.xls");
            obj.setOut(out);
            ex.exportExcel(obj);
            out.close();
            System.out.println("excel导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        try {
            OutputStream out = new FileOutputStream("D://testExportExcelByCol.xls");
            List<String> lst = new ArrayList<String>();
            lst.add("className");
            lst.add("userName");
            obj.setLstColumn(lst);
            obj.setOut(out);
            ex.exportExcelByColomn(obj);
            out.close();
            System.out.println("excel指定列导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }*/
    }
}