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();
        }*/
    }
}