package com.huatek.busi.common.util;

import com.google.common.collect.Lists;
import com.huatek.busi.common.annotation.ExcelAnnotation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 */
@Slf4j
public class ImportExcel {
	
	/**
	 * 工作薄对象
	 */
	private Workbook wb;
	
	/**
	 * 工作表对象
	 */
	private Sheet sheet;
	
	/**
	 * 标题行号
	 */
	private int headerNum;
	
	/**
	 * 构造函数
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum);
	}
	
	/**
	 * 构造函数
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum) 
			throws InvalidFormatException, IOException {
		this(file, headerNum, 0);
	}

	/**
	 * 构造函数
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数（数据导入）
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		if (StringUtils.isBlank(fileName)){
			throw new RuntimeException("导入文档为空!");
		}else if(fileName.toLowerCase().endsWith("xls")){    
			this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
        	this.wb = new XSSFWorkbook(is);
        }else{  
        	throw new RuntimeException("文档格式不正确!");
        }  
		if (this.wb.getNumberOfSheets()<sheetIndex){
			throw new RuntimeException("文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		log.debug("Initialize success.");
	}
	
	/**
	 * 获取行对象
	 * @param rownum
	 * @return
	 */
	public Row getRow(int rownum){
		return this.sheet.getRow(rownum);
	}

	/**
	 * 获取数据行号
	 * @return
	 */
	public int getDataRowNum(){
		return headerNum+1;
	}
	
	/**
	 * 获取最后一个数据行号
	 * @return
	 */
	public int getLastDataRowNum(){
		return this.sheet.getLastRowNum()+headerNum;
	}
	
	/**
	 * 获取最后一个列号
	 * @return
	 */
	public int getLastCellNum(){
		return this.getRow(headerNum).getLastCellNum();
	}
	
