package com.taover.util;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
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.xssf.usermodel.XSSFWorkbook;

public class UtilExcel {
	private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    private final static String CSV =".csv";   //csv
    
    private final static int maxExcelRowNum = 10000;
    private final static int maxExcelColumnNum = 100;
    
    static {
    	ZipSecureFile.setMinInflateRatio(-1.0d);
    }
    
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    private static Workbook getWorkbook(String filePath, boolean isRead) throws Exception{  
        Workbook wb = null;
        File tempFile = null;
        if(isRead){
        	tempFile = new File(filePath);
    		if(!tempFile.exists()){
    			throw new Exception("需要读取的文件不存在!");
    		}
        }
        if(isRead) {
        	wb = WorkbookFactory.create(tempFile);
        }else {
        	int dotIndex = filePath.lastIndexOf(".");
        	if(dotIndex < 0) {
        		throw new Exception("传入的文件没有指定扩展名");
        	}
        	String filteTypeLower = filePath.substring(dotIndex).trim().toLowerCase();
        	if(excel2003L.equals(filteTypeLower) || CSV.equals(filteTypeLower)){
            	wb = new HSSFWorkbook();  //2003-
            } else if (excel2007U.equals(filteTypeLower)){
            	wb = new XSSFWorkbook();  //2007+
            } else {  
                throw new Exception("解析的文件格式有误！");  
            }
        }
        return wb;  
    }
    
