package com.vci.client.utils.excel;

import java.awt.Component;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Locale;
import java.util.Map;

import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vci.client.ui.exception.VCIException;
import com.vci.client.ui.swing.VCISwingUtil;
import com.vci.client.ui.swing.components.VCIJOptionPane;

/**
 * <p>
 * Title: read infos from excel file or write infos into excel file.
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2009
 * </p>
 * <p>
 * Company: VCI
 * </p>
 * 
 * @author eddie
 * @time 2009-6-15
 * @version 1.0
 */
public class ExcelFileOperation {

	public ExcelFileOperation() {

	}

	/**
	 * 写入Excel
	 * <p>Description: </p>
	 * @author: xchao
	 * @time 2014-10-24 下午5:51:15
	 * @param path 文件路径 
	 * @param excelInfoMap 数据 key：sheetName value：String[][] 形式的数据
	 * @param settingMap 为每个sheet 设置的样式 key：sheetName value：ExcelCellStyleSettingCallback 形式的数据
	 * @return 是否写入成功
	 */
	public boolean writeExcelFileInfo(String path, Map<String, String[][]> excelInfoMap, 
			Map<String, ExcelCellStyleSettingCallback> settingMap){
		boolean isSucess = false;
		if(path == null || excelInfoMap == null){
			return isSucess;
		}
		FileOutputStream fos = null; 
		try {
			fos = new FileOutputStream(path);
			Workbook wb = null;
			boolean isEndWithXlsx = path.endsWith(".xlsx");
			if(isEndWithXlsx){
				SXSSFWorkbook swb = new SXSSFWorkbook(100);
				swb = new SXSSFWorkbook(100);
				swb.setCompressTempFiles(true);
				wb = swb;
			} else{
				// 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
				wb = new HSSFWorkbook();
			}
			Iterator<String> its = excelInfoMap.keySet().iterator();
			while(its.hasNext()){
				String sheetName = its.next();
				Sheet sheet = wb.createSheet(sheetName);
				String[][] excelInfos = excelInfoMap.get(sheetName);
				if(excelInfos == null) continue;
				ExcelCellStyleSettingCallback setting = settingMap == null ? null : settingMap.get(sheetName);
				isSucess &= writeExcelInfo(wb, sheet, excelInfos, setting, isEndWithXlsx);
			}
			wb.write(fos);
			fos.close();
			isSucess = true;
		} catch (Exception e) {
			e.printStackTrace();
			isSucess = false;
			throw new RuntimeException(e);
		} finally{
			if(fos != null){
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return isSucess;
	}
	
	private boolean writeExcelInfo(Workbook wb, Sheet sheet, String[][] excelInfos, ExcelCellStyleSettingCallback setting, boolean isEndWithXlsx){
		boolean isSucess = false;
		WorkboolStyleSetting wbStyleSetting = null;
		LinkedHashMap<String, CellStyle> styleMap = null;
		if(setting != null){
			wbStyleSetting = setting.doSetWorkbookStyle(wb);
			styleMap = wbStyleSetting.getStyleMap();
		}

		// 文本类型列样式
		DataFormat fmt = wb.createDataFormat();
		CellStyle textStyle = wb.createCellStyle();
		textStyle.setDataFormat(fmt.getFormat("@"));

		int row = excelInfos.length;
		int column = -1;
		for (int i = 0; i < row; i++) {
			Row rowData = sheet.createRow(i);
			column = excelInfos[i].length;
			for (int j = 0; j < column; j++) {
				Cell cell = rowData.createCell(j);
				if (excelInfos[i][j] == null) {
					cell.setCellValue("");
				} else {
					cell.setCellValue("" + excelInfos[i][j]);
				}
				cell.setCellType(Cell.CELL_TYPE_STRING);
				if(styleMap != null && styleMap.containsKey(""+i+"*"+j+"")){
					CellStyle cs = styleMap.get(""+i+"*"+j+"");
					cs.setDataFormat(textStyle.getDataFormat());
					cell.setCellStyle(cs);
				} else{
					cell.setCellStyle(textStyle);
				}
			}
		}
		
		// add by xchao 2013.10.28 begin
		if(!isEndWithXlsx){
			// 设置列的单元格类型为文本
			for(int i = 0; i < 256; i++){
				sheet.setDefaultColumnStyle(i, textStyle);
			}
		}
		// add by xchao 2013.10.28 end
		isSucess = true;
		return isSucess;
	}
	
	public boolean writeExcelFileInfo(String path, String sheetName, String[][] excelInfos, ExcelCellStyleSettingCallback setting){
		Map<String, String[][]> excelInfoMap = new LinkedHashMap<String, String[][]>();
		excelInfoMap.put(sheetName, excelInfos);
		Map<String, ExcelCellStyleSettingCallback> settingMap = new LinkedHashMap<String, ExcelCellStyleSettingCallback>();
		settingMap.put(sheetName, setting);
		return writeExcelFileInfo(path, excelInfoMap, settingMap);
	}
	
	/**
	 * write infos into a excel file. The columns of every row are not equal.
	 * 
	 * @param path
	 *            String, the file's whole path
	 * @param sheetName
	 *            String, sheet description
	 * @param excelInfos
	 *            String[][], the infos which will be writed into excel file
	 * @return
	 */
	public boolean writeExcelFileInfo(String path, String sheetName, String[][] excelInfos) {
		return writeExcelFileInfo(path, sheetName,excelInfos, null);
	}

	/**
	 * 检查指定路径的excel文件是否是能够支持的EXCEL文件
	 * @param parentComponent 父容器对象
	 * @param path 文件完整路径 
	 * @return
	 */
	public boolean isSupportExcelFormat(Component parentComponent, String path){
		boolean res = false;
		boolean isXls = (path.endsWith(".xls") || path.endsWith(".xlsx"));
		// 如果是.xlsx格式，检查格式问题，会有卡钝现象，故简化处理
		if(isXls){
			return true;
		}
		File file = new File(path);
		// 如果文件后缀是excel格式，但文件不存在时，此时返回true
		if(isXls && !file.exists()) {
			return true;
		}
		if(file.length() == 0) return true;
		Workbook wb = null;
		POIFSFileSystem fs = null;
		try{
			// XSSFWorkbook 支持OFFICE EXCEL 2007，
			// 但不支持2003以下的，包含2003
			wb = new XSSFWorkbook(new FileInputStream(path));
			res = true;
		}catch(Exception ex){
			try {
				// 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
				fs = new POIFSFileSystem(new FileInputStream(path));
				wb = new HSSFWorkbook(fs);
				res = true;
			} catch(OldExcelFormatException e){
				e.printStackTrace();
				VCIJOptionPane.showError(parentComponent, "Excel文件版本错误，当前支支持 Excel 97/2000/XP/2003/2007/2010");
				res = false;
			} catch(IOException e){
				e.printStackTrace();
				if(e.getMessage().contains("Invalid header signature")){
					VCIJOptionPane.showError(parentComponent, "文件类型错误，不是有效的Excel文件！");
				}
				res = false;
			} catch(Exception e){
				e.printStackTrace();
				VCIJOptionPane.showError(parentComponent, "检查文件是否是有效的Excel文件时发生错误！\n" + e.getMessage());
				res = false;
			}
		}
		return res;
	}
	
	/**
	 * 返回Excel文件里数据（全部Sheet）
	 * @param path Excel 文件完整路径
	 * @return
	 * @throws VCIException
	 */
	public String[][][] readExcelFileInfos(String path) throws VCIException{
		String[][][] res = new String[][][]{}; 
		Workbook wb = getWorkBook(path);
		if(wb == null){
			return res;
		}
		int sheetCount = wb.getNumberOfSheets();
		res = new String[sheetCount][0][0];
		for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++){
			res[sheetIndex] = readExcelFileInfo(path, sheetIndex);
		}
		return res;
	}

	/**
	 * 返回Excel文件里的数据（根据指定的Sheet名称）
	 * @param path Excel 文件完整路径
	 * @param sheetName Sheet 名称
	 * @return
	 * @throws VCIException
	 */
	public String[][] readExcelFileInfo(String path, String sheetName) throws VCIException {
		String[][] res = new String[][]{}; 
		Workbook wb = getWorkBook(path);
		if(wb == null){
			return res;
		}
		int sheetIndex = wb.getSheetIndex(sheetName);
		if(sheetIndex < 0){
			return res;
		}
		return readExcelFileInfo(path, sheetIndex);
	}
	/**
	 * 返回Excel文件里的数据（默认返回第一个Sheet里的数据）
	 * @param path Excel 文件完整路径
	 * @return
	 * @throws VCIException
	 */
	public String[][] readExcelFileInfo(String path) throws VCIException {
		return readExcelFileInfo(path, 0);
	}
	
	/**
	 * 返回Excel文件里的数据（根据指定的Sheet索引）
	 * @param path Excel 文件完整路径
	 * @param sheetIndex Sheet索引（0 ~ SheetCount - 1)
	 * @return
	 * @throws VCIException
	 */
	public String[][] readExcelFileInfo(String path, int sheetIndex) throws VCIException {
		String[][] excelInfos = null;
		/**
		 * 读取逻辑&&约束
		 * 1、标题&抬头行不能出现任何的空数据列
		 * 2、标题&抬头行不能出现纯数字列
		 */
		// 取Sheet对象
		Sheet sheet = this.getWorkbookSheet(path, sheetIndex);
		if(sheet == null) return new String[0][0];
		LinkedList<String[]> datas = new LinkedList<String[]>();
		if(sheet.getLastRowNum() <= 0){
			throw new VCIException(String.valueOf("99996"), new Object[]{});// 该文件只有标题&抬头行或没有数据（连标题&抬头行都没有）！
		}

		StringBuffer sbRowValueString = new StringBuffer();
		// 取标题行有效的列数
		int firstRowColumnNum = this.getFirstRowColumnNum(sheet);
		for(int i = 0; i < sheet.getLastRowNum() + 1; i++){
			Row row = sheet.getRow(i);
			if(row == null) { // 整行都没有数据
				i++;
				continue;
			}
			sbRowValueString = new StringBuffer();    
			LinkedList<String> rowValue = new LinkedList<String>();
			for (int j = 0; j < firstRowColumnNum; j++) {
				Cell cell = row.getCell(j);
				String cellValueString = "";
				if(i == 0){// 标题行
					// 移除数据列中前可在存在的"'"
					String val = cell.getStringCellValue();
					while(val.startsWith("'")){
						val = val.substring(1);
					}
					cellValueString = val;
					rowValue.add(val);
					sbRowValueString.append(cellValueString);
				}else{// 数据行
					// 处理数据行中的空数据
					if(cell == null){
						cellValueString = "";
					}else{
						cellValueString = getCellValue(cell);
					}
					rowValue.add(cellValueString);
					sbRowValueString.append(cellValueString);
				}
			}
			// add by xchao 如果数据行的数据全部为空，则不添加
			if(sbRowValueString.length() != 0){
				datas.add(rowValue.toArray(new String[]{}));
			}
		}
		excelInfos = datas.toArray(new String[][]{});
		return excelInfos;
	}
	
	public static String getCellValue(Cell cell){
		String cellValueString = "";
		if(cell == null) return cellValueString;
		int cellType = cell.getCellType();
		NumberFormat numFormat = NumberFormat.getInstance(Locale.CHINA);
		SimpleDateFormat sdfDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// add by xchao 2012.11.22 处理日期类型和Cell
		if(cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)){
			Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
			cellValueString = sdfDateTime.format(date);
		}
		else if(cellType == Cell.CELL_TYPE_BLANK){
			cellValueString = "";
		} else if(cellType == Cell.CELL_TYPE_BOOLEAN){
			cellValueString = cell.getBooleanCellValue() ? "1" : "0";
		} else if(cellType == Cell.CELL_TYPE_ERROR){
			cellValueString = "";
		} else if(cellType == Cell.CELL_TYPE_FORMULA){
			try{
				cellValueString = String.valueOf(cell.getNumericCellValue());
			} catch (Exception e) {
				cellValueString = cell.getRichStringCellValue().getString();
			}
		} else if(cellType == Cell.CELL_TYPE_NUMERIC){
//			String[] dataFormatStrings = cell.getCellStyle().getDataFormatString().replace("_", "").trim().split("\\.");
//			int maxFracDigits = 0;
//			if(dataFormatStrings.length == 2){
//				maxFracDigits = dataFormatStrings[1].length();
//				numFormat.setMaximumFractionDigits(maxFracDigits);
//				cellValueString = numFormat.format(cell.getNumericCellValue()).replace(",", "").trim();
//			} else {
//				cellValueString = cell.toString();
//			}
			//cell.setCellType(Cell.CELL_TYPE_STRING);
			//cellValueString = cell.getStringCellValue();
			DecimalFormat df = new DecimalFormat("#.#########");
			cellValueString = df.format(cell.getNumericCellValue());
		} else if(cellType == Cell.CELL_TYPE_STRING){
			// 移除数据列中前可在存在的"'"
			String val = cell.getStringCellValue();
			while(val.startsWith("'")){
				val = val.substring(1);
			}
			cellValueString = val;
		} 
		return cellValueString;
	}

