wp/nwyj/.svn/pristine/15/156f69ffeaa5aa2d68318c50a5cf848d13e58994.svn-base

package com.sinosoft.em.evaluate.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sinosoft.em.evaluate.EvalUtils;
import com.sinosoft.lz.system.util.JsonUtil;




public class ImportExcelUtils {
	
	/**
	 * 适用于没有标题行的excel
	 * @throws Exception 
	 */
	public static Map<String, List<String>> readInnerExcel(String filepath,int beginCol) throws Exception{
	    String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
	    InputStream is = null;
	    Workbook wb = null;
	    try {
	        is = new FileInputStream(filepath);
	         
	        if (fileType.equals("xls")) {
	            wb = new HSSFWorkbook(is);
	        } else if (fileType.equals("xlsx")) {
	            wb = new XSSFWorkbook(is);
	        } else {
	            throw new Exception("读取的不是excel文件");
	        }
	         
	        Map<String, List<String>> result = new HashMap<String, List<String>>();
	        
            Sheet sheet = wb.getSheetAt(0);
             
            int rowSize = sheet.getLastRowNum() + 1;
            for (int i = 3; i < rowSize; i++) {//遍历行
            	List<String> sheetList = new ArrayList<String>();//对应sheet页
                Row row = sheet.getRow(i);
                if (row == null) {//略过空行
                    continue;
                }
                int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                for(int j=0;j<cellSize;j++){
                	Cell cell = row.getCell(j);
                	String value = "";
                	if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
                		value = String.valueOf(cell.getBooleanCellValue());
                	}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
            			//value = String.valueOf(cell.getNumericCellValue());
                		Object inputValue = null;// 单元格值
            			Long longVal = Math.round(cell.getNumericCellValue());
            			Double doubleVal = cell.getNumericCellValue();
            			if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
            	              inputValue = longVal;
	        	         }else{
	        	              inputValue = doubleVal;
	        	         }
            			DecimalFormat df = new DecimalFormat("#.####"); 
            			value = String.valueOf(df.format(inputValue));
            		}else{
                		value = String.valueOf(cell.getStringCellValue());
                	}
                	if(j>=beginCol && j<=beginCol+8)
                	sheetList.add(value);
                }
                result.put(sheetList.get(6)+"|"+sheetList.get(7), sheetList);
            }
	         
	        return result;
	    } catch (FileNotFoundException e) {
	        throw e;
	    } finally {
	        if (wb != null) {
	            wb.close();
	        }
	        if (is != null) {
	            is.close();
	        }
	    }
	}
	
	
	/**
	 * 适用于没有标题行的excel--导入外部标准
	 * @throws Exception 
	 */
	public static List<Map<String,String>> readInnerExcelOther(String filepath,int beginCol) throws Exception{
	    String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
	    InputStream is = null;
	    Workbook wb = null;
	    try {
	        is = new FileInputStream(filepath);
	         
	        if (fileType.equals("xls")) {
	            wb = new HSSFWorkbook(is);
	        } else if (fileType.equals("xlsx")) {
	            wb = new XSSFWorkbook(is);
	        } else {
	            throw new Exception("读取的不是excel文件");
	        }
	        List<Map<String,String>> result = new ArrayList<Map<String,String>>();
            Sheet sheet = wb.getSheetAt(0);
             
            int rowSize = sheet.getLastRowNum() + 1;
            for (int i = 2; i < rowSize; i++) {//遍历行
            	//List<String> sheetList = new ArrayList<String>();//对应sheet页
            	Map<String, String> map = new HashMap<String, String>();
                Row row = sheet.getRow(i);
                if (row == null) {//略过空行
                    continue;
                }
                int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                for(int j=0;j<cellSize;j++){
                	Cell cell = row.getCell(j);
                	String value = "";
                	if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
                		value = String.valueOf(cell.getBooleanCellValue());
                	}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
            			//value = String.valueOf(cell.getNumericCellValue());
                		Object inputValue = null;// 单元格值
            			Long longVal = Math.round(cell.getNumericCellValue());
            			Double doubleVal = cell.getNumericCellValue();
            			if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
            	              inputValue = longVal;
	        	         }else{
	        	              inputValue = doubleVal;
	        	         }
            			DecimalFormat df = new DecimalFormat("#.####"); 
            			value = String.valueOf(df.format(inputValue));
            		}else{
                		value = String.valueOf(cell.getStringCellValue());
                	}
                	
                	if(j==0){
                		map.put("idx", value);
                	}
                	if(j==1){
                		map.put("NAME", value);
                	}
                	if(j==2){
                		map.put("STA_SCORE", value);
                	}
                	if(j==3){
                		map.put("SUGGESTION", value);
                	}
                	if(j==4){
                		map.put("CHECK_INSTRUCTION", value);
                	}
                	if(j==5){
                		map.put("DESCRIPTION", value);
                	}
                	if(j==6){
                		map.put("SCORE", value);
                	}
                	if(j==7){
                		map.put("QUESTIONS", value);
                	}
                	if(j==8){
                		map.put("SUGGESTIONS", value);
                	}
                	if(j==9){
                		map.put("BASIS_ID", value);
                	}
                	if(j==10){
                		map.put("FD_OBJECTID", value);
                	}
                }
                result.add(map);
            }
	         
	        return result;
	    } catch (FileNotFoundException e) {
	        throw e;
	    } finally {
	        if (wb != null) {
	            wb.close();
	        }
	        if (is != null) {
	            is.close();
	        }
	    }
	}
	
	
	public static Map<String, String> getTypicalProblems(Map<String, String> remap, Map<String, List<String>> excelMap) {
		Set<String> keys = excelMap.keySet();
		Iterator<String> i = keys.iterator();
		List<Map<String,String>> typicallist = new ArrayList<Map<String,String>>();
		String typicalProblems = "";
		String score = "";
		String questions = "";
		String suggestions = "";
		String real_name = "";
		while(i.hasNext()){
			String key = i.next();
			if(key.contains(remap.get("FD_OBJECTID"))){
				if(!"".equals(excelMap.get(key).get(7))){
					boolean isnumber = isNumber(excelMap.get(key).get(2));
					//typicalProblems += (EvalUtils.formatXiaoShu(excelMap.get(key).get(1))+excelMap.get(key).get(2)+"\n");
					if("".equals(excelMap.get(key).get(2))){
						typicalProblems += (excelMap.get(key).get(1)+excelMap.get(key).get(2)+"\n");
					}else{
						typicalProblems += (excelMap.get(key).get(1)+(isnumber?" 共扣"+excelMap.get(key).get(2)+"分\n":" ("+excelMap.get(key).get(2)+")\n"));
					}
					
					Map<String,String> map = new HashMap<String,String>();
					map.put("basis_id", excelMap.get(key).get(7));
					map.put("business_id", excelMap.get(key).get(8));
					map.put("basis_score", EvalUtils.formatXiaoShu(excelMap.get(key).get(2)));
					map.put("basis_text", excelMap.get(key).get(1));
					map.put("question", excelMap.get(key).get(3));
					map.put("suggestion", excelMap.get(key).get(4));
					String ren = excelMap.get(key).get(5);
					typicallist.add(map);
				}
				if(!"".equals(excelMap.get(key).get(0)))
					score = EvalUtils.formatXiaoShu(excelMap.get(key).get(0));
				if(!"".equals(excelMap.get(key).get(3)))
					questions += (excelMap.get(key).get(3))+"\n";
				if(!"".equals(excelMap.get(key).get(4)))
					suggestions += (excelMap.get(key).get(4))+"\n";
				if(!"".equals(excelMap.get(key).get(5)))
					real_name =excelMap.get(key).get(5);
			}
		}
		remap.put("typicalProblems", typicalProblems);
		remap.put("typicallist", JsonUtil.list2json(typicallist));
		remap.put("score", score);
		remap.put("questions", questions);
		remap.put("suggestions", suggestions);
		remap.put("real_name", real_name);
		return remap;
	}
	//判断是否为数字
	public static boolean isNumber(String str){  
        String reg = "^[0-9]+(.[0-9]+)?$";  
        return str.matches(reg);  
    } 
	
	public static void main(String[] args) {
		//String filePath = "D:\\Users\\len\\Desktop\\excelDemo\\ceshi.xlsx";
		String filePath = "D:\\testdaoru.xlsx";
		try {
			
			/*Map<String, List<String>> map = ImportExcelUtils.readInnerExcelOther(filePath,6);*/
			Map<String, List<String>> map = ImportExcelUtils.readInnerExcel(filePath,14);
			System.out.println(JsonUtil.map2json(map));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}




	
	
}