POI读取excel文件通用代码（增强版--支持返回javaBean集合）---2003,2007

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi2.hssf.usermodel.HSSFCell;
import org.apache.poi2.hssf.usermodel.HSSFRow;
import org.apache.poi2.hssf.usermodel.HSSFSheet;
import org.apache.poi2.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {
	
	private ExcelUtil(){};
	private static List<String> columns;//要解析excel中的列名
	private static int sheetNum = 0;//要解析的sheet下标
	/**
	 * poi读取excle
	 * @return
	 */
	public static String readExcel(File file){
		StringBuilder retJson = new StringBuilder();
		InputStream inStream = null;
		try {
			inStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(inStream);
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表
			int lastRowNum = sheet.getLastRowNum();//最后一行
			retJson.append("[");
			for(int i = 0 ;i < lastRowNum;i++){
				HSSFRow row = sheet.getRow(i);//获得行
				String rowJson = readExcelRow(row);
				retJson.append(rowJson);
				if(i<lastRowNum-1)
					retJson.append(",");
			}
			retJson.append("]");
		} catch (Exception e) {
			try {
				inStream = new FileInputStream(file);
				XSSFWorkbook workbook = new XSSFWorkbook(inStream);
				XSSFSheet sheet = workbook.getSheetAt(sheetNum);
				int lastRowNum = sheet.getLastRowNum();//最后一行
				retJson.append("[");
				for(int i = 0 ;i < lastRowNum;i++){
					XSSFRow row = sheet.getRow(i);//获得行
					String rowJson = readExcelRow(row);
					retJson.append(rowJson);
					if(i<lastRowNum-1)
						retJson.append(",");
				}
				retJson.append("]");
			} catch (IOException e1) {
				e1.printStackTrace();
			}
		}finally{
			close(null,inStream);
		}
		return retJson.toString();
	}
	/**
	 * poi读取excle 生成实体集合
	 * @param <E>
	 * @return
	 */
	public static <E> List<E> readExcel(File file,Class<E> clazz){

		InputStream inStream = null;
		List<E> eList = new ArrayList<E>();
		try {
			inStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(inStream);
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表
			int lastRowNum = sheet.getLastRowNum();//最后一行
			for(int i = 1 ;i < lastRowNum;i++){
				HSSFRow row = sheet.getRow(i);//获得行
				String rowJson = readExcelRow(row);
				E _e = json2Bean(rowJson,clazz);
				eList.add(_e);
			}
		} catch (Exception e) {
			try {
				inStream = new FileInputStream(file);
				XSSFWorkbook workbook = new XSSFWorkbook(inStream);
				XSSFSheet sheet = workbook.getSheetAt(sheetNum);
				int lastRowNum = sheet.getLastRowNum();//最后一行
				for(int i = 1 ;i < lastRowNum;i++){
					XSSFRow row = sheet.getRow(i);//获得行
					String rowJson = readExcelRow(row);
					E _e = json2Bean(rowJson,clazz);
					eList.add(_e);
				}
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}finally{
			close(null,inStream);
		}
		return eList;
	}
	/**
	 * 将json转换为Bean实例
	 * @param <E>
	 * @return
	 */
	private static <E> E json2Bean(String json,Class<E> clazz){
		JSONObject jsonObj = JSONObject.fromObject(json);
		Method[] methods = clazz.getDeclaredMethods();
		try {
			E _e = clazz.newInstance();
			for(Method m:methods){
				String name = m.getName();
				if(name.startsWith("set")){
					String keyPrev = name.substring(3,4).toLowerCase();
					String keyNext = name.substring(4);
					String val = "";
					try {
						val = jsonObj.getString(keyPrev+keyNext);
					} catch (Exception e) {
						val = "";
					}
					m.invoke(_e, val);
				}
			}
			return _e;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 读取行值
	 * @return
	 */
	private static String readExcelRow(HSSFRow row){
		StringBuilder rowJson = new StringBuilder();
		int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
		rowJson.append("{");
		for(int i = 0 ;i<lastCellNum;i++){
			HSSFCell cell = row.getCell(i);
			String cellVal = readCellValue(cell);
			rowJson.append(toJsonItem(columns.get(i), cellVal));
			if(i<lastCellNum-1)
				rowJson.append(",");
		}
		rowJson.append("}");
		return rowJson.toString();
	}
	/**
	 * 读取行值
	 * @return
	 */
	private static String readExcelRow(XSSFRow row){
		StringBuilder rowJson = new StringBuilder();
		int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
		rowJson.append("{");
		for(int i = 0 ;i<lastCellNum;i++){
			XSSFCell cell = row.getCell(i);
			String cellVal = readCellValue(cell);
			rowJson.append(toJsonItem(columns.get(i), cellVal));
			if(i<lastCellNum-1)
				rowJson.append(",");
		}
		rowJson.append("}");
		return rowJson.toString();
	}

	/**
	 * 读取单元格的值
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("static-access")
	private static String readCellValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			hssfCell.setCellType(1);//设置为String
			String str_temp = String.valueOf(hssfCell.getRichStringCellValue());//得到值
			return str_temp;
		}else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA){
			int val = hssfCell.getCachedFormulaResultType();
			return val+"";
		} else {
			return String.valueOf(hssfCell.getRichStringCellValue());
		}
	}
	/**
	 * 读取单元格的值
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("static-access")
	private static String readCellValue(XSSFCell sssfCell) {
		if (sssfCell.getCellType() == sssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(sssfCell.getBooleanCellValue());
		} else if (sssfCell.getCellType() == sssfCell.CELL_TYPE_NUMERIC) {
			sssfCell.setCellType(1);//设置为String
			String str_temp = String.valueOf(sssfCell.getRichStringCellValue());//得到值
			return str_temp;
		}else if(sssfCell.getCellType() == sssfCell.CELL_TYPE_FORMULA){
			int val = sssfCell.getCachedFormulaResultType();
			return val+"";
		}else {
			return String.valueOf(sssfCell.getRichStringCellValue());
		}
	}
	/**
	 * 转换为json对
	 * @return
	 */
	private static String toJsonItem(String name,String val){
		return "\""+name+"\":\""+val+"\"";
	}
	/**
	 * 关闭io流
	 * @param fos
	 * @param fis
	 */
	private static void close(OutputStream out, InputStream in) {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                System.out.println("InputStream关闭失败");
                e.printStackTrace();
            }
        }
        if (out != null) {
            try {
                out.close();
            } catch (IOException e) {
                System.out.println("OutputStream关闭失败");
                e.printStackTrace();
            }
        }
    }
	public static List<String> getColumns() {
		return ExcelUtil.columns;
	}
	public static void setColumns(List<String> columns) {
		ExcelUtil.columns = columns;
	}
	public static int getSheetNum() {
		return sheetNum;
	}
	public static void setSheetNum(int sheetNum) {
		ExcelUtil.sheetNum = sheetNum;
	}
	
}