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();
}
}
一个Excel转换Java集合的工具类
最新推荐文章于 2022-09-19 09:15:56 发布