利用POI读入excel通过反射生成Bean源码实现

 题外话： （最近项目不紧，就写写博客，就当是总结了！哈哈）

 

需求：通过上传Excel文件，解析其中的数据，并生成对应的javabean对象，其中Excel里面可能含有一些无用的列数据 (maven项目)

 

 

思路：

        1.创建一个XX.properties文件（key-value），其中key为Excel中列数如1,2,3等，value为Excel中该列数据对应的javabean属性

        2.通过读Excel文件，借助上面的属性文件采用POI遍历数据，生成List<Map<String,Object>>结构的数据

        3.将第二步中的List<Map<String,Object>>结构，通过反射生成对应的Javabean对象。

 

 

             

 

下面是代码的实现：

 

javabean类

 

public class OrderForm{
	
	private Long globalOrderId;
	private String globalOrderNo;
	private String merchantCode;
	private String merchantName;
	private String cityCode;
	private String orderSource;
	private String orderSourceName;
	private String orderNum;
	private String buyerAccount;
	private String phone;
	private String email;
	private String mftNo;
	private String logisticsNo;
	private String logisticsName;
	private String senderName;
	private String senderTel;
	private String senderCompanyName;
	private String senderAddr;
	private String senderZip;
	private String senderCity;
	private String consignee;
	private String consigneeTel;
	private String consigneeProvince;
	private String consigneeCity;
	private String consigneeDistrict;
	private String consigneeAddr;
	private String mailNo;
	private String goodsDesc;
        //省略上面的setter/getter方法

}

 

相应的实现类

 

package com.poi;


import java.io.FileInputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;
import java.util.Set;


import org.apache.commons.beanutils.BeanUtils;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.fastjson.JSONObject;

public class ExcelToBean 
{
	private static ResourceBundle modelPropertiesBundle ;
	
	static {
		//首次加载该类时加载model.properites文件资源
		modelPropertiesBundle = ResourceBundle.getBundle("model");
	}
    
	/**
	 * 将workbook中的值放入List<Map<String,Object>>结构中
	 * */
    public static List<Map<String, Object>> parseExcel(Workbook workbook){
    	List<Map<String, Object>> result = new LinkedList<Map<String,Object>>();
    	int excleRowLength = workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();
    	String [] columnName = new String [excleRowLength]; //相应的javabean类的属性名称数组
    	for(int i=0;i<columnName.length;i++){	//从资源文件中获取
    		if(modelPropertiesBundle.containsKey((String.valueOf(i)))){
    			columnName[i] = modelPropertiesBundle.getString(String.valueOf(i));
    		}
    	}
    	for(int sheetIndex=0;sheetIndex < workbook.getNumberOfSheets();sheetIndex++){	//sheet
    		HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(sheetIndex);
    		for(int rowIndex=1;rowIndex < sheet.getPhysicalNumberOfRows();rowIndex++){	//row
    			HSSFRow row = sheet.getRow(rowIndex);
    			Map<String, Object> map = new HashMap<String, Object>();
    			for(int cellIndex=0;cellIndex < row.getPhysicalNumberOfCells();cellIndex++){ //cell
    				HSSFCell cell = row.getCell(cellIndex);
    				if(columnName[cellIndex]!=null && columnName[cellIndex].trim().length()>0){ //该列值在对应的java对象中有值
    					//取出当前cell的值和对应Javabean类的属性放入到map中
    					map.put(columnName[cellIndex].trim(), getCellValue(cell));  
    				}
    			}
    			result.add(map);
    		}
    	}
    	System.out.println(JSONObject.toJSONString("list<>=" + result));
    	return result;
    }
    

    /**
     * 利用反射将	List<Map<String,Object>>结构 生成相应的List<T>数据
     * 
     * */
    public static <T>  List<T> toObjectList(List<Map<String, Object>> list,Class<T> clazz) throws Exception{
    	List<T> returnList = new LinkedList<T>();
    	for(int i=0;i<list.size();i++){
    		Set<Map.Entry<String, Object>> set =  list.get(i).entrySet();
    		Iterator<Entry<String, Object>> it = set.iterator();
			T obj= clazz.newInstance();
			Method[] methods = clazz.getDeclaredMethods();
    		while (it.hasNext()) {		//生成一个obj
				Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
				for(Method m:methods){
					if(m.getName().startsWith("set")){		//为obj赋值
						String methodName = entry.getKey().toString();
						StringBuffer sb = new StringBuffer(methodName);  
		                sb.replace(0, 1, (methodName.charAt(0)+"").toUpperCase());  
		                methodName = "set" +  sb.toString();
						if(methodName.equals(m.getName())){
							m.invoke(obj, entry.getValue());
							break;
						}
					}
				}
			}
			returnList.add(obj);
    	}
    	System.out.println("size=" + returnList.size());
    	return returnList;
    }
    
    
    /**
     * 获取当前单元格内容
     * */
    private static String getCellValue(Cell cell){
    	String value = "";
    	if(cell!=null){
	    	switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){ //日期类型
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
					value = sdf.format(date);
				}else{
					Double data = cell.getNumericCellValue();
					value = data.toString();
				}
				break;
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				Boolean data = cell.getBooleanCellValue();
				value = data.toString(); 
				break;
			case Cell.CELL_TYPE_ERROR:
				System.out.println("单元格内容出现错误");
				break;
			case Cell.CELL_TYPE_FORMULA:
	            value = String.valueOf(cell.getNumericCellValue());  
	            if (value.equals("NaN")) {// 如果获取的数据值非法,就将其装换为对应的字符串
	                value = cell.getStringCellValue().toString();  
	            }  			
				break;			
			case Cell.CELL_TYPE_BLANK:
				System.out.println("单元格内容 为空值 ");
				break;			
			default :
				value = cell.getStringCellValue().toString();
				break;
			}
    	}
    	return value;
    }
    
    public static void main(String args[]) throws Exception{
    	FileInputStream input = new FileInputStream("C:\\Users\\lenovo\\Desktop\\积分商城订单.xls");
    	HSSFWorkbook workbook = new HSSFWorkbook(input);
    	
    	List<Map<String, Object>> list =  parseExcel(workbook);
    	
    	List<OrderForm> lists =  toObjectList(list, OrderForm.class);
    	
    	System.out.println(JSONObject.toJSONString(lists)); //利用fastjson将其序列化打印出来
    	
    }
    
    
}

 

下面是相应的properties文件

 

#0=order,
1=orderNum
2=goodsSku
3=goodsName
4=quantity
#5=ji fen shuliang *
#jie suan jia *
6=price
#7=status *
#8=ke hu hao *
9=orderTime
10=payTime
11=consignee
12=consigneeTel
#13= Message Tel
14=consigneeAddr
15=buyerAccount
16=buyerTel
#17=numberLocatorProvince
#18=numberLocatorCity
#19=orderStatu
#20=cash
#21=typeId
#22=receiveTime

 

 

maven依赖

 

     <dependency>
  		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.12</version>  
    </dependency>
    <dependency>
    	<groupId>com.alibaba</groupId>
		<artifactId>fastjson</artifactId>
		<version>1.2.6</version>
    </dependency> 

 

相应的excel文件图片

 

 

 

 

评论

1 楼 T丨amo 2017-02-13  

哈喽，博主， 在网上查东西的时候看到了你的文章，很好的解决了我的问题，我也做了总结，参考了你的思路的代码，这里征求你同意一下、   这是我的 地址： http://blog.csdn.net/tiamo_1314/article/details/55005608