题外话: (最近项目不紧,就写写博客,就当是总结了!哈哈)
需求:通过上传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文件图片