    /**
	 * 创建并保存excel表
	 * @param sheetName
	 * @param data
	 * @param path
	 */
	public static File saveExcelFromListString(String sheetName, List<String[]> data, String path) throws Exception{
		Workbook wb = UtilExcel.getWorkbook(path, false);
		//创建Excel工作簿对象
		Sheet sheet = wb.createSheet(sheetName);//创建Excel工作表对象
		for(int i=0; i<data.size(); ++i){
			Row row = sheet.createRow(i); //创建Excel工作表的行
			String[] dataRow = data.get(i);
			if(dataRow != null){
				for(int j=0; j<dataRow.length; ++j){
					Cell cell = row.createCell(j);
					String dataCell = dataRow[j];
					if(dataCell != null){
						cell.setCellValue(dataCell);
					}				
				}	
			}			
		}		
		try {
			FileOutputStream fileOut;
			File tempFile = new File(path);
			if(!tempFile.exists()){
				File parentFile = tempFile.getParentFile();
				if(!parentFile.exists()){
					parentFile.mkdirs();	
				}				
				if(!tempFile.createNewFile()){
					return null;
				}
			}
			fileOut = new FileOutputStream(tempFile);
			wb.write(fileOut);
			wb.close();
			fileOut.close();
			
			return tempFile;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
    
	/**
	 * 创建并保存excel表
	 * @param sheetName
	 * @param data
	 * @param path
	 */
	public static void saveExcel(String sheetName, List<List<Object>> data, String path) throws Exception{
		Workbook wb = UtilExcel.getWorkbook(path, false);
		//创建Excel工作簿对象
		Sheet sheet = wb.createSheet(sheetName);//创建Excel工作表对象
		for(int i=0; i<data.size(); ++i){
			Row row = sheet.createRow(i); //创建Excel工作表的行
			List<Object> dataRow = data.get(i);
			if(dataRow != null){
				for(int j=0; j<dataRow.size(); ++j){
					Cell cell = row.createCell(j);
					Object dataCell = dataRow.get(j);
					setCellValue(cell, dataCell);
				}	
			}			
		}		
		try {
			FileOutputStream fileOut;
			File tempFile = new File(path);
			if(!tempFile.exists()){
				File parentFile = tempFile.getParentFile();
				if(!parentFile.exists()){
					parentFile.mkdirs();	
				}				
				if(!tempFile.createNewFile()){
					return;
				}
			}
			fileOut = new FileOutputStream(tempFile);
			wb.write(fileOut);
			wb.close();
			fileOut.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	private static void setCellValue(Cell cell, Object data) {
		if(data != null){
			if(ClassUtil.isBasicType(data.getClass())){
				cell.setCellValue(Double.valueOf(data.toString()));
			}else if(data.getClass().getSimpleName().equals("Date")){
				cell.setCellValue((Date)data);	
			}else{
				cell.setCellValue(data.toString());
			}	
		}
	}
	
	/**
	 * 创建并保存excel表 sheet
	 * @param sheetName
	 * @param data
	 * @param path
	 */
	public static void saveExcelContailSheet(List<String> sheetList, List<List<List<Object>>> dataList, String path) throws Exception{
		if(sheetList.size() != dataList.size()){
			throw new Exception("sheet size != excel size");
		}
		Workbook wb = UtilExcel.getWorkbook(path, false);
		//创建Excel工作簿对象
		
		for (int j = 0;j< dataList.size(); j++) {
			List<List<Object>> data = dataList.get(j);
			Sheet sheet = wb.createSheet(sheetList.get(j));//创建Excel工作表对象
			for(int i=0; i<data.size(); ++i){
				Row row = sheet.createRow(i); //创建Excel工作表的行
				List<Object> dataRow = data.get(i);
				if(dataRow != null){
					for(int k=0; k<dataRow.size(); ++k){
						Cell cell = row.createCell(k);
						Object dataCell = dataRow.get(k);
						setCellValue(cell, dataCell);
					}	
				}			
			}		
		}
		
		try {
			FileOutputStream fileOut;
			File tempFile = new File(path);
			if(!tempFile.exists()){
				File parentFile = tempFile.getParentFile();
				if(!parentFile.exists()){
					parentFile.mkdirs();	
				}				
				if(!tempFile.createNewFile()){
					return;
				}
			}
			fileOut = new FileOutputStream(tempFile);
			wb.write(fileOut);
			wb.close();
			fileOut.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	/**
	 * 创建并保存excel表
	 * @param sheetName
	 * @param data
	 * @param path
	 */
	public static void saveExcel(String sheetName, List<List<Object>> data, String path, List<Short> backColorList) throws Exception{
		Workbook wb = UtilExcel.getWorkbook(path, false);
		Map<Short, CellStyle> cellStyleMap = new HashMap<Short, CellStyle>();
		Sheet sheet = wb.createSheet(sheetName);//创建Excel工作表对象
		for(int i=0; i<data.size(); ++i){
			Row row = sheet.createRow(i); //创建Excel工作表的行
			List<Object> dataRow = data.get(i);
			Short backColor = backColorList.get(i);
			CellStyle style = cellStyleMap.get(backColor);
			if(style == null){
				style = wb.createCellStyle();
				style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
				cellStyleMap.put(backColor, style);
			}
			if(backColor != null){
				style.setFillForegroundColor(backColor.shortValue());
			}
			if(dataRow != null){
				for(int j=0; j<dataRow.size(); ++j){
					Cell cell = row.createCell(j);
					Object dataCell = dataRow.get(j);					
					setCellValue(cell, dataCell);
					if(backColor != null){
						cell.setCellStyle(style);	
					}
				}	
			}			
		}		
		try {
			FileOutputStream fileOut;
			File tempFile = new File(path);
			if(!tempFile.exists()){
				File parentFile = tempFile.getParentFile();
				if(!parentFile.exists()){
					parentFile.mkdirs();	
				}				
				if(!tempFile.createNewFile()){
					return;
				}
			}
			fileOut = new FileOutputStream(tempFile);
			wb.write(fileOut);
			wb.close();
			fileOut.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcel(String filepath) throws Exception{
		File file = new File(filepath);
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		return readExcelBySheetIndex(wb, wb.getActiveSheetIndex());
	}
	
	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcel(String filepath, boolean hasLimit) throws Exception{
		File file = new File(filepath);
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		return readExcelBySheetIndex(wb, wb.getActiveSheetIndex(), hasLimit);
	}
	
	/**
	 * 读取
	 * @param filepath
	 * @return
	 * @throws Exception
	 */
	public static List<List<Object>> readExcelExcludeHideLine(String filepath) throws Exception{
		File file = new File(filepath);
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		return readExcelBySheetIndexExcludeHideLine(filepath, wb.getActiveSheetIndex());
	}
	
	/**
	 * 读取excel表--所有工作表
	 * @param path
	 */
	public static List<List<Object>> readExcelAllSheet(String filepath) throws Exception{
		File file = new File(filepath);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);		
		int sheetNumber = wb.getNumberOfSheets();
		
		for(int sheetIndex=0; sheetIndex<sheetNumber; ++sheetIndex){
			result.addAll(readExcelBySheetIndex(filepath, sheetIndex));
		}
		
		return result;
	}
	
	
	/**
	 * 读取excel表--所有工作表 Map封装
	 * @param path
	 */
	public static Map<String,List<List<Object>>> readExcelAllSheetMap(String filepath) throws Exception{
		File file = new File(filepath);
		HashMap<String, List<List<Object>>> map = new HashMap<String,List<List<Object>>>();
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);		
		int sheetNumber = wb.getNumberOfSheets();
		
		for(int sheetIndex=0; sheetIndex<sheetNumber; ++sheetIndex){
			map.put(sheetIndex+"",(readExcelBySheetIndexExcludeHideLine(filepath, sheetIndex)));
		}
		
		return map;
	}
	
	

	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcelBySheetIndex(String filepath, int sheetIndex) throws Exception{
		File file = new File(filepath);
		if(!file.exists()){
			throw new Exception("Excel["+filepath+"]文件不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);  
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return readExcelBySheet(sheet, UtilExcel.maxExcelRowNum, false);
	}
	
	private static List<List<Object>> readExcelBySheetIndex(Workbook wb, int sheetIndex) throws Exception{
		return readExcelBySheetIndex(wb, sheetIndex, true);
	}
	
	private static List<List<Object>> readExcelBySheetIndex(Workbook wb, int sheetIndex, boolean hasRowLimit) throws Exception{  
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if(hasRowLimit){
			return readExcelBySheet(sheet, UtilExcel.maxExcelRowNum, false);	
		}else {
			return readExcelBySheet(sheet, Integer.MAX_VALUE, false);
		}
	}
	
	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	private static List<List<Object>> readExcelBySheet(Sheet sheet, int rowLimit, boolean excludeRowZeroHeight) throws Exception{
		List<List<Object>> result = new ArrayList<List<Object>>();
		int start = sheet.getFirstRowNum();
		int end = sheet.getLastRowNum();
		if(end > rowLimit){
			end = rowLimit;
		}
		DecimalFormat df = new DecimalFormat("0.####");
		for(int i=start; i<end+1; ++i){
			Row row = sheet.getRow(i);
			if(row == null){
				continue;
			}
			//去除隐藏行
			if(excludeRowZeroHeight && row.getZeroHeight()){
				continue;
			}
			List<Object> dataRow = new ArrayList<Object>();
			int lastCellNum = row.getLastCellNum();
			if(lastCellNum > UtilExcel.maxExcelColumnNum){
				lastCellNum = UtilExcel.maxExcelColumnNum;
			}
			for(int j=0; j<lastCellNum; ++j){
				try {
					dataRow.add(getCellValue(row.getCell(j), df));
				}catch(Exception e) {
					dataRow.add("");
					e.printStackTrace();
				}
			}
			result.add(dataRow);
		}
		return result;
	}
	
	private static Object getCellValue(Cell cell, DecimalFormat df) {
		if(cell == null) {
			return "";
		}
		CellType currCellType = cell.getCellTypeEnum();
		
		if(currCellType.compareTo(CellType.STRING) == 0){
			return UtilString.trimCodePage(cell.getRichStringCellValue().getString());	
		}else if(currCellType.compareTo(CellType.NUMERIC) == 0){
			
			/**
			 * yyyy-MM-dd----- 14
				yyyy年m月d日---    31
				yyyy年m月-------  57
				m月d日  ----------    58
				HH:mm-----------    20
				h时mm分  -------  32
				yyyy-MM-dd HH:hh:ss 22
			 */
			System.out.println(cell.getCellStyle().getDataFormat());
			short format = cell.getCellStyle().getDataFormat();
            if (cell.getCellStyle().getDataFormat()==28 || cell.getCellStyle().getDataFormat()==22||cell.getCellStyle().getDataFormat()==31 
            		|| cell.getCellStyle().getDataFormat() == 58 || cell.getCellStyle().getDataFormat()==14 
            		|| cell.getCellStyle().getDataFormat()==57 || cell.getCellStyle().getDataFormat()==32 || cell.getCellStyle().getDataFormat()==20) {
                
            	//  如果是date类型则 ，获取该cell的date值
                //return  new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
            	SimpleDateFormat sdf = null;  
                if(format == 14 || format == 31 || format == 57 || format == 58){  
                    //日期  
                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                }else if (format == 20 || format == 32) {  
                    //时间  
                    sdf = new SimpleDateFormat("HH:mm");  
                }else {
                	sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                }
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
                return sdf.format(date);  
            } else {
            	return df.format(cell.getNumericCellValue());
            }
            
		}else if(currCellType.compareTo(CellType.BOOLEAN) == 0){
			return cell.getBooleanCellValue();
		}else if(currCellType.compareTo(CellType.FORMULA) == 0){
			return getFormulatValue(cell, df);
		}
		return "";
	}
	
	private static Object getFormulatValue(Cell cell, DecimalFormat df) {
		CellType formulatResultType = cell.getCachedFormulaResultTypeEnum();
		
		if(formulatResultType.compareTo(CellType.STRING) == 0){
			return UtilString.trimCodePage(cell.getRichStringCellValue().getString());	
		}else if(formulatResultType.compareTo(CellType.NUMERIC) == 0){
			return df.format(cell.getNumericCellValue());
		}else if(formulatResultType.compareTo(CellType.BOOLEAN) == 0){
			return cell.getBooleanCellValue();
		}else if(formulatResultType.compareTo(CellType.FORMULA) == 0){
			return "";
		}
		return "";
	}
	
	public static List<List<Object>> readExcelBySheetIndexExcludeHideLine(String filepath, int sheetIndex) throws Exception{
		File file = new File(filepath);
		if(!file.exists()){
			throw new Exception("Excel文件["+filepath+"]不存在");
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true); 
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return readExcelBySheet(sheet, UtilExcel.maxExcelRowNum, true);
	}	
	
	public static void main(String args[]){
		//String filepath = "C:\\Users\\root\\Desktop\\千丁-6.27.xlsx";
		//String filepath = "C:\\Users\\gaoming\\Desktop\\cccc.xls";
		DecimalFormat df = new DecimalFormat("0.####");
		 
		System.out.println(df.format(3.333113F));
		System.out.println(df.format(3.3));
		System.out.println(df.format(3.3300));
		
		
		String filepath = "C:\\Users\\EDZ\\Desktop\\acccc.xls";
		List<List<Object>> data = null;
		
		try {
			long start = System.currentTimeMillis();
			System.out.println(start);
			Map<String, List<List<Object>>> map = UtilExcel.readExcelAllSheetMap(filepath);
			long end = System.currentTimeMillis();
			System.out.println(end);
			System.out.println((end-start)/1000);
			
			
			//System.out.println(map);
			data = map.get("0");
			System.out.println(data);
			for (int i = 0; i < data.size(); i++) {
				System.out.println(data.get(i).get(1));
			}
//			System.out.println(data.size());
//			System.out.println(UtilExcel.readExcelAllSheetMap(filepath));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		List<Short> styleList = new ArrayList<Short>();
//		for(int i=0; i<data.size(); ++i){
//			if(i == 1)styleList.add(Short.valueOf(HSSFColor.RED.index));
//			else styleList.add(null);
//			for(int j=0; j<data.get(i).size(); ++j){
//				System.out.print(data.get(i).get(j).toString()+" : ");
//			}
//			System.out.println("");
//		}
//		try {
//			List<String> headerList = new ArrayList<String>();
//			headerList.add("shhe1");
//			headerList.add("shhe2");
//			List<List<List<Object>>> dataList = new ArrayList<List<List<Object>>>();
//			List<List<Object>> list1 = new ArrayList<List<Object>>();
//			List<Object> list11 = new ArrayList<Object>();
//			list11.add("hahaha");
//			list11.add("hahaha");
//			list11.add("hahaha");
//			list1.add(list11);
//			
//			List<List<Object>> list2 = new ArrayList<List<Object>>();
//			List<Object> list22 = new ArrayList<Object>();
//			list22.add("hahaha2");
//			list22.add("hahaha2");
//			list22.add("hahaha2");
//			list2.add(list22);
//			list2.add(list22);
//			
//			dataList.add(list1);
//			dataList.add(list2);
//			
//			UtilExcel.saveExcelContailSheet(headerList, dataList, "C:\\Users\\gaoming\\Desktop\\qwer.xls");
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
	}
}