package com.zcloud.util;

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.Cell;
import org.apache.poi.ss.usermodel.CellType;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 说明：从EXCEL导入到系统


 */
public class ObjectExcelRead {

	/**
	 * @param filepath //文件路径
	 * @param filename //文件名
	 * @param startrow //开始行号
	 * @param startcol //开始列号
	 * @param sheetnum //sheet
	 * @return list
	 */
	public static List<Map<String,String>> readExcel(File target, int startrow, int startcol, int sheetnum) {
		List<Map<String,String>> varList = new ArrayList<Map<String,String>> ();

		try {
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			HSSFSheet sheet = wb.getSheetAt(sheetnum); 					//sheet 从0开始
//			int rowNum = getExcelRealRow(sheet) ; 	 					//取得最后一行的行号

			for (int i = startrow; i <= sheet.getLastRowNum(); i++) {					//行循环开始
				Map<String,String> varMap = new HashMap<String, String>();
				HSSFRow row = sheet.getRow(i); 							//行
				boolean isEmpty = true;
				if (row == null) {
					continue;
				}
				for (Cell c : row) {
					if (c.getCellType() !=  CellType.BLANK) {
						isEmpty = false;
						break;
					}
				}
				if (isEmpty){
					continue;
				}
				varMap.put("rowNum", String.valueOf(row.getRowNum()+1));
				int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置

				for (int j = startcol; j < cellNum; j++) {				//列循环开始

					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						switch (cell.getCellType().getCode()) { 					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
							case 0:
								BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());
								cellValue = bigDecimal.toPlainString();
//							cellValue = String.valueOf((int) cell.getNumericCellValue());
								break;
							case 1:
								cellValue = cell.getStringCellValue();
								break;
							case 2:
								cellValue = cell.getNumericCellValue() + "";
								// cellValue = String.valueOf(cell.getDateCellValue());
								break;
							case 3:
								cellValue = "";
								break;
							case 4:
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case 5:
								cellValue = String.valueOf(cell.getErrorCellValue());
								break;
						}
					} else {
						cellValue = "";
					}
					varMap.put("var"+j, cellValue);

				}
				varList.add(varMap);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return varList;
	}

	/**
	 * 获取excel表格的真实行数
	 * @param sheet excel文件的工作簿的名称
	 * @return
	 */
	public static int getExcelRealRow(HSSFSheet sheet) {
		boolean flag = false;
		for (int i = 1; i <= sheet.getLastRowNum(); ) {
			HSSFRow r = sheet.getRow(i);
			if (r == null) {
				// 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
				continue;
			}
			flag = false;
			for (Cell c : r) {
				if (c.getCellType() !=  CellType.BLANK) {
					flag = true;
					break;
				}
			}
			if (flag) {
				i++;
				continue;
			} else {
				// 如果是空白行（即可能没有数据，但是有一定格式）
				if (i == sheet.getLastRowNum())// 如果到了最后一行，直接将那一行remove掉
					sheet.removeRow(r);
				else//如果还没到最后一行，则数据往上移一行
					sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
			}
		}
		return sheet.getLastRowNum();
	}
}
