package com.taover.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
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.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 = 50;
    
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    private static Workbook getWorkbook(String filePath, boolean isRead) throws Exception{  
        Workbook wb = null;
        if(isRead){
        	File tempFile = new File(filePath);
    		if(!tempFile.exists()){
    			throw new Exception("需要读取的文件不存在!");
    		}
        }
        String fileType = filePath.substring(filePath.lastIndexOf("."));        
        if(excel2003L.equals(fileType.trim().toLowerCase())){  
        	if(isRead){        		
        		wb = new HSSFWorkbook(new FileInputStream(filePath));  //2003-
        	}else{
        		wb = new HSSFWorkbook();  //2003-	
        	}              
        }else if(excel2007U.equals(fileType.trim().toLowerCase())){
        	if(isRead){
        		wb = new XSSFWorkbook(new FileInputStream(filePath));  //2007+
        	}else{
        		wb = new XSSFWorkbook();  //2007+	
        	}              
        }else if(CSV.equals(fileType.trim().toLowerCase())){
        	if(isRead){        		
        		wb = new HSSFWorkbook(new FileInputStream(filePath));  //2003-
        	}else{
        		wb = new HSSFWorkbook();  //2003-	
        	}
        } 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);
					if(dataCell != null){
						if(dataCell.getClass().isPrimitive()){
							cell.setCellValue(Double.valueOf(dataCell.toString()));
						}else if(dataCell.getClass().getSimpleName().equals("Date")){
							cell.setCellValue((Date)dataCell);	
						}else{
							cell.setCellValue(dataCell.toString());
						}	
					}				
				}	
			}			
		}		
		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(style.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);					
					if(dataCell != null){
						if(dataCell.getClass().isPrimitive()){
							cell.setCellValue(Double.valueOf(dataCell.toString()));
						}else if(dataCell.getClass().getSimpleName().equals("Date")){
							cell.setCellValue((Date)dataCell);	
						}else{
							cell.setCellValue(dataCell.toString());
						}	
					}
					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);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			return result;
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		return readExcelBySheetIndex(filepath, wb.getActiveSheetIndex());
	}
	
	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcel(String filepath, int rowLimit) throws Exception{
		File file = new File(filepath);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			return result;
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		return readExcelBySheetIndex(filepath, wb.getActiveSheetIndex());
	}
	
	/**
	 * 读取
	 * @param filepath
	 * @return
	 * @throws Exception
	 */
	public static List<List<Object>> readExcelExcludeHideLine(String filepath) throws Exception{
		File file = new File(filepath);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			return result;
		}
		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()){
			return result;
		}
		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表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcelBySheetIndex(String filepath, int sheetIndex) throws Exception{
		File file = new File(filepath);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			return result;
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		//创建Excel工作簿对象
		DecimalFormat df = new DecimalFormat("0");  
		Sheet sheet = wb.getSheetAt(sheetIndex);
		int start = sheet.getFirstRowNum();
		int end = sheet.getLastRowNum();
		if(end > UtilExcel.maxExcelRowNum){
			throw new Exception("目前系统只支持读取10000行以内记录，您当前Excel行数过大");
		}
		for(int i=start; i<end+1; ++i){
			Row row = sheet.getRow(i);
			if(row == null){
				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;
	}
	
	/**
	 * 读取excel表--当前激活的工作表
	 * @param path
	 */
	public static List<List<Object>> readExcelBySheet(Sheet sheet, int rowLimit) 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;
			}
			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){
			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);
		List<List<Object>> result = new ArrayList<List<Object>>();
		if(!file.exists()){
			return result;
		}
		Workbook wb = UtilExcel.getWorkbook(filepath, true);
		//创建Excel工作簿对象
		DecimalFormat df = new DecimalFormat("0");  
		Sheet sheet = wb.getSheetAt(sheetIndex);
		int start = sheet.getFirstRowNum();
		int end = sheet.getLastRowNum();
		if(end > UtilExcel.maxExcelRowNum){
			throw new Exception("目前系统只支持读取10000行以内记录，您当前Excel行数过大");
		}
		for(int i=start; i<end+1; ++i){
			Row row = sheet.getRow(i);
			if(row == null){
				continue;
			}
			//去除隐藏行
			if(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){
				Cell cell = row.getCell(j);
				if(cell != null){
					CellType currCellType = cell.getCellTypeEnum();
					
					if(currCellType.compareTo(CellType.STRING) == 0){
						dataRow.add(UtilString.trimCodePage(cell.getRichStringCellValue().getString()));	
					}else if(currCellType.compareTo(CellType.NUMERIC) == 0){
						dataRow.add(df.format(cell.getNumericCellValue()));
						
					}else if(currCellType.compareTo(CellType.BOOLEAN) == 0){
						dataRow.add(cell.getBooleanCellValue());
					}else if(currCellType.compareTo(CellType.FORMULA) == 0
							|| currCellType.compareTo(CellType.BLANK) == 0
							|| currCellType.compareTo(CellType.ERROR) == 0){
						dataRow.add("");
					}				
					
				}else{
					dataRow.add("");
				}
			}
			result.add(dataRow);
		}
		return result;
	}
	
	public static List<List<Object>> readExcelBySheetExcludeHideLine(Sheet sheet, int rowLimit) throws Exception{
		List<List<Object>> result = new ArrayList<List<Object>>();
		DecimalFormat df = new DecimalFormat("0");
		int start = sheet.getFirstRowNum();
		int end = sheet.getLastRowNum();
		if(end > rowLimit){
			end = rowLimit;
		}
		for(int i=start; i<end+1; ++i){
			Row row = sheet.getRow(i);
			if(row == null){
				continue;
			}
			//去除隐藏行
			if(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){
				Cell cell = row.getCell(j);
				if(cell != null){
					CellType currCellType = cell.getCellTypeEnum();
					
					if(currCellType.compareTo(CellType.STRING) == 0){
						dataRow.add(UtilString.trimCodePage(cell.getRichStringCellValue().getString()));	
					}else if(currCellType.compareTo(CellType.NUMERIC) == 0){
						dataRow.add(df.format(cell.getNumericCellValue()));						
					}else if(currCellType.compareTo(CellType.BOOLEAN) == 0){
						dataRow.add(cell.getBooleanCellValue());
					}else if(currCellType.compareTo(CellType.FORMULA) == 0
							|| currCellType.compareTo(CellType.BLANK) == 0
							|| currCellType.compareTo(CellType.ERROR) == 0){
						dataRow.add("");
					}				
					
				}else{
					dataRow.add("");
				}
			}
			result.add(dataRow);
		}
		return result;
	}
	
	public static void main(String args[]){
		//String filepath = "C:\\Users\\root\\Desktop\\千丁-6.27.xlsx";
		String filepath = "C:\\Users\\EDZ\\Desktop\\榴莲1 (5).xlsx";
		List<List<Object>> data = null;
		try {
			data = UtilExcel.readExcel(filepath);
			System.out.println(data);
			System.out.println(data.size());
			System.out.println(UtilExcel.readExcelExcludeHideLine(filepath).size());
		} 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 {
//			UtilExcel.saveExcel("测试", data, "D:\\12345.xlsx", styleList);
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
	}
}