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.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
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 int maxExcelRowNum = 5000;
    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)){  
        	if(isRead){        		
        		wb = new HSSFWorkbook(new FileInputStream(filePath));  //2003-
        	}else{
        		wb = new HSSFWorkbook();  //2003-	
        	}              
        }else if(excel2007U.equals(fileType)){
        	if(isRead){
        		wb = new XSSFWorkbook(new FileInputStream(filePath));  //2007+
        	}else{
        		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);
					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>> 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){
			end = UtilExcel.maxExcelRowNum;
		}
		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){
				Cell cell = row.getCell(j);
				if(cell != null){
					if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
						dataRow.add(cell.getStringCellValue());	
					}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
						dataRow.add(df.format(cell.getNumericCellValue()));
					}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
						dataRow.add(Boolean.valueOf(cell.getBooleanCellValue()));
					}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
						dataRow.add(""+cell.getCellFormula());
						//dataRow.add(cell.getNumericCellValue());
					}else{
						dataRow.add(cell.getStringCellValue());
					}						
				}else{
					dataRow.add("");
				}
			}
			result.add(dataRow);
		}
		return result;
	}
	
	public static void main(String args[]){
		String filepath = "C:\\Users\\root\\Desktop\\千丁-6.27.xlsx";
		List<List<Object>> data = null;
		try {
			data = UtilExcel.readExcel(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 {
			UtilExcel.saveExcel("测试", data, "D:\\12345.xlsx", styleList);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}