	/**
	 * 返回Sheet的标题&抬头行的实际有效的列数
	 * @param sheet
	 * @return
	 * @throws VCIException
	 */
	private int getFirstRowColumnNum(Sheet sheet) throws VCIException{
		int i = 0;
		Row row = sheet.getRow(i);
		int rowCellNum = row.getLastCellNum();
		int firstRowColumnNum = 0;
		for (int j = 0; j < rowCellNum; j++) {
			int columnIndex = j;
			int columnIndexToShow = columnIndex + 1;
			Cell cell = row.getCell(j);
			int cellType = 0;
			if(i == 0){// 标题行
				if(cell == null){
					throw new VCIException(String.valueOf("99995"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列的不能为空！
				}
				cellType = cell.getCellType();
				if(cellType == Cell.CELL_TYPE_BLANK){
					throw new VCIException(String.valueOf("99995"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列的不能为空！
				}
				if(cellType == Cell.CELL_TYPE_NUMERIC || cellType == Cell.CELL_TYPE_NUMERIC){
					throw new VCIException(String.valueOf("99994"), new Object[]{columnIndexToShow});// 标题&抬头行中的第{0}列不能是纯数字！
				}
				// 计算标题行有效列数
				firstRowColumnNum += 1;
			}
		}
		return firstRowColumnNum;
	}
	
	/**
	 * 批回指定文件的Sheet
	 * @param path excel文件完整路径
	 * @param sheetIndex Sheet 索引（从0 ~ SheetCount-1）
	 * @return
	 * @throws VCIException
	 */
	private Sheet getWorkbookSheet(String path, int sheetIndex) throws VCIException{
		Workbook wb = getWorkBook(path);
		if(wb == null) return null;
		int sheetCount = wb.getNumberOfSheets();
		if(sheetIndex < 0 || sheetIndex >= sheetCount) return null;
		return wb.getSheetAt(sheetIndex);
	}
	
	private Workbook getWorkBook(String path) throws VCIException{
		Workbook wb = null;
		POIFSFileSystem fs = null; 
		try{
			// XSSFWorkbook 支持OFFICE EXCEL 2007，
			// 但不支持2003以下的，包含2003
			wb = new XSSFWorkbook(new FileInputStream(path));
		}catch(Exception ex){
			try {
				// 用 HSSFWorkbook 处理2003或以下版本EXCEL文件
				fs = new POIFSFileSystem(new FileInputStream(path));
				wb = new HSSFWorkbook(fs);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				throw new VCIException("99999", new String[]{path});
			} catch (IOException e) {
				e.printStackTrace();
				throw new VCIException("99998", new String[]{path});
			} catch(Exception e){
				e.printStackTrace();
				throw new VCIException("99997", new String[]{path, e.getMessage()});
			}
		}
		return wb;
	}
	
	public static void main(String[] args) throws VCIException{
		String path = "E:/";
		String name = "test.xlsx";
		String[][] datas = new ExcelFileOperation().readExcelFileInfo(path + name);
		for(String[] row : datas){
			for(String val : row){
				System.out.print(val + "\t");
			}
			System.out.println("");
		}
//		writeTest();
//		readTest();
	}
	private static void readTest() throws VCIException{
		String path = VCISwingUtil.getExcelFileURL(null, false, "");
		System.out.println("readTest1:");
		readTest1(path);
		
		System.out.println("readTest2:");
		readTest2(path);
		
		System.out.println("readTest3:");
		readTest3(path);
		
		System.out.println("readTest4:");
		readTest4(path);
	}
	private static void readTest1(String path) throws VCIException{
		out(new ExcelFileOperation().readExcelFileInfo(path));
	}
	private static void readTest2(String path) throws VCIException{
		if(path == null) return;
		out(new ExcelFileOperation().readExcelFileInfo(path, 0));
		out(new ExcelFileOperation().readExcelFileInfo(path, 1));
		out(new ExcelFileOperation().readExcelFileInfo(path, 2));
		out(new ExcelFileOperation().readExcelFileInfo(path, 3));
	}
	private static void readTest3(String path) throws VCIException{
		if(path == null) return;
		out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet1"));
		out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet2"));
		out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet3"));
		out(new ExcelFileOperation().readExcelFileInfo(path, "Sheet4"));
	}
	private static void readTest4(String path) throws VCIException{
		if(path == null) return;
		out(new ExcelFileOperation().readExcelFileInfos(path));
	}
	private static void out(String[][][] datas){
		for(String[][] data : datas){
			out(data);
		}
	}
	private static void out(String[][] datas){
		for(String[] row : datas){
			for(String cell : row){
				System.out.print(cell + "\t");
			}
			System.out.print("\n");
		}
	}
	private static void writeTest(){
		ExcelFileOperation efo = new ExcelFileOperation();
		boolean isRead = false;
		File file = null;
		String filePath = VCISwingUtil.getExcelFileURL(null, !isRead, "");
		if(filePath != null){
			file = new File(filePath);
		}
		if(file == null){
			System.exit(0);
		}
		LinkedList<String[]> list = new LinkedList<String[]>();
		for(int i = 0; i < 100; i++){
			LinkedList<String> row = new LinkedList<String>();
			for(int j = 0; j < 52; j++){
				row.add(String.valueOf(j));
			}
			list.add(row.toArray(new String[]{}));
		}
		String[][] excelInfos = {{"a","b","c"},{"123","456","789"}};
		excelInfos = list.toArray(new String[][]{});
		ExcelCellStyleSettingCallback setting = new ExcelCellStyleSettingCallback() {
			public WorkboolStyleSetting doSetWorkbookStyle(final Workbook workbook) {
				WorkboolStyleSetting setting = new WorkboolStyleSetting() {
					public LinkedHashMap<String, CellStyle> getStyleMap() {
						LinkedHashMap<String, CellStyle> styleMap = new LinkedHashMap<String, CellStyle>();
						org.apache.poi.ss.usermodel.CellStyle style = workbook.createCellStyle();
						org.apache.poi.ss.usermodel.Font font = (org.apache.poi.ss.usermodel.Font)workbook.createFont();
						font.setColor(HSSFFont.COLOR_RED);
						style.setFont(font);
						styleMap.put("0*0", style);
						return styleMap;
					}
				};
				return setting;
			}
		};
		Map<String, String[][]> excelInfoMap = new LinkedHashMap<String, String[][]>();
		excelInfoMap.put("sheet1", excelInfos);
		excelInfoMap.put("sheet2", excelInfos);
		excelInfoMap.put("sheet3", excelInfos);
		
		Map<String, ExcelCellStyleSettingCallback> settingMap = new HashMap<String, ExcelCellStyleSettingCallback>();
		settingMap.put("sheet1", setting);
		efo.writeExcelFileInfo(file.getAbsolutePath(), excelInfoMap, settingMap);
	}
}