java代码-Exccel工具类

古城微笑少年丶 2024-02-18 19:42 12阅读 0赞

import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.io.PushbackInputStream;
    import java.nio.channels.FileChannel;
    import java.text.DateFormat;
    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 java.util.Random;
    
    import org.apache.poi.POIXMLDocument;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    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.XSSFWorkbook;
    
    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    
    
    /**
     * 
     * @author  
     *  
     */
    public class ExcelUtils2 {
    	private static final String EXCEL_XLS = "xls";
    	private static final String EXCEL_XLSX = "xlsx";
    	
    	private static HSSFWorkbook workbook = null;
    	
    	
    	
    	
    	 
    	 
    
    
    	/**
    	 * 判断文件是否存在.
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 * @return
    	 */
    	public static boolean fileExist(String fileDir) {
    		boolean flag = false;
    		File file = new File(fileDir);
    		flag = file.exists();
    		return flag;
    	}
    
    	/**
    	 * 判断文件的sheet是否存在.
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 * @param sheetName
    	 *            表格索引名
    	 * @return
    	 */
    	public static boolean sheetExist(String fileDir, String sheetName) throws Exception {
    		boolean flag = false;
    		File file = new File(fileDir);
    		if (file.exists()) { // 文件存在
    			// 创建workbook
    			try {
    				workbook = new HSSFWorkbook(new FileInputStream(file));
    				// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
    				HSSFSheet sheet = workbook.getSheet(sheetName);
    				if (sheet != null)
    					flag = true;
    			} catch (Exception e) {
    				throw e;
    			}
    
    		} else { // 文件不存在
    			flag = false;
    		}
    		return flag;
    	}
    
    	 
    
    	/**
    	 * 删除文件.
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 */
    	public static boolean deleteExcel(String fileDir) {
    		boolean flag = false;
    		File file = new File(fileDir);
    		// 判断目录或文件是否存在
    		if (!file.exists()) { // 不存在返回 false
    			return flag;
    		} else {
    			// 判断是否为文件
    			if (file.isFile()) { // 为文件时调用删除文件方法
    				file.delete();
    				flag = true;
    			}
    		}
    		return flag;
    	}
    	
    	
    	/**
    	 * 复制文件
    	 * @param f1 d:\ad.txt
    	 * @param f2 c:\ad.txt
    	 * @return
    	 * @throws Exception
    	 */
    	public static long forTransfer(File f1,File f2) throws Exception{
            long time=new Date().getTime();
            int length=2097152;
            FileInputStream in=new FileInputStream(f1);
            FileOutputStream out=new FileOutputStream(f2);
            FileChannel inC=in.getChannel();
            FileChannel outC=out.getChannel();
            int i=0;
            while(true){
                if(inC.position()==inC.size()){
                    inC.close();
                    outC.close();
                    return new Date().getTime()-time;
                }
                if((inC.size()-inC.position())<20971520)
                    length=(int)(inC.size()-inC.position());
                else
                    length=20971520;
                inC.transferTo(inC.position(),length,outC);
                inC.position(inC.position()+length);
                i++;
            }
        }
     
    	/**
    	 * 判断Excel的版本,获取Workbook
    	 * 
    	 * @param in
    	 * @param filename
    	 * @return
    	 * @throws IOException
    	 */
    	public static Workbook getWorkbok(File file) throws IOException {
    		Workbook wb = null;
    		FileInputStream in = new FileInputStream(file);
    		if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
    			wb = new HSSFWorkbook(in);
    		} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
    			wb = new XSSFWorkbook(in);
    		}
    		return wb;
    	}
    
    	public static void main(String[] args) {
    		String[] a={"123.1","321","222"};
    		String[] b={"123","321","222"};
    		String[][] aa={
       {"11","111","111"},{"133","331","232"},{"153","351","55"}};
    		String[][] bb={
       {"11","112","111"},{"133","331","232"},{"153","351","55","66"}};
    		
    		//创建oneSheet假数据
    		List list=new ArrayList<HashMap<String, String>>();	
    		for (int i = 0; i < 3; i++) {
    			Map map = new HashMap<String, String>();
    			for (int j = 0; j < 3; j++) {
    				map.put("777"+j, "666"+j);
    			}
    			list.add(map);
    		}
    		String[] title={"7770","7771","7772"};
    		
    		
    		System.out.println(list);//[{7770=6660, 7772=6662, 7771=6661}, {7770=6660, 7772=6662, 7771=6661}, {7770=6660, 7772=6662, 7771=6661}]
    		String sheetName="123";
    		
    		
    		
    		
    		//创建SameTitel假数据
    		Random rand = new Random(); 
    		List list_same=new ArrayList<>();
    		for (int l = 0; l < 3; l++) {
    			List list_SameTitel1=new ArrayList<HashMap<String, String>>();	
    			for (int i = 0; i < 3; i++) {//3 个sheet
    				Map map = new HashMap<String, String>();
    				for (int j = 0; j < 3; j++) {//3行数据
    					for (int k = 0; k < 3; k++) {//每行的数据
    						map.put("777"+j, rand.nextInt(100) + 1);
    					}
    				}
    				list_SameTitel1.add(map);
    			}		
    			list_same.add(l, list_SameTitel1);
    		}
    		System.out.println(list_same);
    		//[[{7770=31, 7772=85, 7771=19}, {7770=61, 7772=10, 7771=79}, {7770=78, 7772=66, 7771=73}], [{7770=62, 7772=36, 7771=52}, {7770=68, 7772=7, 7771=46}, {7770=17, 7772=71, 7771=43}], [{7770=83, 7772=76, 7771=62}, {7770=48, 7772=6, 7771=18}, {7770=47, 7772=6, 7771=94}]]
    		String[] sheetName_SameTitel={"123","321","222"};
    		
    		//[[{7770=51, 7772=58, 7771=68}, {7770=23, 7772=84, 7771=50}, {7770=40, 7772=18, 7771=24}], [{7770=12, 7772=96, 7771=23}, {7770=40, 7772=15, 7771=20}, {7770=97, 7772=73, 7771=61}], [{7770=91, 7772=68, 7771=70}, {7770=66, 7772=10, 7771=6}, {7770=48, 7772=45, 7771=64}]]
    
    		
    		//创建DisaffinityTitel假数据
    		String[] sheetName_disaff={"123","321","222"};
    		String[][] title_disaff={
       {"110","111","112"},{"220","221","222"},{"440","441","442","443"}};
    		List list_disaff=new ArrayList<>();
    		for (int l = 0; l < 3; l++) {
    			List list_disaff1=new ArrayList<HashMap<String, String>>();	
    			if (l==0) {
    				list_disaff1.clear(); 	
    				for (int i = 0; i < 3; i++) {//3行数据
    					Map map = new HashMap<String, String>();
    							for (int k = 0; k < 3; k++) {//每行的数据
    								map.put("11"+k, rand.nextInt(100) + 1);
    							}
    					list_disaff1.add(map);
    				}
    			} else if (l==1){
    				list_disaff1.clear();
    				for (int i = 0; i < 3; i++) {//3行数据
    					Map map = new HashMap<String, String>();
    							for (int k = 0; k < 3; k++) {//每行的数据
    								map.put("22"+k, rand.nextInt(100) + 1);
    							}
    					list_disaff1.add(map);
    				}
    			} else if (l==2){
    				list_disaff1.clear();
    				for (int i = 0; i < 4; i++) {//4行数据
    					Map map = new HashMap<String, String>();
    							for (int k = 0; k < 4; k++) {//每行的数据
    								map.put("44"+k, rand.nextInt(100) + 1);
    							}
    					list_disaff1.add(map);
    				}
    			}
    			
    			list_disaff.add(l, list_disaff1);
    		}
    		System.out.println(list_disaff);
    		//[[{110=99, 111=37, 112=83}, {110=37, 111=86, 112=79}, {110=93, 111=55, 112=59}], [{220=17, 221=48, 222=65}, {220=98, 221=51, 222=20}, {220=46, 221=79, 222=11}], [{440=69, 441=68, 442=54, 443=71}, {440=29, 441=53, 442=1, 443=52}, {440=47, 441=65, 442=88, 443=46}, {440=53, 441=70, 442=5, 443=12}]]
    
    		String path = ExcelUtils.filePath()+File.separator+"writeweather_sameTitel.xls";
    		System.out.println(path);
    		try {
    			//createExcel_OneSheet(path, "sheet123", a);
     			//createExcel_ManySheet_SameTitel(path, b, a);
    			//createExcel_ManySheet_DisaffinityTitel(path, b, aa);
    			
    //			JSONArray readExcel_OneSheet = readExcel_OneSheet(path, a);// readweather
    //			System.out.println(readExcel_OneSheet);
    //			JSONArray readExcel_SameTitel = readExcel_SameTitel(path, a);//  readweather_sameTitel
    //			System.out.println(readExcel_SameTitel);
    //			JSONArray readExcel_DisaffinityTitel = readExcel_DisaffinityTitel(path, bb);//  readweather_diffTitel
    //			System.out.println(readExcel_DisaffinityTitel);
    		
    //			writeToExcel_OneSheet(path, sheetName, list, title); // writeweather
    //			writeToExcel_ManySheet_SameTitel(path, sheetName_SameTitel, list_same, title); // writeweather_sameTitel
    //			writeToExcel_ManySheet_DisaffinityTitel(path, sheetName_disaff, list_disaff, title_disaff); // writeweather_diffTitel
    			
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	
    	/** 
    	 * 下载文件 
    	 * 返回byte[] 
    	 * @param fileName 需要下载的文件名 
    	 * @return 
    	 * @throws Exception 
    	 */  
    	public static byte[] downFileByte(String downLoadPath) throws Exception{  
    	    byte[] return_arraybyte=null;  
            InputStream ins=new FileInputStream(downLoadPath );  
            ByteArrayOutputStream byteOut = new ByteArrayOutputStream();  
            byte[] buf = new byte[1024];  
            int bufsize = 0;  
            while ((bufsize = ins.read(buf, 0, buf.length)) != -1) {  
                byteOut.write(buf, 0, bufsize);  
            }  
            return_arraybyte = byteOut.toByteArray();  
            byteOut.close();  
            ins.close();  
    	return return_arraybyte;  
    	}
    	
    	
    	   /**
    	    * 静态方法  解决创建Workbook 创建产生的问题(核心方法)
    	    * @param inp
    	    * @return
    	    * @throws IOException
    	    * @throws InvalidFormatException
    	    */
    	   public static Workbook createworkbook(InputStream inp) throws IOException,InvalidFormatException {
    	       if (!inp.markSupported()) {
    	           inp = new PushbackInputStream(inp, 8);
    	       }
    	       if (POIFSFileSystem.hasPOIFSHeader(inp)) {
    	           return new HSSFWorkbook(inp);
    	       }
    	       if (POIXMLDocument.hasOOXMLHeader(inp)) {
    	           return new XSSFWorkbook(OPCPackage.open(inp));
    	       }
    	       throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    	   }
    	   
    ///创建Excel方法/ 	
    	/**
    	 * 创建一个新excel.  一个sheet
    	 * 
    	 * @param fileDir
    	 *            excel的路径
    	 * @param sheetName
    	 *            要创建的表格索引
    	 * @param titleRow
    	 *            excel的第一行即表格头
    	 */
    	public static void createExcel_OneSheet(String fileDir, String sheetName, String titleRow[]) throws Exception {
    		// 创建workbook
    		workbook = new HSSFWorkbook();
    		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
    		HSSFSheet sheet1 = workbook.createSheet(sheetName);
    		// 新建文件
    		FileOutputStream out = null;
    		try {
    			// 添加表头
    			HSSFRow row = workbook.getSheet(sheetName).createRow(0); // 创建第一行
    			for (short i = 0; i < titleRow.length; i++) {
    				HSSFCell cell = row.createCell(i);
    				cell.setCellValue(titleRow[i]);
    			}
    			out = new FileOutputStream(fileDir);
    			workbook.write(out);
    		} catch (Exception e) {
    			throw e;
    		} finally {
    			try {
    				out.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    	
    	/**
    	 * 创建一个新excel,多个sheet的名字, 表格头都相同 
    	 * 
    	 * @param fileDir
    	 *            excel的路径
    	 * @param sheetName[]
    	 *            要创建的表格索引名
    	 * @param titleRow
    	 *            excel的第一行即表格头
    	 */
    	public static void createExcel_ManySheet_SameTitel(String fileDir, String[] sheetName, String titleRow[]) throws Exception {
    		// 创建workbook
    		workbook = new HSSFWorkbook();
    		// 新建文件
    		FileOutputStream out = null;
    		try {
    			for (int k = 0; k < sheetName.length; k++) {
    			// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
    			HSSFSheet sheet1 = workbook.createSheet(sheetName[k]); 
    			// 添加表头
    			HSSFRow row = workbook.getSheet(sheetName[k]).createRow(0); // 创建第一行
    			//HSSFSheet sheet1 = workbook.createSheet(); 创建默认名字的sheet
    			//HSSFRow row = workbook.getSheetAt(k).createRow(0);
    			for (short i = 0; i < titleRow.length; i++) {
    				HSSFCell cell = row.createCell(i);
    				cell.setCellValue(titleRow[i]);
    			}
    			out = new FileOutputStream(fileDir);
    			workbook.write(out);
    			}
    			
    		} catch (Exception e) {
    			throw e;
    		} finally {
    			try {
    				out.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    	
    	/**
    	 * 创建一个新excel,多个sheet的名字, 表格头不相同 
    	 * 
    	 * @param fileDir
    	 *            excel的路径
    	 * @param sheetName[]
    	 *            要创建的表格索引 
    	 *            {"sheet1","sheet2","sheet3","sheet4",......}
    	 * @param titleRow[][]
    	 *            excel的第一行即表格头
    	 *            {
       {"项目编号","项目名称","所属事业部"...},{"编号1","名称2","事业部3","项目经理4"...},{"编号5","名称6","事业部7"...},{"11","22","33","44"...},...}
    	 */
    	public static void createExcel_ManySheet_DisaffinityTitel(String fileDir, String[] sheetName, String titleRow[][])
    			throws Exception {
    		// 创建workbook
    		workbook = new HSSFWorkbook();
    		// 新建文件
    		FileOutputStream out = null;
    		try {
    			for (int k = 0; k < sheetName.length; k++) {
    				// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
    				HSSFSheet sheet1 = workbook.createSheet(sheetName[k]);
    				// 添加表头
    				HSSFRow row = workbook.getSheet(sheetName[k]).createRow(0); // 创建第一行
    				// HSSFSheet sheet1 = workbook.createSheet(); 创建默认名字的sheet
    				// HSSFRow row = workbook.getSheetAt(k).createRow(0);
    				for (short i = 0; i < titleRow[k].length; i++) {
    					HSSFCell cell = row.createCell(i);
    					cell.setCellValue(titleRow[k][i]);
    				}
    				out = new FileOutputStream(fileDir);
    				workbook.write(out);
    			}
    		} catch (Exception e) {
    			throw e;
    		} finally {
    			try {
    				out.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    ///创建Excel方法完
    	
    	
    	
    ///写入Excel方法
    	/**
    	 * 往excel中写入(已存在的数据无法写入).
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 * @param sheetName 
    	 *            表格索引
    	 * @param mapList 
    	 *            需要写入的数据
    	 * @param titleRow[]
    	 *            excel的第一行即表格头
    	 * @throws Exception
    	 */
    	public static void writeToExcel_OneSheet(String fileDir, String  sheetName, List<Map> mapList,String titleRow[]) throws Exception {
    		OutputStream out = null;
    		// 创建workbook
    		File file = new File(fileDir);
    		Workbook workBook = getWorkbok(file);
    		Sheet sheet = workBook.getSheet(sheetName);
    		// 删除原有数据，除了属性列
    		int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
    		Row row0 = sheet.getRow(0);
    		for (int i = 1; i <= rowNumber; i++) {
    			Row row = sheet.getRow(i);
    			sheet.removeRow(row);
    		}
    		// 往Excel中写新数据
    		for (int j = 0; j < mapList.size(); j++) {
    			// 创建一行：从第二行开始，跳过属性列
    			Row row = sheet.createRow(j + 1);
    			Map dataMap = mapList.get(j);
    			System.out.println(dataMap);
    			int count = row0.getLastCellNum(); 
    			for (int k = 0; k <count; k++) {
    				for (int i = 0; i < titleRow.length; i++) {
    					Cell cell = row.createCell(k);
    					if (dataMap.get(titleRow[k])!=null) {
    						cell.setCellValue(dataMap.get(titleRow[k]).toString());
    					}
    				}
    			}
    		}
    		// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
    		out = new FileOutputStream(fileDir);
    		workBook.write(out);
    	}
    	
    	/**
    	 * 往excel中写入(已存在的数据无法写入). 多个sheet,相同的表格头
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 * @param sheetName[]
    	 *            表格索引
    	 * @param mapList 
    	 *            需要写入的数据     
    	  * @param titleRow[]
    	 *            excel的第一行即表格头
    	 * @param object
    	 * @throws Exception
    	 */
    	public static void writeToExcel_ManySheet_SameTitel(String fileDir, String[]  sheetName, List<List<Map>> mapList,String titleRow[]) throws Exception {
    		OutputStream out = null;
    		// 创建workbook
    		File file = new File(fileDir);
    		Workbook workBook = getWorkbok(file);
    		for (int q = 0; q < sheetName.length; q++) {
    			Sheet sheet = workBook.getSheet(sheetName[q]);
    			// 删除原有数据，除了属性列
    			int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
    			Row row0 = sheet.getRow(0);
    			for (int i = 1; i <= rowNumber; i++) {
    				Row row = sheet.getRow(i);
    				sheet.removeRow(row);
    			}
    			// 往Excel中写新数据
    			List<Map> list = mapList.get(q);
    			for (int j = 0; j < list.size(); j++) {
    				// 创建一行：从第二行开始，跳过属性列
    				Row row = sheet.createRow(j + 1);
    				Map dataMap = list.get(j);
    				System.out.println(dataMap);
    				int count = row0.getLastCellNum(); 
    				for (int k = 0; k <count; k++) {
    					for (int i = 0; i < titleRow.length; i++) {
    						Cell cell = row.createCell(k);
    						if (dataMap.get(titleRow[k])!=null) {
    							cell.setCellValue(dataMap.get(titleRow[k]).toString());
    						}
    					}
    				}
    			}
    		}
    		// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
    		out = new FileOutputStream(fileDir);
    		workBook.write(out);
    	}
    	
    	/**
    	 * 往excel中写入(已存在的数据无法写入). 多个sheet,不相同的表格头
    	 * 
    	 * @param fileDir
    	 *            文件路径
    	 * @param sheetName[]
    	 *            表格索引
    	 * @param mapList 
    	 *            需要写入的数据  
    	 * @param titleRow[][]
    	 *            excel的第一行即表格头
    	 * @param object
    	 * @throws Exception
    	 */
    	public static void writeToExcel_ManySheet_DisaffinityTitel(String fileDir, String[]  sheetName, List<List<Map>> mapList,String titleRow[][]) throws Exception {
    		OutputStream out = null;
    		// 创建workbook
    		File file = new File(fileDir);
    		Workbook workBook = getWorkbok(file);
    		for (int q = 0; q < sheetName.length; q++) {
    			Sheet sheet = workBook.getSheet(sheetName[q]);
    			// 删除原有数据，除了属性列
    			int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
    			Row row0 = sheet.getRow(0);
    			for (int i = 1; i <= rowNumber; i++) {
    				Row row = sheet.getRow(i);
    				sheet.removeRow(row);
    			}
    			// 往Excel中写新数据
    			List<Map> list = mapList.get(q);
    			for (int j = 0; j < list.size(); j++) {
    				// 创建一行：从第二行开始，跳过属性列
    				Row row = sheet.createRow(j + 1);
    				Map dataMap = list.get(j);
    				System.out.println(dataMap);
    				int count = row0.getLastCellNum(); 
    				for (int k = 0; k <count; k++) {
    					for (int i = 0; i < titleRow[q].length; i++) {
    						Cell cell = row.createCell(k);
    						if (dataMap.get(titleRow[q][k])!=null) {
    							cell.setCellValue(dataMap.get(titleRow[q][k]).toString());
    						}
    					}
    				}
    			}
    		}
    		// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
    		out = new FileOutputStream(fileDir);
    		workBook.write(out);
    	}
    ///写入Excel方法完	
    	
    	
    	
    	
    ///读取Excel方法	
    	
    	/**
    	 * 只读sheet1,其余sheet有值也不读
    	 * @param filePath
    	 * @param excel_row
    	 * @return
    	 * @throws IOException
    	 * @throws InvalidFormatException
    	 */
    	public static JSONArray readExcel_OneSheet(String filePath,String excel_row[]) throws IOException, InvalidFormatException {
    		InputStream is = new FileInputStream(filePath);
    		Workbook workbook = createworkbook(is);
    		JSONArray array = new JSONArray();
    		List<String> list = new ArrayList<>();
    		if (workbook != null) {
    			// Read the Sheet
    			for (int numSheet = 0; numSheet < 1; numSheet++) {//1 workbook.getNumberOfSheets();
    				Sheet sheet = workbook.getSheetAt(numSheet);
    				if (sheet == null) {
    					continue;
    				}
    				// Read the Row
    				for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
    					JSONObject data = new JSONObject();
    					Row row = sheet.getRow(rowNum);
    					if (row == null) {
    						continue;
    					}
    					int start = row.getFirstCellNum();
    					int end = row.getLastCellNum();
    					boolean isRowBlank=true;
    					for(int i=start;i<end;i++){
    						Cell cell = row.getCell(i);
    						String element = "";
    						if (cell!=null&&!cell.equals("")) {
    							switch (cell.getCellType()) { 
    							case HSSFCell.CELL_TYPE_NUMERIC: // 数字    
    								if (HSSFDateUtil.isCellDateFormatted(cell)) {
    									Date d = cell.getDateCellValue();
    									DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
    									element = formater.format(d);
    								}else{
    									//因为数值会由12变成12.0 所以处理一下
    									String b=cell.getNumericCellValue()+"";
    									String bb=b.substring(b.lastIndexOf(".")+1);
    									if (Integer.valueOf(bb)==0) {
    										element =b.substring(0, b.lastIndexOf("."));
    									}else{
    										element = cell.getNumericCellValue() + "";
    									}
    								}
    								isRowBlank=false;
    								break;
    							case HSSFCell.CELL_TYPE_STRING: // 字符串
    								element = cell.getStringCellValue();
    								isRowBlank=false;
    								break;
    							case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
    								element = cell.getBooleanCellValue() + "";
    								isRowBlank=false;
    								break;
    							case HSSFCell.CELL_TYPE_FORMULA: // 公式
    								element = cell.getCellFormula() + "";
    								isRowBlank=false;
    								break;
    							case HSSFCell.CELL_TYPE_BLANK: // 空值
    								break;
    							case HSSFCell.CELL_TYPE_ERROR: // 故障
    								break;
    							default:
    								break;
    							}
    						}  
    						if(isRowBlank) continue;
    					 
    						if (rowNum == 0) {
    							list.add(i, element);
    						} else {
    							data.put(list.get(i), element);
    						}
    					}
    					if(rowNum == 0){
    						if(!validateFormat(list,excel_row)){
    							System.out.println("文档格式不正确");
    							return null;
    						}else{
    							System.out.println("验证通过");
    						}
    					}
    					if (rowNum > 0&&!data.isEmpty())
    						array.add(data);
    				}
    			}
    		}
    		return array;
    	}	
    	
    	
    	
    		
    	   /**
    		 * 读取Excel文件,格式：.xls,.xlsx,多个sheet的标题头格式必须保持一致
    		 * 返回数据K-V格式，表头作为key格式如下：[{"所属专项名称":"金凤","指南方向":"指南方向","负责人":"XXX"}]
    		 * @param filePath
    		 *            文件详细地址 /path/excel.xls
    		 * @param excel_row
    		 *             Excel的标题头
    		 * @throws IOException 
    		 * @throws InvalidFormatException 
    		 * @return 合并多个sheet的row值
    		 * [[{"321":"2","222":"3","123.1":"1"},{"321":"5","222":"6","123.1":"4"}],[{"321":"222","222":"333","123.1":"222"}],[{"321":"222","222":"222","123.1":"222"}]]
    		 */
    		public static JSONArray readExcel_SameTitel(String filePath,String[] excel_row) throws IOException, InvalidFormatException {
    			InputStream is = new FileInputStream(filePath);
    			Workbook workbook = createworkbook(is);
    			JSONArray array = new JSONArray();
    			List<String> list = new ArrayList<>();
    			if (workbook != null) {
    				// Read the Sheet
    				for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
    					list.clear();
    					Sheet sheet = workbook.getSheetAt(numSheet);
    					if (sheet == null) {
    						continue;
    					}
    					JSONArray array_sheet = new JSONArray();
    					// Read the Row
    					for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
    						JSONObject data = new JSONObject();
    						Row row = sheet.getRow(rowNum);
    						if (row == null) {
    							continue;
    						}
    						int start = row.getFirstCellNum();
    						int end = row.getLastCellNum();
    						boolean isRowBlank=true;
    						for(int i=start;i<end;i++){
    							Cell cell = row.getCell(i);
    							String element = "";
    							if (cell!=null&&!cell.equals("")) {
    								switch (cell.getCellType()) { 
    								case HSSFCell.CELL_TYPE_NUMERIC: // 数字    
    									if (HSSFDateUtil.isCellDateFormatted(cell)) {
    										Date d = cell.getDateCellValue();
    										DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
    										element = formater.format(d);
    									}else{
    										//因为数值会由12变成12.0 所以处理一下
    										String b=cell.getNumericCellValue()+"";
    										String bb=b.substring(b.lastIndexOf(".")+1);
    										if (Integer.valueOf(bb)==0) {
    											element =b.substring(0, b.lastIndexOf("."));
    										}else{
    											element = cell.getNumericCellValue() + "";
    										}
    									}
    									isRowBlank=false;
    									break;
    								case HSSFCell.CELL_TYPE_STRING: // 字符串
    									element = cell.getStringCellValue();
    									isRowBlank=false;
    									break;
    								case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
    									element = cell.getBooleanCellValue() + "";
    									isRowBlank=false;
    									break;
    								case HSSFCell.CELL_TYPE_FORMULA: // 公式
    									element = cell.getCellFormula() + "";
    									isRowBlank=false;
    									break;
    								case HSSFCell.CELL_TYPE_BLANK: // 空值
    									break;
    								case HSSFCell.CELL_TYPE_ERROR: // 故障
    									break;
    								default:
    									break;
    								}
    							}  
    							if(isRowBlank) continue;
    						 
    							if (rowNum == 0) {
    								list.add(i, element);
    							} else {
    								data.put(list.get(i), element);
    							}
    						}
    						if(rowNum == 0){
    							if(!validateFormat(list,excel_row)){
    								System.out.println("文档格式不正确");
    								return null;
    							}else{
    								System.out.println("验证通过");
    							}
    						}
    						if (rowNum > 0&&!data.isEmpty()){
    							array_sheet.add(data);
    						}
    					}
    					array.set(numSheet, array_sheet); 
    				}
    			}
    			System.out.println("size:"+array.size());
    			System.out.println(array.toJSONString());
    			return array;
    		}
    		
    		
    		
    		
    		   /**
    			 * 读取Excel文件,格式：.xls,.xlsx,多个sheet格式不一致
    			 * 返回数据K-V格式，表头作为key格式如下：[{"所属专项名称":"金凤","指南方向":"指南方向","负责人":"XXX"}]
    			 * @param filePath
    			 *            文件详细地址 /path/excel.xls
    			 * @param excel_row
    			 *             多个sheet的标题头 
    			 * @throws IOException 
    			 * @throws InvalidFormatException 
    			 * @return
    			 * [[{"11":"1","111":"3","112":"2"},{"11":"4","111":"6","112":"5"}],[{"133":"222","331":"222","232":"333"}],[{"55":"222","66":"2","153":"222","351":"222"}]]
    			 */
    			public static JSONArray readExcel_DisaffinityTitel(String filePath,String[][] excel_row) throws IOException, InvalidFormatException {
    				InputStream is = new FileInputStream(filePath);
    				Workbook workbook = createworkbook(is);
    				JSONArray array = new JSONArray();
    				List<String> list = new ArrayList<>();
    				if (workbook != null) {
    					// Read the Sheet
    					for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
    						list.clear();
    						Sheet sheet = workbook.getSheetAt(numSheet);
    						if (sheet == null) {
    							continue;
    						}
    						JSONArray array_sheet = new JSONArray();
    						// Read the Row
    						for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
    							JSONObject data = new JSONObject();
    							Row row = sheet.getRow(rowNum);
    							if (row == null) {
    								continue;
    							}
    							int start = row.getFirstCellNum();
    							int end = row.getLastCellNum();
    							boolean isRowBlank=true;
    							for(int i=start;i<end;i++){
    								Cell cell = row.getCell(i);
    								String element = "";
    								if (cell!=null&&!cell.equals("")) {
    									switch (cell.getCellType()) { 
    									case HSSFCell.CELL_TYPE_NUMERIC: // 数字    
    										if (HSSFDateUtil.isCellDateFormatted(cell)) {
    											Date d = cell.getDateCellValue();
    											DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
    											element = formater.format(d);
    										}else{
    											//因为数值会由12变成12.0 所以处理一下
    											String b=cell.getNumericCellValue()+"";
    											String bb=b.substring(b.lastIndexOf(".")+1);
    											if (Integer.valueOf(bb)==0) {
    												element =b.substring(0, b.lastIndexOf("."));
    											}else{
    												element = cell.getNumericCellValue() + "";
    											}
    										}
    										isRowBlank=false;
    										break;
    									case HSSFCell.CELL_TYPE_STRING: // 字符串
    										element = cell.getStringCellValue();
    										isRowBlank=false;
    										break;
    									case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
    										element = cell.getBooleanCellValue() + "";
    										isRowBlank=false;
    										break;
    									case HSSFCell.CELL_TYPE_FORMULA: // 公式
    										element = cell.getCellFormula() + "";
    										isRowBlank=false;
    										break;
    									case HSSFCell.CELL_TYPE_BLANK: // 空值
    										break;
    									case HSSFCell.CELL_TYPE_ERROR: // 故障
    										break;
    									default:
    										break;
    									}
    								}  
    								if(isRowBlank) continue;
    							 
    								if (rowNum == 0) {
    									list.add(i, element);
    								} else {
    									data.put(list.get(i), element);
    								}
    							}
    							if(rowNum == 0){
    								if(!validateFormat(list,excel_row[numSheet])){
    									System.out.println("文档格式不正确");
    									return null;
    								}else{
    									System.out.println("验证通过");
    								}
    							}
    							if (rowNum > 0&&!data.isEmpty())
    								array_sheet.add(data);
    						}
    						array.set(numSheet, array_sheet); 
    					}
    				}
    				System.out.println("size:"+array.size());
    				System.out.println(array.toJSONString());
    				return array;
    			}
    
    ///读取Excel方法完			
    		
    			
    			/**
    			 * 验证标题头
    			 * @param list
    			 * @param excel_row
    			 * @return
    			 */
    			public static  boolean validateFormat(List<String> list,String excel_row[]){
    				boolean state =false;
    					if(list.size()==excel_row.length){
    						for (int i = 0; i < list.size(); i++) {
    							state = list.get(i).equals(excel_row[i]);
    							if (!state) {
    								break;
    							}
    						}
    					}
    				return state;
    			}	
    			
    			 
    		 
    }