package com.randy;
import java.beans.beaninfo;
import java.beans.introspectionexception;
import java.beans.introspector;
import java.beans.propertydescriptor;
import java.io.fileinputstream;
import java.io.filenotfoundexception;
import java.io.ioexception;
import java.text.dateformat;
import java.text.decimalformat;
import java.text.simpledateformat;
import java.util.arraylist;
import java.util.date;
import java.util.hashmap;
import java.util.iterator;
import java.util.list;
import java.util.map;
import java.util.set;
import org.apache.commons.beanutils.beanutils;
import org.apache.commons.beanutils.propertyutils;
import org.apache.commons.collections.maputils;
import org.apache.commons.lang.arrayutils;
import org.apache.commons.lang.booleanutils;
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;
public class excelreader {
final simpledateformat format = new simpledateformat("yyyy-mm-dd");
private fileinputstream inputstream;
private hssfworkbook workbook;
private int sheetindex;
/**
* 开始读取数据行
*/
private int startrow =0;
* 开始读取数据列
private int startcolumn=0;
* 指定输入流
* 初始化workbook对象
private void initworkbook() {
if (workbook == null) {
try {
workbook = new hssfworkbook(inputstream);
} catch (ioexception e) {
e.printstacktrace();
}
}
}
* 获取所有行
* @return
public list<hssfrow> readhssfrows(){
hssfsheet sheet = workbook.getsheetat(sheetindex);
//实际存在的行数
int rows = sheet.getphysicalnumberofrows();
list<hssfrow> hssfrows = new arraylist<hssfrow>();
for (int count = startrow; count <=rows-1; count++) {
hssfrow row = sheet.getrow(count);
if(row !=null){
hssfrows.add(row);
return hssfrows;
* 读取行数据
* @param hssfrow
public string[] readcellsvalue(hssfrow hssfrow){
int cells = hssfrow.getphysicalnumberofcells();//列数
system.out.println("\nrow " + hssfrow.getrownum() + " has " + cells
+ " cell(s).");
string[] cellvalues = new string[cells];
for (int counter = startcolumn; counter < cells; counter++) {
hssfcell cell = hssfrow.getcell(counter);
string value = null;
switch (cell.getcelltype()) {
case hssfcell.cell_type_formula:
value = cell.getcellformula();
break;
case hssfcell.cell_type_numeric:
value = string.valueof(cell.getnumericcellvalue());
if (hssfdateutil.iscelldateformatted(cell)) {
date date = cell.getdatecellvalue();
if (date != null) {
value = format.format(date);
} else {
value = "";
}
} else {
value = new decimalformat("0").format(cell.getnumericcellvalue());
}
case hssfcell.cell_type_string:
value = cell.getstringcellvalue();
default:
value ="9999";
cellvalues[counter] = value;
}//end for
return cellvalues;
*
* 根据传入的map;将cell的数值依次对应赋值给对象属性
* @param propertiesmap:格式為[-1:class,0:filedname1;1:filedname2.....]
* 其中：0代表第0个cell;1代表第1个cell
* @param cells
@suppresswarnings("unchecked")
public object genvofromcells(map<integer,string> propertiesmap,string cells[]){
//存放对象属性描述信息
map<string,propertydescriptor> propmap = new hashmap<string,propertydescriptor>();
object obj = null;
string classname = propertiesmap.get(-1);
map properdesc = null;
try {
class cls = class.forname(classname);
obj = cls.newinstance();
//获取对象的描述信息
beaninfo bi = introspector.getbeaninfo(cls);
propertydescriptor[] pds = bi.getpropertydescriptors();
for(propertydescriptor p:pds){
propmap.put(p.getname(), p);
}
properdesc = beanutils.describe(obj);
set<integer> keys = propertiesmap.keyset();
iterator<integer> iter = keys.iterator();
while(iter.hasnext()){
integer index = iter.next();
if(index.intvalue() == -1){continue;}
string property = propertiesmap.get(index);
if(properdesc.containskey(property)){
//获取对象类型
class<?> propertytype = propmap.get(property).getpropertytype();
system.out.println(property+".class:"+propertytype);
string value = cells[index];
if(string.class.equals(propertytype)){
propertyutils.setproperty(obj, property, value);
}else if(date.class.equals(propertytype)){
date d;
try {
d = format.parse(value);
} catch (exception e) {
d = new date();
}
propertyutils.setproperty(obj,property,d);
}else if(int.class.equals(propertytype)|| integer.class.equals(propertytype)){
int i = 0;
try{
i = integer.parseint(value);
}catch (numberformatexception e) {
i = 0;
propertyutils.setproperty(obj,property,i);
}else if(double.class.equals(propertytype) || double.class.equals(propertytype)){
double d = 0;
d = double.parsedouble(value);
d = 0.0;
propertyutils.setproperty(obj,property,d);
}else if(long.class.equals(propertytype) || long.class.equals(propertytype)){
long l = 0;
l = long.parselong(value);
l = 0l;
propertyutils.setproperty(obj,property,l);
}else if(float.class.equals(propertytype) || float.class.equals(propertytype)){
float f = 0;
f = float.parsefloat(value);
f = 0l;
propertyutils.setproperty(obj,property,f);
}else{//boolean
boolean b = booleanutils.tobooleanobject(value);
propertyutils.setproperty(obj,property,b);
}
}
} catch (exception e) {
e.printstacktrace();
return obj;
* 设置输入流；
* 必须为excel指定输入流
* @param inputstream
public void setinputstream(fileinputstream inputstream) {
this.inputstream = inputstream;
initworkbook();
* 设置读取sheet
* @param sheetindex
public void setsheetindex(int sheetindex) {
this.sheetindex = sheetindex;
* 设置读取数据起始行
* 默认从0开始
* @param startrow
public void setstartrow(int startrow) {
this.startrow = startrow;
* 设置行数据，读取数据起始列
* @param startcolumn
public void setstartcolumn(int startcolumn) {
this.startcolumn = startcolumn;
public static void main(string args[]){
map<integer,string> propertiesmap = new hashmap<integer,string>();
propertiesmap.put(-1, "com.randy.vo");
propertiesmap.put(0, "num");
propertiesmap.put(1, "str");
propertiesmap.put(2, "date");
excelreader reader = new excelreader();
fileinputstream excelfile = null;
excelfile = new fileinputstream("f:\\workbook.xls");
} catch (filenotfoundexception e) {
reader.setinputstream(excelfile);
list<hssfrow> rows = reader.readhssfrows();
for(hssfrow row:rows){
string[] values = reader.readcellsvalue(row);
for(string v:values){
system.out.print(v+"\t");
system.out.println();
object obj = reader.genvofromcells(propertiesmap,values);
system.out.println(obj);
}