ExcelReader.java
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.math.NumberUtils;
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.poifs.filesystem.POIFSFileSystem;
public class ExcelReader {
@SuppressWarnings( "unchecked")
public static List<ExcelAble> parseXls2Beans(BeanSpecification bean, String dir,String fileName){
bean.setFileName(fileName);
try {
return (List<ExcelAble>)xlsToBean(dir,bean);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@SuppressWarnings( "unchecked")
private static List<ExcelAble> xlsToBean(String dir,BeanSpecification bean) throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
String fileName = dir+bean.getFileName();
POIFSFileSystem poi = new POIFSFileSystem( new FileInputStream(fileName));
HSSFWorkbook wb = new HSSFWorkbook(poi);
HSSFSheet sheet = wb.getSheetAt(0);
int headCol = 0;
int fromCol = 1;
if (bean.getHead() != null) {
headCol = bean.getHead() - 1 < 0 ? 0 : bean.getHead() - 1;
}
if (bean.getHead() != null) {
fromCol = bean.getFrom() - 1 < 1 ? 1 : bean.getFrom() - 1;
}
/**
* 解析头部信息
*/
HSSFRow head = sheet.getRow(headCol);
int cols=head.getPhysicalNumberOfCells();
String[] p = new String[cols];
for ( int j = 0; j < cols; j++) {
HSSFCell cell = head.getCell(j);
String col = cell.getStringCellValue();
if (bean.getPropertyNameByValue(col) != null) {
p[j] = bean.getPropertyNameByValue(col);
}
}
Class clazz = Class.forName(bean.getClassName());
List<ExcelAble> result = new ArrayList<ExcelAble>();
int rows=sheet.getPhysicalNumberOfRows();
begin: for ( int i = fromCol; i < rows; i++) {
HSSFRow row = sheet.getRow(i);
ExcelAble sh = (ExcelAble) clazz.newInstance(); //新建对象
XlsImpRule rule = new XlsImpRule(); //为对象定义规则对象
for ( int j = 0; j < cols; j++) {
String propertyName = p[j]; //Bean 属性名称
if (propertyName == null) {
continue;
}
Object value = null;
HSSFCell cell = row.getCell(j);
if(cell== null){
if(!bean.nullable(propertyName)) {
break begin;
} else{
continue;
}
}
//获取单元格的值
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
//TODO 这里或许有情况
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
} else {
value = cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
//字符串比较特殊,当此列不能为空的时候,空字符串也不行
if(FileUtil.isEmpty((String)value)){
if(!bean.nullable(propertyName)) {
break begin;
}
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
} else{
value = cell.getNumericCellValue();
}
break;
case 3:
value = null;
if(1==1){
throw new RuntimeException();
}
if(!bean.nullable(propertyName)) {
break begin;
} else{
break;
}
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR:
value = cell.getErrorCellValue();
break;
default:
break;
} //获取单元格的值
// if()
try {
Class type = PropertyUtils.getPropertyType(sh,propertyName);
if(Integer. class ==type || int. class == type){
try {
String valueOf = String.valueOf(value);
value = Integer.valueOf(valueOf);
//value = NumberUtils.toInt(value.toString());
} catch (Exception e) {
if(Double. class == value.getClass()){
value = ((Double)value).intValue();
} else{
value = 0;
}
}
}
if(Long. class ==type || long. class == type){
System.out.println(propertyName+ "是Long形");
}
} catch (InvocationTargetException e1) {
e1.printStackTrace();
} catch (NoSuchMethodException e1) {
e1.printStackTrace();
}
//依次验证行的每个单元格
Boolean pv = rule.validateProperty(bean,propertyName,value);
if(!pv){ //验证不通过
sh.setCheck( false); //设置数据对象验证不通过
rule.addPropertyWarrning(i,bean,propertyName);
}
try {
BeanUtils.setProperty(sh, propertyName, value);
} catch (Exception e) {
e.printStackTrace();
}
}
if(!sh.isCheck()){
sh.setMessage(rule.reportWarrning());
}
result.add(sh);
}
return result;
}
}
解析类,使用了BeanUtils 提供的方法,自动为属性赋值。有一个问题,当Excel中为数字类型时,如果对象的属性为Integer(int)、Long(long)时,会转换不成功。
所以添加了如下语句
try {
Class type = PropertyUtils.getPropertyType(sh,propertyName);
if(Integer. class ==type || int. class == type){
try {
String valueOf = String.valueOf(value);
value = Integer.valueOf(valueOf);
//value = NumberUtils.toInt(value.toString());
} catch (Exception e) {
if(Double. class == value.getClass()){
value = ((Double)value).intValue();
} else{
value = 0;
}
}
}
if(Long. class ==type || long. class == type){
System.out.println(propertyName+ "是Long形");
}
} catch (InvocationTargetException e1) {
e1.printStackTrace();
} catch (NoSuchMethodException e1) {
e1.printStackTrace();
}
Class type = PropertyUtils.getPropertyType(sh,propertyName);
if(Integer. class ==type || int. class == type){
try {
String valueOf = String.valueOf(value);
value = Integer.valueOf(valueOf);
//value = NumberUtils.toInt(value.toString());
} catch (Exception e) {
if(Double. class == value.getClass()){
value = ((Double)value).intValue();
} else{
value = 0;
}
}
}
if(Long. class ==type || long. class == type){
System.out.println(propertyName+ "是Long形");
}
} catch (InvocationTargetException e1) {
e1.printStackTrace();
} catch (NoSuchMethodException e1) {
e1.printStackTrace();
}
当为对象填充值时,添加验证功能。
//依次验证行的每个单元格
Boolean pv = rule.validateProperty(bean,propertyName,value);
if(!pv){ //验证不通过
sh.setCheck( false); //设置数据对象验证不通过
rule.addPropertyWarrning(i,bean,propertyName);
}
Boolean pv = rule.validateProperty(bean,propertyName,value);
if(!pv){ //验证不通过
sh.setCheck( false); //设置数据对象验证不通过
rule.addPropertyWarrning(i,bean,propertyName);
}
本文转自 randy_shandong 51CTO博客,原文链接:http://blog.51cto.com/dba10g/756538,如需转载请自行联系原作者