package cn.timer.api.utils;

import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.web.multipart.MultipartFile;


/**
 *
 * @author Tang
 * @date 2019年7月26日
 *
 */
public class ImportExcelUntil {

	/**
	 * 拼装单个obj
	 * 
	 * @param obj
	 * @param row
	 * @return
	 * @throws Exception
	 */
	private static Map<String, Object> dataObj(Object obj, HSSFRow row)
			throws Exception {
		Class<?> rowClazz = obj.getClass();
		Field[] fields = FieldUtils.getAllFields(rowClazz);
		if (fields == null || fields.length < 1) {
			return null;
		}

		// 容器
		Map<String, Object> map = new HashMap<String, Object>();

		// 注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
		for (int j = 0; j < fields.length; j++) {
			map.put(fields[j].getName(), getVal(row.getCell(j)));
		}
		return map;
	}

	@SuppressWarnings("deprecation")
	public static List<Map<String, Object>> importExcel(MultipartFile file,Object obj) throws Exception {

		// 装载流
		POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
		HSSFWorkbook hw = new HSSFWorkbook(fs);
		// 获取第一个sheet页
		HSSFSheet sheet = hw.getSheetAt(0);

		// 容器
		List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();

		// 遍历行 从下标第一行开始（去除标题）
		for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
			HSSFRow row = sheet.getRow(i);
			String result = new String(); 
			if (row != null) {
				// 装载obj
				for (int k = 0; k < row.getLastCellNum(); k++) {     
					HSSFCell cell = row.getCell(k);
					 if (null != cell) {     
                         switch (cell.getCellType()) {     
                         case HSSFCell.CELL_TYPE_NUMERIC: // 数字   
//                             System.out.print(cell.getNumericCellValue()     
//                                     + "   ");     
//                             System.out.print( new DecimalFormat("#").format(cellValue));   
                        	 DecimalFormat df=new DecimalFormat("0"); 
                        	 String cellValue = df.format(cell.getNumericCellValue());;   
                        	 if(cellValue.lastIndexOf("E")>-1){
                        		 cell.setCellValue(new DecimalFormat("#").format(cell.getNumericCellValue()));
                        	 }
                        	 
                        	 String str  = String.valueOf(cell);
                        	 if (str.indexOf(".0") != -1 || str.indexOf("-") != -1) {
                        	 
                        		 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                     				SimpleDateFormat sdf = null;
                     				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                     					sdf = new SimpleDateFormat("HH:mm");
                     				} else {// 日期
                     					sdf = new SimpleDateFormat("yyyyMMdd");
                     				}
                     				Date date = cell.getDateCellValue();
                     				result = sdf.format(date);
                     				cell.setCellValue(result);
                     			} else if (cell.getCellStyle().getDataFormat() == 176) {
                     				// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                     				SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                     				double value = cell.getNumericCellValue();
                     				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                     				result = sdf.format(date);
                     				
                     				cell.setCellValue(result);
                     			} else if(cell.getCellStyle().getDataFormat() == 14 || cell.getCellStyle().getDataFormat() == 31){
                     				
                     				SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                     				double value = cell.getNumericCellValue();
                     				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                     				result = sdf.format(date);
                     				
                     				cell.setCellValue(result);
                     			}
                        		 
                        		 System.out.println(result);
                        	 }
                        	 
                             break;     
                         case HSSFCell.CELL_TYPE_STRING: // 字符串     
//                             System.out.print(cell.getStringCellValue()     
//                                     + "   ");     
                             break;     
                         case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean     
//                             System.out.println(cell.getBooleanCellValue()     
//                                     + "   ");     
                             break;     
                         case HSSFCell.CELL_TYPE_FORMULA: // 公式     
//                             System.out.print(cell.getCellFormula() + "   ");     
                             break;     
                         case HSSFCell.CELL_TYPE_BLANK: // 空值     
//                             System.out.println(" ");     
                             break;     
                         case HSSFCell.CELL_TYPE_ERROR: // 故障     
//                             System.out.println(" ");     
                             break;     
                         default:     
//                             System.out.print("未知类型   ");     
                             break;     
                         } 
					 }
				}
				ret.add(dataObj(obj, row));
			}
		}
		hw.close();
		return ret;
	}

	/**
	 * 处理val（暂时只处理string和number，可以自己添加自己需要的val类型）
	 * 
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static String getVal(HSSFCell hssfCell) {
		if(hssfCell!=null){
			  hssfCell.setCellType(Cell.CELL_TYPE_STRING);
			  return hssfCell.getStringCellValue();
	     }else {
				return "";
		 }
	}
}