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 blocke.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 blocke.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;}}