Excel的解析--使用apache POI组件

弄了个文件上传，顺便多加了个功能，将上传的excel文件解析成定义好的实体。使用的是POI组件进行excel操作。

POI组件可以上http://poi.apache.org/去下载（下图是POI能够解析的文档）

解析excel文件设计思路如下图：

（1）.映射文件xml：映射excel每一行每个字段对应的目标实体的每个field，每一个bean元素对应一个目标实体类：
（2）.映射文件读取程序：负责解析xml，讲对应属性转成映射实体。
（3）.映射实体：每一个映射实体对象对应xml的一个bean元素的property子元素，讲映射实体对象组合起来就是bean元素。
（4）结合映射实体Map，读取Excel文件并转换成目标实体的List集合。
整个Excel解析是为了将特定格式的excel表格内容转换成java的实体bean。下面上代码:

xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans>
	<bean name="TestUser" class="com.sinosoft.demo.schema.model.TestUser">
		<property name="usercode" position="1" length="20" isnull="false" isnum=""></property>
		<property name="username" position="2" length="20"></property>
		<property name="age" position="3" length="20" isnull="false" isnum = "true"></property>
		<property name="sex" position="4" length="1"></property>
		<property name="birthdate" position="5" length="19" isdate="true"></property>
		<property name="mobile" position="6" length="20"></property>
		<property name="validstatus" position="7" length="1"></property>
		<property name="flag" position="8" length="15"></property>
	</bean>
</beans>

映射实体：

package com.sinosoft.demo.common.web.bean;

public class XlsLoadVerifyBean {
	private String name;
	private Integer length;
	private boolean isNull;
	private boolean isNum;
	private boolean isDate;
	public boolean isDate() {
		return isDate;
	}
	public void setDate(boolean isDate) {
		this.isDate = isDate;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getLength() {
		return length;
	}
	public void setLength(Integer length) {
		this.length = length;
	}
	public boolean isNull() {
		return isNull;
	}
	public void setNull(boolean isNull) {
		this.isNull = isNull;
	}
	public boolean isNum() {
		return isNum;
	}
	public void setNum(boolean isNum) {
		this.isNum = isNum;
	}
}

xml读取程序：

package com.sinosoft.demo.common.util;

import java.io.File;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.sinosoft.demo.common.web.bean.XlsLoadVerifyBean;

/**
 * 通过读取文件xml文件解析出类名为beanName的bean的各个field的属性
 * @author tianxingjian
 *
 */
public class XmlRead4Xls {

	
	public Map<Integer, Object> readXml(String beanName){
		String filePath = "xlsLoad2DB.xml";
		
		return readXml(filePath, beanName);
	}
	
	public  Map<Integer, Object> readXml(String filePath, String beanName){
		
		SAXReader saxReader = new SAXReader(); 			//使用SAXReader方式读取XML文件
		Map<Integer, Object> map = new HashMap<Integer, Object>();
        //加载数据库XML配置文件，得到Document对象
        Document document;
		try {
			File file = new File(filePath);
			//System.out.println(file.getAbsolutePath());
			document = saxReader.read(this.getClass().getClassLoader().getResourceAsStream(filePath));
			Element root = document.getRootElement();				 		//获得根节点
	        Iterator iterator = root.elementIterator();
			while(iterator.hasNext()){
				 Element element = (Element) iterator.next();
	        	 if( element.attributeValue("name") != null &&  beanName.equals(element.attributeValue("name"))){
	        		 Iterator property = element.elementIterator();
		        	 while(property.hasNext()){
		        		 Element elementPro = (Element) property.next();
		        		 XlsLoadVerifyBean xlsLoadVerifyBean = new XlsLoadVerifyBean(); 
		        		 xlsLoadVerifyBean.setName(elementPro.attributeValue("name"));
		        		 xlsLoadVerifyBean.setLength(Integer.valueOf((elementPro.attributeValue("length") == null || elementPro.attributeValue("length") == "") ? "0" : elementPro.attributeValue("length")));
		        		 xlsLoadVerifyBean.setNull(Boolean.valueOf((elementPro.attributeValue("isnull") == null || elementPro.attributeValue("isnull") == "") ? "true" : elementPro.attributeValue("isnull")));
		        		 xlsLoadVerifyBean.setNum(Boolean.valueOf((elementPro.attributeValue("isnum") == null || elementPro.attributeValue("isnum") == "") ? "false" : elementPro.attributeValue("isnum")));
		        		 xlsLoadVerifyBean.setDate(Boolean.valueOf((elementPro.attributeValue("isdate") == null || elementPro.attributeValue("isdate") == "") ? "false" : elementPro.attributeValue("isdate")));
		        		 map.put(Integer.valueOf(elementPro.attributeValue("position")), xlsLoadVerifyBean);
		        	 }
	        	 }
			}
		} catch (DocumentException e) {
			e.printStackTrace();
		} 
        
		return map;
	 }
}

目标实体：注释是hibernate注释，如果没用hibernate可以删除

package com.sinosoft.demo.schema.model;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "testUser")
public class TestUser implements Serializable {

