ExcelReader.java
 
 
package com.ivfly.xlsbean; 

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(); 
        } 
 
当为对象填充值时,添加验证功能。
       //依次验证行的每个单元格 
        Boolean pv = rule.validateProperty(bean,propertyName,value); 
         if(!pv){ //验证不通过    
          sh.setCheck( false); //设置数据对象验证不通过 
          rule.addPropertyWarrning(i,bean,propertyName); 
        }