java代码-Exccel工具类

 
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;
			}	
			
			 
		 
}