将Excel sheet 映射成对象

package cn.howso.sros.utils.xl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.XSSFWorkbook;


import cn.howso.sros.dao.model.xl.ActivatePDPRequestBean;
import cn.howso.sros.dao.model.xl.ActivatePDPacceptBean;
import cn.howso.sros.dao.model.xl.AttachCompleteBean;
import cn.howso.sros.dao.model.xl.AttachRequestBean;
import cn.howso.sros.dao.model.xl.DNSBean;
import cn.howso.sros.dao.model.xl.FlushLLBean;
import cn.howso.sros.dao.model.xl.HttpWapBean;
import cn.howso.sros.dao.model.xl.IMBean;
import cn.howso.sros.dao.model.xl.ImsiMsisdnBean;
import cn.howso.sros.dao.model.xl.KqiSumIMSIBean;
import cn.howso.sros.dao.model.xl.RadioStatusBean;
import cn.howso.sros.dao.model.xl.RauRejectBean;
import cn.howso.sros.dao.model.xl.RaurequestBean;
import cn.howso.sros.utils.StringUtil;

/**
 * 功能说明：信令解析工具类
 * 创建时间：2014-10-16下午5:13:31
 * @author liudd
 */
public class XlUtils {
	//private Logger log = Logger.getLogger(XlUtils.class);
	/**
	 * 获取解析excel数据
	 * @param excel_path
	 * @return
	 */
	public Map<String,List<Object>> getDataBeans(String excel_path) throws FileNotFoundException, IOException, SecurityException, ClassNotFoundException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
	{
		File f = new File(excel_path);
		if(!f.exists())
		{
			System.out.println("文件不存在!!");
			return null;
		}
		Workbook wb = null;
		if(f.getName().endsWith(".xlsx"))
		{
			wb = new XSSFWorkbook(new FileInputStream(f));
		}else if(f.getName().endsWith(".xls"))
		{
			wb = new HSSFWorkbook(new FileInputStream(f));
		}
		else
		{
			System.out.println("excel文件格式错误!!");
			return null;
		}
		
		// class 顺序 需要与excel sheet 顺序对应一致
		Class<?> [] clsAry = {
				            AttachRequestBean.class,
				            AttachCompleteBean.class,
				            ActivatePDPRequestBean.class,
							ActivatePDPacceptBean.class,
							RadioStatusBean.class,
							DNSBean.class,
							RaurequestBean.class,
							RauRejectBean.class,
							KqiSumIMSIBean.class,
							FlushLLBean.class,
							HttpWapBean.class,
							IMBean.class,
							ImsiMsisdnBean.class
		                  };
		Map<String,List<Object>> map = new HashMap<String, List<Object>>();
		for(int i=0;i<clsAry.length;i++)
		{
			System.out.println("======开始解析："+clsAry[i].getSimpleName());
			map.put(clsAry[i].getSimpleName(), new XlUtils().getBeansFromExcel(wb, clsAry[i], i));
			System.out.println("======解析完成："+clsAry[i].getSimpleName());
		}
		
		return map;
	}
	
	public static void main(String[] args) throws SecurityException, ClassNotFoundException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, FileNotFoundException, IOException {

		Map<String,List<Object>> map = new XlUtils().getDataBeans("D://xl.xlsx");
		List<Object> ls = map.get(ActivatePDPacceptBean.class.getSimpleName());
		
		for(Object obj:ls)
		{
			System.out.println(((ActivatePDPacceptBean)obj).getIMSI());
		}
	}
	
	/**
	 * 获取sheet对应的class bean 集合 excel列标题必须与类字段名称一致
	 * @param book
	 * @param cls sheet映射类
	 * @param index
	 * @return
	 */
	public List<Object> getBeansFromExcel(Workbook book,Class<?> cls, int index ) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
	{
		Sheet sheet = book.getSheetAt(index);
		Field [] fs = cls.getDeclaredFields();
		String [] names = new String[fs.length];
		// 获取列名
		for(int i=0;i<fs.length;i++)
		{
			names[i] = fs[i].getName();
		}
		int rowIndex = 1;
		List<Object> list = new ArrayList<Object>();
		while(true)
		{
			Row row = sheet.getRow(rowIndex);
			if(row==null)
			{
				break;
			}
			Object obj = cls.newInstance();
			Method [] methods = cls.getMethods();
			Map<String,Method> mdMap = this.getMethodMap(methods);
			// 判断是否存在数据
			boolean isEmpty = true;
			
			for(int i=0;i<names.length;i++)
			{
				String value = this.getCellValue(row.getCell(i));
				if(!StringUtil.isEmpty(value))
				{
					isEmpty = false;
				}
				// 获取set field 方法
				String setMethod = this.wrapSetField(names[i]);
				Method md = mdMap.get(setMethod);
				md.invoke(obj, value);
			}
			if(isEmpty)
			{
				break;
			}
			list.add(obj);
			rowIndex++;
		}
		return list;
	}
	
	/**
	 * 获取类方法键值对
	 * @param ms
	 * @return
	 */
	private Map<String,Method> getMethodMap(Method [] ms)
	{
		Map<String,Method> map = new HashMap<String, Method>();
		for(Method m : ms)
		{
			map.put(m.getName(), m);
		}
		
		return map;
	}
	/**
	 * 获取set方法名称
	 * @param name
	 * @return
	 */
	private String wrapSetField(String name)
	{
		String firstChar = String.valueOf(name.charAt(0)).toUpperCase();
		name = firstChar + name.substring(1);
		return "set"+name;
	}
	
	/**
	 * 获取cell String值
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell)
	{
		if(cell==null)
		{
			return "";
		}
		int type = cell.getCellType();
		if(type==Cell.CELL_TYPE_NUMERIC)
		{
			return cell.getNumericCellValue()+"";
		}
	    if(type==Cell.CELL_TYPE_STRING)
		{
			return cell.getStringCellValue();
		}
	    else
	    {
	    	return "未知格式";
	    }
	
	}
}