package *.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import *.domain.ExcelSheet;
import *.ICate_tabService;
import *.IDataService;
import *.IFieldService;
import *.ITableService;
import *.impl.Cate_tabServiceImpl;
import *.impl.DataServiceImpl;
import *.impl.FieldServiceImpl;
import *.impl.TableServiceImpl;

// TODO: Auto-generated Javadoc
/**
 * The Class ExcelUtil.
 *
 * @author jsyzthz@gmail.com
 */
public class ExcelUtil {
	private final String DEFINE_TAG = "";
	private final String DEFINE_ERROR = "SYS_ERROR";
	private final String DEFINE_MERGED = "SYS_MERGED";
	ITableService tableService = new TableServiceImpl();
	IFieldService fieldService = new FieldServiceImpl();
	IDataService dataService = new DataServiceImpl();
	ICate_tabService cate_tabService = new Cate_tabServiceImpl();

	
	/**
	 * 根据sheetIndex读取excel表中具体的sheet信息.
	 *
	 * @param filePath the file path
	 * @param sheetIndex the sheet index
	 * @return Sheet
	 */
	public Sheet createSheet(String filePath, int sheetIndex){
		Sheet sheet;
		try{
			sheet=createHSSFSheet(filePath,sheetIndex);
		}catch(Exception ex){
			sheet=createXSSFSheet(filePath,sheetIndex);
		}
		return sheet;
	}

