package net.w_horse.excelpojo.excel;

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.bean.Utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.springframework.util.ReflectionUtils;

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

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


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

		Object value;
		switch(cell.getCellType()){
		case HSSFCell.CELL_TYPE_BLANK:
			value = "";
			break;
		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			// 日付・整数・少数の判別を行う
            if(HSSFDateUtil.isCellDateFormatted(cell)
            	|| CellDateFormat.contains(cell.getCellStyle().getDataFormat())
            ){			//日付
             	value = getDateType(cell, requiredType);
			} else {	// 数値
				if (requiredType.equals(String.class)) {
					HSSFDataFormatter formatter = new HSSFDataFormatter();
			        String strValue = formatter.formatCellValue(cell);
			        if (strValue.endsWith("_ ")) {
			        	strValue = strValue.substring(0, strValue.length() -2);
			        }
			        return (T) strValue.trim();	// String の場合はそのまま返す
				}
	            value = getNumericType(cell);
			}
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			value = getFormulaType(cell, requiredType);
			break;
		default:
			value = "";
		}
    	return (T) Utils.convertIfNecessary(value, requiredType);
	}
	@SuppressWarnings("unchecked")
	private static <T>T getFormulaType(HSSFCell cell, Class<T> requiredType) {
		try {
			HSSFRichTextString stringValue = cell.getRichStringCellValue();
			return (T) stringValue.getString();
		} catch (Exception e) {
			if(HSSFDateUtil.isCellDateFormatted(cell)
            	|| CellDateFormat.contains(cell.getCellStyle().getDataFormat())
            ){	//日付
             	return getDateType(cell, requiredType);
			} else {
				if (requiredType.equals(String.class)) {
			        return (T) getNumericTypeString(cell);
				}
				return (T) getNumericType(cell);
			}
		}
	}
	private static Object getFormulaType(HSSFCell cell) {
		try {
			HSSFRichTextString stringValue = cell.getRichStringCellValue();
			return stringValue.getString();
		} catch (Exception e) {
			if(HSSFDateUtil.isCellDateFormatted(cell)
            	|| CellDateFormat.contains(cell.getCellStyle().getDataFormat())
            ){	//日付
             	return getDateType(cell);
			} else {
				return getNumericType(cell);
			}
		}
	}
	@SuppressWarnings("unchecked")
	private static <T>T getDateType(HSSFCell cell, Class<T> requiredType) {
		if (requiredType.equals(String.class)) {
			return (T) getDateTypeString(cell);
		}
		return (T) getDateType(cell);
	}
	private static Date getDateType(HSSFCell cell) {
    	return cell.getDateCellValue();
	}
	private static String getDateTypeString(HSSFCell cell) {
		HSSFCellStyle cellStyle = cell.getCellStyle();
		Date theDate = getDateType(cell);
		DateFormat dateFormat = CellDateFormat.getFormt(cellStyle.getDataFormat()).getDateFormat();
    	return dateFormat.format(theDate);
	}
	private static String getNumericTypeString(HSSFCell cell) {
        return String.valueOf(getNumericType(cell));
	}
	private static Object getNumericType(HSSFCell 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(HSSFCell cell, Object value) {
		if (cell == null) return;
		if (value == null) {
			value = "";
		}

		Class<?> parameterType = value.getClass();
		if (parameterType.equals(Integer.TYPE)
			|| parameterType.equals(Double.TYPE)
			|| parameterType.equals(Short.TYPE)
			|| parameterType.equals(Long.TYPE)
			|| parameterType.equals(Float.TYPE)
		){
			parameterType = double.class;
		}
		try {
			ReflectionUtils.invokeMethod(cell.getClass().getDeclaredMethod("setCellValue", parameterType),
					cell,
					new Object[]{value});
		} catch (NoSuchMethodException e) {
			cell.setCellValue(String.valueOf(value));
		}
	}


}