	private static final long serialVersionUID = 1L;

	private String usercode; // -- 人员代码
	private String username; // 人员名称
	private Integer age;// 年龄
	private String sex;// 性别 1：男 2：女
	private Date birthdate;// 出生日期
	private String mobile;// 联系电话
	private String validstatus;// 有效状态 1：有效 0：无效

	private String flag;

	/**
	 * 人员代码
	 */

	@Id
	@Column(name = "usercode")
	public String getUsercode() {
		return usercode;
	}

	public void setUsercode(String usercode) {
		this.usercode = usercode;
	}

	/**
	 * 年龄
	 */
	@Column(name = "username")
	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	/**
	 * 性别 1：男 2：女
	 */
	@Column(name = "age")
	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	/**
	 * 性别 1：男 2：女
	 */
	@Column(name = "sex")
	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	/**
	 * 出生日期
	 */
	@Column(name = "birthdate")
	public Date getBirthdate() {
		return birthdate;
	}

	public void setBirthdate(Date birthdate) {
		this.birthdate = birthdate;
	}

	/**
	 * 联系电话
	 */
	@Column(name = "mobile")
	public String getMobile() {
		return mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	/**
	 * 有效状态 1：有效 0：无效
	 */
	@Column(name = "validstatus")
	public String getValidstatus() {
		return validstatus;
	}

	public void setValidstatus(String validstatus) {
		this.validstatus = validstatus;
	}

	@Column(name = "flag")
	public String getFlag() {
		return flag;
	}

	public void setFlag(String flag) {
		this.flag = flag;
	}
	
	public String toString(){
		return "{员工编码：" + this.usercode+ "}" + "\t" + "{员工名称：" + this.username+ "}" + "\t" + "{员工年龄：" + this.age+ "}"
				+ "\n" + "{员工性别：" + this.sex + "}" + "\t" + "{出生日期：" + this.birthdate+ "}" + "\t" + "{电话号码：" + this.mobile+ "}"
				+ "\n" + "{有效标识：" + this.validstatus + "}" + "\t" + "{标志：" + this.flag+ "}";
	}
}

Excel解析程序：支持xls和xlsx后缀两种格式

package com.sinosoft.demo.common.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
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.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sinosoft.demo.common.web.bean.XlsLoadVerifyBean;
import com.sinosoft.demo.schema.model.TestUser;

/**
 * 读取excel类
 * @author tianxingjian
 *
 */
public class ReadXlsAndXlsx {
	public static String errLog = ""; 
	