	/**
	 * 处理XLS格式.
	 *
	 * @param filePath the file path
	 * @param sheetIndex the sheet index
	 * @return Sheet
	 */
	public Sheet createHSSFSheet(String filePath, int sheetIndex) {
		InputStream inp;
		try {
			inp = new FileInputStream(filePath);
			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
			HSSFSheet sheet = wb.getSheetAt(sheetIndex);
			inp.close();
			return sheet;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 处理XLSX格式.
	 *
	 * @param filePath the file path
	 * @param sheetIndex the sheet index
	 * @return Sheet
	 */
	public Sheet createXSSFSheet(String filePath, int sheetIndex) {
		Workbook wb;
		try {
			wb = new XSSFWorkbook(filePath);
			Sheet sheet = wb.getSheetAt(sheetIndex);
			return sheet;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获得一个excel文件中的所有sheet信息.
	 *
	 * @param filePath the file path
	 * @return List<ExcelSheet>
	 */
	public List<ExcelSheet> getAllSheet(String filePath) {
		InputStream inp;
		Workbook wb;
		List<ExcelSheet> sheetList = new ArrayList<ExcelSheet>();
		try {
			inp = new FileInputStream(filePath);
			wb = WorkbookFactory.create(inp);
			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				Sheet sheet = wb.getSheetAt(i);
				if (sheet.getLastRowNum() != 0) {
					Row row = sheet.getRow(0);
					Cell cell = row.getCell(0);
					String value = getCellValue(cell);
					if (!value.equals(DEFINE_ERROR) && !value.equals(DEFINE_TAG)) {
						ExcelSheet sheets = new ExcelSheet(Integer.toString(i),
								value);
						sheetList.add(sheets);
					}
				}
			}
			inp.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return sheetList;
	}

	/**
	 * 标记已经上传的表（Sheet）.
	 *
	 * @param sheetList the sheet list
	 * @return List<ExcelSheet>
	 */
	public List<ExcelSheet> signExistSheet(List<ExcelSheet> sheetList) {
		List<ExcelSheet> list = new ArrayList<ExcelSheet>();
		if (sheetList.size() == 0) {
			return null;
		}
		for (ExcelSheet excelSheet : sheetList) {
			boolean flag = tableService.tableExist(excelSheet.getSheetValue());
			if (flag) {
				excelSheet.setUsed(true);
			}
			list.add(excelSheet);
		}
		return list;
	}

	/**
	 * 读取一个指定sheet中的所有单元格数据，标记空值、错误、和合并单元格.
	 *
	 * @param sheet the sheet
	 * @return String[][]
	 */
	public String[][] getAllData(Sheet sheet) {
		int rows = sheet.getPhysicalNumberOfRows();
		int cols = sheet.getRow(0).getPhysicalNumberOfCells();
		if (rows < 1 || cols < 1) {
			return null;
		}
		String[][] excelData = null;
		try {
			excelData = new String[rows][cols];
			for (int i = 0; i < rows; i++) {
				Row rows_head = sheet.getRow(i);// get a rows
				for (int j = 0; j < cols; j++) {
					Cell cell_value = rows_head.getCell(j); // get a cell
					if (cell_value != null) {// 不为null的单元格才处理
						boolean megred[] = isMergedRegion(sheet, cell_value);
						if (megred != null && megred.length == 2) {
							if (megred[0] == true && megred[1] == false) {
								excelData[i][j] = DEFINE_MERGED;
							} else {
								excelData[i][j] = getCellValue(cell_value); //获得单元格的值
							}
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return excelData;
	}

	/**
	 * 获得单元格里面的值，包括单元格中的公式计算等.
	 *
	 * @param cell the cell
	 * @return String
	 */
	public String getCellValue(Cell cell) {
		String cellValue = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			cellValue = DEFINE_TAG;
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			cellValue = String.valueOf(cell.getErrorCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			cellValue = String.valueOf(cell.getNumericCellValue()); 
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				cellValue = String.valueOf(cell.getDateCellValue());
			} else {
				cellValue = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING:
			cellValue = String.valueOf(cell.getRichStringCellValue());
			break;
		default:
			cellValue = "ERROR";
			break;
		}
		return cellValue;
	}

	/**
	 * 对单元格中一种特殊情况进行错误标记<html中表格不支持此种显示方法>.
	 *
	 * @param excelData the excel data
	 * @return String[][]
	 */
	public String[][] signErrorValue(String[][] excelData) {
		String[][] temp_Data = excelData;
		for (int i = 0; i < temp_Data.length; i++) {
			String temp_Value = temp_Data[i][0];
			if (temp_Value.equalsIgnoreCase(DEFINE_MERGED)) {
				int j = 1;
				for (; j < temp_Data[i].length; j++) {
					if (!temp_Data[i][j].equalsIgnoreCase(DEFINE_MERGED)) {
						break;
					}
				}
				if (j == temp_Data[i].length) {
					for (int k = 0; k < temp_Data[i].length; k++) {
						temp_Data[i][k] = DEFINE_ERROR;
					}
				}
			}
		}
		return temp_Data;
	}

	/**
	 * 这里是处理人工处理中的一些错误，例如，表中有注释等.
	 *
	 * @param excelData the excel data
	 * @return String[][]
	 */
	public String[][] signHumanErrorValue(String[][] excelData) {
		String[][] temp_Data = excelData;
		int rIndex = temp_Data.length;// 一共的行数
		/* 标记最后一行中有注释无法处理的问题 */
		for (int i = 0; i < temp_Data[rIndex - 1].length; i++) {
			if (temp_Data[rIndex - 1][i] == null) {
				for (int j = 0; j < temp_Data[rIndex - 1].length; j++) {
					temp_Data[rIndex - 1][j] = DEFINE_ERROR;
				}
				break;
			}
		}
		return temp_Data;
	}

	/**
	 * 发现excel常规错误，比如空单元格等.
	 *
	 * @param sheet the sheet
	 * @return String
	 */
	public String getAllErrors(Sheet sheet) {
		return null;
	}

	/**
	 * 将sheet中的数据部分写入数据库.
	 *
	 * @param excelData the excel data
	 * @param topHead the top head
	 * @param leftHead the left head
	 * @param tid the tid
	 * @param topRows the top rows
	 * @param leftCols the left cols
	 */
	public void insertData(String[][] excelData, Object[] topHead,
			Object[] leftHead, Object tid, int topRows, int leftCols) {
		int objectLength = excelData.length;
		int vertialLength = excelData[0].length;
		try {
			for (int i = topRows + 1; i < objectLength; i++) {
				for (int j = leftCols; j < vertialLength; j++) {
					String cellValue = excelData[i][j].equals("SYS_DEFINE") ? ""
							: excelData[i][j];
					if (leftHead[i] == null)
						dataService.add(tid, topHead[j], 0, cellValue);
					else
						dataService.add(tid, topHead[j], leftHead[i],
								cellValue);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 将sheet中标题写入数据库.
	 *
	 * @param sheetTitle the sheet title
	 * @param toprow the toprow
	 * @param leftcol the leftcol
	 * @return Object
	 */
	public Object insertSheetTitle(String sheetTitle,int toprow,int leftcol) {
		Object temp_id = tableService.add(sheetTitle,toprow,leftcol);
		return temp_id;
	}

	/**
	 * 将sheet中的表中的上边标志字段写入数据库.
	 *
	 * @param sheet the sheet
	 * @param excelData the excel data
	 * @param topRows the top rows
	 * @param leftCols the left cols
	 * @param tid the tid
	 * @return Object[]
	 */
	public Object[] insertSheetTopHead(Sheet sheet, String[][] excelData,
			int topRows, int leftCols, Object tid) {
		int topChoose;
		int sequence;
		Object[] topPid = new Object[excelData[0].length];// top
		for (int i = 1; i < topRows + 1; i++) {
			topChoose = 0;
			sequence = 0;
			for (int j = 0; j < excelData[0].length; j++) {
				if (!excelData[i][j].equalsIgnoreCase(DEFINE_ERROR)) {
					int cols = calCols(sheet, excelData, i, j, topRows,
							leftCols);
					int rows = calRows(sheet, excelData, i, j, topRows,
							leftCols);
					if (!excelData[i][j].equalsIgnoreCase(DEFINE_MERGED)) {
						Object topParentid;
						topChoose++;
						if (i == 1) {
							topParentid = 0;
							sequence++;
						} else {
							topParentid = topPid[j];
							if (j < excelData[0].length - 1)
								if (topPid[j + 1].equals(topPid[j])) {
									sequence++;
								} else {
									sequence = 1;
								}
						}
						Object pid = fieldService.add(topParentid,
								excelData[i][j], tid, "h", sequence, cols,
								rows, topChoose);
						for (int k = j; k < j + cols; k++) {
							topPid[k] = pid;
						}
					} 
				}
			}
		}
		return topPid;
	}

	/**
	 * 将sheet中的表中的及左边标志字段写入数据库.
	 *
	 * @param sheet the sheet
	 * @param excelData the excel data
	 * @param topRows the top rows
	 * @param leftCols the left cols
	 * @param tid the tid
	 * @return Object[]
	 */
	public Object[] insertSheetLeftHead(Sheet sheet, String[][] excelData,
			int topRows, int leftCols, Object tid) {
		int leftChoose = 0;
		int sequence = 1;
		int sequence1 = 1;
		int sequence2 = 0;
		int objectLength = excelData.length;
		Object[] leftPid = new Object[objectLength];// left
		for (int i = topRows + 1; i < objectLength; i++) {
			for (int j = 0; j < leftCols; j++) {
				if (!excelData[i][j].equalsIgnoreCase(DEFINE_ERROR)) {
					int cols = calCols(sheet, excelData, i, j, topRows,
							leftCols);
					int rows = calRows(sheet, excelData, i, j, topRows,
							leftCols);
					if (!excelData[i][j].equalsIgnoreCase(DEFINE_MERGED)) {
						Object leftParentId;
						leftChoose++;
						if (j == 0) {
							leftParentId = 0;
						} else {
							leftParentId = leftPid[i];
						}
						if (j == 0) {
							sequence = sequence1;
							sequence1++;
						} else {
							sequence = sequence2;
							if (i < objectLength - 1) {
								if (leftPid[i + 1] == leftPid[i]) {
									sequence2++;
								} else {
									sequence2 = 1;
								}
							}
						}
						Object pid = fieldService.add(leftParentId,
								excelData[i][j], tid, "v", sequence, cols,
								rows, leftChoose);
						for (int k = 0; k < rows; k++) {
							leftPid[i + k] = pid;
						}

					}
				}
			}
		}
		return leftPid;
	}

	/**
	 * 计算单元格跨列数.
	 *
	 * @param sheet the sheet
	 * @param excelData the excel data
	 * @param cIndex the c index
	 * @param rIndex the r index
	 * @param topRows the top rows
	 * @param leftCols the left cols
	 * @return int
	 */
	public int calCols(Sheet sheet, String[][] excelData, int cIndex,
			int rIndex, int topRows, int leftCols) {
		int cols = 1;
		int[] postion = getMergedPosition(sheet, rIndex, cIndex);
		// 这个地方为什么是rIndex和cIndex交换呢
		if (postion != null) {
			for (int i = rIndex + 1; i < excelData[topRows / 2].length; i++) {
				if (cIndex > topRows && rIndex < leftCols && i >= leftCols) {
					break;
				}
				if (excelData[cIndex][i].equalsIgnoreCase(DEFINE_MERGED)) {
					int[] next_postion = getMergedPosition(sheet, i, cIndex);
					if (next_postion != null && next_postion[0] == postion[0]
							&& next_postion[1] == postion[1]) {
						cols++;
					} else {
						break;
					}
				} else {
					break;
				}
			}
		}
		return cols;
	}

	/**
	 * 计算单元格跨行数.
	 *
	 * @param sheet the sheet
	 * @param excelData the excel data
	 * @param cIndex the c index
	 * @param rIndex the r index
	 * @param topRows the top rows
	 * @param leftCols the left cols
	 * @return int
	 */
	public int calRows(Sheet sheet, String[][] excelData, int cIndex,
			int rIndex, int topRows, int leftCols) {
		int rows = 1;
		int[] postion = getMergedPosition(sheet, rIndex, cIndex);
		if (postion != null) {
			for (int i = cIndex + 1; i < excelData.length; i++) {
				if (cIndex <= topRows && i > topRows) {
					break;
				}
				if (excelData[i][rIndex].equalsIgnoreCase(DEFINE_MERGED)) {
					int[] next_postion = getMergedPosition(sheet, rIndex, i);
					if (next_postion != null && next_postion[0] == postion[0]
							&& next_postion[1] == postion[1]) {
						rows++;
					} else {
						break;
					}

				} else {
					break;
				}
			}
		}
		return rows;
	}

	/**
	 * 将单元格数据写入数据库，共外部调用.
	 *
	 * @param path the path
	 * @param toprow the toprow
	 * @param leftcol the leftcol
	 * @param sequence the sequence
	 * @return String
	 */
	public String addData(String path, int toprow, int leftcol, int sequence) {
		StringBuffer msgBuffer = new StringBuffer("执行写入数据库；<br />");
		Sheet sheet = createSheet(path, sequence);
		String[][] temp = getAllData(sheet);
		temp = signHumanErrorValue(temp);// 标记人为的错误
		temp = signErrorValue(temp);// 标记两种软件之间系统级别的错误，非人为
		Object table_id = insertSheetTitle(temp[0][0],toprow,leftcol);
		if (table_id != null && !table_id.toString().equals("-1")) {
			try {
				Object[] tmpTop = insertSheetTopHead(sheet, temp, toprow,
						leftcol, table_id);
				Object[] tmpLeft = insertSheetLeftHead(sheet, temp, toprow,
						leftcol, table_id);
				insertData(temp, tmpTop, tmpLeft, table_id, toprow, leftcol);
			} catch (Exception ex) {
				ex.printStackTrace();
				/* 回滚，由于某处的失败导致要删除所有刚写入的记录 */
				cate_tabService.remove(table_id);
				dataService.remove(table_id);
				fieldService.remove(table_id);
				tableService.remove(table_id);
			}
		} else {
			msgBuffer.append("写入Table 失败，其余表未写入！<br />");
		}
		return table_id + "@" + msgBuffer.toString();
	}

	/**
	 * 判断单元格是否是合并单元格，如果是再判断是否是该合并单元格中第一个单元格.
	 *
	 * @param sheet the sheet
	 * @param cell the cell
	 * @return boolean[]
	 */
	public boolean[] isMergedRegion(Sheet sheet, Cell cell) {
		// 得到一个sheet中有多少个合并单元格
		int columnIndex = cell.getColumnIndex();
		int rowIndex = cell.getRowIndex();
		int[] position = getMergedPosition(sheet, columnIndex, rowIndex);
		if (position != null && position.length == 2) {
			return new boolean[] { true,
					columnIndex == position[0] && rowIndex == position[1] };
		}
		return new boolean[] { false, false };
	}

	/**
	 * 返回该位置合并单元格中的第一个单元格坐标.
	 *
	 * @param sheet the sheet
	 * @param cIndex the c index
	 * @param rIndex the r index
	 * @return int[]
	 */
	public int[] getMergedPosition(Sheet sheet, int cIndex, int rIndex) {
		int[] position = null;
		int sheetmergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetmergerCount; i++) {
			// 得出具体的合并单元格
			CellRangeAddress ca = sheet.getMergedRegion(i);
			// 得到合并单元格的起始行, 结束行, 起始列, 结束列
			int firstC = ca.getFirstColumn();
			int lastC = ca.getLastColumn();
			int firstR = ca.getFirstRow();
			int lastR = ca.getLastRow();
			if (cIndex <= lastC && cIndex >= firstC) {
				if (rIndex <= lastR && rIndex >= firstR) {
					position = new int[] { firstC, firstR };
					break;
				}
			}
		}
		return position;
	}
}