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 "未知格式";
}
}
}
将Excel sheet 映射成对象
最新推荐文章于 2023-09-25 15:46:41 发布