	public static Object[] loadXls(String filePath, int fromRow, String beanName, Class clazz){
		errLog = ""; 
		Object[] objs = null;
		List<Object> list = new ArrayList();
		Map<String, Object> m = null;
		XmlRead4Xls xrx = new XmlRead4Xls();
		Map<Integer, Object> xmlM = xrx.readXml(beanName);		//从配置文件中读取xls文件中的参数位置配置
		InputStream input = null;
		try {
			input = new FileInputStream(filePath);
			POIFSFileSystem fs = new POIFSFileSystem(input);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			// Iterate over each row in the sheet   
			Iterator rows = sheet.rowIterator();
			while (rows.hasNext()) {
				HSSFRow row = (HSSFRow) rows.next();
				m = new HashMap();
				Object obj = clazz.newInstance();
				if(row.getRowNum() >= fromRow){
					Iterator cells = row.cellIterator();
					int count = 1;			//数一行中的位置
					boolean flag = true;
					while (cells.hasNext()) {
						HSSFCell cell = (HSSFCell) cells.next();
						String cellValue = "";
						switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_NUMERIC:
								cellValue = String.valueOf(((Double)cell.getNumericCellValue()).longValue());
								break;
							case HSSFCell.CELL_TYPE_STRING:
								cellValue = cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN:
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case HSSFCell.CELL_TYPE_FORMULA:
								cellValue = String.valueOf(cell.getCellFormula());
								break;
							case HSSFCell.CELL_TYPE_BLANK:
								//System.out.println(cell.getStringCellValue());
							default:
								//System.out.println("unsuported sell type");
								break;
						}
						if(xmlM.get(count) != null){
							XlsLoadVerifyBean xlsLoadVerifyBean = (XlsLoadVerifyBean)xmlM.get(count);
							flag = verifyBean(xlsLoadVerifyBean, row.getRowNum(), count, cellValue);
							if(!flag){
								break;
							}
							if(xlsLoadVerifyBean.isDate()){
								Date cellDateValue = DateConvert.getDate(cellValue);
								BeanUtils.setProperty(obj, ((XlsLoadVerifyBean)xmlM.get(count)).getName(), cellDateValue);
							}else{
								BeanUtils.setProperty(obj, ((XlsLoadVerifyBean)xmlM.get(count)).getName(), cellValue);
							}
						}
						count++;
					}
					
					if(obj != null && flag){
						list.add(obj);
					}
					
				}
			}
			m = (Map<String, Object>) new HashMap();
			m.put("errLogXls", errLog);
			
		} catch (IOException ex) {
			ex.printStackTrace();
		}catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}finally{
			if(input != null){
				try {
					input.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		objs = new Object[]{list, m};
		return objs;
	}
	
	public static Object[] loadXlsx(String filePath, int fromRow, String beanName, Class clazz) { 
		Object[] objs = null;
		List<Object> list = new ArrayList<Object>();
		Map<String, Object> m = null;
		XmlRead4Xls xrx = new XmlRead4Xls();
		Map<Integer, Object> xmlM = xrx.readXml(beanName);		//从配置文件中读取xlsx文件中的参数位置配置
		XSSFWorkbook xwb = null;
		Object obj;
		InputStream input = null;
		try {
			input = new FileInputStream(filePath);
			xwb = new XSSFWorkbook(input);
			XSSFSheet sheet = xwb.getSheetAt(0); 
			XSSFRow row;    
			if(fromRow >= sheet.getPhysicalNumberOfRows()){
				fromRow = sheet.getPhysicalNumberOfRows() -1 ;
			}
			for (int i = fromRow; i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				int count = 1;			//数一行中的位置
				m = new HashMap<String, Object>();
				obj = clazz.newInstance();
				boolean flag = true;
				for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
					XSSFCell cell = row.getCell(j);
					String cellValue = "";
					
					switch (cell.getCellType()) {
						case XSSFCell.CELL_TYPE_NUMERIC:
							cellValue = String.valueOf(((Double)cell.getNumericCellValue()).longValue());
							break;
						case XSSFCell.CELL_TYPE_STRING:
							cellValue = (cell.getStringCellValue());
							break;
						case XSSFCell.CELL_TYPE_BOOLEAN:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case XSSFCell.CELL_TYPE_FORMULA:
							cellValue = String.valueOf(cell.getCellFormula());
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							cellValue = (cell.getStringCellValue());
							break;
						default:
							//System.out.println("unsuported sell type");
							break;
					
					}
//					System.out.println(cellValue);
					if(xmlM.get(count) != null){
						XlsLoadVerifyBean xlsLoadVerifyBean = (XlsLoadVerifyBean)xmlM.get(count);
						flag = verifyBean(xlsLoadVerifyBean, row.getRowNum(), count, cellValue);
						if(!flag){
							break;
						}
						if(xlsLoadVerifyBean.isDate()){
							Date cellDateValue = DateConvert.getDate(cellValue);
							BeanUtils.setProperty(obj, ((XlsLoadVerifyBean)xmlM.get(count)).getName(), cellDateValue);
						}else{
							BeanUtils.setProperty(obj, ((XlsLoadVerifyBean)xmlM.get(count)).getName(), cellValue);
						}
						
						//m.put(((XlsLoadVerifyBean)xmlM.get(count)).getName(), cellValue);
					}
					count++;
					
				}
				if(obj != null && flag){
					list.add(obj);
				}
			}
			m = (Map<String, Object>) new HashMap();
			m.put("errLogXls", errLog);
			objs = new Object[]{list, m};
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			//System.out.println("读取文件出错");
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				input.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return objs;
	} 
	
	public static boolean verifyBean(XlsLoadVerifyBean xlsLoadVerifyBean, int rowNum, int colNum, String value){
		if(xlsLoadVerifyBean.getLength() > 0){
			if(value.length() > xlsLoadVerifyBean.getLength()){
				errLog += "第" + rowNum + "行，第" + colNum + "列，超过系统规定字符长度，第" + rowNum + "行暂未导入；";
				return false;
			}
			
		}
		
		if(!xlsLoadVerifyBean.isNull()){
			if(value == null || value.compareTo("") == 0){
				errLog += "第" + rowNum + "行，第" + colNum + "列 不允许为空，第" + rowNum + "行暂未导入；";
				return false;
			}
		}
		if(xlsLoadVerifyBean.isNum()){
			Pattern pattern = Pattern.compile("[0-9]*");
			Matcher isNum = pattern.matcher(value); 
			if( !isNum.matches() ) {
				errLog += "第" + rowNum + "行，第" + colNum + "列 必须为数字类型，第" + rowNum + "行暂未导入；";
				return false;
			}
		}
		
		
		return true;
	}
	
	public static Object[] loadData(String filePath, int fromRow, String beanName, Class clazz){
		String tempStr = filePath.substring(filePath.lastIndexOf("."));
		if (".xls".equals(tempStr)){
			return loadXls(filePath, fromRow, beanName, clazz);
		}else if(".xlsx".equals(tempStr)){
			return loadXlsx(filePath, fromRow, beanName, clazz); 
		}
		return null;
	}
}