	/**
	 * 获取单元格值
	 * @param row 获取的行
	 * @param column 获取单元格列号
	 * @return 单元格值
	 */
	public Object getCellValue(Row row, int column){
		Object val = "";
		try{
			Cell cell = row.getCell(column);
			if (cell != null){
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					val = cell.getNumericCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
					val = cell.getStringCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
					val = cell.getCellFormula();
				}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					val = cell.getBooleanCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
					val = cell.getErrorCellValue();
				}
			}
		}catch (Exception e) {
			return val;
		}
		return val;
	}
	
	/**
	 * 获取导入数据列表
	 * @param cls 导入对象类型
	 * @param groups 导入分组
	 */
	public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
		List<Object[]> annotationList = Lists.newArrayList();
		// Get annotation field 
		Field[] fs = cls.getDeclaredFields();
		for (Field f : fs){
			ExcelAnnotation ef = f.getAnnotation(ExcelAnnotation.class);
			if (ef != null && (ef.type()==0 || ef.type()==2)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, f});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, f});
				}
			}
		}
		// Get annotation method
		Method[] ms = cls.getDeclaredMethods();
		for (Method m : ms){
			ExcelAnnotation ef = m.getAnnotation(ExcelAnnotation.class);
			if (ef != null && (ef.type()==0 || ef.type()==2)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, m});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, m});
				}
			}
		}
		// Field sorting
		Collections.sort(annotationList, Comparator.comparing(o -> new Integer(((ExcelAnnotation) o[0]).sort())));
		//log.debug("Import column count:"+annotationList.size());
		// Get excel data
		List<E> dataList = Lists.newArrayList();
		for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
			E e = (E)cls.newInstance();
			int column = 0;
			Row row = this.getRow(i);
			StringBuilder sb = new StringBuilder();
			for (Object[] os : annotationList){
				Object val = this.getCellValue(row, column++);
				if (val != null){
					ExcelAnnotation ef = (ExcelAnnotation)os[0];
					// If is dict type, get dict value
//					if (StringUtils.isNotBlank(ef.dictType())){
//						val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
//						//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
//					}
					// Get param type and type cast
					Class<?> valType = Class.class;
					if (os[1] instanceof Field){
						valType = ((Field)os[1]).getType();
					}else if (os[1] instanceof Method){
						Method method = ((Method)os[1]);
						if ("get".equals(method.getName().substring(0, 3))){
							valType = method.getReturnType();
						}else if("set".equals(method.getName().substring(0, 3))){
							valType = ((Method)os[1]).getParameterTypes()[0];
						}
					}
					//log.debug("Import value type: ["+i+","+column+"] " + valType);
					try {
						if (valType == String.class){
							String s = String.valueOf(val.toString());
							if(StringUtils.endsWith(s, ".0")){
								val = StringUtils.substringBefore(s, ".0");
							}else{
								val = String.valueOf(val.toString());
							}
						}else if (valType == Integer.class){
							val = Double.valueOf(val.toString()).intValue();
						}else if (valType == Long.class){
							val = Double.valueOf(val.toString()).longValue();
						}else if (valType == Double.class){
							val = Double.valueOf(val.toString());
						}else if (valType == Float.class){
							val = Float.valueOf(val.toString());
						}else if (valType == Date.class){
							val = DateUtil.getJavaDate((Double)val);
						}else{
							if (ef.fieldType() != Class.class){
								val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
							}else{
								val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
										"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
							}
						}
					} catch (Exception ex) {
						log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
						val = null;
					}
					// set entity value
					if (os[1] instanceof Field){
						Reflections.invokeSetter(e, (( Field)os[1]).getName(), val);
					}else if (os[1] instanceof Method){
						String mthodName = ((Method)os[1]).getName();
						if ("get".equals(mthodName.substring(0, 3))){
							mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
						}
						Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
					}
				}
				sb.append(val+", ");
			}
			dataList.add(e);
			log.debug("Read success: ["+i+"] "+sb.toString());
		}
		return dataList;
	}

	/**
	 * 获取sheet数量
	 * @param multipartFile
	 * @return
	 */
	public static int getSheetNum(MultipartFile multipartFile){
		try {
			InputStream inputStream = multipartFile.getInputStream();
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			HSSFWorkbook hs = new HSSFWorkbook(fs);
			return hs.getNumberOfSheets();
		}catch (Exception e){
			log.error("获取sheet数量异常:{}", e);
		}
		return 0;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * @param sheet
	 * @param cell
	 * @return
	 */
	public static boolean isMergedRegion(Sheet sheet, Cell cell) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if(cell.getRowIndex() >= firstRow && cell.getRowIndex() <= lastRow){
				if(cell.getColumnIndex() >= firstColumn && cell.getColumnIndex() <= lastColumn){
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 是否空行
	 * @param row
	 * @return
	 */
	public static boolean isRowEmpty(Row row) {
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
				return false;
			}
		}
		return true;
	}

	public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
		List<CellRangeAddress> list = new ArrayList<>();
		//获得一个 sheet 中合并单元格的数量
		int sheetmergerCount = sheet.getNumMergedRegions();
		//遍历所有的合并单元格
		for(int i = 0; i<sheetmergerCount;i++)
		{
			//获得合并单元格保存进list中
			CellRangeAddress ca = sheet.getMergedRegion(i);
			list.add(ca);
		}
		return list;
	}

	public static Map<String, Object> getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
		int xr = 0;
		int firstC = 0;
		int lastC = 0;
		int firstR = 0;
		int lastR = 0;
		Map<String, Object> map = new HashMap<>();
		for(CellRangeAddress ca:listCombineCell) {
			//获得合并单元格的起始行, 结束行, 起始列, 结束列
			firstC = ca.getFirstColumn();
			lastC = ca.getLastColumn();
			firstR = ca.getFirstRow();
			lastR = ca.getLastRow();
			if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
				if(firstR != lastR){
					map.put("firstR", firstR);
					map.put("lastR", lastR);
					break;
				}
//				if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
//					xr = lastR;
//				}
			}
		}
		return map;
	}

	/**
	 * 获取合并单元格的值
	 * @param sheet
	 * @param index 行索引
	 * @param cellIndex 列索引
	 * @return
	 */
	public static String getMergeValue(Sheet sheet, int index, int cellIndex){
		Cell cell = sheet.getRow(index).getCell(cellIndex);
		if(cell != null){
			if (isMergedRegion(sheet, cell)) {
				List<CellRangeAddress> list = getCombineCell(sheet);
				Map<String, Object> mapRow = getRowNum(list, cell, sheet);
				if (mapRow.get("firstR") != null && mapRow.get("lastR") != null) {
					int firstR1 = Integer.valueOf(mapRow.get("firstR").toString());
					int lastR1 = Integer.valueOf(mapRow.get("lastR").toString());

					int firstR, firstC;
					for (CellRangeAddress cellRangeAddress : list) {
						// 第一列
						firstC = cellRangeAddress.getFirstColumn() + cellIndex;
						// 最后一列
						int lastC = cellRangeAddress.getLastColumn() + cellIndex;
						// 第一行
						firstR = cellRangeAddress.getFirstRow();
						// 最后一行
						int lastR = cellRangeAddress.getLastRow();
						if (firstR == firstR1 && firstC == cellIndex) {
							if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
								if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
									// 合并行数
									int xr = lastR - firstR + 1;

									Row fRow = sheet.getRow(firstR);
									Cell fCell = fRow.getCell(firstC);
									if(fCell != null){
										return fCell.toString().trim();
									}
								}
							}
						}
					}
				}
			}
		}
		return null;
	}

	/**
	 * Excel文件写入下拉样式及数据
	 * @param sheet
	 * @param firstRow 首行索引
	 * @param lastRow 末行索引
	 * @param firstCol 首列索引
	 * @param lastCol 末列索引
	 * @param pos 下拉数据
	 */
	public static void createBox(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String[] pos){
		DataValidationHelper helper = sheet.getDataValidationHelper();
		CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidationConstraint constraint = helper.createExplicitListConstraint(pos);
		DataValidation dataValidation = helper.createValidation(constraint, addressList);
		// 处理Excel兼容性问题
		if(dataValidation instanceof XSSFDataValidation) {
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.setShowErrorBox(true);
		}else {
			dataValidation.setSuppressDropDownArrow(false);
		}
		sheet.addValidationData(dataValidation);
	}
}
