package com.yem.ensign.common;

import com.yem.ensign.common.MyFileFilter;
import kd.bos.exception.KDBizException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.awt.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.List;
import java.util.*;

/**
 * Excel导入工具类
 */
public class ExcelHepler {


	/**
	 * 导入execl得到内容
	 * @param colNameList 字段名称
	 * @return Excel数据集合
	 */
	public static List importExcel(List colNameList){

		JFileChooser parseDir = new JFileChooser();
		parseDir.setCurrentDirectory(new File("."));
		parseDir.setAcceptAllFileFilterUsed(false);
		parseDir.addChoosableFileFilter(new MyFileFilter(new String[]{"xls","xlsx"}, "Excel Files"));
		int returnValue = parseDir.showOpenDialog(null);
		if(returnValue!=0||parseDir.getSelectedFile()==null){
			return null;
		}

		String path=  parseDir.getSelectedFile().getPath();
		List dataList = new ArrayList();
		try {
			InputStream is = new FileInputStream(path);
			String type = path.substring(path.indexOf(".")+1,path.length());
			if("xls".equals(type)){
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
				if (hssfSheet == null) {
					return null;
				}
				if(!checkNameIsEquals(hssfSheet,colNameList)){
					throw new KDBizException("导入Excel信息和分录信息不一致是否继续！");
				}
				List excelColNameList = getColNameListBySheet(hssfSheet);
				Map rowMap;
				HSSFRow hssfRow;
				HSSFCell xh;
				// 循环行Row
				for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					hssfRow = hssfSheet.getRow(rowNum);
					rowMap = new HashMap();
					if (hssfRow == null) {
						break;
					}
					boolean isAllCellNull = true;
					for (int cellNum = 0; cellNum <excelColNameList.size(); cellNum++) {
						xh = hssfRow.getCell(cellNum);
						if (xh == null) {
							rowMap.put(excelColNameList.get(cellNum), null);
						}else{
							isAllCellNull = false;
							rowMap.put(excelColNameList.get(cellNum), getValue(xh));
						}
					}
					if(isAllCellNull){
						break;
					}
					dataList.add(rowMap);
				}
			}else if("xlsx".equals(type)){
				XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
				if (xssfSheet == null) {
					return null;
				}
				if(!checkNameIsEquals(xssfSheet,colNameList)){
					throw new KDBizException("导入Excel信息和分录信息不一致是否继续！");
				}
				List excelColNameList = getColNameListBySheet(xssfSheet);
				Map rowMap;
				XSSFRow hssfRow;
				XSSFCell xh;
				// 循环行Row
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					hssfRow = xssfSheet.getRow(rowNum);
					rowMap = new HashMap();
					if (hssfRow == null) {
						break;
					}
					boolean isAllCellNull = true;
					for (int cellNum = 0; cellNum <excelColNameList.size(); cellNum++) {
						xh = hssfRow.getCell(cellNum);
						if (xh == null) {
							rowMap.put(excelColNameList.get(cellNum), null);
						}else{
							isAllCellNull = false;
							rowMap.put(excelColNameList.get(cellNum), getValue(xh));
						}
					}
					if(isAllCellNull){
						break;
					}
					dataList.add(rowMap);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
			throw new KDBizException("导入Excel出错："+e.getMessage());
		}
		return dataList;
	}

	/**
	 * 得到当前页签的第一行信息（也就是导入时候对应的表头信息）
	 * @param sheet 页签
	 * @return
	 */
	private static List getColNameListBySheet(HSSFSheet sheet) {
		List colNameList = new ArrayList();
		if (sheet == null) {
			return colNameList;
		}
		Set colSet = new HashSet();
		HSSFRow hssfRow = sheet.getRow(0);
		String colName = null;
		for (int cellNum = 0; cellNum <255; cellNum++) {
			HSSFCell xh = hssfRow.getCell(cellNum);
			if(xh==null){
				break;
			}
			colNameList.add(getValue(xh));
		}
		return colNameList;
	}

	/**
	 * 得到当前页签的第一行信息（也就是导入时候对应的表头信息）
	 * @param sheet 页签
	 * @return
	 */
	private static List getColNameListBySheet(XSSFSheet sheet) {
		List colNameList = new ArrayList();
		if (sheet == null) {
			return colNameList;
		}
		Set colSet = new HashSet();
		XSSFRow hssfRow = sheet.getRow(0);
		String colName = null;
		for (int cellNum = 0; cellNum <255; cellNum++) {
			XSSFCell xh = hssfRow.getCell(cellNum);
			if(xh==null){
				break;
			}
			colNameList.add(getValue(xh));
		}
		return colNameList;
	}


	/**
	 * 判断是excel第一行字段和表格表头信息是否一致
	 * @param sheet 页签
	 * @param colNameList 字段名称
	 * @return
	 */
	private static boolean checkNameIsEquals(HSSFSheet sheet, List colNameList) {
		if (sheet == null||colNameList==null||colNameList.size()<=0) {
			return false;
		}
		Set colSet = new HashSet();
		HSSFRow hssfRow = sheet.getRow(0);
		String colName = null;
		for (int cellNum = 0; cellNum <colNameList.size(); cellNum++) {
			HSSFCell xh = hssfRow.getCell(cellNum);
			if (xh == null) {
				return false;
			}
			colName = getValue(xh);
			if(!colNameList.contains(colName)){
				return false;
			}
			colSet.add(colName);
		}
		if(colSet.size()!=colNameList.size()){
			return false;
		}
		return true;
	}

	/**
	 * 判断是excel第一行字段和表格表头信息是否一致
	 * @param sheet 页签
	 * @param colNameList 字段名称
	 * @return
	 */
	private static boolean checkNameIsEquals(XSSFSheet sheet, List colNameList) {
		if (sheet == null||colNameList==null||colNameList.size()<=0) {
			return false;
		}
		Set colSet = new HashSet();
		XSSFRow xssfRow = sheet.getRow(0);
		String colName = null;
		for (int cellNum = 0; cellNum <colNameList.size(); cellNum++) {
			XSSFCell xh = xssfRow.getCell(cellNum);
			if (xh == null) {
				return false;
			}
			colName = getValue(xh);
			if(!colNameList.contains(colName)){
				return false;
			}
			colSet.add(colName);
		}
		if(colSet.size()!=colNameList.size()){
			return false;
		}
		return true;
	}


	/**
	 * 得到Excel表中的值
	 * @param hssfCell Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == CellType.BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == CellType.NUMERIC) {
			// 返回数值类型的值
			BigDecimal value =  new BigDecimal(hssfCell.getNumericCellValue());
			return value.toPlainString();
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	/**
	 * 得到Excel表中的值
	 * @param hssfCell Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	private static String getValue(XSSFCell hssfCell) {
		if (hssfCell.getCellType() == CellType.BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == CellType.NUMERIC) {
			// 返回数值类型的值
			BigDecimal value =  new BigDecimal(hssfCell.getNumericCellValue());
			return value.toPlainString();
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}
