package net.w_horse.excelpojo.excel;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.MathContext;
import java.text.DateFormat;
import java.util.Date;

import net.w_horse.excelpojo.ExcelPOJOException;
import net.w_horse.excelpojo.bean.Utils;
import net.w_horse.excelpojo.converter.CellFieldConverter;
import net.w_horse.excelpojo.converter.NothingConverter;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.springframework.util.ReflectionUtils;

public class ExcelUtils {
	public static Object getCellValue(Cell cell) {
		if(cell == null){
			return null;
		}

		switch(cell.getCellType()){
		case Cell.CELL_TYPE_BLANK:
			return "";
		case Cell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString();
		case Cell.CELL_TYPE_NUMERIC:
			// 日付・整数・少数の判別を行う
            if(DateUtil.isCellDateFormatted(cell)
            	|| CellDateFormat.contains(cell.getCellStyle().getDataFormat())
            ){			//日付
             	return cell.getDateCellValue();
			} else {	// 数値
	            return getNumericType(cell);
			}
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return getFormulaType(cell);
		default:
			return "";
		}
	}

	public static <T>T getCellValue(Cell cell, Class<T> requiredType) throws ExcelPOJOException {
		return getCellValue(cell, requiredType, new NothingConverter());
	}

	@SuppressWarnings("unchecked")
	public static <T>T getCellValue(Cell cell, Class<T> requiredType, CellFieldConverter converter) throws ExcelPOJOException {
		if(cell == null){
			return (T) Utils.convertIfNecessary(null, requiredType);
		}

		Object value;
		switch(cell.getCellType()){
		case Cell.CELL_TYPE_BLANK:
			value = converter.convertCell2Field("");
			break;
		case Cell.CELL_TYPE_STRING:
			value = converter.convertCell2Field(cell.getRichStringCellValue().getString());
			break;
		case Cell.CELL_TYPE_NUMERIC:
			// 日付・整数・少数の判別を行う
            if (DateUtil.isCellDateFormatted(cell)
            	|| CellDateFormat.contains(cell.getCellStyle().getDataFormat())
            ) {			//日付
             	value = getDateType(cell, requiredType, converter);
			} else {	// 数値
				if (requiredType.equals(String.class)) {
					DataFormatter formatter = new DataFormatter();
			        String strValue = formatter.formatCellValue(cell);
			        if (strValue.endsWith("_ ")) {
			        	strValue = strValue.substring(0, strValue.length() -2);
			        }
			        return (T) strValue.trim();	// String の場合はそのまま返す
				}
	            value = converter.convertCell2Field(getNumericType(cell));
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = converter.convertCell2Field(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = getFormulaType(cell, requiredType, converter);
			break;
		default:
			value = converter.convertCell2Field("");
		}
		return (T) Utils.convertIfNecessary(value, requiredType);
	}

	private static <T>T getFormulaType(Cell cell, Class<T> requiredType, CellFieldConverter converter) throws ExcelPOJOException {
		FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
		return getCellValue(evaluator.evaluateInCell(cell), requiredType, converter);
	}
	private static Object getFormulaType(Cell cell) {
		FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
		return getCellValue(evaluator.evaluateInCell(cell));
	}
	@SuppressWarnings("unchecked")
	private static <T>T getDateType(Cell cell, Class<T> requiredType, CellFieldConverter converter) throws ExcelPOJOException {
		Object value = converter.convertCell2Field(cell.getDateCellValue());
		if ((converter instanceof NothingConverter)
			&& requiredType.equals(String.class)
		) {
			return (T) getDateTypeString(cell);
		}
		return (T) value;
	}
	private static String getDateTypeString(Cell cell) {
		CellStyle cellStyle = cell.getCellStyle();

		Date theDate = cell.getDateCellValue();
		DateFormat dateFormat = CellDateFormat.getFormt(cellStyle.getDataFormat()).getDateFormat();
    	return dateFormat.format(theDate);
	}
	private static Object getNumericType(Cell cell) {
        BigDecimal bigDecimalval = new BigDecimal(cell.getNumericCellValue(), new MathContext(17));
        try {
        	BigInteger bigIntVal = bigDecimalval.toBigIntegerExact();
        	if (bigIntVal.compareTo(BigInteger.valueOf(bigIntVal.intValue())) == 0) {
        		return bigIntVal.intValue();
        	} else {
        		return bigIntVal;
        	}
        } catch (ArithmeticException e) {
        	return cell.getNumericCellValue();
        }
	}

	public static void setCellValue(Cell cell, Object value, CellFieldConverter converter) {
		if (cell == null) return;
		if (value == null) {
			value = "";
		}

		try {
			Object parameter = converter.convertField2Cell(value);
			Class<?> parameterType = parameter.getClass();
			if (parameterType.equals(int.class)
				|| parameterType.equals(Integer.class)
				|| parameterType.equals(double.class)
				|| parameterType.equals(Double.class)
				|| parameterType.equals(short.class)
				|| parameterType.equals(Short.class)
				|| parameterType.equals(long.class)
				|| parameterType.equals(Long.class)
				|| parameterType.equals(float.class)
				|| parameterType.equals(Float.class)
			){
				parameterType = double.class;
			} else if (parameterType.equals(Boolean.class)) {
				parameterType = boolean.class;
			}
			Method method = cell.getClass().getDeclaredMethod("setCellValue", parameterType);
			ReflectionUtils.invokeMethod(method,
					cell,
					new Object[]{parameter});
		} catch (Exception e) {
			cell.setCellValue(String.valueOf(value));
		}
	}


}
