一个Excel转换Java集合的工具类

 import java.io.FileInputStream;  
 import java.lang.reflect.InvocationTargetException;  
 import java.util.ArrayList;  
 import java.util.Collection;  
 import java.util.Iterator;  
 import java.util.Properties;  
   
 import org.apache.commons.beanutils.BeanUtils;  
 import org.apache.log4j.Logger;  
 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 com.tarena.crm.entity.CustomerProxy;  
   
 public class ExcelToCollectionUtil {  
     /** 
      * Logger for this class 
      */  
     private static final Logger logger = Logger  
             .getLogger(ExcelToCollectionUtil.class);  
     /** 
      * Excel和对象成员的映射集合. 
      */  
     private Properties pro;   
     /** 
      * Excel封装对象. 
      */  
     private HSSFWorkbook hb;  
     /** 
      * 坏数据静态异常类. 
      */  
     public static class ExcelBadRecordException extends Exception{  
         private StringBuilder ms=new StringBuilder();  
         private boolean flag=false;  
         public void addMessage(String s){  
             flag=true;  
             ms.append(s+"\n");  
         }  
         public ExcelBadRecordException(){  
             super();  
         }  
         @Override  
         public String getMessage() {  
             return ms.toString();  
         }  
         public boolean hasError(){  
             return flag;  
         }  
           
     }  
     private ExcelBadRecordException exception=new ExcelBadRecordException();  
     /** 
      * 集合中所存储的对象类型. 
      */  
     private Class clazz;  
   
     public ExcelToCollectionUtil(Properties pro, HSSFWorkbook hb, Class clazz) {  
         super();  
         this.pro = pro;  
         this.hb = hb;  
         this.clazz = clazz;  
     }  
     /** 
      * 从Excel导入到Collection 
      */  
     public Collection toCollection() {  
         Collection table=new ArrayList();   //保存数据的集合  
         HSSFSheet sheet = hb.getSheetAt(0); //得到第一个sheet ， 应该改进成可以选择的.  
         HSSFRow head = sheet.getRow(0); //得到第一行表头  
         for (int i = 1;; i++) {  
             HSSFRow row = sheet.getRow(i);  //得到一行记录  
             if(row==null)      //判断是否为最后记录， 应该判断记录是否有效.  
                 break;  
             Object object=null;  
             try {  
                 object = this.exchangObjectFromRow(head, row);  //通过一条记录产生一个对象  
             } catch (Exception e) {  
                 //遇到无效的数据跳过  
                 Exception ee=new Exception("第"+i+"条记录格式有错误！",e);  
                 exception.addMessage(ee.getMessage());  //将无效记录异常内容存在ExcelBadRecordException对象里  
                 continue;                                         
             }   
             table.add(object); //将产生的对象存入集合中  
         }  
         return table;  
     }  
     /** 
      * 抛出无效数据异常. 
      * @throws ExcelBadRecordException 
      */  
     public void showException() throws ExcelBadRecordException{  
         if(exception.hasError()){  
             throw exception;  
         }  
     }  
     /** 
      * 将一个row对象数据转换成javabean对象. 
      * @param head 
      * @param row 
      * @return 
      * @throws InvocationTargetException  
      * @throws IllegalAccessException  
      */  
     private Object exchangObjectFromRow(HSSFRow head,HSSFRow row) throws Exception{  
         StringBuilder sb = new StringBuilder();  
         Object object=this.newInstance(clazz);  
         if(row.getCell((short)0)==null){  
             throw new Exception("第一列单元格没有数据.");  
         }  
         for (int j = 0;; j++) {  
             HSSFCell cell = row.getCell((short) j);  //得到一个单元格.  
             if (cell == null)  
                 break;  
               
             String attribute=head.getCell((short)j).getRichStringCellValue().getString();  // 得到Excel中该列列名.  
             String property=pro.getProperty(attribute);                                 // 从Properties中得到对应的成员名  
             Object value=null;  //成员对象  
             if(property==null||property.equals("")||attribute.equals(""))  //防止有没有对应的字段映射  
                 continue;  
             switch (cell.getCellType()) {  // 判断单元格的数据类型，做不同处理.  
                 case HSSFCell.CELL_TYPE_STRING:  //判断是字符串类型  
                     value=cell.getRichStringCellValue().getString();                        // 得到对象的成员对象.  
                     break;  
                 case HSSFCell.CELL_TYPE_NUMERIC:  //判断是数字类型  
                     if(HSSFDateUtil.isCellDateFormatted(cell)){ //判断是标准日期类型（1996-1-1）  
                         value=cell.getDateCellValue();                                          // 得到对象的成员对象.  
                     }else{  
                         value=new Double(cell.getNumericCellValue()).longValue();  
                     }  
                     break;  
                 default:  
                     logger.error("excel里非字符串和数字的类型.");  
             }  
             sb.append(property+"="+value);  
             sb.append(" ");  
             BeanUtils.setProperty(object,property,value);  
               
         }  
         if (logger.isDebugEnabled()) {  
             //logger.debug(clazz.getSimpleName()+"[" + sb.toString() + "]");  
             logger.debug(BeanUtils.describe(object));  
         }  
         return object;  
     }  
     /** 
      * 创建对象. 
      * @param clazz 
      * @return 
      */  
     private Object newInstance(Class clazz){  
         try{  
             return  clazz.newInstance();  
         }catch(Exception e){  
             logger.error(e.getMessage());  
         }  
         return null;  
     }  
     public static void main(String[] args) throws Exception {  
         HSSFWorkbook book = new HSSFWorkbook(new FileInputStream("e:\\project\\test.xls"));  
           
         Properties pro=new Properties();  
         pro.load(ExcelToCollectionUtil.class.getClassLoader().getResourceAsStream("CustomerProxy.properties"));  
         ExcelToCollectionUtil util = new ExcelToCollectionUtil(pro, book, CustomerProxy.class);  
         Collection col=util.toCollection();  
         Iterator it=col.iterator();  
         while(it.hasNext()){  
             CustomerProxy con=(CustomerProxy) it.next();  
             System.out.println(con.getName()+" "+con.getValidStateString()+" "+con.getGradeString()+" "+con.getRegisterConsultantString());  
         }  
         util.showException();  
           
     }  
   
 } 

 

评论

4 楼 sunxboy 2014-09-10  

用的apache poi 包

3 楼 u010100704 2014-09-03  

晕死!

2 楼 gaoyue999 2009-12-21  

给的类也不全，郁闷。

1 楼 gaoyue999 2009-12-21  

用到了什么jar包也说一下好不，要不怎么查。。